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 schema R is in 3NF with respect to a set F 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 schema R.
   • Each attribute A in is contained in a candidate key for R.
4. A database design is in 3NF if each member of the set of relation schemas 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 schemas in BCNF satisfy the first two conditions only, a schema in BCNF is also in 3NF.
7. BCNF is a more restrictive constraint than 3NF.
8. Our Banker-schema decomposition did not have a dependency-preserving lossless-join decomposition into BCNF. The schema 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 F of functional dependencies to be in canonical form.

     let    be a canonical cover for F;
    
    		 i := 0;
    
    		 for each functional dependency    do
    
    		 if none of the schemas   ,    contains   
    
    		 		 then begin
    
    		 		 		 i := i + 1;
    
    		 		 		    :=   
    
    		 		 end
    
    		 if none of the schemas   ,    
    contains a candidate key for R
    
    		 		 then begin
    
    		 		 		 i := i + 1;
    
    		 		 		    := any candidate key for R
    
    		 		 end
    
    		 return (  )
    
    

11. Each relation schema is in . Why? (A proof is given is [Ullman 1988].)
12. The design is as a schema is built for each given dependency.

    is guaranteed by the requirement that a candidate key for R be in at least one of the schemas.

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

     Banker-info-schema = (bname, cname, banker-name, office#)
    
    

    The set F of functional dependencies is

     banker-name    bname office# 
    
    		 cname bname    banker-name 
    
    

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

     Banker-office-schema = (banker-name, bname, office#)
    
    		 Banker-schema = (cname, bname, banker-name)
    
    

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