DEV Community

SASHMITHA G 24CB054
SASHMITHA G 24CB054

Posted on

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

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;



🚀 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.

SQL #DBMS #DatabaseDesign #Normalization #BackendDevelopment

Thank you @santhoshnc sir for guiding me...

Top comments (0)