Introduction:
In this post, we will understand Database Normalization with a simple student-course example.
We will start from a single table (base table) and convert it step by step into 1NF, 2NF, and 3NF using MySQL 8.0.
Finally, we will run a JOIN query to display students with their courses and instructors.
🔹Base Table
Here is the starting data:
StudentID StudentName CourseID CourseName Instructor InstructorPhone
S01 Arjun C101 DBMS Dr. Kumar 9876543210
S01 Arjun C102 DataMining Dr. Mehta 9123456780
S02 Priya C101 DBMS Dr. Kumar 9876543210
S03 Kiran C103 AI Dr. Rao 9988776655
🔹Anomalies in this table:
Insert anomaly: Can’t add a new instructor without adding a course.
Update anomaly: If Dr. Kumar’s phone changes, we must update many rows.
Delete anomaly: If S02’s record is deleted, course DBMS info may also be lost.
🔹Step 1 — First Normal Form (1NF):
In 1NF, all values should be atomic (no repeating groups).
We also add a primary key = (StudentID, CourseID).
CREATE TABLE Base1NF (
StudentID VARCHAR(10),
StudentName VARCHAR(50),
CourseID VARCHAR(10),
CourseName VARCHAR(50),
Instructor VARCHAR(50),
InstructorPhone VARCHAR(20),
PRIMARY KEY (StudentID, CourseID)
);
INSERT INTO Base1NF VALUES
('S01','Arjun','C101','DBMS','Dr. Kumar','9876543210'),
('S01','Arjun','C102','Data Mining','Dr. Mehta','9123456780'),
('S02','Priya','C101','DBMS','Dr. Kumar','9876543210'),
('S03','Kiran','C103','AI','Dr. Rao','9988776655');
🔹Step 2 — Second Normal Form (2NF)
In 2NF, we remove partial dependencies.
We create 3 tables: Students, Courses, Enrollments.
CREATE TABLE Students2NF (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);
CREATE TABLE Courses2NF (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
Instructor VARCHAR(50),
InstructorPhone VARCHAR(20)
);
CREATE TABLE Enrollments2NF (
StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students2NF(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses2NF(CourseID)
);
-- Insert Students
INSERT INTO Students2NF VALUES ('S01','Arjun'),('S02','Priya'),('S03','Kiran');
-- Insert Courses
INSERT INTO Courses2NF VALUES
('C101','DBMS','Dr. Kumar','9876543210'),
('C102','Data Mining','Dr. Mehta','9123456780'),
('C103','AI','Dr. Rao','9988776655');
-- Insert Enrollments
INSERT INTO Enrollments2NF VALUES
('S01','C101'),('S01','C102'),('S02','C101'),('S03','C103');
🔹 Step 3 — Third Normal Form (3NF)
In 3NF, we remove transitive dependencies.
Instructor phone depends on Instructor, not Course → so we create a separate Instructors table.
CREATE TABLE Instructors3NF (
InstructorID INT PRIMARY KEY,
InstructorName VARCHAR(50),
InstructorPhone VARCHAR(20)
);
CREATE TABLE Students3NF (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);
CREATE TABLE Courses3NF (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
InstructorID INT,
FOREIGN KEY (InstructorID) REFERENCES Instructors3NF(InstructorID)
);
CREATE TABLE Enrollments3NF (
StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students3NF(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses3NF(CourseID)
);
-- Insert Instructors
INSERT INTO Instructors3NF VALUES
(1,'Dr. Kumar','9876543210'),
(2,'Dr. Mehta','9123456780'),
(3,'Dr. Rao','9988776655');
-- Insert Students
INSERT INTO Students3NF VALUES
('S01','Arjun'),('S02','Priya'),('S03','Kiran');
-- Insert Courses
INSERT INTO Courses3NF VALUES
('C101','DBMS',1),
('C102','Data Mining',2),
('C103','AI',3);
-- Insert Enrollments
INSERT INTO Enrollments3NF VALUES
('S01','C101'),('S01','C102'),('S02','C101'),('S03','C103');
🔹Step 4 — JOIN Query
Now we can easily display all students with their courses and instructors.
SELECT s.StudentID, s.StudentName,
c.CourseID, c.CourseName,
i.InstructorName, i.InstructorPhone
FROM Enrollments3NF e
JOIN Students3NF s ON e.StudentID = s.StudentID
JOIN Courses3NF c ON e.CourseID = c.CourseID
JOIN Instructors3NF i ON c.InstructorID = i.InstructorID;
👉 Output:
S01 | Arjun | C101 | DBMS | Dr. Kumar | 9876543210
S01 | Arjun | C102 | Data Mining | Dr. Mehta | 9123456780
S02 | Priya | C101 | DBMS | Dr. Kumar | 9876543210
S03 | Kiran | C103 | AI | Dr. Rao | 9988776655
✅ Conclusion
1NF: atomic values, primary key added.
2NF: separated Students, Courses, Enrollments.
3NF: separated Instructors table, removed redundancy.
Now our database is clean, consistent, and free from anomalies 🎉.
Top comments (0)