Normalization is one of the most important concepts in database design. In this blog, we’ll walk through normalizing a student-course-instructor dataset step by step — from 1NF to 3NF — using SQL CREATE TABLE and INSERT INTO statements.
Identify Anomalies
Insertion anomaly:
Can’t insert a new course unless a student enrolls.
Update anomaly:
If Dr. Kumar’s phone number changes, we must update multiple rows.
Deletion anomaly:
If student S02 drops the course, we lose info about the DBMS course entirely.
Convert to 1NF (First Normal Form)
Remove repeating groups, ensure atomic values.
Convert to 2NF (Second Normal Form)
Remove partial dependency (non-key attributes should depend on the whole key).
Create separate tables for Students, Courses, Instructors, Studentcourses.
Create table for Students
Create table for Courses
Insertion of values
Output
Create table for Instructors
Insertion of values
Output
Create table for Studentcourses
Insertion of values
Output
Convert to 3NF (Third Normal Form)
Remove transitive dependency (non-key attributes shouldn’t depend on other non-keys).
👉 In our case, the InstructorPhone depends on Instructor, not directly on Course.
So we already separated Instructors, which makes this 3NF compliant.
Final tables in 3NF:
- Students(StudentID, StudentName)
- Instructors(Instructor, InstructorPhone)
- Courses(CourseID, CourseName, Instructor)
- StudentCourses(StudentID, CourseID)
Query Using JOINs
Simple Breakdown
By breaking down our initial table step by step:
- 1NF → Atomic values
- 2NF → Remove partial dependencies
- 3NF → Remove transitive dependencies
We achieved a clean, normalized schema that eliminates anomalies and improves consistency.
Top comments (0)