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

#sql #database #oracle #tutorial

Database normalization is one of the most important concepts in DBMS. It helps in reducing redundancy and improving data integrity.
In this post, let’s understand 1NF, 2NF, and 3NF using a simple real-world example — and implement everything using Oracle Live SQL.

🧩The Base Table

We’ll start with this unnormalized table:

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

⚠️ Step 1 — Identify Anomalies

In the base table, we can observe three major problems:

🔸 Insertion Anomaly
You cannot insert a new course unless a student enrolls in it.
🔸 Update Anomaly
If Dr. Kumar changes his phone number, we must update it in multiple rows.
🔸 Deletion Anomaly
If all students drop the “AI” course, we lose Dr. Rao’s details completely.

So, let’s fix these issues one normalization step at a time.

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

Rule:
Each cell must contain only atomic (single) values — no repeating groups.

✅ Our base table already satisfies 1NF, but we’ll create it formally 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 — Convert to 2NF (Second Normal Form)

Rule:
Remove partial dependencies — no non-key attribute should depend on part of a composite key.

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

We separate the data into three smaller tables:

Student
Course
Enrollment

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

Now, data redundancy is reduced — but we can still do better!

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

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

Here, InstructorPhone depends on Instructor, not on CourseID.
So, we’ll create a separate Instructor table and link it to Course via an InstructorID.

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 3NF Schema Diagram

Tables:

Student(StudentID, StudentName)
Instructor(InstructorID, InstructorName, InstructorPhone)
Course(CourseID, CourseName, InstructorID)
Enrollment(EnrollID, StudentID, CourseID) This structure removes redundancy completely and makes the database consistent and scalable.

🧩 Step 6 — Sample Data Insertion
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 — Display Data using JOINs

Now, we can easily query all students, their courses, and their instructors:

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 Thoughts

Through normalization: