Normalization
Normalization organises tables to reduce redundancy and avoid anomalies:
- Insertion anomaly: cannot add a fact without unrelated data.
- Update anomaly: one fact stored in many rows must be changed everywhere.
- Deletion anomaly: deleting a row loses unrelated facts.
Functional dependencies
X → Y means that if two rows agree on X they must agree on Y. Example: roll_no → name.
The closure X+ is the set of attributes determined by X; a candidate key is a minimal X with X+ = all attributes.
Normal forms
| Form | Rule |
|---|---|
| 1NF | Every column holds atomic values; no repeating groups |
| 2NF | 1NF and no partial dependency (non-key attribute depends on part of a composite key) |
| 3NF | 2NF and no transitive dependency (non-key → non-key) |
| BCNF | For every non-trivial X → Y, X is a super key |
| 4NF | No non-trivial multivalued dependencies |
Worked example
order(order_id, product_id, product_name, customer_id, customer_city) with key (order_id, product_id).
product_id → product_nameis a partial dependency → violates 2NF.- After splitting:
order_item(order_id, product_id)andproduct(product_id, product_name). customer_id → customer_cityin an orders table is transitive → move tocustomer(customer_id, customer_city)for 3NF.
Lossless and dependency-preserving decomposition
A good decomposition should be lossless (joining the parts gives back the original relation) and ideally dependency preserving. 3NF can always achieve both; BCNF can always be lossless but may lose dependencies.
TIP
Normalise for correctness, then denormalise deliberately (duplicate some columns) if read performance requires it.