All Normal Forms Database Normalisation

Database Normalisation
A Complete Guide to All Normal Forms
1NF · 2NF · 3NF · BCNF · 4NF · 5NF

Contents

1 Preliminaries — Key Concepts 2

1.1 Functional Dependency (FD) . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.2 Candidate Key, Superkey, and Prime Attribute . . . . . . . . . . . . . . . 2
1.3 Anomalies that Normalisation Eliminates . . . . . . . . . . . . . . . . . . . 2
1.4 Normalisation Ladder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

2 First Normal Form (1NF) 4

2.1 Violation Type 1 — Multi-valued Cell . . . . . . . . . . . . . . . . . . . . 4
2.2 Violation Type 2 — Repeating Column Groups . . . . . . . . . . . . . . . 4

3 Second Normal Form (2NF) 6

3.1 Identifying a Partial Dependency . . . . . . . . . . . . . . . . . . . . . . . 6
3.2 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.3 How to Convert to 2NF . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.4 Step-by-Step Conversion Algorithm . . . . . . . . . . . . . . . . . . . . . . 7

4 Third Normal Form (3NF) 8

4.1 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
4.2 How to Convert to 3NF . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
4.3 Step-by-Step Conversion Algorithm . . . . . . . . . . . . . . . . . . . . . . 9
4.4 Synthesis Algorithm for 3NF (Bernstein’s Algorithm) . . . . . . . . . . . . 9

5 Boyce-Codd Normal Form (BCNF) 11

5.1 The 3NF Loophole that BCNF Closes . . . . . . . . . . . . . . . . . . . . . 11
5.2 The Classic Counter-Example . . . . . . . . . . . . . . . . . . . . . . . . . 11
5.3 How to Convert to BCNF . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
5.4 The Critical Tradeoff . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

6 Fourth Normal Form (4NF) 13

6.1 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
6.2 How to Convert to 4NF . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

7 Fifth Normal Form (5NF) 15

7.1 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
7.2 How to Convert to 5NF . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

8 Summary and Quick Reference 17

8.1 Normal Form Comparison Table . . . . . . . . . . . . . . . . . . . . . . . . 17
8.2 Conversion Recipes at a Glance . . . . . . . . . . . . . . . . . . . . . . . . 17
8.3 Practical Guidance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

1
All Normal Forms Database Normalisation

Preliminaries — Key Concepts

Functional Dependency (FD)

Definition: Functional Dependency

A functional dependency X → Y holds on relation R if, for every valid instance of
R, whenever two tuples agree on the attributes in X, they must also agree on every
attribute in Y . We say “X determines Y ” or “Y is functionally dependent on X”.

Notation:
• attr — primary key attribute (underlined, blue)
• attr — foreign key attribute (italic, teal)
• attr — attribute involved in a violation (red)

Candidate Key, Superkey, and Prime Attribute

Definition: Keys
• Superkey: A set of attributes that uniquely identifies every tuple.
• Candidate key: A minimal superkey — removing any attribute loses the unique-
ness property.
• Primary key: The candidate key chosen as the principal identifier.
• Prime attribute: Any attribute that is part of any candidate key.
• Non-prime attribute: An attribute that belongs to no candidate key.

Anomalies that Normalisation Eliminates

Anomaly Description
Update anomaly The same fact is stored in multiple rows; changing it in
one place but not others leaves the database
inconsistent.
Insert anomaly A new fact cannot be recorded unless some other
unrelated fact is also recorded.
Delete anomaly Deleting a row inadvertently destroys the only record
of another fact.

2
All Normal Forms Database Normalisation

Normalisation Ladder

Fifth Normal Form (5NF)

Fourth Normal Form (4NF) join dependencies

BCNF multi-valued dependencies

Third Normal Form (3NF) non-superkey determinants

Second Normal Form (2NF) transitive dependencies

First Normal Form (1NF) partial dependencies

Unnormalised (UNF) repeating groups, multi-valued cells

3
All Normal Forms Database Normalisation

First Normal Form (1NF)

Definition: 1NF
A relation is in First Normal Form (1NF) if and only if:
1. Every attribute contains only atomic (indivisible) values.
2. There are no repeating groups — no sets, lists, or arrays stored in a single
cell, and no sets of columns that represent the same concept (e.g. phone1, phone2,
phone3).
3. Every row is uniquely identifiable (a primary key exists).

Violation Type 1 — Multi-valued Cell

Before (Unnormalised)

student id name courses

1 Arjun Maths, Physics
2 Priya Maths, Chemistry,
Biology
3 Kiran Physics

Violation: The courses column stores a comma-separated list — multiple values in

a single cell. A query like “find all students enrolled in Maths” cannot use a simple
equality predicate.

How to Convert

Rule: Decompose multi-valued cells by creating one row per atomic value. The
primary key becomes a composite of the original key plus the multi-valued attribute.

After (1NF)

student id course name

1 Maths Arjun
1 Physics Arjun
2 Maths Priya
2 Chemistry Priya
2 Biology Priya
3 Physics Kiran

Violation Type 2 — Repeating Column Groups

4
All Normal Forms Database Normalisation

Before (Unnormalised)

order id item1 item2 item3

101 Pen Notebook NULL
102 Stapler NULL NULL
103 Ruler Eraser Compass

Violation: Columns item1, item2, item3 are a repeating group. This design im-
poses an arbitrary ceiling (3 items), wastes space with NULLs, and makes queries
across all items awkward.

After (1NF)

order id item
101 Pen
101 Notebook
102 Stapler
103 Ruler
103 Eraser
103 Compass

Note: 1NF alone does not eliminate all redundancy. In the student–course table
above, Arjun is repeated on every row of his enrolments. That redundancy is ad-
dressed by higher normal forms.

5
All Normal Forms Database Normalisation

Second Normal Form (2NF)

Definition: 2NF
A relation is in Second Normal Form (2NF) if:
1. It is in 1NF, and
2. Every non-prime attribute is fully functionally dependent on the entire
primary key — i.e. no non-prime attribute is partially dependent on a proper
subset of the primary key.
Note: 2NF is only relevant when the primary key is composite. A relation with a
single-column PK is automatically in 2NF if it is in 1NF.

Identifying a Partial Dependency

Definition: Partial Dependency

A partial dependency exists when a non-prime attribute A is functionally deter-
mined by a proper subset K ′ of the primary key K:

K′ ⊊ K and K ′ → A

Example
Before (1NF, not 2NF)

student id course id grade student name

1 CS101 A Arjun
1 MA201 B+ Arjun
2 CS101 B Priya
2 PH301 A+ Priya
3 MA201 A Kiran

Functional Dependency Analysis

(student id, course id) → grade (full dependency — OK)

student id → student name (partial dependency — violation!)

Violation: student name depends only on student id, not on the full composite
key. This causes:
• Update anomaly: Changing Arjun’s name requires updating multiple rows.
• Insert anomaly: Cannot add a student without also assigning a course.
• Delete anomaly: Deleting Kiran’s only course also erases Kiran’s existence.

6
All Normal Forms Database Normalisation

How to Convert to 2NF

Rule: For each partial dependency K ′ → A, create a new relation with K ′ as the
primary key and A as its attribute. Remove A from the original relation, leaving K ′
as a foreign key.

After (2NF) — Two Relations

Relation 1: Enrolment
student id course id grade
1 CS101 A
1 MA201 B+
2 CS101 B
2 PH301 A+
3 MA201 A
Relation 2: Student
student id student name
1 Arjun
2 Priya
3 Kiran

Step-by-Step Conversion Algorithm

1. Identify the composite primary key (K1 , K2 , . . .).
2. List every non-prime attribute and its minimal determinant.
3. For each non-prime attribute A where determinant(A) ⊊ P K:
a. Create relation Ri (determinant(A), A).
b. Remove A from the original relation.
c. The determinant in the original relation becomes a foreign key.
4. Verify: every remaining non-prime attribute requires the full PK to be determined.

7
All Normal Forms Database Normalisation

Third Normal Form (3NF)

Definition: 3NF
A relation is in Third Normal Form (3NF) if:
1. It is in 2NF, and
2. For every non-trivial functional dependency X → Y , either X is a superkey, or
Y is a prime attribute (part of some candidate key).
Equivalently: no non-prime attribute transitively depends on the primary key
through another non-prime attribute.

Definition: Transitive Dependency

A transitive dependency exists when:

PK → X → Y

where X is a non-prime attribute and Y is a non-prime attribute, so Y is indirectly

determined by the PK via X.

Example
Before (2NF, not 3NF)

student id zip code city state

1 400001 Mumbai Maharashtra
2 400001 Mumbai Maharashtra
3 110001 Delhi Delhi
4 560001 Bengaluru Karnataka
5 110001 Delhi Delhi

Functional Dependency Chain

{z id} −→ |zip{z
student
| code −→ city, state
} | {z }
PK non-prime non-prime

Violation: city and state are facts about zip code, not about a student. Storing
them in the Student table means:
• Update anomaly: If Mumbai’s zip reclassification changes the city name, all
student rows with that zip must be updated.
• Insert anomaly: A new zip code cannot be recorded without attaching a
student to it.
• Delete anomaly: Deleting all students with zip 560001 loses the Bengaluru
mapping.

8
All Normal Forms Database Normalisation

How to Convert to 3NF

Rule: For each transitive dependency chain P K → X → Y , create a new relation

with X as the primary key and Y as its attributes. Remove Y from the original
relation, leaving X as a foreign key.

After (3NF) — Two Relations

Relation 1: Student
student id zip code
1 400001
2 400001
3 110001
4 560001
5 110001
Relation 2: ZipCode

zip code city state

400001 Mumbai Maharashtra
110001 Delhi Delhi
560001 Bengaluru Karnataka

Step-by-Step Conversion Algorithm

1. Confirm the relation is in 2NF.
2. For every pair of non-prime attributes (X, Y ), test whether X → Y holds.
3. For each confirmed transitive dependency P K → X → Y :
a. Create relation Ri (X, Y ) with X as the primary key.
b. Remove Y from the original relation.
c. X remains in the original relation as a foreign key referencing Ri .
4. Verify: no non-prime attribute determines any other non-prime attribute.

Synthesis Algorithm for 3NF (Bernstein’s Algorithm)

When starting from a set of FDs rather than an existing table, the systematic approach
is:
1. Compute canonical cover Fc : eliminate redundant FDs and extraneous at-
tributes from each FD’s left-hand side.
2. Group FDs by left-hand side (same LHS ⇒ same relation).
3. Create one relation per group: Ri (LHS, RHS1 , RHS2 , . . .).

9
All Normal Forms Database Normalisation

4. If no relation contains a candidate key of the original schema, add a new relation
consisting of any one candidate key.
5. Eliminate redundant relations (those whose schema is a subset of another relation
in the result).

10
All Normal Forms Database Normalisation

Boyce-Codd Normal Form (BCNF)

Definition: BCNF
A relation is in Boyce-Codd Normal Form (BCNF) if:
1. It is in 3NF, and
2. For every non-trivial functional dependency X → Y , X must be a superkey.
BCNF is strictly stronger than 3NF: every BCNF relation is in 3NF, but not vice
versa.

The 3NF Loophole that BCNF Closes

3NF permits X → Y when X is not a superkey provided Y is a prime attribute. BCNF
removes this exception entirely.

The Classic Counter-Example

Scenario: Each student can enrol in multiple subjects; each subject has multiple teachers;
each teacher teaches exactly one subject.

Before (3NF, not BCNF)

student subject teacher

Arjun Maths Prof. Sharma
Arjun Physics Prof. Iyer
Priya Maths Prof. Sharma
Priya Physics Prof. Iyer

Candidate Keys and FD Analysis

Two candidate keys exist: CK1 = (student, subject) and CK2 = (student, teacher).

(student, subject) → teacher (determinant is a superkey — OK)

teacher → subject (teacher is not a superkey — BCNF violation!)

Why does it pass 3NF? Because subject is a prime attribute (part of CK1 ), the 3NF
loophole applies. BCNF ignores this exception.

How to Convert to BCNF

Rule (BCNF Decomposition): Find a relation R and a non-trivial FD X → Y

in R where X is not a superkey. Decompose R into:
• R1 = (X ∪ Y ) — with X as primary key,
• R2 = (R − Y ) — original schema minus Y , retaining X as FK.
Repeat until all relations are in BCNF.

11
All Normal Forms Database Normalisation

After (BCNF) — Two Relations

Relation 1: TeacherSubject

teacher subject
Prof. Sharma Maths
Prof. Iyer Physics
Relation 2: StudentTeacher
student teacher
Arjun Prof. Sharma
Arjun Prof. Iyer
Priya Prof. Sharma
Priya Prof. Iyer

The Critical Tradeoff

BCNF may lose dependency preservation. The FD (student, subject) →

teacher no longer resides in any single relation — it must be checked via a join of
StudentTeacher and TeacherSubject. 3NF always guarantees dependency preser-
vation; BCNF does not. In practice, if dependency preservation is critical, 3NF is
preferred over BCNF.

12
All Normal Forms Database Normalisation

Fourth Normal Form (4NF)

Definition: Multi-valued Dependency (MVD)

A multi-valued dependency X ↠ Y holds on relation R(X, Y, Z) if the set of Y -

values associated with a given X-value is independent of the Z-values. Formally: if
tuples (x, y1 , z1 ) and (x, y2 , z2 ) are in R, then (x, y1 , z2 ) and (x, y2 , z1 ) must also be
in R.

Definition: 4NF
A relation is in Fourth Normal Form (4NF) if:
1. It is in BCNF, and
2. For every non-trivial multi-valued dependency X ↠ Y, X is a superkey.

Example
Scenario: A lecturer can teach multiple courses and speak multiple languages. Courses
and languages are independent of each other.

Before (BCNF, not 4NF)

lecturer course language

Dr. Mehta Databases Hindi
Dr. Mehta Databases English
Dr. Mehta Algorithms Hindi
Dr. Mehta Algorithms English

Multi-valued Dependency Analysis

lecturer ↠ course and lecturer ↠ language

Both MVDs exist independently. Storing them in one table forces every (lecturer, course)
to appear with every (lecturer, language), generating spurious combinations and redun-
dancy.

Violation: If Dr. Mehta starts teaching a new course, a new row must be added for
every language she speaks — and vice versa. The two facts (courses taught, languages
spoken) are entangled in one table.

How to Convert to 4NF

Rule: For each non-trivial MVD X ↠ Y where X is not a superkey, decompose into:
• R1 (X, Y ) — one relation per independent multi-valued fact,
• R2 (X, Z) — the remaining attributes.

13
All Normal Forms Database Normalisation

After (4NF) — Two Relations

Relation 1: LecturerCourse
lecturer course
Dr. Mehta Databases
Dr. Mehta Algorithms
Relation 2: LecturerLanguage

lecturer language
Dr. Mehta Hindi
Dr. Mehta English

14
All Normal Forms Database Normalisation

Fifth Normal Form (5NF)

Definition: Join Dependency (JD)

A relation R satisfies a join dependency (JD) ▷◁ {R1 , R2 , . . . , Rn } if R can be

losslessly reconstructed by joining its projections R1 , R2 , . . . , Rn .

Definition: 5NF / PJNF

A relation is in Fifth Normal Form (5NF) (also called Project-Join Normal

Form, PJNF) if:
1. It is in 4NF, and
2. Every non-trivial join dependency is implied by the candidate keys — i.e. the
relation cannot be further losslessly decomposed.

Example
Scenario: Agents sell products on behalf of companies, but not every agent sells every
company’s products, and not every product is offered by every company.

Before (4NF, not 5NF)

agent company product

A1 Acme Pen
A1 Acme Ink
A1 Bata Shoe
A2 Acme Pen
The table contains a cyclic join dependency:
▷◁ {(agent, company), (company, product), (agent, product)}

This means all three pairwise projections can be joined to reconstruct the original relation
without spurious tuples — a non-trivial join dependency not implied by any key.

How to Convert to 5NF

Rule: Decompose into three binary (or minimal) projections. Each projection is
independently maintained; the original relation is derived only when all three are
joined.

After (5NF) — Three Relations

AgentCompany CompanyProduct
agent company company product
A1 Acme Acme Pen
A1 Bata Acme Ink
A2 Acme Bata Shoe

15
All Normal Forms Database Normalisation

AgentProduct
agent product
A1 Pen
A1 Ink
A1 Shoe
A2 Pen

16
All Normal Forms Database Normalisation

Summary and Quick Reference

Normal Form Comparison Table

NF Eliminates Requires Key question to ask

rowalt Non-atomic values, Every cell Is every cell a single,

1NF repeating groups atomic; PK indivisible value?
exists
2NF Partial 1NF; composite Does every non-prime
dependencies PK attribute need the whole
PK?
rowalt Transitive 2NF Does any non-prime
3NF dependencies attribute determine
another non-prime
attribute?
BCNF All non-superkey 3NF (may lose Is every FD’s
determinants dep. determinant a superkey?
preservation)
rowalt Non-trivial MVDs BCNF Are independent
4NF with non-superkey multi-valued facts stored
LHS in one table?
5NF Non-trivial join 4NF Can the table be split
dependencies not into smaller projections
implied by keys that rejoin losslessly?

Conversion Recipes at a Glance

17
All Normal Forms Database Normalisation

Practical Guidance

• 3NF is the standard target for most production databases. It is always achievable
losslessly and preserves all functional dependencies.
• BCNF is preferred when redundancy persists after 3NF, but only when losing
dependency preservation is acceptable.
• 4NF and 5NF are rarely needed in practice and are primarily of theoretical in-
terest; however, 4NF problems do occasionally appear in schemas modelling many-
to-many-to-many relationships.
• Over-normalisation increases the number of joins required for common queries.
Always balance normalisation against query performance.

18