Database Normalization: A Complete
Step-by-Step Guide (1NF to 4NF)
Understanding normalization is easiest when you see how a poorly designed table is broken
down step-by-step. Below, we start with a "monster" table that violates every normalization rule,
and we will fix it one step at a time.
🛑 0NF: The Unnormalized Table (UNF)
Here is our initial table for a University system. It tracks Students, the Courses they take, their
Professors, and their extracurriculars.
Table: University_Records
Stude Student Ph Hob Proj Cour Course D Dept_ Profe
nt_ID _Name one bies ects se_I _Name e Head ssor
s D pt
S101 Alice 123 Che Proj_ C10 Databas C Dr. Prof.
-45 ss, A, e S Smith Alan
6, Art Proj_
789 B
-01
2
S102 Bob 555 Mus Proj_ C10 Databas C Dr. Prof.
-66 ic C e S Smith Alan
6
S101 Alice 123 Che Proj_ C20 Java C Dr. Prof.
-45 ss, A, S Smith John
6, Art Proj_
789 B
-01
2
Why is this bad? (The Violations)
● 1NF Violation: Phones, Hobbies, and Projects have multiple values in a single cell
(comma-separated).
● 2NF Violation: Student_Name depends only on Student_ID, but the primary key for the
academic record requires both Student_ID + Course_ID. This is a partial dependency.
● 3NF Violation: Dept_Head depends on Dept, which depends on Course_ID. This is a
transitive dependency.
● BCNF Violation: Professor dictates the Course_ID (Prof. Alan only teaches Database),
but Professor is not a primary key.
● 4NF Violation: Hobbies and Projects are completely independent of each other, but
storing them together creates a multi-valued dependency nightmare.
✅ Step 1: First Normal Form (1NF)
The Rule: A table must have Atomic Values (no multiple values in a single cell) and a unique
primary key.
The Fix: We expand the comma-separated values into multiple rows. (Notice how this creates
redundant data combinations—this highlights why 4NF will be needed later!).
Table: University_Records_1NF (Showing just a piece of Alice's data for simplicity)
Stude Student_ Ph Ho Pro Cours Course_ D Dept_ Profe
nt_ID Name on bb ject e_ID Name e Head ssor
e y pt
S101 Alice 12 Ch Proj C10 Databas C Dr. Prof.
3-4 ess _A e S Smith Alan
56
S101 Alice 12 Ch Proj C10 Databas C Dr. Prof.
3-4 ess _B e S Smith Alan
56
S101 Alice 12 Art Proj C10 Databas C Dr. Prof.
3-4 _A e S Smith Alan
56
... ... ... ... ... ... ... ... ... ...
●
Status: 1NF is achieved. Every cell has a single value.
✅ Step 2: Second Normal Form (2NF)
The Rule: Must be in 1NF AND have no partial dependencies. (Non-key columns must
depend on the entire primary key, not just a part of it).
● Our logical primary key to identify a specific class record is (Student_ID, Course_ID).
● But Student_Name only depends on Student_ID. Course_Name only depends on
Course_ID.
The Fix: Break the table into separate tables based on what the data strictly belongs to.
1. Student_Table (Depends only on Student_ID)
Student_ID Student_Name Phone Hobby Project
S101 Alice 123-456 Chess Proj_A
2. Course_Table (Depends only on Course_ID)
Course_ID Course_Name Dept Dept_Head
C10 Database CS Dr. Smith
C20 Java CS Dr. Smith
3. Enrollment_Table (Depends on Student_ID + Course_ID)
Student_ID Course_ID Professor
S101 C10 Prof. Alan
S102 C10 Prof. Alan
●
Status: 2NF is achieved. Partial dependencies are removed.
✅ Step 3: Third Normal Form (3NF)
The Rule: Must be in 2NF AND have no transitive dependencies. (Non-key columns cannot
depend on other non-key columns).
● The Problem: Look at the Course_Table. Course_ID determines Dept, and Dept
determines Dept_Head. Therefore, Dept_Head is transitively dependent on Course_ID.
The Fix: Split the Course_Table to remove the middle-man.
1. Course_Details
Course_ID Course_Name Dept
C10 Database CS
C20 Java CS
2. Department_Table
Dept Dept_Head
CS Dr. Smith
(Student_Table and Enrollment_Table remain untouched for now).
● Status: 3NF is achieved.
✅ Step 4: Boyce-Codd Normal Form (BCNF)
The Rule: Must be in 3NF AND for every dependency (A -> B), A must be a Candidate Key (a
unique identifier).
● The Problem: Look at the Enrollment_Table. The candidate keys are (Student_ID,
Course_ID). However, there is a hidden rule: A Professor only teaches one specific
Course. So, Professor -> Course_ID.
● Because Professor is determining Course_ID, but Professor is not a candidate key,
BCNF is violated.
The Fix: Split the Enrollment_Table so the determinant (Professor) becomes a primary key.
1. Professor_Course_Table
Professor Course_ID
Prof. Alan C10
Prof. John C20
2. Student_Professor_Table
Student_ID Professor
S101 Prof. Alan
S102 Prof. Alan
●
Status: BCNF is achieved.
✅ Step 5: Fourth Normal Form (4NF)
The Rule: Must be in BCNF AND have no Multi-Valued Dependencies (MVDs).
● The Problem: Look back at our Student_Table from Step 2. Alice has multiple Phones,
multiple Hobbies, and multiple Projects. These are completely independent of each
other!
● Because they are in the same table, if Alice gets a new phone number, we have to add a
new row for every single hobby and project she has (combinatorial
explosion/redundancy).
The Fix: Separate independent multi-valued attributes into their own tables.
1. Student_Master
Student_ID Student_Name
S101 Alice
2. Student_Phones
Student_ID Phone
S101 123-456
S101 789-012
3. Student_Hobbies
Student_ID Hobby
S101 Chess
S101 Art
4. Student_Projects
Student_ID Project
S101 Proj_A
S101 Proj_B
●
Status: 4NF is achieved! No more independent lists forced into the same table.
🏆 Summary of the Final Normalized Schema
From one messy table, we now have a perfectly clean database structure:
1. Student_Master (Student_ID, Student_Name)
2. Student_Phones (Student_ID, Phone)
3. Student_Hobbies (Student_ID, Hobby)
4. Student_Projects (Student_ID, Project)
5. Course_Details (Course_ID, Course_Name, Dept)
6. Department_Table (Dept, Dept_Head)
7. Professor_Course_Table (Professor, Course_ID)
8. Student_Professor_Table (Student_ID, Professor)
Why did we do this?
● No duplicate data (saves storage).
● No update anomalies (updating a Dept_Head only happens in one place).
● No insert/delete anomalies (we can add a new Department even if no courses belong to
it yet).