La normalisation est une technique de conception de base de données, qui est utilisée pour concevoir une table de base de données relationnelle jusqu’à une forme normale supérieure. Le processus est progressif et un niveau plus élevé de normalisation de la base de données ne peut être atteint que si les niveaux précédents ont été satisfaits.
Cela signifie que, avoir des données sous une forme non normalisée (la moins normalisée) et visant à atteindre le plus haut niveau de normalisation, la première étape consisterait à assurer la conformité à la première forme normale, la deuxième étape consisterait à s’assurer que la deuxième forme normale est satisfaite, et ainsi de suite dans l’ordre mentionné ci-dessus, jusqu’à ce que les données soient conformes à la sixième forme normale.
Cependant, il convient de noter que les formes normales au-delà de 4NF sont principalement d’intérêt académique, car les problèmes qu’elles doivent résoudre apparaissent rarement dans la pratique.
Veuillez noter que les données de l’exemple suivant étaient intentionnellement conçu pour contredire la plupart des formes normales. Dans la vraie vie, il est tout à fait possible de pouvoir sauter certaines des étapes de normalisation car le tableau ne contient rien qui contredit la forme normale donnée. Il arrive également fréquemment que la correction d’une violation d’une forme normale corrige également une violation d’une forme normale supérieure dans le processus. Une table a également été choisie pour la normalisation à chaque étape, ce qui signifie qu’à la fin de cet exemple de processus, il se peut que certaines tables ne satisfassent pas à la forme normale la plus élevée.
Initial dataEdit
Soit une table de base de données avec la structure suivante:
| Titre | Auteur | Auteur Nationalité | Format | Prix | Objet | Pages | Épaisseur | Éditeur | Pays de l’éditeur | Type de publication | Identifiant du genre | Nom du genre |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Début de la conception et de l’optimisation de la base de données MySQL | Chad Russell | Américain | Couverture rigide | 49,99 | MySQL,
Base de données, Conception |
520 | Thick | Apress | USA | E-book | 1 | Tutoriel |
Nous supposons dans cet exemple que chaque livre n’a qu’un seul auteur.
Satisfaisant 1NFEdit
Pour satisfaire 1NF, les valeurs de chaque colonne d’une table doivent être atomiques. Dans le tableau initial, Subject contient un ensemble de valeurs de sujet, ce qui signifie qu’il n’est pas conforme.
Une façon d’atteindre le 1NF serait de séparer les duplicités en plusieurs colonnes en utilisant des groupes répétitifs Subject:
| Titre | Format | Auteur | Nationalité de l’auteur | Prix | Sujet 1 | Sujet 2 | Sujet 3 | Pages | Épaisseur | Éditeur | Pays de l’éditeur | Identifiant du genre | Nom du genre |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Début de la conception et de l’optimisation de la base de données MySQL | Couverture rigide | Chad Russell | Américain | 49,99 | MySQL | Base de données | Conception | 520 | Épaisse | Apress | États-Unis | 1 | Tutoriel |
Bien que maintenant la table soit formellement conforme au 1NF (est atomique), le problème avec cette solution est évident – si un livre a plus de trois sujets, il ne peut pas être ajouté à la base de données sans modifier sa structure.
Pour résoudre le problème de manière plus élégante, il est nécessaire d’identifier les entités représentées dans le tableau et de les séparer dans leurs propres tables respectives. Dans ce cas, il en résulterait des tableaux Livre, Sujet et Editeur:
| Titre | Format | Auteur | Auteur Nationalité | Prix | Pages | Épaisseur | Identifiant du genre | Nom du genre | Identifiant de l’éditeur |
|---|---|---|---|---|---|---|---|---|---|
| Début de MySQL Conception et optimisation de la base de données | Couverture rigide | Chad Russell | Américain | 49.99 | 520 | Épais | 1 | Tutoriel | 1 |
|
|
Le simple fait de séparer les données initiales en plusieurs tables romprait la connexion entre les données. Cela signifie que les relations entre les tables nouvellement introduites doivent être déterminées. Notez que la colonne « ID éditeur » dans le tableau du livre est une clé étrangère réalisant une relation plusieurs-à-un entre un livre et un éditeur.
Un livre peut contenir de nombreux sujets, ainsi qu’un sujet peut correspondent à de nombreux livres. Cela signifie également qu’une relation plusieurs-à-plusieurs doit être définie, obtenue en créant une table de liens:
|
Au lieu d’une table sous forme non normalisée, il y a maintenant 4 tables conformes au 1NF.
Satisfaisant 2NFEdit
La table Book a une clé candidate (qui est donc la clé primaire), la clé composée {Title, Format}. Considérez le fragment de tableau suivant:
| Titre | Format | Auteur | Nationalité de l’auteur | Prix | Pages | Épaisseur | Identifiant du genre | Nom du genre | Identifiant de l’éditeur |
|---|---|---|---|---|---|---|---|---|---|
| Début de la conception et de l’optimisation de la base de données MySQL | Couverture rigide | Chad Russell | Américain | 49,99 | 520 | épais | 1 | Tutoriel | 1 |
| Début de la conception et de l’optimisation de la base de données MySQL | E-book | Chad Russell | Américain | 22,34 | 520 | épais | 1 | Tutoriel | 1 |
| Le modèle relationnel pour la gestion de bases de données: version 2 | E-book | EFCodd | Britannique | 13,88 | 538 | épais | 2 | Science populaire | 2 |
| Le modèle relationnel pour Database Ma nagement: Version 2 | Broché | EFCodd | Britannique | 39,99 | 538 | Epais | 2 | Science populaire | 2 |
Tous les attributs qui ne font pas partie de la clé candidate dépendent du titre, mais seul le prix dépend également du format. Pour se conformer à 2NF et supprimer les doublons, chaque attribut de clé non candidate doit dépendre de la clé candidate entière, pas seulement d’une partie de celle-ci.
Pour normaliser cette table, faites de {Title} une clé candidate (simple) (la clé primaire) pour que chaque attribut de clé non candidate dépende de la clé candidate entière, et supprimez Price dans une table séparée afin que sa dépendance au format puisse être préservée:
|
|
Maintenant, la table Book est conforme à 2NF.
Satisfait à 3NFEdit
La table Book a toujours une dépendance fonctionnelle transitive ({Author Nationality} dépend de {Author}, qui dépend de {Title }). Une violation similaire existe pour le genre ({Genre Name} dépend de {Genre ID}, qui dépend de {Title}). Par conséquent, la table Book n’est pas dans 3NF. Pour le faire dans 3NF, utilisons la structure de table suivante, éliminant ainsi les dépendances fonctionnelles transitives en plaçant {Author Nationality} et {Genre Name} dans leurs propres tables respectives:
| Titre | Auteur | Pages | Épaisseur | Identifiant de genre | Identifiant éditeur |
|---|---|---|---|---|---|
| Début de la conception de la base de données MySQL et Optimisation | Chad Russell | 520 | épais | 1 | 1 |
| Le modèle relationnel pour la gestion des bases de données: version 2 | EFCodd | 538 | Thick | 2 | 2 |
|
| Auteur | Auteur Nationalité |
|---|---|
| Chad Russell | Américain |
| EFCodd | Britannique |
| Gen re ID | Nom du genre |
|---|---|
| 1 | Tutoriel |
| 2 | Science populaire |
Satisfaire EKNFEdit
La forme normale de clé élémentaire (EKNF) se situe strictement entre 3NF et BCNF et n’est pas beaucoup discutée dans la littérature. Il vise à « capturer les principales qualités de 3NF et de BCNF » tout en évitant les problèmes des deux (à savoir que 3NF est « trop indulgent » et que le BCNF est « sujet à une complexité de calcul »). Comme il est rarement mentionné dans la littérature, il n’est pas inclus dans cet exemple.
4NFEdit satisfaisant
Supposons que la base de données appartient à une franchise de libraires qui a plusieurs franchisés qui possèdent des magasins dans différents endroits.C’est pourquoi le détaillant a décidé d’ajouter un tableau contenant des données sur la disponibilité des livres à différents endroits:
| ID du franchisé | Titre | Emplacement |
|---|---|---|
| 1 | Début de la conception et de l’optimisation de la base de données MySQL | Californie |
| 1 | Début de la conception et de l’optimisation de la base de données MySQL | Floride |
| 1 | Début de la conception et de l’optimisation de la base de données MySQL | Texas |
| 1 | Le modèle relationnel pour la gestion des bases de données: version 2 | Californie |
| 1 | Le modèle relationnel pour la gestion des bases de données: version 2 | Floride |
| 1 | Le modèle relationnel pour la gestion des bases de données: version 2 | Texas |
| 2 | Début de la conception et de l’optimisation de la base de données MySQL | Californie |
| 2 | Début de la conception et de l’optimisation de la base de données MySQL | Floride |
| 2 | Début de la conception et de l’optimisation des bases de données MySQL | Texas |
| 2 | Le modèle relationnel pour la gestion des bases de données: version 2 | Californie |
| 2 | Le modèle relationnel pour la gestion des bases de données: version 2 | Floride |
| 2 | Le modèle relationnel pour la gestion des bases de données: version 2 | Texas |
| 3 | Début de la conception et de l’optimisation de la base de données MySQL | Texas |
Comme cette structure de table consiste en une clé primaire composée, elle ne contient aucun attribut non clé et elle est déjà en BCNF (et donc satisfait également toutes les formes normales précédentes). Cependant, si nous supposons que tous les livres disponibles sont proposés dans chaque zone, nous pourrions remarquer que le titre n’est pas lié sans ambiguïté à un certain emplacement et que la table ne satisfait donc pas 4NF.
Cela signifie que, pour satisfaire la quatrième forme normale, ce tableau doit également être décomposé:
|
|
||||||||||||
| ID du franchisé | Emplacement | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Californie | ||||||||||||
| 1 | Floride | ||||||||||||
| 1 | Texas | ||||||||||||
| 2 | Californie | ||||||||||||
| 2 | Floride | ||||||||||||
| 2 | Texas | ||||||||||||
| 3 | Texas |
Désormais, chaque enregistrement est identifié sans ambiguïté par une super-clé, donc 4NF est satisfait.
Satisfait à ETNFEdit
Supposons que les franchisés puissent également commander des carnets auprès de différents fournisseurs. Soit la relation également soumise à la contrainte suivante:
- Si un certain fournisseur fournit un certain titre
- et que le titre est fourni au franchisé
- et le franchisé est fourni par le fournisseur,
- alors le fournisseur fournit le titre au franchisé.
| ID fournisseur | Titre | ID du franchisé |
|---|---|---|
| 1 | Début de la conception et de l’optimisation de la base de données MySQL | 1 |
| 2 | Le modèle relationnel pour la gestion de base de données: version 2 | 2 |
| 3 | Apprentissage de SQL | 3 |
Cette table est en 4NF, mais l’ID fournisseur est égal à la jointure de ses projections: {{ID fournisseur, livre}, {Livre, ID franchisé}, {ID franchisé, fournisseur IDENTIFIANT}}.Aucun composant de cette dépendance de jointure n’est une super-clé (la seule super-clé étant l’en-tête entier), donc la table ne satisfait pas l’ETNF et peut être décomposée davantage:
|
|
|
La décomposition produit la conformité ETNF.
Satisfaisant 5NFEdit
Pour repérer une table ne satisfaisant pas le 5NF, il est généralement nécessaire pour examiner les données de manière approfondie. Supposons la table de l’exemple 4NF avec une petite modification des données et examinons si elle satisfait 5NF:
| ID du franchisé | Titre | Emplacement |
|---|---|---|
| 1 | Début de la conception et de l’optimisation de la base de données MySQL | Californie |
| 1 | Apprentissage de SQL | Californie |
| 1 | Le modèle relationnel pour la gestion des bases de données: version 2 | Texas |
| 2 | Le modèle relationnel pour la gestion des bases de données: version 2 | Californie |
Si nous décomposons ce tableau, nous réduisons les redondances et obtenons les deux tableaux suivants:
|
|
Que se passe-t-il si nous essayons de rejoindre ces tables? La requête renverrait les données suivantes:
| ID du franchisé | Titre | Emplacement |
|---|---|---|
| 1 | Début de la conception de la base de données MySQL et Optimisation | Californie |
| 1 | Learning SQL | Californie |
| 1 | Le modèle relationnel pour la gestion des bases de données: version 2 | Californie |
| 1 | Le modèle relationnel pour la gestion des bases de données: version 2 | Texas |
| 1 | Learning SQL | Texas |
| 1 | Début de la conception et de l’optimisation de la base de données MySQL | Texas |
| 2 | Le modèle relationnel pour la gestion des bases de données: version 2 | Californie |
Apparemment, le JOIN renvoie trois lignes de plus qu’il ne le devrait – essayons d’ajouter une table pour clarifier la relation.Nous nous retrouvons avec trois tableaux distincts:
|
|
|
Que retournera le JOIN maintenant? Il n’est en fait pas possible de joindre ces trois tables. Cela signifie qu’il n’était pas possible de décomposer le Franchisé – Emplacement du livre sans perte de données, donc la table satisfait déjà 5NF.
CJ Date a fait valoir que seule une base de données dans 5NF est vraiment « normalisée ».
Satisfaisant DKNFEdit
Jetons un œil à la table Book des exemples précédents et voyons si elle satisfait la forme normale de la clé de domaine:
| Titre | Pages | Épaisseur | ID de genre | ID d’éditeur | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Début de la conception et de l’optimisation de la base de données MySQL | 520 | Épais | 1 | 1 | ||||||||||||||||||||||||||
| Le modèle relationnel pour la gestion de base de données: version 2 | 538 | Épais | 2 | 2 | ||||||||||||||||||||||||||
| Apprentissage de SQL | 338 | Slim | 1 | 3 | ||||||||||||||||||||||||||
| Livre de recettes SQL | 636 | Épais | 1 | 3 |
|
|
De cette façon, la violation d’intégrité de domaine a été éliminée et la table est en DKNF.
Satisfaire 6NFEdit
Une définition simple et intuitive de la sixième forme normale est que « 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 du 1NF
| Publisher_ID | Nom | Pays |
|---|---|---|
| 1 | Apress | USA |
doit être plus décomposé en deux tableaux:
|
|
L’inconvénient évident de 6NF est la prolifération des tableaux nécessaires pour représenter les informations sur une seule entité. Si une table dans 5NF a une colonne de clé primaire et N attributs, représentant les mêmes informations dans 6NF nécessitera N tables; les mises à jour multi-champs d’un seul enregistrement conceptuel nécessiteront des mises à jour de plusieurs tables; et les insertions et les suppressions nécessiteront également des opérations sur plusieurs tables. Pour cette raison, dans les bases de données destinées à répondre aux besoins de traitement des transactions en ligne, 6NF ne doit pas être utilisé.
Cependant, dans les entrepôts de données, qui ne permettent pas les mises à jour interactives et qui sont spécialisés pour les requêtes rapides sur de gros volumes de données , certains SGBD utilisent une représentation 6NF interne – connue sous le nom de magasin de données en colonne. Dans les situations où le nombre de valeurs uniques d’une colonne est bien inférieur au nombre de lignes de la table, le stockage orienté colonne permet d’importantes économies d’espace grâce à la compression des données. Le stockage en colonne permet également une exécution rapide des requêtes de plage (par exemple, afficher tous les enregistrements où une colonne particulière est comprise entre X et Y, ou inférieure à X.)
Dans tous ces cas, cependant, le concepteur de la base de données ne doivent effectuer la normalisation 6NF manuellement en créant des tables séparées. Certains SGBD spécialisés pour l’entreposage, tels que Sybase IQ, utilisent le stockage en colonnes par défaut, mais le concepteur ne voit toujours qu’une seule table multi-colonnes. D’autres SGBD, tels que Microsoft SQL Server 2012 et versions ultérieures, vous permettent de spécifier un « index columnstore » pour une table particulière.
