Normalization in 1NF,2NF,3NF in DBMS

Normalization is a key concept in relational database design. It helps eliminate redundancy and improve data integrity. In this post, we’ll explore the first three normal forms—1NF, 2NF, and 3NF—and implement each using SQL CREATE TABLE statements.

First Normal Form (1NF)
Rule:

Each column should contain atomic (indivisible) values.

No repeating groups or arrays.

Example: Unnormalized Table

sql
CREATE TABLE Students (
student_id INT,
name VARCHAR(100),
subjects VARCHAR(255) -- e.g., 'Math, Science, English'
);
Normalized to 1NF

sql
CREATE TABLE StudentSubjects (
student_id INT,
name VARCHAR(100),
subject VARCHAR(50) -- one subject per row
);

Second Normal Form (2NF)
Rule:

Must be in 1NF

All non-key attributes must be fully functionally dependent on the entire primary key.

Problem Example (Partial Dependency)

sql
CREATE TABLE Enrollments (
student_id INT,
course_id INT,
student_name VARCHAR(100) -- depends only on student_id
);
Normalized to 2NF

sql
CREATE TABLE Students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100)
);

CREATE TABLE Courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100)
);

CREATE TABLE Enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id)
);

Third Normal Form (3NF)
Rule:

Must be in 2NF

No transitive dependencies (non-key attributes should not depend on other non-key attributes)

Problem Example (Transitive Dependency)

sql
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
dept_name VARCHAR(100),
dept_location VARCHAR(100) -- depends on dept_name
);
Normalized to 3NF

sql
CREATE TABLE Departments (
dept_name VARCHAR(100) PRIMARY KEY,
dept_location VARCHAR(100)
);

CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
dept_name VARCHAR(100),
FOREIGN KEY (dept_name) REFERENCES Departments(dept_name)
);
Summary
Normal Form Key Focus
1NF Atomic values, no repeating groups
2NF Full functional dependency
3NF No transitive dependency
Normalization helps keep your data clean, consistent, and scalable. If you’re building relational databases, mastering these forms is essential.

Thank you @santhoshnc sir for guideing me