第3正規形
キー以外の列で決まる列
最後が第3正規形、3NF です。条件はこうです。
主キー以外の列で決まる列を、テーブルに置かない。
2NF は「主キーの一部で決まる列」を追い出しました。3NF は「そもそも主キーと関係ない列で決まる列」を追い出します。
店舗テーブルを見る
地域別の集計をしたい、という要件が来て region を足したとします。
| id | name | pref | region |
|---|---|---|---|
| 1 | 渋谷店 | 東京都 | 関東 |
| 2 | 新宿店 | 東京都 | 関東 |
| 3 | 梅田店 | 大阪府 | 関西 |
列ごとに「何で決まるか」を聞きます。
nameはidで決まる → 主キーで決まる。問題なしprefはidで決まる → 主キーで決まる。問題なしregionはprefで決まる → 主キーではなく、別の列で決まっている
東京都なら必ず関東です。店舗が何であろうと関係ありません。この「主キー → pref → region」と2段階で決まっている状態を推移的関数従属と呼びます。
何が起きるか
東京都の店舗が50店あれば、「東京都は関東」という事実が50回書かれます。またしても同じ構造です。
そして、ここには2NF のときには無かった嫌らしさがあります。間違った組み合わせを書けてしまうことです。
| 4 | 池袋店 | 東京都 | 関西 |
この行を止めるものは何もありません。データベースは pref と region の関係を知らないからです。人が入力する限り、いつかこの行が入りえます。
直し方
決める側の列を主キーとする別のテーブルに移します。
region は pref で決まるので、pref を主キーとする prefectures テーブルを作って移します。
| prefectures | stores | ||||
|---|---|---|---|---|---|
| pref | region | id | name | pref | |
| 東京都 | 関東 | 1 | 渋谷店 | 東京都 | |
| 大阪府 | 関西 | 2 | 新宿店 | 東京都 |
こうなると「東京都は関東」は1行しかありません。池袋店を関西にすることは、構造的に不可能になります。
3つの正規形をまとめる
| 正規形 | 追い出すもの | 合言葉 |
|---|---|---|
| 1NF | 1マスの中の複数値 | 繰り返しは行にする |
| 2NF | 主キーの一部で決まる列 | 部分関数従属を消す |
| 3NF | 主キー以外で決まる列 | 推移的関数従属を消す |
実務では、この3つをまとめて「すべての列は、キーによって、キー以外の何ものにもよらず決まる」と覚えられています。3NF まで来れば、日常の設計はほぼ足ります。
分離した情報を結合で復元する
図書館の住所情報を市区町村の表に分けたら、共通のキーを使って元の情報を取り出せます。
SQL クエリ
SELECT l.id, l.name, c.area
FROM libraries l
JOIN cities c ON c.code = l.city_code
ORDER BY l.id;JOIN は ON の条件が合う相手と行を結びます。LEFT JOIN と異なり、相手の無い行は出ません。地域は cities にだけ保存し、必要なときに結合で読みます。
手を動かす
店舗テーブルから地域を分離します。
stores の region は pref で決まっているので、3NF 違反です。分離してください。
prefecturesテーブルを作る。列はpref text PRIMARY KEYとregion text NOT NULLstoresから重複を除いた都道府県と地域をprefecturesに入れるstoresからregion列を消すstoresとprefecturesを結合して、id、name、pref、regionの4列をidの順に取り出す
都道府県の表の作成と移送は用意済みです。元の地域列を外し、関係を使って地域を復元します。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 2 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL,
pref text NOT NULL,
region text NOT NULL
);
INSERT INTO stores VALUES
(1, '渋谷店', '東京都', '関東'),
(2, '新宿店', '東京都', '関東'),
(3, '梅田店', '大阪府', '関西'),
(4, '博多店', '福岡県', '九州');
期待される出力
| id | name | pref | region |
|---|---|---|---|
| 1 | 渋谷店 | 東京都 | 関東 |
| 2 | 新宿店 | 東京都 | 関東 |
| 3 | 梅田店 | 大阪府 | 関西 |
| 4 | 博多店 | 福岡県 | 九州 |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い