Database Normalization

1NF → 2NF → 3NF

Complete Step-by-Step Guide with Examples

Based on Elmasri & Navathe — Fundamentals of Database Systems

What is Normalization?
Normalization is the process of organizing a database to reduce redundancy and eliminate
update anomalies. It works by decomposing large 'bad' tables into smaller, well-structured
tables.

Memory Rule
1NF — All attributes depend on the key 2NF — All attributes depend on the whole key 3NF — All
attributes depend on nothing but the key

Why Normalization is Needed — The 3 Anomalies

When data is mixed into one large table, three problems occur:

Anomaly Problem Example

Update Anomaly Changing one value requires many Changing project name 'Billing' must
rows to be updated update all 100 employee rows
Insert Anomaly Cannot insert data without unrelated Cannot add a new project unless at
data existing first least one employee is assigned
Delete Anomaly Deleting one record accidentally Deleting the last employee on a
removes other data project removes the project too
Step 1 — First Normal Form (1NF)
Rule
Every cell must hold exactly ONE atomic value. No sets, lists, or nested relations allowed.

What 1NF Forbids

Violation Type Example (Bad) Fix
Composite attribute Full_Name = 'Ali Khan' Split into Fname + Lname
Multivalued attribute Dlocations = {Bellaire, One row per location
Sugarland}
Nested relation PROJS = {(P1,32.5),(P2,7.5)} Separate relation with FK

Example — Figure 14.9 from Slides

BEFORE 1NF — Dlocations holds a set (not atomic)
Dname Dnumber (PK) Dmgr_ssn Dlocations ✗
Research 5 333445555 {Bellaire, Sugarland,
Houston}
Administration 4 987654321 {Stafford}
Headquarters 1 888665555 {Houston}

↓ Expand: one row per location. New PK = (Dnumber, Dlocation)

AFTER 1NF — One atomic value per cell

Dname Dnumber (PK) Dmgr_ssn Dlocation (PK) ✓
Research 5 333445555 Bellaire
Research 5 333445555 Sugarland
Research 5 333445555 Houston
Administration 4 987654321 Stafford
Headquarters 1 888665555 Houston

1NF Achieved
PK is now (Dnumber, Dlocation). Every cell holds exactly one value. Note: some row-level
redundancy still exists — 2NF and 3NF will address that.
Step 2 — Second Normal Form (2NF)
Rule
Must be in 1NF + every non-prime attribute must fully depend on the WHOLE primary key — not just

part of it.

Key Concepts
Term Meaning Example
Full Dependency Attribute needs ALL parts of PK {Ssn, Pnumber} → Hours
Partial Dependency Attribute needs only PART of Ssn → Ename (only half PK)
PK
Prime Attribute Member of any candidate key Ssn, Pnumber
Non-prime Attribute NOT part of any candidate key Hours, Ename, Pname

Example — EMP_PROJ Table (Figure 14.11a)

BEFORE 2NF — Original EMP_PROJ with partial dependencies
Primary Key = {Ssn + Pnumber} (composite — both needed)

Pnumber
Ssn (PK) Hours Ename Pname Plocation
(PK)
123456789 1 32.5 Smith, John B. ProductX Bellaire
123456789 2 7.5 Smith, John B. ProductY Sugarland
666884444 3 40.0 Narayan, ProductZ Houston
Ramesh K.
 453453453 1 20.0 English, Joyce ProductX Bellaire
A.
333445555 2 10.0 Wong, ProductY Sugarland
Franklin T.

Partial Dependencies Identified (violations in red above)

FD1: {Ssn, Pnumber} → Hours ← FULL dependency (OK) FD2: Ssn → Ename ← PARTIAL (only
half the PK used) ✗ FD3: Pnumber → Pname, Plocation ← PARTIAL (only half the PK used) ✗

↓ Decompose into 3 relations — one per functional dependency

AFTER 2NF — Three separate relations

EP1 — The relationship table (Full dep: {Ssn, Pnumber} → Hours)

Ssn (PK) Pnumber (PK) Hours
123456789 1 32.5
123456789 2 7.5
666884444 3 40.0
453453453 1 20.0
333445555 2 10.0

EP2 — Employee info (Full dep: Ssn → Ename)

Ssn (PK) Ename
123456789 Smith, John B.
666884444 Narayan, Ramesh K.
453453453 English, Joyce A.
333445555 Wong, Franklin T.

EP3 — Project info (Full dep: Pnumber → Pname, Plocation)

 Pnumber (PK) Pname Plocation
1 ProductX Bellaire
2 ProductY Sugarland
3 ProductZ Houston

2NF Achieved
No partial dependencies remain. Each non-prime attribute fully depends on the entire PK of its own
relation. EP1 keeps both FK keys (Ssn and Pnumber) to JOIN with EP2 and EP3 when needed.

How JOIN Works After Decomposition

When you need all data together, SQL JOIN re-combines the tables using the FK links:

SELECT [Link], [Link], [Link],

[Link],
[Link], [Link]
FROM EP1
JOIN EP2 ON [Link] = [Link]
JOIN EP3 ON [Link] = [Link];

Each FK in EP1 calls its own table independently — Ssn calls EP2, Pnumber calls EP3.
Step 3 — Third Normal Form (3NF)
Rule
Must be in 2NF + no non-prime attribute should depend on another non-prime attribute (no transitive
dependency).

What is a Transitive Dependency?

A transitive dependency is an indirect chain: A → B → C
Where A is the PK, B is a non-key attribute, and C depends on B (not directly on A).
This is a problem only when B is NOT a candidate key.

Transitive (BAD) ✗ Non-transitive (OK) ✓

Ssn → Dnumber → Dname (Dnumber is not a Ssn → Ename (no intermediate non-key attribute)
candidate key)

Example — EMP_DEPT Table (Figure 14.11b)

BEFORE 3NF — Transitive dependency present
Primary Key = Ssn (simple, not composite)

Ename Ssn (PK) Bdate Address Dnumber Dname Dmgr_ssn

Smith 123456789 1965-01- 731 Fondren 5 Research 333445555
09
Wong 333445555 1955-12- 638 Voss 5 Research 333445555
08
Zelaya 999887777 1968-07- 3321 Castle 4 Admin 987654321
19
Narayan 666884444 1962-09- 975 FireOak 5 Research 333445555
15
 Transitive Dependency Chain (violations in red above)
Ssn → Dnumber (direct — OK) Dnumber → Dname, Dmgr_ssn (Dnumber determines dept name)
Therefore: Ssn → Dname transitively ← VIOLATION ✗ Problem: 'Research' + '333445555' repeats
for EVERY employee in dept 5. Changing the manager requires updating ALL those rows.

↓ Move the transitive part (Dnumber → Dname, Dmgr_ssn) to its own table

AFTER 3NF — Two clean relations

ED1 — Employee table (Dnumber kept as FK, not transitive data)

Ename Ssn (PK) Bdate Address Dnumber (FK)
Smith, John B. 123456789 1965-01-09 731 Fondren 5
Wong, Franklin T. 333445555 1955-12-08 638 Voss 5
Zelaya, Alicia J. 999887777 1968-07-19 3321 Castle 4
Narayan, Ramesh 666884444 1962-09-15 975 FireOak 5
K.

ED2 — Department table (transitive data now lives here)

Dnumber (PK) Dname Dmgr_ssn
5 Research 333445555
4 Administration 987654321
1 Headquarters 888665555

3NF Achieved
No transitive dependencies remain. Changing the Research department manager now requires
updating exactly ONE row in ED2 — not every employee row. Dnumber in ED1 is a foreign key
pointing to ED2.
Summary — All Three Normal Forms
NF What It Fixes Test to Pass Example Violation
1NF Non-atomic values Every cell has ONE Dlocations = {Bellaire,
value Sugarland, Houston}
2NF Partial dependencies All non-key attrs need Ssn → Ename in EMP_PROJ
WHOLE PK (only half of PK used)
3NF Transitive No non-key attr Ssn → Dnumber → Dname in
dependencies depends on another EMP_DEPT
non-key attr

Progression Diagram

1NF → 2NF → 3NF

Atomic values No partial deps No transitive deps

Final Memory Aid

1NF — All attributes depend on the key 2NF — All attributes depend on the whole key 3NF — All
attributes depend on nothing but the key

Practice question :
Properity id is a PK