Database normalization is the process of organizing data in a relational database to reduce redundancy, avoid anomalies, and improve data integrity. Each “normal form” adds rules that make your tables cleaner and more reliable.
This guide covers 1NF to 5NF, plus BCNF, with simple examples and real-world scenarios.
Examples and Scenarios
🧱 1NF — First Normal Form
Rule Summary
All values must be atomic (no arrays, lists, or repeating groups).
Each row must be unique.
Columns must not mix different data types.
Every table should have a primary key.
Storing a repeating group of data items in a single row violates 1NF.
❌ Example (Not in 1NF) - Must be atomic
StudentID
Name
Subjects
1
Rose
Math, English, PE
✅ Fix (1NF)
StudentID
Name
Subject
1
Rose
Math
1
Rose
English
1
Rose
PE
❌ Example (Not in 1NF) - No mixing of data types
StudentID
Name
Grades
1
Rose
80 to 85
❌ Example (Not in 1NF) - A table without a primary key violates
Employee Attendance (Incorrect Table)
EmployeeName
Date
Status
Rose G
2024-01-01
Present
Rose G
2024-01-01
Present
Rose G
2024-01-02
Absent
✅ Fix (1NF) - with primary key
AttendanceID
EmployeeName
Date
Status
1
Rose G
2024-01-01
Present
2
Rose G
2024-01-01
Present
3
Rose G
2024-01-02
Absent
❌ Example (Not in 1NF) - Storing a repeating group of data items
Student Phone Numbers (Incorrect Table)
StudentName
Phone1
Phone2
Phone3
Rose G
0917123456
0998123456
0922123456
John D
0918123000
NULL
NULL
✅ Fix (1NF) - Storing a repeating group of data items
Student Table
StudentID
StudentName
1
Rose G
2
John D
Student Phones Table
PhoneID
StudentID
Phone
1
1
0917123456
2
1
0998123456
3
1
0922123456
4
2
0918123000
🧩 2NF — Second Normal Form
Rule
Must be in 1NF
No partial dependency (non-key column depends on only part of a composite key)
No non-prime attribute that depends on part of a candidate key
Notes
Non-prime attributes = NOT part of ANY candidate key
❌ Example (Not in 2NF)
Composite Key: (StudentID, CourseID)
StudentID
CourseID
StudentName
CourseName
1
MATH101
Rose G
Calculus
1
ENG201
Rose G
Literature
2
MATH101
John D
Calculus
❌ Why This Violates 2NF
StudentName depends only on StudentID
CourseName depends only on CourseID
These are partial dependencies, so the table is not in 2NF.
✅ Fix (2NF)
Students
StudentID
StudentName
1
Rose G
2
John D
Courses
CourseID
CourseName
MATH101
Calculus
ENG201
Literature
Enrollment
StudentID
CourseID
1
MATH101
1
ENG201
2
MATH101
🧠 3NF — Third Normal Form
Rule
Must be in 2NF
No transitive dependency (non-key depends on another non-key)
Every non-key attribute in a table should depend on the key, the whole key, and nothing but the key.
Notes
Non-key attribute = NOT part of the primary key
❌ Example (Not in 3NF)
EmployeeID
DepartmentID
DepartmentName
10
D01
Finance
✅ Fix (3NF)
Employees
EmployeeID
DepartmentID
10
D01
Departments
DepartmentID
DepartmentName
D01
Finance
🔐 BCNF — Boyce‑Codd Normal Form
Rule
Every determinant must be a superkey = Only keys are allowed to determine other attributes
If A → B, then A must be a superkey (anything that uniquely identifies the row)
Stronger version of 3NF
❌ Example (Not in BCNF)
Rules:
Each Course has one Instructor
Each Instructor teaches in one Room
Why this is valid 3NF
Instructor does determine Room (Instructor -> Room)
But Instructor does not determine Course (the key)
So there is no forbidden transitive dependency
Course
Instructor
Room
CS101
Ana
R1
CS102
Pete
R2
Functional Dependencies
Course → Instructor
Instructor → Room
Problem
Instructor determines Room but is not a key.
✅ Fix (BCNF)
CourseInstructors
Course
Instructor
CS101
Ana
InstructorRooms
Instructor
Room
Ana
R1
🧬 4NF — Fourth Normal Form
Rule
Must be in BCNF
No multi-valued dependencies
A table should not contain two or more independent multi-valued attributes
❌ Example (Not in 4NF)
A teacher can:
Teach multiple Subjects
Speak multiple Languages These two sets are independent.
Teacher
Subject
Language
Ana
Math
English
Ana
Math
Spanish
Ana
Science
English
Ana
Science
Spanish
Problem
Subjects and Languages are unrelated, causing unnecessary combinations.
✅ Fix (4NF)
TeacherSubjects
Teacher
Subject
Ana
Math
Ana
Science
TeacherLanguages
Teacher
Language
Ana
English
Ana
Spanish
🧩 5NF — Fifth Normal Form
Rule
Must be in 4NF
No join dependencies
A table should be decomposed only if it can be split into smaller tables without losing information
Used for complex many-to-many-to-many relationships
❌ Example (Not in 5NF)
A company assigns:
Vendors who can supply certain Products
Products that can be delivered to certain Regions
Vendors that can deliver to certain Regions
But these relationships are independent.
Vendor
Product
Region
V1
P1
R1
V1
P1
R2
V1
P2
R1
V2
P1
R1
Problem
The table mixes three independent relationships:
Vendor ↔ Product
Product ↔ Region
Vendor ↔ Region
This creates redundant combinations.
✅ Fix (5NF)
Break into three independent tables:
VendorProducts
Vendor
Product
V1
P1
V1
P2
V2
P1
ProductRegions
Product
Region
P1
R1
P1
R2
P2
R1
VendorRegions
Vendor
Region
V1
R1
V1
R2
V2
R1
📝 Terminologies in simplified manner
Primary key = the official winner
Candidate keys = all contestants who could win
Prime attributes = contestants
Non‑prime attributes = people who didn't even join the contest
Non‑key attributes = contestants who didn’t win the crown
Super Key = Any contestant or group of contestants that can guarantee a unique winner