Third Normal Form

1. When we cannot meet all three design criteria, we abandon BCNF and accept a weaker form called third normal form (3NF).
2. It is always possible to find a dependency-preserving lossless-join decomposition that is in 3NF.
3. A relation scheme is in 3NF with respect to a set of functional dependencies if for all functional dependencies in of the form , where and , at least one of the following holds:
   • is a trivial functional dependency.
   • is a superkey for scheme .
   • Each attribute in is contained in a candidate key for .
4. A database design is in 3NF if each member of the set of relation schemes is in 3NF.

5. We now allow functional dependencies satisfying only the third condition. These dependencies are called transitive dependencies, and are not allowed in BCNF.

6. As all relation schemes in BCNF satisfy the first two conditions only, a scheme in BCNF is also in 3NF.

7. BCNF is a more restrictive constraint than 3NF.

8. Our Banker-scheme decomposition did not have a dependency-preserving lossless-join decomposition into BCNF. The scheme was already in 3NF though (check it out).

9. We now present an algorithm for finding a dependency-preserving lossless-join decomposition into 3NF.

10. Note that we require the set of functional dependencies to be in canonical form.

    

11. Each relation scheme is in 3NF. Why? (A proof is given is [Ullman 1988].)

12. The design is dependency-preserving as a scheme is built for each given dependency.

    Lossless-join is guaranteed by the requirement that a candidate key for be in at least one of the schemes.

13. To review our Banker-scheme consider an extension to our example:

    

    The set of functional dependencies is

    

    The for loop in the algorithm gives us the following decomposition:

    

    Since Banker-scheme contains a candidate key for Banker-info-scheme, the process is finished.

14. Some Things To Note About 3NF
    • A 3NF decomposition is not necessarily dependency preserving. Consider

      

    • However, the algorithm given will always generate 3NF decompositions that are dependency preserving (why?).

    • When we retreat to our 3NF decomposition algorithm to obtain dependency preservation, the resulting scheme is in 3NF, but it may also be in BCNF!
    • The point to note here is that not all BCNF decompositions are optimal in terms of reducing redundancy (ones achieved by this 3NF algorithm may have more relations).