# Boyce-Codd Normal Form (BCNF)

Document Sample

```					 Boyce-Codd Normal Form (BCNF)

Definition
R in 1NF and
Every determinant (the left side
of a FD) is a candidate key.

January 11, 2010         UWP - Qi Yang         1
BCNF and 3NF

BCNF is stronger than 3NF

If R in BCNF, then R in 3NF.
If R not in 3NF, then R not in BCNF.

2
Proof
If R not in 3NF, then
PK ---> B, and B ---> C, (PK ---> C)
NO cycle for transitive FD, i.e.
B ---> PK : False
B is not candidate key
but a determinant (B ---> C )
So, R is not in BCNF.

3
Example
Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName)
Primary Key: PNo, Start
Alternate Key: PNo, Finish
FDs:
PNo, Start ---> All other attributes
PNo, Finish ---> All other attributes
PAddress, Start ---> All other attributes
PAddress, Finish ---> All other attributes
PNo ---> PAddress, ONo, OName (Pno not a candidate key)
RNo ---> Rname (Rno not a candidate key)
ONo ---> OName (Ono not a candidate key)

Not in BCNF.
How many tables?

4
Convert Relations into BCNF
Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName)
PNo ---> PAddress, ONo, OName (Pno not a candidate key)
RNo ---> Rname (Rno not a candidate key)
ONo ---> OName (Ono not a candidate key)

Owner (ONo, OName)                      Renter (RNo, RName)
ONo ---> Oname                           RNo ---> RName

Lease (RNo, PNo, Start, Finish, Rent)
PNo, Start ---> All
PNo, Finish ---> All
5
Review: Normalization
•   1NF
Remove multi-value attributes
Why: each element is not a set (first order logic)
•   2NF
Remove partial FDs on PK
Why: remove redundant data
•   3NF
Remove transitive FDs on PK
Why: remove redundant data
•   BCNF
Strong requirement

In most cases, 3NF is enough.

Why normalize?

6
Lossless Decomposition

After a relation is normalized into
two or more relations, the
original relations could be
obtained by joining new
relations
Primary Key and Foreign Key

7
Convert Relations into BCNF
Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName)

Owner (ONo, OName)
ONo ---> OName

Renter (RNo, RName)
RNo ---> RName

Lease (RNo, PNo, Start, Finish, Rent)
PNo, Start ---> All other attributes
PNo, Finish ---> All other attributes

How to get Property, Owner, Renter data for a lease?

8
Example
R (A, B, C, D)
PK: A, B
AK: B, C
FK: None
FDs: A, B, C  All
D, B, C  All
D, C  A

PK must be changed
9
De-Normalization
• Normalized relations
Minimal redundancy
Need join operation to get results
• How far should we go?
• Where to stop?

10
Review: Database Design
A structured approach that uses procedures, techniques, tools,
and documentation aids to support and facilitate the
process of design.

Three main phases
1.   Conceptual database design
Understanding client data
E-R (EER) Model
Contract between clients and designers

2.   Logical database design
Mapping E-R Model to (relational) database schema
DBDL
Normalization

3.   Physical database design

11
Assignment 5 (Part II)

12

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 423 posted: 1/11/2010 language: English pages: 12
How are you planning on using Docstoc?