花札にハマってます。ko_ya346です。
最近こちらの本を読み終えました。
DBど素人の自分でも読みやすく、DB設計の難しさや考え方が理解できたのでめちゃいい本でした。
テーブルを正規化する問題があったので、今回はそれを解いていきたいと思います。
3章の章末問題
次のような「支社支店商品」テーブルについて考えます。
下線が引いてある列は主キーです。
| 支社コード | 支社名 | 支店コード | 支店名 | 商品コード | 商品名 | 商品分類コード | 分類名 |
|---|---|---|---|---|---|---|---|
| 001 | 東京 | 01 | 渋谷 | 001 | 石鹸 | C1 | 水洗用品 |
| 001 | 東京 | 01 | 渋谷 | 002 | タオル | C1 | 水洗用品 |
| 001 | 東京 | 01 | 渋谷 | 003 | 歯ブラシ | C1 | 水洗用品 |
| 001 | 東京 | 02 | 八重洲 | 002 | タオル | C1 | 水洗用品 |
| 001 | 東京 | 02 | 八重洲 | 003 | 歯ブラシ | C1 | 水洗用品 |
| 001 | 東京 | 02 | 八重洲 | 004 | コップ | C1 | 水洗用品 |
| 001 | 東京 | 02 | 八重洲 | 005 | 箸 | C2 | 食器 |
| 001 | 東京 | 02 | 八重洲 | 006 | スプーン | C2 | 食器 |
| 002 | 大阪 | 01 | 堺 | 001 | 石鹸 | C1 | 水洗用品 |
| 002 | 大阪 | 01 | 堺 | 002 | タオル | C1 | 水洗用品 |
| 002 | 大阪 | 02 | 豊中 | 007 | 雑誌 | C3 | 書籍 |
| 002 | 大阪 | 02 | 豊中 | 008 | 爪切り | C4 | 日用雑貨 |
関数従属性
はじめに、このテーブルの関数従属性について考えます。
関数従属性の定義は「X列の値を決めれば、Y列の値が一つに決まる」です。
このテーブルについて考えると、
{支社コード} -> {支社名}
は、関数従属性を満たします。
例えば
支社コードの「001」 -> 支社名「東京」
支社コードの「002」 -> 支社名「大阪」
に一意に対応しています。
一方で、
{支社コード} -> {支店名}
は関数従属性を満たしていません。
これは、支社コードの「001」 に対し、支店名は「渋谷」「八重洲」の2つが対応しているからです。
このテーブルの関数従属性をまとめると、
{支社コード} -> {支社名}
{支社コード、支店コード} -> {支店名}
（支店コード「01」だけでは支店名が「渋谷」「堺」がヒットしてしまう）
{商品コード} -> {商品名、商品分類コード}
{商品分類コード} -> {分類名}
です。
また、主キーの一部の列に対して従属する列がある場合、部分関数従属と呼びます。
このテーブルでは、
{支社コード} -> {支社名}
{支社コード、支店コード} -> {支店名}
{商品コード} -> {商品名、商品分類コード}
が部分関数従属です。
正規形
第一、第二、第三正規形の定義を確認していきます。
第一正規形
一つのセルの中には一つの値しか含まない
例えば、
| col1 | col2 |
|---|---|
| aaa | [1, 2] |
のように、セル中にリストを含むテーブルは第一正規形ではありません。
この場合は、
| col1 | col2 |
|---|---|
| aaa | 1 |
| aaa | 2 |
のように行方向に展開して、第一正規形にすることができます。
第二正規形
テーブル中に部分関数従属の関係の列がない
です。
最初のテーブルには部分関数従属が含まれているので、これを解消していくと、
| 支社コード | 支店コード | 商品コード |
|---|---|---|
| 001 | 01 | 001 |
| 001 | 01 | 002 |
| 001 | 01 | 003 |
| 001 | 02 | 002 |
| 001 | 02 | 003 |
| 001 | 02 | 004 |
| 001 | 02 | 005 |
| 001 | 02 | 006 |
| 002 | 01 | 001 |
| 002 | 01 | 002 |
| 002 | 02 | 007 |
| 002 | 02 | 008 |
| 支社コード | 支社名 |
|---|---|
| 001 | 東京 |
| 002 | 大阪 |
| 支社コード | 支店コード | 商品名 |
|---|---|---|
| 001 | 01 | 渋谷 |
| 001 | 02 | 八重洲 |
| 002 | 01 | 堺 |
| 002 | 02 | 豊中 |
| 商品コード | 商品名 | 商品分類コード | 分類名 |
|---|---|---|---|
| 001 | 石鹸 | C1 | 水洗用品 |
| 002 | タオル | C1 | 水洗用品 |
| 003 | 歯ブラシ | C1 | 水洗用品 |
| 004 | コップ | C1 | 水洗用品 |
| 005 | 箸 | C2 | 食器 |
| 006 | スプーン | C2 | 食器 |
| 007 | 雑誌 | C3 | 書籍 |
| 008 | 爪切り | C4 | 日用雑貨 |
のように4テーブルに分割できました。
第三正規形
テーブル中に推移的関数従属の関係の列がない
テーブル内部に存在する段階的な従属関係のことを、推移的関数従属と呼びます。
先ほど第二正規形にした最後のテーブルには、
{商品分類コード} -> {分類名}
という関係が残っています。
これを解消すると、
| 商品コード | 商品名 | 商品分類コード |
|---|---|---|
| 001 | 石鹸 | C1 |
| 002 | タオル | C1 |
| 003 | 歯ブラシ | C1 |
| 004 | コップ | C1 |
| 005 | 箸 | C2 |
| 006 | スプーン | C2 |
| 007 | 雑誌 | C3 |
| 008 | 爪切り | C4 |
| 商品分類コード | 分類名 |
|---|---|
| C1 | 水洗用品 |
| C2 | 食器 |
| C3 | 書籍 |
| C4 | 日用雑貨 |
となります。
最初のテーブルを第三正規形に直すと、5つのテーブルに分割することができました。
結局正規化すると何がうれしいの？
例えば、支社「hoge」を新たに追加したい場合を考えます。
一番最初のテーブルに登録しようとした場合、支社コードと支社名しか決まっていないので他の値はnullまたは適当なダミー値（未分類etc...）を入れる等しなければいけません。
| 支社コード | 支社名 | 支店コード | 支店名 | 商品コード | 商品名 | 商品分類コード | 分類名 |
|---|---|---|---|---|---|---|---|
| 003 | hoge | null | null | null | null | null | null |
一方、正規化したテーブルの場合は、支社名のテーブルに追加するだけの改修で済みます。
| 支社コード | 支社名 |
|---|---|
| 001 | 東京 |
| 002 | 大阪 |
| 003 | hoge |
また、テーブルを分割することでコードと名称の対応を間違えることもありません。
以上がメリットとなります。
感想
業務中、「テーブル分割されすぎててjoinめんどくせー」と思う場面が何度かあり、あまりテーブル正規化のメリットを感じることが少なかったので、今回知ることが出来てよかったです。
データを分析するだけでなく、データを作る業務にも今後はかかわりたいなと思っていたので、その足掛かりにちょうど良かったなと思いました。
