Database Normalization

#programming #learning #sql #database

Database Normalization:

Database normalization is the process of structuring a relational database to reduce redundancy and improve data integrity.

In this blog, we’ll normalize a student-course-instructor dataset from Unnormalized Form → 1NF → 2NF → 3NF, and implement it in SQL.

Step 1: Base Table

The initial unnormalized table includes details of students, their courses, instructors, and corresponding grades.

Step 2: Identifying Anomalies

Insertion anomaly: A new course cannot be added unless it is linked to a student.

Update anomaly: Modifying a course name requires updating it in several rows.

Deletion anomaly: Removing a student may also remove valuable course details if that student was the only enrollee.

1️⃣ First Normal Form (1NF)

Rule: Eliminate repeating groups, ensure atomic values.

So, we split multi-valued attributes into separate rows:

SQL Table in 1 NF,

CREATE TABLE Students_1NF (
Student_ID INT,
Student_Name VARCHAR2(100),
Course_ID INT,
Course_Name VARCHAR2(100),
Instructor VARCHAR2(100),
Grade CHAR(2),
PRIMARY KEY (Student_ID, Course_ID)
);

2️⃣ Second Normal Form (2NF)

Rule: Remove partial dependency → non-key attributes should depend on the whole primary key.

Here, student_id depends on student info, course_id depends on course info, and instructor depends on the course.
So, we split into three tables:

SQL Create Tables (2NF):

CREATE TABLE Students (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(100)
);

CREATE TABLE Courses (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(100),
Instructor VARCHAR2(100),
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Enrollments (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

3️⃣ Third Normal Form (3NF)

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

Here, instructor_phone depends on instructor, not on course_id. So we separate Instructor data:

SQL Create Tables (3NF):

REATE TABLE Instructors (
InstructorID VARCHAR2(10) PRIMARY KEY,
InstructorName VARCHAR2(100),
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Courses3NF (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(100),
InstructorID VARCHAR2(10),
FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);

CREATE TABLE Students3NF (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(100)
);

CREATE TABLE Enrollments3NF (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student3NF(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course3NF(CourseID)
);

Step 6: Insert Sample Data

-- Instructors
INSERT INTO Instructor VALUES ('I01', 'Dr. Kumar', '9876543210');
INSERT INTO Instructor VALUES ('I02', 'Dr. Mehta', '9123456780');
INSERT INTO Instructor VALUES ('I03', 'Dr. Rao', '9988776655');

-- Courses
INSERT INTO Course3NF VALUES ('C101', 'DBMS', 'I01');
INSERT INTO Course3NF VALUES ('C102', 'Data Mining', 'I02');
INSERT INTO Course3NF VALUES ('C103', 'AI', 'I03');

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

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

Step 7: Query with JOINs

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