16 - Normalization: 1NF, 2NF, 3NF

🎯 Learning Objectives

• Identify insertion, deletion, and update anomalies
• Define and apply 1NF, 2NF, and 3NF
• Distinguish between prime and non-prime attributes
• Detect partial dependencies and transitive dependencies
• Normalize a relation up to 3NF

📋 Prerequisites

• 14 - Functional Dependencies — FDs, attribute closure, candidate keys
• 15 - Canonical Cover — Minimal FD set

📖 Core Content

16.1 Intuition: Why Normalize?

1. Update anomaly: If Alice changes departments, we must update EVERY row with emp_id=101
2. Insertion anomaly: Can't add a new department without assigning an employee
3. Deletion anomaly: If Bob leaves, we lose info about Project Gamma Normalization is the process of decomposing tables to eliminate these anomalies. It's a series of tests (normal forms) that a schema can pass or fail.

Why This Matters: Normalization is the core of good database design (~13% of exam questions). A non-normalized database leads to data inconsistencies and maintenance nightmares.

16.2 Anomalies in Detail

16.3 The Normal Forms Hierarchy

16.4 Key Terminology

16.5 First Normal Form (1NF)

16.6 Second Normal Form (2NF)

1. It is in 1NF
2. No non-prime attribute is partially dependent on any candidate key Check: For every candidate key CK and every proper subset S ⊂ CK, there is no FD S → A where A is non-prime.

Worked Example: 2NF Check

• $A \rightarrow D$: A ⊂ CK {A, B}, and D is non-prime. This is a partial dependency!
• $AB \rightarrow C$: LHS = full CK, not a subset. Not partial. Since $A \rightarrow D$ is a partial dependency, R is NOT in 2NF. Fix: Decompose R into:
• $R_1(A, D)$ — covers the partial dependency
• $R_2(A, B, C)$ — the remaining attributes with the original CK Now both $R_1$ and $R_2$ are in 2NF.

Example 2: 2NF Check

• $B \rightarrow D$: B ⊂ CK {A, B}, D is non-prime. Partial dependency!
• R is NOT in 2NF.

16.7 Third Normal Form (3NF)

1. It is in 2NF
2. No non-prime attribute is transitively dependent on any candidate key OR: For every non-trivial FD $X \rightarrow A$, either:
   • $X$ is a superkey, OR
   • $A$ is a prime attribute (part of some candidate key) Alternative definition: A relation is in 3NF if for every non-trivial FD $X \rightarrow A$:

Worked Example: 3NF Check

• $A \rightarrow B$: A is not a superkey ($A^+$ = {A,B,C} ≠ R). B is non-prime. Violation of 3NF!
• $B \rightarrow C$: B is not a superkey ($B^+$ = {B,C} ≠ R). C is non-prime. Violation! R is NOT in 3NF. Fix: Decompose into:
• $R_1(A, B, C)$ — covers the FDs (PK = A)
• $R_2(A, D)$ — has the original CK Now check 3NF for the decomposition:
• $R_1(A, B, C)$ with $F = \{A \rightarrow B, B \rightarrow C\}$:
  • CK = {A}.
  • $A \rightarrow B$: A is a superkey. ✓
  • $B \rightarrow C$: B is not a superkey, C is non-prime. Still violates 3NF! Need to further decompose $R_1$:
  • $R_{11}(A, B)$ — covers $A \rightarrow B$
  • $R_{12}(B, C)$ — covers $B \rightarrow C$

Example 2: 3NF Check

• $A \rightarrow B$: A is a superkey. ✓
• $B \rightarrow C$: B is NOT a superkey ($B^+$ = {B,C} ≠ R). C is non-prime. Violation! Not in 3NF. Decompose to $R_1(A, B)$ and $R_2(B, C)$.

Example 3: 3NF with Prime Attribute on RHS

• $AB \rightarrow C$: AB is a superkey. ✓
• $C \rightarrow A$: C is not a superkey ($C^+$ = {A,C} ≠ R). BUT A is a prime attribute (part of {A,B}). This is allowed in 3NF! So R IS in 3NF (but NOT in BCNF — that's the distinction we'll see later).

16.8 Normalization: Step-by-Step Process

16.9 Worked Example: Complete Normalization

• Attributes not on RHS: A, B (both appear on LHS only)
• $\{A, B\}^+$: {A,B} → $AB \rightarrow C$ → {A,B,C} → $C \rightarrow E$ → {A,B,C,E} → $A \rightarrow D$ → {A,B,C,D,E} = R
• CK = {A, B}. Prime: A, B. Non-prime: C, D, E. Step 2: Check 2NF.
• $A \rightarrow D$: A ⊂ CK, D non-prime. Partial dependency!
• Not in 2NF. Step 3: Decompose to 2NF.
• $R_1(A, D)$ — covers $A \rightarrow D$
• $R_2(A, B, C, E)$ — remaining Step 4: Check $R_2$ for 2NF. CK of $R_2$: {A, B}. FDs in $R_2$: $AB \rightarrow C$, $C \rightarrow E$.
• $AB \rightarrow C$: Full CK. Not partial. ✓
• $C \rightarrow E$: C is not a subset of CK. Not partial. ✓ No partial dependencies. $R_2$ is in 2NF. Step 5: Check $R_2$ for 3NF. CK = {A, B}. Prime: A, B. Non-prime: C, E.
• $AB \rightarrow C$: AB is a superkey. ✓
• $C \rightarrow E$: C is NOT a superkey ($C^+$ = {C,E} in $R_2$). E is non-prime. Transitive dependency! Step 6: Decompose $R_2$ to 3NF.
• $R_{21}(C, E)$ — covers $C \rightarrow E$
• $R_{22}(A, B, C)$ — remaining Final 3NF decomposition: $R_1(A, D)$, $R_{21}(C, E)$, $R_{22}(A, B, C)$ All three relations are in 3NF (and also in 2NF and 1NF).

📐 Key Formulas / Concepts

⚠️ Common Pitfalls

Pitfall 1: Checking 2NF Against Only the Primary Key

Pitfall 2: Confusing 3NF and BCNF

Pitfall 3: Not Identifying All Candidate Keys First

📝 Practice Questions

Q1. Define the three types of anomalies that normalization addresses.

Answer

1. Insertion anomaly: Cannot insert a new row without providing unrelated data (e.g., can't add a new department without assigning an employee)
2. Deletion anomaly: Deleting a row deletes unintended information (e.g., deleting an employee also deletes the department info)
3. Update anomaly: Changing a value requires updating multiple rows (e.g., changing a department name requires updating every employee row)

Q2. What is 1NF? Give an example of a violation.

Answer

1NF requires all attributes to have atomic (indivisible) values.

Violation: A table where an attribute can contain multiple values:

Fix: Create separate rows for each course:

Q3. What is a partial dependency? When does it violate 2NF?

Answer

A partial dependency occurs when a non-prime attribute depends on only part of a candidate key (a proper subset).

Violation: In $R(A, B, C, D)$ with CK = {A, B} and FD $A \rightarrow D$:

• D is non-prime, A is a proper subset of CK
• This is a partial dependency → R is NOT in 2NF

Fix: Decompose into $R_1(A, D)$ and $R_2(A, B, C)$.

Q4. Check if $R(A, B, C)$ with $F = \{A \rightarrow B, B \rightarrow C\}$ is in 3NF.

Answer

Candidate key: {A}

Prime attributes: A. Non-prime: B, C.

Check 2NF: CK = {A} has no proper subset, so no partial dependency. ✓

Check 3NF:

• $A \rightarrow B$: A is a superkey. ✓
• $B \rightarrow C$: B is NOT a superkey ($B^+$ = {B,C}). C is non-prime. Violation!

R is in 2NF but NOT in 3NF. Decompose to $R_1(A, B)$ and $R_2(B, C)$.

Q5. Is $R(A, B, C)$ with $F = \{AB \rightarrow C, C \rightarrow B\}$ in 3NF?

Answer

Find candidate keys:

• $\{A, B\}^+$ = {A,B,C} = R. So {A,B} is a CK.
• $\{A, C\}^+$: {A,C} → $C \rightarrow B$ → {A,B,C} = R. So {A,C} is also a CK.

Prime: A, B, C (all are prime — every attribute is in some candidate key).

Check 3NF (alternative definition):

• $AB \rightarrow C$: AB is a superkey. ✓
• $C \rightarrow B$: C is not a superkey ($C^+$ = {B,C} ≠ R). BUT B is a prime attribute. ✓

R IS in 3NF (but NOT in BCNF, as we'll see later).

Q6. What is the difference between prime and non-prime attributes?

Answer

• Prime attribute: An attribute that is part of ANY candidate key for the relation
• Non-prime attribute: An attribute that is NOT part of any candidate key

Example: In $R(A, B, C, D)$ with candidate key {A, B}:

• Prime: A, B
• Non-prime: C, D

Q7. Normalize $R(A, B, C, D)$ with $F = \{A \rightarrow B, AB \rightarrow C, B \rightarrow D\}$ to 3NF.

Answer

Step 1: Find candidate keys.

• Attributes not on RHS: A (appears only on LHS)
• $\{A\}^+$: {A} → $A \rightarrow B$ → {A,B} → $B \rightarrow D$ → {A,B,D} → $AB \rightarrow C$ → {A,B,C,D} = R
• CK = {A}. Prime: A. Non-prime: B, C, D.

Step 2: Check 2NF (no partial dependency since CK is single attribute). ✓

Step 3: Check 3NF.

• $A \rightarrow B$: A is a superkey. ✓
• $AB \rightarrow C$: AB is a superkey (contains A). ✓
• $B \rightarrow D$: B is NOT a superkey ($B^+$ = {B,D}). D is non-prime. Transitive dependency!

Step 4: Decompose.

• $R_1(B, D)$ — covers $B \rightarrow D$
• $R_2(A, B, C)$ — remaining (covers $A \rightarrow B$ and $AB \rightarrow C$)

Final in 3NF: $R_1(B, D), R_2(A, B, C)$

Q8. Explain transitive dependency with an example.

Answer

A transitive dependency occurs when a non-prime attribute depends on another non-prime attribute through a chain of FDs.

Example: In $R(A, B, C)$ with $F = \{A \rightarrow B, B \rightarrow C\}$, CK = {A}:

• $A \rightarrow B$: fine (A is superkey)
• $B \rightarrow C$: B → C where B is non-prime and C is non-prime
• This forms a transitive dependency: $A \rightarrow B \rightarrow C$

To break it, decompose into $R_1(A, B)$ and $R_2(B, C)$.

🔗 Cross-References

• Next Topic: 17 - BCNF & Higher Normal Forms
• Previous Topic: 15 - Canonical Cover
• Related: 18 - Decomposition (how to split relations properly)
• Related: 12 - ER Model (good ER design leads to normalized relations)
• Textbook: Silberschatz, Korth, Sudarshan — Chapter 8 (Relational Database Design) Join Discord Previous15 - Canonical CoverNext17 - BCNF & Higher Normal Forms