BCNF, 4NF and 5NF

Why it matters

3NF removes most redundancy, but not all. Tables with overlapping composite keys can still repeat facts, and tables that combine two independent lists can explode in size. Boyce-Codd normal form closes the first gap and 4NF the second. These are standard exam topics, and recognising them in real schemas explains some stubborn duplication that 3NF thinking misses.

The idea

Boyce-Codd normal form

A relation is in BCNF when, for every non-trivial functional dependency X → Y, X is a super key. It is 3NF without the escape clause: 3NF also allows X → A when A is a prime attribute; BCNF does not.

The difference shows up only when candidate keys overlap. Consider Teaching(student, subject, instructor) with the rules: each instructor teaches exactly one subject, and a student takes a subject from exactly one instructor. Dependencies: {student, subject} → instructor and instructor → subject. Candidate keys: {student, subject} and {student, instructor}. Every attribute is prime, so the table is in 3NF; but instructor → subject has a determinant that is not a super key, so it is not in BCNF, and each instructor's subject is repeated for every student they teach.

That is the important caveat: BCNF decomposition is always lossless but not always dependency-preserving, while 3NF can always achieve both. When a BCNF decomposition would lose a dependency you care about, staying in 3NF (and accepting a little redundancy) is a legitimate choice.

Multivalued dependencies and 4NF

A multivalued dependency X ↠ Y holds when, for each X value, the set of Y values is independent of the other attributes. Suppose Employee(emp, skill, language) where an employee's skills and spoken languages are unrelated lists. To record that Asha knows two skills and two languages, the table needs every combination: four rows. Add a skill and you must add a row per language. The table may have no functional dependencies at all (its only key is all three columns), so it is in BCNF, yet it is clearly redundant.

emp	skill	language
Asha	SQL	English
Asha	SQL	Hindi
Asha	Go	English
Asha	Go	Hindi

A relation is in 4NF when it is in BCNF and every non-trivial multivalued dependency X ↠ Y has X as a super key. The fix is to split independent lists: EmployeeSkill(emp, skill) and EmployeeLanguage(emp, language). This is the formal version of the ER rule that each multivalued attribute gets its own table.

Join dependencies and 5NF

A join dependency says a relation equals the join of several of its projections. 5NF (project-join normal form) requires that every join dependency follows from the candidate keys. The textbook case is a three-way rule: if a supplier supplies a part, the part is used in a project, and the supplier supplies to that project, then the supplier supplies that part to that project. Such a table can be split into three two-column tables and rejoined without loss, but not into any two. 5NF cases are rare in practice and are mostly recognised by asking whether a three-way relationship is really three independent pairwise facts.

The ladder

Form	Removes	Condition
1NF	Repeating groups, non-atomic values	Atomic values
2NF	Partial dependencies	No non-prime attribute depends on part of a key
3NF	Transitive dependencies	For X → A, X is a super key or A is prime
BCNF	Remaining FD redundancy with overlapping keys	For every X → Y, X is a super key
4NF	Independent multivalued facts	For every X ↠ Y, X is a super key
5NF	Redundancy from join dependencies	Every join dependency implied by keys

Each form implies all the ones above it.

Trade-offs

Higher normal forms remove subtler redundancy at the cost of more tables, and for BCNF possibly a dependency that can no longer be enforced by a key. In practice, aim for 3NF or BCNF by default, apply 4NF whenever you see independent lists in one table, treat 5NF as a check on genuine three-way relationships, and denormalize only deliberately for read performance.

In an interview

Show the difference with the canonical example: "Teaching(student, subject, instructor) with instructor → subject is in 3NF, because subject is prime, but not in BCNF, because instructor is not a super key. Decomposing fixes the redundancy but loses the rule that a student has one instructor per subject, which is why 3NF is sometimes preferred." That shows you understand the trade, not just the definitions.