Boyce-Codd Normal Form: Understanding 3NF and Dependencies on Candidate Keys, Slides of Database Management Systems (DBMS)

Download Boyce-Codd Normal Form: Understanding 3NF and Dependencies on Candidate Keys and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Boyce-Codd Normal Form

Overview

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

First Normal Form

• Eliminate Repeating Groups
• Require uniquely identified rows

First Normal form

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

Second Normal Form

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

2NF/3NF

• A Relation without non-key attributes is 2NF

and 3NF.

Third Normal Form

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

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."

Example of 3NF and BCNF

Given: R(A,B,C,D,E)

AB –> C AB –> D AB –> E DE –> C DE –> A DE –> 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.

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.