Boyce Code Normal Form [BCNF]

Boyce Code Normal Form: [BCNF]:

                BCNF a relation schema R is in BCNF with respect to a set F of functional dependency for all functional dependency in F+ of the form where ∝ -> β, where ∝ ⊆ R, β ⊆ R  one of the following holds,
·          ∝ -> β is a trivial functional dependency.

·         ∝ is a super key for schema R.

                                                                                                                                     

                    A database design in BCNF if each number of set of relation schema that constitute the design is in BCNF.

Example: Let us consider following relation schema respective functional dependency.

          R

Customer schema -> {customer name, Customer Street, customer city}

                                                  β

Customer name -> {customer street, customer city}

ð  Branch Schema = {branch name, branch city, assets} Branch name -> branch city, asset

ð  Lon information ={branch name, customer name, loan number, amount} loan number -> branch name, customer name, amount.

The customer schema is the BCNF. We know that candidate key for the schema is customer name. The only non trivial function dependencies that hold on customer schema have customer name and left side at arrows.

BCNF decomposition Algorithm: 

 Result:= R

Done:= False

Compute F++

Which (not done)do

     If (there is a schema Ri in Result that:=not in BCNF)then

Begin

     Let ∝ -> β be a non trivial functional dependency that holds on

    Ri is ∝ -> Ri is not in F+ and ∝ ⋂ β = ∅

Result:=(Result – Ri) ⋃ (Ri – β) ⋃ (∝,β)

End

Else done:=true.

Let us applying the BCNF decomposition algorithm to the lending schema.

Lending schema=[Branch Name, Branch city, Asset, customer Name, Loan Number, Amount]

Branch Name -> Assets, Branch city

Loan Number -> Amount, Branch Name.

The candidate key for the schema is branch name, loan number.