Understanding 1NF, 2NF, and 3NF Using SQL — Step-by-Step Implementation

#sql #database #tutorial

Database normalization is a process used to organize data in a database efficiently.It helps to remove redundancy, improve data integrity, and make data maintenance easier.
This post walks through the process of converting a database into 1NF, 2NF, and 3NF, showing each stage with practical SQL CREATE TABLE and INSERT examples.

Base Table

Let’s start with the following unnormalized Student Enrollment table:

This table stores student details, courses, and instructor information together.

Data Anomalies

This unnormalized table can cause three types of anomalies:

Insertion anomaly:
Cannot add a new course unless a student is enrolled in it.

Update anomaly:
If an instructor’s phone number changes, it must be updated in multiple rows.

Deletion anomaly:
Deleting a student record might also remove information about a course or instructor.

To overcome these issues, we will normalize the table step by step.

Step 1: First Normal Form (1NF)

Each column should contain atomic (single) values.
No repeating groups or arrays are allowed.

CREATE TABLE Student_1NF (
    StudentID INT,
    StudentName VARCHAR(50),
    Course VARCHAR(50),
    Instructor VARCHAR(50),
    InstructorPhone VARCHAR(15)
);

Step 2: Second Normal Form (2NF)

The table must be in 1NF.
All non-key attributes must depend on the entire primary key, not just a part of it.

CREATE TABLE Students (
    StudentID VARCHAR(10) PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL
);

CREATE TABLE Courses_2NF (
    CourseID VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    Instructor VARCHAR(100) NOT NULL,
    InstructorPhone VARCHAR(15) NOT NULL
);

CREATE TABLE Enrollments_2NF (
    StudentID VARCHAR(10) NOT NULL,
    CourseID VARCHAR(10) NOT NULL,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses_2NF(CourseID)
);

INSERT INTO Students (StudentID, StudentName) VALUES
('S01','Arjun'),
('S02','Priya'),
('S03','Kiran');

INSERT INTO Courses_2NF (CourseID, CourseName, Instructor, InstructorPhone) VALUES
('C101','DBMS','Dr. Kumar','9876543210'),
('C102','Data Mining','Dr. Mehta','9123456780'),
('C103','AI','Dr. Rao','9988776655');

INSERT INTO Enrollments_2NF (StudentID, CourseID) VALUES
('S01','C101'),('S01','C102'),('S02','C101'),('S03','C103');

Step 3: Third Normal Form (3NF)

The table must be in 2NF.
There should be no transitive dependency (non-key attributes should not depend on other non-key attributes).

Here, InstructorPhone depends on Instructor, not on the key — so we separate instructor details into a new table.

CREATE TABLE StudentDetails (
    StudentID VARCHAR(10) PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE InstructorDetails (
    InstructorID VARCHAR(10) PRIMARY KEY,
    InstructorName VARCHAR(100) NOT NULL,
    InstructorPhone VARCHAR(15) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE CourseDetails (
    CourseID VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    InstructorID VARCHAR(10) NOT NULL,
    FOREIGN KEY (InstructorID) REFERENCES InstructorDetails(InstructorID)
) ENGINE=InnoDB;

CREATE TABLE StudentCourseEnrollments (
    StudentID VARCHAR(10) NOT NULL,
    CourseID VARCHAR(10) NOT NULL,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES StudentDetails(StudentID),
    FOREIGN KEY (CourseID) REFERENCES CourseDetails(CourseID)
) ENGINE=InnoDB;

INSERT INTO StudentDetails (StudentID, StudentName) VALUES
('S11','Rohan'),
('S12','Meera'),
('S13','Vikram'),
('S14','Sneha');

INSERT INTO InstructorDetails (InstructorID, InstructorName, InstructorPhone) VALUES
('I11','Dr. Sharma','9871112233'),
('I12','Dr. Nair','9122223344'),
('I13','Dr. Kapoor','9988771122'),
('I14','Dr. Joshi','9899988776');

INSERT INTO CourseDetails (CourseID, CourseName, InstructorID) VALUES
('C201','Operating Systems','I11'),
('C202','Computer Networks','I12'),
('C203','Machine Learning','I13'),
('C204','Cloud Computing','I14');

INSERT INTO StudentCourseEnrollments (StudentID, CourseID) VALUES
('S11','C201'),
('S11','C202'),
('S12','C201'),
('S13','C203'),
('S14','C204');