Normalisation des bases de données

Soit une table de base de données ayant la structure suivante, décrivant un livre :

Dans cet exemple, on suppose que chaque livre n'a qu'un seul auteur.

Une table conforme au modèle relationnel possède une clé primaire qui identifie de manière unique chaque ligne. Dans notre exemple, la clé primaire est une clé composite { Titre, Format} , indiquée par le soulignement :

En première forme normale, chaque champ contient une seule valeur. Un champ ne peut pas contenir un ensemble de valeurs ni un enregistrement imbriqué. Le champ « Sujet » contient un ensemble de valeurs de sujet, ce qui signifie qu’il n’est pas conforme. Pour résoudre ce problème, les sujets sont extraits dans une table distincte nommée « Sujet » :

Au lieu d'un seul tableau sous forme non normalisée , il y a maintenant deux tableaux conformes à la 1NF.

Rappelons que la table Book ci-dessous possède une clé composite { Title, Format} , qui ne satisfait pas la 2NF si un sous-ensemble de cette clé est un déterminant. À ce stade de notre conception, la clé n'est pas encore finalisée comme clé primaire ; elle est donc appelée clé candidate . Considérons la table suivante :

Tous les attributs qui ne font pas partie de la clé candidate dépendent de Title , mais seul Price dépend également de Format . Pour être conforme à la 2NF et supprimer les doublons, chaque attribut qui ne fait pas partie de la clé candidate doit dépendre de la clé candidate entière, et non d'une partie seulement.

Pour normaliser ce tableau, faites de {Title} une clé candidate (simple) (la clé primaire) afin que chaque attribut non candidat dépende de la clé candidate entière, et déplacez Price dans un tableau séparé afin que sa dépendance à Format puisse être préservée :

Désormais, les tables Book et Price sont toutes deux conformes à la 2NF .

La table Livre présente toujours une dépendance fonctionnelle transitive ({Nationalité de l'auteur} dépend de {Auteur}, qui dépend de {Titre}). Des violations similaires existent pour l'éditeur ({Pays de l'éditeur} dépend de {Éditeur}, qui dépend de {Titre}) et pour le genre ({Nom du genre} dépend de {ID du genre}, qui dépend de {Titre}). Par conséquent, la table Livre n'est pas en 3NF. Pour résoudre ce problème, nous pouvons placer {Nationalité de l'auteur}, {Pays de l'éditeur} et {Nom du genre} dans leurs propres tables respectives, éliminant ainsi les dépendances fonctionnelles transitives.

Supposons que les franchisés puissent également commander des livres auprès de différents fournisseurs. Supposons également que la relation soit soumise à la contrainte suivante :

• Si un certain fournisseur fournit un certain titre
• et le titre est fourni au franchisé
• et le franchisé est approvisionné par le fournisseur,
• puis le fournisseur fournit le titre au franchisé .

Ce tableau est en 4NF , mais l'ID du fournisseur est égal à la jointure de ses projections : {{ID du fournisseur, Titre}, {Titre, ID du franchisé}, {ID du franchisé, ID du fournisseur}} . Aucun composant de cette dépendance de jointure n'est une superclé (la seule superclé étant l'en-tête entier), donc le tableau ne satisfait pas l' ETNF et peut être décomposé davantage :

La décomposition permet d'obtenir la conformité à l'ETNF.

Pour repérer un tableau ne respectant pas la 5NF , il est généralement nécessaire d'examiner attentivement les données. Prenons le tableau de l'exemple de la 4NF avec une légère modification des données et vérifions s'il respecte la 5NF :

La décomposition de ce tableau réduit les redondances, ce qui donne les deux tableaux suivants :

La requête reliant ces tables renverrait les données suivantes :

La jointure renvoie trois lignes de plus que prévu ; l’ajout d’une autre table pour clarifier la relation aboutit à trois tables distinctes :

Que renverra la jointure maintenant ? Il est en fait impossible de joindre ces trois tables. Cela signifie qu’il n’a pas été possible de décomposer la table Franchisee – Book – Location sans perte de données ; par conséquent, la table satisfait déjà la 5NF .

Avertissement : les données utilisées illustrent le principe, mais ne sont pas nécessairement exactes. Dans ce cas, il serait préférable de décomposer les données comme suit, avec une clé de substitution que nous appellerons « ID du magasin » :

La fonction JOIN renverra désormais le résultat attendu :

CJ Date a soutenu que seule une base de données en 5NF est véritablement « normalisée ».

Examinons la table Book des exemples précédents et voyons si elle satisfait la forme normale de la clé de domaine :

Logiquement, l'épaisseur d' un livre est déterminée par son nombre de pages. Autrement dit, elle dépend du nombre de pages , ce qui n'est pas un critère déterminant. Prenons l'exemple d'un livre de moins de 350 pages considéré comme « mince » et d'un livre de plus de 350 pages considéré comme « épais ».

Cette convention est techniquement une contrainte, mais ce n'est ni une contrainte de domaine ni une contrainte de clé ; par conséquent, nous ne pouvons pas nous appuyer sur les contraintes de domaine et les contraintes de clé pour garantir l'intégrité des données.

Autrement dit, rien ne nous empêche de mettre, par exemple, « Épais » pour un livre de seulement 50 pages, et cela rend le tableau non conforme à la DKNF .

Pour résoudre ce problème, une table contenant une énumération définissant l' épaisseur est créée, et cette colonne est supprimée de la table d'origine :

Ainsi, la violation d'intégrité du domaine a été éliminée et la table est en DKNF .

La normalisation ne permet pas d'éviter tous les cas de résultats impossibles, contradictoires ou imprévisibles. Dans cet exemple, des valeurs minimales et maximales de pages de 1/350 et 200/999 999 999 999 entraîneraient des résultats imprévisibles. Il est donc préférable de spécifier et d'utiliser uniquement le nombre minimal de pages.

Une définition simple et intuitive de la sixième forme normale est la suivante : « une table est en 6NF lorsque la ligne contient la clé primaire et au plus un autre attribut »..

Cela signifie, par exemple, la table Publisher conçue lors de la création de la 1NF :

doit être décomposée en deux tableaux :

L'inconvénient majeur de la 6NF réside dans la multiplication des tables nécessaires pour représenter les informations relatives à une seule entité. Si une table en 5NF possède une colonne de clé primaire et N attributs, la représentation de ces mêmes informations en 6NF nécessitera N tables ; les mises à jour de plusieurs champs d'un même enregistrement conceptuel impliqueront des mises à jour dans plusieurs tables ; et les insertions et suppressions nécessiteront également des opérations sur plusieurs tables. C'est pourquoi, dans les bases de données destinées au traitement transactionnel en ligne (OLTP), la 6NF est à proscrire.

Cependant, dans les entrepôts de données , qui ne permettent pas les mises à jour interactives et sont spécialisés dans l'interrogation rapide de grands volumes de données, certains SGBD utilisent une représentation interne en 6NF, appelée stockage en colonnes . Lorsque le nombre de valeurs uniques d'une colonne est bien inférieur au nombre de lignes de la table, le stockage en colonnes permet de réaliser d'importantes économies d'espace grâce à la compression des données. Ce type de stockage permet également l'exécution rapide de requêtes par plage (par exemple, afficher tous les enregistrements où une valeur donnée d'une colonne est comprise entre X et Y, ou inférieure à X).

Dans tous ces cas, le concepteur de base de données n'a pas à effectuer manuellement la normalisation 6NF en créant des tables distinctes. Certains SGBD spécialisés dans l'entreposage de données, comme Sybase IQ , utilisent par défaut un stockage en colonnes, mais le concepteur ne voit alors qu'une seule table à plusieurs colonnes. D'autres SGBD, comme Microsoft SQL Server 2012 et versions ultérieures, permettent de spécifier un « index columnstore » pour une table particulière.