Normalization

Database Normalization in SQL – From 1NF to 3NF
Introduction:

In this blog, we will explore Database Normalization with a practical SQL example. We’ll start with an unnormalized student-course table, identify anomalies, and then normalize the table step by step into 1NF, 2NF, and 3NF. Finally, we’ll use SQL JOIN queries to retrieve meaningful information.

Step 1: Data Anomalies in Unnormalized Tables

When data is not properly organized, we face:

Insertion Anomaly – Can’t add a course without assigning it to a student.

Update Anomaly – Updating instructor details in multiple rows creates redundancy.

Deletion Anomaly – Deleting a student might accidentally remove course information too.

Step 2: First Normal Form (1NF)

In 1NF, data must be atomic, with no repeating groups.

CREATE TABLE StudentCourse_1NF (
StudentName VARCHAR(50),
CourseID VARCHAR(10),
CourseName VARCHAR(50),
Instructor VARCHAR(50),
InstructorPhone VARCHAR(15)
);

Step 3: Second Normal Form (2NF)

In 2NF, we remove partial dependencies. We separate students and courses.

Tables created:

CREATE TABLE Course_2NF (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
Instructor VARCHAR(50),
InstructorPhone VARCHAR(15)
);

CREATE TABLE StudentCourse_2NF (
StudentName VARCHAR(50),
CourseID VARCHAR(10),
FOREIGN KEY (CourseID) REFERENCES Course_2NF(CourseID)
);

Step 4: Third Normal Form (3NF)

In 3NF, we remove transitive dependencies. Instructor details are moved into a new table.

Tables created:

CREATE TABLE Instructor_3NF (
Instructor VARCHAR(50) PRIMARY KEY,
InstructorPhone VARCHAR(15)
);

CREATE TABLE Course_3NF (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
Instructor VARCHAR(50),
FOREIGN KEY (Instructor) REFERENCES Instructor_3NF(Instructor)
);

CREATE TABLE StudentCourse_3NF (
StudentName VARCHAR(50),
CourseID VARCHAR(10),
FOREIGN KEY (CourseID) REFERENCES Course_3NF(CourseID)
);

Step 5: Inserting Data

We then inserted sample data into the normalized tables using INSERT INTO.

Step 6: JOIN Query

Finally, we retrieved the data using JOINs to display students with their courses and instructors:

SELECT s.StudentName, c.CourseName, i.Instructor
FROM StudentCourse_3NF s
JOIN Course_3NF c ON s.CourseID = c.CourseID
JOIN Instructor_3NF i ON c.Instructor = i.Instructor;

Conclusion

We successfully normalized a student-course table from 1NF → 2NF → 3NF. Along the way, we eliminated redundancy, avoided data anomalies, and ensured data integrity.

With proper JOINs, we could easily retrieve clean and consistent information from the database.

Normalization ensures our databases are efficient, reliable, and scalable.