This content originally appeared on DEV Community and was authored by Jai Surya
Introduction to database Normalization:
Database normalization is a systematic process of organizing data in a database to reduce redundancy and improve data integrity.
It involves dividing large, complex tables into smaller, related ones to eliminate anomalies and ensure consistent, efficient data storage.
step 1 : Base Table
The initial unnormalized table includes details of students, their courses, instructors, and corresponding grades.
step 2 : Identifying anomalies
Insertion Anomaly :Can’t add a new course unless a student is enrolled in it.
Update Anomaly :If Prof. Rao changes department, we must update multiple rows.
Deletion Anomaly :If Alice drops “OS,” we lose data about that course/instructor
step 3 : Converting to 1NF
1NF Rule:
Each cell should contain atomic values (no repeating groups).
Each record must be unique.
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 : Converting to 2NF
2NF Rule:
The table must already be in 1NF.
Remove partial dependencies — every non-key attribute must depend on the whole primary key, not just part of it.
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)
);
Step 5 : converting to 3NF
Rule:
The table must already be in 2NF.
Remove transitive dependencies — non-key attributes must not depend on other non-key attributes.
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)
);
step 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');
step 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;
Conclusion :
By following these normalization steps — 1NF → 2NF → 3NF — we’ve:
- Eliminated redundancy
- Prevented anomalies
- Improved data consistency
- Made queries easier and more efficient
Special thanks to @santhoshnc for mentoring me on database normalization concepts!
SQL #Oracle #DBMS #DatabaseNormalization #1NF #2NF #3NF #BCNF #4NF #5NF #DataModeling
This content originally appeared on DEV Community and was authored by Jai Surya
Jai Surya | Sciencx (2025-10-07T12:30:31+00:00) DATABASE NORMALIZATION. Retrieved from https://www.scien.cx/2025/10/07/database-normalization-3/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.