Activity: Database Normalization

Understanding 1NF, 2NF, and 3NF through real-world examples

Grade XII • Computer Science ⏱️ ~25 min

Brief Intro — Database Normalization

Normalization is the art of organizing a database to eliminate redundant data and prevent errors when adding, updating, or deleting information. By following a set of strict "Normal Forms," you ensure your data remains consistent and your database stays efficient as it grows.

In this activity, you'll learn how to transform messy tables into clean, professional structures using 1NF, 2NF, and 3NF.

Prerequisites

Objectives Learning Goals & Outcomes

By the end of this activity, you will be able to:

Concept Overview What is Normalization?

At its core, normalization is about reducing data redundancy to make databases easier to maintain and less prone to errors.

Normalization is the process of organizing a database table to reduce redundancy and avoid anomalies (insertion, update, and deletion problems). It happens in stages:

  1. 1NF — No multi-valued attributes. Every cell holds exactly one value.
  2. 2NF — No partial dependency. Every non-key attribute depends on the whole composite primary key.
  3. 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

The first step of normalization ensures that every cell in a table holds only a single, atomic piece of information.

Example: EMPLOYEE table (not in 1NF)

Notice that EMP_PHONE contains multiple values for some employees.

EMP_IDEMP_NAMEEMP_PHONEEMP_PROVINCE
14Ram7272826385, 9064738238Bagmati
20Sita8574783832Koshi
12Hari7390372389, 8589830302Gandaki

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_IDEMP_NAMEEMP_PHONEEMP_PROVINCE
14Ram7272826385Bagmati
14Ram9064738238Bagmati
20Sita8574783832Koshi
12Hari7390372389Gandaki
12Hari8589830302Gandaki

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

Reaching 2NF requires removing partial dependencies, ensuring that every piece of data depends on the entire primary key.

Example: TEACHER table (not in 2NF)

The primary key is (TEACHER_ID, SUBJECT) — a composite key.

TEACHER_IDSUBJECTTEACHER_AGE
25Chemistry30
25Biology30
47English35
83Math38
83Computer38

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

3NF takes organization further by eliminating transitive dependencies, ensuring no data depends on other non-key columns.

Example: EMPLOYEE_DETAIL table (not in 3NF)

The primary key is EMP_ID (a single column).

EMP_IDEMP_NAMEEMP_ZIPEMP_PROVINCEEMP_CITY
222Hari44600BagmatiKathmandu
333Sita44200BagmatiBharatpur
444Lakhan56700KoshiDharan
555Krishna33700GandakiPokhara
666Jivan22400LumbiniGhorahi

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

Normalization solves three critical data anomalies — insertion, update, and deletion — that can otherwise corrupt your database information.

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.

Completion Checklist:

  • Understood multi-valued attributes and how 1NF fixes them
  • Understood partial dependency and how 2NF fixes it
  • Understood transitive dependency and how 3NF fixes it
  • Can distinguish between partial and transitive dependency
  • Completed the challenge quiz

Ready to test your knowledge?

Take the Assessment →