Jai Surya

Posted on Oct 7

DATABASE NORMALIZATION

#database #sql #softwaredevelopment #github

Introduction to database Normalization:

Database normalization is a systematic process of organizing data in a database to reduce redundancy and improve data integrity.
It involves dividing large, complex tables into smaller, related ones to eliminate anomalies and ensure consistent, efficient data storage.

step 1 : Base Table

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

step 2 : Identifying anomalies

Insertion Anomaly :Can’t add a new course unless a student is enrolled in it.

Update Anomaly :If Prof. Rao changes department, we must update multiple rows.

Deletion Anomaly :If Alice drops “OS,” we lose data about that course/instructor

step 3 : Converting to 1NF

1NF Rule:

Each cell should contain atomic values (no repeating groups).

Each record must be unique.

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

Step 4 : Converting to 2NF

2NF Rule:

The table must already be in 1NF.

Remove partial dependencies — every non-key attribute must depend on the whole primary key, not just part of it.

Observations:

Student_Name depends only on Student_ID.

Course_Name, Instructor, Instructor_Phone depend only on Course_ID.

Grade (if existed) would depend on both Student_ID + Course_ID.

Solution: Split into three tables:

Students → Student_ID, Student_Name

Courses → Course_ID, Course_Name, Instructor, Instructor_Phone

Enrollments → Student_ID, Course_ID

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

Step 5 : converting to 3NF

Rule:

The table must already be in 2NF.

Remove transitive dependencies — non-key attributes must not depend on other non-key attributes.

instructor’s phone depends on Instructor, not Course_ID → we can split Courses into:

Courses → Course_ID, Course_Name, Instructor_ID

Instructors → Instructor_ID, Instructor_Name, Instructor_Phone

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