Course contentsShow
← Software Engineering
Lesson 862 of 1,93327. Database Design in PracticeFree lesson

Boyce-Codd Normal Form (BCNF)

Learn BCNF's stricter requirements and how it addresses anomalies not covered by 3NF.

Boyce-Codd Normal Form (BCNF)

What you'll learn: How BCNF strengthens 3NF to eliminate anomalies that can still exist in third normal form databases.

The Stricter Requirement

Boyce-Codd Normal Form (BCNF) is a slightly stricter version of 3NF that addresses a specific corner case: what happens when candidate keys overlap or when a table has multiple composite candidate keys.

While 3NF says "non-key attributes must depend on the key, the whole key, and nothing but the key," BCNF goes further: every determinant must be a candidate key. In other words, if any attribute (or set of attributes) determines another attribute, that determinant must itself be a valid candidate key for the table.

Why BCNF Matters

3NF can still allow anomalies when a non-key attribute determines part of a candidate key. This creates situations where:

  • Updates can cause inconsistencies
  • Deletions lose unrelated information
  • Insertions require unnecessary data

Example scenario: Imagine a table tracking professors teaching courses in specific departments. If a professor can only teach in one department, but a course-department pair can have multiple professors, you might have overlapping candidate keys. A professor's department assignment could determine part of the key, violating BCNF even while satisfying 3NF.

The Trade-off

Achieving BCNF sometimes requires decomposing tables in ways that might require more joins for common queries. In practice, many databases stop at 3NF unless specific anomalies justify the additional normalization.

Key Takeaway: BCNF requires that every determinant (anything that determines other attributes) must be a candidate key, closing loopholes that 3NF allows when candidate keys overlap or contain non-key determinants.