NORMALIZATION

Understanding 1NF, 2NF & 3NF with SQL (A Student–Course Example)

“Why do we even normalize databases? ”
If you’ve ever asked this question, you’re not alone.
Let’s take a fun ride into 1NF → 2NF → 3NF using SQL code and a real-world classroom scenario.

The Story: Students, Courses & Instructors

Imagine you’re designing a system to track which student is taking which course, and who teaches it.
Here’s the initial table we came up with:

StudentID	StudentName	CourseID	CourseName	Instructor	InstructorPhone
S01	Arjun	C101	DBMS	Dr. Kumar	9876543210
S01	Arjun	C102	Data Mining	Dr. Mehta	9123456780
S02	Priya	C101	DBMS	Dr. Kumar	9876543210
S03	Kiran	C103	AI	Dr. Rao	9988776655

Looks fine, right? But…

Insertion anomaly: can’t add a new course unless a student enrolls.
Update anomaly: change Dr. Kumar’s number = update multiple rows.
Deletion anomaly: if Priya drops DBMS, we lose all info about the course!

That’s where normalization comes to the rescue.

Step 1: 1NF – First Normal Form

Rule: Eliminate repeating groups, ensure atomic values.

Our table is already atomic, so in SQL we can define it as:

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

Insert sample data:

INSERT INTO StudentCourse1NF VALUES
('S01', 'Arjun', 'C101', 'DBMS', 'Dr. Kumar', '9876543210'),
('S01', 'Arjun', 'C102', 'Data Mining', 'Dr. Mehta', '9123456780'),
('S02', 'Priya', 'C101', 'DBMS', 'Dr. Kumar', '9876543210'),
('S03', 'Kiran', 'C103', 'AI', 'Dr. Rao', '9988776655');

Data is atomic but anomalies still exist. Let’s move forward.

Step 2: 2NF – Second Normal Form

Rule: Eliminate partial dependency (non-key columns depending only on part of a composite key).

• CourseName, Instructor, and InstructorPhone depend only on CourseID.
• So let’s split them into separate tables.

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)
);

Now we insert data:

-- Students
INSERT INTO Student VALUES
('S01', 'Arjun'),
('S02', 'Priya'),
('S03', 'Kiran');

-- Courses
INSERT INTO Course VALUES
('C101', 'DBMS', 'Dr. Kumar', '9876543210'),
('C102', 'Data Mining', 'Dr. Mehta', '9123456780'),
('C103', 'AI', 'Dr. Rao', '9988776655');

-- Enrollment (who studies what)
INSERT INTO Enrollment VALUES
('S01', 'C101'),
('S01', 'C102'),
('S02', 'C101'),
('S03', 'C103');

Now we don’t repeat course details for every student.
But there’s still a problem…

---

Step 3: 3NF – Third Normal Form

Rule: Eliminate transitive dependencies (non-key columns depending on other non-key columns).

Here, InstructorPhone depends on Instructor, not on CourseID.
So let’s give instructors their own table.

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

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

Insert values:

-- Instructors
INSERT INTO Instructor VALUES
(1, 'Dr. Kumar', '9876543210'),
(2, 'Dr. Mehta', '9123456780'),
(3, 'Dr. Rao', '9988776655');

-- Courses
INSERT INTO Course3NF VALUES
('C101', 'DBMS', 1),
('C102', 'Data Mining', 2),
('C103', 'AI', 3);

Query Time!

Let’s pull out all students with their courses and instructors:

SELECT s.StudentName, c.CourseName, i.InstructorName, i.InstructorPhone
FROM Enrollment e
JOIN Student s ON e.StudentID = s.StudentID
JOIN Course3NF c ON e.CourseID = c.CourseID
JOIN Instructor i ON c.InstructorID = i.InstructorID;

Output :

StudentName	CourseName	InstructorName	InstructorPhone
Arjun	DBMS	Dr. Kumar	9876543210
Arjun	Data Mining	Dr. Mehta	9123456780
Priya	DBMS	Dr. Kumar	9876543210
Kiran	AI	Dr. Rao	9988776655

Key Takeaways

• 1NF: Atomic values, no repeating groups.
• 2NF: No partial dependency.
• 3NF: No transitive dependency.

In real projects, normalized tables reduce redundancy, anomalies, and maintenance pain.

That’s it! You’ve just walked from messy unnormalized data → fully normalized schema with SQL.
Try this in DB Fiddle or SQLite Online and see it in action.