Deuxième forme normale les cinq formes normales

Ce tutoriel explique le deuxième formulaire normal, lié à la base de données relationnelle. C'est la deuxième partie de la série: les cinq formes normales. L'explication de ces cinq formes normales suit une histoire, qui commence comme suit: un père est mort et a laissé de l'argent pour son fils. Le fils a décidé d'utiliser l'argent pour ouvrir un magasin de commodité. Le fils a employé quelques travailleurs (commis). Tout dans la boutique est déjà approvisionné, et le personnel a commencé à vendre à certains clients. Au début du fonctionnement de la boutique, qui n'est pas longtemps, le fils, qui est le propriétaire, et ses travailleurs, ne savaient rien des formes normales.

Vous êtes déjà un ami du fils, le propriétaire. Vous avez enfin terminé cette série de tutoriels sur les formulaires normaux, vous savez tout sur les formulaires normaux, et vous êtes devenu un développeur de base de données. Vous avez visité la boutique de votre ami hier et vous avez remarqué qu'ils n'avaient qu'un tableau de transaction, qui ne se conformait pas au premier formulaire normal. Vous avez ensuite enseigné au propriétaire et à ses greffiers sur la façon de produire le tableau des transactions sous la première forme normale. Ils ont compris et la table qu'ils ont maintenant dans la première forme normale est:

Ceci est le tableau des transactions. Il existe une colonne pour la clé primaire, qui est transid (auto-imprégnement).

Étant donné que tout premier formulaire normal peut avoir des vulnérabilités, vous leur avez visité (la boutique) aujourd'hui pour les former sur le deuxième formulaire normal afin de réduire les vulnérabilités.

Avant de commencer à faire quoi que ce soit, le propriétaire a demandé:

«Ce tableau de transaction actuel n'est-il pas trop grand?

J'ai entendu dire que les entreprises qui n'utilisent pas d'ordinateurs enregistrent leurs données dans différents livres.

Je pense que ce tableau de transaction se compose en fait de deux tables de transaction plus petites, qui peuvent avoir de nouveaux noms de table de vente et de table de commande."

Vous répondez en souriant:

«Oui, la table de transaction actuelle est trop grande."

Vous l'avez fait féliciter d'être positivement curieux, surtout concernant son entreprise. Vous êtes d'accord avec ses pensées. Vous continuez à dire qu'il y a en fait trois tables principales là-bas: la table de vente, la table de commande et la table de produits. Lorsque les tables de vente et de commande seront identifiées, ils remplaceront le tableau de transaction. La table de vente et la table de commandes sont des tables de transactions plus petites (divisées en moitié) en elles-mêmes. Le tableau des produits est clairement une entité. Il doit être obtenu à partir de la table des transactions importante précédente.

Cependant, ces trois tables obtenues à partir de la grande table précédente seraient toujours sous la première forme normale. Le deuxième formulaire normal aborde le problème de la répétition (redondance), et ce n'est pas ce qui va se passer ici.

Remarque: Le deuxième formulaire normal peut ne pas résoudre tous les problèmes de répétition (redondance).

Vous, le développeur de la base de données et un ami du propriétaire, continuez à produire les trois tables avec leur participation comme suit:

Les principales entités

Les principales entités sont les trois principales tableaux mentionnés précédemment. Dans la colonne précédente, avec l'en-tête, l'action a une vente ou une commande. La vente signifie que le produit de cette ligne a été vendu à un client. La commande signifie que le produit de cette ligne a été commandé auprès d'un fournisseur. Un magasin de commodité doit commander des produits avant de les vendre.

Les colonnes correspondant aux trois tables sont les suivantes:

Ventes (SaleID, produit, client, employé, vente de vente)

Commande (OrderId, Product, Fournisseur, Employé, CostPrice)

Produits (productId, produit, catégorie)

Chaque notation de table a une clé primaire soulignée. Chacune de ces tables est toujours sous la première forme normale. Et donc, chaque numéro de clé est directement lié à ses valeurs de ligne.

Dans le tableau des ventes, les Saleids ne sont pas les copies des transides. Dans le tableau des commandes, les OrderIDS ne sont pas non plus les copies des transides. Chacune des colonnes SaleID et OrderID est automatiquement, à partir de 1. La table de transaction et ses ID de ligne ne sont plus importants car toutes les informations du tableau de transaction sont maintenant dans ces trois tables. Les colonnes SaleID et OrderID remplacent la table de transaction, mais pas en copie. La table de vente n'a pas la colonne du fournisseur et le tableau de commande n'a pas la colonne client, qui était ensemble dans le tableau des transactions. De plus, aucune des deux tableaux n'a la colonne d'action depuis que la vente et la commande sont maintenant séparées, et les deux valeurs de vente et de commande ne sont plus nécessaires.

La colonne de catégorie, qui se trouvait dans la colonne de transaction, n'est ni dans le tableau des ventes ni dans le tableau des commandes. C'est dans la table des produits. En effet. La colonne de catégorie doit sortir de la table de transaction avec la colonne du produit pour obtenir le tableau des produits. Ce raisonnement a quelque chose à voir avec la dépendance transitive dans la production de la troisième forme normale qui sera discutée dans le prochain tutoriel.

Le tableau des produits obtenu à partir du tableau de transaction précédent est:

Table de produits

Notez que ce tableau n'a pas de répétition du nom du produit (vers le bas) qui aurait pu être dans le tableau des transactions. De plus, chaque valeur de produit est présente et chaque valeur de catégorie est également présente.

Ce tableau est toujours en 1NF. La dépendance des valeurs d'une ligne est liée à la clé primaire uniquement, jusqu'à présent. Les valeurs de la colonne de catégorie se répètent lorsque la colonne est descendante. La confiserie se répète deux fois. «Boisson gazeuse» se répète quatre fois. «Produit laitier» se répète trois fois. La répétition est redondante et provoque des problèmes comptables. Mettre une table en 2NF signifie éliminer de nombreuses répétitions. Cette élimination des répétitions ne se fait pas arbitrairement.

Le tableau de vente obtenu à partir du tableau des transactions précédent est:

Table de vente

Quatre nouvelles lignes sont ajoutées, et il y a eu une certaine modification pour améliorer l'explication. Une colonne de date est introduite pour la même raison. Maintenant, il y a 15 lignes dans ce tableau de vente au lieu de 11 pour le tableau des transactions.

Règles commerciales

Qu'est-ce qu'une vente? Si le même employé vend à un client particulier à la même date au moins un produit, c'est-à-dire une vente. Même le même jour (date), si un client vient deux fois et est servi par deux employés différents, alors ce sont deux ventes. Si un client arrive deux fois le même jour et est servi par le même employé, même si les deux ensembles de produits sont différents, les deux ventes forment une vente. En une seule vente, un client peut acheter un ou plusieurs produits. En d'autres termes, dans une vente, le trio (client, employé et date) doit être le même. Une fois que l'une de ces valeurs de trio change, c'est une autre vente. Différentes ventes sont identifiées par différentes Saleides dans le tableau précédent. Et donc, les Saleids se répètent. Différentes valeurs de colonne se répètent dans leurs colonnes.

Dans la première vente qui a deux rangées et le même Saleid, John Smith, un client, a acheté un bonbons et un sprite à Jacob Jones à la même date.

Le matin du 09/06/22, James Taylor, un client, est venu et a acheté deux yaourts et un Coca-Cola. C'est une vente. Il faut trois rangées dans la table avec le même SaleID.

Le même jour, mais dans l'après-midi, le même James Taylor est venu et a acheté Pepsi, mais à un autre employé qui est Peter Lewis. Le trio a maintenant un changement avec un employé différent. Et donc, c'est une vente différente causée par un changement dans l'un des trio. Puisqu'il s'agit d'une vente différente, il a une ligne différente dans le tableau avec un SaleID différent.

Le 09/08/22, Susan Wright, un client, est venu et a acheté deux fromages et un lait à Mary Baker. C'est une vente car le trio reste le même (dans les trois rangées). Cependant, il faut trois rangées dans le tableau. Puisque le trio reste le même, le Saleid reste également le même.

Le reste des rangées vers le bas dans la table n'a pas la même répétition de venteid. Ce tableau est toujours en 1NF. Jusqu'à présent, la dépendance pour les valeurs d'une ligne n'est toujours liée qu'à la clé principale de cette ligne. Chaque colonne a des valeurs répétées. La répétition dans une colonne ne doit pas nécessairement être dans des cellules consécutives qui descendent.

Voir quand considérer la colonne de prix ou de vente de vente dans la section tutoriel de la gestion des répétitions. Mettre une table en 2NF résout le problème des répétitions des ensembles communs (redondance) entre les lignes.

La table de commande obtenue à partir du tableau de transaction précédent est:

Table de commande

Ce tableau est toujours sous la première forme normale. Il y a la possibilité que toute valeur dans une colonne de répéter sous sa colonne. Ces répétitions sont traitées dans ce tutoriel pour avoir la deuxième forme normale du tableau.

À ce stade, vous, le développeur de la base de données, avez convenu avec la suggestion du personnel de mettre la table de transaction dans des tables plus petites. Et vous mettez la table de transaction dans des tables (entités) plus petites de manière pratique. Le personnel, y compris son propriétaire, croit maintenant qu'ils ont également le potentiel de comprendre complètement les formes normales et sont prêts à en savoir plus parce que leur suggestion s'est matérialisée.

Cependant, vous, le développeur de la base de données, insiste pour eux que le tableau de transaction d'origine n'existe plus et a été remplacé par les trois tables plus petites. Les ventes et le tableau de commandes remplacent essentiellement le tableau des transactions. Le transid (ID de transaction) n'est plus pertinent. Il est remplacé par le SaleID et OrderID dans deux tables différentes.

Les principales entités qui sont désormais des tables plus petites de la table de transaction d'origine sont: le tableau des produits, la table de vente et la table de commande. Vous, le développeur de la base de données, continuez à expliquer et à leur insister sur le fait que ces tables nouvelles mais plus petites sont toujours sous la première forme normale. Le remplacement d'une table par ses principaux tables entités n'est pas la normalisation car aucun des types de définitions de dépendance n'est utilisé pour décomposer le grand tableau. Vous, le développeur de la base de données, continuez à mettre les trois tables dans la deuxième forme normale comme suit:

Traiter les répétitions

La table des produits

Dans le tableau des produits, les valeurs de la colonne de catégorie se répètent. Tous les noms (valeurs) de la colonne de catégorie doivent être supprimés de la table de produits dans un tableau des catégories où il n'y aurait pas de répétitions limitées. Le tableau des catégories devient:

Table de catégories

Le tableau des catégories n'a plus d'élément tel que la «boisson gazeuse» qui répète. Ce tableau est plus court verticalement que son placement dans le tableau des produits précédents.

Toute table a besoin d'une clé primaire. Jusqu'à présent, le tableau des catégories se compose d'une colonne où toutes les valeurs sont uniques et il n'y a pas de valeur de cellule ou de null vide. Cette colonne peut être la colonne de clé principale pour le tableau des catégories. Cependant, il pourrait être préférable d'avoir une clé primaire automatique. Le tableau des catégories modifiées suivantes montre ceci:

Tableau des catégories - 2NF

Ceci est le tableau des catégories finales. Il est maintenant en 1NF et 2NF. Qu'en est-il de la table des produits d'origine? Une nouvelle table de produits doit être créée. Dans le nouveau tableau, tout nom de catégorie dans la table d'origine sera remplacé par l'ID correspondant dans le tableau des catégories. Ainsi, le tableau des produits devient:

Tableau de produits - 2NF

La colonne de catégorie est remplacée par la colonne Catégoried. Les informations pour les valeurs de catégorie sont toujours là dans le tableau des produits en tant que catégorieides. La colonne de catégorie est placée juste après la colonne ProductId plutôt qu'après la colonne du produit pour une meilleure présentation. Cette table n'est pas plus courte que la table des produits d'origine mais il a toujours un avantage.

«Quelle est la clé principale du tableau des produits?»Demanda par l'un des commis. Notez que dans chaque ligne, le ProductId et CatégoryId dépendent tous deux de la valeur du nom du produit (valeur). Si le productID ou le catégorieID est modifié pour n'importe quelle ligne, la nouvelle combinaison de ProductId et CatégoryId pointerait vers un nom de produit différent (valeur). En d'autres termes, un nom de produit (valeur) dans la ligne est lié à la fois au produit et à la catégorie de cette ligne. En raison de cette dépendance (dépendance fonctionnelle) de la combinaison de productide et catégorieid sur un nom de produit particulier (valeur), le productid et le catégoriement forment la clé principale.

Lorsqu'une clé est une combinaison de plus d'une colonne, la clé est appelée une clé composite. La notation du tableau pour ce nouveau tableau de produits est:

Produits (ProductId, catégorieID, produit)

La relation entre le produit et catégorieID est plusieurs à un.

Chaque nom de colonne pour la clé primaire (clé composite cette fois) est souligné. Les valeurs catégories dans le tableau des produits et les valeurs catégories dans le tableau des catégories font les correspondances exactes entre les deux tables.

La notation du tableau pour le tableau des catégories est:

Catégories (catégorieId, catégorie)

Les nouvelles tables qui ont remplacé la table des produits d'origine sont: Table des catégories et table de produits (même nom). Ces tables sont maintenant sous la première forme normale et la deuxième forme normale. Voir les règles réelles pour la deuxième forme normale dans la discussion suivante.

Le tableau des ventes

Les sections de lignes où se répète le SaleID mais les valeurs des cellules de colonne ne se répètent pas, doivent être supprimées du tableau de vente et être placées dans un nouveau tableau. Dans le nouveau tableau, les sections de ligne (colonnes) identiques où les valeurs des cellules se répètent avec le SaleID dans le tableau de vente ne seront pas incluses. C'est-à-dire que toute valeur cellulaire ou section de ligne telle que le trio (client, employé et date) qui doit se répéter avec le même SaleID dans le tableau de vente ne sera pas inclus dans le nouveau tableau même si la répétition n'est qu'une seule fois. Les valeurs de la colonne de produit qui peuvent changer avec le même SaleID dans le tableau des ventes doivent être dans le nouveau tableau. Une nouvelle colonne est introduite qui a le nombre des mêmes produits vendus pour un SaleID particulier. Qui maintient le nouveau tableau en 1NF, le portant à 2NF. La nouvelle table est appelée table Saledetails. Si le développeur ne peut pas trouver un nouveau nom approprié pour la nouvelle table, quelque chose a mal tourné avec son analyse. Le tableau Saledetails devient:

Saledetails - 2NF

Le tableau Saledetails, retiré du tableau des ventes, est maintenant dans la deuxième forme normale ainsi que dans la première forme normale. Le SaleID de la table de vente d'origine doit être inclus dans le tableau Saledetails afin de maintenir la relation entre le tableau de vente d'origine et le nouveau tableau Saledetails. Maintenant, il y a 13 rangées dans le tableau Saledetails au lieu de 15 du tableau de vente d'origine.

Dans le tableau des ventes d'origine, toute colonne dont la valeur n'a pas changé, alors que le SaleID ne changeait pas, est resté dans le tableau de vente d'origine et n'a pas été supprimé. Ce sont essentiellement le trio (client, employé et date) dans cette situation. Les valeurs de la colonne du produit ont changé alors que le SaleID ne change pas, il doit donc être supprimé. Si les valeurs de la colonne de prix ou de vente de vente changent alors que le SaleID ne change pas, il doit également être supprimé.

Tout le monde viendrait-il dans un magasin et n'achèterait-il qu'une seule boîte de lait? Non. Pour tout client qui achète, disons 4 boîtes de lait, le numéro 4 s'intégrerait bien dans la colonne Numberold dans la ligne appropriée.

«Et quelle est la clé principale de la nouvelle table Saledetails?»Demanda par le propriétaire. Ceci est votre réponse en tant que développeur de base de données:

Notez que dans chaque ligne, le SaleID et le produit dépendent tous deux de Numbersold et SellPrice (valeurs). Si le SalesID ou le nom de produit est modifié pour n'importe quelle ligne, la nouvelle combinaison du SaleID et du produit indique une autre ligne de Numbersold et Sellprice. En d'autres termes, une ligne Numbersold et SellPrice est liée à la fois aux valeurs SaleID et aux produits de cette ligne. En raison de cette dépendance (dépendance fonctionnelle) du SalesID et de la combinaison de produits sur une ligne particulière, le SalesID et le produit forment la clé principale. Le produit doit également être souligné.

Lorsqu'une clé est une combinaison de plus d'une colonne, la clé est appelée une clé composite. La notation du tableau pour ce nouveau tableau Saledetails est:

Saledetails (SaleID, produit, nombres, vente de vente)

La relation entre le SalesId et le produit est plusieurs à plusieurs.

«J'ai l'intention d'ordination de la base de données. Puisqu'une table de produits existe déjà avec ProductId, il ne serait pas préférable de remplacer le produit dans le cadre de la clé par ProductId? Ensuite, l'ordinateur utilisera le ProductId à partir de la table SaleDetails et recherchera le nom de produit de la table des produits », fait remarquer le propriétaire.

Vous, le développeur et entraîneur de la base de données, sourit en hochant la tête. Et c'est votre réponse:

«Propriétaire, tu vas bien. Vous comprenez plus rapidement que ce à quoi je m'attendais. Lorsqu'une base de données ne sera dans des livres d'exercice (registres) uniquement, dans la mesure du possible, il aura des noms de texte au lieu d'identification numérotée. Lorsqu'une base de données sera dans un ordinateur, dans la mesure du possible, il aura des ID numérotés au lieu de noms de texte. L'ordinateur connectera les ID numérotés et les noms de texte dans leurs tables et imprimera les noms de texte lorsqu'une requête est publiée.

Permettez-moi d'avoir l'honneur de faire l'informatisation; Mais pour l'informatisation, tu me paieras.»Et donc, la notation du tableau pour le tableau Saledetails devient:

Saledetails (SaleID, ProductId, Numbersold, SelltPrice)

«Ce qui reste de la table de vente?»Demanda par l'un des commis. Les colonnes dont les valeurs n'ont pas changé alors que le Saleid ne changeait pas est resté dans le tableau des ventes. Le SaleID demeure également parce qu'il «régit» chaque ligne dans le tableau des ventes. Le nouveau tableau de vente devient:

Table de vente - Intermédiaire

Les colonnes de produit et de vente de vente où il y a eu un changement de valeur alors que le Saleid ne changeait pas a été supprimé. Maintenant, il y a clairement des lignes complètes en double. De tels doublons ont déjà été comptés et enregistrés dans le tableau Saledetails dans la colonne Numbersold. Dans le tableau réel des saledetails, le nombre est de 2 ou 1. Ainsi, les doublons ne doivent pas être pris en considération dans le nouveau tableau des ventes. Si les doublons sont autorisés, l'une des règles du premier formulaire normal serait violée. Le nouveau tableau de vente devient:

Tableau de vente - 2NF

Ce nouveau tableau de vente final est en 2NF et toujours en 1NF. Aucun Saleid ne se produit plus d'une fois dans ce tableau. Il y a 10 lignes ici, et pas 15, par rapport au tableau de vente d'origine. Ce nouveau tableau de vente est plus court que les lignes d'origine par cinq.

Ce tableau de vente final n'a qu'une seule colonne de clé principale qui est le SaleID. Il est souligné. Les valeurs SaleID dans le tableau des ventes et les valeurs SaleID dans le tableau Saledetails font les correspondances exactes entre les deux tables.

La notation du tableau pour le tableau des ventes est:

Ventes (SaleID, client, employé, date)

Et la notation du tableau pour le tableau Saledetails est:

Saledetails (SaleID, ProductId, Numbersold, SelltPrice)

Les nouvelles tables qui ont remplacé la table de vente d'origine sont: Table et table de vente de Saledetails (même nom). Ces tables sont maintenant sous la première forme normale et la deuxième forme normale. Voir les règles réelles pour la deuxième forme normale dans la discussion suivante:

La table de commande

L'analyse similaire à celle pour le tableau de vente peut être faite pour que le tableau des commandes ait les nouvelles tables de remplacement:

Commande (OrderId, Fournisseur, Employé, date)

et

OrderDetails (OrderId, ProductId, Number Bound, CostPrice)

À ce stade, vous, le développeur de la base de données, vient de terminer illustrer les membres du personnel, y compris le propriétaire sur la façon dont le 2NF est produit à partir du 1NF.

Le propriétaire demande maintenant: «Est-ce que nous allons former le (s) tableau (s) 2NF du tableau 1NF?«Vous, le développeur de la base de données, répond comme suit:

"Hé bien oui. Cependant, quelle que soit la façon dont vous utilisez pour former un 2NF du 1NF doit respecter les règles du 2NF.«Vous continuez à expliquer les règles 2NF.

Règles pour le deuxième formulaire normal

Pour qu'un tableau soit sous la deuxième forme normale, il doit respecter les deux règles suivantes:

1) Le tableau doit déjà être sous la première forme normale.

2) Il ne doit pas y avoir de dépendance partielle.

La dépendance fonctionnelle ou simplement la dépendance est expliquée dans la partie précédente de la série, la première forme normale. L'explication est brièvement répétée ici, puis la dépendance partielle sera expliquée.

Dépendance fonctionnelle

Dans n'importe quelle table dans la première forme normale, une fois qu'une clé primaire est connue, le reste des valeurs de la ligne de la clé primaire peut être récupérée. Par exemple, dans le tout premier tableau de l'exemple précédent, les valeurs du numéro principal du numéro 10 sont: dentifrice, articles de toilette, entreprise de nettoyage, Peter Lewis, Order et 4. Ainsi, le numéro de clé 10 dépend de ces valeurs. Une clé primaire identifie de manière unique toutes ses valeurs.

Dépendance partielle

La dépendance partielle est une situation avec une clé composite où une valeur de clé non primaire en ligne ne peut avoir qu'une partie de la clé composite, e.g. une de ses cellules en fonction de. Dans les tables précédentes avec des clés composites, chaque valeur de clé non primaire dans une ligne a les deux cellules de la clé primaire en fonction. La deuxième règle pour le 2NF dit qu'il ne doit pas y avoir de dépendance partielle. Et il n'y a aucune dépendance partielle dans aucune des tables précédentes.

Les deux cellules de la clé composite dépendent de chaque valeur de la ligne pour toutes les tables ci-dessus avec des touches composites. S'il s'agissait d'une dépendance partielle, une cellule dans la clé composite dépendrait de certaines valeurs dans la ligne, et l'autre cellule de la clé composite dépendrait des autres valeurs de la même ligne.

La production de 2NF de la table de produits et du tableau des ventes comparées

La table de produits a une limite de longueur (descendre). Le tableau de vente n'a pas de limite de longueur car c'est un tableau de transaction. Cependant, cette différence n'est pas ce qui donne nécessairement aux deux tables leurs différentes façons d'obtenir les tables 2NF.

Dans le tableau des produits 1NF donnés, les catégories se répètent vers le bas. La colonne de catégorie est supprimée pour former un nouveau tableau de longueur limitée et la clé composite va à la table parent, la table de produits.

Dans le tableau de vente 1NF donné, l'ensemble SaleID et correspondant d'autres cellules de la même ligne se répète vers le bas. Les colonnes plutôt non répétées ont été supprimées pour former une nouvelle table et la clé composite va à la table enfant, la table SaledeTails. Les ventes et les tables Saledetails sont de longueurs illimitées.

À ce stade, vous, le développeur de la base de données qui forme le personnel, y compris leur propriétaire, vous demandez à tous de vérifier si toutes les nouvelles tables sont vraiment dans les première et deuxième formes normales. Ils devraient le faire avec succès et répondre oui.

Et puis, vous concluez.

Conclusion

Un tableau est dans la deuxième forme normale si elle respecte les règles suivantes:

1) Le tableau doit déjà être sous la première forme normale.

2) Il ne doit pas y avoir de dépendance partielle.

Pour toutes les tables avec des clés primaires composites, toutes les valeurs de clé non primaire en ligne déterminent chacune des clés principales de cette ligne.

Prendre une table de 1NF à 2NF impliquerait de gérer un groupe répétitif majeur (ensemble de cellules).

Bien que certaines vulnérabilités aient été éliminées, un tableau en 2NF a encore d'autres vulnérabilités. Plus de ces vulnérabilités seront traitées dans le prochain tutoriel (article) sur le troisième formulaire normal.

D'après les questions que les membres du personnel posent et les commentaires de leur part, cela montre qu'ils ont compris tout ce qui leur a été enseigné jusqu'à présent. Vous devez les féliciter avant de partir et de revenir pour discuter de la troisième forme normale.