Normalisation en SQL : 1NF, 2NF, 3NF & BCNF

La normalisation est un processus systématique visant à garantir qu'un modèle de base de données relationnelle est efficace, adapté aux requêtes à usage général et exempt de caractéristiques indésirables telles que les anomalies d'insertion, de mise à jour et de suppression, entraînant la perte de l'intégrité des données. Ce processus de normalisation permet également d'éliminer la redondance des données et réduit les risques d'incohérence après toute opération d'insertion, de mise à jour ou de suppression.

Pour une meilleure compréhension, considérez le schéma suivant : Étudiant (Nom, Adresse, Matière, Niveau)

Il y a quelques problèmes ou inefficacités dans ce schéma.

1) Redondance : L'Adresse de l'élève est répétée pour chaque matière à laquelle il est inscrit.

2) Anomalie de mise à jour : nous avons peut-être mis à jour l'adresse dans un tuple (ligne) tout en la laissant inchangée dans les autres lignes. Ainsi, nous n'aurions pas une adresse systématiquement unique pour chaque étudiant.

3) Anomalie d'insertion : Nous n'enregistrerons pas l'Adresse d'un élève sans s'inscrire à au moins une Matière. De même, lorsqu'un étudiant veut s'inscrire pour un nouveau sujet, il est possible qu'une adresse différente soit insérée.

4) Anomalie de suppression : Si un étudiant décide d'interrompre toutes les matières inscrites, l'adresse de l'étudiant sera également perdue lors du processus de suppression.

Ainsi, il est important de représenter les données de l'utilisateur par des relations qui ne créent pas d'anomalies suite aux opérations d'ajout, de suppression ou de mise à jour de tuples. Cela ne peut être réalisé que par une analyse minutieuse des contraintes d'intégrité, en particulier des dépendances de données de la base de données.

Les relations doivent être conçues de manière à ne regrouper que les attributs qui existent naturellement ensemble. Cela peut principalement être fait par une compréhension de base de la signification de tous les attributs de données. Cependant, nous avons encore besoin d'une mesure formelle pour assurer notre objectif de conception.

La normalisation est cette mesure formelle. Il répond à la question de savoir pourquoi un groupe particulier d'attributs sera meilleur qu'un autre.

Sept formes normales existent à ce jour :

• Première forme normale (1NF)
• Deuxième forme normale (2NF)
• Troisième forme normale (3NF)
• Forme normale de Boyce-Codd (BCNF)
• Quatrième forme normale (4NF)
• Cinquième forme normale (5NF)
• Sixième ou forme normale de clé de domaine (6NF)

Lire : Types de vues en SQL

Première forme normale (1NF ou forme minimale)

• Il n'y a pas d'ordre de haut en bas pour les lignes et de gauche à droite pour les colonnes.
• Il n'y a pas de lignes en double.
• Chaque intersection de ligne et de colonne contient exactement une valeur du domaine applicable ou une valeur nulle. Cette condition indique que toutes les valeurs de colonne doivent être atomiques, scalaires ou ne contenir qu'une seule valeur. Aucune répétition d'informations ou de valeurs dans plusieurs colonnes n'est autorisée ici.
• Toutes les colonnes sont régulières (c'est-à-dire que les lignes n'ont pas de composants masqués tels que les ID de ligne, les ID d'objet ou les horodatages masqués).

Prenons un exemple de schéma non normalisé. Supposons qu'un concepteur souhaite enregistrer les noms et numéros de téléphone des clients. Ils définissent une table client comme indiqué :

Ici, ce n'est pas dans 1 NF. La colonne Numéros de téléphone n'est pas atomique ou n'a pas de valeur scalaire, c'est-à-dire qu'elle a eu plus d'une valeur, ce qui ne peut pas être autorisé dans 1 NF.

Pour en faire 1 NF

• Nous allons d'abord casser (décomposer) notre table unique en deux.
• Chaque table doit contenir des informations sur une seule entité.

Les groupes répétés de numéros de téléphone ne se produisent pas dans cette conception. Au lieu de cela, chaque lien Client-Numéro de téléphone apparaît sur son propre enregistrement.

Checkout : Questions et réponses les plus courantes lors des entretiens SQL

Deuxième forme normale

Chaque forme normale a des critères plus contraignants que son prédécesseur. Ainsi, toute table qui est en deuxième forme normale (2NF) ou supérieure est, par définition, également en 1NF. D'autre part, une table qui est en 1NF peut ou non être en 2NF ; s'il est en 2NF, il peut ou non être en 3NF, et ainsi de suite.

Une table 1NF est dite en 2NF si et seulement si aucun de ses attributs non premiers ne dépend fonctionnellement d'une partie (sous-ensemble propre) d'une clé candidate. (Un attribut non premier n'appartient à aucune clé candidate.)

Notez que lorsqu'une table 1NF n'a pas de clés candidates composites (clés candidates constituées de plusieurs attributs), la table est automatiquement en 2NF.

Vérifiez si une relation R (A, B, C, D, E) avec FD défini comme { BC ? D, CA ? BE, B ? E } est en 2NF ?

• Comme on peut le voir, la clôture de AC est (AC)+ = {A, C, B, E, D} en appliquant l'algorithme d'appartenance. Mais aucun de ses sous-ensembles ne peut déterminer tous les attributs de la relation par eux-mêmes, donc AC est la clé candidate pour cette relation. De plus, ni A ni C ne peuvent être dérivés d'un autre attribut de la relation, il n'y aura donc qu'une seule clé candidate qui est {AC}.
• Ici {A, C} sont les attributs premiers et {B, D, E} sont les attributs non premiers.
• La relation R est déjà en 1ère forme normale car un SGBD relationnel en 1NF n'autorise pas les attributs multivalués ou composites.

AVANT JC ? D est en 2ème forme normale car BC n'est pas un sous-ensemble approprié de la clé candidate AC,

CA ? BE est en 2ème forme normale car AC lui-même est la clé candidate, et

B ? E est en 2e forme normale B n'est pas un sous-ensemble approprié de la clé candidate AC.

Ainsi, la relation donnée R est dans la 2e forme normale.

Troisième forme normale

Une table est dite en 3NF si et seulement si pour chacune de ses dépendances fonctionnelles.

X → A , au moins une des conditions suivantes est vérifiée :

• X contient A (c'est-à-dire que X → A est une dépendance fonctionnelle triviale), ou
• X est une super clé, ou
• A est un attribut principal (c'est-à-dire que A est présent dans une clé candidate)

Une autre définition de 3NFénonce que chaque attribut non clé de R est dépendant de manière non transitive (c'est-à-dire directement dépendant) de la clé primaire de R. Cela signifie qu'aucun attribut non premier (ne faisant pas partie de la clé candidate) ne dépend fonctionnellement d'autres attributs non premiers. S'il existe deux dépendances telles que A ? B et BC, alors à partir de ces DF, on peut dériver A ? C. Cette dépendance AC est transitive.

Exemple de 3NF :

Considérez la relation suivante Order (Order#, Part, Supplier, UnitPrice, QtyOrdered) avec l'ensemble de FD donné :

Commande# ? Pièce, fournisseur, quantité commandée et fournisseur, pièce ? Prix ​​unitaire)

Ici, Order# est la clé de la relation.

En utilisant les axiomes d'Amstrong, on obtient

Commande# ? Pièce, Commande ? Fournisseur et Commande ? QtéCommandée.

Commande# ? Pièce, Fournisseur et Fournisseur, Pièce ? Prix ​​unitaire, les deux donnent le numéro de commande ? Prix ​​unitaire.

Ainsi, nous voyons que tous les attributs non premiers dépendent de la clé (Order#). Cependant, il existe une dépendance transitive entre Order# et UnitPrice. Donc cette relation n'est pas en 3NF. Comment fait-on en 3NF ?

Nous ne pouvons pas stocker le prix unitaire d'une pièce fournie par un fournisseur à moins que quelqu'un ne passe une commande pour cette pièce. Nous devrons donc décomposer le tableau pour le faire suivre 3NF comme suit.

Commande (n° de commande, pièce, fournisseur, quantité commandée) et fiche de prix (pièce, fournisseur, prix unitaire).

Maintenant, il n'y a plus de dépendances transitives. La relation est en 3NF.

Lire aussi : SQL pour la science des données

Apprenez des cours de logiciels en ligne dans les meilleures universités du monde. Gagnez des programmes Executive PG, des programmes de certificat avancés ou des programmes de maîtrise pour accélérer votre carrière.

Conclusion

Il y a plus à la normalisation, comme BCNF, 4NF, 5NF et 6NF. En bref, BCNF n'est rien d'autre qu'une extension de 3NF, car la dernière règle de 3NF ne s'applique pas ici. Toutes les dépendances fonctionnelles doivent avoir les attributs clés à gauche et aucun à droite. (BCNF est aussi appelé 3.5NF). Cependant, les formes normales de 4NF et au-delà sont rarement mises en œuvre dans la pratique régulière.

Si vous souhaitez en savoir plus sur le développement full-stack, consultez le programme Executive PG de upGrad & IIIT-B en développement logiciel full-stack, qui est conçu pour les professionnels et offre plus de 500 heures de formation rigoureuse, plus de 9 projets, et affectations, statut d'ancien de l'IIIT-B, projets de synthèse pratiques et aide à l'emploi avec les meilleures entreprises.