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: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 thewhole 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 dependenciesnon-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 ('101', 'Dr.

Kumar', '9876543210');

INSERT INTO Instructor VALUES ('102', 'Dr.

Mehta', '9123456780');

INSERT INTO Instructor VALUES ('103', 'Dr.

Rao', '9988776655');

Courses

INSERT INTO Course3NF VALUES ('C101', 'DBMS', '101');

INSERT INTO Course3NF VALUES ('C102',

'Data Mining', '102');

INSERT INTO Course3NF VALUES ('C103', 'ΑΙ', '103');

Students

INSERT INTO Student3NF VALUES ('S01', 'Arjun');

INSERT INTO Student3NF VALUES ('S02',

'Priva'):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.CourselD

JOIN Instructor i ON c.InstructorID = i.InstructorID;** Wrap Up**

We started with an unnormalized table and step-by-step applied:

1NF ← Removed repeating groups

2NF ← Removed partial dependencies

3NF ← Removed transitive dependencies

Result

A clean, normalized database3NF

← Removed transitive dependencies

← Result A clean, normalized database with reduced redundancy, better integrity, and easier queries

Special thanks to @santhoshnc for mentoring me on database normalization concepts!

SQL #Oracle

DBMS

DatabaseNormalization

1NF

2NF

3NF

BCNF

4NF

5NF

DataModeling