Database normalization is a fundamental methodology in relational database design. Its main objectives are:
- Reduce redundancy
- Prevent anomalies
- Maintain data integrity
This document provides an overview of the six normal forms, including BCNF, with practical examples.
| Step | Description |
|---|---|
| UNF | Unnormalized Form Data may contain repeating groups or nested structures. |
| ↓ | |
| 1NF | First Normal Form (1NF) All attributes are atomic; no repeating groups. |
| ↓ | |
| 2NF | Second Normal Form (2NF) No partial dependencies; every non-key attribute depends on the full key. |
| ↓ | |
| 3NF | Third Normal Form (3NF) No transitive dependencies; non-key attributes depend only on the key. |
| ↓ | |
| BCNF | Boyce–Codd Normal Form (BCNF) Every determinant is a superkey; resolves anomalies beyond 3NF. |
| ↓ | |
| 4NF | Fourth Normal Form (4NF) No multi-valued dependencies. |
| ↓ | |
| 5NF | Fifth Normal Form (5NF) Only join dependencies implied by keys; removes complex redundancy. |
| ↓ | |
| 6NF | Sixth Normal Form (6NF) Decomposed to the smallest relations; used for temporal or warehouse data. |
A relation is in 1NF if:
- Each attribute domain is atomic.
- No repeating groups or nested relations exist.
Example:
| StudentID | Name | PhoneNumbers |
|---|---|---|
| 1 | Alice | 123-456, 234-567 |
| 2 | Bob | 345-678 |
Normalized 1NF version:
| StudentID | Name | PhoneNumber |
|---|---|---|
| 1 | Alice | 123-456 |
| 1 | Alice | 234-567 |
| 2 | Bob | 345-678 |
A relation is in 2NF if:
- It satisfies 1NF.
- Every non-key attribute depends on the whole key.
Example:
Composite key (StudentID, CourseID):
Original Table:
| StudentID | CourseID | StudentName | Grade |
|---|---|---|---|
| 1 | C101 | Alice | A |
| 1 | C102 | Alice | B |
2NF Decomposition:
Students Table:
| StudentID | StudentName |
|---|---|
| 1 | Alice |
Enrollments Table:
| StudentID | CourseID | Grade |
|---|---|---|
| 1 | C101 | A |
| 1 | C102 | B |
A relation is in 3NF if:
- It satisfies 2NF.
- No transitive dependencies exist.
Example:
| CourseID | ProfessorID | ProfessorName |
|---|---|---|
| C101 | P001 | Dr. Smith |
3NF Decomposition:
Courses Table:
| CourseID | ProfessorID |
|---|---|
| C101 | P001 |
Professors Table:
| ProfessorID | ProfessorName |
|---|---|
| P001 | Dr. Smith |
A relation is in BCNF if:
- Every determinant is a superkey.
Example:
| StudentID | CourseID | Instructor |
|---|---|---|
| 1 | C101 | Dr. Smith |
| 2 | C101 | Dr. Smith |
BCNF Decomposition:
Courses Table:
| CourseID | Instructor |
|---|---|
| C101 | Dr. Smith |
Enrollments Table:
| StudentID | CourseID |
|---|---|
| 1 | C101 |
| 2 | C101 |
A relation is in 4NF if:
- It is in BCNF.
- No multi-valued dependencies exist.
Example:
| Professor | Course | Language |
|---|---|---|
| Dr. A | Math | English |
| Dr. A | Math | French |
| Dr. A | Physics | English |
| Dr. A | Physics | French |
4NF Decomposition:
ProfessorCourses Table:
| Professor | Course |
|---|---|
| Dr. A | Math |
| Dr. A | Physics |
ProfessorLanguages Table:
| Professor | Language |
|---|---|
| Dr. A | English |
| Dr. A | French |
A relation is in 5NF if:
- All join dependencies are implied by candidate keys.
Example:
Suppliers, Parts, Projects table decomposed into three smaller tables: SupplierParts, SupplierProjects, PartProjects.
A relation is in 6NF if:
- It is decomposed into the smallest possible relations.
- Mainly used in temporal databases or data warehouses.
Example:
EmployeeRole Table:
| EmployeeID | Role |
|---|---|
| 1 | Manager |
EmployeeRolePeriod Table:
| EmployeeID | StartDate | EndDate |
|---|---|---|
| 1 | 2020-01-01 | 2021-12-31 |
| Normal Form | Core Requirement | Prevents |
|---|---|---|
| 1NF | Atomic values only | Repeating groups |
| 2NF | Full dependency on key | Partial dependency |
| 3NF | No transitive dependencies | Update anomalies |
| BCNF | Determinants must be superkeys | Redundancy beyond 3NF |
| 4NF | No multi-valued dependencies | Unnecessary combinations |
| 5NF | Only key-implied joins | Complex redundancy |
| 6NF | Single dependency per relation | Temporal inconsistencies |
- MIT – Database and Internet Systems Integration, Lecture 10
https://ocw.mit.edu/courses/1-264j-database-internet-and-systems-integration-technologies-fall-2013/8230628ff6c6aeff33f1c096a78f2bd8_MIT1_264JF13_lect_10.pdf - University of Texas at Arlington – Lecture Notes on Database Normalization
https://idir.uta.edu/~nyan/cse3330/slides/lecture%20notes%20on%20database%20normalization.pdf - University of Toronto – CSC343, Week 12: Normal Forms
https://www.cs.toronto.edu/~faye/343/f07/lectures/wk12/12_NormalFormsRevised2-up.pdf - Duke University – Design Theory and Normalization
https://courses.cs.duke.edu/spring22/compsci516/Lectures/Lecture-7-Normalization.pdf - Northeastern University – CS3200 Databases, Lecture 07
https://course.ccs.neu.edu/cs3200f20s2/ssl/lectures/lecture_07_normalization.pdf