next up previous
Next: Third Normal Form Up: Normalization Using Functional Dependencies Previous: Repetition of Information

Boyce-Codd Normal Form

  1. A relation schema R is in Boyce-Codd Normal Form (BCNF) with respect to a set F of functional dependencies if for all functional dependencies in tex2html_wrap_inline1628 of the form tex2html_wrap_inline1730 , where tex2html_wrap_inline1732 and tex2html_wrap_inline1734 , at least one of the following holds:
  2. A database design is in BCNF if each member of the set of relation schemas is in BCNF.
  3. Let's assess our example banking design:

     Customer-schema = (cname, street, ccity)

    cname tex2html_wrap_inline1526 street ccity

     Branch-schema = (bname, assets, bcity)

    bname tex2html_wrap_inline1526 assets bcity

     Loan-info-schema = (bname, cname, loan#, amount)

    loan# tex2html_wrap_inline1526 amount bname

    Customer-schema and Branch-schema are in BCNF.

  4. Let's look at Loan-info-schema:
  5. If we decompose into

     Loan-schema = (bname, loan#, amount)

    Borrow-schema = (cname, loan#)

    we have a lossless-join decomposition. (Remember why?)

    To see whether these schemas are in BCNF, we need to know what functional dependencies apply to them.

    We also no longer have the repetition of information problem. Branch name and loan amount information are not repeated for each customer in this design.
  6. Now we can give a general method to generate a collection of BCNF schemas.

     result :=  tex2html_wrap_inline1754 ;

    done := false;

    compute tex2html_wrap_inline1628 ;

    while (not done) do

    if (there is a schema tex2html_wrap_inline1556 in result that is not in BCNF)

    then begin

    let tex2html_wrap_inline1730 be a nontrivial functional dependency that holds on tex2html_wrap_inline1556

    such that tex2html_wrap_inline1764 is not in tex2html_wrap_inline1628 , and tex2html_wrap_inline1768 ;

    result = (result tex2html_wrap_inline1770 ;


    else done = true;

  7. This algorithm generates a lossless-join BCNF decomposition. Why?

  8. Let's apply this algorithm to our earlier example of poor database design:

     Lending-schema = (bname, assets, bcity, loan#, cname, amount)

    The set of functional dependencies we require to hold on this schema are

     bname  tex2html_wrap_inline1526  assets bcity 

    loan# tex2html_wrap_inline1526 amount bname

    A candidate key for this schema is {loan#, cname}.

    We will now proceed to decompose:

  9. Not every decomposition is dependency-preserving.

    This shows us that not every BCNF decomposition is dependency-preserving.
  10. It is not always possible to satisfy all three design goals:
  11. We can see that any BCNF decomposition of Banker-schema must fail to preserve

     cname bname  tex2html_wrap_inline1526  banker-name 

  12. Some Things To Note About BCNF

next up previous
Next: Third Normal Form Up: Normalization Using Functional Dependencies Previous: Repetition of Information

Osmar Zaiane
Thu Jun 18 12:56:34 PDT 1998