正規化とデータモデル変更｜関数従属・サブタイプ・制約・DDLをつなぐ

表に顧客名を繰り返し保存すると、一部の行だけ変更されて名前が食い違うことがあります。列を減らすだけでなく、どの事実が何で一意に決まるかを考えるのが正規化です。追加要件に対応するときも、業務上の一意性、履歴を残す意味、制約を適用する場所を分けて設計します。

この記事で理解すること

• 関数従属と候補キーから、挿入・更新・削除の異常を説明する。

• 1NF・2NF・3NFと無損失な分解を、受注明細の例に適用する。

• スーパータイプとサブタイプの制約、DDLの表現範囲、既存データの移行手順を判断する。

E-R図の記号と主キー・外部キーの基礎は関連の概念データモデル記事で扱います。ここでは業務ルールの変更が表の構造と整合性に及ぼす影響を学びます。教材SQLはSQLiteで検証し、内部のIDはすべて整数です。

関数従属は、たまたま一致した値ではない

架空の販売会社では一注文が一顧客に属し、明細番号は注文内で一意です。顧客には現在の名称が一つ、商品には現在の名称が一つあります。以下は名称と受注時単価を一つの表へ置いた状態です。

関数従属X→Yは、Xの値が同じならYも同じになるというルールです。この例では注文ID→顧客ID、顧客ID→現在の顧客名、商品ID→現在の商品名が成り立ちます。数量と受注時単価は(注文ID,明細番号)で決まります。同じ商品でも注文ごとの単価は異なります。

顧客名が全行同じだから「名前→顧客ID」と逆に決めてはいけません。同名顧客を認めるルールなら名前はキーになりません。候補キーは全属性を一意に決める最小の属性集合です。表の小さなサンプルで重複がないだけでは、その属性が候補キーである証明になりません。

更新異常と1NF：一つの事実を適切に置く

更新異常は名称の変更を複数行へ反映し損ねること、挿入異常は注文がない顧客や商品を登録しにくいこと、削除異常は最後の明細を消すと必要な顧客情報まで失うことです。事実の保存先が業務上の独立した存在と合っていないことが原因になります。

1NFは、関係の各属性値をそのドメインで一つの値として扱い、繰返しの組を持たせない基本形です。商品ID1・商品ID2・商品ID3と列を増やしたり、一セルへ複数商品のリストを詰めたりする設計を明細行へ分けます。「数字だけにする」「全文を一文字ずつ分ける」という意味ではありません。

2NFと3NF：キーの一部と推移を見分ける

明細の候補キーを(注文ID,明細番号)とします。注文IDだけで決まる顧客IDはキーの一部への部分関数従属です。2NFでは、1NFを満たし、非キー属性が候補キーの一部だけへ従属しないようにします。注文の事実を注文表へ分けるのが自然です。

注文ID→顧客ID→顧客名という推移的な従属もあります。3NFでは非キー属性同士を介したこのような従属を分離します。厳密には非自明なX→Aについて、Xがスーパーキー、またはAが候補キーの構成属性であることが条件です。一般的な受注例では、顧客名を顧客表へ分ける説明で理解できます。

採番したidを主キーに追加しても、業務上の候補キーや従属は消えません。「主キーが単一列なので正規化完了」と結論しないようにします。明細idとは別にUNIQUE(order_id,line_no)を置き、注文内の番号という業務上一意な条件を守ります。

分解後の表と、履歴価格を残す意味

顧客・商品・注文・明細へ分解し、参照列で結びます。注文と明細を注文IDで結合すれば、元の注文ごとの明細を復元できます。分けた表を結合したとき、元になかった組合せを生む分解は適切ではありません。無損失結合は元の関係を過不足なく戻せるという意味です。

CREATE TABLE customer (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL
);
CREATE TABLE product (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER NOT NULL REFERENCES customer(id),
  ordered_on TEXT NOT NULL
);
CREATE TABLE order_line (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id INTEGER NOT NULL REFERENCES orders(id),
  line_no INTEGER NOT NULL CHECK(line_no > 0),
  product_id INTEGER NOT NULL REFERENCES product(id),
  quantity INTEGER NOT NULL CHECK(quantity > 0),
  unit_price INTEGER NOT NULL CHECK(unit_price >= 0),
  UNIQUE(order_id, line_no)
);

受注時単価は商品の現在価格ではありません。明細から単価を消して商品マスタの現在価格だけを参照すると、値上げ後に過去の請求額が変わってしまいます。時点の違う事実を別に保存することは、同じ現在情報を無意味に重複することとは違います。

顧客名も「今の宛名」なら顧客表を参照できますが、「発行時の請求書に印字した名前」を再現する要件なら、スナップショットや版・有効期間を持つ履歴が必要です。正規化という語を理由に、異なる時点の事実を同じ属性へまとめてはいけません。

制約：NULLと参照先の条件を読む

NOT NULLは値の未指定を禁止し、CHECKはその行の条件、UNIQUEは指定列の組合せの一意性、FOREIGN KEYは参照整合性を守ります。CHECK(quantity > 0)だけではSQLiteやPostgreSQLでNULLを拒否できないため、必須ならNOT NULLを併用します。

一意制約でNULLをどう扱うかはDBMSと設定を確認します。参照先の削除には禁止・連鎖削除・NULL化等がありますが、請求記録まで連鎖削除する設計は保存要件と衝突し得ます。SQLiteで外部キーを検証する教材では、接続ごとにPRAGMA foreign_keys = ONを設定します。

スーパータイプとサブタイプ：共通属性と固有属性

会員を個人と法人へ広げる場合、会員ID・名前などの共通情報をmember、法人番号をcompany_memberへ分ける設計があります。子のIDは新しい別人のIDではなく、親と同じ会員の整数IDを使います。個人専用属性も同様に別表へ置けます。

CREATE TABLE member (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  kind TEXT NOT NULL CHECK(kind IN ('person', 'company')),
  UNIQUE(id, kind)
);
CREATE TABLE company_member (
  id INTEGER PRIMARY KEY,
  kind TEXT NOT NULL DEFAULT 'company'
    CHECK(kind = 'company'),
  company_no TEXT NOT NULL UNIQUE,
  FOREIGN KEY(id, kind) REFERENCES member(id, kind)
);

この例の複合外部キーは、法人子のkindがcompanyであることと、親の同じIDのkindもcompanyであることを結びます。単なるid外部キーだけでは、personの親に法人子を作れるので区分の整合性が足りません。kindが文字列でも内部IDを文字列へ変える必要はありません。

排他的なら一会員はどれか一種、重複を許すなら複数種に属せます。全域なら必ず何かの子に属し、部分的なら共通情報だけの会員も認めます。このDDLだけでは全法人親に法人子が必ずあることを強制しません。親子を同一トランザクションで登録する処理や適切なDBの制約で補います。

モデル変更は、既存データとアプリも移す

まず追加要件、候補キー、NULLの意味、削除と履歴の規則を決めます。次に既存データの欠落・重複を調べ、表・列を追加して変換します。全件の件数だけでなくキー対応、数量・金額、孤立した参照の有無を照合してから、新しい読書きへ切り替えます。

必須列へ根拠のない一律値を埋めて検証を通すと、制約が成立しても意味が誤ります。移行中の二重書込みや旧画面からの更新も考え、戻せる段階とバックアップを定めます。性能のための意図的な非正規化では、更新責任、再計算、整合性検査を具体化します。

演習1：部分関数従属

条件：明細の候補キーは(注文ID,明細番号)。注文IDだけで顧客IDが決まる。

問い：何をどの表へ分けるか。

解答例：注文IDと顧客ID等を注文表へ分ける。

根拠と誤答の確認：顧客IDは明細キーの一部に従属します。

演習2：採番と業務キー

条件：明細へ単一のidを追加した。注文内の明細番号は一意という規則がある。

問い：番号重複を防ぐ制約を答える。

解答例：UNIQUE(order_id,line_no)。

根拠と誤答の確認：採番IDの一意性だけでは注文内の明細番号の重複を防げません。

演習3：履歴の単価

条件：id501の現在価格を30000から32000へ変える。過去の受注単価は28000だった。

問い：過去請求の計算に何を使うか。

解答例：明細に保存した受注時単価28000。

根拠と誤答の確認：現在価格と過去の契約条件は別の事実です。

演習4：CHECKとNULL

条件：数量列にはCHECK(quantity > 0)だけがある。

問い：NULLを必ず禁止できるか。

解答例：できない。必須ならNOT NULLも指定する。

根拠と誤答の確認：CHECKの評価が不明になるNULLと、偽になる0以下を区別します。

演習5：サブタイプの整合

条件：親のkind=personなのに同じIDの法人子を登録しようとする。

問い：掲載の複合外部キーは何を守るか。

解答例：親子IDとkindの対応を守り、法人子を個人親へ付ける登録を拒否する。

根拠と誤答の確認：親の存在だけを確かめるid外部キーより条件が強くなります。

演習6：全域の条件

条件：全会員は必ず個人または法人の子を持つ要件がある。親への外部キーだけがある。

問い：要件は十分に強制できているか。

解答例：できていない。子が親を参照できても、全親に子が存在することは保証しない。

根拠と誤答の確認：参照の向きと、最低一件を必要とする側を確認します。

参照資料とこの記事の範囲

事例・数値・図・演習は独自に作成した教材です。用語の範囲はIPAシラバス、仕組みは以下の一次資料で確認しました。特定年度の問題を読んでいなくても学べます。製品固有の動作と一般的な原理は本文で区別します。

IPA APシラバス

Microsoft：データベース正規化

PostgreSQL 18：制約

SQLite：外部キー

関連テーマを続けて学ぶ

E-R図とキー｜業務ルールからエンティティ・関連・多対多を設計する

SQLの結合と集計｜JOIN・NULL・副問合せを結果表から理解する

応用SQLと索引｜ウィンドウ関数・再帰問合せ・実行効率を読む

DBトランザクション｜ACID・分離レベル・ロック・障害回復を追う