DEV Community

Cover image for Understanding 1NF, 2NF, and 3NF Using SQL — Step-by-Step Implementation
Ilakkiya
Ilakkiya

Posted on

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

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');

At this stage, the database is fully normalized into Third Normal Form.

Displaying All Students with Their Courses and Instructors

Conclusion

Through these steps, we successfully:

  • Identified anomalies in the base table
  • Converted it to 1NF, 2NF, and 3NF
  • Used SQL commands to design normalized tables
  • Joined the data to display meaningful results

Database normalization not only removes redundancy but also ensures that data remains consistent and easy to maintain.

sql #database #normalization #learning #assignments

Top comments (0)