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.
