Database Design: Logical and Physical Design, Normalization
Domain 2 - Database design (logical and physical design, normalization)
The exam tests 論理設計 - normalizing an unnormalized relation to 3NF/BCNF - and 物理設計 - tablespaces, blocks, partitioning, indexes and storage. You must find 関数従属 and prove which normal form a relation violates.
Core rules
- 1NF: atomic values, no repeating groups.
- 2NF: 1NF plus no 部分関数従属 - no non-key attribute depends on part of a composite key.
- 3NF: 2NF plus no 推移的関数従属 - no non-key attribute depends on another non-key attribute.
- BCNF: every determinant is a candidate key.
- 4NF and 5NF address 多値従属 and 結合従属; recognize them, but they are rarely the answer.
- Physical design chooses データ型, 主キー, インデックス, パーティション and placement; 非正規化 only with a measured performance reason.
- 自然キー vs 代理キー: a surrogate key does not remove the need for a unique constraint on the natural key.
Worked example
受注明細(受注番号, 商品コード, 商品名, 単価, 数量), key = (受注番号, 商品コード). 商品名 and 単価 depend on 商品コード alone - a partial dependency, so 2NF is violated. Split into 受注明細(受注番号, 商品コード, 数量) and 商品(商品コード, 商品名, 単価). If 商品 also stores 分類コード and 分類名, then 分類名 depends on 分類コード, a transitive dependency violating 3NF; split into 商品(商品コード, 商品名, 単価, 分類コード) and 分類(分類コード, 分類名).
Traps
Claiming a 2NF violation when the key is a single attribute; calling a relation 3NF while a transitive dependency remains; over-normalizing until every query needs many joins; assuming a surrogate key fixes duplicate natural keys; and ignoring NULL behavior in UNIQUE constraints.