Beyond BCNF: when two independent facts still cause redundancy
Even a relation in BCNF can be redundant if it stores two unrelated multi-valued facts about the same entity together. This closing lesson names that specific problem and the two normal forms built to solve it.
After this lesson
You should be able to
- Recognize a multivalued dependency and the redundancy it causes when two such facts share a relation.
- State the core idea 4NF and 5NF each add beyond BCNF.
Two independent multi-valued facts, needlessly combined
Consider StudentActivity(roll_number, hobby, club) where a student can have several hobbies and belong to several clubs, and these two facts are completely independent of each other. Storing both in one relation forces every hobby to be paired with every club in separate rows, purely to satisfy the table's shape — a student with 2 hobbies and 3 clubs needs 6 rows to represent facts that are really just 2 plus 3 separate pieces of information.
This is a multivalued dependency: roll_number multi-determines hobby, independently of club (written roll_number to-to hobby). This relation can already be in BCNF — every functional dependency's left side is a key — yet still suffer this specific redundancy, because BCNF only reasons about functional dependencies, and a multivalued dependency is a genuinely different kind of constraint.
4NF splits independent multi-valued facts apart
Fourth Normal Form requires that a relation have no non-trivial multivalued dependency unless every attribute is functionally dependent on the key — in practice, this means splitting StudentActivity into StudentHobby(roll_number, hobby) and StudentClub(roll_number, club), the same decomposition instinct from the previous lesson, now applied to a dependency type BCNF cannot see.
Fifth Normal Form handles an even narrower case: a relation that can be losslessly split into three or more smaller relations, but not into any two — a join dependency that only shows up across three or more pieces at once. This is a rare, specialized situation in practice; most real-world schema design in industry stops at 3NF or BCNF, with 4NF and 5NF reserved for specific redundancy patterns that show up only occasionally.
Try it yourself
A relation Faculty(faculty_id, subject_taught, language_known) stores subjects a faculty member teaches and languages they know, with no relationship between the two. Explain why this violates 4NF and how to fix it.
Need a hint?
faculty_id multi-determines subject_taught independently of language_known — the exact same shape as the hobby-and-club example.
Check the worked solution
This violates 4NF because faculty_id to-to subject_taught and faculty_id to-to language_known are two independent multivalued dependencies forced into one relation, needlessly pairing every subject with every language in separate rows. The fix is decomposition into FacultySubject(faculty_id, subject_taught) and FacultyLanguage(faculty_id, language_known).
Quick check
Why can a relation already in BCNF still suffer redundancy that only 4NF fixes?
Why this lesson exists
Syllabus mapping
Multivalued dependencies, Fourth Normal Form, Fifth normal form
Maps to course outcome CO3.