Skills101

AdvancedDBMS & SQL · Lesson 25 of 25 · Updated ·

4NF and 5NF — Multivalued Dependencies, Join Dependencies and Higher Normal Forms

The normalization lesson stopped at BCNF, where every functional dependency has a superkey on its left side. Most real designs stop there too. But there is a kind of redundancy that functional dependencies cannot even describe: two independent lists stored in the same table. Fixing it gives Fourth Normal Form (4NF). A rarer, subtler case gives Fifth Normal Form (5NF). Both appear in university syllabi, usually as "define with an example".

1. The problem: two independent facts in one table

A college records which subjects each teacher can teach and which languages each teacher can teach in. The two facts have nothing to do with each other: Asha can teach DBMS or OS, in Hindi or English, in any combination.

CREATE TABLE teacher_info (
  teacher  TEXT,
  subject  TEXT,
  language TEXT,
  PRIMARY KEY (teacher, subject, language)
);
INSERT INTO teacher_info VALUES
  ('Asha', 'DBMS', 'Hindi'), ('Asha', 'DBMS', 'English'),
  ('Asha', 'OS',   'Hindi'), ('Asha', 'OS',   'English'),
  ('Ravi', 'Java', 'English');
SELECT * FROM teacher_info ORDER BY teacher, subject, language;
teacher | subject | language
Asha | DBMS | English
Asha | DBMS | Hindi
Asha | OS | English
Asha | OS | Hindi
Ravi | Java | English

There are no non-trivial functional dependencies: a teacher does not determine one subject, and the only key is all three columns together. So the table is in BCNF. Yet it is clearly redundant: Asha's 2 subjects × 2 languages need 4 rows, and every subject is stored once per language.

The anomalies are real:

  • Insertion: Asha starts teaching in Marathi. You must add two rows (one per subject). Add only one and the table claims she teaches OS in Marathi but not DBMS — a fact nobody stated.
  • Deletion: if Ravi stops teaching Java, deleting that row also loses the fact that he teaches in English.
  • Update: renaming a subject touches one row per language.

2. Multivalued dependency (MVD)

A multivalued dependency X ↠ Y ("X multi-determines Y") holds in a table R when, for each value of X, the set of Y values is independent of the other columns (Z = R − X − Y).

Formal test: whenever two rows agree on X, the table must also contain the rows formed by swapping their Y values. In teacher_info:

  • (Asha, DBMS, Hindi) and (Asha, OS, English) agree on teacher.
  • So (Asha, OS, Hindi) and (Asha, DBMS, English) must exist too — and they do.

Hence teacher ↠ subject and, by symmetry, teacher ↠ language. (MVDs always come in pairs: if X ↠ Y then X ↠ Z, where Z is the rest of the columns.)

Facts to remember:

FactMeaning
Trivial MVDX ↠ Y is trivial if Y ⊆ X or X ∪ Y = R (all columns). It always holds and causes no problem.
Every FD is an MVDif X → Y, then X ↠ Y (the "set" of Y values has just one member)
Not every MVD is an FDteacher ↠ subject holds, but teacher → subject does not
Needs 3+ columnsin a two-column table every MVD is trivial

3. Fourth Normal Form (4NF)

A table is in 4NF if, for every non-trivial multivalued dependency X ↠ Y, X is a superkey.

In teacher_info, teacher ↠ subject is non-trivial and teacher is not a superkey, so it is not in 4NF. Since every FD is an MVD, 4NF is stricter than BCNF: every 4NF table is in BCNF.

Fix: decompose on the MVD into (X, Y) and (X, Z) — one table per independent fact:

teacher_info with 5 rows, where Asha's 2 subjects and 2 languages form all 4 combinations, is decomposed into teacher_subject with 3 rows and teacher_language with 3 rows

CREATE TABLE teacher_subject AS SELECT DISTINCT teacher, subject FROM teacher_info;
CREATE TABLE teacher_language AS SELECT DISTINCT teacher, language FROM teacher_info;
SELECT (SELECT COUNT(*) FROM teacher_subject) AS subject_rows,
       (SELECT COUNT(*) FROM teacher_language) AS language_rows;
subject_rows | language_rows
3 | 3

Is the decomposition lossless? Join the two tables back and compare with the original:

SELECT COUNT(*) AS rows_after_join,
       (SELECT COUNT(*) FROM teacher_info) AS original_rows
FROM teacher_subject ts
JOIN teacher_language tl ON tl.teacher = ts.teacher;
rows_after_join | original_rows
5 | 5

The join gives back exactly the original 5 rows, so nothing was lost or invented. This is guaranteed: a decomposition of R into (X ∪ Y) and (X ∪ Z) is lossless exactly when X ↠ Y holds. Now adding Marathi for Asha is one row in teacher_language.

In practice, 4NF violations come from merging two independent M:N relationships into one table. If your ER diagram is mapped correctly (one table per M:N relationship, as in the ER-to-tables lesson), you will rarely create one.

4. Join dependencies and Fifth Normal Form (5NF)

Some tables can be decomposed losslessly into three (or more) parts but not into any two. A join dependency JD(R1, R2, …, Rn) says R is always equal to the join of its projections on R1 … Rn. An MVD is simply a join dependency with two parts.

Example: agents sell products made by companies, with a business rule: if an agent represents a company, and the agent sells a type of product, and the company makes that product, then the agent sells that company's product.

CREATE TABLE sells (agent TEXT, company TEXT, product TEXT);
INSERT INTO sells VALUES
  ('Ravi', 'Mahindra', 'Car'), ('Ravi', 'Tata', 'Truck'),
  ('Neha', 'Tata', 'Car'),     ('Ravi', 'Tata', 'Car');
CREATE TABLE agent_company   AS SELECT DISTINCT agent, company FROM sells;
CREATE TABLE company_product AS SELECT DISTINCT company, product FROM sells;
CREATE TABLE agent_product   AS SELECT DISTINCT agent, product FROM sells;
SELECT ac.agent, ac.company, cp.product
FROM agent_company ac JOIN company_product cp ON cp.company = ac.company
ORDER BY 1, 2, 3;
agent | company | product
Neha | Tata | Car
Neha | Tata | Truck
Ravi | Mahindra | Car
Ravi | Tata | Car
Ravi | Tata | Truck

Joining only these two projections invents (Neha, Tata, Truck): Neha represents Tata and Tata makes trucks, but Neha does not sell trucks. Every two-way join here produces one such spurious row. Join all three projections and the extra row disappears, because agent_product has no (Neha, Truck):

SELECT ac.agent, ac.company, cp.product
FROM agent_company ac
JOIN company_product cp ON cp.company = ac.company
JOIN agent_product ap ON ap.agent = ac.agent AND ap.product = cp.product
ORDER BY 1, 2, 3;
agent | company | product
Neha | Tata | Car
Ravi | Mahindra | Car
Ravi | Tata | Car
Ravi | Tata | Truck

A table is in 5NF (also called project-join normal form, PJNF) if every non-trivial join dependency is implied by its candidate keys.

sells (key: all three columns) is not in 5NF, because the JD(agent_company, company_product, agent_product) is not implied by the key. The 5NF design stores the three two-column tables. Then the rule above is enforced by the design itself: adding "Neha sells trucks" to agent_product automatically means she sells Tata trucks, with no risk of forgetting the matching row.

5NF cases are rare and hard to spot; they depend on a cyclic business rule like the one above. Without that rule, the three-column table would be correct as it is.

5. The full ladder

Nested normal forms: 1NF contains 2NF, which contains 3NF, then BCNF, then 4NF, then 5NF in the centre; each inner form removes one more kind of redundancy

Normal formRemoves redundancy caused by
1NFrepeating groups, non-atomic values
2NFpartial dependency on part of a composite key
3NFtransitive dependency (non-key → non-key)
BCNFany FD whose left side is not a superkey
4NFnon-trivial multivalued dependencies whose left side is not a superkey
5NFjoin dependencies not implied by the keys

Common mistakes

  • Thinking BCNF means "no redundancy at all". BCNF only deals with functional dependencies; MVDs can still cause repetition.
  • Writing teacher → subject for the example. A teacher has many subjects; the correct statement is teacher ↠ subject.
  • Calling X ↠ Y non-trivial when X ∪ Y covers every column. That MVD is trivial.
  • Decomposing every three-column table into pairs "for 5NF". Without a real join dependency, the pairs lose information.
  • Forgetting that every 4NF table is also in BCNF, and every 5NF table is also in 4NF.

Quick recap

  • X ↠ Y: for each X, the set of Y values is independent of the other columns; test by swapping Y values between rows with the same X.
  • Trivial MVD: Y ⊆ X or X ∪ Y = R. Every FD is an MVD.
  • 4NF: every non-trivial MVD has a superkey on the left. Fix: split into (X, Y) and (X, Z) — lossless.
  • Join dependency: R equals the join of several projections. 5NF (PJNF): every JD is implied by the keys.
  • 1NF ⊃ 2NF ⊃ 3NF ⊃ BCNF ⊃ 4NF ⊃ 5NF.

Practice what you learned

📝 Quiz: 4NF and 5NF5 questions · instant answers & explanations · +10 XP · counts towards your free certificateTake the quiz →