SQLもプログラミング言語…っぽい4
このシリーズも4回目になる。お題は以下の通り
テーブルの正規化
正規化とはなんぞや?
ここが分かりやすかった → データベースの正規化(正規形)とはなんぞや
以下、上のリンクに従って概要をまとめてみる
非正規形
わかりやすく言うとExcelで左のセルを縦に結合したとき、右に複数行のデータがあればそれは非正規形であると言える。データベースに配列型をぶっこもうとしている状態、アカンのである。
食品名 | 栄養成分 | 値 | 単位 | |
精白米 | ||||
炭水化物 | 168 | kcal | ||
エネルギー | 37.1 | g | ||
ビタミン | C | 1 | μg | |
D | 1 | μg | ||
E | 1 | μg |
上の例だと、「精白米」という1レコードに対して「炭水化物、エネルギー、ビタミン」という3レコードがくっついている。
この考えをそのままデータベースに適用しようとすると、配列型が用意されてないDBだとできない*1。それで、VARCHAR型に無理やりカンマ区切りでデータを入れたりするやらかし先生がいるかもしれない。「炭水化物,エネルギー,ビタミン」みたいな感じで…
実は、これはSQLの有名なアンチパターンで、「Jaywalking」でググるといろいろ出てくる。
www.slideshare.net
上のスライドには種本がある、自分はまだ読めていない
- 作者: Bill Karwin,和田卓人,和田省二,児島修
- 出版社/メーカー: オライリージャパン
- 発売日: 2013/01/26
- メディア: 大型本
- 購入: 9人 クリック: 698回
- この商品を含むブログ (45件) を見る
第1正規形
先ほどのExcelの結合部分を右の行数に合わせて分割する。繰り返し項目が増えるが、これでレコードがそれぞれ1行で表せる。
食品名 | 栄養成分 | 値 | 単位 |
精白米 | 炭水化物 | 168 | kcal |
精白米 | エネルギー | 37.1 | g |
精白米 | ビタミンC | 1 | μg |
精白米 | ビタミンD | 1 | μg |
精白米 | ビタミンE | 1 | μg |
第2正規形・第3正規形
この2つはそんなに変わらない気がする。表を独立する部分で切り出して行けば自ずとこれになる*2。自分が作った例では表が簡単すぎて分割できない…他のサイトを見てみてください。
外部結合の設定
SQLは上の要領で分割して粗結合にしたテーブルを外部結合して見たい情報を揃えるのが一般的な使い方だ。
以前取り上げた例をもとにやってみよう
SQLもプログラミング言語…っぽい - なんとな~くしあわせ?の日記
LEFT JOIN
Parentテーブル
主キー | 整理番号 | 姓 | 名 |
1 | 12345 | 山田 | 太郎 |
2 | 12346 | 山田 | 花子 |
3 | 12346 | 山田 | 花子 |
4 | 12347 | 山田 | 嘘子 |
5 | 12348 | 山田 | 良子 |
Childテーブル
整理番号 | 住所 | 郵便番号 |
12345 | どこかの台 1丁目 | 131-0202 |
12346 | すすきが原 3丁目 | 251-0003 |
12346 | ニセコ | 201-0003 |
Parentテーブルの整理番号とChildテーブルの整理番号が共通してるので、Parentテーブルを基本として左外部結合してみる
CREATE TABLE parent(`主キー` text, `整理番号` int, `姓` text, `名` text); INSERT INTO parent(`主キー`, `整理番号`, `姓`, `名`) VALUES (00001, 12345, "山田", "太郎") , (00002, 12346, "山田", "花子") , (00003, 12346, "山田", "花子") , (00004, 12347, "山田", "嘘子") , (00005, 12348, "山田", "良子"); CREATE TABLE child(`整理番号` int, `住所` text, `郵便番号` text); INSERT INTO child(`整理番号`, `住所`, `郵便番号`) VALUES (12345, "どこかの台 1丁目", "131-0202") , (12346, "すすきが原 3丁目", "251-0003") , (12346, "ニセコ", "201-0003");
このように、左に指定したテーブルが中心となる。そのため右に結合したテーブルにNULLが入ることもある。
主キー | 整理番号 | 姓 | 名 | 整理番号 | 住所 | 郵便番号 |
1 | 12345 | 山田 | 太郎 | 12345 | どこかの台 1丁目 | 131-0202 |
2 | 12346 | 山田 | 花子 | 12346 | すすきが原 3丁目 | 251-0003 |
3 | 12346 | 山田 | 花子 | 12346 | すすきが原 3丁目 | 251-0003 |
2 | 12346 | 山田 | 花子 | 12346 | ニセコ | 201-0003 |
3 | 12346 | 山田 | 花子 | 12346 | ニセコ | 201-0003 |
4 | 12347 | 山田 | 嘘子 | (null) | (null) | (null) |
5 | 12348 | 山田 | 良子 | (null) | (null) | (null) |
右に配置されるレコードが複数行ありえる場合、レコードが増えてしまう。それを防ぐためには、以下のようにレコードを一意に絞りこめるようにする。
LEFT JOIN child ON parent.`整理番号` = child.`整理番号` AND child.`郵便番号` = '251-0003'
書籍の紹介
とまあここまでは実は応用情報技術者試験の範囲のようだ。
以下の本のSQLのところを見るともっと網羅的な解説が載っている。
ニュースペックテキスト 応用情報技術者 平成29・30年 (情報処理技術者試験)
- 作者: TAC情報処理講座
- 出版社/メーカー: TAC出版
- 発売日: 2017/07/15
- メディア: 単行本(ソフトカバー)
- この商品を含むブログを見る
履歴管理
みんなやってることなので、Qiitaに記事がある
*1:Postgresql先生にはあるようだ ぱせらんメモ - PostgreSQLで配列型のカラムを使ってみる
*2:コッド博士はこれをちゃんと定義して説明したから偉い、想像するだけなら凡人でもできる