Prerequisites
- You should know what a table, row, column, and primary key are.
- You should understand the concept of functional dependency.
Objectives Learning Goals & Outcomes
By the end of this activity, you will be able to:
- Identify multi-valued attributes and fix them to reach 1NF.
- Detect partial dependency using composite keys and normalize to 2NF.
- Detect transitive dependency and normalize to 3NF.
- Distinguish between partial and transitive dependency.
Concept Overview What is Normalization?
Normalization is the process of organizing a database table to reduce redundancy and avoid anomalies (insertion, update, and deletion problems). It happens in stages:
- 1NF — No multi-valued attributes. Every cell holds exactly one value.
- 2NF — No partial dependency. Every non-key attribute depends on the whole composite primary key.
- 3NF — No transitive dependency. No non-key attribute depends on another non-key attribute.
Mnemonic: 2NF = "the whole key", 3NF = "nothing but the key".
Key Distinction: Partial dependency is only possible when the primary key is composite (made of two or more columns). Transitive dependency can occur with any primary key type.
Part 1 First Normal Form (1NF) — Multi-Valued Attributes
Example: EMPLOYEE table (not in 1NF)
Notice that EMP_PHONE contains multiple values for some employees.
| EMP_ID | EMP_NAME | EMP_PHONE | EMP_PROVINCE |
|---|
| 14 | Ram | 7272826385, 9064738238 | Bagmati |
| 20 | Sita | 8574783832 | Koshi |
| 12 | Hari | 7390372389, 8589830302 | Gandaki |
Problem: EMP_PHONE has multiple values in a single cell. This violates 1NF, which requires every cell to hold a single atomic value.
Fix: Split multi-valued cells into separate rows.
EMPLOYEE (in 1NF):
| EMP_ID | EMP_NAME | EMP_PHONE | EMP_PROVINCE |
|---|
| 14 | Ram | 7272826385 | Bagmati |
| 14 | Ram | 9064738238 | Bagmati |
| 20 | Sita | 8574783832 | Koshi |
| 12 | Hari | 7390372389 | Gandaki |
| 12 | Hari | 8589830302 | Gandaki |
Exercise 1.1 — Which normal form removes multi-valued attributes?
Show answer
1NF — it ensures every cell contains only one atomic value.
Exercise 1.2 — What is the primary key of the 1NF EMPLOYEE table above?
Show answer
(EMP_ID, EMP_PHONE) — since the same EMP_ID can appear with different phone numbers, both columns are needed to uniquely identify each row.
Part 2 Second Normal Form (2NF) — Partial Dependency
Example: TEACHER table (not in 2NF)
The primary key is (TEACHER_ID, SUBJECT) — a composite key.
| TEACHER_ID | SUBJECT | TEACHER_AGE |
|---|
| 25 | Chemistry | 30 |
| 25 | Biology | 30 |
| 47 | English | 35 |
| 83 | Math | 38 |
| 83 | Computer | 38 |
Problem: TEACHER_AGE depends only on TEACHER_ID, not on SUBJECT. Since the primary key is composite, this is a partial dependency.
Partial dependency means a non-key attribute depends on only PART of a composite primary key. It is only possible when the PK has two or more columns.
Fix: Split into two tables.
TEACHER_DETAIL: (TEACHER_ID, TEACHER_AGE)
TEACHER_SUBJECT: (TEACHER_ID, SUBJECT)
Now TEACHER_AGE depends on the entire key of its own table (just TEACHER_ID). No partial dependency remains.
Exercise 2.1 — Partial dependency is only possible when the primary key is:
Show answer
Composite — partial dependency means depending on only part of the key, which requires a key made of multiple columns.
Exercise 2.2 — In the TEACHER table, TEACHER_AGE depends on:
Show answer
TEACHER_ID only — the age does not change with the subject, so it depends only on part of the composite key.
Part 3 Third Normal Form (3NF) — Transitive Dependency
Example: EMPLOYEE_DETAIL table (not in 3NF)
The primary key is EMP_ID (a single column).
| EMP_ID | EMP_NAME | EMP_ZIP | EMP_PROVINCE | EMP_CITY |
|---|
| 222 | Hari | 44600 | Bagmati | Kathmandu |
| 333 | Sita | 44200 | Bagmati | Bharatpur |
| 444 | Lakhan | 56700 | Koshi | Dharan |
| 555 | Krishna | 33700 | Gandaki | Pokhara |
| 666 | Jivan | 22400 | Lumbini | Ghorahi |
Problem: EMP_PROVINCE and EMP_CITY depend on EMP_ZIP, not directly on EMP_ID. Since EMP_ZIP is a non-key column, this is a transitive dependency: EMP_ID → EMP_ZIP → EMP_PROVINCE.
Transitive dependency means a non-key attribute depends on another non-key attribute. This can happen with any primary key type — single or composite.
Fix: Split into two tables.
EMPLOYEE: (EMP_ID, EMP_NAME, EMP_ZIP)
EMPLOYEE_ZIP: (EMP_ZIP, EMP_PROVINCE, EMP_CITY)
Now EMP_PROVINCE and EMP_CITY depend on EMP_ZIP, which is the primary key of its own table. No transitive dependency remains.
Exercise 3.1 — EMP_PROVINCE depends on EMP_ZIP. This is an example of:
Show answer
Transitive dependency — EMP_PROVINCE depends on EMP_ZIP, which is a non-key attribute, creating a chain: EMP_ID → EMP_ZIP → EMP_PROVINCE.
Exercise 3.2 — 3NF removes _____ dependency.
Show answer
Transitive — 3NF specifically targets transitive dependencies (non-key → non-key).
Part 4 Normalization Anomalies — Matching Exercise
Match each anomaly to its description
Update Anomaly
Changing one fact requires updating multiple rows
Insertion Anomaly
Cannot add data without unrelated data
Deletion Anomaly
Deleting a row unintentionally loses unrelated data
Show answer
Update Anomaly = changing one fact requires updating multiple rows. Insertion Anomaly = cannot add data without unrelated data. Deletion Anomaly = deleting a row unintentionally loses unrelated data.
Exercise 4.1 — In the EMPLOYEE table (1NF), if Ram changes his phone number, how many rows must be updated?
Show answer
2 — Ram has two rows in the 1NF table (one for each phone number), so both must be updated.
Challenge Quiz Test Your Understanding
1. Arrange the normal forms in the correct order:
3NF
1NF
2NF
Show answer
1NF → 2NF → 3NF (first fix multi-valued attributes, then partial dependencies, then transitive dependencies).
2. A relation is in 2NF if it is in 1NF and all non-key attributes are fully functionally dependent on the _____.
Show answer
Primary key — 2NF requires full functional dependency on the entire primary key.
3. Arrange the dependency chain in the EMPLOYEE_DETAIL example:
EMP_PROVINCE
EMP_ID
EMP_ZIP
Show answer
EMP_ID → EMP_ZIP → EMP_PROVINCE (the transitive chain).
4. Which normal form requires that every non-key attribute depends on "the whole key and nothing but the key"?
Show answer
3NF — "the key" = primary key (2NF part), "nothing but the key" = no transitive dependency (3NF part). Together they describe 3NF.
5. Match each normal form to what it removes:
1NF
Transitive dependency
2NF
Multi-valued attributes
3NF
Partial dependency
Show answer
1NF removes multi-valued attributes. 2NF removes partial dependency. 3NF removes transitive dependency.
6. Why is the EMPLOYEE table in Part 1 not in 1NF?
Show answer
EMP_PHONE has multiple values in one cell (e.g., "7272826385, 9064738238"), which violates 1NF.
7. In the TEACHER table, what type of dependency does TEACHER_AGE on TEACHER_ID represent?
Show answer
Partial dependency — TEACHER_AGE depends on only part of the composite key (TEACHER_ID), not on the whole key.
8. After normalizing the EMPLOYEE table from 1NF to remove redundancy, what should be the primary key of the TEACHER_SUBJECT table?
Show answer
(TEACHER_ID, SUBJECT) — both columns together are needed to uniquely identify each row in the TEACHER_SUBJECT table.
9. Which of the following is TRUE about transitive dependency?
Show answer
It can occur with any type of primary key — transitive dependency is a chain between non-key attributes regardless of PK type.
10. In the EMPLOYEE_DETAIL table, which columns form the transitive chain?
Show answer
EMP_ID → EMP_ZIP → EMP_PROVINCE — EMP_ZIP is a non-key attribute that determines EMP_PROVINCE, creating the transitive chain.