DATABASE NORMALIZATION
Complete Study Guide — 1NF, 2NF, 3NF
Based on Patients Table Case Study
BBSUL — Department of Computer Science | DBMS Course
Section 1: Definitions
1.1 What is Normalization?
Normalization is the process of organizing a relational database to reduce data redundancy and
improve data integrity by dividing large tables into smaller, well-structured tables and defining
relationships between them.
The main goals of normalization are:
• Eliminate redundant (repeated) data
• Ensure data dependencies make sense — only related data is stored in a table
• Prevent insertion, deletion, and update anomalies
• Make the database easier to maintain and query
Normalization is guided by a series of rules called Normal Forms (NF). Each normal form builds on the
previous one. A database must satisfy 1NF before it can be in 2NF, and must satisfy 2NF before it can
be in 3NF.
1.2 Key Terms
Term Definition
Primary Key (PK) One or more columns that uniquely identify each row in a table
A column that references the Primary Key of another table, creating a
Foreign Key (FK)
relationship
A primary key made of TWO or more columns combined — used when no
Composite Key
single column is unique
Column B is functionally dependent on Column A if knowing A always tells
Functional Dependency
you B. Written as A -> B
A non-key attribute depends on PART of a composite key, not the whole
Partial Dependency
key — violates 2NF
A non-key attribute depends on another non-key attribute (A -> B -> C
Transitive Dependency
where B is not a key) — violates 3NF
Each cell contains ONE indivisible value — no lists, no comma-separated
Atomicity
values, no arrays
A problem that occurs when inserting, deleting, or updating data in a poorly
Anomaly
structured table
Section 2: The Original (Unnormalized) Patients Table
The following table is in Unnormalized Form (UNF). It contains data for a hospital's patient records.
Patien PatientNa DoctorAssi
Diseases ContactNumbers VisitDates
tID me gned
9876543210, 01-Jan, 15-
P01 John Dr. Smith Diabetes, BP
9123456789 Jan, 20-Feb
P02 Sara Dr. Jones Asthma 9988776655 10-Feb
Fever, Cold, 9871234560, 05-Mar, 12-
P03 Mike Dr. Smith
BP 9860001111 Mar
Orange-highlighted cells contain multiple values — this is the core problem that normalization solves.
Problems in the UNF Table
• Diseases column: P01 has 'Diabetes, BP' — two diseases in one cell
• ContactNumbers column: P01 has two phone numbers in one cell
• VisitDates column: P01 has three dates in one cell
• Mike (P03) has 3 diseases, 2 contact numbers, and 2 visit dates — all crammed into single cells
• If Dr. Smith changes his name, we must update multiple rows — update anomaly
• We cannot add a doctor to the system without first adding a patient — insertion anomaly
Section 3: First Normal Form (1NF)
3.1 Definition of 1NF
A table is in First Normal Form (1NF) if: (1) All columns contain atomic (indivisible) values. (2) Each
column contains values of a single type. (3) Each row is unique — no duplicate rows. (4) There are
no repeating groups or arrays.
3.2 How 1NF Solves the Multi-Value Problem
The fix is simple but has a cost: for every combination of values, create a separate row. This is called
decomposition.
John's record example (P01): John has 2 diseases (Diabetes, BP), 2 contact numbers, and 3 visit
dates.
• In UNF: 1 row with everything crammed in
• In 1NF: Each atomic value gets its own row
• PatientID P01 appears on multiple rows — once for each Disease + Contact + VisitDate
combination
• The composite primary key is (PatientID, Disease, ContactNumber, VisitDate) — all four
together identify a unique row
Mike's record (P03): Mike has 3 diseases, 2 contact numbers, and 2 visit dates — all split into
separate rows in 1NF.
The 1NF table after decomposition:
Patien PatientNa DoctorAssi
Disease* ContactNumber* VisitDate*
tID* me gned
P01 John Dr. Smith Diabetes 9876543210 01-Jan
P01 John Dr. Smith Diabetes 9876543210 15-Jan
P01 John Dr. Smith BP 9123456789 20-Feb
P02 Sara Dr. Jones Asthma 9988776655 10-Feb
P03 Mike Dr. Smith Fever 9871234560 05-Mar
P03 Mike Dr. Smith Cold 9871234560 12-Mar
P03 Mike Dr. Smith BP 9860001111 05-Mar
* = part of composite primary key
3.3 Violations in the Original Table
Column Violation Example Fix Applied
Diseases Multi-valued 'Diabetes, BP' One disease per row
ContactNumbers Multi-valued '9876…, 9123…' One number per row
VisitDates Multi-valued '01-Jan, 15-Jan, 20-Feb' One date per row
3.4 Remaining Problem After 1NF
After 1NF, PatientName and DoctorAssigned repeat on EVERY row for a patient. John's name
appears 3 times; Dr. Smith appears 5 times. This is data redundancy caused by PARTIAL
DEPENDENCY — these attributes depend only on PatientID, not on the full composite key.
Section 4: Second Normal Form (2NF)
4.1 Definition of 2NF
A table is in Second Normal Form (2NF) if: (1) It is already in 1NF. (2) Every non-key attribute is
FULLY functionally dependent on the ENTIRE primary key — no partial dependencies.
Note: 2NF is only relevant when there is a COMPOSITE primary key. A table with a single-column
primary key is automatically in 2NF if it is in 1NF.
4.2 Identifying Partial Dependencies in the 1NF Table
The composite key in 1NF is: (PatientID, Disease, ContactNumber, VisitDate)
Attribute Depends On Type Violation?
PatientName PatientID only (not full key) Partial Dependency YES
DoctorAssigned PatientID only Partial Dependency YES
Disease Full composite key Full Dependency NO
ContactNumber Full composite key Full Dependency NO
VisitDate Full composite key Full Dependency NO
4.3 Solving 2NF — Split the Table
Move partially-dependent attributes into a separate table with their determinant (PatientID) as the
primary key.
Table 1 — Patients
PatientID (PK) PatientName
P01 John
P02 Sara
P03 Mike
Table 2 — Visits (with remaining attributes)
PatientI
DoctorAssig ContactNumber
D Disease (PK) VisitDate (PK)
ned (PK)
(PK/FK)
P01 Dr. Smith 9876543210 Diabetes 01-Jan
P01 Dr. Smith 9876543210 Diabetes 15-Jan
P01 Dr. Smith 9123456789 BP 20-Feb
P02 Dr. Jones 9988776655 Asthma 10-Feb
P03 Dr. Smith 9871234560 Fever 05-Mar
P03 Dr. Smith 9871234560 Cold 12-Mar
P03 Dr. Smith 9860001111 BP 05-Mar
Orange cells in the Visits table show DoctorAssigned still repeating — Dr. Smith appears 5 times.
This is a TRANSITIVE DEPENDENCY, which will be fixed in 3NF.
Section 5: Third Normal Form (3NF)
5.1 Definition of 3NF
A table is in Third Normal Form (3NF) if: (1) It is already in 2NF. (2) There are NO transitive
dependencies — no non-key attribute depends on another non-key attribute.
Transitive Dependency rule: If A -> B and B -> C, and B is not a key, then C is transitively dependent
on A through B. This is the violation 3NF removes.
5.2 Transitive Dependency in the 2NF Table
In the Visits table after 2NF: PatientID -> DoctorAssigned but DoctorAssigned is a non-key attribute.
Doctor details (name, specialization) belong to a Doctor entity, not to a visit record. If Dr. Smith
changes his name, every row must be updated.
5.3 Solving 3NF — Final Tables
Extract each concern into its own table:
Table 1 — Patients
PatientID (PK) PatientName
P01 John
P02 Sara
P03 Mike
Table 2 — Doctors
DoctorID (PK) DoctorName
D01 Dr. Smith
D02 Dr. Jones
Table 3 — PatientDoctor (links patients to doctors)
PatientID (FK) DoctorID (FK)
P01 D01
P02 D02
P03 D01
Table 4 — PatientDiseases
PatientID (PK/FK) Disease (PK)
P01 Diabetes
P01 BP
P02 Asthma
P03 Fever
P03 Cold
P03 BP
Table 5 — PatientContacts
PatientID (PK/FK) ContactNumber (PK)
P01 9876543210
P01 9123456789
P02 9988776655
P03 9871234560
P03 9860001111
Table 6 — PatientVisits
PatientID (PK/FK) VisitDate (PK)
P01 01-Jan
P01 15-Jan
P01 20-Feb
P02 10-Feb
P03 05-Mar
P03 12-Mar
Section 6: Column-by-Column Violation Analysis (Q1)
All columns analyzed for 1NF, 2NF, and 3NF violations:
Column 1NF Violation? 2NF Violation? 3NF Violation?
PatientID 1NF: OK 2NF: OK (part of PK) 3NF: OK
2NF: VIOLATES — depends
PatientNam
1NF: OK (atomic) only on PatientID, not full 3NF: OK after 2NF fix
e
composite key
3NF: VIOLATES —
DoctorAssig 2NF: VIOLATES — depends DoctorAssigned transitively
1NF: OK (atomic)
ned only on PatientID depends via PatientID ->
Doctor entity
1NF: VIOLATES —
2NF: VIOLATES (partial dep on
Diseases multi-valued 3NF: OK after separation
PatientID)
(Diabetes, BP)
1NF: VIOLATES —
ContactNum
multi-valued (two 2NF: VIOLATES (partial dep) 3NF: OK after separation
bers
numbers)
1NF: VIOLATES —
VisitDates multi-valued (3 2NF: VIOLATES (partial dep) 3NF: OK after separation
dates)
Section 7: John's Record Violations — Row-by-Row
Analysis (Q2)
John is PatientID P01 with: Diseases: Diabetes, BP | Contacts: 9876543210, 9123456789 | Visits: 01-
Jan, 15-Jan, 20-Feb
NF Count What Violations Exist Why
Diseases (2 values), ContactNumbers (2 Non-atomic cells — multiple
1NF 3
values), VisitDates (3 values) values in single columns
These depend only on
PatientName (John) and DoctorAssigned
2NF 2 PatientID, not on the full
(Dr. Smith) repeat on every row for P01
composite key
PatientID -> DoctorAssigned,
DoctorAssigned — doctor details belong
3NF 1 but doctor's details depend on
to a separate Doctor entity
the doctor, not the patient
Total violations in John's record: 6 (3 in 1NF + 2 in 2NF + 1 in 3NF). Note that 2NF and 3NF
violations are only visible AFTER 1NF is applied and rows are expanded.
Section 8: Anomalies and Problems at Each Stage
Anomaly Type Occurs At Example from Patients Table Solution
Cannot add a new doctor without
UNF / 1NF /
Insertion Anomaly assigning a patient first (PatientID Separate Doctor table (3NF)
2NF
is part of PK)
Deleting the last patient of a
UNF / 1NF /
Deletion Anomaly doctor also deletes the doctor's Separate Doctor table (3NF)
2NF
record
Changing Dr. Smith's name
UNF / 1NF /
Update Anomaly requires updating every row Separate Doctor table (3NF)
2NF
where he appears
PatientName and DoctorAssigned
Data Redundancy UNF / 1NF 2NF and 3NF splitting
repeat on every visit row
Diseases column holds 'Diabetes,
Non-Atomic
UNF BP' — cannot query individual 1NF — one value per cell
Values
diseases
8.1 Data Redundancy After 1NF (Q4)
After converting to 1NF, the biggest visible problem is data repetition. PatientName and
DoctorAssigned are repeated on every single row for a patient. For John (P01), his name 'John' and
doctor 'Dr. Smith' appear on 3 rows. For Mike (P03), they appear on 3 rows. If any of this data
changes, ALL copies must be updated simultaneously — if even one is missed, the database
becomes inconsistent (update anomaly).
Specific redundancies visible after 1NF:
• 'John' stored 3 times (once per visit row for P01)
• 'Dr. Smith' stored 5 times (3 for P01, 2 for P03... wait, Mike also has 3 rows)
• 'Dr. Jones' stored 1 time for P02
• This is exactly what 2NF solves — by removing partial dependencies
Section 9: Normalization Comparison Summary
Remaining
NF Core Rule Condition How to Fix
Problems
Data repetition;
Split multi-values into
No multi-valued cells, partial
1NF Atomicity separate rows;
no repeating groups dependencies
composite PK
remain
Remove attributes that
Every non-key attribute Transitive
No partial depend on part of
2NF depends on FULL dependencies may
dependency composite key; create
primary key remain
new table
No non-key attribute Extract transitive Considered fully
No transitive
3NF depends on another dependencies into their normalized for most
dependency
non-key attribute own table practical purposes
Section 10: Complete Viva Question Bank
All possible viva questions with detailed answers for the Patients table case study.
10.1 Definition Questions
Q: What is normalization?
A: Normalization is the process of organizing a database to reduce redundancy and improve data
integrity. It involves decomposing a large table into smaller, well-structured tables using a set of rules
called Normal Forms (1NF, 2NF, 3NF, BCNF, etc.).
Q: What is First Normal Form (1NF)?
A: A table is in 1NF if every column contains only atomic (indivisible) values, every column contains
values of the same type, each row is unique, and there are no repeating groups. The key rule: ONE
value per cell.
Q: What is Second Normal Form (2NF)?
A: A table is in 2NF if it is already in 1NF AND every non-key attribute is fully functionally dependent
on the entire primary key — no partial dependencies exist. 2NF applies only when a composite
primary key exists.
Q: What is Third Normal Form (3NF)?
A: A table is in 3NF if it is already in 2NF AND there are no transitive dependencies — meaning no
non-key attribute depends on another non-key attribute. Every non-key attribute must depend directly
on the primary key.
Q: What is a functional dependency?
A: Column B is functionally dependent on column A (written A -> B) if knowing the value of A always
determines the value of B. For example, PatientID -> PatientName means knowing a patient's ID
always tells you their name.
Q: What is a partial dependency?
A: A partial dependency occurs when a non-key attribute depends on only PART of a composite
primary key, not the full key. For example, if the key is (PatientID, Disease) but PatientName
depends only on PatientID, that is a partial dependency. This violates 2NF.
Q: What is a transitive dependency?
A: A transitive dependency occurs when A -> B -> C, where B is a non-key attribute. C is transitively
dependent on A through B. Example: PatientID -> DoctorID -> DoctorName. DoctorName is
transitively dependent on PatientID. This violates 3NF.
Q: What is atomicity?
A: Atomicity means each cell in a database table holds one and only one value — it cannot be broken
down further in the context of the application. A cell holding 'Diabetes, BP' violates atomicity because
it contains two values.
10.2 Practical Application Questions
Q: Which columns in the Patients table violate 1NF and why?
A: Three columns violate 1NF: (1) Diseases — P01 has 'Diabetes, BP', P03 has 'Fever, Cold, BP' —
multiple values in one cell. (2) ContactNumbers — P01 has two numbers, P03 has two numbers. (3)
VisitDates — P01 has three dates, P03 has two dates. All three violate the atomicity requirement of
1NF.
Q: How do you fix 1NF violations?
A: Create a new row for each individual value. For P01 who has Diabetes and BP, create two
separate rows — one with Disease=Diabetes and one with Disease=BP. The primary key becomes
composite: (PatientID, Disease, ContactNumber, VisitDate). Every cell now holds exactly one value.
Q: Why does PatientName violate 2NF?
A: In the 1NF table, the composite key is (PatientID, Disease, ContactNumber, VisitDate).
PatientName depends ONLY on PatientID — knowing the disease, contact number, or visit date tells
you nothing new about the patient's name. This partial dependency on just one part of the composite
key violates 2NF.
Q: Why does DoctorAssigned violate 3NF?
A: After 2NF, DoctorAssigned is in the Patients table where PatientID is the primary key.
DoctorAssigned depends on PatientID (PatientID -> DoctorAssigned), but the doctor's details (name,
specialization) actually belong to the doctor entity. There is a chain: PatientID -> DoctorID ->
DoctorName — this transitive dependency violates 3NF.
Q: How many tables result after full normalization to 3NF?
A: Six tables: (1) Patients — PatientID, PatientName. (2) Doctors — DoctorID, DoctorName. (3)
PatientDoctor — PatientID (FK), DoctorID (FK). (4) PatientDiseases — PatientID (FK), Disease. (5)
PatientContacts — PatientID (FK), ContactNumber. (6) PatientVisits — PatientID (FK), VisitDate.
Q: What is the composite key in the 1NF table?
A: The composite primary key in the 1NF Patients table is (PatientID, Disease, ContactNumber,
VisitDate). All four columns together uniquely identify one row. No single column alone is sufficient to
identify a unique record.
Q: What problems remain after 1NF?
A: After 1NF: (1) Data redundancy — PatientName and DoctorAssigned repeat on every row for the
same patient. (2) Partial dependencies still exist — PatientName and DoctorAssigned depend only
on PatientID, not the full composite key. These are the 2NF violations that must be fixed next.
Q: What is an update anomaly? Give an example from this table.
A: An update anomaly is when changing one piece of data requires updating multiple rows, and
missing even one creates inconsistency. Example: Dr. Smith's name appears in 5 rows. If he
changes his name, all 5 must be updated. If only 3 are updated, the database has two different
names for the same doctor — an inconsistency.
Q: What is an insertion anomaly? Give an example.
A: An insertion anomaly is when you cannot add data without adding other unrelated data. In the
UNF/2NF table, you cannot record a new doctor (say Dr. Ali) without first assigning at least one
patient to them — because DoctorAssigned only exists in the context of a patient row.
Q: What is a deletion anomaly? Give an example.
A: A deletion anomaly is when deleting a row accidentally deletes other important data. If patient P02
(Sara) is the only patient of Dr. Jones, deleting P02's record also removes all information about Dr.
Jones from the database.
10.3 Conceptual and Deeper Questions
Q: Why is 2NF only relevant with composite keys?
A: Partial dependency means a non-key attribute depends on PART of the key. If the primary key has
only ONE column, there are no 'parts' — so partial dependency is impossible. Therefore, any table in
1NF with a single-column primary key is automatically in 2NF.
Q: Can a table be in 3NF but not 2NF?
A: No. Normal forms are cumulative — 3NF requires the table to first satisfy 2NF, which requires
1NF. You must go through each level in order. A table violating 2NF cannot be in 3NF.
Q: What is BCNF (Boyce-Codd Normal Form)?
A: BCNF is a stricter version of 3NF. A table is in BCNF if for every functional dependency A -> B, A
must be a superkey (a key or superset of a key). BCNF handles cases where a table is in 3NF but
still has anomalies due to multiple overlapping candidate keys. For most practical databases, 3NF is
sufficient.
Q: What are the advantages of normalization?
A: Advantages: (1) Eliminates data redundancy — data stored once. (2) Prevents update, insertion,
and deletion anomalies. (3) Ensures data consistency. (4) Makes the database easier to maintain. (5)
Reduces storage space.
Q: What are the disadvantages of normalization?
A: Disadvantages: (1) More tables require JOIN operations, which can slow queries. (2) More
complex schema — harder to understand for beginners. (3) For read-heavy applications
(analytics/reporting), a denormalized design may perform better. (4) More development time needed
upfront.
Q: What is denormalization and when is it used?
A: Denormalization is the deliberate process of combining normalized tables back into fewer, larger
tables to improve read query performance. It is used in data warehouses, reporting systems, and
OLAP (Online Analytical Processing) scenarios where read speed is more important than write
efficiency.
Q: What is the difference between 2NF and 3NF in simple terms?
A: 2NF: Remove attributes that depend on only PART of the key (partial dependency). 3NF: Remove
attributes that depend on other non-key attributes rather than directly on the key (transitive
dependency). Simple memory trick: 2NF = fix partial, 3NF = fix transitive.
Q: How do you know when a table is fully normalized (3NF)?
A: A table is in 3NF when: (1) Every cell has one value (1NF). (2) Every non-key column depends on
the WHOLE primary key (2NF). (3) Every non-key column depends ONLY on the primary key, not on
other non-key columns (3NF). If you can say 'Every attribute depends on the key, the whole key, and
nothing but the key' — the table is in 3NF.
Q: What is the famous memory trick for 3NF?
A: The classic phrase: 'Every non-key attribute must depend on the key (1NF), the whole key (2NF),
and nothing but the key (3NF) — so help me Codd.' This was coined in reference to E.F. Codd who
invented relational theory.
Section 11: Quick Reference Cheat Sheet
Remaining
NF Core Rule Condition How to Fix
Problems
Data repetition;
Split multi-values into
No multi-valued cells, partial
1NF Atomicity separate rows;
no repeating groups dependencies
composite PK
remain
Remove attributes that
Every non-key attribute Transitive
No partial depend on part of
2NF depends on FULL dependencies may
dependency composite key; create
primary key remain
new table
No non-key attribute Extract transitive Considered fully
No transitive
3NF depends on another dependencies into their normalized for most
dependency
non-key attribute own table practical purposes
Normal Form Check this question Memory Trick
Does every cell have exactly ONE
1NF One value per cell — no lists, no arrays
value?
Does every non-key attribute depend Full key dependency — no partial
2NF
on the FULL key? shortcuts
Does every non-key attribute depend
Direct key dependency — no
3NF DIRECTLY on the key (not through
middlemen
another non-key attribute)?
End of Normalization Study Guide
BBSUL — Department of Computer Science — DBMS