Past third normal form

Third normal form removes most redundancy. It does not remove all of it, and the cases it misses are small, real and a reliable exam question.

Where 3NF leaves something

3NF allows a non key attribute to determine part of a key. Consider a table of which teacher teaches which subject to which student:

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

Candidate keys are (student, subject) and (student, teacher). Every attribute is part of some key, so the table is in 3NF.

It still has a problem. teacher -> subject is a dependency whose left side is not a superkey, so the fact that Ravi teaches Physics is repeated on every row for every student Ravi teaches. Update it in one place and the table contradicts itself.

Boyce Codd normal form

BCNF: for every dependency X -> Y, X must be a superkey. No exceptions for prime attributes.

Splitting the example:

TEACHES  (teacher, subject)          teacher is the key
STUDIES  (student, teacher)          the pair is the key

Ravi teaches Physics is now stored once.

BCNF is stricter than 3NF and occasionally costs something: a decomposition into BCNF is not always dependency preserving, meaning a constraint that was checkable within one table now spans two and needs a join to enforce. That is the reason 3NF is sometimes deliberately kept.

Fourth: independent multi valued facts

A person has several phone numbers and several skills, and the two are unrelated:

Four rows to record two facts and two facts. Adding a third phone requires two more rows. 4NF says independent multi valued dependencies belong in separate tables, and the fix is obvious once named: one table for phones, one for skills.

Fifth, and the summary

5NF covers cases where a table can be split three ways but not two. It is rare enough that most practitioners never meet one deliberately.