DEV Community

Cover image for Database Normalization: From 1NF to 3NF
DEEPAK KUMAR B 24CB007
DEEPAK KUMAR B 24CB007

Posted on

Database Normalization: From 1NF to 3NF

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


Output

Next


Output

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)