第2正規形
複合キーの一部だけで決まる列
1NF になったら次は第2正規形、2NF です。条件はこうです。
主キーの一部だけで決まる列を、テーブルに置かない。
これが問題になるのは、主キーが複合キーのときだけです。主キーの候補が 1 列だけなら、その1列で決まらない列というものが存在しないので、1NF ならそのまま 2NF です。
注文明細を見る
注文明細の主キーは (order_id, product_id) の複合キーでした。この表に商品名を入れてしまった状態を考えます。
| order_id | product_id | product_name | quantity |
|---|---|---|---|
| 1 | 1 | ノートPC | 1 |
| 1 | 2 | マウス | 2 |
| 2 | 1 | ノートPC | 3 |
列ごとに「何で決まるか」を聞いてみます。
quantityは、どの注文のどの商品かが決まらないと決まらない → 主キー全体で決まるproduct_nameは、product_idだけで決まる → 主キーの一部で決まる
この「主キーの一部だけで決まっている」状態を部分関数従属と呼びます。2NF はこれを禁止します。
何が起きるか
部分関数従属を放置すると、product_id = 1 の商品名が明細の件数だけ書かれます。ノートPCが100回売れたら、100回「ノートPC」と書かれます。
これは第1章で見た sales_flat と同じ構造です。商品名を変えるときに全部書き換える必要があり、書き換え漏れが不整合になります。
直し方
主キーの一部だけで決まる列を、その一部を主キーとする別のテーブルに移します。
product_name は product_id だけで決まるので、product_id を主キーとする products テーブルに移します。明細には product_id だけを残します。
| products | order_items | ||||
|---|---|---|---|---|---|
| id | name | order_id | product_id | quantity | |
| 1 | ノートPC | 1 | 1 | 1 | |
| 2 | マウス | 1 | 2 | 2 | |
| 2 | 1 | 3 |
商品名は1か所にしかありません。100回売れても書かれるのは1回です。
見つけ方の手順
実務では、次の順で見ます。
- 主キーが複合キーのテーブルを探す
- その表の主キー以外の列を1つずつ取り上げる
- 「この列は、主キーの一部だけで決まらないか」と聞く
- 決まるなら、その一部を主キーとする表に移す
単価はどうでしょうか。 unit_price は「注文したときの値段」なので、商品が同じでも注文が違えば変わり得ます。だから主キー全体で決まり、明細に残して正解です。同じ列名でも意味が違えば判定が変わるので、名前ではなく意味で判断します。
重複を除いてから列を外す
貸出の明細に同じ本が何度も登場しても、本の表には1冊1行だけを移します。
SQL クエリ
INSERT INTO books (id, title)
SELECT DISTINCT book_id, book_title
FROM loan_details;
ALTER TABLE loan_details DROP COLUMN book_title;DISTINCT は選んだ列の組み合わせが同じ行を1つにします。たとえば (7, 'SQL入門') が3行あっても1行だけ移ります。移送が済んでから DROP COLUMN で元の重複列を外します。
手を動かす
注文明細から商品名を分離します。
order_items_flat は主キーが (order_id, product_id) の複合キーなのに、product_id だけで決まる product_name を持っています。2NF に直してください。
productsテーブルを作る。列はid integer PRIMARY KEYとname text NOT NULLorder_items_flatから重複を除いた商品をproductsに入れるorder_items_flatからproduct_name列をALTER TABLE ... DROP COLUMNで消す- 明細と
productsをproduct_idで結び、明細の全列と商品名をorder_id、product_idの順に取り出す (確認用の SQL。書き換えずに最後に残してください。書き換えたかどうかは採点では見分けられません。明細にproduct_nameが残っていると列が合いません)
分離先の表と確認は用意済みです。重複を除いた移送と元の列の削除を担当します。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 4 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE order_items_flat (
order_id integer NOT NULL,
product_id integer NOT NULL,
product_name text NOT NULL,
quantity integer NOT NULL,
unit_price integer NOT NULL,
PRIMARY KEY (order_id, product_id)
);
INSERT INTO order_items_flat VALUES
(1, 1, 'ノートPC', 1, 128000),
(1, 2, 'マウス', 2, 3200),
(2, 1, 'ノートPC', 3, 128000),
(2, 3, 'キーボード', 1, 8900),
(3, 2, 'マウス', 1, 2980);
期待される出力
| order_id | product_id | quantity | unit_price | name |
|---|---|---|---|---|
| 1 | 1 | 1 | 128000 | ノートPC |
| 1 | 2 | 2 | 3200 | マウス |
| 2 | 1 | 3 | 128000 | ノートPC |
| 2 | 3 | 1 | 8900 | キーボード |
| 3 | 2 | 1 | 2980 | マウス |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い