学習目標
正規化は更新時の矛盾を減らすデータ設計、トランザクションは複数の操作を安全に確定する実行制御である。表を分ける話と、操作を一まとめにする話を混同しない。
関数従属性を先に書く
1行を1注文明細とする表を考える。属性は注文番号、商品番号、顧客番号、顧客名、商品名、数量。仮定として、1注文は1顧客、同一注文内に同じ商品は1行だけ、商品名は商品番号で一意に決まる。このとき候補キーは「注文番号＋商品番号」。注文番号→顧客番号、顧客番号→顧客名、商品番号→商品名が成り立つ。
| 段階 | この例で除く問題 | 分けるもの |
|---|---|---|
| 第1正規形 | 1セルに商品の繰返しを詰め込む | 1行を1明細にする |
| 第2正規形 | 複合キーの一部に非キー属性が依存 | 注文情報と商品情報を明細から分離 |
| 第3正規形 | キーから非キー属性への推移的な依存 | 顧客番号→顧客名を顧客表へ分離 |
この表は例の解き方であり、全ての候補キーを考える厳密な定義の代用ではない。第3正規形の形式的条件は、非自明な関数従属性X→Aについて、Xがスーパーキーであるか、Aがいずれかの候補キーの構成属性であること。BCNFは全ての非自明な従属性でXがスーパーキーであることを求める。単に「主キーが1項目なら正規化済み」とは判断しない。
解法例
顧客名を注文表に残すと、同じ顧客の改名を注文ごとに更新する必要が生じる。顧客表へ分ければ現在の顧客名の管理を一か所にできる。一方、「注文時の販売単価」は現在の商品単価とは別の事実なので、注文明細に保存する設計が必要になることがある。値が同じに見えることと、同じ意味の属性であることは別である。Microsoftの正規化入門
ACIDは何を守るか
| 性質 | 着眼点 | 在庫引当と注文登録の例 |
|---|---|---|
| 原子性 Atomicity | 全部実行か全部取り消しか | 在庫だけ減らして注文がない状態を防ぐ |
| 一貫性 Consistency | 定めた制約を守るか | 数量非負などの整合性条件 |
| 独立性 Isolation | 並行実行の干渉をどう制御するか | 同じ在庫への同時注文 |
| 永続性 Durability | 確定した結果を障害後も維持するか | 確定済みの注文を復旧できる |
一貫性はDBMSが業務の意味を自動理解するという意味ではない。正しい制約、処理、隔離方式の設計が必要。PostgreSQLのトランザクション説明
隔離レベルの読み方
| SQL標準のレベル | ダーティリード | 非反復読取り | ファントム |
|---|---|---|---|
| Read Uncommitted | 許容 | 許容 | 許容 |
| Read Committed | 禁止 | 許容 | 許容 |
| Repeatable Read | 禁止 | 禁止 | 許容 |
| Serializable | 禁止 | 禁止 | 禁止 |
ダーティリードは未確定値を読むこと、非反復読取りは同じ行の再読で確定値が変わること、ファントムは同じ検索条件に合う行集合が変わること。表の「許容」は必ず発生する意味ではない。PostgreSQLではRead UncommittedもRead Committedとして動き、Repeatable Readでファントムを防ぐなど、実装は最低保証より強い場合がある。Serializableでは直列実行と整合する結果を保証するため、トランザクションが失敗して再試行を要することもある。PostgreSQL 18の隔離レベル
間違えやすい点と確認
- 正規化を進めれば、全ての検索が速くなるわけではない。更新整合性と検索性能を分けて検討する。
- 外部キーと主キーは同義でない。外部キーは参照先との整合性のために使う。
- 「未確定の在庫を読んだ」現象は？ → ダーティリード。
- Read Committedなら業務上の競合を全て防げるか？ → 防げない。条件付き更新、ロック、再試行などを設計する。
出典と適用上の注意
データモデルと例は独自作成。標準モデルを問う設問と、特定DBMSの動作を問う設問を区別する。SQLやロックの細部は対象製品・バージョンの仕様で判断する。