The relation must be in 2NF.
Every non-prime attribute of the relation is non-transitively dependent on the candidate keys of the relation.
Non-key attributes should not be functionally determined by other non-key attributes. If Y->A, and Y is not a key, then the relation is not in 3NF. (If Y is a partial key then 2NF is violated).
Restated, a relation R is in Third Normal Form if it is in 2NF and every non-key attribute of R is non-transitively dependent on each candidate key of R.
A transitive functional dependency implies that when X->Y and Y->A then X->A where X is a key and Y is a non-key.
Fixed by decomposition into relations where the non-key attributes that determined the other non-key attributes become keys.
The following violates 3NF because at least one of the non-prime attributes is dependent upon another non-prime attribute:
ID | Course | Grade | GPA |
---|---|---|---|
8245 | CP102 | B- | 7 |
8245 | HP202 | C+ | 6 |
9995 | CP363 | A- | 10 |
9995 | CP400 | B+ | 9 |
9995 | CP102 | A | 11 |
Note that WLU uses a 12 point GPA system.
Grade and GPA are non-prime attributes because they are not part of any candidate keys - they do not contribute to the uniqueness of a tuple.
Again, this table is subject to update anomalies. What happens if a student receives a grade of A+ but a GPA of 0? Which is the correct mark? (If you are a student the answer is obvious, of course.)
The relation’s functional dependencies are:
Course, ID → Grade
Course, ID → GPA
Grade → GPA
GPA → Grade
Thus these transitive dependencies are also true:
Course, ID → GPA → Grade
Course, ID → Grade → GPA
Having discovered what the functional dependencies are and the fact that we have at least one transitive dependency we have to fix the table. We can use these functional dependencies to guide our decomposition. Note that we may have to make a choice as to how to decompose a table. Sometimes this choice is arbitrary.
EXTRA NOTE: Why do zombies make great database designers? They are excellent at decomposition.
In a general sense, a table or relation should contain information about only one thing, where that 'thing' is determined by the primary key. According to Kent (1983) all facts in the relation are about the key (or the thing that the key identifies), the whole key and nothing but the key. A transitive dependency tells you that some of the attributes in a tuple are not about the key, but about something related to the key, and the relation should be decomposed in such a way that each table has attributes only about the key.
The original relation is decomposed into: (although the functional dependencies show that another decomposition is also valid)
ID | Course | GPA |
---|---|---|
8245 | CP102 | 7 |
8245 | HP202 | 6 |
9995 | CP363 | 10 |
9995 | CP400 | 9 |
9995 | CP102 | 11 |
GPA | Grade |
---|---|
12 | A+ |
11 | A |
10 | A- |
9 | B+ |
8 | B |
This decomposition removes the update anomaly problem. Now grades appear in one place only and a simple join is enough to connect them back to a student's course.