分ける前に、何が一つに決まるかを書く
注文の表へ注文番号、商品番号、顧客番号、現在住所、商品の現在価格、個数をまとめたとします。同じ顧客の住所が多数の行へ繰り返されると、住所変更のたびに全てを直す必要があります。一行だけ直すと、同じ顧客に二つの現在住所がある状態になります。正規化は表を何となく細かくする手順ではなく、どの属性から何が一つに決まるかという関数従属性へ基づき、更新の重複を考える方法です。
今回の説明では、注文番号から顧客番号、顧客番号から現在住所、商品番号から現在価格、注文番号と商品番号の組から個数が決まると仮定します。一注文に同じ商品が複数の独立した明細として現れる仕様なら、この組は明細を識別しない場合があります。その場合は行番号など別の属性が必要です。見えている数行だけで業務全体の従属性が証明できたとは考えず、契約として成立する前提を書きます。
現在価格と、注文時の価格を混同しない
商品番号から価格が決まるという仮定は、「今の一つの価格」を表す場合のものです。過去の注文時に合意した単価を残すなら、後の価格変更で過去の合計を変えないようにします。その単価は注文の明細へ属する場合があり、商品番号だけで一つに決まるとは言えません。正規化の図をそのまま製品へ移す前に、列が表す時点と業務上の意味を確認してください。
住所も同じです。現在の連絡先住所と、過去の配送先住所は別の事実です。顧客表から現在住所を引けば過去の配送先を正しく再構築できる、とは限りません。この教材のSQL例は現在値を持つ小さな関係を分解するためのもので、注文履歴の完成したスキーマではありません。重複を減らすことが目的でも、異なる時点の事実を誤って一つへ潰すことは改善ではありません。列の意味が決まってから、キーと従属性を導きます。
閉包から候補キーを確かめる
注文番号と商品番号の組から、注文の顧客、顧客の住所、商品の価格、明細の個数を全て導けます。したがってこの組はスーパーキーです。注文番号だけではどの商品と個数か分からず、商品番号だけではどの注文か分からないので、どちらも取り除けません。今回の仮定ではこの組が候補キーになります。行数が少ないから今は一意という観測と、従属性から全属性を決定できることを区別します。
属性集合の閉包は、最初に持つ属性から適用できる従属性を繰り返し使い、得られる属性を増やして求めます。全属性へ届くかに加え、どの属性を除くと届かないかを確かめます。キーらしく見える列名へ頼るのではありません。別の従属性が追加されれば候補キーの組も変わる場合があるため、仮定の一覧と閉包の途中結果を一緒に残します。DDLのPRIMARY KEYは選んだキーを制約にする操作で、業務の意味を自動で発見する機能ではありません。
分解後に同じ行へ戻れるか確かめる
顧客と住所、注文と顧客、商品と価格、注文・商品と個数へ分け、共通の識別属性で結合します。今回の従属性を満たす前提では、意図した三行へ戻る分解を考えられます。SQL例では元表と復元表の両方向のEXCEPTを取り、行数も3かをassertします。片方向だけだと、元の行を全部含んでいても余分な組合せが増えた場合を見落とすためです。
無損失結合は、分けた表から元の関係へ余計な行を作らず戻れる性質です。一つの標本で再結合が一致したことは、その標本の検査であって一般の証明そのものではありません。どの共通属性が片側を決定するかという条件と対応させます。CREATE TABLE ASで作る実験表は、新しい更新へ必要な全制約を自動で引き継いでいません。将来の更新を受ける設計では、キー、外部キー、必要な業務制約を別に定義します。
BEGIN;
CREATE TEMP TABLE original(order_id text,product_id text,customer_id text,address text,price int,qty int,PRIMARY KEY(order_id,product_id));
INSERT INTO original VALUES('O1','P1','C1','North',10,2),('O1','P2','C1','North',20,1),('O2','P1','C1','North',10,1);
CREATE TEMP TABLE customers AS SELECT DISTINCT customer_id,address FROM original;
CREATE TEMP TABLE orders AS SELECT DISTINCT order_id,customer_id FROM original;
CREATE TEMP TABLE products AS SELECT DISTINCT product_id,price FROM original;
CREATE TEMP TABLE lines AS SELECT order_id,product_id,qty FROM original;
CREATE TEMP TABLE restored AS SELECT order_id,product_id,customer_id,address,price,qty FROM lines JOIN orders USING(order_id) JOIN customers USING(customer_id) JOIN products USING(product_id);
DO $$BEGIN
IF EXISTS((SELECT * FROM original EXCEPT SELECT * FROM restored) UNION ALL (SELECT * FROM restored EXCEPT SELECT * FROM original)) THEN RAISE EXCEPTION 'lossy reconstruction'; END IF;
IF (SELECT count(*) FROM restored)<>3 THEN RAISE EXCEPTION 'wrong row count';END IF;
END$$;
SELECT count(*) AS restored_rows FROM restored;
CREATE TEMP TABLE ac(a text,c text,PRIMARY KEY(a,c));
CREATE TEMP TABLE cb(c text PRIMARY KEY,b text);
INSERT INTO ac VALUES('a1','c1'),('a1','c2');
INSERT INTO cb VALUES('c1','b1'),('c2','b1');
SELECT a,b,count(DISTINCT c) AS c_values FROM ac JOIN cb USING(c) GROUP BY a,b HAVING count(DISTINCT c)>1;
ROLLBACK;
3NFとBCNFを別の条件として読む
非自明な従属性X→Aを考え、Xがスーパーキーであることを要求するのがBCNFの基準です。3NFでは、Xがスーパーキーでない場合でも、右辺Aが何らかの候補キーへ属する主属性なら許す条件があります。単に「キー以外がキー以外へ依存しない」という短い覚え方だけでは、この違いを捉えられません。候補キーが複数ある例で確認します。
関係R(A,B,C)にAB→CとC→Bがあるとします。候補キーはABとACで、三属性は全てどこかの候補キーへ属します。C→BのCだけではAを決定できないので、BCNFには違反します。一方、右辺Bは主属性なので、この従属性は3NFで許せます。キーを一つ選んだ後、そのキーに含まれない属性は全て非主属性だと思い込まないことが重要です。主属性の判断は選んだPRIMARY KEYだけでなく、候補キー全体を基準にします。
| 集合 | 閉包 | 候補キーか |
|---|---|---|
| AB | ABC | はい |
| AC | ABC | はい |
| C | BC | いいえ:Aが不足 |
無損失でも、従属性を局所で守れるとは限らない
先ほどのRをCBとACへ分けると、共通のCがCBを決定するため、元の従属性の下で無損失な分解になります。しかしAB→Cを二表それぞれの局所的な制約だけで守れるとは限りません。SQL例のACへ(a1,c1)と(a1,c2)、CBへ(c1,b1)と(c2,b1)を入れると、各表のキーは守れていても、結合後の同じ(a1,b1)へ二つのCが現れます。
この例は、無損失結合と従属性保存が別の性質であることを示します。BCNFへ分けたら全ての元制約が簡単になるというわけではありません。3NFで従属性を守りやすくする選択、BCNFの分解後に結合を含む制約を別に管理する選択などを検討します。正規形の番号を品質の順位だけとみなさず、どの冗長性を減らし、どの整合性の検査をどこへ置くかという設計の判断として説明します。
読取り費用と更新の意味を組み合わせる
分解すると住所の更新場所は一つにできますが、読取りでは結合が増える場合があります。必要な問い合わせ、更新頻度、履歴の保存要件から評価します。結合が一つ増えるから必ず遅いとも、正規化すれば必ず全体が速いとも言いません。索引や行数の見積もりを含めて実行計画を確認し、変更後も同じ業務上の答えを返すかを先に検査します。
読取り用の投影やキャッシュへ冗長に持つ設計もあります。その場合は、どこが正本か、更新の伝播、古い値を許す期間、再構築の手順を定めます。正規化の意味を理解した上で、意図して重複を持つことと、偶然の更新漏れを区別します。第3段階のCQRSへつなぐ場合も、正本の従属性と投影の遅延を同じ制約へ押し込まないようにします。一つの表へ戻すことだけが性能改善の答えではありません。
表の分解と、更新の制約を分ける
分解した表へPRIMARY KEYを付けるだけで、元の意味を全て守れるとは限りません。注文が存在しない明細を受け付けないなら、明細の注文番号から注文表への参照も必要です。一方、顧客がまだ注文を持たない状態を許すなら、顧客表へ単独で行を追加できることには意味があります。結合の結果だけを見て、どの表が独立に存在できるかを決めないようにします。
SQLではNULLの扱いにも注意します。古典的な関係モデルで一つの値として属性を扱う議論と、SQLの未知値を含む比較は、そのまま同じではありません。キーにしたい列では必要なNOT NULLと一意性を確認します。UNIQUEを一つ付けたから、どのDBでもNULLを含む行まで同じ規則で一意になる、と仮定しません。
実習のCREATE TABLE ASは、結合するデータを小さく確かめるためのものです。運用するDDLを完成させた例ではありません。追加する制約ごとに、受け付ける行と拒否する行を一つずつ用意すると、正規化の数式と実装の境界が見えます。制約を付けずに表だけ分けた案と、元の業務上の約束まで保持した案を区別してください。
二つの性質を答える課題
課題はR(A,B,C)、AB→C、C→Bで候補キー、3NFかBCNFか、CBとACへの分解で失い得る局所検査を答えることです。解答は候補キーABとAC、3NFだがBCNFではない、AB→Cが局所では守れない場合がある、です。Cの閉包がBCまででAへ届かないことと、Bが候補キーABに属することを、それぞれの条件へ対応させて説明します。
追加課題は商品の現在価格を注文時単価へ読み替える案です。解答は、商品番号→単価という仮定を先に見直す必要がある、です。過去の価格が違うことは、現在価格の例と同じ制約の下での更新異常ではありません。意味が違う事実を新しく定義しています。正規化を進める前に、時点、キー、従属性、無損失結合、従属性保存の五点を述べられることが到達点です。