What is wrong with a transitive dependency?

问题:

I have some transitive dependencies in my database design. I have been told by my superiors that these can cause bugs. I am finding it difficult to find resources that will tell me how having these dependencies will cause bugs. What kind of problems will they cause?

I am not disputing the fact, just eager to learn what kind of problems they can cause.

Edit for more details:

From wikipedia :

Transitive dependency
A transitive dependency is an indirect functional dependency, one in which X→Z only by virtue of X→Y and Y→Z.

回答1:

I'll explain by an example:

-------------------------------------------------------------------
|  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       |
-------------------------------------------------------------------

• If you have a Course you can easily get its Instructor so Course ->Instructor.
• If you have an Instructor you can't get its Course as he might be teaching different courses.
• If you have an Instructor you can easily get his Phone so Instructor->Phone.

That means the if you have a Course then you can get the Instructor Phone which means Course ->Instructor Phone (i.e. Transitive dependency)

Now for the problems:

1. If you delete both the French and English courses then you will delete their instructor John Doe as well and his phone number will be lost forever.
2. There is no way to add a new Instructor to your database unless you add a Course for him first, or you can duplicate the data in an Instructors table which is even worse.
3. If Instructor John Doe changes his phone number then you will have to update all Courses that he teaches with the new info which can be very prone to mistakes.
4. You can't delete an Instructor from your database unless you delete all the courses he teaches or set all his fields to null.
5. What if you decide to keep the birth date of your instructors? You will have to add a Birth Date field to the Courses table. Does this even sound logical? Why keep an instructor information in the courses table in the first place.

回答2:

One way to express the 3NF is:

All attributes should depend on the key, whole key and nothing but the key.

The transitive dependency X->Y->Z violates that principle, leading to data redundancy and potential modification anomalies.

---

Let us break this down:

1. By definition, for a functional dependency X->Y->Z to also be transitive, the X<-Y must not hold.
2. If Y was a key, the X<-Y would hold, so Y cannot be a key. (FOOTNOTE1)
3. Since Y is not a key, any given Y can be repeated in multiple rows.
4. The Y->Z implies that all rows holding the same Y must also hold the same Z. (FOOTNOTE2)
5. Repeating the same (Y, Z) tuple in several rows does not contribute any useful information to the system. It is redundant.

In short, since Y is not a key and Y->Z, we have violated the 3NF.

Redundancies lead to modification anomalies (e.g. updating some but not all of the Zs "connected" to the same Y essentially corrupts the data, since you no longer know which copy is correct). This is typically resolved by splitting the original table into two tables, one containing {X, Y} and the other other containing {Y, Z}, This way, Y can be a key in the second table and Z is not repeated.

On the other hand, if the X<-Y does hold (i.e. X->Y->Z is not transitive), then we can retain a single table, where both X and Y are keys. Z won't be unnecessarily repeated in this scenario.

(FOOTNOTE1) A key is a (minimal) set of attributes that functionally determine all of the attributes in a relation. Rationale: If K is a key, there cannot be multiple rows with the same value of K, so any given value of K is always associated to precisely one value of every other attribute (assuming 1NF). By definition (see FOOTNOTE2), "being associated to precisely one" is the same thing as "being in a functional dependency".

(FOOTNOTE2) By definition, Y->Z if, and only if, each Y value is associated with precisely one Z value.

---

Example:

Assuming each message has exactly one author and each author has exactly one primary e-mail, attempting to represent messages and users in the same table would lead to repeating e-mails:

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

(In reality, these would be MESSAGE_IDs, but let us keep things simple here.)

Now, what happens if Jon decides to change his e-mail to, say, "[e-mail]"? We would need to update both of Jon's rows. If we only update one, then we have the following situation...

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

...and we no longer know which one of the Jon's e-mails is correct. We have essentially lost the data!

The situation is especially bad since there is no declarative constraint we could use to coerce the DBMS into enforcing both updates for us. The client code will have bugs and is probably written without much regard for complex interactions that can happen in the concurrent environment.

However, if you split the table...

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

USER    EMAIL
----    -----
Jon     [e-mail]
Rob     [e-mail]

...there is now only one row that knows about Jon's e-mail, so ambiguity is impossible.

BTW, all this can be viewed as just another expression of the DRY principle.

回答3:

If there's transitive dependencies in your table then it's not compliant with 3NF; so there's a high probability that there is redundant data in your table. Check this to clarify this concept.

回答4:

Take a look at this link:

http://en.wikipedia.org/wiki/Transitive_dependency

Using the example, what would happen if I update the nationality of Jules Verne on one row, but not the other? Author nationality is determined by author alone, not the combination of book and author. So with the example data structure, I could potentially ask the database the nationality of Jules Verne. If I ran the following SQL command

SELECT TOP 1 author_nationality FROM books WHERE author='Jules Verne'

I could get a different answer depending on how the database selects TOP 1.

回答5:

I just put together a post which addresses why transitive dependencies are generally a bad idea: http://www.essentialsql.com/get-ready-to-learn-sql-11-database-third-normal-form-explained-in-simple-english/