正規化の実践
判断は自分でする
ここまで、1NF・2NF・3NF を1つずつ潰してきました。今回は、どこが違反しているかを教えずに乱れた表を渡します。自分で見つけて直してください。
渡される表はこれです。主キーは (order_id, product_id) です。
| order_id | product_id | product_name | category_id | category_name | quantity |
|---|---|---|---|---|---|
| 1 | 1 | ノートPC | 10 | パソコン | 1 |
| 1 | 2 | マウス | 20 | 周辺機器 | 2 |
| 2 | 1 | ノートPC | 10 | パソコン | 3 |
手順は毎回同じ
乱れた表を渡されたら、次の順で見ます。順番を守ることが大事で、飛ばすと見落とします。
手順1 — 1マスに複数値が入っていないか。カンマ区切りや tag1、tag2 を探します。あれば行に分解します。
手順2 — 主キーが複合キーなら、一部だけで決まる列がないか。 主キー以外の列を1つずつ取り上げ、「これは主キー全体が要るか、一部で足りるか」と聞きます。一部で足りるなら、その一部を主キーとする表に移します。
手順3 — 主キー以外の列で決まる列がないか。残った列同士で「AがBを決めていないか」を聞きます。決めているなら、Aを主キーとする表に移します。
上の表に当てはめる
手順1では何も見つかりません。どのマスも値は1つです。
手順2で、product_name、category_id、category_name が引っかかります。どれも product_id だけで決まります。order_id は要りません。この3列は product_id を主キーとする表に移します。
手順3は、移したあとの表に対しても行います。products に移った category_name は category_id で決まるので、さらに categories に移すことになります。
正規化は1回で終わらない、というのがここでの学びです。移した先でまた違反が見つかるので、見つからなくなるまで繰り返します。
分けすぎではないか
3つの表に増えました。JOIN が増えて面倒に見えるかもしれません。
しかし、カテゴリ名を「周辺機器」から「アクセサリ」に変えるとき、直すのは1行です。分けていなければ、その商品が売れた明細すべてを直すことになります。面倒さは書くときに前払いし、あとで受け取ります。
3つの表をつなぐ土台
2回 JOIN すると、貸出から本、本からジャンルへと関係をたどれます。
SQL クエリ
SELECT l.id, b.title, g.name AS genre
FROM borrowings l
JOIN books b ON b.id = l.book_id
JOIN genres g ON g.id = b.genre_id
ORDER BY l.id;
ALTER TABLE loan_details DROP COLUMN book_title, DROP COLUMN genre_name;複数の列を外す場合は、1つの ALTER TABLE に操作をカンマで並べられます。演習では復元用の結合と分離先の表を用意し、移送と不要な列の削除に集中します。
手を動かす
手順1から3を順に当てて、3つの表に分けてください。
sales_raw を 3NF まで直してください。主キーは (order_id, product_id) です。
categoriesテーブルを作る。列はid integer PRIMARY KEYとname text NOT NULL。重複を除いて入れるproductsテーブルを作る。列はid integer PRIMARY KEY、name text NOT NULL、category_id integer NOT NULL。重複を除いて入れるsales_rawからproduct_name、category_id、category_nameの3列を消す- 3つの表を結合して、
order_id、product_id、商品名をproduct_name、カテゴリ名をcategory_name、quantityの5列を、order_id、product_idの順に取り出す
2つの分離先と復元用の結合は用意済みです。重複を除く移送を2本書き、3列の削除は1文にまとめます。 ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 5 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE sales_raw (
order_id integer NOT NULL,
product_id integer NOT NULL,
product_name text NOT NULL,
category_id integer NOT NULL,
category_name text NOT NULL,
quantity integer NOT NULL,
PRIMARY KEY (order_id, product_id)
);
INSERT INTO sales_raw VALUES
(1, 1, 'ノートPC', 10, 'パソコン', 1),
(1, 2, 'マウス', 20, '周辺機器', 2),
(2, 1, 'ノートPC', 10, 'パソコン', 3),
(2, 3, 'キーボード', 20, '周辺機器', 1),
(3, 4, 'モニタ', 30, 'ディスプレイ', 2);
期待される出力
| order_id | product_id | product_name | category_name | quantity |
|---|---|---|---|---|
| 1 | 1 | ノートPC | パソコン | 1 |
| 1 | 2 | マウス | 周辺機器 | 2 |
| 2 | 1 | ノートPC | パソコン | 3 |
| 2 | 3 | キーボード | 周辺機器 | 1 |
| 3 | 4 | モニタ | ディスプレイ | 2 |
ヒント
まだ開いていません
付箋は 3 枚。下の段ほど答えに近づきます
ヒント 2
ヒント 3答えに近い