Normalizing a Student-Course schema step-by-step and validating with JOIN outputs
Introduction
This post shows how a simple Student–Course dataset was modeled in Oracle Live SQL and normalized from 1NF → 2NF → 3NF, ending with a clean junction table and a working JOIN query result. The screenshots are added to illustrate each milestone and the final output table. Special thanks to Santhosh sir for assigning and guiding this exercise.
What problem I solved
Started with a single wide table mixing student, course, and instructor details, which risks redundancy and update anomalies.
Applied normalization to split data into well-structured tables and enforce referential integrity.
Verified with INSERT and SELECT … JOIN to ensure the design works end-to-end.
1NF: Single wide table
Created an initial table StudentCourse1NF with columns: StudentID, StudentName, CourseID, CourseName, Instructor, InstructorPhone.
Goal at this stage: ensure atomic values in each cell and a primary identifier present.
2NF: Remove partial dependencies
Identified that non-key attributes like StudentName depend on StudentID, while CourseName/Instructor details depend on CourseID, causing partial dependency on a composite key.
Split into base entity tables: Students(StudentID, StudentName) and Courses(CourseID, CourseName, Instructor, InstructorPhone).
Created a pure relationship table StudentCourse1NF_New or StudentCourses with keys only (StudentID, CourseID).
3NF: Remove transitive dependencies
Ensured instructor details belong with Courses, not with Students–Courses mapping, avoiding transitive dependencies via CourseID.
Declared composite primary key on StudentCourses(StudentID, CourseID) and foreign keys referencing Students and Courses.
This guarantees each fact lives in exactly one place, preventing update anomalies.
DDL snippets used
Students
CREATE TABLE Students (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(50)
);
Courses
CREATE TABLE Courses (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);
StudentCourses (junction)
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)
);
Sample data
INSERT INTO Students VALUES ('S01','Kiran');
INSERT INTO Students VALUES ('S02','Arjun');
INSERT INTO Students VALUES ('S03','Priya');
INSERT INTO Courses VALUES ('C101','DBMS',1); // or include instructor fields per your design
INSERT INTO StudentCourses VALUES ('S01','C101');
Continue adding rows to cover multiple students and courses.
*Validation query
*
SELECT s.StudentName, c.CourseName, i.InstructorName
FROM StudentCourses sc
JOIN Students s ON sc.StudentID = s.StudentID
JOIN Courses c ON sc.CourseID = c.CourseID
JOIN Instructors i ON c.InstructorID = i.InstructorID; // if you separated Instructors
What the screenshots show
1NF image: The initial table creation for denormalized structure.
2NF image: Refactoring tables and creating entity tables.
3NF image: Final DDL with keys and foreign keys in Oracle Live SQL.
Final output image: Query result proving the relationships and data integrity.
Key takeaways
Use a junction table for many-to-many Student–Course relationships.
Keep attributes with the entity they truly describe to avoid redundancy.
Composite PK on (StudentID, CourseID) plus FKs gives strong integrity.
Always validate by inserting data and running JOINs to see real results.
A sincere thanks to @santhoshnc sir for giving this assignment and motivating the step-by-step approach. Your guidance made normalization concepts much clearer in practice.
Top comments (0)