CP363 : Third Normal Form

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.

3NF Violation

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:

Thus these transitive dependencies are also true:

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.

3NF Violation - Fixed

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.