Database Normalization in Oracle SQL — From 1NF to 3NF with Example

Normalization is a process that helps to remove redundancy, avoid anomalies, and improve data consistency in a relational database.
Step 1: Understanding Data Anomalies

Before normalization, let’s look at an unnormalized table:

Anomalies Present

Insertion Anomaly: Can’t insert a new course unless a student takes it.

Update Anomaly: Changing an instructor’s phone requires multiple updates.

Deletion Anomaly: Deleting a student may also delete course information.

Step 2: Conversion to 1NF

In 1NF, each field holds only one value — our table already follows this form.

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

Step 3: Conversion to 2NF

In 2NF, we remove partial dependencies.
We separate students and courses into different tables.

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

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

CREATE TABLE StudentCourses (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
Step 4: Conversion to 3NF

In 3NF, we remove transitive dependencies — instructor details are separated into their own table.

CREATE TABLE Instructors (
InstructorID INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
InstructorName VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Courses (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(50),
InstructorID INT,
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);

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

CREATE TABLE StudentCourses (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
Step 5: Inserting Sample Data
-- Insert Students
INSERT INTO Students VALUES ('S01', 'Arjun');
INSERT INTO Students VALUES ('S02', 'Priya');
INSERT INTO Students VALUES ('S03', 'Kiran');

-- Insert Instructors
INSERT INTO Instructors (InstructorName, InstructorPhone)
VALUES ('Dr.Kumar', '9876543210');
INSERT INTO Instructors (InstructorName, InstructorPhone)
VALUES ('Dr.Mehta', '9123456780');
INSERT INTO Instructors (InstructorName, InstructorPhone)
VALUES ('Dr.Rao', '9988774455');

-- Insert Courses
INSERT INTO Courses (CourseID, CourseName, InstructorID)
VALUES ('C101', 'DBMS', 1);
INSERT INTO Courses (CourseID, CourseName, InstructorID)
VALUES ('C102', 'Data Mining', 2);
INSERT INTO Courses (CourseID, CourseName, InstructorID)
VALUES ('C103', 'AI', 3);

-- Insert Mapping
INSERT INTO StudentCourses VALUES ('S01', 'C101');
INSERT INTO StudentCourses VALUES ('S01', 'C102');
INSERT INTO StudentCourses VALUES ('S02', 'C103');

Step 6: Joining Tables to Display Final Output

Let’s join all tables to display Student Name, Course, and Instructor:

SELECT
s.StudentName,
c.CourseName,
i.InstructorName
FROM
Students s
JOIN
StudentCourses sc ON s.StudentID = sc.StudentID
JOIN
Courses c ON sc.CourseID = c.CourseID
JOIN
Instructors i ON c.InstructorID = i.InstructorID;