Database Normalization

#programming #sql #oracle #dbms

Database normalization is a crucial process in designing efficient and reliable databases. In this tutorial, I’ll show you how to normalize a sample table from 1NF to 3NF using Oracle Live SQL, with clear explanations and examples

1. The Base Table

We start with an unnormalized table that contains students, courses, instructors, and grades

2. Identifying Anomalies

Before normalization, it’s important to understand the potential anomalies in the table:

Insertion anomaly: You can’t add a new course without adding a student.

Update anomaly: Updating a course name requires changing multiple rows.

Deletion anomaly: Deleting a student might remove important course information if that student was the only one enrolled.

3. Converting to 1NF

1NF (First Normal Form) requires:

Each column must contain atomic (indivisible) values.

Each row must be unique.

Steps taken:

Split multivalued fields into separate rows.

Ensured each column contains a single value.

SQL Table in 1NF:

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

4. Conversion to 2NF

2NF Rule: Eliminate partial dependency (non-prime attributes must depend on the whole 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 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)
);

SQL Create Tables (2NF):

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

SQL Create Tables (3NF):

6: Insert Sample Data

-- Instructors
INSERT INTO Instructor VALUES ('I01', 'Dr. Kumar', '9876543210');
INSERT INTO Instructor VALUES ('I02', 'Dr. Mehta', '9123456780');
INSERT INTO Instructor VALUES ('I03', 'Dr. Rao', '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');

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.CourseID
JOIN Instructor i ON c.InstructorID = i.InstructorID;