TP7 : gestion des clés étrangères - Jointures


Modèle relationnel

Implanter le Modèle Relationnel suivant :

etudiant(noEtudiant, nomEtudiant, #idGroupe)

groupe(idGroupe,libelleGroupe)

Une clé étrangère est une colonne d’une table (relation) qui fait référence à une clé primaire d’une autre table (relation).
Par convention une clé étrangère est souvent précédée par le symbole #.
On parle de contrainte d’intégrité et plus précisément de Contrainte de référence : Les seules valeurs que peut prendre une clé étrangère sont celles qui sont déjà saisies dans la clé primaire de la table qu’elle référence.


Créer un script SQL du type :


DROP TABLE IF EXISTS ... ;

DROP TABLE IF EXISTS ... ;

CREATE TABLE ... (

) ;

CREATE TABLE ... (

) ;


INSERT INTO groupe VALUES(1,'S1A1');    -- il faudrait mettre NULL pour 
INSERT INTO groupe VALUES(2,'S1A2');    -- les valeurs des identifiants
INSERT INTO groupe VALUES(3,'S1B1');    -- (on force les valeurs des identifiants)
INSERT INTO groupe VALUES(4,'S3');   

INSERT INTO etudiant VALUES(1,'paul',1);
INSERT INTO etudiant VALUES(2,'pierre',2);
INSERT INTO etudiant VALUES(3,'toto',17);  -- si erreur mettre le groupe à 2 ou 3
INSERT INTO etudiant VALUES(4,'jacques',NULL);

Remarque : les valeurs des clés primaires sont imposées mais il est souhaitable de remplacer par la valeur “NULL” par la suite



Article sur toutes les contraintes (plus compliqué).



C.I. référentielle : A chaque valeur d’une clé étrangère doit correspondre une valeur de la clé primaire associée

Définition d’une « clé étrangère »

Faire ATTENTION à l'ordre de suppression et de création des tables quand il y a une contrainte.


Conclusion :


Utiliser les commandes ci dessous pour supprimer puis remettre la contrainte sur la clé étrangère de la table etudiant



Tester instruction après instruction et cliquer dans datagrip sur le contenu de la colonne pour afficher le résultat complet de l’instruction SHOW CREATE TABLE etudiant;

exemple d’affichage pour l’instruction SHOW CREATE TABLE etudiant;

Remarques :

Présentation des jointures

l’objectif est d’afficher les étudiants et leur groupe associé :



MLD

MCD





Activer l’option pour afficher le résultat des requêtes dans l’éditeur



Exercice

Modifier le script ci-dessous :


DROP TABLE IF EXISTS xxxx;
DROP TABLE IF EXISTS Etudiant;
DROP TABLE IF EXISTS xxxx;
DROP TABLE IF EXISTS xxxx;
DROP TABLE IF EXISTS xxxx;
DROP TABLE IF EXISTS xxxx;

CREATE TABLE Etudiant (
  idEtudiant INT AUTO_INCREMENT
  , nom VARCHAR(20)
  , adresse VARCHAR(40)
  , ville VARCHAR(20)
  , code_postal VARCHAR(5) 
  , telephone VARCHAR(10) 
  , date_naissance DATE
  , sexe VARCHAR(1)
  , boursier VARCHAR(1)
  , groupe_id INT
  , PRIMARY KEY(idEtudiant)
);

CREATE TABLE etudiant_periode (
    etudiant_id INT,
    periode_id INT,
    semestre_id INT,
    PRIMARY KEY (etudiant_id,periode_id,semestre_id)
);

CREATE TABLE Groupe (
    id_groupe INT AUTO_INCREMENT,
    libelle VARCHAR(255),
    PRIMARY KEY (id_groupe)
);

CREATE TABLE Semestre (
    id_semestre INT AUTO_INCREMENT,
    libelle VARCHAR(255),
    PRIMARY KEY (id_semestre)
);

CREATE TABLE Periode (
    id_periode INT AUTO_INCREMENT,
    date_debut  DATE,
    date_fin DATE,
    PRIMARY KEY (id_periode)
);


-- 2026  
-- attention les colonnes ne sont pas toutes les mêmes que dans le TP5-6

INSERT INTO Etudiant (idEtudiant, nom, adresse, ville, code_postal, telephone, date_naissance, sexe, boursier, groupe_id) VALUES
(1, 'BERNARD', '1 rue sous bois', 'Belfort', '90000', '0384545401', '2006-01-01', 'F', 'N', 1),
(19, 'CHAVEAUX', '5 rue du chasseur', 'Strasbourg', '68000', '0384545419', '2006-01-01', 'H', 'N', 2),
(20, 'PRETTOT', '8 rue vilapogo', 'Belfort', '90000', '0384545420', '2006-07-12', 'H', 'O', 2),
(7, 'RIOT', '67 rue pasteur', 'Montbeliard', '25200', '0384545407', '2006-06-06', 'H', 'N', 2),
(8, 'BOISSENIN', '1 rue sous bois', 'Belfort', '90000', '0384545408', '2006-08-09', 'H', 'O', 2),
(2, 'PEQUIGNOT', '2 rue de la liberation', 'Valdoie', '90300', '0384545402', '2005-06-06', 'H', 'O', 1),
(3, 'ZILLIOX', '7 rue du verger', 'Bavilliers', '90120', '0384545403', '2005-01-01', 'H', 'N', 1),
(4, 'MONNIER', '3 rue du boulanger', 'TAILLECOURT', '25400', '0384545404', '2005-02-06', 'H', 'O', 1),
(5, 'BRISCHOUX', '5 rue du chasseur', 'Belfort', '90000', '0384545405', '2005-05-25', 'H', 'N', 2),
(6, 'DUVAL', '8 rue vilapogo', 'Bavilliers', '90120', '0384545406', '2005-03-11', 'H', 'O', 2),
   (10, 'FAIVRE', '7 rue des vergers  de rioz', 'Valdoie', '90300', '0384545410', '2005-01-05', 'H', 'O', 3),
(17, 'DELANOE', '7 rue du verger', 'Valdoie', '90300', '0384545417', '2005-01-01', 'H', 'N', 1),
(18, 'BONVALOT', '3 rue du boulanger', 'Belfort', '90000', '0384545418', '2005-01-01', 'H', 'O', 1),
(21, 'COULON', '67 rue pasteur', 'Valdoie', '90300', '0384545421', '2005-01-01', 'H', 'N', 2),
(23, 'KENDE', '2 rue de la liberation', 'Bavilliers', '90120', '0384545423', '2005-05-31', 'H', 'N', 3),
(24, 'KLEIN', '7 rue du verger', 'Montbeliard', '25200', '0384545424', '2005-01-01', 'H', 'N', 3),
(25, 'VALZER', '3 rue du boulanger', 'Valdoie', '90300', '0384545425', '2005-01-06', 'H', 'O', 3),
(26, 'PY', '5 rue du chasseur', NULL, '90000', '0384545426', '2005-01-10', 'F', 'N', 3),
(27, 'VERNET', '8 rue vilapogo', NULL, '90120', '0384545427', '2005-02-02', 'H', 'O', 4),
(28, 'BAILLIT', '67 rue pasteur', NULL, '25200', '0384545428', '2005-01-01', 'H', 'N', 4),
(16, 'LUZET', '2 rue de la liberation', 'Belfort', '90000', '0384545416', '2004-01-01', 'H', 'O', 1),
  (22, 'VALOT', '1 rue sous bois', '', '90000', '0384545422', '2004-12-12', 'H', 'O', 2),
(29, 'DUPONT', '8 rue vilapogo', '', '90300', '0384545429', '2004-06-06', 'H', 'O', 4),
   (11, 'FAIVRE', '3 rue des vergers', 'Cernay', '68000', '0384545411', '2004-01-01', 'F', 'N', 3),
(12, 'DUCHENNE', '5 rue du chasseur', 'Belfort', '90000', '0384545412', '2004-01-01', 'F', 'O', 3),
(13, 'BOULANGER', '8 rue vilapogo', 'Belfort', '90000', '0384545413', '2004-01-01', 'F', 'N', 1),
   (9, 'FONTAINE-LEGIOT', '2 rue des vergers', 'Mulhouse', '68000', '0384545409', '2003-01-01', 'H', 'N', 3),
(14, 'MOREAU', '67 rue pasteur', 'Belfort', '90000', '0384545414', '2003-06-01', 'H', 'O', 1),
(15, 'RIGOULOT', '1 rue sous bois', 'Valdoie', '90300', '0384545415', '2003-12-12', 'H', 'N', 1);


INSERT INTO etudiant_periode VALUES (1,1,1),(19,1,2),(20,1,2),(7,1,1),(8,1,1),(2,1,1),(3,1,1),(4,1,1),(5,1,1),
(6,1,1),(10,1,1),(17,1,2),(18,1,2),(21,1,2),(23,1,2),(24,1,2),(25,1,2),(26,1,2),(27,1,3),(28,1,3),(16,1,2),
(22,1,2),(29,1,3),(11,1,1),(12,1,1),(13,1,2),(9,1,1),(14,1,2),(15,1,2); 

INSERT INTO Semestre VALUES (NULL,'S1'),(NULL,'S2'),(NULL,'S3'),(NULL,'S4'),(NULL,'S5'),(NULL,'S6');

INSERT INTO Groupe VALUES (NULL,'A1'),(NULL,'A2'),(NULL,'B1'),(NULL,'B2'),(NULL,'C1'),(NULL,'C2'),(NULL,'D1');


INSERT INTO Periode VALUES (NULL,'2023-09-01','2024-01-15'),(NULL,'2024-1-15','2024-7-1'),
                           (NULL,'2024-09-01','2025-01-15'),(NULL,'2025-1-15','2025-7-1'),
                           (NULL,'2025-09-01','2026-01-15'),(NULL,'2026-1-15','2026-7-1'),
                           (NULL,'2026-09-01','2027-01-15'),(NULL,'2027-1-15','2027-7-1');

+----------------+---------+
| Nbre_etudiants | libelle |
+----------------+---------+
|             12 | S1      |
|             14 | S2      |
|              3 | S3      |
+----------------+---------+
+----------------+---------+
| Nbre_etudiants | libelle |
+----------------+---------+
|             12 | S1      |
|             14 | S2      |
|              3 | S3      |
|              0 | S4      |
|              0 | S5      |
|              0 | S6      |
+----------------+---------+

Activer l’option pour afficher le résultat des requêtes dans l’éditeur

Réflexion sur le schéma

Utiliser le fichier looping ci-joint et modifier le pour obtenir le bon MCD (Modèle Conceptuel de Données)