Boyce-Codd Normal Form

1
Boyce-Codd Normal Form

• Kelvin Nishikawa
• SE157a-03
• Fall 2006

2
Overview

• 1NF, 2NF, and 3NF
• BCNF
• Conclusion

3
Database Normalization

• Each datum in the database should represented
  once.
• Purpose is to eliminate update, insert, and
  deletion anomalies.
• Normal Forms are increasingly strict subset of
  1NF.

4
First Normal Form

• Eliminate Repeating Groups
• Require uniquely identified rows

5
First Normal form

• 1 girl per row
• 2 Pamelas are uniquely identifiable.

6
Second Normal Form

• Eliminate Redundant Data

7
Second Normal Form

• Non-key columns are dependent on all columns of
  the primary key.

8
2NF/3NF

• A Relation without non-key attributes is 2NF and
  3NF.

9
Third Normal Form

• Eliminate Columns Not Dependent On Key
• i.e. if a column is in a relation, then it must
  be dependent on the key.

10
Third Normal Form

• Move non-key-dependent attributes to a new table.

11
Boyce-Codd Normal Form (BCNF)

• 3NF, but
• All functional dependencies imply the only whole
  key.
• "The key, the whole key, and nothing but the key,
  so help me Codd."

12
Identifying (non)BCNF

• A Relation can be 3NF and not BCNF if
• There are multiple candidate keys
• The keys are composed of multiple attributes
• There are common attributes between the keys.

13
Example of 3NF and BCNF

• Given R(A,B,C,D,E)
• AB gt C
• AB gt D
• AB gt E
• DE gt C
• DE gt A
• DE gt B
• With the key AB

• Holds true for 3NF
• All left hands of functional dependencies are
  candidate keys.
• Breaks for BCNF
• There exist dependencies between candidate keys.

14
Summary

• BCNF if
• No dependencies between candidate keys.
• 3NF

• 3NF
• No attributes dependent on non-keys.
• 2NF
• Attributes dependent on all parts of the key
• 1NF
• No repeating groups
• Each row uniquely identifiable.

15
Questions?