Normaliser une base

Pourquoi normaliser ?

Une table range toutes les informations d'une commande sur 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 2, on perd le prix de la Souris, qui n'apparaît dans aucune autre commande → anomalie de suppression (et supprimer la commande 3 ferait disparaître Bob et sa ville)
• 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

Le chapitre sur les systèmes d'information les a posées : A → B quand chaque valeur de A ne va qu'avec une seule valeur de B, et le sens compte. Les formes normales ne sont que des questions posées à ces dépendances.

Première Forme Normale (1NF)

Une table de commandes range dans une colonne produits la valeur Clavier, Souris, Écran. 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.

Mise en 1NF, la commande 1 prend une ligne par produit :

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

La dépendance idCommande → client, produits y est vérifiée sans réserve : une table peut satisfaire toutes ses dépendances et rester inutilisable.

Défaut voisin, des colonnes répétées :

Chaque cellule y est atomique, et la 1NF au sens strict n'y voit rien à redire. Le défaut est pourtant le même : un quatrième produit oblige à modifier la structure, et chercher un produit, à regarder trois colonnes.

Deuxième Forme Normale (2NF)

Le prix catalogue du clavier change. Dans la table Vente ci-dessous, de clé (idCommande, idProduit), ce prix est recopié sur chaque ligne de commande qui contient un clavier : en oublier une en le modifiant suffit pour que la base donne deux prix catalogue au même produit.

nomProduit et prixCatalogue dépendent seulement de idProduit, pas du couple (idCommande, idProduit) : les deux dépendances de la table n'ont pas la même partie gauche, ce qui s'écrit avant la flèche, et c'est exactement le symptôme. Une dépendance à une partie de la clé s'appelle une dépendance partielle, et la deuxième forme normale l'interdit.

La 2NF ne peut être violée que par une table dont la clé est composite : une clé d'une seule colonne n'a pas de partie dont un attribut pourrait dépendre.

Après (2NF), les attributs sont séparés selon leur dépendance : Produit(idProduit, nomProduit, prixCatalogue) et Vente(idCommande, idProduit, quantite). Le prix catalogue 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 de recoller les deux tables sur idProduit, ce qu'on appelle une jointure (chapitre sur l'algèbre relationnelle) : 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 la table d'employés ci-dessous, 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 un service sans employé n'a nulle part où exister. La figure met les deux dépendances de la table côte à côte, et c'est leur partie gauche qu'il faut comparer.

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, qui passe par une colonne intermédiaire. La troisième forme normale vise ce défaut-là.

Après (3NF), Employe(idEmploye, nom, idService) désigne Service(idService, nomService, batiment). Le déménagement ne touche plus qu'une ligne, un service vide s'enregistre sans difficulté, et chaque table ne porte plus qu'une seule dépendance, qui a la clé pour gauche.

Reste un cas que la règle, lue trop vite, condamnerait. Une table des techniciens porte l'adresse électronique de chacun, qui n'appartient qu'à lui : email → nom est vraie, et email n'est pas la clé primaire. Ce n'est pas une dépendance transitive : l'adresse est une clé candidate, elle ne se répète jamais, et le nom n'est écrit qu'une fois.

Au-delà de la 3NF : la forme de Boyce-Codd

Une table peut passer les trois tests et garder une redondance. Dans une école, chaque enseignant n'enseigne qu'une matière, et chaque étudiant n'a qu'un enseignant par matière.

Chacun de ses trois attributs appartient à une clé candidate : la 2NF et la 3NF, qui ne parlent que des attributs non-clés, la laissent passer. La matière de Durand y est pourtant écrite deux fois, une par étudiant qu'il encadre, et un enseignant qui n'encadre encore personne n'a aucune ligne où dire ce qu'il enseigne. La forme normale de Boyce-Codd (BCNF) le reproche : la partie gauche de chaque dépendance doit être une clé de la table. enseignant → matiere ne l'est pas.

La décomposition qu'elle propose sépare Enseignant (enseignant, matiere), où la matière ne s'écrit plus qu'une fois par enseignant, et Encadrement (etudiant, enseignant). Elle a un prix, que la figure écrit sous la décomposition : la règle « un étudiant n'a qu'un enseignant par matière », etudiant, matiere → enseignant, ne se lit plus dans aucune des deux tables, et la vérifier demande une jointure. Rien n'empêcherait plus d'inscrire Martin auprès d'Aubert et de Leclerc, tous deux en statistiques. C'est pourquoi la plupart des modèles s'arrêtent à la 3NF, et ne vont en BCNF que lorsque la règle perdue peut se vérifier autrement.

Une base peut aussi recopier des valeurs exprès : une base qui sert surtout à lire de très gros volumes dénormalise, pour répondre sans recoller dix tables, comme le modèle en étoile du chapitre sur l'entrepôt de données. Recopier se décide alors en connaissance de cause, avec un moyen de tenir la copie à jour, jamais par oubli.

Mettre en pratique