In this post, we’ll take a Student–Course–Instructor dataset, identify anomalies, and normalize it step by step from 1NF → 3NF, finally writing SQL queries and testing them in Oracle Live SQL.
🔎 The Raw Table
We start with this table:
⚠️ Anomalies in the Design
- Insertion anomaly – Cannot insert a new course unless a student enrolls.
- Update anomaly – Updating an instructor’s phone requires changing multiple rows.
- Deletion anomaly – If Priya drops DBMS, we lose Dr. Kumar’s info.
✅ Step 1: 1NF
The table is already in 1NF (atomic values).
CREATE TABLE StudentCourse (
StudentID VARCHAR2(10),
StudentName VARCHAR2(50),
CourseID VARCHAR2(10),
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15),
PRIMARY KEY (StudentID, CourseID)
);
✅ Step 2: 2NF
We remove partial dependencies:
- StudentID → StudentName
- CourseID → CourseName, Instructor, InstructorPhone
So we split into 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)
);
✅ Step 3: 3NF
Now we remove transitive dependency: InstructorPhone depends on Instructor, not directly on Course.
So we create a separate Instructor table.
CREATE TABLE Instructor (
InstructorID NUMBER PRIMARY KEY,
InstructorName VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);
CREATE TABLE Course (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(50),
InstructorID NUMBER,
FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);
CREATE TABLE Student (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(50)
);
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)
);
✅ Insert Sample Data
-- Instructors (manual IDs)
INSERT INTO Instructor VALUES (1, 'Dr. Kumar', '9876543210');
INSERT INTO Instructor VALUES (2, 'Dr. Mehta', '9123456780');
INSERT INTO Instructor VALUES (3, 'Dr. Rao', '9988776655');
-- Courses
INSERT INTO Course VALUES ('C101', 'DBMS', 1);
INSERT INTO Course VALUES ('C102', 'Data Mining', 2);
INSERT INTO Course VALUES ('C103', 'AI', 3);
-- Students
INSERT INTO Student VALUES ('S01', 'Arjun');
INSERT INTO Student VALUES ('S02', 'Priya');
INSERT INTO Student VALUES ('S03', 'Kiran');
-- Enrollments
INSERT INTO Enrollment VALUES ('S01', 'C101');
INSERT INTO Enrollment VALUES ('S01', 'C102');
INSERT INTO Enrollment VALUES ('S02', 'C101');
INSERT INTO Enrollment VALUES ('S03', 'C103');
✅ Final Query with JOINs
To list all students with their courses and instructors:
SELECT s.StudentName, c.CourseName, i.InstructorName
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;
🎯 Conclusion
- We started with an unnormalized table.
- Identified anomalies.
- Converted step by step into 1NF → 2NF → 3NF.
- Inserted clean sample data.
- Wrote a JOIN query to fetch students, courses, and instructors.
This makes the database cleaner, consistent, and flexible ✅.
Top comments (0)