10. NORMALIZATION
10.1.3: Partial Functional Dependencies
Consider a relation with department number, department chair name, course number and course title attributes. The combination {department number, course number} must be a key. The directed lines depict the FDs that are present:
Note the functional dependency of chair name on department number. If two or more rows in the relation have the same value for department number, they must have the same value for chair name. We say this redundancy is due to the FD of chair name on department number. Because chair name is a non-key attribute and is dependent on department number, a subset of a key, we call this dependency a partial dependency.
In general, if we have a composite key {A, B} and the dependencies below:
we say that C is partially dependent on {A, B}.
Exercises
1) Suppose each delivery of a course is called a section. In any one term suppose a course may have multiple sections and each section is assigned an instructor. Each course has a course title. Consider a Section relation where the PK is {dept number, course number, section number}. What FDs exist? Is there a partial dependency?
|
deptNo |
courseNo |
sectionNo |
instructor |
title |
|
91 |
1906 |
001 |
J. Smith |
Java I |
|
91 |
1906 |
002 |
D. Grand |
Java I |
|
91 |
1910 |
001 |
J. Smith |
Java II |
|
91 |
1910 |
002 |
J. Daniels |
Java II |
|
53 |
1906 |
001 |
S. Farrell |
History of the World |
|
… |
… |
… |
… |
… |
2) Consider a relation with attributes X, Y, Z, W where the only CK is {X,Y}, and where the FDs are {X,Y} –> Z, {X,Y} –> W, and Y –> W. Is there a partial dependency?
