Understanding 1NF, 2NF, and 3NF in DBMS with SQL Examples

#database #design #tutorial #sql

Base Table
Use the following data as the starting point:

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:

StudentID StudentName CourseID CourseName Instructor InstructorEmail

1 Alice C101 DBMS Dr. Smith smith@uni.edu
2 Bob C102 Networks Dr. Lee lee@uni.edu
3 Alice C103 AI Dr. Clark clark@uni.edu

🚨 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;

🚀 Final Thoughts

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.