| Lesson 3 | Transitive Dependencies: |
| Objective | Define Transitive Dependencies |
Define Transitive Dependencies
Transitive dependencies occur when a determinant[1] affects the values of more than one business object.
Consider again the relation from the preceding lesson, which stores information about items stocked by Stories on CD.
Again, we are assuming each CD belongs to exactly one category.
The functional dependency for the relation is written:
The problem is that there are actually two functional dependencies masquerading as one. It is equally correct to write the dependency as:
Or, to put it another way:
Again, we are assuming each CD belongs to exactly one category.
Item (ItemNo, Title, Price, CategoryID, CategoryName, CategoryManager)
The functional dependency for the relation is written:
ItemNo -> (Title, Price, CategoryID, CategoryName, CategoryManager)
The problem is that there are actually two functional dependencies masquerading as one. It is equally correct to write the dependency as:
ItemNo -> (Title, Price, CategoryID)
CategoryID -> (CategoryName, CategoryManager)
Or, to put it another way:
ItemNo -> Title, Price, CategoryID -> CategoryName, CategoryManager
The functional dependency follows the mathematical property of transitivity[2] , which states that if A=B and B=C, then A=C. Because ItemNo determines CategoryID, which in turn determines CategoryName and CategoryManager, the relation contains a transitive dependency. The next lesson explains how to eliminate transitive dependencies.
Transitive Dependencies
A transitive dependency occurs when the value of a non-key attribute is functionally dependent on the value of another non-key attribute. In order for a transitive dependency to exist, a table must have at least two non-key attributes. Consider the following table (FareCode is a code like C for Coach or FC for First Class):
However, in this case the answer is no. There might be two people on a plane with the same name but one is flying first class and the other is flying coach. The specific value of FareCode is dependent on the unique identifier of Passenger (PassengerId). Next we will check fare code and fare price. If we know the fare code, do we know the fare price? Assuming that there is only one price for each code, then the answer is yes.
This is an example of a transitive dependency. We correct this problem by splitting the original table into two tables. The first is a table with the original key and all values determined directly by it. A separate table is created with the transitive dependency. For our example, we have:
Passenger = { PassengerId + Name + FareCode + FarePrice }
Here we have three non-key attributes so we might have a transitive dependency. We need to examine the non-key attributes to see if any functional dependencies exist. Start with passenger name and fare code.
If we know a passenger's name, do we know the fare code?
If the answer is yes, we have found a transitive dependency.
However, in this case the answer is no. There might be two people on a plane with the same name but one is flying first class and the other is flying coach. The specific value of FareCode is dependent on the unique identifier of Passenger (PassengerId). Next we will check fare code and fare price. If we know the fare code, do we know the fare price? Assuming that there is only one price for each code, then the answer is yes.
This is an example of a transitive dependency. We correct this problem by splitting the original table into two tables. The first is a table with the original key and all values determined directly by it. A separate table is created with the transitive dependency. For our example, we have:
Passenger = { PassengerId + Name + FareCode }
Fare = { FareCode + FarePrice }
[1]determinant: An attribute upon which another attribute is functionally dependent.
[2]transitivity: In mathematics, a binary relation R over a set X is transitive if whenever an element a is related to an element b, and b is in turn related to an element c, then a is also related to c. Transitivity is a key property of both partial order relations and equivalence relations.
