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 :

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

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

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)

Cardinalité

La cardinalité indique combien de fois une entité peut participer à une association. Elle s'écrit (min, max) de chaque côté de l'association.

Lecture d'un MCD

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

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 à [relation] ... 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 de relations

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

Visualiser les trois types

Relations réflexives

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

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.

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.

Attribut de relation

La note n'appartient pas à l'étudiant seul (il a des notes différentes dans chaque cours), ni au cours seul. Elle existe uniquement dans le contexte de l'inscription.

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 :

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

Se traduit par :

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

Se traduit par :

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.

Se traduit par :

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

Se traduit par :

Tableau récapitulatif

Application sur l'exemple "École"

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

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

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

Dépendance fonctionnelle

On dit que B dépend fonctionnellement de A (noté A → B) si, pour chaque valeur de A, il existe une seule valeur possible de B.

Exemples :
idEtudiant → nom : un id d'étudiant détermine un seul nom
idProduit → prixUnitaire : un id de produit détermine un seul prix
ville → codePostal : une ville détermine un seul code postal (approximativement)

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

Première Forme Normale (1NF)

1NF, Règle

Chaque cellule contient une et une seule valeur atomique. Pas de listes, pas de groupes répétés dans une colonne.

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)

2NF, Règle

La table est en 1NF et chaque attribut non-clé dépend de la totalité de la clé primaire, pas d'une partie seulement (pas de dépendance partielle).

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)

3NF, Règle

La table est en 2NF et aucun attribut non-clé ne dépend d'un autre attribut non-clé (pas de dépendance transitive).

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

MLD

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.

Synthèse, La démarche de modélisation

1. Lire le texte : annoter noms (entités), verbes (relations), quantificateurs (cardinalités).
2. Construire le MCD : entités, attributs, associations, cardinalités.
3. Traduire en MLD : appliquer les règles selon le type de relation (1-1, 1-N, N-M).
4. Vérifier la normalisation : 1NF → 2NF → 3NF.
5. Implémenter en SQL : CREATE TABLE avec clés et contraintes.

Ressources complémentaires

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