Database Normalization

INTRODUCTION

Database normalization is like tidying up your data so everything is neat, organized, and easy to find. It’s a way of designing your database so that there’s no duplicate information, and everything is stored in the right place. This makes your database faster, easier to update, and less likely to run into problems later. Think of it as giving your data a clean, clutter-free home .It involves dividing a large, unstructured table into smaller, well-structured tables and establishing relationships between them using keys.

Step 1: Data Table

Above is 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 person who enrolled.

Step 3: Converting to 1NF

_First Normal Form (1NF) rules:
_
Every column should hold atomic (indivisible) values.
Each record must be unique.

Steps applied:

Divided multivalued attributes into individual rows.
Ensured that each column stores only one value.

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

Step 4: Conversion to 2NF

2NF Rule: Remove partial dependency, ensuring that non-key attributes rely on the entire composite primary key.

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 Students1 (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(100)
);

CREATE TABLE Courses1 (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(100),
Instructor VARCHAR2(100),
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Enrollments1 (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students1(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses1(CourseID)
);

Step 5: Conversion to 3NF

3NF Rule: Remove transitive dependencies (non-prime attributes must depend only on primary key).

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 Courses13NF (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(100),
InstructorID VARCHAR2(10),
FOREIGN KEY (InstructorID) REFERENCES Instructors(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. Kay', '9876543210');
INSERT INTO Instructor VALUES ('I02', 'Dr. Mia', '9123456780');
INSERT INTO Instructor VALUES ('I03', 'Dr. Ray', '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
JOIN Student3NF s ON e.StudentID = s.StudentID
JOIN Course3NF c ON e.CourseID = c.CourseID
JOIN Instructor i ON c.InstructorID = i.InstructorID;

Conclusion:-

Normalization helps achieve reduced data redundancy, improved data integrity simplified maintenance and updates .Following the progression from 1NF → 2NF → 3NF in Oracle SQL enables the creation of efficient designs.