sql normalisation Quel est le problème avec une dépendance transitive?




normalisation base de données (4)

Je viens de mettre en place un article qui explique pourquoi les dépendances transitives sont généralement une mauvaise idée: http://www.essentialsql.com/get-ready-to-learn-sql-11-database-third-normal-form-explained-in-simple-english/

J'ai quelques dépendances transitives dans la conception de ma base de données. Mes supérieurs m'ont dit que cela pouvait causer des insectes. J'ai du mal à trouver des ressources qui me diront comment ces dépendances peuvent causer des bogues. Quel genre de problèmes vont-ils causer?

Je ne conteste pas le fait, juste désireux d'apprendre quel genre de problèmes ils peuvent causer.

Modifier pour plus de détails:

De wikipedia:

Dépendance transitive
Une dépendance transitive est une dépendance fonctionnelle indirecte, une dans laquelle X → Z uniquement en vertu de X → Y et Y → Z.


Une façon d'exprimer le 3NF est:

Tous les attributs doivent dépendre de la clé, de la clé entière et de la clé.

La dépendance transitive X-> Y-> Z viole ce principe, entraînant une redondance des données et des anomalies potentielles de modification.

Disons ceci:

  1. Par définition , pour qu'une dépendance fonctionnelle X-> Y-> Z soit également transitive , le X <-Y ne doit pas contenir.
  2. Si Y était une clé, le X <-Y serait maintenu, donc Y ne peut pas être une clé. (Note1)
  3. Puisque Y n'est pas une clé, tout Y donné peut être répété sur plusieurs lignes.
  4. Le Y-> Z implique que toutes les lignes contenant le même Y doivent également contenir le même Z. (FOOTNOTE2)
  5. Répéter le même tuple (Y, Z) sur plusieurs lignes ne fournit aucune information utile au système. C'est redondant .

En bref, puisque Y n'est pas une clé et Y-> Z, nous avons violé la 3NF.

Les redondances conduisent à des anomalies de modification (par exemple, la mise à jour de certains Z, mais pas de tous les «connectés» au même Y, corrompt essentiellement les données, car vous ne savez plus quelle copie est correcte). Cela est généralement résolu en divisant la table d'origine en deux tables, l'une contenant {X, Y} et l'autre contenant {Y, Z}. De cette façon, Y peut être une clé dans la deuxième table et Z n'est pas répété.

D'un autre côté, si le X <-Y tient (c'est-à-dire que X-> Y-> Z n'est pas transitif), nous pouvons conserver une seule table, X et Y étant des clés. Z ne sera pas répété inutilement dans ce scénario.

(FOOTNOTE1) Une clé est un ensemble (minimal) d'attributs qui déterminent fonctionnellement tous les attributs d'une relation. Justification: Si K est une clé, il ne peut y avoir plusieurs lignes avec la même valeur de K, donc toute valeur donnée de K est toujours associée à une valeur précise de chaque autre attribut (en supposant que 1NF). Par définition (voir FOOTNOTE2), «être associé à un seul» est la même chose que «être dans une dépendance fonctionnelle».

(FOOTNOTE2) Par définition , Y-> Z si et seulement si chaque valeur Y est associée précisément à une valeur Z.

Exemple:

En supposant que chaque message a exactement un auteur et que chaque auteur possède exactement un courrier électronique principal, tenter de représenter des messages et des utilisateurs dans la même table entraînerait la répétition des messages électroniques:

MESSAGE                         USER    EMAIL
-------                         ----    -----
Hello.                          Jon     [email protected]
Hi, how are you?                Rob     [email protected]
Doing fine, thanks for asking.  Jon     [email protected]

(En réalité, ce seraient les MESSAGE_ID s, mais gardons les choses simples ici.)

Maintenant, que se passe-t-il si Jon décide de changer son adresse e-mail pour, par exemple, "[email protected]"? Nous devrions mettre à jour les deux lignes de Jon. Si nous ne mettons à jour qu'un seul, nous avons la situation suivante ...

MESSAGE                         USER    EMAIL
-------                         ----    -----
Hello.                          Jon     [email protected]
Hi, how are you?                Rob     [email protected]
Doing fine, thanks for asking.  Jon     [email protected]

... et nous ne savons plus lequel des courriels de Jon est correct. Nous avons essentiellement perdu les données!

La situation est particulièrement mauvaise car il n’ya pas de contrainte déclarative que nous pourrions utiliser pour contraindre le SGBD à appliquer les deux mises à jour pour nous. Le code client aura des bogues et sera probablement écrit sans tenir compte des interactions complexes pouvant survenir dans un environnement concurrent.

Cependant, si vous divisez la table ...

MESSAGE                         USER
-------                         ----
Hello.                          Jon 
Hi, how are you?                Rob 
Doing fine, thanks for asking.  Jon 

USER    EMAIL
----    -----
Jon     [email protected]
Rob     [email protected]

... il n'y a plus qu'une seule ligne qui connaisse l'e-mail de Jon, l'ambiguïté est donc impossible.

BTW, tout cela peut être considéré comme une autre expression du principe DRY .


S'il existe des dépendances transitives dans votre table, elles ne sont pas compatibles avec 3NF; il y a donc une forte probabilité qu'il y ait des données redondantes dans votre table. Cochez this pour clarifier ce concept.


Je vais expliquer par un exemple:

-------------------------------------------------------------------
|  Course  |    Field     |   Instructor   |  Instructor Phone    |
-------------------------------------------------------------------
|  English |  Languages   |  John Doe      |     0123456789       |
|  French  |  Languages   |  John Doe      |     0123456789       |
|  Drawing |  Art         |  Alan Smith    |     9856321158       |
|  PHP     |  Programming |  Camella Ford  |     2225558887       |
|  C++     |  Programming |  Camella Ford  |     2225558887       |
-------------------------------------------------------------------
  • Si vous avez un Course vous pouvez facilement obtenir son cours d' Instructor Course ->Instructor .
  • Si vous avez un Instructor vous ne pouvez pas suivre son Course car il pourrait enseigner différents cours.
  • Si vous avez un Instructor vous pouvez facilement obtenir son Phone si Instructor->Phone .

Cela signifie que si vous avez un Course vous pouvez obtenir le Instructor Phone l' Instructor Phone qui signifie Course ->Instructor Phone (c.-à-d. Dépendance transitive)

Maintenant pour les problèmes:

  1. Si vous supprimez les cours en French et en English , vous supprimerez également leur instructeur John Doe et son numéro de téléphone sera définitivement perdu.
  2. Il n'y a aucun moyen d'ajouter un nouvel Instructor à votre base de données, sauf si vous ajoutez un Course pour lui en premier, ou vous pouvez dupliquer les données dans un Instructors table ce qui est encore pire.
  3. Si l'instructeur John Doe modifie son numéro de téléphone, vous devrez alors mettre à jour tous les cours qu'il enseigne avec les nouvelles informations, ce qui peut entraîner des erreurs.
  4. Vous ne pouvez pas supprimer un instructeur de votre base de données à moins de supprimer tous les cours qu'il enseigne ou de définir tous ses champs sur null.
  5. Et si vous décidiez de conserver la date de naissance de vos instructeurs? Vous devrez ajouter un champ Birth Date à la table Courses . Cela semble-t-il logique? Pourquoi conserver les informations d'un instructeur dans la table des cours en premier lieu.




transitive-dependency