Understanding Database Normalization (1NF, 2NF, 3NF) with SQL Examples

In this post, we will learn about 1NF, 2NF, and 3NF with SQL examples. We’ll start with a denormalized base table and gradually normalize it step by step.

Bae Table:

Anomalies in Base Table
Insertion Anomaly: Can’t add a new course until a student registers.
Update Anomaly: If Prof. Smith changes name → must update multiple rows.
Deletion Anomaly: If Alice drops all courses → course info is lost.

First Normal Form (1NF)
Rule: Remove repeating groups and ensure atomic values.
In our base table, values are already atomic, so 1NF just looks like this:
CREATE TABLE StudentCourse (
StudentID VARCHAR(10),
StudentName VARCHAR(50),
CourseID VARCHAR(10),
CourseName VARCHAR(50),
InstructorName VARCHAR(50),
PRIMARY KEY (StudentID, CourseID)
);

Second Normal Form (2NF)
Rule: Eliminate partial dependency (non-key attributes should depend on the whole primary key).
Here, StudentName depends only on StudentID.
CourseName and InstructorName depend only on CourseID.
So, we split into three tables:
-- Students Table
CREATE TABLE Students (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

-- Courses Table
CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
InstructorName VARCHAR(50)
);

-- Relationship Table
CREATE TABLE StudentCourse (
StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Third Normal Form (3NF)
👉 Rule: Remove transitive dependency (non-key attribute depending on another non-key).
InstructorName depends on CourseID, but instructor details should be separate.
So, we restructure:
-- Students Table
CREATE TABLE Students (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

-- Instructors Table
CREATE TABLE Instructors (
InstructorID VARCHAR(10) PRIMARY KEY,
InstructorName VARCHAR(50)
);

-- Courses Table
CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
InstructorID VARCHAR(10),
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);

-- Relationship Table
CREATE TABLE StudentCourse (
StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
-- Insert Students
INSERT INTO Students VALUES ('S1', 'Alice');
INSERT INTO Students VALUES ('S2', 'Bob');

-- Insert Instructors
INSERT INTO Instructors VALUES ('I1', 'Prof. Smith');
INSERT INTO Instructors VALUES ('I2', 'Prof. John');
INSERT INTO Instructors VALUES ('I3', 'Prof. Mary');

-- Insert Courses
INSERT INTO Courses VALUES ('C101', 'DBMS', 'I1');
INSERT INTO Courses VALUES ('C102', 'Networks', 'I2');
INSERT INTO Courses VALUES ('C103', 'AI', 'I3');

-- Student-Course Mapping
INSERT INTO StudentCourse VALUES ('S1', 'C101');
INSERT INTO StudentCourse VALUES ('S2', 'C102');
INSERT INTO StudentCourse VALUES ('S1', 'C103');

Query with JOINs
Now, let’s list all students with their courses and instructors:

SELECT s.StudentName, c.CourseName, i.InstructorName
FROM Students s
JOIN StudentCourse sc ON s.StudentID = sc.StudentID
JOIN Courses c ON sc.CourseID = c.CourseID
JOIN Instructors i ON c.InstructorID = i.InstructorID;

THANK YOU @santhoshnc sir for guiding and supporting me!!