*Introduction
*
In database design, normalization is the process of organizing data to minimize redundancy and improve integrity. In this post, we’ll normalize a sample student-course-instructor table step by step — from unnormalized form to 3NF. Each stage will include explanations, SQL code, and screenshots.
1️⃣ Identify Anomalies
Our raw table has redundancy and anomalies. For example, the same instructor’s phone number is stored multiple times, and deleting a student may also delete important course information. Let’s list the anomalies:
Insertion anomaly
Update anomaly
Deletion anomaly
2️⃣ Convert to 1NF
In 1NF, we make sure that data is atomic and there are no repeating groups. Our original table already satisfies atomicity, so we just define it in SQL.
3️⃣ Convert to 2NF
In 2NF, we remove partial dependencies. Attributes must depend on the whole primary key.
StudentName depends only on StudentID
CourseName and Instructor depend only on CourseID
So, we split the table into Students, Courses, and Enrollment.
4️⃣ Convert to 3NF
In 3NF, we remove transitive dependencies. For example, InstructorPhone depends on Instructor, not directly on Course.
Thus, we create a separate Instructors table and link it with Courses.
5️⃣ Insert Sample Data
Now, we add sample data into our normalized tables. We insert instructors first, then students, then courses, and finally enrollment records (to respect foreign key constraints).
6️⃣ Query with JOINs
Finally, we can query across all the normalized tables using JOIN. This lets us fetch a clean dataset of students, their courses, and instructors.
Thank you @santhoshnc sir for guiding me
Top comments (0)