Understanding 1NF, 2NF, and 3NF Using SQL — Step-by-Step Implementation

#sql #database #tutorial

Database normalization is a process used to organize data in a database efficiently.It helps to remove redundancy, improve data integrity, and make data maintenance easier.
This post walks through the process of converting a database into 1NF, 2NF, and 3NF, showing each stage with practical SQL CREATE TABLE and INSERT examples.

Base Table

Let’s start with the following unnormalized Student Enrollment table:

This table stores student details, courses, and instructor information together.

Data Anomalies

This unnormalized table can cause three types of anomalies:

Insertion anomaly:
Cannot add a new course unless a student is enrolled in it.

Update anomaly:
If an instructor’s phone number changes, it must be updated in multiple rows.

Deletion anomaly:
Deleting a student record might also remove information about a course or instructor.

To overcome these issues, we will normalize the table step by step.

Step 1: First Normal Form (1NF)

Each column should contain atomic (single) values.
No repeating groups or arrays are allowed.

CREATE TABLE Student_1NF ( StudentID INT, StudentName VARCHAR(50), Course VARCHAR(50), Instructor VARCHAR(50), InstructorPhone VARCHAR(15) );

Step 2: Second Normal Form (2NF)

The table must be in 1NF.
All non-key attributes must depend on the entire primary key, not just a part of it.

CREATE TABLE Students ( StudentID VARCHAR(10) PRIMARY KEY, StudentName VARCHAR(100) NOT NULL );

CREATE TABLE Courses_2NF ( CourseID VARCHAR(10) PRIMARY KEY, CourseName VARCHAR(100) NOT NULL, Instructor VARCHAR(100) NOT NULL, InstructorPhone VARCHAR(15) NOT NULL );

CREATE TABLE Enrollments_2NF ( StudentID VARCHAR(10) NOT NULL, CourseID VARCHAR(10) NOT NULL, PRIMARY KEY (StudentID, CourseID), FOREIGN KEY (StudentID) REFERENCES Students(StudentID), FOREIGN KEY (CourseID) REFERENCES Courses_2NF(CourseID) );

INSERT INTO Students (StudentID, StudentName) VALUES ('S01','Arjun'), ('S02','Priya'), ('S03','Kiran');

INSERT INTO Courses_2NF (CourseID, CourseName, Instructor, InstructorPhone) VALUES ('C101','DBMS','Dr. Kumar','9876543210'), ('C102','Data Mining','Dr. Mehta','9123456780'), ('C103','AI','Dr. Rao','9988776655');

INSERT INTO Enrollments_2NF (StudentID, CourseID) VALUES ('S01','C101'),('S01','C102'),('S02','C101'),('S03','C103');

Step 3: Third Normal Form (3NF)

The table must be in 2NF.
There should be no transitive dependency (non-key attributes should not depend on other non-key attributes).

Here, InstructorPhone depends on Instructor, not on the key — so we separate instructor details into a new table.

CREATE TABLE StudentDetails ( StudentID VARCHAR(10) PRIMARY KEY, StudentName VARCHAR(100) NOT NULL ) ENGINE=InnoDB;

CREATE TABLE InstructorDetails ( InstructorID VARCHAR(10) PRIMARY KEY, InstructorName VARCHAR(100) NOT NULL, InstructorPhone VARCHAR(15) NOT NULL ) ENGINE=InnoDB;

CREATE TABLE CourseDetails ( CourseID VARCHAR(10) PRIMARY KEY, CourseName VARCHAR(100) NOT NULL, InstructorID VARCHAR(10) NOT NULL, FOREIGN KEY (InstructorID) REFERENCES InstructorDetails(InstructorID) ) ENGINE=InnoDB;

CREATE TABLE StudentCourseEnrollments ( StudentID VARCHAR(10) NOT NULL, CourseID VARCHAR(10) NOT NULL, PRIMARY KEY (StudentID, CourseID), FOREIGN KEY (StudentID) REFERENCES StudentDetails(StudentID), FOREIGN KEY (CourseID) REFERENCES CourseDetails(CourseID) ) ENGINE=InnoDB;

INSERT INTO StudentDetails (StudentID, StudentName) VALUES ('S11','Rohan'), ('S12','Meera'), ('S13','Vikram'), ('S14','Sneha');

INSERT INTO InstructorDetails (InstructorID, InstructorName, InstructorPhone) VALUES ('I11','Dr. Sharma','9871112233'), ('I12','Dr. Nair','9122223344'), ('I13','Dr. Kapoor','9988771122'), ('I14','Dr. Joshi','9899988776');

INSERT INTO CourseDetails (CourseID, CourseName, InstructorID) VALUES ('C201','Operating Systems','I11'), ('C202','Computer Networks','I12'), ('C203','Machine Learning','I13'), ('C204','Cloud Computing','I14');

INSERT INTO StudentCourseEnrollments (StudentID, CourseID) VALUES ('S11','C201'), ('S11','C202'), ('S12','C201'), ('S13','C203'), ('S14','C204');