| Lesson 7 | Normalization beyond third normal form |
| Objective | Distinguish BCNF, 4NF, and 5NF, and recognize when independent multivalued facts require decomposition. |
Normalization Beyond Third Normal Form: BCNF, 4NF, and 5NF
A relation in 3NF can still contain redundancy caused by dependencies that require stronger tests. BCNF strengthens the functional-dependency rule. Fourth normal form addresses nontrivial multivalued dependencies, while fifth normal form addresses more general join dependencies. Choose the appropriate test from the business rules, rather than splitting a table simply to increase its normal-form number.
What Each Normal Form Checks
| Normal form | Requirement | Design question |
|---|---|---|
| 3NF | For every nontrivial functional dependency X → A, X is a superkey or A is prime. | Does the relation meet the key or prime-attribute condition? |
| BCNF | For every nontrivial functional dependency, the determinant is a superkey. | Does any non-key determinant control another attribute? |
| 4NF | For every nontrivial multivalued dependency, the determinant is a superkey. | Are independent sets of facts multiplied together? |
| 5NF (PJ/NF) | Every nontrivial join dependency is implied by candidate keys. | Does a further lossless decomposition follow from a join rule beyond the keys? |
A superkey uniquely identifies a row; a candidate key is a minimal superkey. A prime attribute belongs to at least one candidate key. The usual implication chain is 5NF ⇒ 4NF ⇒ BCNF ⇒ 3NF. A stronger form implies the weaker forms, but the reverse does not necessarily hold.
BCNF: A Stronger Functional-Dependency Test
3NF permits a non-superkey determinant when the dependent attribute is prime. BCNF removes that exception. Overlapping candidate keys can produce this situation, although the formal dependency test is what decides whether a violation exists.
Consider Teaching(StudentID, CourseID, InstructorID). Suppose a student has one instructor per course, and each instructor teaches only one course. The dependencies are:
(StudentID, CourseID) → InstructorID
InstructorID → CourseIDThe candidate keys are (StudentID, CourseID) and (StudentID, InstructorID). All attributes are prime, so the relation satisfies 3NF under these dependencies. It violates BCNF because InstructorID determines CourseID but does not identify a row: an instructor may teach many students.
A lossless split into InstructorCourse(InstructorID, CourseID) and StudentInstructor(StudentID, InstructorID) removes that BCNF violation. However, the original rule that a student has only one instructor per course is no longer enforceable through keys on those two tables alone. This illustrates why dependency preservation must be checked alongside losslessness. The next lesson examines BCNF in more detail.
4NF: Separate Independent Multivalued Facts
A multivalued dependency describes independence between sets of values within a relation. It means more than “one entity has many related rows.” In R(X, Y, Z), X ↠ Y means that, for a fixed X, the permitted Y values combine independently with the permitted Z values. When two rows share X, the corresponding swapped Y/Z combinations must also be present.
Such a dependency is nontrivial when Y is not a subset of X and X together with Y does not cover the entire relation. [1] The determinant need not be a key; indeed, a non-superkey determinant in a nontrivial multivalued dependency is the problem that 4NF detects.
Worked Example: Audiobook Authors and Narrators
Retain the lesson's CD scenario, treating each CD as an audiobook edition. Its credited authors and its credited narrators are two independent sets. We are not recording which narrator reads which author's contribution. That distinction is essential.
For CD 101, assume authors A1 and A2 and narrators N1, N2, and N3. No additional functional dependencies hold among the three identifiers. A combined relation representing all combinations therefore needs six rows:
| CDNo | AuthorID | NarratorID |
|---|---|---|
| 101 | A1 | N1 |
| 101 | A1 | N2 |
| 101 | A1 | N3 |
| 101 | A2 | N1 |
| 101 | A2 | N2 |
| 101 | A2 | N3 |
The only candidate key is the entire triple (CDNo, AuthorID, NarratorID). With no nontrivial functional dependencies, this relation satisfies BCNF and therefore 3NF. Nevertheless, it has the nontrivial multivalued dependencies CDNo ↠ AuthorID and CDNo ↠ NarratorID. CDNo is not a superkey, so it violates 4NF.
Why the Combined Table Is Awkward
- Adding author A3 requires three rows, one for each narrator. Omitting a combination contradicts the independence rule.
- Removing narrator N2 requires deleting both rows involving that narrator.
- Recording an author before any narrators are known requires an artificial placeholder or a different representation.
- With m authors and n narrators, the combined relation stores m times n combinations, even though the underlying facts are just the two independent sets.
The repeated identifiers represent redundant combinations, not repeated author biographies. Author and narrator names would belong in their own entity tables, keyed by their identifiers.
Decompose the Example into 4NF
Fourth normal form requires every nontrivial multivalued dependency to have a superkey determinant. [2] Separate the independent credit relationships:
CD_Author(CDNo, AuthorID)
Primary key: (CDNo, AuthorID)
CD_Narrator(CDNo, NarratorID)
Primary key: (CDNo, NarratorID)| CDNo | AuthorID |
|---|---|
| 101 | A1 |
| 101 | A2 |
| CDNo | NarratorID |
|---|---|
| 101 | N1 |
| 101 | N2 |
| 101 | N3 |
The two binary relations satisfy 4NF under the stated rules. Their dependencies from CDNo to the other column are trivial multivalued dependencies because the two columns cover the entire relation. 4NF does not prohibit every multivalued dependency.
The design stores five independent relationship rows instead of six combinations. More importantly, adding an author now needs only one CD_Author row, regardless of the number of narrators. Define foreign keys to the corresponding CD, Author, and Narrator entity tables to enforce valid references.
Verify the Join—and Its Meaning
SELECT ca.CDNo, ca.AuthorID, cn.NarratorID
FROM CD_Author AS ca
JOIN CD_Narrator AS cn
ON cn.CDNo = ca.CDNo;This query reconstructs all six original combinations. The multivalued dependency justifies the lossless decomposition for every valid instance satisfying independence, not merely this sample. If a CD has authors but no narrators yet, an inner join returns no rows for that CD; the author records remain safely stored in CD_Author.
Do not make this split if pairings carry meaning. If N1 narrates only A1's material while N2 narrates only A2's, decomposing into independent lists would generate false pairings when joined. Keep the actual assignment relationship, or model contributions and narration assignments explicitly. The business rule decides which design preserves information.
5NF: More General Join Dependencies
A join dependency says that a relation equals the join of specified projections of itself. Fifth normal form, also called projection-join normal form, requires every nontrivial join dependency to be implied by candidate keys. Examples often involve three projections, but “three or more tables” is not the definition.
Consider a supplier-part-project relation. Three pairwise lists might say which suppliers can provide parts, which suppliers serve projects, and which parts projects use. Their join is valid as a replacement for the triple relation only if the business rules guarantee that every mutually compatible combination is an allowed supplier-part-project assignment.
If the triple records actual deliveries or specifically authorized assignments, pairwise compatibility alone may invent combinations that never occurred or were never approved. A three-way split would then be lossy in the practical sense of losing the original distinctions and producing spurious tuples on reconstruction. Verify the join dependency before applying a decomposition.
DKNF and Practical Stopping Points
Domain-key normal form is a broader ideal in which all constraints follow from domain and key constraints. It is not simply the instruction to add more tables. Complex business policies can make that target difficult to achieve.
Use 3NF or BCNF as a starting point for dependency analysis, then examine genuine independent multivalued facts and join dependencies when the domain requires it. A higher form can arise naturally from correct modeling; it need not be an academic exercise. Evaluate performance separately, using measured workloads and documented consistency mechanisms if deliberate redundancy is introduced.
Checklist Before Decomposing Further
- Identify the dependency type: functional, multivalued, or join dependency.
- Confirm the rule with domain experts rather than inferring it from a small data sample.
- Identify all candidate keys and apply the relevant normal-form test.
- Prove lossless reconstruction and check which constraints remain enforceable locally.
- Test missing relationships, new combinations, deletions, and meaningful pairings.
Summary
BCNF tightens the functional-dependency test; 4NF separates independent sets; 5NF addresses general join dependencies. In the CD example, independent author and narrator credits belong in separate relationship tables. The benefit is independent maintenance of those facts without storing every combination, not a blanket guarantee that all possible anomalies disappear.
Further reading: Ronald Fagin's Multivalued Dependencies and a New Normal Form for Relational Databases introduces the theoretical basis for 4NF.