Ceci est la troisième partie de la série, Five Normal Forms. Les titres des deux premières parties (tutoriels) sont First Normal Form, suivi de Second Normal Form. Dans cette partie de la série, la troisième forme normale est expliquée.
L'explication suit le fil de l'histoire : Un père est décédé et a laissé de l'argent à son fils. Le fils a décidé d'investir l'argent dans un dépanneur. Un dépanneur, également connu sous le nom de dépanneur, est un petit commerce de détail qui reçoit des fournisseurs des articles de tous les jours et les vend aux particuliers du quartier.
À ce stade, la boutique est déjà approvisionnée et certaines ventes ont déjà été réalisées. Le fils, qui est le propriétaire de l'entreprise, a des employés, appelés commis dans ce tutoriel. Le propriétaire et tout employé peuvent s'approvisionner et réaliser des ventes après avoir enregistré les produits.
Cependant, avant l'ouverture de la boutique, ni le propriétaire ni les employés ne connaissaient les formulaires normaux. Ainsi, ils enregistraient tout comme des transactions dans un tableau et un cahier. Ils n'avaient pas d'ordinateur.
Vous, le lecteur, avez terminé les cinq parties de cette série de didacticiels ; vous êtes maintenant un développeur de base de données. Le propriétaire du dépanneur est votre ami. Vous avez visité le magasin il y a deux jours et avez formé le propriétaire et les employés à produire une table dans sa première forme normale. Vous avez également visité la boutique hier et les avez formés sur la façon de créer une table dans la deuxième forme normale à partir de la première forme normale.
Aujourd'hui, vous venez d'arriver à la boutique pour une visite afin de les former à la réalisation d'un tableau en troisième forme normale à partir de la deuxième forme normale. Toutes les tables qu'ils ont actuellement sont dans la deuxième forme normale. Les tableaux (par nom et en-têtes de colonne) sont :
Produits (ID produit, ID catégorie, produit)
Catégories (categoryID, catégorie)
Ventes (ID de vente, client, employé, date)
SaleDetails(saleID, productID, numberSold, sellingPrice)
Commandes (numéro de commande, fournisseur, employé, date)
Détails de la commande (numéro de commande, numéro de produit, numéro acheté, prix de revient)
Les touches simples ou composées sont soulignées.
Après avoir résumé ce qui a été enseigné au cours des deux jours précédents et avant que vous ne puissiez faire quoi que ce soit, le propriétaire demande :
« Qu'en est-il des numéros de téléphone, des adresses, etc., pour les clients et les employés ?
Qu'en est-il de la quantité en stock, du niveau de réapprovisionnement, etc., pour les produits ?
Ont-ils besoin de leurs propres tables séparées, ou devraient-ils être intégrés aux tables actuelles ? »
Vous, le développeur de la base de données, répondez :
« Félicitations, propriétaire ! Vous avez indirectement introduit le problème de la troisième forme normale.
Vous continuez.
Autres colonnes nécessaires
Les autres colonnes nécessaires sont d'abord ajoutées aux tables précédentes, qui sont en 1NF et 2NF. Certains des noms de colonne précédents sont modifiés.
Au minimum, le tableau Catégories doit contenir les colonnes suivantes :
Catégories (categoryID, categoryName, description)
La description est un court paragraphe décrivant la catégorie. Cette table de catégories est déjà en 1NF, 2NF et 3NF. Le 3NF est expliqué ci-dessous :
Au minimum, la table Produits doit contenir les colonnes suivantes :
Produits (identifiant du produit, identifiant de la catégorie, identifiant du fournisseur, nom du produit, prix unitaire, quantité en stock, niveau de commande)
Au fur et à mesure que chaque produit est vendu, un faible niveau (nombre) de produits sera atteint lorsque le produit devra être commandé à nouveau, de sorte que les clients ne doivent pas venir au magasin et ne pas avoir le produit. Une telle absence n'est pas bonne pour les affaires. quantitéInStock est le nombre d'un produit particulier en stock. Cela inclut ce qui est dans le magasin et ce qui est sur l'étagère.
categoryID et supplierID sont des clés étrangères. C'est pourquoi ils ont un trait de soulignement au lieu d'un simple trait de soulignement. La clé étrangère est expliquée ci-dessous. Dans la partie précédente de la série (Second Normal Form), categoryID faisait partie de la clé primaire avec un seul soulignement en raison de la façon dont il a été obtenu. Cependant, d'après l'explication ci-dessous, il serait clair que l'ID de catégorie doit être une clé étrangère (avec un tiret souligné).
Cette table de produits est déjà en 1NF, 2NF et 3NF. Voyez pourquoi il est en 3NF ci-dessous :
Au minimum, la table SaleDetails doit contenir les colonnes suivantes :
SaleDetails (saleID, productID, unitSellingPrice, quantité, remise)
La valeur de remise devrait être nulle la plupart du temps. Une remise est la remise que le magasin accorde à un client.
Au minimum, la table OrderDetails doit contenir les colonnes suivantes :
OrderDetails (numéro de commande, numéro de produit, prix unitaire, quantité, remise)
La valeur de remise devrait être nulle la plupart du temps. La remise ici est la remise que le fournisseur accorde au magasin.
Comme on le voit ci-dessous, la table Produits peut être considérée en 2NF ou 3NF. Les tables Sales et Order ont le problème de 3NF. Seul le tableau des ventes sera utilisé pour expliquer le problème et la solution. Le 3NF pour le tableau des commandes et le tableau des produits suivent un raisonnement similaire et seraient simplement cités.
Lors de l'ajout de colonnes, la table Sales serait :
Ventes (saleID, dateSold customerName, téléphone, adresse, ville, région, code postal, pays, employé)
Sept colonnes ont remplacé la colonne client dans le tableau d'origine. Étant donné que les clients sont des personnes du voisinage, les cellules des colonnes ville, région (état), code postal et pays peuvent être laissées vides, bien qu'elles ne le soient pas dans cet article.
Cette table des ventes est toujours en 2NF car les règles 1NF et 2NF n'ont pas été violées. Cependant, il faut savoir que dans une ligne de table Sales, le client (nom) a été remplacé par sept cellules de ligne client.
Remarque : une cellule d'adresse contient le numéro de maison, le nom de la rue ou de la route et le nom de la ville, tous séparés par des virgules. Une ville peut être considérée comme composée de plusieurs communes. Bien que des virgules séparent ces composants de chaîne particuliers, ils forment une valeur de cellule et non trois valeurs de cellule.
La colonne des employés doit également être remplacée par sept de ces colonnes. Cependant, cela n'est pas fait dans ce didacticiel pour économiser du temps et de l'espace d'enseignement. Ainsi, une table Sales avec des données peut être :
Tableau des ventes – 2NF – Sans ID client
La colonne SaleID de type de données est un entier ou, mieux, une incrémentation automatique. Le type de données de la colonne dateSold est une date et non un nombre car il contient le caractère '/', qui n'est pas un chiffre. Le type de données pour le reste des colonnes, y compris la colonne de téléphone, est chaîne (ou texte). La valeur du téléphone comporte le caractère '-', qui n'est pas un chiffre.
Notez que pour chaque ligne, le client (nom), comme dans la partie précédente de la série, a été remplacé par sept cellules, dont l'une est toujours le nom du client. Cela signifie que les données client sont une entité. Actuellement, le nom du client identifie ses six autres données à la suite. Si cette table est programmée, il conviendra d'identifier l'entité client dans chaque ligne par un entier (pas d'auto-incrémentation). Dans ce cas, une colonne customerID doit précéder le customerName. Le tableau précédent devient :
Tableau des ventes – 2NF – Avec ID client
Il existe trois ID client : 1, 2 et 3, 1 apparaissant cinq fois pour John Smith, 2 apparaissant deux fois pour James Taylor et 3 apparaissant une fois pour Susan Wright.
Notez que certains ID client et leurs dépendances se répètent.
Règles pour la troisième forme normale
Une table est en troisième forme normale si elle respecte les règles suivantes :
- Il devrait déjà être dans la deuxième forme normale.
- Et il ne devrait pas avoir de dépendance transitive.
Puis l'un des commis (employés) demande : « Qu'est-ce qu'une dépendance transitive ? ». Et vous, le développeur de la base de données, répondez : 'C'est une bonne question !'
Dépendance transitive
Il est vrai que dans une ligne, SaleID identifie toutes les valeurs de la ligne ; cependant, customerID identifie ses sept valeurs de données mais n'identifie pas le reste des valeurs identifiées par SaleID dans cette ligne. En d'autres termes, le SaleID dépend de dix valeurs de cellule dans chaque ligne. Toutefois, l'ID client dépend de sept valeurs de cellule dans la même ligne, mais l'ID client ne dépend pas de l'ID de vente et des autres valeurs dont dépend l'ID de vente.
Une telle dépendance pour le custumerID est une dépendance transitive. Et customerID est appelé une clé étrangère et est souligné par un tiret dans cette série de didacticiels, Les cinq formes normales.
Supposons qu'un attribut non principal (valeur de cellule non principale) dépende d'autres attributs non principaux, et que l'attribut non principal en question (par exemple, customerID et ses dépendances) ne dépend pas de la clé primaire et du reste de la cellule valeurs dans la ligne. Alors c'est la dépendance transitive.
La table Sales précédente avec la clé étrangère et ses dépendances, causerait des problèmes de comptabilité (anomalies).
Tableau des ventes de 2NF à 3NF
Pour résoudre le problème posé par la clé étrangère et ses dépendants, supprimez la clé étrangère et ses dépendants, pour former une nouvelle table sans répétitions. Cependant, même si la clé étrangère ne dépend pas de la clé primaire, la clé primaire dépend de la clé étrangère. Ainsi, une copie de la clé étrangère doit rester dans la table parent. Le nouveau tableau des ventes, à ce stade, est conforme aux normes 1NF, 2NF et 3NF ; c'est une table mère. La nouvelle table enfant de la table Sales précédente est également conforme aux normes 1NF, 2NF et 3NF. Le nom de la table enfant avec la clé étrangère et ses dépendances est Customers. Si un nom approprié ne peut pas être trouvé, alors quelque chose s'est mal passé avec l'analyse. La nouvelle table des ventes en 3NF est :
Tableau des ventes finales en 3NF
Ce tableau en 3NF a le même nombre de lignes que celui en 2NF mais avec moins de colonnes.
La notation de table pour cette table finale des ventes dans 3NF est :
Ventes (ID de vente, date de vente, ID de client, ID d'employé)
Le saleID est la clé primaire avec un seul trait de soulignement. customerID est une clé étrangère, soulignée par un tiret. employeeID est également une clé étrangère soulignée par un tiret. Notez que la situation de l'employé dans la table Ventes de 2NF est la même que la situation du client. Le employeeID et ses propres dépendances doivent être retirés pour former une autre table ; une copie de l'employeeID reste.
Remarque : saleID, customerID et employeeID ne forment pas une clé composite. saleID dépend de l'ID client et de l'ID employé.
La relation entre saleID et customerID est plusieurs à un.
La table des clients en 3NF
Cette table comporte trois lignes au lieu de 9 lignes dans la table 2NF Sales. Dans ce tableau, customerID est une clé primaire. C'est la même chose que la clé étrangère dans la table Sales, mais sans répétitions. La clé étrangère de la table Sales et la clé primaire de la table Customer lient les deux tables.
Les lignes répétées dans la table Customer ont été supprimées afin de ne pas enfreindre 1NF.
Comme le lecteur peut le voir, mettre une table en 3NF résoudrait également le problème des lignes répétées (redondance).
La notation de table pour la table des clients est :
Clients (ID client, nom du client, téléphone, adresse, ville, région, code postal, pays)
La Table des Produits Revisitée
Le tableau des produits donné ci-dessus sous forme de notation est :
Produits (identifiant du produit, identifiant de la catégorie, identifiant du fournisseur, nom du produit, prix unitaire, quantité en stock, niveau de commande)
La clé primaire ici est productID. categoryID et supplierID sont des clés étrangères. Semblable à la table Customer, il existe une table Categories, où categoryID est la clé primaire, et il y a une table Supplier, où supplierID est la clé primaire.
Si les valeurs des cellules pour unitPrice, QuantityInStock et reorderLevel restent fixes, alors la table Products, telle qu'elle est, est vraiment en 3NF. Si ces valeurs changent, alors la table Products, telle qu'elle est, est en 2NF. Dans cette partie de la série de didacticiels, on suppose que ces valeurs restent fixes dans le temps.
Tous les tableaux
Toutes les tables sont maintenant en 3NF. Ils sont affichés comme suit :
Employés (identifiant de l'employé, nom, téléphone, adresse, ville, région, code postal, pays, date de naissance, date de location, date de libération)
Fournisseurs (ID fournisseur, nom, téléphone, adresse, ville, région, code postal, pays)
Produits (identifiant du produit, identifiant de la catégorie, identifiant du fournisseur, nom du produit, prix unitaire, quantité en stock, niveau de commande)
Catégories (categoryID, categoryName, description)
Ventes (ID de vente, date de vente, ID de client, ID d'employé)
SaleDetails(saleID, productID, numberSold, sellingPrice)
Clients (ID client, nom du client, téléphone, adresse, ville, région, code postal, pays)
Commandes (numéro de commande, date de vente, numéro de fournisseur, numéro d'employé)
Détails de la commande (numéro de commande, numéro de produit, numéro acheté, prix de revient)
Jusqu'à neuf tables professionnelles ont été produites à partir d'une seule table produite par des novices pour éviter les redondances et les problèmes de comptabilité (anomalies d'insertion, de suppression et de mise à jour). La table des débutants entraînerait à elle seule des pertes financières.
Tester le personnel
À ce stade, tous les employés, y compris le propriétaire, doivent avoir compris 1NF, 2NF et 3NF. Cependant, ils doivent être testés. Tous, y compris le propriétaire, s'assiéront à des endroits différents et passeront le test. Le test composé d'une question, durera une heure, et se déroule comme suit :
Question : En utilisant les règles pour 1NF, 2NF et 3NF, prouvez que les neuf tables ci-dessus sont déjà en première forme normale, deuxième forme normale et troisième forme normale. Les clients et les fournisseurs ne doivent pas nécessairement être des entités réelles. Les données des tableaux doivent sauvegarder les notations du tableau.
Pendant qu'ils terminent le test, vous, en tant que développeur de la base de données, sortez prendre une collation et une bière, pour revenir après une heure.
Le futur proche et lointain
Pendant que vous, le développeur de la base de données, êtes absent, vous réfléchissez également aux conseils à leur donner s'ils réussissent tous le test.
De plus, pendant que vous les formiez, et maintenant qu'ils passent le test, les clients vont et viennent sans être servis. Ce n'est pas bon pour les affaires, et vous, le développeur de la base de données, le savez. Certains clients peuvent se rendre dans les magasins concurrents et ne jamais revenir.
Vous, le développeur de la base de données, avez 30 ans. Le propriétaire, comme votre ami, a également 30 ans. Les commis (employés) ont entre 18 et 24 ans. Toutes les qualités dont ils avaient besoin pour travailler pour le propriétaire étaient : être en bonne santé, savoir lire et écrire, savoir additionner, soustraire, multiplier et diviser. , et être capable d'utiliser l'ordinateur et Internet.
Lorsqu'une table est en 3NF, la plupart des vulnérabilités ont été supprimées de la base de données. De nombreuses bases de données commerciales ne vont pas au-delà de 3NF, et les cabinets ou entreprises sont à l'aise.
Donc, si tous réussissent le test, vous demanderez aux greffiers d'aller continuer à travailler. Vous leur conseillerez également d'économiser une partie de leurs salaires afin qu'ils puissent posséder leurs commerces de proximité. Vous continuerez demain à former uniquement le propriétaire en 4NF et 5NF. Avec la connaissance de 4NF et 5NF, toutes les vulnérabilités connues sont supprimées.
Évaluation
Au bout d'une heure, vous, le développeur de la base de données, revenez. Vous marquez leurs scripts. Une excellente nouvelle ! Ils ont tous, y compris le propriétaire, 100 % chacun. Hourra! C'est excellent !
Alors félicitations à vous tous : le professeur et les élèves.
Il n'y a plus rien à faire dans ce tutoriel à part conclure.
Conclusion
Un tableau est en première forme normale s'il ne viole aucune des règles suivantes :
- Toutes les colonnes d'un tableau doivent avoir des noms d'en-tête uniques.
- Chaque cellule ne doit avoir qu'une seule valeur.
- Les valeurs stockées dans une colonne doivent être du même type.
- Les rangées doivent être distinctes.
- L'ordre des colonnes ou des lignes n'a pas d'importance.
Un tableau est en deuxième forme normale s'il ne viole aucune des règles suivantes :
- La table doit déjà être en première forme normale.
- Il ne doit pas y avoir de dépendance partielle.
Une table est en troisième forme normale, si elle ne viole aucune des règles suivantes :
- Il doit déjà être dans la deuxième forme normale.
- Et il ne doit pas avoir de dépendance transitive.
Vous, le développeur de la base de données, dites aux commis qu'ils en ont assez appris. Vous leur prodiguez des conseils et leur demandez de reprendre le travail et de rester à leur poste par défaut.
Vous fixez un rendez-vous avec le propriétaire uniquement, qui aura lieu dans son bureau demain pour une formation sur 4NF et 5NF.
