Normalisation : 1FN, 2FN, 3FN

La normalisation est une méthode qui vérifie qu'une table ne contient pas de redondance, à l'aide de règles appelées formes normales. La première forme normale (1FN) exige des valeurs atomiques, la deuxième (2FN) que chaque attribut dépende de toute la clé, la troisième (3FN) qu'il ne dépende que de la clé. Une base en 3FN évite les anomalies de mise à jour, d'insertion et de suppression. Ce chapitre définit chaque forme normale et normalise pas à pas un bon de commande.

Les anomalies d'une table mal conçue

Une petite société de maintenance informatique vend aussi du matériel à ses clients. Son ancien logiciel enregistre les commandes dans une seule table :

Le nom et la ville du Garage Morel sont recopiés trois fois, le libellé et le prix du SSD deux fois. Cette redondance provoque trois anomalies :

• Anomalie de mise à jour. Le Garage Morel déménage à Lons-le-Saunier : il faut modifier trois lignes. Une oubliée, et le client a deux villes.
• Anomalie d'insertion. Un nouveau produit arrive au catalogue. Tant que personne ne l'a commandé, on ne peut pas l'enregistrer : il n'y a pas de numéro de commande à mettre dans la ligne.
• Anomalie de suppression. On supprime la commande C102, annulée. Le Cabinet Roux disparaît avec elle : c'était sa seule ligne.

Les formes normales donnent une méthode pour découper cette table en plusieurs, sans rien perdre, jusqu'à faire disparaître ces anomalies. Elles reposent sur les dépendances fonctionnelles du chapitre 3.

Première forme normale (1FN)

Une table est en première forme normale si :

1. elle a une clé primaire ;
2. chaque colonne contient une valeur atomique : une seule valeur, pas une liste ;
3. elle ne contient pas de groupe répétitif : pas de colonnes tel1, tel2, tel3, ni produit1, produit2.

Le premier logiciel de la société, encore plus ancien, avait une ligne par commande :

La colonne produits contient une liste : impossible de compter les SSD vendus, de trier par produit, d'interdire une faute de frappe. La table n'est pas en 1FN. Pour l'y mettre, on écrit une ligne par produit commandé, ce qui donne la grande table du paragraphe précédent. Sa clé primaire est le couple (num_cde, ref_produit) : une commande contient plusieurs produits, un produit figure dans plusieurs commandes, mais un produit n'apparaît qu'une fois par commande.

Deuxième forme normale (2FN)

Une table est en deuxième forme normale si elle est en 1FN et si chaque colonne qui ne fait pas partie de la clé dépend de toute la clé, et non d'une partie seulement. Autrement dit, toutes les DF entre la clé et les autres colonnes sont élémentaires.

La question ne se pose que pour une clé composée : une table en 1FN dont la clé est une seule colonne est automatiquement en 2FN.

Dans la table en 1FN, la clé est (num_cde, ref_produit). Écrivons les DF :

num_cde               → date_cde, num_client
num_client            → nom_client, ville_client
ref_produit           → libelle, prix_unitaire
(num_cde, ref_produit) → quantite

Seule quantite dépend de toute la clé. date_cde, num_client, nom_client et ville_client dépendent de num_cde seul ; libelle et prix_unitaire de ref_produit seul. La table n'est pas en 2FN.

On la met en 2FN en sortant chaque groupe d'attributs qui dépend d'une partie de la clé dans une nouvelle table, dont la clé est cette partie :

commande (num_cde, date_cde, num_client, nom_client, ville_client)

produit (ref_produit, libelle, prix_unitaire)

ligne_commande (#num_cde, #ref_produit, quantite)

L'anomalie d'insertion d'un produit a disparu : un produit s'enregistre dans produit, sans commande.

Troisième forme normale (3FN)

Une table est en troisième forme normale si elle est en 2FN et si aucune colonne qui ne fait pas partie de la clé ne dépend d'une autre colonne non clé. Autrement dit, il n'y a pas de DF transitive entre la clé et les autres colonnes.

La table commande est en 2FN (sa clé est simple), mais :

num_cde → num_client → nom_client, ville_client

nom_client et ville_client dépendent de num_client, qui n'est pas la clé : c'est une DF transitive. Le Garage Morel est encore recopié sur chacune de ses commandes. La table n'est pas en 3FN.

On la met en 3FN en sortant les colonnes qui dépendent d'une colonne non clé dans une nouvelle table, dont la clé est cette colonne ; la colonne reste dans la table d'origine, comme clé étrangère :

client (num_client, nom_client, ville_client)

commande (num_cde, date_cde, #num_client)

Les anomalies de mise à jour et de suppression ont disparu : la ville du Garage Morel est écrite une fois, et supprimer la commande C102 ne supprime pas le Cabinet Roux.

Le résultat

Le schéma final, en 3FN :

client (num_client, nom_client, ville_client)

commande (num_cde, date_cde, #num_client)

produit (ref_produit, libelle, prix_unitaire)

ligne_commande (#num_cde, #ref_produit, quantite)

C'est exactement le MLD qu'on aurait obtenu en partant d'un MCD avec trois entités (CLIENT, COMMANDE, PRODUIT), une association un à plusieurs (PASSER) et une association plusieurs à plusieurs porteuse (CONTENIR, avec la quantité). Ce n'est pas un hasard.

Décomposer sans perte

Découper une table n'a d'intérêt que si l'on peut retrouver toutes les informations d'origine par des jointures, sans en perdre ni en inventer. La règle qui le garantit : la source de la DF devient la clé de la nouvelle table et reste dans l'ancienne, où elle devient clé étrangère. C'est ce qu'on a fait avec num_client, gardé dans commande.

La jointure des quatre tables redonne la table d'origine, ligne pour ligne :

SELECT c.num_cde, c.date_cde, cl.num_client, cl.nom_client, cl.ville_client,
       p.ref_produit, p.libelle, p.prix_unitaire, l.quantite
FROM ligne_commande l
JOIN commande c  ON c.num_cde = l.num_cde
JOIN client cl   ON cl.num_client = c.num_client
JOIN produit p   ON p.ref_produit = l.ref_produit;

Si on avait découpé n'importe comment, par exemple en mettant nom_client dans une table sans num_client, on n'aurait plus su à quel client appartient quel nom.

Normalisation et MCD

Un MCD bien construit (chaque propriété dépend de l'identifiant de son entité, et de lui seul ; les propriétés qui dépendent de plusieurs identifiants sont sur des associations) donne directement des tables en 3FN. La normalisation sert alors à vérifier le travail.

Elle sert aussi dans deux situations où l'on n'a pas de MCD :

• pour reprendre des données existantes : un tableur, un export, une vieille base, qu'on normalise pour concevoir la nouvelle ;
• pour juger une table dans un sujet ou dans le code d'un collègue : un simple coup d'œil aux DF dit si elle contient de la redondance.

Au-delà de la 3FN : la forme normale de Boyce-Codd

La forme normale de Boyce-Codd (FNBC, ou BCNF en anglais) est un peu plus stricte que la 3FN : toute source d'une DF doit être une clé candidate. Elle ne diffère de la 3FN que dans des cas rares, où une colonne non clé détermine une partie de la clé.

Exemple : dans un lycée, chaque professeur n'enseigne qu'une matière, et un élève a un seul professeur par matière. La table suivre (num_eleve, code_matiere, num_professeur) est en 3FN, mais num_professeur → code_matiere, et num_professeur n'est pas une clé : la matière d'un professeur est recopiée pour chacun de ses élèves. En FNBC, on découpe en enseigner (num_professeur, code_matiere) et suivre (#num_eleve, #num_professeur). Le BTS ne demande pas la FNBC ; il existe aussi des formes normales supérieures (4FN, 5FN), réservées aux cas d'école.

Dénormaliser volontairement

Une base normalisée oblige à joindre des tables pour reconstituer l'information. Dans de rares cas, on accepte une redondance contrôlée pour gagner en performance : un compteur du nombre de commandes stocké dans client, une table de statistiques recalculée chaque nuit, un entrepôt de données pour l'informatique décisionnelle.

La dénormalisation a un prix : il faut maintenir la copie à jour (par un trigger, un traitement planifié ou le code), sinon les anomalies reviennent. Elle se décide après avoir normalisé, se justifie par une mesure (une requête trop lente, prouvée avec EXPLAIN : chapitre Index du cours SQL) et se documente. Pour un projet de BTS, la bonne réponse est presque toujours : rester en 3FN, et utiliser une vue pour simplifier les requêtes.

Exercices