n this blog, we’ll understand how to normalize a database table step-by-step from 1NF → 2NF → 3NF using clear SQL examples and outputs.
We’ll start from a single unnormalized table containing student, course, and instructor data, and then progressively decompose it to remove redundancy and anomalies.
Identify Anomalies
Insertion Anomaly: We can’t add a new student without assigning a course.
Update Anomaly: If Dr. Kumar changes his phone number, multiple rows must be updated.
Deletion Anomaly: If Arjun drops all courses, his details will be deleted too.
First Normal Form (1NF)
Create a table
Insert data
Output
Second Normal Form (2NF)
Remove partial dependencies.
Create tables separately
Insert values into the respected table
Output
Next
Next
Next
Output
Third Normal Form (3NF)
Remove transitive dependencies — InstructorPhone depends on Instructor, not directly on Course.
In this case, the InstructorPhone depends on Instructor, not directly on Course.
So we already separated Instructors, which makes this 3NF compliant.
Tables in 3NF:
1.Students(StudentID, StudentName)
2.Instructors(Instructor, InstructorPhone)
3.Courses(CourseID, CourseName, Instructor)
4,StudentCourses(StudentID, CourseID)
Query Using JOINs
This structure ensures:
No redundant instructor or course info
Easy updates and clean data relationships
Faster JOIN queries in real databases
We achieved a clean, normalized schema that eliminates anomalies and improves consistency.
Top comments (0)