DATABASE NORMALIZATION

A Complete Study Guide for DBMS

1NF | 2NF | 3NF | BCNF | 4NF | 5NF

Covers 1NF through 5NF with detailed examples

Real-life analogies and memory tricks for every concept

Step-by-step conversion examples with tables

Comparison charts and exam-style questions

Designed for college exams and interview preparation

From Beginner to Advanced -- Master Every Normal Form

TABLE OF CONTENTS
1. Introduction to Database Normalization

2. Types of Dependencies

3. First Normal Form (1NF)

4. Second Normal Form (2NF)

5. Third Normal Form (3NF)

6. Boyce-Codd Normal Form (BCNF)

7. Fourth Normal Form (4NF)

8. Fifth Normal Form (5NF)

9. Comparison Tables

10. Final Summary & Quick Revision

11. Conclusion
1. Introduction to Database Normalization
Normalization is the process of organizing data in a database to reduce redundancy (duplicate data) and
improve data integrity. It involves dividing large tables into smaller, well-structured tables and defining
relationships between them.

Why Normalize?
• Eliminate Redundancy: Remove duplicate data that wastes storage
• Prevent Anomalies: Avoid insertion, update, and deletion anomalies
• Ensure Data Integrity: Keep data accurate and consistent
• Simplify Queries: Well-structured tables make queries easier
• Easier Maintenance: Changes to data structure are localized

The Normalization Hierarchy: UNF (Unnormalized) -> 1NF -> 2NF -> 3NF -> BCNF -> 4NF -> 5NF. Each level
builds on the previous one.
2. Types of Dependencies
Understanding dependencies is the key to mastering normalization. Each normal form targets a specific type of
dependency.

Partial Dependency
Definition: A non-key attribute depends on only a PART of a composite primary key.

Example: In (EmpID, ProjectID) -> EmpName, EmpName depends only on EmpID.

Resolved by: 2NF

Transitive Dependency
Definition: A non-key attribute depends on another non-key attribute (Key -> A -> B).

Example: StudentID -> DeptID -> DeptName. DeptName transitively depends on StudentID.

Resolved by: 3NF

Multivalued Dependency
Definition: For a key X, there exist two independent sets of values Y and Z (X ->> Y and X ->> Z).

Example: ActorID ->> Movie and ActorID ->> Language independently.

Resolved by: 4NF

Join Dependency
Definition: A table can be losslessly decomposed into 3+ smaller tables and rejoined to produce the original.

Example: Supplier-Part-Project can be split into three binary relationship tables.

Resolved by: 5NF

Dependency Type Description Resolved By

Partial Dependency A non-key attribute depends on only 2NF
a PART of a composite primary...
Transitive Dependency A non-key attribute depends on 3NF
another non-key attribute (Key -> ...
Multivalued Dependency For a key X, there exist two 4NF
independent sets of values Y and Z (...
Join Dependency A table can be losslessly decomposed 5NF
into 3+ smaller tables and r...
3. First Normal Form (1NF)

A. Definition
Textbook Definition: A relation is in 1NF if every attribute contains only atomic (indivisible) values, and each
record is unique.

Simple Explanation: Each cell in the table must hold only ONE value — no lists, no sets, no repeating groups.
Think of it as: one box = one item.

B. Main Rule
Eliminate repeating groups and multivalued attributes. Every column must contain only single (atomic)
values, and every row must be uniquely identifiable.

C. Why It Is Needed
• Removes repeating groups that make queries difficult
• Eliminates multivalued fields that cause confusion
• Makes data searchable and sortable
• Prevents ambiguity in data interpretation

D. Easy Memory Trick

1NF = 'One value per cell, No repeating groups, First step to clean data'

E. Real-Life Analogy
Imagine a student registration form where one student writes multiple phone numbers in a single phone
number field. The office can't search or sort properly. 1NF says: give each phone number its own row or
column!

F. NOT in 1NF -- Example

Student-Course Table (Violates 1NF)

Primary Key: StudentID

StudentID Name Courses

S101 Rahul DBMS, OS, CN
S102 Abhi DBMS, Java
S103 Amit OS

Problem: The 'Courses' column contains MULTIPLE values in a single cell (e.g., 'DBMS, OS, CN'). This is a
multivalued attribute — it violates 1NF because values are not atomic.
G. Converted to 1NF -- Solution
Student-Course Table (In 1NF)

StudentID Name Course

S101 Rahul DBMS
S101 Rahul OS
S101 Rahul CN
S102 Abhi DBMS
S102 Abhi Java
S103 Amit OS

How it was fixed: Each cell now contains only ONE value. The composite primary key is (StudentID, Course).
Every row is unique and every attribute is atomic.

H. Step-by-Step Conversion
Step 1: Identify columns with multiple values in a single cell

Step 2: Split each multivalued entry into separate rows

Step 3: Set a composite primary key that uniquely identifies each row

Step 4: Verify: every cell has exactly one atomic value

I. Important Notes
• * 1NF is the foundation — all higher normal forms require 1NF first
• * Atomic means indivisible in the context of the application
• * A table not in 1NF is not even considered a valid relation
• * Composite keys often emerge when converting to 1NF

J. Common Exam Questions

Q1. Define 1NF. Give an example of a table that violates 1NF and convert it.

Q2. Why is atomicity important in relational databases?

Q3. What is the difference between a repeating group and a multivalued attribute?

K. More Examples for 1NF

Here are 5 additional examples to deepen your understanding:

Example 1: Employee-Skills
Primary Key: EmpID

EmpID Name Skills

E1 Arun Java, Python, SQL
E2 Meera C++, Java
E3 Ravi Python
 Problem: Skills column has multiple values in one cell — not atomic.

Corrected:

Employee-Skill (1NF)

EmpID Name Skill

E1 Arun Java
E1 Arun Python
E1 Arun SQL
E2 Meera C++
E2 Meera Java
E3 Ravi Python

Fix: Each skill gets its own row. PK is now (EmpID, Skill).

Example 2: Doctor-Patients
Primary Key: DoctorID

DoctorID DoctorName Patients

D1 Dr. Sharma Rahul, Abhi, Amit
D2 Dr. Verma Sneha, Ravi

Problem: Patients column stores a comma-separated list — violates atomicity.

Corrected:

Doctor-Patient (1NF)

DoctorID DoctorName Patient

D1 Dr. Sharma Rahul
D1 Dr. Sharma Abhi
D1 Dr. Sharma Amit
D2 Dr. Verma Sneha
D2 Dr. Verma Ravi

Fix: One patient per row. PK becomes (DoctorID, Patient).

Example 3: Book-Authors
Primary Key: BookID

BookID Title Authors

B1 DBMS Concepts Navathe, Elmasri
B2 Let Us C Kanetkar
B3 Algorithms Cormen, Rivest, Stein

Problem: Authors column has multiple author names in one cell.

Corrected:

Book-Author (1NF)

BookID Title Author

B1 DBMS Concepts Navathe
B1 DBMS Concepts Elmasri
B2 Let Us C Kanetkar
B3 Algorithms Cormen
B3 Algorithms Rivest
B3 Algorithms Stein

Fix: Each author in a separate row. PK is (BookID, Author).

Example 4: Order-Products
Primary Key: OrderID

OrderID Customer Products

O1 Rahul Laptop, Mouse, Keyboard
O2 Abhi Phone, Charger

Problem: Products column lists multiple items — not single-valued.

Corrected:

Order-Product (1NF)

OrderID Customer Product

O1 Rahul Laptop
O1 Rahul Mouse
O1 Rahul Keyboard
O2 Abhi Phone
O2 Abhi Charger
 Fix: One product per row. PK becomes (OrderID, Product).

Example 5: Teacher-Subjects
Primary Key: TeacherID

TeacherID Name Subjects

T1 Mrs. Gupta Maths, Physics
T2 Mr. Khan Chemistry, Biology, Maths

Problem: Subjects field stores a list — breaks the atomicity rule.

Corrected:

Teacher-Subject (1NF)

TeacherID Name Subject

T1 Mrs. Gupta Maths
T1 Mrs. Gupta Physics
T2 Mr. Khan Chemistry
T2 Mr. Khan Biology
T2 Mr. Khan Maths

Fix: Each subject is now atomic. PK is (TeacherID, Subject).

4. Second Normal Form (2NF)

A. Definition
Textbook Definition: A relation is in 2NF if it is in 1NF and every non-key attribute is fully functionally
dependent on the entire primary key (no partial dependencies).

Simple Explanation: Every non-key column must depend on the WHOLE primary key, not just part of it. If your
key has two parts, every other column must need BOTH parts.

B. Main Rule
Remove partial dependencies. If a non-key attribute depends on only a part of a composite primary key,
move it to a separate table.

C. Why It Is Needed
• Eliminates partial dependency which causes data redundancy
• Reduces update anomalies (changing one fact requires multiple row updates)
• Prevents insertion anomalies (can't add data without unrelated data)
• Prevents deletion anomalies (deleting a row loses unrelated information)

D. Easy Memory Trick

2NF = 'The whole key, nothing but the key' — every non-key column depends on ALL parts of the primary
key.

E. Real-Life Analogy
In a hospital, a Doctor-Patient record has DoctorID + PatientID as the key. The doctor's specialization depends
only on DoctorID (partial dependency!). If you store it in this table, you'll repeat 'Cardiologist' for every patient
that doctor sees. 2NF says: move doctor info to its own table!

F. NOT in 2NF -- Example

Employee-Project Table (Violates 2NF)

Primary Key: (EmpID, ProjectID)

EmpID ProjectID EmpName ProjectName Hours

E1 P1 Arun Website 120
E1 P2 Arun App 80
E2 P1 Meera Website 150
E2 P2 Meera App 90

Problem: EmpName depends only on EmpID (partial dependency). ProjectName depends only on ProjectID
(partial dependency). Only 'Hours' depends on the full key (EmpID, ProjectID). This causes redundancy —
 'Arun' and 'Website' are repeated.

G. Converted to 2NF -- Solution

Employee Table

EmpID EmpName
E1 Arun
E2 Meera

Project Table

ProjectID ProjectName
P1 Website
P2 App

EmpProject Table

EmpID ProjectID Hours

E1 P1 120
E1 P2 80
E2 P1 150
E2 P2 90

How it was fixed: We decomposed the table into three: Employee (EmpID -> EmpName), Project (ProjectID -
> ProjectName), and EmpProject (EmpID, ProjectID -> Hours). No partial dependencies remain.

H. Step-by-Step Conversion
Step 1: Identify the composite primary key

Step 2: Find all non-key attributes and their dependencies

Step 3: If any non-key attribute depends on PART of the key, it's a partial dependency

Step 4: Move partially dependent attributes to a new table with the relevant part of the key

Step 5: Keep fully dependent attributes in the original table

I. Important Notes
• * 2NF only applies when the primary key is composite (has multiple columns)
• * If the primary key is a single column, a 1NF table is automatically in 2NF
• * Partial dependency = non-key attribute depends on a SUBSET of the primary key
• * Decomposition must be lossless — you should be able to reconstruct the original data using JOINs
J. Common Exam Questions
Q1. Define 2NF and partial dependency. Give an example.

Q2. Convert the given table into 2NF by removing partial dependencies.

Q3. Can a table with a single-column primary key violate 2NF? Explain.

K. More Examples for 2NF

Here are 5 additional examples to deepen your understanding:

Example 1: Student-Course-Instructor
Primary Key: (StudentID, CourseID)

StudentID CourseID StudentName CourseFee

S1 C1 Rahul 5000
S1 C2 Rahul 7000
S2 C1 Abhi 5000

Problem: StudentName depends only on StudentID; CourseFee depends only on CourseID — both are partial
dependencies.

Corrected:

Student

StudentID StudentName
S1 Rahul
S2 Abhi

Course

CourseID CourseFee
C1 5000
C2 7000

Enrollment

StudentID CourseID
S1 C1
S1 C2
S2 C1

Fix: Separated student info and course info into own tables. Enrollment links them.
Example 2: Order-Product-Details
Primary Key: (OrderID, ProductID)

OrderID ProductID CustomerName ProductPrice Qty

O1 P1 Rahul 500 2
O1 P2 Rahul 300 1
O2 P1 Abhi 500 3

Problem: CustomerName depends on OrderID only; ProductPrice depends on ProductID only.

Corrected:

Order

OrderID CustomerName
O1 Rahul
O2 Abhi

Product

ProductID ProductPrice
P1 500
P2 300

OrderItem

OrderID ProductID Qty

O1 P1 2
O1 P2 1
O2 P1 3

Fix: Customer and product details moved to their own tables.

Example 3: Doctor-Patient-Clinic
Primary Key: (DoctorID, PatientID)

DoctorID PatientID DoctorName Specialization VisitDate

D1 P1 Dr. Sharma Cardiology 2024-01-10
D1 P2 Dr. Sharma Cardiology 2024-01-12
D2 P1 Dr. Verma Neurology 2024-01-15

Problem: DoctorName and Specialization depend only on DoctorID — partial dependency.

Corrected:

Doctor

DoctorID DoctorName Specialization

D1 Dr. Sharma Cardiology
D2 Dr. Verma Neurology

Visit

DoctorID PatientID VisitDate

D1 P1 2024-01-10
D1 P2 2024-01-12
D2 P1 2024-01-15

Fix: Doctor info separated. Visit table holds only the full-key-dependent data.

Example 4: Movie-Actor-Details
Primary Key: (MovieID, ActorID)

MovieID ActorID MovieGenre ActorAge Role

M1 A1 Action 35 Hero
M1 A2 Action 28 Villain
M2 A1 Drama 35 Hero

Problem: MovieGenre depends on MovieID; ActorAge depends on ActorID — partial deps.

Corrected:

Movie

MovieID MovieGenre
M1 Action
M2 Drama

Actor

ActorID ActorAge
A1 35
A2 28
Cast

MovieID ActorID Role

M1 A1 Hero
M1 A2 Villain
M2 A1 Hero

Fix: Movie and actor facts stored once each. Cast holds combined facts.

Example 5: Teacher-Class-Schedule
Primary Key: (TeacherID, ClassID)

TeacherID ClassID TeacherPhone ClassRoom Day

T1 CL1 9876543210 Room101 Mon
T1 CL2 9876543210 Room202 Tue
T2 CL1 9123456780 Room101 Wed

Problem: TeacherPhone depends only on TeacherID; ClassRoom depends only on ClassID.

Corrected:

Teacher

TeacherID TeacherPhone
T1 9876543210
T2 9123456780

Class

ClassID ClassRoom
CL1 Room101
CL2 Room202

Schedule

TeacherID ClassID Day

T1 CL1 Mon
T1 CL2 Tue
T2 CL1 Wed

Fix: Phone and room info stored once. Schedule links teacher to class.
5. Third Normal Form (3NF)

A. Definition
Textbook Definition: A relation is in 3NF if it is in 2NF and no non-key attribute is transitively dependent on the
primary key.

Simple Explanation: No non-key column should depend on another non-key column. Every non-key column
must depend DIRECTLY on the primary key — no middleman allowed!

B. Main Rule
Remove transitive dependencies. If A -> B -> C (where A is the key, B and C are non-key), then B -> C is a
transitive dependency. Move B -> C to a new table.

C. Why It Is Needed
• Eliminates transitive dependencies that cause hidden redundancy
• Prevents update anomalies from indirect dependencies
• Ensures every non-key fact is stored exactly once
• Makes the database easier to maintain and update

D. Easy Memory Trick

3NF = 'Nothing but the key, so help me Codd!' — non-key attributes depend on the key, the whole key, and
nothing but the key.

E. Real-Life Analogy
In a bookstore database, a Book table has BookID -> PublisherID -> PublisherCity. The city depends on the
publisher, not the book directly. If the publisher moves, you'd need to update EVERY book row! 3NF says: store
publisher info separately.

F. NOT in 3NF -- Example

Student Table (Violates 3NF)

Primary Key: StudentID

StudentID Name DeptID DeptName HOD

S101 Rahul D1 CSE Dr. Sharma
S102 Abhi D1 CSE Dr. Sharma
S103 Amit D2 ECE Dr. Verma
S104 Sneha D2 ECE Dr. Verma

Problem: StudentID -> DeptID -> DeptName, HOD. DeptName and HOD depend on DeptID (a non-key
attribute), NOT directly on StudentID. This is a transitive dependency. 'CSE' and 'Dr. Sharma' are repeated for
 every CSE student.

G. Converted to 3NF -- Solution

Student Table

StudentID Name DeptID

S101 Rahul D1
S102 Abhi D1
S103 Amit D2
S104 Sneha D2

Department Table

DeptID DeptName HOD

D1 CSE Dr. Sharma
D2 ECE Dr. Verma

How it was fixed: DeptName and HOD now reside in the Department table where they depend directly on
DeptID (the primary key of that table). The Student table references DeptID as a foreign key. No transitive
dependencies remain.

H. Step-by-Step Conversion
Step 1: Ensure the table is already in 2NF

Step 2: Identify all functional dependencies among non-key attributes

Step 3: If non-key attribute X determines non-key attribute Y, that's a transitive dependency

Step 4: Create a new table with X as primary key and Y as its attribute

Step 5: In the original table, keep X as a foreign key and remove Y

I. Important Notes
• * 3NF is the most commonly targeted normal form in practical database design
• * A transitive dependency forms a chain: Key -> A -> B
• * 3NF does NOT handle all anomalies — BCNF goes further
• * Most real-world databases aim for 3NF as a balance between normalization and performance

J. Common Exam Questions

Q1. Define transitive dependency with an example. How does 3NF eliminate it?

Q2. Differentiate between partial dependency and transitive dependency.

Q3. Normalize the given relation to 3NF showing all intermediate steps.
K. More Examples for 3NF
Here are 5 additional examples to deepen your understanding:

Example 1: Employee-Department
Primary Key: EmpID

EmpID EmpName DeptID DeptName Manager

E1 Arun D1 Sales Mr. Roy
E2 Meera D1 Sales Mr. Roy
E3 Ravi D2 HR Ms. Das

Problem: EmpID->DeptID->DeptName,Manager. DeptName and Manager transitively depend on EmpID via

DeptID.

Corrected:

Employee

EmpID EmpName DeptID

E1 Arun D1
E2 Meera D1
E3 Ravi D2

Department

DeptID DeptName Manager

D1 Sales Mr. Roy
D2 HR Ms. Das

Fix: Department details now depend directly on DeptID in their own table.

Example 2: Book-Publisher
Primary Key: BookID

BookID Title PubID PubName PubCity

B1 DBMS PB1 McGraw NYC
B2 OS PB1 McGraw NYC
B3 CN PB2 Pearson London

Problem: BookID->PubID->PubName,PubCity. Publisher info transitively depends on BookID.

Corrected:
Book

BookID Title PubID

B1 DBMS PB1
B2 OS PB1
B3 CN PB2

Publisher

PubID PubName PubCity

PB1 McGraw NYC
PB2 Pearson London

Fix: Publisher details stored once. Book references PubID as FK.

Example 3: Order-Customer-City
Primary Key: OrderID

OrderID OrderDate CustID CustName City

O1 2024-01-05 C1 Rahul Delhi
O2 2024-01-06 C1 Rahul Delhi
O3 2024-01-07 C2 Abhi Mumbai

Problem: OrderID->CustID->CustName,City. Customer info is transitive.

Corrected:

Order

OrderID OrderDate CustID

O1 2024-01-05 C1
O2 2024-01-06 C1
O3 2024-01-07 C2

Customer

CustID CustName City

C1 Rahul Delhi
C2 Abhi Mumbai

Fix: Customer data separated — each customer stored once.

Example 4: Patient-Ward
Primary Key: PatientID

PatientID Name WardID WardName WardCapacity

P1 Rahul W1 General 20
P2 Abhi W1 General 20
P3 Amit W2 ICU 10

Problem: PatientID->WardID->WardName,WardCapacity. Ward info is transitive.

Corrected:

Patient

PatientID Name WardID

P1 Rahul W1
P2 Abhi W1
P3 Amit W2

Ward

WardID WardName WardCapacity

W1 General 20
W2 ICU 10

Fix: Ward details in own table. Updating capacity only needs one change.

Example 5: Movie-Director-Country
Primary Key: MovieID

MovieID Title DirectorID Director Country

M1 Film1 DR1 Nolan USA
M2 Film2 DR1 Nolan USA
M3 Film3 DR2 Hirani India

Problem: MovieID->DirectorID->Director,Country. Director info is transitive.

Corrected:
Movie

MovieID Title DirectorID

M1 Film1 DR1
M2 Film2 DR1
M3 Film3 DR2

Director

DirectorID Director Country

DR1 Nolan USA
DR2 Hirani India

Fix: Director details separated. No repeated country/name data.

6. Boyce-Codd Normal Form (BCNF)

A. Definition
Textbook Definition: A relation is in BCNF if for every functional dependency X -> Y, X is a superkey. BCNF is a
stricter version of 3NF.

Simple Explanation: Every determinant (left side of a dependency) must be a candidate key. In simple words:
only keys can determine other columns — no exceptions!

B. Main Rule
For every functional dependency A -> B in the relation, A must be a superkey. If a non-superkey attribute
determines another attribute, decompose the table.

C. Why It Is Needed
• Handles anomalies that 3NF cannot detect
• Addresses cases where a non-key attribute determines part of a candidate key
• Provides a stronger guarantee against redundancy
• Ensures complete elimination of redundancy from functional dependencies

D. Easy Memory Trick

BCNF = 'Every arrow (dependency) must start from a superkey.' If any arrow starts from a non-key, break the
table!

E. Real-Life Analogy
A university assigns professors to courses per department. A professor teaches only in ONE department, but
multiple professors can teach the same course. Here Professor -> Department, but Professor is not a key. BCNF
says: separate this out!

F. NOT in BCNF -- Example

Teaching Table (Violates BCNF)

Primary Key: (Student, Course)

Student Course Professor

Rahul DBMS Dr. A
Abhi DBMS Dr. A
Rahul OS Dr. B
Amit OS Dr. C

Problem: Candidate key: (Student, Course). FD: Professor -> Course (each professor teaches only one
course). Professor is NOT a superkey, but it determines Course. This violates BCNF. If Dr. A changes their
 course, multiple rows need updating.

G. Converted to BCNF -- Solution

Professor-Course Table

Professor Course
Dr. A DBMS
Dr. B OS
Dr. C OS

Student-Professor Table

Student Professor
Rahul Dr. A
Abhi Dr. A
Rahul Dr. B
Amit Dr. C

How it was fixed: We decomposed based on the violating FD (Professor -> Course). Now every determinant
is a key in its respective table. Professor is the key in Professor-Course, and (Student, Professor) is the key in
Student-Professor.

H. Step-by-Step Conversion
Step 1: Find all functional dependencies in the relation

Step 2: For each FD X -> Y, check if X is a superkey

Step 3: If X is NOT a superkey, decompose: create table (X, Y) with X as key

Step 4: Create another table with the remaining attributes

Step 5: Repeat until all tables satisfy BCNF

I. Important Notes
• * Every BCNF relation is in 3NF, but not every 3NF relation is in BCNF
• * BCNF decomposition may NOT always preserve all functional dependencies
• * 3NF decomposition always preserves dependencies — this is the trade-off
• * In practice, BCNF is preferred unless dependency preservation is critical

J. Common Exam Questions

Q1. How does BCNF differ from 3NF? Give an example where 3NF is satisfied but BCNF is not.

Q2. Decompose the given relation into BCNF. Is the decomposition dependency-preserving?
Q3. What is a determinant? Explain the role of superkeys in BCNF.

K. More Examples for BCNF

Here are 5 additional examples to deepen your understanding:

Example 1: Course-Room-Instructor
Primary Key: (Course, Room)

Course Room Instructor

DBMS R1 Dr. A
DBMS R2 Dr. B
OS R1 Dr. C
OS R2 Dr. A

Problem: FD: Instructor->Room (each instructor uses one room). Instructor is not a superkey — violates
BCNF.

Corrected:

Instructor-Room

Instructor Room
Dr. A R1
Dr. B R2
Dr. C R1

Course-Instructor

Course Instructor
DBMS Dr. A
DBMS Dr. B
OS Dr. C
OS Dr. A

Fix: Decomposed on violating FD. Every determinant is now a key.

Example 2: Student-Advisor-Dept
Primary Key: (Student, Department)

Student Department Advisor

Rahul CSE Dr. X
Abhi CSE Dr. Y
Amit ECE Dr. Z
Rahul ECE Dr. Z
 Problem: FD: Advisor->Department (each advisor belongs to one dept). Advisor is not a superkey.

Corrected:

Advisor-Dept

Advisor Department
Dr. X CSE
Dr. Y CSE
Dr. Z ECE

Student-Advisor

Student Advisor
Rahul Dr. X
Abhi Dr. Y
Amit Dr. Z
Rahul Dr. Z

Fix: Advisor's department stored once. No redundancy.

Example 3: Delivery-Driver-Area
Primary Key: OrderID

OrderID Driver Area

O1 Ram North
O2 Ram North
O3 Shyam South
O4 Shyam South

Problem: FD: Driver->Area (driver assigned to one area). In 3NF but violates BCNF since Driver is a
determinant but not superkey.

Corrected:

Driver-Area

Driver Area
Ram North
Shyam South
Order-Driver

OrderID Driver
O1 Ram
O2 Ram
O3 Shyam
O4 Shyam

Fix: Area info stored per driver, not per order. Clean BCNF.

Example 4: Flight-Pilot-Gate
Primary Key: Flight

Flight Pilot Gate

F1 Capt. A G1
F2 Capt. A G1
F3 Capt. B G2

Problem: FD: Pilot->Gate (each pilot uses assigned gate). Pilot is not a superkey.

Corrected:

Pilot-Gate

Pilot Gate
Capt. A G1
Capt. B G2

Flight-Pilot

Flight Pilot
F1 Capt. A
F2 Capt. A
F3 Capt. B

Fix: Gate info tied to pilot, not flight. Decomposed cleanly.

Example 5: Exam-Room-Invigilator
Primary Key: ExamCode

ExamCode Invigilator Room

EX1 Prof. P R101
 EX2 Prof. P R101
EX3 Prof. Q R202

Problem: FD: Invigilator->Room. Invigilator is not a superkey.

Corrected:

Invigilator-Room

Invigilator Room
Prof. P R101
Prof. Q R202

Exam-Invigilator

ExamCode Invigilator
EX1 Prof. P
EX2 Prof. P
EX3 Prof. Q

Fix: Room info stored per invigilator. BCNF satisfied.

7. Fourth Normal Form (4NF)

A. Definition
Textbook Definition: A relation is in 4NF if it is in BCNF and contains no non-trivial multivalued dependencies.

Simple Explanation: A table should not have two or more independent multivalued facts about the same
entity. If a student has multiple hobbies AND multiple phone numbers (independently), store them in separate
tables!

B. Main Rule
For every non-trivial multivalued dependency X ->> Y, X must be a superkey. If two independent multivalued
attributes exist, decompose the table.

C. Why It Is Needed
• Eliminates redundancy caused by independent multivalued facts
• Prevents the Cartesian product explosion of unrelated data
• Reduces storage waste from artificial combinations
• Makes insertions and deletions cleaner

D. Easy Memory Trick

4NF = 'No independent multi-facts in one table.' If two lists are unrelated, they get their own tables!

E. Real-Life Analogy
A movie actor works in multiple movies AND speaks multiple languages. These are independent facts. Storing
both in one table creates fake combinations — as if the actor speaks Hindi BECAUSE of Movie X. 4NF says:
separate movies and languages!

F. NOT in 4NF -- Example

Actor Table (Violates 4NF)

Primary Key: (ActorID, Movie, Language)

ActorID Movie Language

A1 Film1 Hindi
A1 Film1 English
A1 Film2 Hindi
A1 Film2 English
A2 Film3 Tamil

Problem: ActorID ->> Movie and ActorID ->> Language are independent multivalued dependencies. Actor
A1's movies and languages are unrelated, but we're forced to create all combinations (2 movies x 2
languages = 4 rows). This is redundant!
G. Converted to 4NF -- Solution
Actor-Movie Table

ActorID Movie
A1 Film1
A1 Film2
A2 Film3

Actor-Language Table

ActorID Language
A1 Hindi
A1 English
A2 Tamil

How it was fixed: By separating the two independent multivalued dependencies into their own tables, we
eliminate the Cartesian product redundancy. Actor A1 now has 2+2=4 rows total instead of 2x2=4 rows in
one table, and adding a new movie doesn't require duplicating languages.

H. Step-by-Step Conversion
Step 1: Ensure the table is in BCNF

Step 2: Identify multivalued dependencies (X ->> Y)

Step 3: Check if multiple independent MVDs exist for the same key

Step 4: If yes, decompose: create separate tables for each MVD

Step 5: Each new table has the key plus one multivalued attribute

I. Important Notes
• * Multivalued dependency (MVD): X ->> Y means for each X, there's a well-defined set of Y values,
independent of other attributes
• * 4NF violations cause Cartesian product redundancy
• * A trivial MVD is when Y is a subset of X or X union Y is the entire set of attributes
• * 4NF is important for tables with multiple independent 1:N relationships

J. Common Exam Questions

Q1. Define multivalued dependency. How does 4NF handle it?

Q2. Give an example of a table in BCNF but not in 4NF. Convert it to 4NF.

Q3. Differentiate between functional dependency and multivalued dependency.

K. More Examples for 4NF
Here are 5 additional examples to deepen your understanding:

Example 1: Student-Hobby-Sport
Primary Key: (StudentID, Hobby, Sport)

StudentID Hobby Sport

S1 Reading Cricket
S1 Reading Football
S1 Painting Cricket
S1 Painting Football

Problem: StudentID->>Hobby and StudentID->>Sport independently. 2 hobbies x 2 sports = 4 rows for S1.

Corrected:

Student-Hobby

StudentID Hobby
S1 Reading
S1 Painting

Student-Sport

StudentID Sport
S1 Cricket
S1 Football

Fix: Independent facts separated. 2+2=4 rows instead of 2x2=4 in one table.

Example 2: Employee-Skill-Language
Primary Key: (EmpID, Skill, Language)

EmpID Skill Language

E1 Java Hindi
E1 Java English
E1 Python Hindi
E1 Python English

Problem: EmpID->>Skill and EmpID->>Language are independent MVDs.

Corrected:
Emp-Skill

EmpID Skill
E1 Java
E1 Python

Emp-Language

EmpID Language
E1 Hindi
E1 English

Fix: Skills and languages stored independently — no fake pairings.

Example 3: Teacher-Subject-Certification
Primary Key: (TeacherID, Subject, Certification)

TeacherID Subject Certification

T1 Maths [Link]
T1 Maths [Link]
T1 Physics [Link]
T1 Physics [Link]

Problem: TeacherID->>Subject and TeacherID->>Certification are independent.

Corrected:

Teacher-Subject

TeacherID Subject
T1 Maths
T1 Physics

Teacher-Cert

TeacherID Certification
T1 [Link]
T1 [Link]

Fix: Subjects and certifications are unrelated — stored separately.

Example 4: Company-Product-Market
Primary Key: (CompanyID, Product, Market)

CompanyID Product Market

C1 Phone India
C1 Phone USA
C1 Laptop India
C1 Laptop USA

Problem: CompanyID->>Product and CompanyID->>Market independently.

Corrected:

Company-Product

CompanyID Product
C1 Phone
C1 Laptop

Company-Market

CompanyID Market
C1 India
C1 USA

Fix: Products and markets are independent lists — no Cartesian product needed.

Example 5: Author-Book-Award
Primary Key: (AuthorID, Book, Award)

AuthorID Book Award

A1 Novel1 Booker
A1 Novel1 Pulitzer
A1 Novel2 Booker
A1 Novel2 Pulitzer

Problem: AuthorID->>Book and AuthorID->>Award are independent MVDs.

Corrected:

Author-Book
 AuthorID Book
A1 Novel1
A1 Novel2

Author-Award

AuthorID Award
A1 Booker
A1 Pulitzer

Fix: Books and awards separated. Adding a new book doesn't duplicate awards.
8. Fifth Normal Form (5NF)

A. Definition
Textbook Definition: A relation is in 5NF (also called Project-Join Normal Form / PJNF) if it is in 4NF and cannot
be further decomposed without losing data (no non-trivial join dependencies).

Simple Explanation: A table is in 5NF when it cannot be split into smaller tables and then joined back without
creating false (spurious) data. It deals with complex relationships among THREE or more entities.

B. Main Rule
Every non-trivial join dependency must be implied by the candidate keys. If a table can be losslessly
decomposed into three or more smaller tables, it should be.

C. Why It Is Needed
• Handles complex multi-way relationships that 4NF misses
• Eliminates subtle redundancy from join dependencies
• Ensures the database captures only true real-world relationships
• Prevents spurious tuples when reconstructing data

D. Easy Memory Trick

5NF = 'Five = Final decomposition.' The table can't be broken down any further without losing meaning. It's
the ultimate normal form!

E. Real-Life Analogy
A supplier can supply certain parts, and certain projects need certain parts, and certain suppliers work with
certain projects. These three pairwise relationships together represent a three-way constraint. Storing all three
pairs in one table might imply false combinations. 5NF says: decompose into three binary tables if the three-
way relationship is really just pairwise!

F. NOT in 5NF -- Example

Supply Table (Violates 5NF)

Primary Key: (Supplier, Part, Project)

Supplier Part Project

S1 Bolt ProjA
S1 Nut ProjA
S1 Bolt ProjB
S2 Bolt ProjA

Problem: This table has a join dependency: it can be decomposed into three tables (Supplier-Part, Part-
Project, Supplier-Project) and rejoined losslessly. The three-way fact is actually composed of three binary
 facts. Keeping it as one table creates redundancy.

G. Converted to 5NF -- Solution

Supplier-Part Table

Supplier Part
S1 Bolt
S1 Nut
S2 Bolt

Part-Project Table

Part Project
Bolt ProjA
Nut ProjA
Bolt ProjB

Supplier-Project Table

Supplier Project
S1 ProjA
S1 ProjB
S2 ProjA

How it was fixed: The original table is decomposed into three binary relationship tables. When you JOIN all
three, you get exactly the original data — no more, no less. Each binary relationship is stored independently,
eliminating redundancy from the three-way coupling.

H. Step-by-Step Conversion
Step 1: Ensure the table is in 4NF

Step 2: Identify if the table represents a multi-way relationship (3+ entities)

Step 3: Check if the table can be decomposed into smaller projections

Step 4: Verify that joining the projections produces exactly the original table (lossless)

Step 5: If lossless decomposition is possible and reduces redundancy, perform it

I. Important Notes
• * 5NF is also called PJNF (Project-Join Normal Form)
• * 5NF violations are rare in practice but important theoretically
• * Testing for 5NF requires checking all possible decompositions
• * 5NF guarantees the database is fully normalized with respect to all dependencies

J. Common Exam Questions

Q1. What is a join dependency? How does 5NF handle it?

Q2. Explain 5NF with a real-world example of Supplier-Part-Project.

Q3. Why is 5NF also called Project-Join Normal Form? Differentiate from 4NF.

K. More Examples for 5NF

Here are 5 additional examples to deepen your understanding:

Example 1: Agent-Company-Product
Primary Key: (Agent, Company, Product)

Agent Company Product

A1 TCS Laptop
A1 TCS Phone
A1 Wipro Laptop
A2 TCS Phone

Problem: Three-way relationship decomposable into three binary tables without data loss.

Corrected:

Agent-Company

Agent Company
A1 TCS
A1 Wipro
A2 TCS

Company-Product

Company Product
TCS Laptop
TCS Phone
Wipro Laptop

Agent-Product

Agent Product
A1 Laptop
A1 Phone
A2 Phone
 Fix: Three pairwise tables. JOIN reproduces original exactly.

Example 2: Student-Subject-Semester
Primary Key: (Student, Subject, Semester)

Student Subject Semester

Rahul DBMS Sem3
Rahul DBMS Sem4
Rahul OS Sem3
Abhi DBMS Sem3

Problem: Can be decomposed into Student-Subject, Subject-Semester, Student-Semester losslessly.

Corrected:

Student-Subject

Student Subject
Rahul DBMS
Rahul OS
Abhi DBMS

Subject-Semester

Subject Semester
DBMS Sem3
DBMS Sem4
OS Sem3

Student-Semester

Student Semester
Rahul Sem3
Rahul Sem4
Abhi Sem3

Fix: Three binary facts. Natural join gives back exact original data.

Example 3: Doctor-Hospital-Treatment
Primary Key: (Doctor, Hospital, Treatment)

Doctor Hospital Treatment

 Dr. A H1 Surgery
Dr. A H1 Checkup
Dr. A H2 Surgery
Dr. B H1 Checkup

Problem: Join dependency exists — decomposable into three binary relations losslessly.

Corrected:

Doctor-Hospital

Doctor Hospital
Dr. A H1
Dr. A H2
Dr. B H1

Hospital-Treatment

Hospital Treatment
H1 Surgery
H1 Checkup
H2 Surgery

Doctor-Treatment

Doctor Treatment
Dr. A Surgery
Dr. A Checkup
Dr. B Checkup

Fix: Each binary relationship stored once. Rejoining gives original.

Example 4: Instructor-Course-Textbook
Primary Key: (Instructor, Course, Textbook)

Instructor Course Textbook

Prof. X DBMS Navathe
Prof. X DBMS Silberschatz
Prof. Y DBMS Navathe
Prof. X OS Stallings

Problem: Three-way fact is actually three pairwise facts. Join dependency exists.
Corrected:

Instructor-Course

Instructor Course
Prof. X DBMS
Prof. Y DBMS
Prof. X OS

Course-Textbook

Course Textbook
DBMS Navathe
DBMS Silberschatz
OS Stallings

Instructor-Textbook

Instructor Textbook
Prof. X Navathe
Prof. X Silberschatz
Prof. Y Navathe
Prof. X Stallings

Fix: Binary relations capture all facts. JOIN reconstructs original.

Example 5: Salesman-City-Product
Primary Key: (Salesman, City, Product)

Salesman City Product

S1 Delhi TV
S1 Delhi AC
S1 Mumbai TV
S2 Delhi AC

Problem: Decomposable into Salesman-City, City-Product, Salesman-Product losslessly.

Corrected:

Salesman-City

Salesman City
 S1 Delhi
S1 Mumbai
S2 Delhi

City-Product

City Product
Delhi TV
Delhi AC
Mumbai TV

Salesman-Product

Salesman Product
S1 TV
S1 AC
S2 AC

Fix: Pairwise tables. No redundancy. Lossless join verified.

9. Comparison Tables

Comparison: 1NF vs 2NF vs 3NF

Feature 1NF 2NF 3NF
Primary Goal Atomic values Remove partial deps Remove transitive deps
Prerequisite None Must be in 1NF Must be in 2NF
Key Type Any key Composite key focus Any key
Dependency Removed Repeating groups Partial dependency Transitive dependency
Redundancy Level High Medium Low

Comparison: 3NF vs BCNF

Feature 3NF BCNF
Rule No transitive deps Every determinant is a superkey
Strictness Less strict More strict
Dependency Preservation Always preserved May not be preserved
Practical Use Most common target Preferred when possible
Relationship All BCNF is 3NF Not all 3NF is BCNF

Comparison: 4NF vs 5NF

Feature 4NF 5NF
Handles Multivalued dependencies Join dependencies
Prerequisite Must be in BCNF Must be in 4NF
Decomposition Into 2 tables Into 3+ tables
Practical Occurrence Occasional Very rare
Also Known As — PJNF
10. Final Summary & Quick Revision

One-Line Rule for Each Normal Form

Normal Form One-Line Rule
1NF Every cell must have a single atomic value. No repeating
groups.
2NF No partial dependency — non-key attributes depend on
the FULL key.
3NF No transitive dependency — non-key attributes depend
ONLY on the key.
BCNF Every determinant must be a superkey — stricter than
3NF.
4NF No independent multivalued dependencies in the same
table.
5NF No join dependencies — cannot decompose further
without data loss.

Quick Revision Notes

1. Normalization reduces redundancy and prevents anomalies

2. 1NF ensures atomic values -- no repeating groups

3. 2NF removes partial dependencies (relevant for composite keys)

4. 3NF removes transitive dependencies (non-key depending on non-key)

5. BCNF ensures every determinant is a superkey

6. 4NF handles independent multivalued dependencies

7. 5NF handles join dependencies (three-way relationships)

8. Each normal form INCLUDES all previous normal forms

9. Most real-world databases target 3NF or BCNF

10. Over-normalization can hurt performance -- balance is key

Normalization Hierarchy Chart

Level Name Eliminates Key Concept
1 1NF Non-atomic values Atomicity
2 2NF Partial dependencies Full functional dependency
3 3NF Transitive dependencies Direct dependency on key
3.5 BCNF Non-superkey Every determinant is a key
determinants
4 4NF Multivalued dependencies Independent multi-facts
5 5NF Join dependencies Lossless decomposition
11. Conclusion
Database normalization is one of the most fundamental concepts in DBMS. Understanding normalization helps
you design efficient, consistent, and maintainable databases.

• Start with 1NF -- make sure all values are atomic

• Progress through 2NF and 3NF -- remove partial and transitive dependencies
• Aim for BCNF when possible -- it handles edge cases that 3NF misses
• Use 4NF and 5NF when needed -- for complex dependencies
• Balance normalization with performance -- controlled denormalization is acceptable

Remember: "The key, the whole key, and nothing but the key -- so help me Codd!" This famous phrase
captures the essence of normalization. Master this, and you'll master normalization!

-- End of Study Material --

Best of luck for your exams and interviews!