5 - Advanced Normal Forms: BCNF, 4NF, and 5NF — Module Topics

Review of 3NF and Its Limitations

• Quick Recap of Third Normal Form (3NF) — 3NF requires that a relation be in 2NF and that every non-prime attribute be non-transitively dependent on every candidate key.
• The Prime-Attribute Exception in 3NF — 3NF's allowance for functional dependencies whose dependent side is a prime attribute is the root cause of its most significant limitation.
• Redundancy Anomalies That Survive 3NF — Even a properly constructed 3NF schema can exhibit insertion, deletion, and update anomalies when overlapping candidate keys are present.
• Overlapping Candidate Keys as the Core Problem — 3NF schemas become problematic specifically when a relation has two or more candidate keys that share at least one attribute.
• Lossless Decomposition and 3NF's Trade-off — One reason 3NF is sometimes preferred in practice is that it guarantees both lossless-join decomposition and dependency preservation, trade-offs that stricter normal forms may not always offer.
• Motivating the Move to Higher Normal Forms — The edge cases where 3NF falls short establish a clear motivation for BCNF, 4NF, and 5NF, each targeting a progressively more subtle class of dependency.

Boyce-Codd Normal Form (BCNF)

• What is BCNF and How Does It Differ from 3NF? — Boyce-Codd Normal Form (BCNF) is a stricter refinement of Third Normal Form (3NF), designed to eliminate certain anomalies that 3NF can still permit.
• Formal Definition of BCNF — BCNF is formally defined in terms of functional dependencies and superkeys within a relational schema.
• Identifying BCNF Violations — A BCNF violation occurs whenever a functional dependency exists in a relation where the left-hand side is not a superkey.
• Decomposing a Schema to Achieve BCNF — When a BCNF violation is found, the standard remedy is lossless decomposition — splitting the relation into two or more smaller relations that are each in BCNF.
• Lossless Decomposition and the BCNF Trade-off — BCNF decomposition guarantees a lossless join but does not always preserve all functional dependencies, which is an important practical trade-off.
• When 3NF is Insufficient and BCNF is Needed — There are specific edge cases where a schema satisfies 3NF but still exhibits anomalies that only BCNF can resolve.

Comparing 3NF and BCNF

• Recap: What 3NF and BCNF Require — Both 3NF and BCNF address functional dependencies, but they differ in how strictly they eliminate redundancy caused by non-trivial dependencies.
• When 3NF and BCNF Coincide — For many practical schemas, satisfying 3NF is sufficient to also satisfy BCNF, making the distinction irrelevant in those cases.
• The Classic Gap: Overlapping Candidate Keys — The most common scenario where 3NF and BCNF diverge involves relations with two or more overlapping candidate keys, producing a non-superkey determinant that points to a prime attribute.
• Dependency Preservation: 3NF's Advantage — A key trade-off is that decomposing a schema to BCNF can sometimes make it impossible to enforce all original functional dependencies within individual relations.
• Lossless-Join Decomposition in Both Forms — Both 3NF and BCNF can always be achieved through a lossless-join decomposition, meaning no information is lost when the decomposed relations are rejoined.
• Redundancy: BCNF's Advantage — BCNF eliminates more redundancy than 3NF because it removes every case where a non-superkey functionally determines another attribute, including when that attribute is prime.
• Choosing Between 3NF and BCNF in Practice — The decision between stopping at 3NF or continuing to BCNF depends on weighing redundancy elimination against dependency preservation requirements.

Multivalued Dependencies and Fourth Normal Form (4NF)

• What Is a Multivalued Dependency? — A multivalued dependency (MVD) exists when one attribute in a relation independently determines a set of multiple values of another attribute, regardless of other attributes present.
• How MVDs Cause Redundancy That BCNF Cannot Resolve — BCNF eliminates redundancy caused by functional dependencies, but it is powerless against the redundancy introduced by multivalued dependencies, meaning a relation can be in BCNF yet still contain significant data duplication.
• Formal Definition of Fourth Normal Form (4NF) — A relation is in Fourth Normal Form (4NF) if and only if, for every non-trivial multivalued dependency X ↠ Y, X is a superkey of the relation.
• Identifying 4NF Violations — To check whether a relation satisfies 4NF, you must identify all multivalued dependencies that hold, classify each as trivial or non-trivial, and verify whether the left-hand side is a superkey.
• Decomposing Relations to Achieve 4NF — The standard approach to resolving 4NF violations is to decompose the offending relation into two separate relations, each capturing one of the independent multivalued attributes along with the shared determinant.
• Trade-offs and Practical Considerations of 4NF — While 4NF eliminates MVD-driven redundancy, achieving it requires careful identification of truly independent multi-valued attributes and results in a larger number of smaller tables.

Join Dependencies and Fifth Normal Form (5NF)

• What Is a Join Dependency? — A join dependency (JD) is a constraint on a relation stating that it can be losslessly reconstructed by joining a specific set of its projections.
• Defining Fifth Normal Form (5NF) — A relation is in Fifth Normal Form (5NF), also called Project-Join Normal Form (PJ/NF), if every join dependency it satisfies is implied by its candidate keys.
• Recognizing 5NF Violations — A 5NF violation occurs when a relation contains a non-trivial join dependency that cannot be derived from its candidate keys, causing repeated fact combinations across rows.
• Decomposing a Relation to Achieve 5NF — To normalize a relation to 5NF, identify all non-trivial join dependencies and decompose the relation into projections whose join dependencies are implied by their own candidate keys.
• Lossless Join Property in 5NF Decomposition — A decomposition is lossless if and only if the natural join of all projections returns precisely the original set of tuples, with no spurious additions or omissions.
• Practical Trade-offs of 5NF — While 5NF achieves the highest degree of redundancy elimination, decomposing to 5NF increases the number of tables and the complexity of queries that must rejoin them.

Applying Higher Normal Forms to Complex Schemas

• Auditing an Existing Schema Before Normalization — Before applying any higher normal form, a systematic audit of the existing schema is essential to identify all functional, multivalued, and join dependencies.
• Step-by-Step BCNF Decomposition in Practice — Applying BCNF to a real schema involves iteratively finding violating FDs and splitting relations until every determinant is a superkey.
• Recognizing and Resolving 4NF Violations in Multi-Valued Contexts — After achieving BCNF, schemas may still contain multivalued dependencies (MVDs) that require 4NF decomposition to eliminate redundant data duplication.
• Identifying Join Dependencies and 5NF Violations — 5NF addresses join dependencies that cannot be expressed as MVDs, where a relation can only be losslessly reconstructed by joining three or more projections.
• Managing Trade-offs During Multi-Step Normalization — Normalizing a complex schema through BCNF, 4NF, and 5NF sequentially involves trade-offs between redundancy elimination, query performance, and constraint preservation.
• End-to-End Normalization Walkthrough on a Complex Schema — Working through a multi-relation schema from 3NF to 5NF reinforces the ability to chain normalization steps and validate each transformation.