#Normalisation : éliminer la redondanceCopier le lien

Objectif : réduire les redondances et les anomalies d'insertion, de mise à jour et de suppression grâce aux formes normales (1NF, 2NF, 3NF, BCNF) et à la décomposition sans perte. La normalisation rend les données cohérentes et plus faciles à maintenir ; on ne dénormalise ensuite, de façon mesurée, que si des lectures spécifiques l'exigent.

#Prérequis et objectifsCopier le lien

• Prérequis : Modélisation (clés primaires, étrangères, associations N-N).
• Définir et repérer une dépendance fonctionnelle.
• Diagnostiquer la forme normale d'une table et les anomalies qu'elle provoque.
• Décomposer une table en 3NF/BCNF sans perte d'information et en préservant les dépendances.

#Vocabulaire : deux définitions suffisentCopier le lien

• Dépendance fonctionnelle (DF) : X → Y signifie que la valeur de X détermine celle de Y. Deux lignes partageant X partagent nécessairement Y. Exemple : isbn → titre.
• Clé candidate : ensemble minimal d'attributs qui détermine tous les autres. Une super-clé est un ensemble déterminant tous les autres, pas forcément minimal.

Les définitions formelles du cours, à connaître mot pour mot :

Soient A et B des attributs d'une relation. B est fonctionnellement dépendant de A (ce qui s'écrit $A \to B$) si chaque valeur de A est associée à exactement une valeur de B.

Soient A et B des attributs d'une relation. B est fonctionnellement complètement dépendant de A si B dépend fonctionnellement de A mais ne dépend d'aucun sous-ensemble propre de A.

La nuance « aucun sous-ensemble propre » est le cœur de la 2NF. Si A est un ensemble de trois attributs {A1, A2, A3} et que A1 → B, alors B dépend de A mais aussi d'une partie de A : la dépendance est partielle, pas complète.

#Comment on identifie une DF sur un échantillonCopier le lien

Un énoncé donne souvent un tableau de valeurs et demande d'en déduire les DF. La règle est une lecture en colonnes : si pour deux lignes quelconques, des valeurs égales dans la colonne X impliquent des valeurs égales dans la colonne Y, alors X → Y. Dit autrement, X admet une injection sur Y.

Sur un échantillon, l'absence de contre-exemple est une présomption, pas une preuve — c'est la limite que tout énoncé de ce type comporte. On ne peut que proposer les DF compatibles avec les données, en supposant l'échantillon représentatif de toutes les valeurs possibles.

Exemple type, sur une table Echantillon(A, B, C, D, E) :

Lecture : A prend deux valeurs (a, e) ; à a correspondent toujours C = z et D = w, à e correspondent toujours C = r et D = w. Donc $A \to C$ et $A \to D$. En revanche a est associé à b puis à d puis à f : $A \not\to B$. Symétriquement, $C \to A$ et $C \to D$.

La clé primaire est le plus petit ensemble d'attributs qui détermine fonctionnellement tous les autres. Ici, ni A ni C ni D ne déterminent B ou E ; la clé est donc composite, et c'est le plus petit ensemble qui fonctionne.

#Les formes normales, une par uneCopier le lien

• 1NF : valeurs atomiques. Pas de liste dans une colonne, pas de groupe répétitif (tel1, tel2, tel3).
• 2NF : 1NF, et aucun attribut non-clé ne dépend d'une partie de la clé composite. Ne peut être violée qu'avec une clé composite.
• 3NF : 2NF, et aucune dépendance transitive : un attribut non-clé ne dépend pas d'un autre attribut non-clé.
• BCNF : pour toute DF non triviale X → Y, X est une super-clé. Strictement plus exigeante que 3NF.

Moyen mnémotechnique classique : « la clé, toute la clé, rien que la clé » ; 2NF porte sur « toute la clé », 3NF sur « rien que la clé ».

#Les définitions officiellesCopier le lien

Première forme normale (1NF) : une relation est en 1NF si elle ne contient pas d'attributs à valeurs multiples ou composites, c'est-à-dire si l'intersection de toute ligne et de toute colonne ne comporte qu'une et une seule valeur.

Deuxième forme normale (2NF) : une relation est en 2NF si elle est en 1NF et si tout attribut de clé non primaire est fonctionnellement complètement dépendant de la clé primaire.

Troisième forme normale (3NF) : une relation est en 3NF si elle est en 2NF et si aucun attribut ne faisant pas partie de la clé primaire ne dépend transitivement de la clé primaire.

Deux précisions que la définition courte de la 1NF laisse échapper, et qui font la différence en TD :

• La 1NF concerne les lignes autant que les attributs. Un attribut viole la 1NF s'il porte plusieurs valeurs pour une ligne (attribut multivalué : une colonne téléphones avec « 06…, 07… ») et s'il porte plusieurs informations distinctes dans une seule valeur (attribut composite : une colonne BatEtEsc contenant « A,1 » pour le bâtiment A et l'escalier 1). Le remède est le même dans les deux cas : découper en colonnes atomiques, ou créer une table séparée si le nombre de valeurs varie.
• Une relation en 2NF peut violer la 3NF. Les deux formes sont indépendantes dans ce sens : la 2NF traite la dépendance à une partie de la clé, la 3NF traite la dépendance à un autre attribut non-clé. Une relation peut donc être en 2NF et pas en 3NF — c'est même le cas le plus fréquent en pratique.

#Partielle ou transitive : le diagnosticCopier le lien

Sur une relation de clé primaire (A, B, C), trois DF possibles se lisent très différemment :

1A, B, C -> D    D dépend complètement de la clé         → conforme 2NF2A, B    -> E    E dépend partiellement de la clé        → viole 2NF3A, F    -> G    G dépend d'un attribut non-clé (F)      → viole 3NF

Le troisième cas mérite attention : si F n'appartient pas à la clé, alors G dépend transitivement de la clé via F. Corriger la 2NF ne le corrige pas — il faut traiter la 3NF séparément.

Transformer une relation en 2NF signifie créer autant de tables supplémentaires qu'il y a de dépendances partielles distinctes, chacune contenant la partie de clé concernée et les attributs qu'elle détermine. Transformer une relation en 3NF signifie soit ajouter l'attribut concerné à la table appropriée, soit créer une nouvelle table contenant cet attribut et l'attribut non-clé qui le détermine — lequel devient clé étrangère de la table d'origine.

#Animation : décomposition sans perteCopier le lien

#Étude de cas : la table facturesCopier le lien

Table initiale : factures(num, client, ville, ville_cp, total), avec la DF ville → ville_cp (chaque ville a un code postal unique).

Anomalies concrètes :

• Insertion : impossible d'enregistrer le code postal d'une ville sans client, puisqu'il faut un numéro de facture (clé).
• Mise à jour : le code postal de Nice figure sur mille factures ; le changer demande mille mises à jour, et une seule omission crée une incohérence.
• Suppression : supprimer la dernière facture d'une ville efface aussi la connaissance de son code postal.

Décomposition :

1create table villes(2  nom text primary key,3  code_postal text not null4);5 6create table factures(7  num integer primary key,8  client text not null,9  ville text not null references villes(nom),10  total real not null11);

#Pas-à-pas : vérifier la décompositionCopier le lien

#Cas 2NF : la clé compositeCopier le lien

Table ligne_commande(commande_id, produit, libelle_produit, quantite), clé (commande_id, produit). La DF produit → libelle_produit ne porte que sur une partie de la clé : violation 2NF. Le libellé d'un produit se répète sur toutes ses lignes de commande ; le corriger demande autant de mises à jour que d'occurrences.

Décomposition : produits(produit, libelle) et ligne_commande(commande_id, produit, quantite). La jointure sur produit reconstitue tout, sans perte.

#Cas BCNF : quand 3NF ne suffit plusCopier le lien

Table inscriptions(etudiant, cours, enseignant), avec les règles : un étudiant s'inscrit à des cours, chacun auprès d'un enseignant ; chaque enseignant enseigne un seul cours. DF : etudiant, cours → enseignant et enseignant → cours. Clés candidates : (etudiant, cours) et (etudiant, enseignant). Tous les attributs appartiennent à une clé candidate : la table est 3NF. Mais le déterminant enseignant seul n'est pas une super-clé : BCNF est violée, et si un enseignant change de cours, il faut corriger toutes ses inscriptions.

Décomposition BCNF : enseignements(enseignant primary key, cours) et inscriptions(etudiant, enseignant). La DF etudiant, cours → enseignant n'est plus contrôlable dans une seule table : c'est le compromis à connaître, BCNF n'offre pas toujours la préservation des DF alors que 3NF la garantit.

#Propriétés souhaitables d'une décompositionCopier le lien

• Sans perte : la jointure naturelle des tables résultantes reconstitue exactement la table d'origine. Non négociable.

#Exercice : normaliser une table de réservationsCopier le lien

Table : reservations(id, nom_client, email_client, hotel, ville_hotel, etoiles_hotel, date_arrivee, nb_nuits).

DF identifiées : id → tous les attributs ; hotel → ville_hotel, etoiles_hotel ; email_client → nom_client.

1. Listez les anomalies d'insertion, de mise à jour et de suppression.
2. Donnez la décomposition en 3NF, sans perte.
3. Vérifiez que chaque DF est préservée.

#Exercice type examen 1 : les anomalies de PersonnelFilialeCopier le lien

Table : PersonnelFiliale(numPersonnel, nomP, fonction, salaire, numFiliale, adresseF).

Décrivez les anomalies d'insertion, de suppression et de modification.

#Exercice type examen 2 : la table des rendez-vous dentairesCopier le lien

C'est l'exercice de normalisation le plus complet du cours : il traverse les trois formes normales sur un cas réaliste. Table initiale (une ligne par rendez-vous) :

Règles métier : un patient reçoit un rendez-vous à une heure et une date précises chez le dentiste qui opère dans un cabinet précis. Chaque jour de consultation, un dentiste est affecté à un cabinet dentaire précis. Un patient ne peut pas être examiné par un même dentiste plus d'une fois par jour.

1. Identifiez la clé primaire.
2. Identifiez les dépendances fonctionnelles.
3. Normalisez en 3NF.
4. Ajoutez l'attribut prestationCabinet et re-normalisez.

1numPraticien, numPatient, date -> heure, numCabinet    (et chacun pris séparément aussi)2numPraticien                   -> nomDentiste3numPatient                     -> nomPatient4numPraticien, date             -> numCabinet

1PraticienPatient(numPraticien#, numPatient#, date, heure)   -- clé (numPraticien, numPatient, date)2Dentiste(numPraticien, nomDentiste)3Patient(numPatient, nomPatient)4Cabinet(numPraticien#, date, numCabinet)

#Exercice type examen 3 : la médiathèqueCopier le lien

Table initiale. Dans l'énoncé d'origine, numUtilis et nomUtilis sont fusionnés sur deux lignes : chaque utilisateur a emprunté deux DVD, et les deux premières colonnes ne sont écrites qu'une fois. C'est précisément cette fusion qui matérialise l'attribut multivalué.

La médiathèque ne gère pas plus d'un exemplaire par DVD, et un utilisateur ne peut pas emprunter un DVD plusieurs fois.

1. Transformez la relation en 1NF et indiquez la clé primaire.
2. Identifiez les dépendances fonctionnelles et transformez en 2NF.
3. Identifiez les dépendances transitives et transformez en 3NF.

1numUtilis -> nomUtilis2codeDvd   -> titreDvd, genreDvd, numRealis3numRealis -> nomRealis4numUtilis, codeDvd -> dateEmprunt, dateRetour

1Utilisateur(numUtilis, nomUtilis)2Dvd(codeDvd, titreDvd, genreDvd, numRealis#)3Emprunt(numUtilis#, codeDvd#, dateEmprunt, dateRetour)4Realisateur(numRealis, nomRealis)

#QuizCopier le lien