DATABASE NORMALIZATION IN SQL - 1NF,2NF AND 3NF EXPLAINED (STUDENT - COURSE CASE STUDY

#programming #performance #database #sql

📘 Introduction

Database Normalization is the process of organizing data in a database to reduce redundancy and avoid data anomalies (insertion, update, and deletion problems).

It divides a large, unorganized table into smaller, related tables — ensuring data consistency and integrity.

The most common normal forms are:

1NF (First Normal Form): Remove repeating groups and ensure each cell holds a single value.

2NF (Second Normal Form): Remove partial dependency — every non-key attribute depends on the whole primary key.

3NF (Third Normal Form): Remove transitive dependency — non-key attributes should not depend on other non-key attributes.

Normalization makes your database cleaner, faster, and easier to maintain.

⚠️ Step 1: Identify Anomalies

Insertion Anomaly: Can’t insert a new course unless a student takes it.
Update Anomaly: If an instructor changes name, you must update multiple rows.
Deletion Anomaly: If Alice drops a course, you might lose information about that course’s instructor

✅ Step 2: Convert to 1NF (First Normal Form)

👉 Rule: Remove repeating groups — each column must have atomic (single) values.

The table already satisfies 1NF since all columns have atomic values.
Let’s write the SQL:

✅ Step 3: Convert to 2NF (Second Normal Form)

👉 Rule: Remove partial dependency — every non-key attribute must depend on the entire primary key.

Here, the composite key is (StudentID, CourseID).
But StudentName depends only on StudentID, and CourseName and InstructorName depend only on CourseID.

So we split the table into three:

1️⃣ Students
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50)
);

2️⃣ Courses
CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
InstructorName VARCHAR(50)
);

3️⃣ Enrollments
CREATE TABLE Enrollments (
StudentID INT,
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID)
);

✅ Step 4: Convert to 3NF (Third Normal Form)

👉 Rule: Remove transitive dependency — non-key columns shouldn’t depend on other non-key columns.

In Courses, InstructorName depends on CourseID (which is fine), but if one instructor teaches multiple courses, that’s repetition.
So we create a separate Instructors table.

1️⃣ Instructors
CREATE TABLE Instructors (
InstructorID INT PRIMARY KEY,
InstructorName VARCHAR(50)
);

2️⃣ Update Courses to include a foreign key
CREATE TABLE Courses_3NF (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
InstructorID INT,
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);

3️⃣ Students and Enrollments remain same
CREATE TABLE Students_3NF (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50)
);

CREATE TABLE Enrollments_3NF (
StudentID INT,
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students_3NF(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses_3NF(CourseID)
);

✅ Step 5: Insert Sample Data
INSERT INTO Instructors VALUES
(1, 'Dr. Kumar'),
(2, 'Dr. Meena'),
(3, 'Dr. Ravi');

INSERT INTO Courses_3NF VALUES
('C101', 'DBMS', 1),
('C102', 'OS', 2),
('C103', 'Networks', 3);

INSERT INTO Students_3NF VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO Enrollments_3NF VALUES
(1, 'C101'),
(2, 'C102'),
(1, 'C102'),
(3, 'C103');

✅ Step 6: Query to List All Students with Courses and Instructors
SELECT s.StudentName, c.CourseName, i.InstructorName
FROM Enrollments_3NF e
JOIN Students_3NF s ON e.StudentID = s.StudentID
JOIN Courses_3NF c ON e.CourseID = c.CourseID
JOIN Instructors i ON c.InstructorID = i.InstructorID;