Normalization in DBMS with SQL Examples (1NF 2NF 3NF)

In this blog, I am sharing my learnings about Normalization in DBMS.
As part of my coursework, I implemented 1NF, 2NF, and 3NF using SQL.
This blog will explain step by step with tables and queries.

Base Table

Anomalies

Insertion Anomaly: Cannot add a course without student.

Update Anomaly: Instructor’s phone number must be updated in many rows.

Deletion Anomaly: If last student leaves, course info also deleted.

step 1: 1NF (First Normal Form)
👉 Condition: No repeating groups / multivalued attributes.
Already table is in 1NF (since all fields atomic).

SQL (2NF Tables)
CREATE TABLE StudentCourse (
StudentID VARCHAR(10),
StudentName VARCHAR(50),
CourseID VARCHAR(10),
CourseName VARCHAR(50),
Instructor VARCHAR(50),
InstructorPhone VARCHAR(15),
PRIMARY KEY (StudentID, CourseID)
);

Step 2: 2NF (Second Normal Form)

👉 Condition: No partial dependency (non-key attribute should depend on full primary key).
Here StudentName depends only on StudentID,
and CourseName, Instructor, InstructorPhone depend only on CourseID.

So we split:

Student table

Course table

Enrollment (Student-Course relation) table
CREATE TABLE Student (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

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

CREATE TABLE Enrollment (
StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Step 3: 3NF (Third Normal Form)

👉 Condition: No transitive dependency.
Here InstructorPhone depends on Instructor, not on CourseID.

So we separate Instructor into another table.

SQL (3NF Tables)
CREATE TABLE Student (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

CREATE TABLE Instructor (
InstructorID VARCHAR(10) PRIMARY KEY,
InstructorName VARCHAR(50),
InstructorPhone VARCHAR(15)
);

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

CREATE TABLE Enrollment (
StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

step 4:Sample Data
INSERT INTO Student VALUES ('S01','Arjun'), ('S02','Priya'), ('S03','Kiran');
INSERT INTO Instructor VALUES ('I01','Dr. Kumar','9876543210'), ('I02','Dr. Mehta','9123456780'), ('I03','Dr. Rao','9988776655');
INSERT INTO Course VALUES ('C101','DBMS','I01'), ('C102','Data Mining','I02'), ('C103','AI','I03');
INSERT INTO Enrollment VALUES ('S01','C101'), ('S01','C102'), ('S02','C101'), ('S03','C103');

step 5:Query with JOIN
SELECT s.StudentName, c.CourseName, i.InstructorName
FROM Enrollment e
JOIN Student s ON e.StudentID = s.StudentID
JOIN Course c ON e.CourseID = c.CourseID
JOIN Instructor i ON c.InstructorID = i.InstructorID;

Conclusion
Through Normalization, we reduced redundancy and avoided anomalies.
1NF ensured atomicity, 2NF removed partial dependency, and 3NF removed transitive dependency.
This is how databases remain consistent and efficient.