Chapitre 813 minIntermédiaire
Passage du MCD au MLD : les règles
Par Josselyn C., prof de BTS SIO. Mis à jour le .
À la fin de ce chapitre, tu sauras
- écrire un MLD au format des sujets ;
- placer chaque clé étrangère du bon côté ;
- traduire les associations plusieurs à plusieurs, réflexives et ternaires ;
- vérifier un MLD avant d'écrire le SQL.
4 exercices corrigés2 schémas
Le passage du MCD au MLD transforme le schéma entité-association en schéma relationnel, c'est-à-dire en tables, clés primaires et clés étrangères. Il suit quelques règles fixes : une entité devient une table, une association un à plusieurs devient une clé étrangère, une association plusieurs à plusieurs devient une table. Ce chapitre donne toutes les règles, avec un exemple pour chacune, la façon de noter un MLD comme dans les sujets, et le MLD complet de Rivelire.
Le MLD et sa notation
Le MLD (modèle logique de données), qu'on appelle aussi schéma relationnel, décrit les tables de la future base : leur nom, leurs colonnes, leur clé primaire et leurs clés étrangères. Il ne contient plus d'associations ni de cardinalités : tout lien est devenu une clé étrangère.
Les sujets et les cours l'écrivent sous forme de texte, une ligne par table. La clé primaire est soulignée et chaque clé étrangère est précédée d'un #. Le Markdown ne sait pas souligner : dans ce cours, comme dans le cours SQL, la clé primaire est en gras.
exemplaire (code_barre, date_acquisition, etat, #num_document, #code_site)
Les sujets d'examen précisent souvent les clés en toutes lettres sous chaque table, ce qui lève toute ambiguïté :
Exemplaire (codeBarre, dateAcquisition, etat, numDocument, codeSite)
Clé primaire : codeBarre
Clé étrangère : numDocument en référence à numDocument de Document
Clé étrangère : codeSite en référence à codeSite de Site
Le MLD se dessine aussi : une boîte par table, une flèche de chaque clé étrangère vers la table qu'elle référence. C'est ce que produisent les logiciels de modélisation et ce que tu trouveras dans la documentation technique d'une application.
Règle 1 : une entité devient une table
Chaque entité devient une table de même nom. Ses propriétés deviennent des colonnes ; son identifiant devient la clé primaire.
SITE (code_site, nom_site, adresse_site, ville_site)
site (code_site, nom_site, adresse_site, ville_site)
Règle 2 : une association un à plusieurs devient une clé étrangère
Une association binaire dont une patte a un maximum 1 et l'autre un maximum n disparaît. La table de l'entité du côté du maximum 1 reçoit une clé étrangère qui référence la clé primaire de l'autre table.
EXEMPLAIRE 1,1 ──── RATTACHER ──── 0,n SITE
exemplaire (code_barre, date_acquisition, etat, #code_site)
site (code_site, nom_site, adresse_site, ville_site)
Pourquoi de ce côté ? Un exemplaire a un seul site : une colonne code_site dans exemplaire contient une seule valeur. Dans l'autre sens, un site a des centaines d'exemplaires : il faudrait des centaines de colonnes dans site, ce qui est impossible.
Le minimum décide si la clé étrangère est obligatoire :
- 1,1 : la clé étrangère est obligatoire (
NOT NULLau MPD). Un exemplaire a toujours un site. - 0,1 : la clé étrangère est facultative (elle accepte
NULL). Un ticket d'incident peut ne concerner aucun poste.
Règle 3 : une association plusieurs à plusieurs devient une table
Une association binaire dont les deux maximums valent n devient une table, qu'on appelle table d'association (ou table de jointure). Sa clé primaire est composée des clés primaires des deux entités, qui sont aussi des clés étrangères. Les propriétés portées par l'association deviennent des colonnes de cette table.
ADHERENT 0,n ──── RESERVER (date_reservation) ──── 0,n DOCUMENT
reserver (#num_adherent, #num_document, date_reservation)
La clé primaire composée traduit exactement la règle du MCD : un même couple (adhérent, document) ne peut apparaître qu'une fois. Les minimums (0 ou 1) n'ont ici aucun effet sur la structure.
La table prend le nom de l'association, ou un nom plus parlant : reserver peut s'appeler reservation, contenir peut devenir ligne_commande. Garde le même nom dans tout le dossier.
Règle 4 : une association un à un
Une association binaire dont les deux maximums valent 1 se traduit selon les minimums.
| Cardinalités | Traduction | Exemple |
|---|---|---|
| 0,1 et 1,1 | Clé étrangère dans la table du côté 1,1, obligatoire et unique | voiture_fonction (immatriculation, modele, #matricule) avec matricule unique |
| 0,1 et 0,1 | Clé étrangère facultative et unique, du côté où elle sera le plus souvent renseignée | document (num_document, …, #num_document_suite) |
| 1,1 et 1,1 | Le plus souvent, fusion des deux entités en une seule table | une personne et sa fiche de paie unique : une seule table |
La contrainte d'unicité est ce qui distingue une association un à un d'une association un à plusieurs : sans UNIQUE, rien n'empêcherait d'attribuer la même voiture à deux salariés.
Règle 5 : une association réflexive
On applique les règles 2 ou 3, en renommant les clés étrangères pour dire quel rôle elles jouent.
- Réflexive un à plusieurs : une clé étrangère dans la même table, qui référence sa propre clé primaire.
ADHERENT (représenté) 0,1 ──── REPRESENTER ──── 0,n ADHERENT (responsable)
adherent (num_adherent, nom_adherent, …, #num_responsable)
- num_responsable : clé étrangère en référence à num_adherent de adherent
- Réflexive plusieurs à plusieurs : une table à deux clés étrangères vers la même table, avec deux noms différents.
incompatibilite (#code_medicament1, #code_medicament2)
- code_medicament1 et code_medicament2 : clés étrangères en référence à code_medicament de medicament
Règle 6 : une association ternaire
Une association ternaire devient une table, avec une clé étrangère vers chacune des trois entités et ses éventuelles propriétés. Sa clé primaire est composée des trois clés étrangères.
ENSEIGNER (nb_heures) entre PROFESSEUR 1,n, MATIERE 1,n et CLASSE 1,n
enseigner (#num_professeur, #code_matiere, #code_classe, nb_heures)
Si l'association porte une CIF, la clé primaire se réduit aux identifiants qui forment la source de la CIF. Avec la règle « dans une classe, une matière a un seul professeur » :
enseigner (#code_classe, #code_matiere, #num_professeur, nb_heures)
Règle 7 : une entité faible (identifiant relatif)
La table de l'entité faible a une clé primaire composée de la clé étrangère vers l'entité forte et de son identifiant propre.
SALLE (1,1) ──── SITUER ──── 1,n BATIMENT
salle (#code_batiment, num_salle, capacite)
Règle 8 : l'héritage
Trois traductions sont possibles : une table par entité, une seule table avec une colonne de type, ou les tables spécialisées seules. Elles sont détaillées, avec leurs avantages, au chapitre 7.
Les cas particuliers
- Une propriété sur une association un à plusieurs. Elle ne devrait pas exister (chapitre 4) ; si tu en rencontres une, elle va dans la table qui reçoit la clé étrangère.
- L'entité DATE d'une association historisée ne donne pas de table : la date entre dans la clé primaire de la table d'association (chapitre 7).
- Plusieurs associations vers la même table donnent plusieurs clés étrangères, chacune avec un nom qui dit son rôle :
num_medecin_traitantetnum_medecin_prescripteur,id_demandeuretid_technicien. - Une association 0,1 — 0,n avec beaucoup de valeurs vides peut aussi devenir une table séparée, pour éviter une colonne presque toujours vide. C'est rare au BTS ; la clé étrangère facultative reste la réponse attendue.
Le MLD complet de Rivelire
En appliquant ces règles au MCD du chapitre 4, avec l'héritage traduit par une table par entité (solution 1 du chapitre 7) :
site (code_site, nom_site, adresse_site, ville_site)
genre (code_genre, libelle_genre)
document (num_document, titre, annee_parution, resume, #code_genre)
livre (#num_document, isbn, nb_pages)
film (#num_document, duree_min)
jeu (#num_document, plateforme, pegi)
contributeur (num_contributeur, nom_contributeur, prenom_contributeur)
contribuer (#num_contributeur, #num_document, role)
exemplaire (code_barre, date_acquisition, etat, #num_document, #code_site)
adherent (num_adherent, nom_adherent, prenom_adherent, date_naissance, email, date_inscription, #num_responsable)
emprunt (num_emprunt, date_emprunt, date_retour_prevue, date_retour, #num_adherent, #code_barre)
reserver (#num_adherent, #num_document, date_reservation)
Avec les clés étrangères :
- document.code_genre en référence à genre.code_genre ;
- livre.num_document, film.num_document et jeu.num_document en référence à document.num_document ;
- contribuer.num_contributeur en référence à contributeur.num_contributeur ; contribuer.num_document en référence à document.num_document ;
- exemplaire.num_document en référence à document.num_document ; exemplaire.code_site en référence à site.code_site ;
- adherent.num_responsable en référence à adherent.num_adherent ;
- emprunt.num_adherent en référence à adherent.num_adherent ; emprunt.code_barre en référence à exemplaire.code_barre ;
- reserver.num_adherent en référence à adherent.num_adherent ; reserver.num_document en référence à document.num_document.
Vérifie le compte : 7 entités, plus 3 entités spécialisées, plus 2 associations plusieurs à plusieurs (CONTRIBUER et RESERVER) donnent 12 tables. Les 5 associations un à plusieurs et la réflexive donnent 6 clés étrangères simples ; les 3 tables spécialisées en ont une chacune ; les 2 tables d'association en ont deux chacune : 13 clés étrangères en tout.
Vérifier un MLD
Avant de passer au SQL, relis ton MLD avec cette liste :
- chaque entité a donné une table, et chaque table a une clé primaire ;
- chaque association un à plusieurs a donné une clé étrangère du côté du maximum 1 ;
- chaque association plusieurs à plusieurs ou ternaire a donné une table, dont la clé primaire est composée de clés étrangères ;
- chaque clé étrangère référence une clé primaire qui existe, et porte un nom qui dit son rôle quand il y en a plusieurs vers la même table ;
- aucune propriété ne se retrouve dans deux tables, sauf les clés étrangères ;
- le nombre de tables est égal au nombre d'entités plus le nombre d'associations plusieurs à plusieurs et ternaires (en tenant compte de l'héritage).
Exercices
Exercice 1 sur 4 Une règle par association Réussi À revoir
Traduis chaque association en tables (n'écris que les colonnes utiles : clés et propriétés de l'association).
COMMANDE 1,1 ── PASSER ── 0,n CLIENTCOMMANDE 1,n ── CONTENIR (quantite) ── 0,n PRODUITEMPLOYE (encadré) 0,1 ── ENCADRER ── 0,n EMPLOYE (encadrant)TICKET 0,1 ── CONCERNER ── 0,n POSTECHAMBRE (1,1) ── APPARTENIR ── 1,n HOTELSALARIE 0,1 ── DISPOSER ── 1,1 BADGE
Voir la réponseMasquer la réponse de l'exercice 1
- commande (num_commande, …, #num_client), avec
num_clientobligatoire. - contenir (#num_commande, #ref_produit, quantite). Le minimum 1 côté COMMANDE (une commande contient au moins un produit) ne change pas la structure : il se vérifie dans l'application.
- employe (num_employe, …, #num_encadrant), avec
num_encadrantfacultatif, en référence ànum_employedeemploye. - ticket (num_ticket, …, #id_poste), avec
id_postefacultatif (NULLpour un ticket réseau). - chambre (#code_hotel, num_chambre, …) : clé primaire composée, car l'identifiant de CHAMBRE est relatif à HOTEL.
- badge (num_badge, …, #matricule), avec
matriculeobligatoire et unique : un badge appartient à un seul salarié, et un salarié a au plus un badge.
Tu avais trouvé ?
Exercice 2 sur 4 Trouver les erreurs d'un MLD Réussi À revoir
Voici le MLD proposé pour une bibliothèque scolaire, à partir du MCD : LIVRE 1,1 ── EDITER ── 0,n EDITEUR, LIVRE 0,n ── ECRIRE ── 1,n AUTEUR, ELEVE 1,1 ── APPARTENIR ── 0,n CLASSE. Trouve quatre erreurs.
- livre (isbn, titre, #code_editeur, #num_auteur)
- editeur (code_editeur, nom_editeur, #isbn)
- auteur (num_auteur, nom_auteur)
- eleve (num_eleve, nom_eleve)
- classe (code_classe, libelle, #num_eleve)
Voir la réponseMasquer la réponse de l'exercice 2
#isbndansediteurest du mauvais côté : un éditeur publie plusieurs livres. La clé étrangère#code_editeurdanslivresuffit ; il faut supprimer#isbnd'editeur.#num_auteurdanslivreest faux : ECRIRE est une association plusieurs à plusieurs (un livre a plusieurs auteurs). Il faut une table ecrire (#isbn, #num_auteur) et retirernum_auteurdelivre.#num_elevedansclasseest du mauvais côté : une classe a plusieurs élèves.- Il manque
#code_classedanseleve(un élève appartient à une seule classe).
Tu avais trouvé ?
Exercice 3 sur 4 Le MLD des stages Réussi À revoir
Traduis le MCD des stages construit aux chapitres 4 et 5 :
- ETUDIANT (num_etudiant, nom_etudiant, prenom_etudiant, email_etudiant), CLASSE (code_classe, libelle_classe), ENTREPRISE (siret, raison_sociale, ville), TUTEUR (num_tuteur, nom_tuteur, prenom_tuteur, tel_tuteur), STAGE (num_stage, date_debut, date_fin)
ETUDIANT 1,1 ── APPARTENIR ── 0,n CLASSE,STAGE 1,1 ── EFFECTUER ── 0,n ETUDIANT,STAGE 1,1 ── ENCADRER ── 0,n TUTEUR,TUTEUR 1,1 ── TRAVAILLER ── 0,n ENTREPRISE
Voir la réponseMasquer la réponse de l'exercice 3
Toutes les associations sont un à plusieurs : cinq tables, quatre clés étrangères, aucune table d'association.
- classe (code_classe, libelle_classe)
- etudiant (num_etudiant, nom_etudiant, prenom_etudiant, email_etudiant, #code_classe)
- entreprise (siret, raison_sociale, ville)
- tuteur (num_tuteur, nom_tuteur, prenom_tuteur, tel_tuteur, #siret)
- stage (num_stage, date_debut, date_fin, #num_etudiant, #num_tuteur)
Toutes les clés étrangères sont obligatoires (cardinalités 1,1). Pour connaître l'entreprise d'un stage, on joint stage, tuteur et entreprise.
Tu avais trouvé ?
Exercice 4 sur 4 Compter avant d'écrire Réussi À revoir
Le MCD d'un cabinet médical contient cinq entités (MEDECIN, PATIENT, ORDONNANCE, MEDICAMENT, ALLERGIE) et ces associations :
ORDONNANCE 1,1 ── REDIGER ── 1,n MEDECINORDONNANCE 1,1 ── CONCERNER ── 0,n PATIENTORDONNANCE 1,n ── CONTENIR (posologie, duree_jours) ── 0,n MEDICAMENTPATIENT 0,n ── ETRE_ALLERGIQUE ── 0,n ALLERGIEPATIENT 0,1 ── AVOIR_POUR_TRAITANT ── 0,n MEDECIN
Combien de tables et combien de clés étrangères le MLD contiendra-t-il ? Écris-le.
Voir la réponseMasquer la réponse de l'exercice 4
7 tables : les 5 entités, plus CONTENIR et ETRE_ALLERGIQUE (plusieurs à plusieurs). 7 clés étrangères : 2 dans ordonnance, 2 dans contenir, 2 dans etre_allergique, 1 dans patient.
- medecin (num_medecin, nom, prenom, num_rpps, specialite)
- patient (num_patient, nom, prenom, date_naissance, #num_medecin_traitant)
- ordonnance (num_ordonnance, date_ordonnance, #num_medecin, #num_patient)
- medicament (code_medicament, nom_medicament, dosage_mg)
- allergie (code_allergie, libelle)
- contenir (#num_ordonnance, #code_medicament, posologie, duree_jours)
- etre_allergique (#num_patient, #code_allergie)
num_medecin_traitant est facultatif (0,1) et porte un nom qui le distingue de num_medecin, le prescripteur.
Tu avais trouvé ?