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 nd 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 F+ of the form α β , where α ∈R and β ∈R , 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 rst 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 nding 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 Fc be a canonical cover for F;
i := 0;
for each functional dependency α β ∈ Fc do
if none of the schemas Rj, 1 ≤j ≤i contains
then begin
i:= i+ 1;
Ri:= αβ
end
if none of the schemas Rj, 1 ≤j ≤i contains a candidate key for R
then begin
i:= i+ 1;
Ri := any candidate key for R
end
return (R1,R2,...,Ri)

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

12. The design is dependency-preserving as a schema is built for each given dependency. Lossless-join 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 nished.