4NF and 5NF
1NF to BCNF deal with functional dependencies. Two higher forms handle redundancy that FDs can't describe.
Multivalued dependencies and 4NF
Asha's hobbies are Music and Chess. She speaks Hindi and English. Her hobbies have nothing to do with her languages. Store both in one table and you need every combination:
| Student | Hobby | Language |
|---|---|---|
| Asha | Music | Hindi |
| Asha | Music | English |
| Asha | Chess | Hindi |
| Asha | Chess | English |
Add a third language and you need two more rows. Forget one and the table implies something false. This is a multivalued dependency (written Student ↠ Hobby): for each student, the set of hobbies is independent of everything else in the row.
The table has no non-trivial FDs at all, so it's in BCNF, and it's still redundant. 4NF says: for every non-trivial multivalued dependency X ↠ Y, X must be a superkey. Fix it by giving each independent list its own table:
| Student | Hobby |
|---|---|
| Asha | Music |
| Asha | Chess |
| Student | Language |
|---|---|
| Asha | Hindi |
| Asha | English |
You'll meet this in real data: a product's sizes and its colours, a supplier's regions and its certifications. Any time two independent lists share a key, keep them in separate tables.
5NF
5NF (also called project-join normal form) deals with join dependencies: cases where a table can be split into three or more smaller tables and rebuilt exactly by joining them, though not by any split into two. It comes up with tricky three-way relationships (which supplier supplies which part to which project, under particular business rules), and rarely in everyday design.