MySQL + SQL · Lesson 29

BCNF (Boyce-Codd Normal Form) in DBMS

In this lesson: What BCNF is, how it differs from 3NF and how to decompose a table into BCNF — solved example step by step.

What is BCNF

BCNF (Boyce-Codd Normal Form) is a stricter version of 3NF. It closes a small gap that 3NF can leave in tables with overlapping candidate keys. If a table is in BCNF, it is guaranteed free of a certain kind of dependency-based redundancy.

💡 BCNF is "3NF, but stricter": for every dependency A -> B, A must be a super key.

The BCNF rule

A table is in BCNF if, for every functional dependency A -> B, the determinant A is a super key (it can uniquely identify a row). If any determinant is not a super key, the table is not in BCNF and should be decomposed.

BCNF vs 3NF

Point	3NF	BCNF
Determinant rule	Super key OR dependent is part of a candidate key	Determinant must be a super key
Strictness	Less strict	More strict
Every BCNF is 3NF?	—	Yes
Every 3NF is BCNF?	Not always	—

When 3NF is not enough

Consider a table where a student takes subjects, each taught by one teacher, and each teacher teaches one subject:

(student, subject) -> teacher
teacher -> subject

Here teacher -> subject has a determinant (teacher) that is not a super key. The table can be in 3NF but still stores redundant subject-teacher facts. It is not in BCNF.

Fixing it to BCNF

Decompose so every determinant becomes a super key:

teaches(teacher PK, subject)
takes(student, teacher)

Now teacher -> subject lives in a table where teacher is the key, and the student-teacher link is separate. Every dependency now has a super-key determinant — the design is in BCNF.

When to use BCNF

Use 3NF for most everyday databases — it is usually enough.
Use BCNF when overlapping candidate keys still cause redundancy after 3NF.
Watch the trade-off — BCNF may need extra joins in queries.

BCNF is about removing the last bit of dependency redundancy, at the cost of more tables.

Common mistakes

Thinking 3NF always equals BCNF. A 3NF table can still violate BCNF.
Ignoring overlapping candidate keys. That is exactly where BCNF matters.
Over-decomposing. Extra tables can add joins; normalize only as far as needed.
Forgetting BCNF implies 3NF. Reaching BCNF means you already satisfy 3NF.

Summary

BCNF is a stricter form of 3NF.
Rule: for every A -> B, A must be a super key.
3NF can leave redundancy with overlapping candidate keys; BCNF removes it.
Every BCNF table is in 3NF, but not vice versa.
Use 3NF for most cases; BCNF when that redundancy remains.

Next, revise the earlier forms in normalization, or the theory in functional dependency.

Frequently Asked Questions

What is BCNF?

BCNF (Boyce-Codd Normal Form) is a stricter version of 3NF. A table is in BCNF if, for every functional dependency A -> B, A is a super key. It removes certain redundancy that 3NF can still leave behind in tables with overlapping candidate keys.

What is the difference between 3NF and BCNF?

3NF allows a dependency where the determinant is not a super key, as long as the dependent is part of a candidate key. BCNF does not allow this at all — every determinant must be a super key. So BCNF is stricter than 3NF.

When is 3NF not enough?

3NF can leave redundancy when a table has multiple overlapping candidate keys and a non-key column determines part of a key. In such rare cases, the table is in 3NF but not BCNF, and anomalies can still occur.

Is every BCNF table also in 3NF?

Yes. BCNF is stronger, so every table in BCNF is automatically in 3NF (and 2NF and 1NF). The reverse is not always true: a 3NF table may not be in BCNF.

Should I always normalize to BCNF?

Often 3NF is enough for practical databases. BCNF is used when you need to remove every last dependency-based redundancy, but decomposing to BCNF can sometimes make queries need more joins, so it is a trade-off.