Boyce-Codd Normal Form (BCNF) Overview

Jul 10, 2025

Overview

This lecture explains Boyce-Codd Normal Form (BCNF), its requirements, how it improves on Third Normal Form (3NF), and provides an example of converting a table into BCNF.

Recap: Second and Third Normal Forms

  • Second Normal Form (2NF) requires no partial dependency; non-prime attributes must depend on the whole candidate key.
  • Partial dependency is when a non-prime attribute depends on part of a candidate key.
  • Third Normal Form (3NF) requires no transitive dependency; non-prime attributes should not depend on other non-prime attributes.

Boyce-Codd Normal Form (BCNF)

  • BCNF is also called 3.5 Normal Form and is a stricter version of 3NF.
  • To be in BCNF, a table must be in 3NF and for every functional dependency A → B, A must be a super key.
  • BCNF does not allow a non-prime attribute to determine a prime attribute.

Example: College Enrollment Table

  • Table contains student ID, subject, and assigned professor.
  • Student ID and subject together form the primary key.
  • Professor column can determine the subject, but professor is not a super key, violating BCNF.
  • Table is in 1NF, 2NF, and 3NF, but not BCNF due to the professor-to-subject dependency.

Converting Table to BCNF

  • Split the original table into two tables:
    • One table with student ID and professor ID.
    • Another table with professor ID, professor name, and subject.
  • This decomposition removes the BCNF violation.

Key Terms & Definitions

  • Prime Attribute — An attribute that is part of any candidate key.
  • Non-Prime Attribute — An attribute not part of any candidate key.
  • Partial Dependency — When a non-prime attribute is dependent on part of a candidate key.
  • Transitive Dependency — When a non-prime attribute depends on another non-prime attribute.
  • Super Key — An attribute or set of attributes that uniquely determine all attributes in a table.
  • BCNF (Boyce-Codd Normal Form) — A normal form where for every dependency A → B, A is a super key.

Action Items / Next Steps

  • Review upcoming videos on Fourth and Fifth Normal Forms for further normalization concepts.

Full transcript