When designing databases, normalization is essential to remove redundancy and anomalies (insertion, update, deletion). In this post, we’ll take a sample base table, identify anomalies, and step-by-step normalize it into 1NF, 2NF, and 3NF using SQL CREATE TABLE statements.
📌 Base Table
Let’s assume a base table with student-course-instructor details:
🚨 Anomalies in this table
Insertion anomaly: Cannot add a new course until a student enrolls.
Update anomaly: If instructor’s email changes, must update multiple rows.
Deletion anomaly: Deleting the last student in a course removes course and instructor details too.
✅ Step 1: Convert to 1NF (First Normal Form)
➡ Eliminate repeating groups and ensure atomic values.
CREATE TABLE StudentCourses_1NF (
StudentID INT,
StudentName VARCHAR(100),
CourseID VARCHAR(10),
CourseName VARCHAR(100),
Instructor VARCHAR(100),
InstructorEmail VARCHAR(100)
);
✅ Step 2: Convert to 2NF (Second Normal Form)
➡ Remove partial dependency (attributes depending only on part of composite key).
We separate Students, Courses, and Enrollments.
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100)
);
CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(100),
Instructor VARCHAR(100),
InstructorEmail VARCHAR(100)
);
CREATE TABLE Enrollments (
StudentID INT,
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
✅ Step 3: Convert to 3NF (Third Normal Form)
➡ Remove transitive dependency (InstructorEmail depends on Instructor, not CourseID).
So, we create a separate Instructors table.
CREATE TABLE Instructors (
InstructorID INT PRIMARY KEY,
InstructorName VARCHAR(100),
InstructorEmail VARCHAR(100)
);
CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(100),
InstructorID INT,
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);
📝 Insert Sample Data
INSERT INTO Students VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO Instructors VALUES (1, 'Dr. Smith', 'smith@uni.edu'),
(2, 'Dr. Lee', 'lee@uni.edu'),
(3, 'Dr. Clark', 'clark@uni.edu');
INSERT INTO Courses VALUES ('C101', 'DBMS', 1),
('C102', 'Networks', 2),
('C103', 'AI', 3);
INSERT INTO Enrollments VALUES (1, 'C101'), (2, 'C102'), (1, 'C103');
🔗 Query with JOINS
List all students with their courses and instructors:
SELECT s.StudentName, c.CourseName, i.InstructorName
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
JOIN Instructors i ON c.InstructorID = i.InstructorID;
Database normalization helps eliminate redundancy, prevent anomalies, and improve efficiency. By following 1NF → 2NF → 3NF, we created a robust schema ready for real-world applications.
Top comments (0)