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

Attribut

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

Association (Relation)

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

Client

idClient

nom

email

0,N

Passe

1,1

Commande

idCommande

dateCommande

montantTotal

Rectangle : entité, identifiant souligné. Losange : relation. Les couples min,max portés sur les pattes se lisent depuis l'entité la plus proche.

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

Regarder le losange autant que les rectangles. <Passe> ne porte ici aucun attribut : le fait qu'un client passe une commande n'a rien à mémoriser en propre. Et retenir la dissymétrie des deux cardinalités, car c'est elle qui décidera plus tard du placement de la clé étrangère.

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

Personne

0,1

Possede

1,1

Passeport

Departement

1,N

Contient

1,1

Employe

Etudiant

0,N

Inscription

note

0,N

Cours

Rectangle : entité, identifiant souligné. Losange : relation. Les couples min,max portés sur les pattes se lisent depuis l'entité la plus proche.

Comparer les trois lignes sur un seul point : le second nombre de chaque cardinalité, c'est-à-dire le maximum. Un 1 des deux côtés donne une relation 1-1, un N d'un seul côté une relation 1-N, un N des deux côtés une relation N-M. Ce coup d'œil suffit à décider de la traduction en tables, avant tout autre raisonnement.

Relations réflexives

Une relation peut lier une entité à elle-même. On parle de relation 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 relation 1-N, qui se traduit par une clé étrangère dans la table elle-même. EstAmieDe porte un N des deux côtés : c'est une relation N-M, qui exige une table de liaison.

Employe

idEmploye

nom

prenom

idSuperviseur→ Employe

EstAmieDe

idPersonne→ Personne

idAmie→ Personne

clé primaire clé étrangère, avec la table visée

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.

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

Etudiant

0,N

Inscription

note, dateInscription

0,N

Cours

Rectangle : entité, identifiant souligné. Losange : relation. Les couples min,max portés sur les pattes se lisent depuis l'entité la plus proche.

La note n'appartient pas à l'étudiant seul, puisqu'il en obtient une différente dans chaque cours. Elle n'appartient pas non plus au cours seul, puisqu'elle change d'un étudiant à l'autre. Elle n'existe que dans le contexte de l'inscription, et c'est pourquoi elle est écrite dans le losange et non dans un rectangle.

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

É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

L'énoncé contient un quatrième nom, « inscription », qui ne devient pourtant pas une entité. Le test de l'étape 2 le refuse : on ne dresse pas la liste des inscriptions indépendamment des étudiants et des cours, une inscription n'existant que par le couple qu'elle relie. C'est donc 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 :

Enseignant

idEnseignant

nom

prenom

specialite

1,N

Dispense

1,1

Cours

idCours

intitule

volumeHoraire

0,N

Inscription

note

0,N

Etudiant

idEtudiant

nom

prenom

dateNaissance

Rectangle : entité, identifiant souligné. Losange : relation. Les couples min,max portés sur les pattes se lisent depuis l'entité la plus proche.

Trois rectangles, deux losanges, et une seule chose à vérifier avant de passer à la suite : 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. Un quantificateur de l'énoncé 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.

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

Personne

0,1

Possede

1,1

Passeport

Rectangle : entité, identifiant souligné. Losange : relation. Les couples min,max portés sur les pattes se lisent depuis l'entité la plus proche.

Se traduit par :

Passeport

idPasseport

idPersonne→ Personne

clé primaire clé étrangère, avec la table visée

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.

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

« Table N » désigne celle dont il existe plusieurs lignes pour une ligne de l'autre, c'est-à-dire celle qui porte (1,1) dans le MCD. La cardinalité est le repère le plus sûr : la clé étrangère va du côté (1,1).

Departement

1,N

Contient

1,1

Employe

Rectangle : entité, identifiant souligné. Losange : relation. Les couples min,max portés sur les pattes se lisent depuis l'entité la plus proche.

Se traduit par :

Employe

idEmploye

idDepartement→ Departement

clé primaire clé étrangère, avec la table visée

Le placement se déduit d'une contrainte matérielle : une colonne ne contient qu'une valeur. Un employé n'appartenant qu'à un département, la colonne idDepartement suffit à retenir le lien. L'inverse demanderait de loger plusieurs identifiants d'employés dans une seule cellule de 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

Rectangle : entité, identifiant souligné. Losange : relation. Les couples min,max portés sur les pattes se lisent depuis l'entité la plus proche.

Se traduit par :

Inscription

idEtudiant→ Etudiant

idCours→ Cours

note

clé primaire clé étrangère, avec la table visée

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. La table Inscription existe donc pour le couple, et c'est elle qui accueille note, qui ne dépend ni de l'étudiant seul ni du cours seul.

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

Une relation réflexive suit les règles 2 à 4 comme si ses deux extrémités étaient deux entités distinctes. Seule s'ajoute l'obligation de nommer les rôles.

Personne

0,N

EstAmieDe

0,N

Personne

Rectangle : entité, identifiant souligné. Losange : relation. Les couples min,max portés sur les pattes se lisent depuis l'entité la plus proche.

Se traduit par :

EstAmieDe

idPersonne→ Personne

idAmie→ Personne

clé primaire clé étrangère, avec la table visée

Les deux colonnes pointent vers la même table Personne. Leur nom est la seule chose qui indique laquelle des deux tient quel rôle, et une relation réflexive 1-N comme Supervise suivrait la règle 3 : une colonne idSuperviseur ajoutée à Employe, sans table de liaison.

Tableau récapitulatif

Application sur l'exemple "École"

Enseignant

idEnseignant

nom

prenom

specialite

Cours

idCours

intitule

volumeHoraire

idEnseignant→ Enseignant

Etudiant

idEtudiant

nom

prenom

dateNaissance

Inscription

idEtudiant→ Etudiant

idCours→ Cours

note

clé primaire clé étrangère, avec la table visée

Relire ces quatre lignes en cherchant les clés étrangères : il y en a trois, et chacune est née d'un (1,1) du MCD. Inscription est la seule table qui n'existait dans aucun rectangle : elle vient de l'association N-M, et elle seule.

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

La relation est 1-N : un enseignant dispense plusieurs cours, un cours est dispensé par un seul enseignant. Une colonne idEnseignant dans Cours suffit donc à retenir le lien. Le choix inverse obligerait à loger plusieurs identifiants de cours dans une seule cellule d'Enseignant, ce que la première forme normale interdit.

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.

Ces trois règles portent des noms proches et se confondent facilement. Le plus simple est de retenir sur quoi chacune porte : la clé primaire, la clé étrangère, les valeurs ordinaires.

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.

1CREATE TABLE Etudiant (

2idEtudiant INT PRIMARY KEY AUTO_INCREMENT, -- unique, jamais NULL

3nom VARCHAR(50) NOT NULL,

4prenom VARCHAR(50) NOT NULL

5);

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.

Intégrité de domaine

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

1CREATE TABLE Produit (

2idProduit INT PRIMARY KEY AUTO_INCREMENT,

3nom VARCHAR(100) NOT NULL,

4prix DECIMAL(10,2) CHECK (prix >= 0),

5stock INT DEFAULT 0,

6actif BOOLEAN DEFAULT TRUE

7);

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.

Normalisation et Formes Normales

Pourquoi normaliser ?

Imaginer 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
idService → nomService : un identifiant de service détermine un seul intitulé

Le sens compte, et l'inverse est presque toujours faux. nom → idEtudiant n'est pas une dépendance fonctionnelle : deux étudiants peuvent porter le même nom. De même, ville → codePostal n'en est pas une, Paris comptant une vingtaine de codes postaux.

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

Première Forme Normale (1NF)

Une table de commandes range dans une colonne produits la valeur Clavier, Souris, Ecran. Rien n'interdit de l'écrire, et la table s'affiche parfaitement. Compter les ventes de souris devient pourtant impossible : le contenu de la cellule est une chaîne de caractères, pas trois produits. C'est cette situation que la première forme normale interdit.

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

La figure suivante fait le diagnostic sur les données elles-mêmes.

Cliquer sur une dépendance pour regrouper le tableau par sa partie gauche.

idCommande → client, produits

Vérifiée sur les 3 lignes : la gauche est exactement la clé : c'est la situation normale.

	idCommande	client	produits
1	1	Durand	Clavier, Souris, Ecran
2	2	Leroy	Clavier
3	3	Moreau	Souris, Ecran

Les lignes sont regroupées par idCommande (3 groupes). Dans chacun, client, produits garde la même valeur : la dépendance tient.

Forme normale atteinte : aucune, la 1NF n'est pas satisfaite

• 1NFla colonne produits contient une liste (« Clavier, Souris, Ecran »). Une cellule doit porter une seule valeur.

Noter ce que la figure ne dit pas : la dépendance idCommande → client, produits est vérifiée sans réserve. Une table peut donc satisfaire toutes ses dépendances et rester inutilisable. Tant qu'une cellule contient une liste, on ne peut ni chercher un produit, ni le compter, ni joindre sur lui.

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)

Le prix du clavier change. Dans une table qui garde ensemble idCommande, idProduit, nomProduit et prixUnitaire, il faut le corriger sur autant de lignes qu'il y a eu de commandes contenant un clavier. En oublier une suffit pour que la base contienne deux prix différents pour le même produit, sans qu'aucune règle ne s'en aperçoive. La deuxième forme normale vise exactement cette situation.

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.

Les deux dépendances de cette table n'ont pas la même gauche, et c'est exactement le symptôme. Cliquer de l'une à l'autre sur la figure : le regroupement change, et l'on voit la ligne Clavier 40 s'écrire deux fois.

Cliquer sur une dépendance pour regrouper le tableau par sa partie gauche.

idProduit → nomProduit, prixUnitaireidCommande, idProduit → quantite

Vérifiée sur les 3 lignes : la gauche est une PARTIE de la clé seulement, ce qui viole la 2NF.

	idCommande	idProduit	nomProduit	prixUnitaire	quantite
1	10	P01	Clavier	40	2
3	11	P01	Clavier	40	3
2	10	P02	Souris	15	1

Les lignes sont regroupées par idProduit (2 groupes). Dans chacun, nomProduit, prixUnitaire garde la même valeur : la dépendance tient.

Forme normale atteinte : 1NF

• 2NFnomProduit, prixUnitaire ne dépend que de idProduit, c'est-à-dire d'une partie de la clé (idCommande, idProduit). La valeur est donc réécrite à chaque ligne qui partage cette partie.

La décomposition qui règle le problème :

• Vente (idCommande, idProduit, quantite)
• Produit (idProduit, nomProduit, prixUnitaire)

Les attributs soulignés forment la clé primaire de chaque table.

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

Compter les occurrences de Clavier avant et après : deux dans la table de départ, une seule maintenant. Le prix du clavier ne s'écrit plus qu'à un seul endroit, donc il ne peut plus se contredire. En échange, connaître le nom du produit d'une commande demande désormais une jointure : c'est le prix de la normalisation, et il est presque toujours bon marché.

Troisième Forme Normale (3NF)

Le service Etudes déménage du bâtiment B au bâtiment C. Dans une table d'employés qui porte à la fois idService, nomService et batiment, ce déménagement coûte autant de modifications qu'il y a d'employés aux Etudes, et une seule ligne oubliée rend la base incohérente. Pire : tant qu'aucun employé n'y est affecté, le service n'a nulle part où exister. La troisième forme normale vise ce défaut-là.

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 : nomService et batiment ne dépendent pas de idEmploye, mais de idService, qui n'est lui-même pas la clé. La chaîne est idEmploye → idService → nomService : c'est une dépendance transitive.

La figure ci-dessous met les deux dépendances côte à côte, et c'est leur partie gauche qu'il faut comparer.

Cliquer sur une dépendance pour regrouper le tableau par sa partie gauche.

idEmploye → nom, idServiceidService → nomService, batiment

Vérifiée sur les 3 lignes : la gauche est exactement la clé : c'est la situation normale.

	idEmploye	nom	idService	nomService	batiment
1	1	Girard	S1	Etudes	B
2	2	Lemoine	S2	Support	A
3	3	Barbier	S1	Etudes	B

Les lignes sont regroupées par idEmploye (3 groupes). Dans chacun, nom, idService garde la même valeur : la dépendance tient.

Forme normale atteinte : 2NF

• 3NFnomService, batiment dépend de idService, qui n'est pas la clé. La chaîne idEmploye → idService → nomService, batiment est une dépendance transitive.

La décomposition qui règle le problème :

• Employe (idEmploye, nom, idService)
• Service (idService, nomService, batiment)

Les attributs soulignés forment la clé primaire de chaque table.

Après (3NF) :

Le déménagement ne touche plus qu'une ligne, et un service vide s'enregistre sans difficulté.

Une fois la décomposition faite, chaque table ne porte plus qu'une seule dépendance, et celle-ci a la clé pour gauche.

Cliquer sur une dépendance pour regrouper le tableau par sa partie gauche.

idService → nomService, batiment

Vérifiée sur les 2 lignes : la gauche est exactement la clé : c'est la situation normale.

	idService	nomService	batiment
1	S1	Etudes	B
2	S2	Support	A

Les lignes sont regroupées par idService (2 groupes). Dans chacun, nomService, batiment garde la même valeur : la dépendance tient.

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

Rectangle : entité, identifiant souligné. Losange : relation. Les couples min,max portés sur les pattes se lisent depuis l'entité la plus proche.

Remarquer que Enseignant apparaît sur les deux lignes, reliée deux fois à la même chaîne d'entités. Ce n'est pas une erreur de découpage : un enseignant responsable d'une classe et un enseignant tuteur d'un élève sont deux liens de nature différente, l'un passant par la classe et l'autre non. Les fusionner ferait perdre le tutorat des élèves dont l'enseignant n'est pas responsable de la classe.

MLD

Enseignant

idEnseignant

nom

prenom

dateEmbauche

Classe

idClasse

nomClasse

idEnseignant→ Enseignant

Eleve

idEleve

nom

prenom

dateNaissance

EstInscrit

idEleve→ Eleve

idClasse→ Classe

annee

Tutorat

idEnseignant→ Enseignant

idEleve→ Eleve

role

clé primaire clé étrangère, avec la table visée

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.

La méthode

De l'énoncé en français aux tables, sans relire le chapitre.

1. Relever tous les noms communs de l'énoncé et en faire une liste de candidats. Écarter aussitôt ceux dont une seule information est demandée : ce sont des attributs, pas des entités.
2. Retenir comme entité tout candidat dont on peut dresser la liste et sur lequel plusieurs informations sont à mémoriser. Donner à chacune un identifiant artificiel (idClient, idCours), jamais une adresse électronique ni un numéro officiel.
3. Relever les verbes qui relient deux entités et en faire les associations, nommées par ce verbe : Passe, Dispense, Inscription.
4. Fixer chaque cardinalité en interrogeant une entité à la fois : « une occurrence de cette entité participe au minimum à combien d'associations, au maximum à combien ? ». Écrire (min, max) du côté de cette entité, et faire de même en face.
5. Placer les attributs restants, chacun sur une seule entité. Si un attribut n'a de sens que lorsque les deux entités sont liées, le poser sur l'association.
6. Traduire en tables : une table par entité, puis regarder les maxima de chaque association. Un N d'un seul côté donne une clé étrangère dans la table d'en face, celle qui porte (1,1). Un N des deux côtés donne une table de liaison, avec les deux clés étrangères en clé primaire composite et les attributs de l'association dedans.
7. Passer chaque table au test des trois formes normales, dans l'ordre : une seule valeur par cellule, aucun attribut qui ne dépende que d'une partie de la clé, aucun attribut qui dépende d'un autre attribut non-clé.
8. Relire le modèle en cherchant une valeur écrite deux fois. S'il en reste une qui ne soit pas une clé étrangère, une décomposition a été manquée.

Synthèse

• Modéliser d'abord parce qu'une requête se réécrit à tout moment, alors qu'une table déjà remplie ne se redécoupe qu'au prix d'une migration.
• La méthode Merise procède en trois niveaux : MCD (le quoi), MLD (les tables), MPD (le SQL réel). Le MCD s'écrit sans penser au SGBD.
• Dans un énoncé, les noms donnent les entités, les verbes les associations, les quantificateurs les cardinalités.
• Une cardinalité s'écrit (min, max) du côté de l'entité et se lit « une occurrence de cette entité participe entre min et max fois à l'association ».
• Un attribut qui n'a aucun sens hors du lien appartient à l'association, pas aux entités : il finira dans la table de liaison.
• Le passage au MLD ne dépend que des maxima : un N d'un seul côté donne une clé étrangère du côté (1,1), un N des deux côtés une table de liaison à clé composite.
• Une relation réflexive suit exactement les mêmes règles ; s'ajoute seulement l'obligation de nommer les deux rôles, faute de quoi les colonnes deviennent indiscernables.
• Trois intégrités se distinguent par ce sur quoi elles portent. D'entité pour la clé primaire : jamais nulle, jamais répétée. Référentielle pour la clé étrangère : elle désigne une ligne qui existe. De domaine pour les valeurs ordinaires : type, NOT NULL, CHECK, UNIQUE, DEFAULT.
• ON DELETE CASCADE propage la suppression aux lignes dépendantes, ON DELETE RESTRICT la refuse tant qu'il en reste une.
• Les trois formes normales se testent dans l'ordre et suppriment chacune une famille d'anomalies : valeurs atomiques (1NF), aucune dépendance à une partie de la clé (2NF), aucune dépendance entre attributs non-clés (3NF).

Quiz

1.Pourquoi modéliser avant d'écrire la moindre table ?

Parce qu'une requête se réécrit, mais qu'une table qui mélange deux notions se rattrape difficilementParce que le modèle sert uniquement à documenter le travail finiParce que le SGBD refuse une table créée sans modèle préalable

2.Comment traduit-on une association N-M lors du passage du MCD au MLD ?

Par la fusion des deux entités en une seule tablePar une clé étrangère placée du côté NPar une table de liaison portant les deux clés étrangères en clé primaire composite

3.Où place-t-on les attributs d'une association N-M, comme une note ou une quantité ?

Dans la table du côté NDans les deux tables, pour y accéder plus viteDans la table de liaison, puisqu'ils dépendent du couple et non de l'un des deux côtés

4.Que garantit l'intégrité référentielle ?

Que chaque colonne respecte son type et ses bornesQu'une clé étrangère désigne toujours une ligne qui existe réellementQue chaque table possède une clé primaire

5.Une colonne produits contient « Clavier, Souris, Ecran ». Quelle forme normale est violée ?

La 3NF, à cause d'une dépendance transitiveLa 2NF, à cause d'une dépendance partielleLa 1NF, qui exige une seule valeur atomique par cellule

6.Dans une table de clé (idCommande, idProduit), la colonne prixUnitaire ne dépend que de idProduit. Quelle forme normale est violée ?

La 2NF, qui interdit une dépendance à une partie seulement de la cléLa 3NF, puisque le prix est un attribut non-cléLa 1NF, puisque la clé est composite

7.idEmploye → idService → nomService. Quel est le problème ?

Une violation de la 1NF, la table ayant trop de colonnesAucun, la chaîne est parfaitement normaleUne dépendance transitive : nomService ne dépend pas de la clé mais d'un autre attribut non-clé

8.Une cardinalité 0,N du côté d'une entité signifie :

que l'entité peut être absente de la baseque la relation est facultative des deux côtésqu'une occurrence de cette entité peut n'être liée à aucune occurrence de l'autre

Ressources complémentaires

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