Understanding Database Normalization (1NF 2NF 3NF) with Oracle SQL — Step-by-Step Example

Database normalization is a crucial concept in DBMS that ensures data integrity and eliminates redundancy.
In this article, I’ve explained 1NF, 2NF, and 3NF through a real-world example and demonstrated each step using Oracle Live SQL.

🧩 The Original Data Structure
We’ll begin with this unnormalized dataset, which contains redundant information:

StudentID StudentName CourseID CourseName Instructor InstructorPhone
S01 Abi C101 DBMS Dr. Saran 9876543210
S01 Abi C102 Data Mining Dr. Mehta 9123456780
S02 Priya C101 DBMS Dr. Kumar 9876543210
S03 Kiran C103 AI Dr. Rao 9988776655

⚠️ Step 1 — Detecting Data Issues
In the above table, we can identify three common anomalies:

🔸 Insertion anomaly – Can’t add a new course unless a student enrolls in it.
🔸 Update anomaly – If Dr. Kumar changes his phone number, it must be modified in multiple rows.
🔸 Deletion anomaly – If all students drop the “AI” course, we lose Dr. Rao’s details completely.

We’ll now fix these issues using normalization, step by step.

🧱 Step 2 — Transform to 1NF (First Normal Form)

Rule: Every field should hold only atomic (single) values, and repeating groups must be removed.

✅ Our data already meets 1NF requirements, but let’s formally create it in SQL:

CREATE TABLE BaseTable (
StudentID VARCHAR2(10),
StudentName VARCHAR2(50),
CourseID VARCHAR2(10),
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);

🧩 Step 3 — Move to 2NF (Second Normal Form)

Rule: Eliminate partial dependencies — every non-key attribute must depend on the entire composite key.

Here, the composite key is (StudentID, CourseID).

We’ll divide the structure into Student, Course, and Enrollment tables:

CREATE TABLE Student (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(50)
);

CREATE TABLE Course (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);

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

🧠 Step 4 — Upgrade to 3NF (Third Normal Form)

Rule: Remove transitive dependencies — non-key attributes should not depend on other non-key attributes.

Since InstructorPhone depends on Instructor, not CourseID, we’ll introduce a separate Instructor table:

CREATE TABLE Instructor (
InstructorID VARCHAR2(10) PRIMARY KEY,
InstructorName VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);

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

🧾 Step 5 — Final Normalized Structure
Tables Overview:

Student(StudentID, StudentName)

Instructor(InstructorID, InstructorName, InstructorPhone)

Course(CourseID, CourseName, InstructorID)

Enrollment(EnrollID, StudentID, CourseID)

This model completely removes redundancy and ensures a consistent, scalable database structure.

🧩 Step 6 — Insert Sample Records

INSERT INTO Student VALUES ('S01', 'Arjun');
INSERT INTO Student VALUES ('S02', 'Priya');
INSERT INTO Student VALUES ('S03', 'Kiran');

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

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

INSERT INTO Enrollment (StudentID, CourseID) VALUES ('S01', 'C101');
INSERT INTO Enrollment (StudentID, CourseID) VALUES ('S01', 'C102');
INSERT INTO Enrollment (StudentID, CourseID) VALUES ('S02', 'C101');
INSERT INTO Enrollment (StudentID, CourseID) VALUES ('S03', 'C103');

🔍 Step 7 — Retrieve Data Using JOINs

SELECT
s.StudentID,
s.StudentName,
c.CourseName,
i.InstructorName,
i.InstructorPhone
FROM Enrollment e
JOIN Student s ON e.StudentID = s.StudentID
JOIN Course c ON e.CourseID = c.CourseID
JOIN Instructor i ON c.InstructorID = i.InstructorID;

Output:
StudentID StudentName CourseName InstructorName InstructorPhone
S01 Arjun DBMS Dr. Kumar 9876543210
S01 Arjun Data Mining Dr. Mehta 9123456780
S02 Priya DBMS Dr. Kumar 9876543210
S03 Kiran AI Dr. Rao 9988776655

✅ Final Insights

Through normalization:

Data duplication is minimized
Insertion, update, and deletion anomalies are resolved
The database becomes more structured, consistent, and efficient
By applying these steps in Oracle Live SQL, you can clearly visualize how normalization enhances your database design.

A special thanks to @santhoshnc sir for guiding me throughout these normalization concepts.