Data Modeling & Schema Design

Higher Normal Forms and Decomposition

Beyond BCNF, multivalued and join dependencies expose relations that combine independent many-valued facts. Decomposition is useful only when joins reconstruct exactly the legal information and important constraints remain enforceable.

A lossless decomposition reconstructs exactly the original relation.
Dependency preservation asks whether constraints can be checked without joining decomposed relations.
Fourth normal form separates independent multivalued facts.
Fifth normal form addresses nontrivial join dependencies not implied by keys.
3NF synthesis favors dependency preservation, while BCNF decomposition may sacrifice it for stronger redundancy control.
A join that happens to reproduce one sample is not proof of losslessness for every legal instance.

Lossless versus lossy decomposition

Original `PRODUCT_CATEGORY(product_id, category_id, category_label)`
product_id	category_id	category_label
P1	C10	Shoes
P2	C11	Shoes

Two decompositions and their joins
Decomposition	Shared determinant	Join result
`PRODUCT_CATEGORY(product_id, category_id)` + `CATEGORY(category_id, category_label)`	`category_id → category_label`	Lossless: each category ID restores its one label
`PRODUCT_LABEL(product_id, category_label)` + `CATEGORY_LABEL(category_id, category_label)`	`category_label` is not unique	Lossy when multiple categories share a label; product/category combinations are invented

Lossy projections and their natural join on `category_label`
Stage	product_id	category_id	category_label	Status
`PRODUCT_LABEL` projection	P1	—	Shoes	Projected original tuple
`PRODUCT_LABEL` projection	P2	—	Shoes	Projected original tuple
`CATEGORY_LABEL` projection	—	C10	Shoes	Projected original tuple
`CATEGORY_LABEL` projection	—	C11	Shoes	Projected original tuple
Natural join	P1	C10	Shoes	Original tuple
Natural join	P1	C11	Shoes	Spurious tuple
Natural join	P2	C10	Shoes	Spurious tuple
Natural join	P2	C11	Shoes	Original tuple

Separating two independent multivalued facts

When category and supplier choices are independent, 4NF stores each relationship once instead of materializing their cross-product.