1社に住所が40通り
ある小さな会社が、注文を1枚の大きな表で管理していました。どの行にも、取引先の名前と住所が繰り返し書いてあります。
取引先が移転しました。担当者は40行のうち38行の住所を直し、2行を見落としました。
これで1社に2つの住所ができ、どちらが正しいか誰にも分かりません。
このレッスンのゴール
メモ
1枚の平たい表を、繰り返しのない設計に分けられる。
重要な言葉
| ことば | いみ |
|---|---|
| 冗長性 | 同じ事実が、2か所以上に書いてあること |
| 異常 | 冗長性のせいで、データを変えるときに起こる不具合 |
| 関数従属 | ある列の値が、別の列の値で1つに決まること |
| 原子的 | 1つのセルに値が1つだけで、リストではないこと |
| 正規形 | 整った設計の段階。このレッスンでは第1から第3までを扱う |
| 正規化 | 冗長性がなくなるまで、表を分けること |
学生が入力するセルは、このプレビューでは空白です。
繰り返しは3つの不具合を生む
平たい表では、同じ事実が何度も書かれます。そこから3つの不具合が起こります。これを異常と呼びます。
更新異常: 1か所を直して、ほかを直し忘れる。
挿入異常: 注文が入るまで、取引先を記録できない。
削除異常: 最後の注文を消すと、取引先の唯一の記録も消える。
例: 平たい表
| order_id | customer | city | prefecture | product | qty |
|---|---|---|---|---|---|
| 1 | 佐藤商店 | 横浜市 | 神奈川県 | コピー用紙 | 2 |
| 1 | 佐藤商店 | 横浜市 | 神奈川県 | ペン | 10 |
| 2 | 鈴木工業 | 浜松市 | 静岡県 | コピー用紙 | 5 |
| 3 | 佐藤商店 | 横浜市 | 神奈川県 | ファイル | 4 |
学生が入力するセルは、このプレビューでは空白です。
設計が省いたものを見る
ショップのデータベースは、もう表に分けてあります。1人の顧客を平たい表にして、何が繰り返されるかを数えます。
まず、準備の2行を Run します。
証明書を受け入れる設定
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
ショップ用の短い名前
alias sql='mariadb -h db -u student -pstudent shop -t -e'ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
1人の顧客を平たい表で見る
sql "SELECT customers.first_name, customers.city, orders.id AS order_no, products.name AS product FROM customers JOIN orders ON orders.customer_id = customers.id JOIN order_items ON order_items.order_id = orders.id JOIN products ON products.id = order_items.product_id WHERE customers.id = 33 ORDER BY orders.id, products.name LIMIT 5"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
明細の行数と顧客の数
sql "SELECT COUNT(*) AS flat_rows, COUNT(DISTINCT orders.customer_id) AS customers FROM orders JOIN order_items ON order_items.order_id = orders.id"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
第1正規形と第2正規形
第1正規形(1NF): どのセルにも値が1つ。「ペン、定規」のようなリストを入れず、item1、item2、item3 のような列も作らない。
第2正規形(2NF): キー以外の列は、キーの一部ではなく、キー全体で決まる。キーが order_id と product の組なら、顧客についての列は order_id だけで決まる。だから外へ出す。
例: 平たい表を分ける
| 新しい表 | 列 | キー | 持つもの |
|---|---|---|---|
| orders | order_id, customer_id | order_id | 注文1件につき1行 |
| order_items | order_id, product_id, qty | order_id と product_id | 明細1つにつき1行 |
| customers | customer_id, name, city_id | customer_id | 1社につき1行 |
| cities | city_id, city, prefecture | city_id | 1つの市につき1行 |
学生が入力するセルは、このプレビューでは空白です。
第3正規形は隠れた従属を外す
第3正規形(3NF): キーでない列が、別のキーでない列で決まることがない。
顧客の id で city が決まります。city で prefecture が決まります。県は、市を通して間接的にキーで決まっています。これを推移的関数従属と呼びます。
市と県を別の表に移し、顧客には市の番号だけを持たせます。
1つの市に国が2つあるか
sql "SELECT COUNT(*) AS cities_with_two_countries FROM (SELECT city FROM customers GROUP BY city HAVING COUNT(DISTINCT country) > 1) AS x"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
市の数と国の数
sql "SELECT COUNT(DISTINCT city) AS cities, COUNT(DISTINCT country) AS countries FROM customers"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
一番多く書かれている市
sql "SELECT city, country, COUNT(*) AS customers FROM customers GROUP BY city, country ORDER BY customers DESC, city LIMIT 3"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
よくある間違い
注意
表は多いほど良いと思いがちです。分けると繰り返しはなくなりますが、読み直すたびに結合が要ります。測って理由が分かるまでは、第3正規形で止めておきます。
試す: 顧客を1回ずつ書く
-- 顧客を、1社ずつ1回だけ書き出します。
SELECT DISTINCT customer, city
FROM flat_orders
ORDER BY customer;
-- 市と県を、1組ずつ1回だけ書き出す形に変えてみましょう。
さらに挑戦
例の4つの表を、自分のデータベース student_depot に作ってください。order_items が指す先として、小さな products 表も作ります。どの表にも主キーを付け、別の表を指す列には外部キーを付けます。
次に、ページ内の SQL ブロックにある平たい表の6行を、その表に入れます。最後に、平たい表をもう一度出す結合を書いて、設計を確かめます。