SQL 79 🛢️ Advanced Normalization: BCNF, 4NF, and 5NF
The first three normal forms eliminate the anomalies that appear in most schemas. Beyond 3NF, three more forms exist: Boyce-Codd Normal Form, Fourth Normal Form, and Fifth Normal Form. Each addresses a narrower class of problem. BCNF handles tables with multiple candidate keys where a non-key column determines part of a key. 4NF handles multi-valued dependencies, where two independent multi-valued facts are stored in the same table. 5NF handles join dependencies, where a table can be decomposed into three or more tables without loss. These forms are rarely needed in practice, but the cases where they matter are the ones where 3NF is not sufficient, and the developer needs to recognize them.
Key point: BCNF, 4NF, and 5NF are not stricter versions of the same idea. Each addresses a different kind of dependency. BCNF is about determinants that are not candidate keys. 4NF is about multi-valued dependencies. 5NF is about join dependencies. A table can be in 4NF but not in BCNF, or in 5NF but not in 4NF, depending on which dependencies it has.
Why the higher normal forms exist
The BCNF problem. A table in 3NF can still have a non-key column that determines part of a candidate key. When a table has multiple candidate keys, the 3NF rule is not strong enough to eliminate the redundancy. BCNF closes the gap by requiring that every determinant be a candidate key.
The multi-valued dependency problem. A table that stores two independent multi-valued facts about the same entity, such as an employee’s skills and an employee’s languages, has a multi-valued dependency. Storing both in one table forces every combination of skill and language to be represented, which is a form of redundancy. 4NF eliminates it by separating the two facts into their own tables.
The join dependency problem. A table that can be losslessly decomposed into three or more tables has a join dependency. 5NF requires that the table not be decomposable further, unless the decomposition is implied by the candidate keys. The cases where this matters are rare but real.
The recognition problem. The higher forms are not applied routinely. They are applied when the schema exhibits the specific pattern that the form addresses. The developer needs to recognize the pattern: a table with multiple candidate keys, a table with two independent multi-valued facts, or a table that can be decomposed without loss.
The cost problem. Each decomposition adds tables and joins. The benefit is eliminating a specific anomaly, and the cost is read performance. The decision is the same as with the lower forms: apply the form when the anomaly is real, and stop when the cost outweighs the benefit.
The exam and interview problem. The LFCS exam does not test the higher normal forms. Technical interviews and database courses do. The forms are part of the vocabulary, and understanding them is part of understanding why the lower forms stop where they do.
a. Boyce-Codd Normal Form (BCNF)
A table is in BCNF when it is in 3NF and every determinant is a candidate key. A determinant is any column or set of columns on which another column depends. The rule is stricter than 3NF because 3NF allows a non-key column to determine part of a candidate key when the dependent column is a key attribute.
The BCNF violation
The classic example is a table that assigns instructors to courses that students take. The primary key is (student, course), and the constraint is that each instructor teaches only one course.
CREATE TABLE student_course_instructor (
student VARCHAR(50),
course VARCHAR(50),
instructor VARCHAR(50),
PRIMARY KEY (student, course),
UNIQUE (student, instructor)
);The functional dependencies are (student, course) → instructor and instructor → course. The instructor → course dependency has instructor as a determinant, but instructor is not a candidate key. The table is in 3NF because course is a key attribute, but it is not in BCNF.
The BCNF decomposition
The fix is to decompose the table into two tables: one for the instructor-to-course relationship and one for the student-to-instructor relationship.
CREATE TABLE instructor_course (
instructor VARCHAR(50) PRIMARY KEY,
course VARCHAR(50) NOT NULL
);
CREATE TABLE student_instructor (
student VARCHAR(50),
instructor VARCHAR(50),
PRIMARY KEY (student, instructor),
FOREIGN KEY (instructor) REFERENCES instructor_course(instructor)
);The instructor → course dependency now lives in instructor_course, where instructor is the candidate key. The (student, instructor) relationship lives in student_instructor. Both tables are in BCNF.
The 3NF versus BCNF difference
The difference appears only in tables with multiple candidate keys where a non-key column determines part of a candidate key. A table with a single candidate key is in BCNF if it is in 3NF, because the only determinant is the key.
-- 3NF and BCNF: single candidate key
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
dept VARCHAR(100)
);Every non-key column depends on id, which is the only candidate key. There is no other determinant. The table is in both forms.
The BCNF trade-off
The decomposition can lose a functional dependency. In the example, the dependency (student, course) → instructor is lost because the two columns no longer appear together in a table. The database cannot enforce the rule that a student takes a course from the instructor who teaches it, because the rule spans two tables. The decomposition is BCNF but not dependency-preserving.
The dependency-preservation problem
A decomposition is dependency-preserving when every functional dependency of the original table is enforceable in the decomposed tables. BCNF decompositions are not always dependency-preserving. 3NF decompositions always are. This is the trade-off: 3NF preserves dependencies but allows some redundancy; BCNF eliminates the redundancy but may lose a dependency.
When BCNF matters
BCNF matters when the table has multiple candidate keys and a non-key column determines part of a candidate key. The pattern is rare, but it appears in scheduling, assignment, and allocation schemas. The developer should recognize the pattern and decide whether the redundancy or the lost dependency is more acceptable.
b. Fourth Normal Form (4NF)
A table is in 4NF when it is in BCNF and has no non-trivial multi-valued dependencies. A multi-valued dependency exists when one attribute determines a set of values for another attribute, independently of a third attribute.
The multi-valued dependency
The classic example is an employee with multiple skills and multiple languages. The two facts are independent: the skills have nothing to do with the languages, and vice versa.
CREATE TABLE employee_skills_languages (
employee VARCHAR(50),
skill VARCHAR(50),
language VARCHAR(50),
PRIMARY KEY (employee, skill, language)
);If an employee has three skills and two languages, the table must store six rows to represent every combination. The six rows encode the same information as three skill rows and two language rows. The redundancy is the multi-valued dependency.
The 4NF decomposition
The fix is to separate the two multi-valued facts into their own tables.
CREATE TABLE employee_skills (
employee VARCHAR(50),
skill VARCHAR(50),
PRIMARY KEY (employee, skill)
);
CREATE TABLE employee_languages (
employee VARCHAR(50),
language VARCHAR(50),
PRIMARY KEY (employee, language)
);The three skills are three rows in employee_skills. The two languages are two rows in employee_languages. The total is five rows, not six, and the two facts are independent.
The independence test
A multi-valued dependency exists when the two attributes are independent. The test is: for a given employee, does the set of skills depend on the languages? If adding a language does not affect the skills, and adding a skill does not affect the languages, the two are independent, and the table violates 4NF.
Employee: Alice
Skills: SQL, Python, Rust
Languages: English, French
Independent → MVD exists → Violates 4NF
Dependent → No MVD → Already in 4NFThe dependent case
If the two attributes are not independent, the table is already in 4NF. For example, if a language requires a specific skill, the two are dependent, and the combined table is correct.
-- No MVD: language depends on skill
CREATE TABLE skill_requirements (
skill VARCHAR(50),
language VARCHAR(50),
PRIMARY KEY (skill, language)
);The table represents the pairs that are valid together. The rows are not a cross-product of two independent sets.
The 4NF and join dependency
A multi-valued dependency is a special case of a join dependency. A table with an MVD can be decomposed into two tables, and the original table is the natural join of the two. The decomposition is lossless, which is why 4NF is a refinement of BCNF.
The BCNF and 4NF relationship
Every 4NF table is in BCNF, but not every BCNF table is in 4NF. The BCNF violation is about functional dependencies; the 4NF violation is about multi-valued dependencies. A table can be in BCNF and still have an MVD.
-- In BCNF but not in 4NF
CREATE TABLE employee_skills_languages (
employee VARCHAR(50),
skill VARCHAR(50),
language VARCHAR(50),
PRIMARY KEY (employee, skill, language)
);The primary key is the full combination, so every determinant is a candidate key. The table is in BCNF. But the multi-valued dependencies employee →→ skill and employee →→ language exist, so the table is not in 4NF.
The 4NF pattern
The pattern to recognize is: a table with a composite primary key of three or more columns, where two of the columns are independent sets. The fix is to separate the independent sets into their own tables, each keyed by the entity and one of the sets.
c. Fifth Normal Form (5NF)
A table is in 5NF when it is in 4NF and every join dependency is implied by the candidate keys. A join dependency exists when a table can be losslessly decomposed into three or more tables, and the original table is the join of the decomposed tables. 5NF is also called Project-Join Normal Form (PJNF).
The join dependency
The classic example is a table that records which suppliers supply which parts for which projects. The constraint is that if a supplier supplies a part, and the part is used in a project, and the supplier supplies the project, then the supplier supplies the part for the project.
CREATE TABLE supplier_part_project (
supplier VARCHAR(50),
part VARCHAR(50),
project VARCHAR(50),
PRIMARY KEY (supplier, part, project)
);The table has a join dependency: it can be decomposed into three binary tables, and the original is the join of the three.
The 5NF decomposition
The fix is to decompose into three tables.
CREATE TABLE supplier_part (
supplier VARCHAR(50),
part VARCHAR(50),
PRIMARY KEY (supplier, part)
);
CREATE TABLE part_project (
part VARCHAR(50),
project VARCHAR(50),
PRIMARY KEY (part, project)
);
CREATE TABLE supplier_project (
supplier VARCHAR(50),
project VARCHAR(50),
PRIMARY KEY (supplier, project)
);The three tables capture the pairwise relationships. The original table is the join of the three, and the join dependency is satisfied.
The cyclic versus acyclic dependency
The 5NF decomposition is needed when the join dependency is cyclic. An acyclic join dependency can be decomposed into two tables without loss, and the table is already in 4NF. The cyclic dependency requires three tables.
Acyclic: A → B, A → C → decompose into two tables (4NF)
Cyclic: A → B, B → C, C → A → decompose into three tables (5NF)The constraint in the 5NF example
The business rule is: if a supplier supplies a part, and the part is used in a project, and the supplier supplies the project, then the supplier supplies the part for the project. The rule is enforced by the three pairwise tables, not by a single three-column table. The three-column table stores every valid combination, but it also stores combinations that violate the rule.
-- The 3-column table can store an invalid combination.
INSERT INTO supplier_part_project VALUES ('S1', 'P1', 'J1');
-- If S1 supplies P1, P1 is used in J1, and S1 supplies J1,
-- the row is valid. Otherwise, it should not exist.The three-table decomposition enforces the rule by construction: a row in supplier_part and a row in part_project and a row in supplier_project imply the combination.
The 5NF rarity
5NF is rarely needed in practice. The cases where a table has a cyclic join dependency are unusual, and the decomposition adds complexity for a narrow benefit. Most schemas stop at 3NF or BCNF, and 4NF and 5NF are applied only when the specific pattern appears.
The recognition pattern
The pattern to recognize is: a table with a three-column primary key where the three columns are pairwise related by business rules. If the rules imply a cyclic dependency, the table is a candidate for 5NF. If the rules are acyclic, the table is already in 4NF.
The lossless join test
A decomposition is lossless when the natural join of the decomposed tables produces exactly the original table, with no spurious rows and no missing rows. The 5NF decomposition is lossless because every valid combination in the original table can be reconstructed from the pairwise tables, and no invalid combination is introduced.
Complete Example Session
-- ============================================
-- PART 1: THE 3NF TABLE
-- ============================================
-- A table in 3NF but with multiple candidate keys.
CREATE TABLE student_course_instructor (
student VARCHAR(50),
course VARCHAR(50),
instructor VARCHAR(50),
PRIMARY KEY (student, course),
UNIQUE (student, instructor)
);
-- Functional dependencies:
-- (student, course) → instructor
-- instructor → course
-- The table is in 3NF because course is a key attribute.
-- The table is not in BCNF because instructor is a determinant
-- that is not a candidate key.
-- ============================================
-- PART 2: THE BCNF DECOMPOSITION
-- ============================================
CREATE TABLE instructor_course (
instructor VARCHAR(50) PRIMARY KEY,
course VARCHAR(50) NOT NULL
);
CREATE TABLE student_instructor (
student VARCHAR(50),
instructor VARCHAR(50),
PRIMARY KEY (student, instructor),
FOREIGN KEY (instructor) REFERENCES instructor_course(instructor)
);
-- Both tables are in BCNF.
-- The dependency instructor → course is preserved.
-- The dependency (student, course) → instructor is lost.
-- ============================================
-- PART 3: THE MULTI-VALUED DEPENDENCY
-- ============================================
CREATE TABLE employee_skills_languages (
employee VARCHAR(50),
skill VARCHAR(50),
language VARCHAR(50),
PRIMARY KEY (employee, skill, language)
);
-- Alice has skills SQL, Python, Rust.
-- Alice has languages English, French.
-- The table stores 6 rows for Alice.
INSERT INTO employee_skills_languages VALUES
('Alice', 'SQL', 'English'),
('Alice', 'SQL', 'French'),
('Alice', 'Python', 'English'),
('Alice', 'Python', 'French'),
('Alice', 'Rust', 'English'),
('Alice', 'Rust', 'French');
-- The 6 rows encode 3 skills and 2 languages.
-- The two facts are independent.
-- ============================================
-- PART 4: THE 4NF DECOMPOSITION
-- ============================================
CREATE TABLE employee_skills (
employee VARCHAR(50),
skill VARCHAR(50),
PRIMARY KEY (employee, skill)
);
CREATE TABLE employee_languages (
employee VARCHAR(50),
language VARCHAR(50),
PRIMARY KEY (employee, language)
);
INSERT INTO employee_skills VALUES
('Alice', 'SQL'),
('Alice', 'Python'),
('Alice', 'Rust');
INSERT INTO employee_languages VALUES
('Alice', 'English'),
('Alice', 'French');
-- 5 rows instead of 6.
-- The two facts are stored independently.
-- ============================================
-- PART 5: THE JOIN DEPENDENCY
-- ============================================
CREATE TABLE supplier_part_project (
supplier VARCHAR(50),
part VARCHAR(50),
project VARCHAR(50),
PRIMARY KEY (supplier, part, project)
);
-- The table can be decomposed into three pairwise tables.
-- The original is the join of the three.
-- ============================================
-- PART 6: THE 5NF DECOMPOSITION
-- ============================================
CREATE TABLE supplier_part (
supplier VARCHAR(50),
part VARCHAR(50),
PRIMARY KEY (supplier, part)
);
CREATE TABLE part_project (
part VARCHAR(50),
project VARCHAR(50),
PRIMARY KEY (part, project)
);
CREATE TABLE supplier_project (
supplier VARCHAR(50),
project VARCHAR(50),
PRIMARY KEY (supplier, project)
);
-- The three tables capture the pairwise relationships.
-- The join of the three reconstructs the original.
-- ============================================
-- PART 7: THE LOSSLESS JOIN
-- ============================================
SELECT sp.supplier, sp.part, pp.project
FROM supplier_part sp
JOIN part_project pp ON pp.part = sp.part
JOIN supplier_project spj
ON spj.supplier = sp.supplier
AND spj.project = pp.project;
-- The result is the original table, with no spurious rows
-- and no missing rows, provided the business rule holds.
-- ============================================
-- PART 8: THE DEPENDENCY-PRESERVING TRADE-OFF
-- ============================================
-- 3NF always preserves dependencies.
-- BCNF may lose a dependency.
-- In the student_course_instructor example,
-- the dependency (student, course) → instructor is lost.
-- The database cannot enforce it without a trigger.
-- ============================================
-- PART 9: THE RECOGNITION PATTERNS
-- ============================================
-- BCNF: multiple candidate keys and a non-key determinant.
-- 4NF: two independent multi-valued facts in one table.
-- 5NF: a cyclic join dependency with pairwise rules.
-- ============================================
-- PART 10: WHEN TO APPLY
-- ============================================
-- Apply BCNF when a non-key column determines part of a key.
-- Apply 4NF when two independent multi-valued facts
-- are stored in one table.
-- Apply 5NF when a three-way relationship has pairwise rules.
-- For most schemas, 3NF is sufficient.
-- The higher forms are applied when the specific pattern appears.The ten parts showed the 3NF table, the BCNF decomposition, the multi-valued dependency, the 4NF decomposition, the join dependency, the 5NF decomposition, the lossless join, the dependency-preserving trade-off, the recognition patterns, and the decision guide.
Quick Reference
The Normal Forms
| Form | Rule | Violation |
|---|---|---|
| 1NF | Atomic values, unique rows | Multi-valued columns |
| 2NF | No partial dependencies | Non-key column depends on part of key |
| 3NF | No transitive dependencies | Non-key column depends on another |
| BCNF | Every determinant is a candidate key | Non-key determinant |
| 4NF | No non-trivial multi-valued dependencies | Independent multi-valued facts |
| 5NF | Every join dependency is implied by keys | Cyclic join dependency |
Dependency Types
| Type | Notation | Violates |
|---|---|---|
| Functional | A → B | 2NF, 3NF, BCNF |
| Multi-valued | A →→ B | 4NF |
| Join | *(A, B, C) | 5NF |
Recognition Patterns
| Pattern | Form |
|---|---|
| Multi-valued column | 1NF |
| Partial dependency | 2NF |
| Transitive dependency | 3NF |
| Non-key determinant | BCNF |
| Independent multi-valued facts | 4NF |
| Cyclic join dependency | 5NF |
The Trade-offs
| Decomposition | Preserves Dependencies | Eliminates Redundancy |
|---|---|---|
| 3NF | Always | Partial |
| BCNF | Not always | Complete |
| 4NF | Not always | Complete |
| 5NF | Not always | Complete |
Best Practices
✅ Do This:
-- Apply BCNF when a non-key column determines part of a key.
CREATE TABLE instructor_course (instructor PRIMARY KEY, course); -- ✅
-- Apply 4NF when two multi-valued facts are independent.
CREATE TABLE employee_skills (employee, skill, PRIMARY KEY (employee, skill)); -- ✅
-- Apply 5NF when pairwise rules imply a cyclic dependency.
CREATE TABLE supplier_part (supplier, part, PRIMARY KEY (supplier, part)); -- ✅
-- Test the decomposition for losslessness.
-- The join of the decomposed tables must equal the original. -- ✅
-- Preserve the business rules with constraints where possible.
FOREIGN KEY (instructor) REFERENCES instructor_course(instructor); -- ✅
-- Stop at 3NF unless the specific pattern appears.
-- The higher forms add complexity. -- ✅❌ Don’t Do This:
-- Don't apply BCNF when 3NF is sufficient.
-- The decomposition may lose a dependency. -- ❌
-- Don't store two independent multi-valued facts in one table.
CREATE TABLE employee_skills_languages (employee, skill, language); -- ❌
-- Don't assume BCNF is always better than 3NF.
-- The dependency-preservation trade-off matters. -- ❌
-- Don't apply 5NF without checking the join dependency.
-- The decomposition may be unnecessary. -- ❌
-- Don't forget the business rules.
-- The decomposed tables may not enforce them. -- ❌
-- Don't normalize for its own sake.
-- Normalize when the anomaly is real. -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| BCNF loses a dependency | Decomposition splits the columns | Use a trigger or accept the loss |
| 4NF decomposition unnecessary | The two facts are dependent | Check independence first |
| 5NF decomposition overcomplicated | The join dependency is acyclic | Use 4NF |
| Lost business rule | No cross-table constraint | Add a trigger or check |
| Over-normalization | Too many small tables | Denormalize selectively |
| Missing the BCNF pattern | Multiple candidate keys overlooked | Identify all candidate keys |
Real-World Examples
1. BCNF Violation
-- instructor → course, instructor is not a candidate key
CREATE TABLE student_course_instructor (student, course, instructor);2. BCNF Decomposition
CREATE TABLE instructor_course (instructor PRIMARY KEY, course);
CREATE TABLE student_instructor (student, instructor, PRIMARY KEY (student, instructor));3. 4NF Violation
CREATE TABLE employee_skills_languages (employee, skill, language);4. 4NF Decomposition
CREATE TABLE employee_skills (employee, skill, PRIMARY KEY (employee, skill));
CREATE TABLE employee_languages (employee, language, PRIMARY KEY (employee, language));5. 5NF Violation
CREATE TABLE supplier_part_project (supplier, part, project);6. 5NF Decomposition
CREATE TABLE supplier_part (supplier, part, PRIMARY KEY (supplier, part));
CREATE TABLE part_project (part, project, PRIMARY KEY (part, project));
CREATE TABLE supplier_project (supplier, project, PRIMARY KEY (supplier, project));7. Lossless Join Test
SELECT * FROM supplier_part
JOIN part_project USING (part)
JOIN supplier_project USING (supplier, project);8. Dependency Preservation
-- A trigger enforces the lost dependency.
CREATE TRIGGER enforce_instructor_course ...9. Independence Test
-- Are the two sets independent?
-- If adding a skill does not affect the languages, they are.10. The Decision
-- Apply 3NF by default.
-- Apply BCNF when a non-key determinant exists.
-- Apply 4NF when two multi-valued facts are independent.
-- Apply 5NF when pairwise rules imply a cyclic dependency.Visual
The Normal Forms Hierarchy
┌────────────────────────────────────────────────────────────┐
│ NORMAL FORMS HIERARCHY │
│ │
│ ┌──────────────────────────────────────────────────┐ │
│ │ 1NF │ │
│ │ Atomic values, unique rows │ │
│ │ ┌────────────────────────────────────────┐ │ │
│ │ │ 2NF │ │ │
│ │ │ No partial dependencies │ │ │
│ │ │ ┌──────────────────────────────┐ │ │ │
│ │ │ │ 3NF │ │ │ │
│ │ │ │ No transitive dependencies │ │ │ │
│ │ │ │ ┌──────────────────────┐ │ │ │ │
│ │ │ │ │ BCNF │ │ │ │ │
│ │ │ │ │ Every determinant │ │ │ │ │
│ │ │ │ │ is a candidate key │ │ │ │ │
│ │ │ │ │ ┌────────────────┐ │ │ │ │ │
│ │ │ │ │ │ 4NF │ │ │ │ │ │
│ │ │ │ │ │ No MVDs │ │ │ │ │ │
│ │ │ │ │ │ ┌──────────┐ │ │ │ │ │ │
│ │ │ │ │ │ │ 5NF │ │ │ │ │ │ │
│ │ │ │ │ │ │ No join │ │ │ │ │ │ │
│ │ │ │ │ │ │ deps │ │ │ │ │ │ │
│ │ │ │ │ │ └──────────┘ │ │ │ │ │ │
│ │ │ │ │ └────────────────┘ │ │ │ │ │
│ │ │ │ └──────────────────────┘ │ │ │ │
│ │ │ └──────────────────────────────┘ │ │ │
│ │ └────────────────────────────────────────┘ │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ Each form is stricter than the one above it. │
│ Most schemas stop at 3NF. │
└────────────────────────────────────────────────────────────┘The BCNF Pattern
┌────────────────────────────────────────────────────────────┐
│ BCNF VIOLATION │
│ │
│ Table: student_course_instructor │
│ Key: (student, course) │
│ FDs: (student, course) → instructor │
│ instructor → course │
│ │
│ ┌──────────────────────────────────────────────────┐ │
│ │ instructor is a determinant. │ │
│ │ instructor is not a candidate key. │ │
│ │ │ │
│ │ → Violates BCNF │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ DECOMPOSITION │
│ ┌──────────────────────────────────────────────────┐ │
│ │ instructor_course (instructor PK, course) │ │
│ │ student_instructor (student, instructor, PK) │ │
│ │ │ │
│ │ instructor → course is preserved. │ │
│ │ (student, course) → instructor is lost. │ │
│ └──────────────────────────────────────────────────┘ │
└────────────────────────────────────────────────────────────┘The 4NF Pattern
┌────────────────────────────────────────────────────────────┐
│ 4NF VIOLATION │
│ │
│ Table: employee_skills_languages │
│ Key: (employee, skill, language) │
│ │
│ Alice has: │
│ skills: SQL, Python, Rust │
│ languages: English, French │
│ │
│ ┌──────────────────────────────────────────────────┐ │
│ │ The 6 rows are a cross-product of 3 skills │ │
│ │ and 2 languages. The two facts are independent. │ │
│ │ │ │
│ │ → Violates 4NF │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ DECOMPOSITION │
│ ┌──────────────────────────────────────────────────┐ │
│ │ employee_skills (employee, skill, PK) │ │
│ │ 3 rows │ │
│ │ │ │
│ │ employee_languages (employee, language, PK) │ │
│ │ 2 rows │ │
│ │ │ │
│ │ Total 5 rows instead of 6. │ │
│ └──────────────────────────────────────────────────┘ │
└────────────────────────────────────────────────────────────┘The 5NF Pattern
┌────────────────────────────────────────────────────────────┐
│ 5NF VIOLATION │
│ │
│ Table: supplier_part_project │
│ Key: (supplier, part, project) │
│ │
│ Business rule: │
│ If a supplier supplies a part, and the part is used in │
│ a project, and the supplier supplies the project, then │
│ the supplier supplies the part for the project. │
│ │
│ ┌──────────────────────────────────────────────────┐ │
│ │ The table can be decomposed into three pairwise │ │
│ │ tables. The original is the join of the three. │ │
│ │ │ │
│ │ → Violates 5NF (cyclic join dependency) │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ DECOMPOSITION │
│ ┌──────────────────────────────────────────────────┐ │
│ │ supplier_part (supplier, part, PK) │ │
│ │ part_project (part, project, PK) │ │
│ │ supplier_project (supplier, project, PK) │ │
│ │ │ │
│ │ The join of the three reconstructs the original. │ │
│ └──────────────────────────────────────────────────┘ │
└────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| BCNF | Every determinant is a candidate key |
| BCNF violation | A non-key column determines part of a candidate key |
| 4NF | No non-trivial multi-valued dependencies |
| 4NF violation | Two independent multi-valued facts in one table |
| 5NF | Every join dependency is implied by the candidate keys |
| 5NF violation | A cyclic join dependency |
| MVD | Multi-valued dependency: A →→ B |
| Join dependency | A table is the join of its decompositions |
| Dependency preservation | 3NF preserves; BCNF may not |
| Lossless join | The decomposition reconstructs the original |
Key takeaways:
- BCNF is stricter than 3NF. It requires that every determinant be a candidate key. A 3NF table can have a non-key column that determines part of a candidate key, and BCNF eliminates that case.
- BCNF may lose a functional dependency. The decomposition splits the columns, and a dependency that spanned them is no longer enforceable. 3NF preserves dependencies; BCNF may not.
- 4NF eliminates multi-valued dependencies. Two independent multi-valued facts stored in the same table force a cross-product, which is redundant. Separating them into their own tables eliminates the redundancy.
- The 4NF test is independence. If the two attributes are independent — if adding a value to one does not affect the other — the table has a multi-valued dependency and violates 4NF.
- 5NF eliminates join dependencies. A table with a cyclic join dependency can be decomposed into three or more tables. The decomposition is lossless, and the original is the join of the decomposed tables.
- 5NF is rare. The cyclic join dependency pattern is unusual. Most schemas stop at 3NF or BCNF, and 4NF and 5NF are applied only when the specific pattern appears.
- The higher forms add complexity. Each decomposition adds tables and joins. The benefit is eliminating a specific anomaly. The cost is read performance, and the decision is whether the anomaly is real.
- 3NF is sufficient for most schemas. The higher forms are not applied routinely. They are applied when the schema exhibits the specific pattern that the form addresses. Recognizing the pattern is the skill.
Remember: The advanced normal forms address the cases that 3NF does not. BCNF handles the case where a non-key column determines part of a candidate key, which 3NF allows. 4NF handles the case where two independent multi-valued facts are stored in one table, which forces a cross-product. 5NF handles the case where a table has a cyclic join dependency and can be decomposed into three or more pairwise tables. Each form is stricter than the last, and each addresses a narrower class of problem. The trade-off is the same at every level: the decomposition eliminates a specific redundancy, and the cost is more tables and joins. The higher forms are not applied routinely; they are applied when the schema exhibits the pattern. Most production schemas stop at 3NF, and the advanced forms are reserved for the cases where the anomaly is real and the cost is justified.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!