🚀 Understanding Database Normalization : 1F,2F,3F With SQL Examples !!!

📌 Introduction:

When working with databases, it’s common to face problems like data redundancy, inconsistencies, and loss of information. This happens when data is not structured properly.

That’s where Database Normalization comes in. It’s a process of organizing data in relational databases to minimize redundancy and improve integrity.

In this blog, we’ll walk through 1NF, 2NF, and 3NF, step by step, with SQL examples.

🏁 Process 1: Base Table

we start with the following table:

🔎 Process 2: Anomalies in Base Table

1.Insertion anomaly – Can’t add a new course unless a student enrolls.
2.Update anomaly – If an instructor’s name changes, we must update multiple rows.
3.Deletion anomaly – If the last student in a course leaves, info about that course is lost.

🧱 Process 3: First Normal Form (1NF)

POINT: Remove repeating groups, ensure atomic values.

CREATE TABLE StudentCourse (
StudentID INT,
StudentName VARCHAR(50),
CourseID VARCHAR(10),
CourseName VARCHAR(50),
InstructorName VARCHAR(50),
PRIMARY KEY (StudentID, CourseID)
);


This ensures no multi-valued attributes.

🧱 Process 4: Second Normal Form (2NF)

POINT: Remove partial dependencies (non-key attributes depending on part of the key).

`CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50)
);

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

CREATE TABLE StudentCourse (
StudentID INT,
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);`

🧱 Process 5: Third Normal Form (3NF)

POINT: Remove transitive dependencies (non-key attributes depending on other non-keys).

`CREATE TABLE Instructors (
InstructorID INT PRIMARY KEY,
InstructorName VARCHAR(50)
);

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

💾 Process 6: Insert Sample Data

`INSERT INTO Students VALUES (1, 'John'), (2, 'Alice'), (3, 'Bob');

INSERT INTO Instructors VALUES (101, 'Dr. Smith'), (102, 'Dr. Miller'), (103, 'Dr. Brown');

INSERT INTO Courses VALUES ('C101', 'DBMS', 101),
('C102', 'OS', 102),
('C103', 'Networks', 103);

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

🔗 Process 7: Query with JOIN

Now let’s get a list of all students along with their courses and instructors:
SELECT s.StudentName, c.CourseName, i.InstructorName
FROM StudentCourse sc
JOIN Students s ON sc.StudentID = s.StudentID
JOIN Courses c ON sc.CourseID = c.CourseID
JOIN Instructors i ON c.InstructorID = i.InstructorID;

🎯 Conclusion:

Through normalization, we transformed our messy base table into well-structured, relational tables.

1NF removed repeating groups.
2NF eliminated partial dependencies.
3NF removed transitive dependencies.
Now our database is clean, scalable, and reliable.

*Thanks @santhoshnc sir for guiding us and encouraging us!!!!
*