Rule for Schema - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Rule for Schema - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Boyce-Codd Normal Form

Boyce-Codd Normal Form

• Eliminates all redundancy that can be

discovered by functional dependencies

• But, we can create a normal form more

restrictive called 4NF

Rule for schema not in BCNF

• Let R be a schema not in BCNF, then there is at

least one nontrivial functional dependency

α→β such that α is not a superkey

Example of not BCNF

• bor_loan = (customer_id, loan_number,

amount)

• loan_number  amount

• but loan_number is not a superkey

Example

• borrower = (customer_id, load_number)

Is BCNF because no nontrivial functional dependency hold onto it

• loan = (loan_number, amount)

Has one nontrivial functional dependency that holds, loan_number  amount , but loan_number is a superkey so loan is in BCNF

3NF vs BCNF

• BCNF requires that all nontrivial dependencies

be of the form αβ, where α is a superkey

• 3NF relaxes this constraint a little bit by

allowing nontrivial functional dependencies

Alternate test for 2)

• For every subset α of attributes in R (^) i check

that a +^ (the attribute closer of α under F )

either includes no attribute of R i - α, or

includes all attributes of Ri

BCNF Decomposition Algorithm

• If R is not in BCNF, we can decompose R into a collection of BCNF schemas R 1 , R 2 , …, R (^) n
• Result := {R}; done := false; computer F+ while( not done) do if(there is a schema R (^) i in result that is not in BCNF) then begin on R^ α→β^ be a nontrivial functional dependency that holds result :=(result – Ri^ such that^ α-> Ri) ∪i^ is not in F (Ri - β) ∪+ (^ , andα,β);^ α∩^ β=∅^ ; else done := true;^ end Pg 289 figure 7.

Example (cont…)

So we replace lending by:

branch = (branch_name, branch_city, assets)

loan_info = (branch_name, customer_name, loan_number, amount) The only nontrivial functional dependencies that hold on branch include branch_name on the left side of the arrow. Since branch_name is a key for branch , the relation branch is in BCNF

Example (cont…)

• For loan_info
• The functional dependency

loan_number  amount branch_name

holds on loan_info but loan_number is not a key for loan_info , so we replace loan_info by loanb = (loan_number, branch_name, amount) borrower = (customer_name, loan_number) loanb and borrower are in BCNF

Conclusion

• Our goals of database design with functional

dependencies are

1) BCNF

2) Losslessness

3) Dependency preservation

Not possible to get all 3, we have to choose

between BCNF or dependency preservation