10. NORMALIZATION
10.1.4: Transitive Functional Dependencies
Consider a relation that describes a couple of concepts, say instructor and department, and where the building shown is the building where the department is located, and the attribute instructor number is the only key:
|
instructor number |
instructor name |
office |
department code |
building |
|
33 |
Joe |
3D15 |
B&A |
Buhler |
|
44 |
Joe |
3D16 |
ACS |
Duckworth |
|
45 |
April |
3D17 |
ACS |
Duckworth |
|
50 |
Susan |
3D17 |
ACS |
Duckworth |
|
21 |
Peter |
3D18 |
B&A |
Buhler |
|
22 |
Peter |
3D18 |
MATH |
Duckworth |
As instructor number is the only key, we have the following FDs:
Suppose we also have the FD: department code determines building. Now our FD diagram becomes:
and we say the FD from instructor number to building is transitive via department code.
In general, if we have a relation with key A and functional dependencies:
A –> B and B –> C, then we say attribute A transitively determines attribute C.
Note: B and C above are non-key attributes. If we also had the functional dependency B –> A (and so A and B are candidate keys) then A does not transitively determine C.
Exercises
1) Consider a relation that describes an employee including the province where the employee was born. Suppose the only key is employeeId and we have the attributes: name, birthDate, birthProvince, currentPopulation.
|
employeeId |
name |
birthDate |
birthProvince |
currentPopulation |
|
123 |
Joe |
Jan 1, 1990 |
MB |
1,200,000 |
|
222 |
Jennifer |
Jan 5, 1988 |
SK |
1,450,000 |
|
345 |
Jimmy |
Feb 5, 1987 |
MB |
1,200,000 |
|
… |
… |
… |
… |
… |
- What FDs would exist? Is there a transitive dependency?
2) Consider a relation with attributes X, Y, Z, W where the only CK is X, and the FDs are X –> Y, X –> Z, X –> W and Y –> Z. Is there a transitive dependency?
