| Lesson 3 | Why We Need Normalization |
| Objective | Explain why normalization improves reliability, reduces redundancy, and prevents anomalies. |
Database Normalization Objective
Normalization structures data so each fact is stored once, relationships are explicit, and updates remain consistent. The aim is information preservation with minimum redundancy, producing tables that are easier to query and maintain.
The purpose of normalization is to maximize logical efficiency—correctness, consistency, and maintainability—so physical performance tuning can be applied deliberately where it matters.
Motivating Example: One Wide Table
| ORDER | |||||||||
| CustID | CustName | Address | Phone | OrderDate | OrderNo | ItemNo | Title | Price | Shipped |
| 101 | C. Frye | Portland | 230.1251 | 2017-01-11 | 101 | 105 | MX | 14.95 | YES |
| 101 | C. Frye | Portland | 230.1251 | 2017-01-11 | 102 | 108 | CX | 15.95 | YES |
| 102 | D. Loge | DC | 555.1234 | 2017-01-12 | 103 | 105 | MX | 14.95 | YES |
| 102 | D. Loge | DC | 555.1234 | 2017-01-13 | 104 | 109 | DX | 13.95 | YES |
Drawbacks of a Single, Wide Table
- Mixed subjects: Customers, Orders, and Items are intermingled in one relation.
- Redundancy: Customer data repeats on every order; more storage and higher risk of inconsistent updates.
- Anomalies: Insert (can’t add a new product without an order), Update (must change the same fact in many rows), Delete (removing a last order can remove the only copy of a customer).
How Normalization Helps
- 1NF: Remove repeating groups; each column is atomic; one value per cell.
- 2NF: With composite keys, ensure non-key columns depend on the whole key (no partial dependencies).
- 3NF: Remove transitive dependencies among non-key attributes.
From UNF to 3NF (Mini Walkthrough)
-- UNF: wide "ORDER" table with repeated items per row
-- 1NF: split repeating items into separate rows
OrderLine1NF(OrderNo, OrderDate, CustID, CustName, ItemNo, Title, Price)
-- FDs (illustrative):
-- CustID → CustName
-- ItemNo → Title
-- OrderNo → OrderDate, CustID
-- (OrderNo, ItemNo) → Price
-- 2NF: remove partial dependencies from composite key (OrderNo, ItemNo)
Customer(CustID, CustName, Address, Phone)
Product(ItemNo, Title)
[Order](OrderNo, OrderDate, CustID)
OrderLine(OrderNo, ItemNo, Price)
-- 3NF: remove transitive deps (if any) among non-keys
-- e.g., if Title determines a CatalogPrice, keep that in Product;
-- keep OrderLine.Price only if capturing the actual sale price (history).
Relational Models, Integrity, and Performance
Normalization ensures correctness. Physical performance is then addressed with indexes, materialized views, caching, and schema-level options—after the logical design is sound. See also Reduce Data Redundancy and Foreign Keys.
Key Terms
- Atomic (attribute): An attribute value that is indivisible in your model’s context (e.g., a single phone number string).
- Normal Form: A level of design discipline (1NF, 2NF, 3NF, BCNF, 4NF, 5NF, DK/NF). A table in a higher NF is also in all lower NFs.
Quiz
Test yourself on the transformation steps: