4NF

Definition

Fourth Normal Form (4NF) requires a relation to be in BCNF and free of non-trivial multi-valued dependencies (MVDs) other than ones implied by a candidate key. An MVD, written X ->> Y, means: for a given value of X, the set of Y values associated with it is independent of any other attribute in the relation — and when two such independent multi-valued facts about the same X get combined into one table, they generate a spurious cross-product of unrelated combinations.

The Classic 4NF Problem, Applied to a Student

Step outside the enrollment table for a moment and consider a poorly-designed StudentContact relation tracking two independent multi-valued facts about a student: their hobbies and their phone numbers.

StudentContact(StudentID, Hobby, PhoneNumber)

Say Asha (S1) has two hobbies (Chess, Painting) and two phone numbers (555-0101, 555-0102). Since neither list has anything to do with the other, a naive table combining them must record every combination to avoid implying a false pairing:

Four rows for two independent facts (2 hobbies x 2 phone numbers = 4). This is the spurious cross-product: nothing about "Chess" is actually linked to "555-0101" specifically — the table is forced to fabricate that pairing just to satisfy the relational requirement that every row be a complete, meaningful tuple. Note this relation is already in BCNF (its only key is the full {StudentID, Hobby, PhoneNumber}, trivially a superkey) — yet it's still badly designed. This is exactly the gap 4NF closes, one level beyond BCNF.

Why This Is Broken

• Insertion anomaly: adding a third hobby ("Reading") for S1 requires inserting two new rows (one per existing phone number), or the data model becomes inconsistent about which hobbies pair with which numbers.
• Update anomaly: changing one phone number to a new one requires updating it in every hobby-paired row.
• Meaningless correlation: a query grouping by PhoneNumber to "count hobbies per phone" would return nonsense, since the pairing was never a real fact to begin with.

The 4NF Fix

Split the two independent multi-valued facts into two separate relations, each with its own natural key:

StudentHobby(StudentID, Hobby)
StudentPhone(StudentID, PhoneNumber)

Now Asha's two hobbies are two rows in StudentHobby, her two phone numbers are two rows in StudentPhone, and no fabricated cross-product exists anywhere.

Edge Cases

• 4NF violations require two or more independent multi-valued facts about the same entity stored in one table. A single multi-valued fact alone (just StudentHobby(StudentID, Hobby)) is not a 4NF problem — it's a normal one-to-many relationship, already fine.
• If the two facts are not actually independent (e.g., "which hobby is practiced at which phone-registered location" is a real, tracked business fact), then the combination isn't a spurious MVD at all — it's a genuine three-way relationship, and forcing it apart into two tables would actually lose information.

Key Takeaways / Interview Angle

• Q: State 4NF in one sentence. BCNF, plus no non-trivial multi-valued dependency other than one implied by a candidate key.
• Q: What's the telltale symptom of a 4NF violation? A spurious cross-product — the row count multiplies (2 hobbies x 2 phones = 4 rows) to represent two facts that have nothing to do with each other.
• Q: How do you fix it? Split each independent multi-valued fact into its own relation, keyed by the entity plus that one fact.