Normalization in DBMS: 1NF, 2NF & 3NF Explained Step by Step With Examples

DBMS Chapter 15 · Normalization Repair Workshop

Normalization in DBMS: 1NF, 2NF & 3NF Explained Step by Step With Examples

Normalization is the process of organizing database tables so that repeated data, update problems and unnecessary dependency are reduced. In this chapter, you will learn why normalization is needed, what insertion, update and deletion anomalies are, how functional dependency is used, and how one messy table is converted step by step into First Normal Form (1NF), Second Normal Form (2NF) and Third Normal Form (3NF). The whole topic is explained with one college example so you can see exactly what changes at each stage.

1NF → 2NF → 3NF One continuous example Before/after tables Functional dependency connection Exam + viva ready Checked: 18 September 2026

Quick answer first

What Is Normalization in DBMS?

Normalization is a database-design process that divides poorly structured tables into smaller, related tables so that duplication and data anomalies are reduced. 1NF removes repeating or non-atomic values, 2NF removes partial dependency, and 3NF removes transitive dependency.

UnnormalizedRepeated groups and mixed facts.

1NFAtomic values and proper rows.

2NFNo partial dependency.

3NFNo problematic transitive dependency.

Easy memory line

1NF = one value · 2NF = whole key · 3NF = nothing but the key

Before starting normalization, you should understand functional dependency. Revise Functional Dependency in DBMS: Types, Rules & Simple Examples if terms such as partial dependency, transitive dependency and candidate key are still new.

Why normalization is needed

What Problem Does Normalization Solve?

Imagine a college stores student, department and course details in one large table. Student name and department name may repeat in many rows. At first this may look harmless, but repeated facts create three common problems called anomalies.

Update Anomaly The same fact appears in many rows, so changing only one copy creates inconsistent data.

Insertion Anomaly You cannot insert one fact without also supplying another unrelated fact.

Deletion Anomaly Deleting one row accidentally removes another useful fact.

Normalization separates different kinds of facts so that each fact is stored in the most suitable place.

Our starting point

The Messy College Table

Suppose the college uses this table:

Student_ID	Student_Name	Dept_ID	Dept_Name	Course_ID	Course_Name	Marks
101	Asha	D1	CSE	C1	DBMS	85
101	Asha	D1	CSE	C2	OS	78
102	Ravi	D2	ECE	C1	DBMS	72
103	Neha	D1	CSE	C1	DBMS	91

The candidate key for this enrollment-style table is:

(Student_ID, Course_ID)

because one student can study many courses and one course can contain many students.

Important functional dependencies are:

Student_ID → Student_Name, Dept_ID
Dept_ID → Dept_Name
Course_ID → Course_Name
(Student_ID, Course_ID) → Marks

These dependencies are the map we use during normalization.

See the problems clearly

Anomalies in the Messy Table

1. Update anomaly

If department D1 changes its name from CSE to Computer Science, we must update every row containing D1. If one row still says CSE and another says Computer Science, the database becomes inconsistent.

2. Insertion anomaly

Suppose the college creates a new course C3 called Networks but no student has enrolled yet. If course details exist only inside the enrollment table, we may not be able to store the course without inventing a student enrollment.

3. Deletion anomaly

If Ravi is the only student enrolled in a course and his row is deleted, the course information may disappear too. A student deletion should not accidentally delete a course definition.

Main idea

Normalization reduces these problems by separating student facts, course facts, department facts and enrollment facts.

Stage 01

First Normal Form (1NF)

A relation is in 1NF when each field contains one atomic value and repeating groups are removed. Each row-column intersection should hold one value from the intended domain, not a list such as “DBMS, OS, CN” inside one cell.

Example of a table that is not in 1NF

Student_ID	Student_Name	Courses
101	Asha	DBMS, OS
102	Ravi	DBMS

The Courses cell for Asha contains multiple course values. That makes searching, joining and updating harder.

Before 1NF

101 · Asha · DBMS, OS

After 1NF

101 · Asha · DBMS
101 · Asha · OS

1NF checklist

One value per cellNo comma-separated lists or repeating groups inside one field.

Rows are clearly identifiableA key or key combination should identify rows.

Consistent domainsEach column represents one type of fact.

No repeating column groupsAvoid designs such as Course1, Course2, Course3 for the same repeating fact.

Our main enrollment table already has one course per row, so it is in 1NF. But 1NF does not solve all redundancy.

Stage 02

Second Normal Form (2NF)

A relation is in 2NF if it is already in 1NF and every non-prime attribute is fully dependent on the whole candidate key. In simple words: if the key has multiple attributes, no non-key fact should depend on only part of that key.

Our composite candidate key is:

(Student_ID, Course_ID)

Now examine these dependencies:

Student_ID → Student_Name, Dept_ID
Course_ID → Course_Name
(Student_ID, Course_ID) → Marks

Partial dependency 1

Student_ID → Student_Name, Dept_ID

Student facts depend only on Student_ID.

Partial dependency 2

Course_ID → Course_Name

Course name depends only on Course_ID.

Full dependency

(Student_ID, Course_ID) → Marks

Marks need the full student-course combination.

Because Student_Name, Dept_ID and Course_Name depend on only part of the composite key, the relation violates 2NF.

How to convert it to 2NF

Move facts that depend on only one part of the key into separate tables.

STUDENT(
  Student_ID,
  Student_Name,
  Dept_ID
)

COURSE(
  Course_ID,
  Course_Name
)

ENROLLMENT(
  Student_ID,
  Course_ID,
  Marks
)

2NF memory line

Every non-key fact must depend on the whole composite key

Pictorial transformation

How the Table Looks After 2NF

STUDENT

Student_ID	Student_Name	Dept_ID	Dept_Name
101	Asha	D1	CSE
102	Ravi	D2	ECE
103	Neha	D1	CSE

COURSE

Course_ID	Course_Name
C1	DBMS
C2	OS

ENROLLMENT

Student_ID	Course_ID	Marks
101	C1	85
101	C2	78
102	C1	72
103	C1	91

We removed partial dependencies, but one problem remains: Dept_Name depends on Dept_ID, not directly on Student_ID.

Stage 03

Third Normal Form (3NF)

A relation is in 3NF if it is already in 2NF and non-prime attributes do not depend transitively on a candidate key through another non-prime attribute.

In the STUDENT table we have:

Student_ID → Dept_ID
Dept_ID → Dept_Name

Therefore:

Student_ID → Dept_Name

but this happens through Dept_ID. Dept_Name is transitively dependent on Student_ID.

Before 3NF

STUDENT(Student_ID, Student_Name, Dept_ID, Dept_Name)

After 3NF

STUDENT(Student_ID, Student_Name, Dept_ID)
DEPARTMENT(Dept_ID, Dept_Name)

Final 3NF design

STUDENT(
  Student_ID,
  Student_Name,
  Dept_ID
)

DEPARTMENT(
  Dept_ID,
  Dept_Name
)

COURSE(
  Course_ID,
  Course_Name
)

ENROLLMENT(
  Student_ID,
  Course_ID,
  Marks
)

3NF memory line

Non-key attributes should not depend on other non-key attributes

See the repaired database

Final 3NF Tables

STUDENT

Student_ID	Student_Name	Dept_ID
101	Asha	D1
102	Ravi	D2
103	Neha	D1

DEPARTMENT

Dept_ID	Dept_Name
D1	CSE
D2	ECE

COURSE

Course_ID	Course_Name
C1	DBMS
C2	OS

ENROLLMENT

Student_ID	Course_ID	Marks
101	C1	85
101	C2	78
102	C1	72
103	C1	91

Now student, department, course and enrollment facts have their own proper places. This reduces repeated values and makes updates safer.

Interactive revision

Explore Each Normal Form

Messy design: mixed facts and repetition

One table stores: Student facts Department facts Course facts Enrollment facts Result: repetition + anomalies

Normalization separates facts according to their dependencies.

What changes?

Main problemRepeated and mixed data.

Next repairMove to 1NF: one atomic value per field.

Fast comparison

Difference Between 1NF, 2NF and 3NF

Normal Form	Main Rule	Main Problem Removed	Typical Check
1NF	Atomic values and no repeating groups	Multi-valued or repeating fields	Does each field hold one value?
2NF	1NF + full dependency on whole candidate key	Partial dependency	Does a non-key attribute depend on only part of a composite key?
3NF	2NF + no problematic transitive dependency	Transitive dependency	Does a non-key attribute depend on another non-key attribute?

Use this method in exams

How to Normalize a Relation Step by Step

Write all attributesStart from the complete relation given in the question.

Identify candidate keyUse functional dependencies and attribute closure if needed.

List functional dependenciesWrite who determines whom.

Check 1NFRemove repeating groups and multi-valued fields.

Check partial dependenciesIf the key is composite, move facts that depend on only part of it.

Check transitive dependenciesMove non-key facts that depend on other non-key attributes.

Verify keys and relationshipsMake sure each new table has a proper primary key and required foreign keys.

Why keys matter during normalization

Primary Keys, Candidate Keys and Foreign Keys After Normalization

STUDENTPrimary key: Student_ID. Dept_ID becomes a foreign key.

DEPARTMENTPrimary key: Dept_ID.

COURSEPrimary key: Course_ID.

ENROLLMENTComposite primary key: (Student_ID, Course_ID). Both also reference parent tables.

For key concepts, revise Keys, Mapping Constraints, Generalization & Aggregation. For foreign-key rules, revise Integrity Constraints in DBMS.

Important design goal

What Is Lossless Decomposition?

Lossless decomposition means that after splitting a relation into smaller relations, we can join them back without losing valid information or creating incorrect extra information.

Normalization should not simply break a table into random smaller tables. The decomposition should preserve the meaning of the original relation.

Memory line

Split safely → join back correctly

Another design goal

What Is Dependency Preservation?

A decomposition is dependency-preserving when important functional dependencies can be enforced by checking the smaller relations directly, without needing expensive joins just to validate every dependency.

Lossless decomposition and dependency preservation are two important goals when designing normalized schemas.

Practical design note

Is More Normalization Always Better?

Normalization improves correctness and reduces redundancy, but real production systems also consider performance and query patterns. Highly normalized designs may require more joins.

Normalization helps

Data consistency, cleaner updates, less repetition and stronger logical design.

Too much splitting can cost

More joins, more complex queries and sometimes slower reporting workloads.

Designers may deliberately denormalize some data for performance, but that should be a conscious decision with clear consistency controls—not an excuse for poor initial design.

A more exact 3NF rule

Formal 3NF Condition in Simple Words

For every non-trivial functional dependency X → A, a relation is in 3NF when at least one of these is true:

X is a superkeyThe determinant can identify the whole relation.

A is a prime attributeThe right-side attribute belongs to at least one candidate key.

For most beginner examples, the easier rule “no non-key attribute should depend transitively on a key” is enough to understand 3NF. The formal definition becomes useful in advanced normalization questions.

Mistakes students often make

Normalization Mistakes to Avoid

Thinking 1NF removes all redundancy1NF only fixes repeating or non-atomic values. Partial and transitive dependencies can still remain.

Applying 2NF when no composite candidate key existsPartial dependency needs a composite key context.

Confusing partial and transitive dependencyPartial = part of composite key. Transitive = key reaches a non-key attribute through another non-key attribute.

Splitting tables without using FDsNormalization should follow functional dependencies, not guesswork.

Forgetting keys in new tablesEvery decomposed relation still needs proper keys and relationships.

Ignoring lossless decompositionA split is useful only if the original information can be reconstructed correctly.

Memorising definitions onlyNormalization questions are easiest when you trace exactly which attribute depends on which key.

Jumping directly to 3NFIn exams, showing the 1NF → 2NF → 3NF logic often earns clearer marks.

Complete solved example

Normalization Example From 1NF to 3NF

Given:

R(
  Student_ID,
  Student_Name,
  Dept_ID,
  Dept_Name,
  Course_ID,
  Course_Name,
  Marks
)

Candidate key:

(Student_ID, Course_ID)

Functional dependencies:

Student_ID → Student_Name, Dept_ID
Dept_ID → Dept_Name
Course_ID → Course_Name
(Student_ID, Course_ID) → Marks

Step 1: 1NF

Assume each field already contains one atomic value. So the relation is in 1NF.

Step 2: Convert to 2NF

Remove partial dependencies:

STUDENT(Student_ID, Student_Name, Dept_ID, Dept_Name)
COURSE(Course_ID, Course_Name)
ENROLLMENT(Student_ID, Course_ID, Marks)

Step 3: Convert to 3NF

In STUDENT, Dept_Name depends on Dept_ID, and Dept_ID depends on Student_ID. So move department details:

STUDENT(Student_ID, Student_Name, Dept_ID)
DEPARTMENT(Dept_ID, Dept_Name)
COURSE(Course_ID, Course_Name)
ENROLLMENT(Student_ID, Course_ID, Marks)

This final design is in 3NF for the functional dependencies used in this example.

Write according to marks

How to Write Normalization in Semester Exams

2 marks

Definition + purpose

Define normalization and mention that it reduces redundancy and anomalies.

5 marks

Explain 1NF, 2NF, 3NF

Write one rule and one short example for each normal form.

8-10 marks

Show full decomposition

Identify key and FDs, explain anomalies, then convert one relation step by step from 1NF to 2NF to 3NF.

For presentation, revise How to Write High-Scoring Answers in University Semester Exams. For deciding what to revise first, use the previous-year-paper priority method.

Practice questions with answers

Practice Questions With Answers

1. What is normalization in DBMS?

Normalization is the process of organizing tables to reduce redundancy, anomalies and unnecessary dependency.

2. What is 1NF?

A relation is in 1NF when each field contains one atomic value and repeating groups are removed.

3. What is 2NF?

A relation is in 2NF when it is in 1NF and every non-prime attribute is fully dependent on the whole candidate key.

4. What is 3NF?

A relation is in 3NF when it is in 2NF and problematic transitive dependency of non-prime attributes is removed.

5. What is partial dependency?

Partial dependency occurs when a non-prime attribute depends on only part of a composite candidate key.

6. What is transitive dependency?

A transitive dependency occurs when a key determines one non-key attribute, which in turn determines another non-key attribute.

7. What are insertion, update and deletion anomalies?

They are data problems caused by poor table design: difficulty inserting facts, inconsistent updates and accidental information loss during deletion.

8. Is every 3NF relation also in 2NF?

Yes. Reaching 3NF assumes the relation already satisfies the lower normal forms required before it.

9. What is lossless decomposition?

Lossless decomposition means the smaller relations can be joined back to reconstruct the original valid information correctly.

10. What comes after 3NF?

The next major normal form is BCNF, followed by higher forms such as 4NF and 5NF for more advanced dependency problems.

60-second revision

Normalization One-Minute Revision Sheet

NormalizationOrganize tables to reduce redundancy and anomalies.

1NFOne atomic value per field; no repeating groups.

2NF1NF + no partial dependency.

3NF2NF + no problematic transitive dependency.

Partial DependencyNon-key fact depends on part of a composite key.

Transitive DependencyKey reaches a non-key fact through another non-key fact.

Update AnomalyRepeated fact must be changed in multiple rows.

Insertion AnomalyOne fact cannot be inserted independently.

Deletion AnomalyDeleting one row removes another useful fact.

Lossless DecompositionSplit tables can be joined back correctly.

Ultimate memory line

1NF = one value · 2NF = whole key · 3NF = no non-key-to-non-key dependency

Frequently asked questions

Frequently Asked Questions

What is normalization in DBMS in simple words?

Normalization means arranging database tables so that each fact is stored in the right place, repeated data is reduced and insert, update and delete operations do not create avoidable inconsistencies.

What is the difference between 1NF, 2NF and 3NF?

1NF removes repeating or non-atomic values, 2NF removes partial dependency on part of a composite key, and 3NF removes problematic transitive dependency between non-key attributes.

Can a table be in 2NF without being in 1NF?

No. A relation must satisfy 1NF before it can satisfy 2NF.

When is 2NF important?

2NF becomes especially important when a relation has a composite candidate key and some non-key attributes depend on only part of that key.

Why is functional dependency important for normalization?

Functional dependencies show which attributes determine other attributes. They help identify keys, partial dependency and transitive dependency, which guide decomposition into normal forms.

What should I study after 3NF?

Study BCNF, lossless decomposition, dependency preservation, multivalued dependency and 4NF.

Next chapter

What Should You Learn Next?

Next: BCNF, Lossless Decomposition & Dependency Preservation

You now understand how normalization moves from atomic values to full dependency and then to removal of transitive dependency. The next chapter should explain BCNF, determinant rules, lossless decomposition, dependency preservation and decomposition testing with solved examples.

For video revision after reading, use the DBMS YouTube study guide only as a secondary resource.