Du MCD au modèle relationnel

Pourquoi modéliser ?

La méthode Merise modélise en trois niveaux, du plus proche du métier au plus proche de la machine :

Le MCD décrit le monde réel, ses objets et leurs liens, sans penser à SQL : la technique n'arrive qu'avec le MLD. Le SGBD (système de gestion de base de données) est le logiciel qui stocke la base et exécute les requêtes : MySQL, par exemple, dont l'installation sur une machine a son propre chapitre.

MCD : Modèle Conceptuel des Données

Les quatre composants fondamentaux

Un MCD est composé de quatre briques.

Entité

Une entité représente un objet ou un acteur du monde réel que le système doit mémoriser. Elle est représentée par un rectangle.

Critère : si on peut en dresser une liste et stocker des informations dessus, c'est probablement une entité.

Attribut

Un attribut est une propriété d'une entité. C'est une information simple et atomique (une seule valeur).

Association

Une association exprime un lien entre deux entités (ou plus). Elle est représentée par un losange.

Exemples : Passe (entre Client et Commande), Enseigne (entre Enseignant et Cours), Habite (entre Personne et Ville)

Le mot « relation » est à éviter ici, même si on l'entend souvent : dans le modèle relationnel, et dans le chapitre sur l'algèbre relationnelle, une relation est une table. Merise dit association pour le losange.

Cardinalité

La cardinalité indique combien de fois une occurrence de l'entité, c'est-à-dire un client ou une commande en particulier, peut participer à une association. Elle s'écrit (min, max) de chaque côté de l'association.

Un premier MCD

Lecture : un client peut passer 0 à N commandes. Une commande est passée par exactement 1 client.

La dissymétrie des deux cardinalités décidera plus loin de la façon de relier les deux tables.

Les cardinalités en détail

Les cardinalités se lisent toujours du côté de l'entité vers l'association : « cette entité participe à cette association au minimum X fois et au maximum Y fois. »

Méthode : « Un(e) [entité A] peut/doit participer à [association] ... fois. »

Pour [Auteur] --(1,N)-- <Ecrit> --(1,1)-- [Livre] :
→ Un auteur écrit au moins 1 livre (1,N)
→ Un livre est écrit par exactement 1 auteur (1,1)

Les trois types d'associations

La combinaison des cardinalités de chaque côté détermine le type d'association :

La figure suivante dessine un exemple de chaque type.

Comparer les trois lignes sur le seul second nombre de chaque cardinalité, le maximum : un 1 des deux côtés, un N d'un seul côté, un N des deux côtés. Ce coup d'œil suffit à décider de la traduction en tables.

Associations réflexives

Une association peut lier une entité à elle-même. On parle d'association réflexive (ou récursive). La difficulté qu'elle pose n'est pas le dessin mais le vocabulaire : les deux extrémités portant le même nom d'entité, il faut nommer les deux rôles pour les distinguer.

Lire les maxima comme pour deux entités distinctes, le fait qu'elles soient identiques n'y change rien. Supervise porte un N d'un seul côté : c'est une association 1-N. EstAmieDe porte un N des deux côtés : c'est une association N-M. Leur traduction en tables vient avec la règle 5, plus bas.

Les attributs d'association

Certains attributs n'appartiennent ni à une entité ni à l'autre, mais à l'association elle-même. Ils décrivent un fait qui n'existe que lorsque les deux entités sont liées.

La note n'appartient pas à l'étudiant seul, qui en obtient une par cours, ni au cours seul, où elle change d'un étudiant à l'autre : elle est écrite dans le losange. Le test : cet attribut a-t-il un sens sans l'association ? Si non, il lui appartient, sous la condition qui suit.

Construire un MCD à partir d'un énoncé

Construire un MCD depuis un texte suit une démarche méthodique en quatre étapes.

Étape 1, Lire et annoter le texte

Chercher :

• Les noms → potentielles entités (personnes, objets, lieux, événements)
• Les verbes → potentielles associations
• Les quantificateurs (« un seul », « plusieurs », « au moins un ») → cardinalités

Étape 2, Identifier les entités

Étape 3, Identifier les associations et cardinalités

Pour fixer la cardinalité, poser la question dans les deux sens :

« Un client peut passer combien de commandes ? » → (0,N) côté Client

« Une commande appartient à combien de clients ? » → (1,1) côté Commande

Les quantificateurs de l'énoncé se traduisent comme dans le tableau des cardinalités : « un seul » donne (1,1), « au moins un » (1,N), « plusieurs » (0,N), « au plus un » (0,1).

Étape 4, Définir les attributs

Règles à respecter :

• Les attributs doivent être atomiques (une seule valeur par cellule).
• Chaque entité a un identifiant (id...) unique et sans signification métier, le plus souvent un entier que la base attribue : pas le numéro de sécurité sociale, ni l'email.

Le type de chaque attribut (un texte VARCHAR, une date DATE, un nombre à virgule DECIMAL) ne se décide qu'au niveau physique, avec le SQL du chapitre sur les requêtes. La frontière entre entité et attribut se règle au même critère qu'à l'étape 2 : Ville reste un attribut tant qu'on n'en garde que le nom, et devient une entité dès qu'on stocke sa population ou sa région.

Exemple guidé pas à pas

La scolarité de l'ouverture commence par décrire son école en trois phrases, sans penser aux tables.

Étape 1, Annotation :

« Une école gère ses enseignants (entité), ses étudiants (entité), et les cours (entité) proposés. Chaque étudiant peut s'inscrire (association) à plusieurs (0,N) cours. Chaque inscription donne lieu à une note (attribut d'association). Chaque cours est dispensé (association) par un seul (1,1) enseignant, mais un enseignant peut enseigner plusieurs (0,N) cours. »

Étape 2, Entités : Etudiant, Cours, Enseignant

Un autre nom de l'énoncé, « inscription », ne devient pas une entité : une inscription n'existe que par le couple étudiant et cours qu'elle relie, et ne se liste pas pour elle-même. C'est une association.

Étape 3, Associations et cardinalités :

Étape 4, Attributs :

• Etudiant : idEtudiant, nom, prenom, dateNaissance
• Cours : idCours, intitule, volumeHoraire
• Enseignant : idEnseignant, nom, prenom, specialite

MCD final :

Reste à vérifier que chaque quantificateur de l'énoncé se retrouve dans une cardinalité : « plusieurs cours » a donné le (0,N) côté Étudiant, « un seul enseignant » le (1,1) côté Cours, « peut enseigner plusieurs cours » le (0,N) côté Enseignant. Un quantificateur sans cardinalité correspondante signale une contrainte oubliée.

MLD : Modèle Logique des Données

Le MLD traduit le MCD en tables relationnelles, prêtes à être implémentées dans un SGBD. L'identifiant de chaque entité y devient la clé primaire de sa table, notée PK (de l'anglais primary key), et une ligne en désigne une autre par une clé étrangère, notée FK (foreign key) : une colonne qui recopie la clé primaire de la ligne visée. Dans l'écriture Cours(idCours PK, idEnseignant FK -> Enseignant), la flèche se lit « désigne une ligne de » : chaque cours désigne une ligne de la table Enseignant.

Règles de passage MCD → MLD

Les règles sont systématiques selon le type d'association.

Règle 1, Chaque entité devient une table

Entité [Etudiant] (idEtudiant, nom, prenom)
  → Table Etudiant(idEtudiant PK, nom, prenom)

Règle 2, Association 1-1 : clé étrangère dans une seule des deux tables

Une seule des deux tables porte la clé étrangère, et le choix n'est pas indifférent. La placer du côté dont la participation est obligatoire, celui qui porte (1,1).

Se traduit par :

Tout passeport a un titulaire, mais toute personne n'a pas de passeport. C'est donc Passeport qui porte idPersonne, et cette colonne est alors renseignée sur chaque ligne. Le choix inverse aurait créé dans Personne une colonne idPasseport vide pour la majorité des lignes.

Le dessin ne montre pas une précaution : pour qu'une personne n'ait pas deux passeports, la colonne idPersonne doit être unique dans Passeport, sans quoi l'association 1-1 deviendrait une 1-N sans que personne ne l'ait décidé. En SQL : idPersonne INT NOT NULL UNIQUE, avec sa clause FOREIGN KEY.

Les deux autres associations 1-1 du tableau des types se tranchent autrement. Avec (0,1) des deux côtés, aucun côté n'est toujours rempli : la clé étrangère va dans l'une ou l'autre table, unique, et vide quand le lien manque. Avec (1,1) des deux côtés, les deux entités vont toujours ensemble, et leurs colonnes se rangent le plus souvent dans une seule table.

Règle 3, Association 1-N : clé étrangère du côté dont le maximum vaut 1

En Merise, le N s'écrit à côté de la table qui ne reçoit rien, ici Departement. Le repère sûr est le maximum qui vaut 1 : la clé étrangère va du côté (1,1) ou (0,1).

Se traduit par :

Une colonne ne contient qu'une valeur : un employé n'appartenant qu'à un département, idDepartement suffit à retenir le lien, quand l'inverse logerait plusieurs identifiants d'employés dans une seule cellule de Departement.

Règle 4, Association N-M : table de liaison

L'association devient une nouvelle table avec les deux clés étrangères en clé primaire composite, c'est-à-dire faite de deux colonnes : aucune des deux ne suffit seule, c'est le couple qui ne se répète jamais. Les attributs de l'association rejoignent cette table.

Se traduit par :

Aucune des deux tables de départ ne pouvait porter l'information, pour la raison inverse de la règle 3 : les deux côtés admettent plusieurs partenaires. Inscription existe donc pour le couple, et note l'y suit.

Règle 5, Association réflexive : nommer les deux rôles

Une association réflexive suit les règles 2 à 4 comme si ses deux extrémités étaient deux entités distinctes, avec ses deux noms de rôles. Supervise, une 1-N, pose une clé étrangère dans la table elle-même ; EstAmieDe, une N-M, demande une table de liaison :

Dans les deux lignes, ce sont les noms de rôles qui portent le sens. idSuperviseur et idAmie désignent la même table que la colonne voisine : sans ces noms, deux clés étrangères identiques rendraient le modèle indéchiffrable. Une amitié étant réciproque, on évite de l'écrire deux fois, (1, 2) puis (2, 1), en imposant un ordre par une contrainte de vérification, que la base contrôle à chaque ligne écrite : CHECK (idPersonne < idAmie).

Règle 6, Association à trois pattes ou plus : une table, puis une clé à réduire

Certains faits relient trois entités à la fois : « tel enseignant fait cours dans telle salle à tel créneau ». Aucune paire n'y suffit, et l'association se traduit comme une N-M : une table qui reçoit les trois identifiants. Reste à trouver sa clé, en cherchant ce qu'une règle de gestion, c'est-à-dire une règle que l'organisation s'impose, détermine. Si une salle n'accueille qu'un enseignant à un créneau donné, la salle et le créneau déterminent l'enseignant, (idSalle, idCreneau) → idEnseignant, et la clé se réduit à ce couple :

Règle 7, Héritage : une entité qui en précise une autre

Un enseignant et un élève sont deux sortes de personnes : ils partagent un nom et un prénom, et ont chacun leurs propres attributs. La traduction la plus sûre donne une table au parent et une table à chaque sorte, dont la clé est aussi une clé étrangère vers le parent :

Tableau récapitulatif

Le mot « côté N » revient dans beaucoup de cours, et c'est lui qui fait placer la clé du mauvais côté.

Application sur l'exemple « École »

Relire ces quatre lignes en cherchant les clés étrangères : il y en a trois. Celle de Cours est née du (1,1) de Dispense, une 1-N. Les deux d'Inscription viennent de l'association N-M, la seule à avoir créé une table : Inscription n'existait dans aucun rectangle.

C'est la première moitié de la réponse à la question de l'ouverture. La scolarité a besoin de quatre tables, et la note se range dans Inscription, la seule dont chaque ligne réunit un étudiant et un cours. Le nom d'une enseignante n'est plus écrit qu'une fois, dans Enseignant, et celui d'un étudiant une fois, dans Etudiant : les deux premiers accidents du tableur n'ont plus où se produire.

Compter les tables avant de les écrire

Les règles de passage se vérifient sur un modèle qui n'est pas dessiné. Un centre de formation décrit le sien en quatre phrases. Un formateur anime des sessions, et chaque session est animée par un seul formateur. Chaque session se tient dans une seule salle, qui en accueille beaucoup. Un stagiaire s'inscrit à autant de sessions qu'il veut, chaque session réunit plusieurs stagiaires, et chaque inscription reçoit une note. Un formateur maîtrise plusieurs compétences, que plusieurs formateurs partagent.

Intégrité des données

Définition : L'intégrité désigne l'ensemble des règles qui garantissent que les données restent valides, cohérentes et fiables au fil du temps, malgré les insertions, modifications et suppressions.

Intégrité d'entité

Chaque enregistrement doit être identifiable de manière unique.

• La clé primaire ne peut jamais être NULL, la marque d'une valeur absente.
• Deux lignes ne peuvent avoir la même valeur de clé primaire.

L'instruction SQL CREATE TABLE crée une table colonne par colonne, avec le type de chacune : INT pour un entier, VARCHAR(50) pour un texte de 50 caractères au plus. Les contraintes s'écrivent à côté de leur colonne :

AUTO_INCREMENT laisse MySQL numéroter lui-même les lignes, et NOT NULL rend une colonne obligatoire. Dans la page, l'identifiant s'écrit INTEGER PRIMARY KEY, qui se numérote seul.

Intégrité référentielle

Une clé étrangère doit toujours pointer vers une valeur qui existe dans la table référencée.

Les deux clauses de cette table ne se choisissent pas au hasard. Une inscription n'a aucun sens sans son étudiant, elle peut donc disparaître avec lui : CASCADE. Un cours existe indépendamment de ceux qui s'y inscrivent, et le supprimer alors qu'il reste des inscriptions détruirait de l'information : RESTRICT bloque l'opération et oblige à trancher. Une clé étrangère déclarée sans clause ON DELETE se comporte de la même façon : MySQL refuse la suppression, et l'absence de règle n'est pas une permission.

C'est la seconde moitié de la réponse à l'ouverture : le cours qui a encore des inscrits ne se supprime plus, et ses notes ne disparaissent plus avec lui. La scolarité doit d'abord décider de leur sort.

La base de démonstration de la page contient cette table Inscription avec ces deux clauses, et son moteur vérifie les clés étrangères comme MySQL :

Une troisième règle garde la ligne dépendante et efface seulement le lien : ON DELETE SET NULL. Un produit dont la famille disparaît reste au catalogue, sans famille, jusqu'à ce qu'on lui en donne une autre.

La colonne doit accepter NULL : déclarée NOT NULL, elle contredirait la règle, et la page refuserait la suppression de la famille.

Intégrité de domaine

Chaque colonne doit respecter un type et des contraintes de valeur.

Les contraintes d'intégrité empêchent d'écrire des valeurs fausses. Elles ne disent rien d'une base qui écrit dix fois la même valeur juste : c'est l'objet de la normalisation, au chapitre suivant.

Écrire le MLD de l'école en SQL

Le MLD de l'école se traduit maintenant en tables, avec les clés et les clauses que la section précédente vient de poser.

Ressources complémentaires

• Looping, site officiel : le logiciel de modélisation Merise du chapitre d'installation, avec ses exemples de schémas entité-association
• Mocodo en ligne : dessine un MCD à partir d'un texte, et en déduit le modèle relationnel et le script SQL

Mettre en pratique