Implanter le Modèle Relationnel suivant :
etudiant(noEtudiant, nomEtudiant, #idGroupe)
groupe(idGroupe,libelleGroupe)
Une clé étrangère est un ensemble d’une ou de plusieurs colonnes d’une relation (table) (précédée par le symbole #) qui fait référence à une clé primaire d’une (autre) relation (table).
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 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,'S2A1'); -- il id 1
INSERT INTO groupe VALUES(2,'S2A2'); -- id 2
INSERT INTO groupe VALUES(3,'S2bisA1'); -- id 3
INSERT INTO etudiant VALUES(1,'paul',1);
INSERT INTO etudiant VALUES(2,'pierre',2);
INSERT INTO etudiant VALUES(3,'toto',17);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é).
Faire ATTENTION à l'ordre de suppression et de création des tables quand il y a une contrainte.
Dans quel ordre faut il supprimer ou créer les tables ?
Supprimer le groupe créé : DELETE FROM groupe WHERE idGroupe=1;
Modifier un enregistrement : UPDATE groupe SET libelleGroupe='1D1' WHERE idGroupe=1;
Conclusion :
Peut on créer une contrainte de type référentielle entre un champ « clé étrangère » d’une table et un champ d’une autre table qui n'est pas une clé primaire ?
Supprimer les 2 mots : “CONSTRAINT fk_etudiant_groupe”, MYSQL donne t'il un nom à cette contrainte ? (vérifier avec la commande SHOW CREATE TABLE etudiant;)
Un logiciel comme « analyseSI » ou « JMerise » ne sait pas dans quel ordre supprimer ou créer les contraintes sur les clés étrangères, il le fait dans un deuxième temps.
Utiliser les commandes ci dessous pour supprimer puis remettre la contrainte sur la clé étrangère de la table etudiant
Remarques :
l’objectif est d’afficher les étudiants et leur groupe associé :
Ouvrir ce lien sur Wikipédia qui concerne l’algèbre relationnelle
Tester les instructions ci-dessous.
Ajouter en commentaire sous chaque groupe d’instruction le(s) opérateur(s) (Opérateurs relationnels ou ensemblistes) de l'algèbre relationnelle.
Les opérateurs sont dans la liste suivante :
Modifier le script ci-dessous :
groupe_id dans la table Etudiant est une clé étrangère (la colonne groupe_id a une dépendance fonctionnelle sur la clé primaire id_groupe de la table groupe).etudiant_id dans la table Etudiant est une clé étrangère.]{.modif}
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 |
+----------------+---------+
Utiliser le fichier looping ci-joint et modifier le pour obtenir le bon MLD
ANNEXE :
