Les modèles relationnels

Pourquoi modéliser ?

Avant d'écrire une seule ligne de SQL, il faut réfléchir à la structure des données : quelles informations doit-on stocker ? Comment sont-elles liées ? Quelles règles doivent être respectées ?

Construire une base de données sans modèle, c'est comme construire une maison sans plan d'architecte : on avance vite au début, puis tout s'effondre.

La méthode Merise propose une démarche en trois niveaux :

MCD : Modèle Conceptuel des Données

Les quatre composants fondamentaux

Un MCD est composé de quatre briques :

[Client] --(0,N)-- <Passe> --(1,1)-- [Commande]
 idClient                              idCommande
 nom                                   dateCommande
 email                                 montantTotal
  

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."

Les trois types de relations

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

-- Relation 1-1
[Personne] --(1,1)-- <Possede> --(0,1)-- [Passeport]

-- Relation 1-N
[Departement] --(1,N)-- <Contient> --(1,1)-- [Employe]
-- Relation N-M
[Etudiant] --(0,N)-- <Inscription> --(0,N)-- [Cours]
                          note
  

Relations réflexives

Une relation peut lier une entité à elle-même. On parle de relation réflexive (ou récursive).

-- Un employé peut superviser d'autres employés
[Employe] --(0,N)-- <Supervise> --(0,1)-- [Employe]
 idEmploye
 nom

-- Des personnes peuvent être amies entre elles
[Personne] --(0,N)-- <EstAmieDe> --(0,N)-- [Personne]
  

Traduction en MLD : une relation réflexive N-M génère une table de liaison avec deux clés étrangères pointant vers la même table, avec des noms de rôles différents.

Employe(idEmploye PK, nom, prenom)
Supervise(idSuperviseur FK -> Employe, idSubordonne FK -> Employe)

Les attributs d'association

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

[Etudiant] --(0,N)-- <Inscription> --(0,N)-- [Cours]
                          note          ← attribut de la relation
                          dateInscription
  

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

Une entité = quelque chose que le système doit mémoriser et à propos duquel on a plusieurs informations.

Étape 3 – Identifier les associations et cardinalités

Une association lie deux entités. Elle est souvent portée par un verbe de l'énoncé.

Pour fixer la cardinalité, posez 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

Étape 4 – Définir les attributs

Chaque entité possède des attributs : les informations à stocker pour chaque occurrence.

Règles à respecter :

• Les attributs doivent être atomiques (une seule valeur par cellule).
• L'identifiant (id...) doit être unique, numérique et sans signification métier — pas le numéro de sécurité sociale, ni l'email.
• Un même attribut ne doit pas apparaître dans plusieurs entités sans raison.

Exemple guidé pas à pas

É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 de relation). 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

É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 :

[Enseignant] --(1,N)-- <Dispense> --(1,1)-- [Cours] --(0,N)-- <Inscription> --(0,N)-- [Etudiant]
 idEnseignant                                idCours                 note               idEtudiant
 nom                                         intitule                                   nom
 prenom                                      volumeHoraire                              prenom
 specialite                                                                              dateNaissance

MLD : Modèle Logique des Données

Le MLD traduit le MCD en tables relationnelles, prêtes à être implémentées dans un SGBD.

Règles de passage MCD → MLD

Les règles sont systématiques selon le type de relation :

Règle 1 – Chaque entité devient une table

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

Règle 2 – Relation 1–1 : clé étrangère dans l'une des deux tables

On place la clé étrangère du côté de l'entité dont la cardinalité est (0,1) ou (1,1) à la même extrémité que la plus petite cardinalité.

[Personne] --(1,1)-- <Possede> --(0,1)-- [Passeport]
  → Passeport(idPasseport PK, ..., idPersonne FK -> Personne)

Règle 3 – Relation 1–N : clé étrangère dans la table "N"

La clé étrangère va dans la table du côté (N).

[Departement] --(1,N)-- <Contient> --(1,1)-- [Employe]
  → Employe(idEmploye PK, ..., idDepartement FK -> Departement)

Règle 4 – Relation N–M : table de liaison

La relation devient une nouvelle table avec les deux clés étrangères en clé primaire composite. Les attributs de la relation rejoignent cette table.

[Etudiant] --(0,N)-- <Inscription(note)> --(0,N)-- [Cours]
  → Inscription(idEtudiant PK FK, idCours PK FK, note)

Règle 5 – Relation réflexive N–M : table de liaison avec rôles

[Employe] --(0,N)-- <Supervise> --(0,1)-- [Employe]
  → Supervise(idSuperviseur PK FK -> Employe, idSubordonne PK FK -> Employe)

Tableau récapitulatif

Application sur l'exemple "École"

Enseignant(idEnseignant PK, nom, prenom, specialite)
Cours(idCours PK, intitule, volumeHoraire, idEnseignant FK -> Enseignant)
Etudiant(idEtudiant PK, nom, prenom, dateNaissance)
Inscription(idEtudiant PK FK -> Etudiant, idCours PK FK -> Cours, note)

Pourquoi idEnseignant va dans Cours (et non l'inverse) ?

La relation est 1–N : un enseignant enseigne plusieurs cours, un cours est enseigné par un seul enseignant. La clé étrangère va dans la table côté "1" de l'entité qui reçoit plusieurs : Cours porte idEnseignant.

Intégrité des données

Intégrité d'entité

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

• La clé primaire ne peut jamais être NULL.
• Deux lignes ne peuvent avoir la même valeur de clé primaire.

CREATE TABLE Etudiant (
  idEtudiant INT PRIMARY KEY AUTO_INCREMENT,  -- unique, jamais NULL
  nom VARCHAR(50) NOT NULL,
  prenom VARCHAR(50) NOT NULL
);

Intégrité référentielle

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

CREATE TABLE Inscription (
  idEtudiant INT,
  idCours    INT,
  FOREIGN KEY (idEtudiant) REFERENCES Etudiant(idEtudiant) ON DELETE CASCADE,
  FOREIGN KEY (idCours)    REFERENCES Cours(idCours)       ON DELETE RESTRICT
);

Intégrité de domaine

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

CREATE TABLE Produit (
  idProduit INT PRIMARY KEY AUTO_INCREMENT,
  nom       VARCHAR(100) NOT NULL,
  prix      DECIMAL(10,2) CHECK (prix >= 0),
  stock     INT DEFAULT 0,
  actif     BOOLEAN DEFAULT TRUE
);

Normalisation et Formes Normales

Pourquoi normaliser ?

Imaginez une table qui stocke toutes les informations d'une commande dans une seule ligne :

Problèmes :

• Si Alice déménage, il faut modifier toutes ses commandes → anomalie de mise à jour
• Si on supprime la commande 3, on perd le prix du Clavier → anomalie de suppression
• On ne peut pas ajouter un produit sans créer une commande → anomalie d'insertion

La normalisation élimine ces problèmes en décomposant les tables selon des règles précises.

Dépendances fonctionnelles

Les dépendances fonctionnelles sont le fondement des formes normales.

Première Forme Normale (1NF)

Avant (non 1NF) :

Après (1NF) :

Autre violation 1NF – colonnes répétées :

→ Même problème : on ne peut pas ajouter un 4e produit sans modifier la structure.

Deuxième Forme Normale (2NF)

La 2NF ne concerne que les tables avec une clé primaire composite.

Avant (non 2NF) — clé = (idCommande, idProduit) :

Problème : nomProduit et prixUnitaire dépendent seulement de idProduit, pas de (idCommande, idProduit). Si le prix du clavier change, il faut mettre à jour plusieurs lignes.

Après (2NF) — on sépare les attributs selon leur dépendance :

Troisième Forme Normale (3NF)

Avant (non 3NF) :

Problème : codePostal dépend de ville, pas de idClient directement. Chaîne : idClient → ville → codePostal → dépendance transitive

Après (3NF) :

Résumé des formes normales

Exemple complet : système scolaire avec tutorat

Entités et attributs

Relations et cardinalités

MCD

[Enseignant] --(0,N)-- <ResponsableDe> --(1,1)-- [Classe] --(1,N)-- <EstInscrit(annee)> --(1,1)-- [Eleve]
                                                                           ↑
                               [Enseignant] --(0,N)-- <Tutorat(role)> --(0,N)-- [Eleve]

MLD

Enseignant(idEnseignant PK, nom, prenom, dateEmbauche)
Classe(idClasse PK, nomClasse, idEnseignant FK -> Enseignant)
Eleve(idEleve PK, nom, prenom, dateNaissance)
EstInscrit(idEleve PK FK -> Eleve, idClasse PK FK -> Classe, annee PK)
Tutorat(idEnseignant PK FK -> Enseignant, idEleve PK FK -> Eleve, role)

Pourquoi annee est-il dans la clé primaire de EstInscrit ? Sans annee, un élève ne peut être inscrit qu'une seule fois dans une classe. Pour permettre le redoublement (même élève, même classe, année différente), on l'inclut dans la clé composite.

Ressources complémentaires

• Cours Merise (PDF)
• MCD et MLD en SQL (PDF)