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

Source: https://skills101.in/learn/dbms-sql/dbms-4nf-5nf-multivalued-dependency — Skills101 free tutorial (DBMS & SQL). Updated 2026-11-21.

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.

```sql
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;
```

```text
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:

| Fact | Meaning |
|---|---|
| **Trivial MVD** | X ↠ Y is trivial if Y ⊆ X **or** X ∪ Y = R (all columns). It always holds and causes no problem. |
| **Every FD is an MVD** | if X → Y, then X ↠ Y (the "set" of Y values has just one member) |
| **Not every MVD is an FD** | teacher ↠ subject holds, but teacher → subject does not |
| **Needs 3+ columns** | in 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](https://skills101.in/learn-img/dbms-sql/mvd-decomposition.svg)

```sql
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;
```

```text
subject_rows | language_rows
3 | 3
```

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

```sql
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;
```

```text
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.

```sql
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;
```

```text
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):

```sql
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;
```

```text
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](https://skills101.in/learn-img/dbms-sql/normal-form-ladder.svg)

| Normal form | Removes redundancy caused by |
|---|---|
| 1NF | repeating groups, non-atomic values |
| 2NF | partial dependency on part of a composite key |
| 3NF | transitive dependency (non-key → non-key) |
| BCNF | any FD whose left side is not a superkey |
| **4NF** | non-trivial **multivalued** dependencies whose left side is not a superkey |
| **5NF** | **join** 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 quiz and free certificate: https://skills101.in/learn/dbms-sql/dbms-4nf-5nf-multivalued-dependency