Module 1Concevoir une base de données
Chapitre 3 sur 9
Normaliser les données
Appliquer les formes normales et traduire le MCD en modèle logique de données (MLD).
- ≈ 105 min
- 3 leçons
- 1 vidéo
- 3 exercices
Module 1Concevoir une base de données
Chapitre 3 sur 9
Appliquer les formes normales et traduire le MCD en modèle logique de données (MLD).
Objectifs
Avec ce chapitre, tu sauras…
Leçon 1
Imagine que le centre de formation range toutes ses inscriptions dans une seule grande table :
numCINEtu | nomEtu | villeEtu | codeSess | codeForm | titreForm | prixForm |
|---|---|---|---|---|---|---|
| G683909 | Alaoui | Tanger | S01 | BD001 | Base de données | 2500 |
| G683909 | Alaoui | Tanger | S07 | CCP01 | C/C++ | 3000 |
| AB123456 | Hilali | Rabat | S01 | BD001 | Base de données | 2500 |
| Anomalie | Exemple |
|---|---|
| de mise à jour | si le prix de BD001 change, il faut le modifier sur toutes les lignes ; en oublier une rend la base incohérente |
| d'insertion | impossible d'enregistrer une nouvelle formation tant qu'aucun étudiant n'y est inscrit |
| de suppression | si Hilali se désinscrit et était le seul inscrit d'une formation, on perd aussi les informations de la formation |
Leçon 2
numCINEtu | nomEtu | prenoms |
|---|---|---|
| G683909 | Alaoui | Mohammed, Amine |
| AB123456 | Hilali | Nour, Laila |
Leçon 3
| Règle | Situation dans le MCD | Traduction dans le MLD |
|---|---|---|
| 1 | une entité | une table du même nom ; l'identifiant devient la clé primaire ; les propriétés deviennent des colonnes |
| 2 | une association binaire (0,N ou 1,N) – (1,1) | pas de nouvelle table : la clé primaire de l'entité côté N est copiée comme clé étrangère dans la table côté (1,1) |
| 3 |
1Une relation avec une case « Mohammed, Amine » viole…
2La 2FN concerne surtout les relations…
3A → B → C dans une même relation de clé A viole…
4Une association (0,N) – (1,1) devient…
5Une association (0,N) – (0,N) devient…
6Dans le MLD, #codeForm désigne…
L'essentiel du chapitre
NOM (clé, attributs, #cléÉtrangère).2FN
| Modèle | Couleur | Version |
|---|---|---|
| 715 | Gris | Enjoy |
| 715 | Gris | Excellence |
| 715 | Blanc | Enjoy |
| 715 | Blanc | Excellence |
| une association binaire (…,N) – (…,N) |
| une nouvelle table ; sa clé primaire est la combinaison des deux clés étrangères ; les propriétés de l'association deviennent ses colonnes |
| 4 | une association ternaire (ou n-aire) | une nouvelle table dont la clé primaire est la combinaison des clés des entités reliées |
Vidéo · À regarder
ENMySQL Database - Full Course
freeCodeCamp.org · YouTube
Cette table contient des redondances (le nom et la ville d'Alaoui, le titre et le prix de BD001 sont répétés), qui provoquent des anomalies :
La normalisation consiste à décomposer les relations (les tables) en relations plus petites, sans perdre d'information, en s'appuyant sur les dépendances fonctionnelles. Elle élimine les redondances et facilite la maintenance des données. On vérifie des formes normales successives : chacune suppose que la précédente est respectée.
NOM (attribut1, attribut2, …) ;PK dans les schémas) ;# ;Première forme normale (1FN) : des attributs atomiques
Une relation est en 1FN si tous ses attributs sont atomiques (une seule valeur par case) et dépendent fonctionnellement de la clé.
L'attribut prenoms contient plusieurs valeurs : la relation n'est pas en 1FN. On ne peut ni trier ni chercher correctement par prénom.
ÉTUDIANT (numCINEtu, nomEtu, prenom1, prenom2) ;TELEPHONE (numTel, #numCli). C'est le cas de l'exemple du résumé FORMATION (codeForm, titreForm, codeSpec) où une formation a plusieurs spécialités : codeSpec ne dépend pas de codeForm, il faut une relation à part.Deuxième forme normale (2FN) : dépendre de toute la clé
Le MLD (ou modèle relationnel) représente la base sous forme de tables reliées par des clés étrangères. Il est obtenu à partir du MCD par des règles de passage et il est adapté aux SGBD relationnels (MySQL, PostgreSQL, Oracle…).
Traduis ton MCD de la bibliothèque (TP 2.4) en MLD, en appliquant les règles de passage. Vérifie que chaque table est en 3FN et dessine le schéma des liens entre clés étrangères et clés primaires.
La table COMMANDE (numCmd, dateCmd, numCli, nomCli, villeCli, codeProd, libelleProd, prixUnitaire, quantite) a pour clé (numCmd, codeProd). Une commande est passée par un seul client et contient plusieurs produits. Indique pour chaque forme normale (1FN, 2FN, 3FN) si elle est respectée, puis donne le schéma relationnel normalisé en 3FN.
Traduis en MLD le MCD de l'agence de location (corrigé 2.3) : CLIENT, LOCATION, VOITURE, AGENCE et leurs trois associations. Précise les clés primaires et étrangères.
Une relation est en 2FN si elle est en 1FN et si chaque attribut non clé dépend de la totalité de la clé primaire, et non d'une partie de celle-ci. La 2FN ne concerne que les relations à clé composée.
INSCRIPTION (numCINEtu, codeSess, nomEtu, villeEtu, typeCours) avec la clé (numCINEtu, codeSess) n'est pas en 2FN : nomEtu et villeEtu dépendent seulement de numCINEtu, une partie de la clé. On décompose :
ÉTUDIANT (numCINEtu PK, nomEtu, villeEtu)
INSCRIPTION (#numCINEtu PK, #codeSess PK, typeCours) typeCours dépend bien des deuxTroisième forme normale (3FN) : pas de dépendance entre attributs non clés
Une relation est en 3FN si elle est en 2FN et si chaque attribut non clé dépend directement de la clé, et non d'un autre attribut non clé (pas de dépendance transitive).
FORMATION (codeForm, titreForm, codeSpecialite, nomSpecialite) n'est pas en 3FN : nomSpecialite dépend de codeSpecialite, qui n'est pas la clé (codeForm → codeSpecialite → nomSpecialite). On décompose :
FORMATION (codeForm PK, titreForm, #codeSpecialite)
SPÉCIALITÉ (codeSpecialite PK, nomSpecialite)Chaque attribut non clé doit dépendre de la clé (1FN), de toute la clé (2FN) et de rien d'autre que la clé (3FN).
Forme normale de Boyce-Codd (FNBC)
Une relation est en FNBC si elle est en 3FN et si toute dépendance fonctionnelle a pour source une clé de la relation. Autrement dit, un attribut ne peut pas déterminer une partie de la clé.
Exemple classique : COURS (étudiant, matière, professeur), avec la clé (étudiant, matière) et la règle « chaque professeur n'enseigne qu'une seule matière » : professeur → matière. La relation est en 3FN (aucun attribut non clé ne dépend d'un autre attribut non clé), mais pas en FNBC, car professeur, qui n'est pas une clé, détermine matière, qui fait partie de la clé. On décompose en ENSEIGNE (professeur, matière) et SUIT (étudiant, professeur).
Le résumé illustre la FNBC avec Commune (commune, ville, région, population), où ville → région et la clé est (commune, ville). Ici, région (non clé) dépend d'une partie de la clé : c'est déjà une violation de la 2FN. La décomposition proposée (Commune (commune, ville, population) et Ville (ville, région)) est correcte, mais l'exemple ci-dessus (un attribut non clé qui détermine une partie de la clé) illustre mieux ce qui est propre à la FNBC.
Tableau: Quatrième forme normale (4FN) : les dépendances multivaluées
Pour un même modèle de voiture, il existe plusieurs couleurs et plusieurs versions, indépendantes les unes des autres : ce sont deux dépendances multivaluées (modèle →→ couleur, modèle →→ version). La table DISPONIBILITÉ (modèle, couleur, version) doit alors contenir toutes les combinaisons. Une relation est en 4FN si elle est en FNBC et ne contient pas plusieurs dépendances multivaluées indépendantes.
Pour retirer la version « Excellence » du modèle 715, il faut supprimer toutes les lignes correspondantes. On décompose :
DISPO_COULEUR (modèle PK, couleur PK)
DISPO_VERSION (modèle PK, version PK)Les trois premières formes normales suffisent dans la grande majorité des projets : une base en 3FN est considérée comme normalisée. La FNBC et la 4FN traitent des cas plus rares.
Pour une association (0,1) – (1,1) (un employé dirige 0 ou 1 service, un service est dirigé par exactement un employé), on place la clé étrangère dans la table côté (1,1) : SERVICE (codeServ, nomServ, #matriculeDirecteur). Pour une association (0,1) – (0,1), on la place du côté où elle sera le moins souvent vide, ou on crée une table d'association.
Application au centre de formation
codeForm est copié dans SESSION.numCINEtu, codeSess), qui porte typeCours. APPARTIENT, (1,N) – (1,N), devient la table COMBINAISON (le nom du résumé), de clé (codeSpec, codeForm).ÉTUDIANT (numCINEtu PK, nomEtu, prenomEtu, dateNaissEtu, niveauEtu, nomVilleEtu, adresseEtu)
FORMATION (codeForm PK, titreForm, dureeForm, prixForm)
SESSION (codeSess PK, nomSess, dateDebutSess, dateFinSess, #codeForm)
SPÉCIALITÉ (codeSpec PK, nomSpec, descSpec)
INSCRIPTION (#numCINEtu PK, #codeSess PK, typeCours)
COMBINAISON (#codeSpec PK, #codeForm PK)INSCRIPTION.numCINEtu ──► ÉTUDIANT.numCINEtu
INSCRIPTION.codeSess ──► SESSION.codeSess
SESSION.codeForm ──► FORMATION.codeForm
COMBINAISON.codeForm ──► FORMATION.codeForm
COMBINAISON.codeSpec ──► SPÉCIALITÉ.codeSpecRevois la partie modélisation : on y passe des entités aux tables et aux clés (en anglais).