作者の名前が何十回も並ぶ
正規化とは、同じ情報を1か所にしか置かないように、表を分けていく設計の方法です。そうすると、1つ直せば全部が直り、データのくい違いが起きません。
図書室の貸出記録に、本を借りるたびに作者の名前を書き写すとします。作者の名前の書き方を直したいとき、何十行も直すことになり、1つ忘れると、同じ人が2通りに載ります。
このレッスンのゴール
メモ
設計から、繰り返しのデータを取り除ける。
キーワード
| ことば | いみ | れい |
|---|---|---|
| 正規化 | 表を分けて、同じ情報の繰り返しを取り除く設計 | |
| 更新の異常 | 同じ情報が何か所もあり、一部だけ直してしまうこと | |
| 第1正規形 | 1つのセルに、値を1つだけ入れる形 | |
| 第2正規形 | 組み合わせのキーの一部だけに決まる列を、なくした形 | |
| 第3正規形 | キー以外の列に決まる列を、なくした形 | |
| 関数従属 | ある列の値が決まれば、別の列の値も決まること | 本→作者 |
学生が入力するセルは、このプレビューでは空白です。
平たい表では何が困るのか
次の表は、図書室の貸出記録を、1つの平たい表に入れたものです。会員の組と、本の作者が、貸出のたびに書き写されています。
田中さんの組が変わると、田中さんの行を、全部直さなければなりません。1行だけ直し忘れると、同じ人が2つの組に入ってしまいます。これが更新の異常です。
試す: 平たい表を読む
-- 平たい表を、そのまま読みます。
SELECT * FROM flat_loans;
-- 同じ作者の名前が、何度出てくるかを数えてみましょう。
例: 直し忘れが起きた表
| loan_id | member | class_name | book |
|---|---|---|---|
| 1 | 田中 | 2年B組 | 銀河鉄道の夜 |
| 2 | 田中 | 2年A組 | 坊っちゃん |
学生が入力するセルは、このプレビューでは空白です。
第1正規形: 1つのセルに1つの値
1つのセルに、「銀河鉄道の夜、坊っちゃん」のように、複数の値を入れてはいけません。数えたり探したりするのが、難しくなります。
複数の値があるときは、行を分けて、1行に1つの値だけを入れます。これが第1正規形です。
第2と第3正規形: 繰り返しを分ける
貸出の表を見ると、会員の名前が決まれば組が決まります。本の名前が決まれば作者が決まります。この、決まる関係を関数従属と呼びます。
貸出の情報とは別の事実を、別の表に分けます。会員の表、本の表、貸出の表の3つにして、貸出の表には番号だけを入れます。これが第3正規形への道です。
始める前に
メモ
上のバーの Start the lab を押し、マシンが動き出すまで待ってください。表は自分のデータベース student_club に作り、ショップは変えません。ターミナルを開き直したときは、最初の2つの Run をもう一度押します。
証明書を受け入れる設定
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
クライアント用の短い名前
alias db='mariadb -h db -u student -pstudent -t -e'ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
平たい表を作る
db "CREATE DATABASE IF NOT EXISTS student_club; DROP TABLE IF EXISTS student_club.flat_loans; CREATE TABLE student_club.flat_loans (loan_id INT PRIMARY KEY, member VARCHAR(20) NOT NULL, class_name VARCHAR(20) NOT NULL, book VARCHAR(40) NOT NULL, author VARCHAR(20) NOT NULL)"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
貸出を6件入れる
db "INSERT INTO student_club.flat_loans VALUES (1, '田中', '2年A組', '銀河鉄道の夜', '宮沢賢治'), (2, '田中', '2年A組', '坊っちゃん', '夏目漱石'), (3, '佐藤', '3年B組', '坊っちゃん', '夏目漱石'), (4, '佐藤', '3年B組', '吾輩は猫である', '夏目漱石'), (5, '鈴木', '2年A組', '銀河鉄道の夜', '宮沢賢治'), (6, '高橋', '1年C組', '吾輩は猫である', '夏目漱石')"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
1行だけ直して、異常を起こす
db "UPDATE student_club.flat_loans SET class_name = '2年B組' WHERE loan_id = 1"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
田中さんの組を確かめる
db "SELECT DISTINCT member, class_name FROM student_club.flat_loans WHERE member = '田中'"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
表を分けて、異常をなくす
平たい表から、本の表を取り出すには、SELECT DISTINCT を使います。DISTINCT は、同じ行の繰り返しを、1つにします。
取り出した行を新しい表に入れ、貸出の表には本の番号だけを残します。作者の名前は、本の表に1か所だけになります。
試す: 会員の表になる行
-- 繰り返しを除いた、会員の表になる行です。
SELECT DISTINCT member, class_name FROM flat_loans;
-- 本と作者だけを DISTINCT で取り出す形に変えてみましょう。
本の表を作る
db "DROP TABLE IF EXISTS student_club.books; CREATE TABLE student_club.books (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(40) NOT NULL UNIQUE, author VARCHAR(20) NOT NULL) AS SELECT DISTINCT book AS title, author FROM student_club.flat_loans"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
本の表を見る
db "SELECT id, title, author FROM student_club.books ORDER BY id"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
よくある間違い
注意
表をたくさん分ければ分けるほど良い、と思いがちです。分けすぎると、読むたびに JOIN が増えます。同じ事実が2か所に書かれないことが目的で、表の数が目的ではありません。
さらに挑戦: 会員の表と貸出の表
同じやり方で、members 表を作ります。平たい表から、会員の名前と組を DISTINCT で取り出してください。
次に、flat_loans の member を、会員の番号に置き換えた loans 表の作り方を考えます。JOIN を使うと、番号と名前を、つなぎ直せるはずです。