達人に学ぶDB設計 徹底指南書 ch3 論理設計と正規化〜なぜテーブルは分割する必要があるのか？ 2/3
せっかくなので閃乱カグラのデータで勉強する
正規化とはなにか？
正規形の定義
正規形のレベルは第5まであるが、普通は第3正規形まで理解すれば十分
- 正規形
- 冗長性を排除
- 一貫性と効率性を保持するためのデータ形式
- 非一貫性
- 更新処理のタイムラグによってデータ不整合がおこる
- そもそもデータを登録できない
第1正規形
第1正規形の定義~スカラ値の原則
- 1つのセルの中には1つの値しか含まない
- スカラ値
第1正規化を作ろう
- 非正規形
学校
| 学校ID(PK) | 学校名 | 姉妹 |
|---|---|---|
| 000A | 半蔵学院 | |
| 000B | 蛇女子学園 | 紫 忌夢 両備 両奈 |
- 第1正規形A(列持ち)
学校
| 学校ID(PK) | 学校名 | 姉妹1 | 姉妹2 | 姉妹3 | 姉妹4 |
|---|---|---|---|---|---|
| 000A | 半蔵学院 | ||||
| 000B | 蛇女子学園 | 紫 | 忌夢 | 両備 | 両奈 |
- 第1正規形B(行持ち)
学校
| 学校ID | 学校名 | 姉妹 |
|---|---|---|
| 000A | 半蔵学院 | |
| 000B | 蛇女子学園 | 紫 |
| 000B | 蛇女子学園 | 忌夢 |
| 000B | 蛇女子学園 | 両備 |
| 000B | 蛇女子学園 | 両奈 |
- 普通は行持ちを採用する
- しかしBは主キーを設定できない
- (学校ID,学校名,姉妹)を複合主キーにしたいが、NULLを含むため不可
- どうする
- 「姉妹なし」を入れることにする
- テーブル分割する
学校
| 学校ID(PK) | 学校名 |
|---|---|
| 000A | 半蔵学院 |
| 000B | 蛇女子学園 |
姉妹
| 学校ID(PK) | 姉妹(PK) |
|---|---|
| 000B | 紫 |
| 000B | 忌夢 |
| 000B | 両備 |
| 000B | 両奈 |
学校IDについてLEFT OUTER JOINしてもとに戻せる
なぜ1つのセルに複数の値を入れては駄目なのか？~関数従属性
- なんで？
- 意外と返答に窮することがある質問
- 主キーが各列の値を一意の特定できないため
- 主キーの定義に反する
- 各列の値を一意に特定できる: 関数従属性
{学校ID} -> {学校名}
第2正規形
- 第1正規形だが第2正規形でない例
学生
| 学校ID(PK) | 学校名 | 学生ID(PK) | 学生名 | バストサイズ | ランクコード | ランク名 |
|---|---|---|---|---|---|---|
| 000A | 半蔵学院 | 000A | 葛城 | 95 | 0D | 上忍 |
| 000A | 半蔵学院 | 000C | 飛鳥 | 90 | 0E | 中忍 |
| 000A | 半蔵学院 | 000D | 柳生 | 85 | 0E | 中忍 |
| 000C | 月閃女学館 | 000A | 雪泉 | 92 | 0C | 特上忍 |
| 000C | 月閃女学館 | 000B | 叢 | 96 | 0D | 上忍 |
| 000C | 月閃女学館 | 000C | 夜桜 | 90 | 0E | 中忍 |
- 部分関数従属
- 複合主キーの一部の列に対する関数従属
{学校ID} -> {学校名}
- 完全関数従属
- 複合主キーのすべての列に対する関数従属
{学校ID,学生ID} -> {学生名}
...
- 第2正規形
- 部分関数従属がなく、完全関数従属のみのテーブルである
第2正規化を行う
正規化とは、現実世界の実体間にある階層の差を反映する手段でもある。
学生
| 学校ID(PK) | 学生ID(PK) | 学生名 | バストサイズ | ランクコード | ランク名 |
|---|---|---|---|---|---|
| 000A | 000A | 葛城 | 95 | 0D | 上忍 |
| 000A | 000C | 飛鳥 | 90 | 0E | 中忍 |
| 000A | 000D | 柳生 | 85 | 0E | 中忍 |
| 000C | 000A | 雪泉 | 92 | 0C | 特上忍 |
| 000C | 000B | 叢 | 96 | 0D | 上忍 |
| 000C | 000C | 夜桜 | 90 | 0E | 中忍 |
学校
| 学校ID(PK) | 学校名 |
|---|---|
| 000A | 半蔵学院 |
| 000C | 月閃女学館 |
- 異なるレベルの実体(エンティティ)をテーブルとしてもきちんと分離する
第2正規形でないと何が悪いのか？
- 学生情報不明の学校を登録できない
- 学校コードと学校名の対応をメチャメチャにできてしまう
| 学校ID(PK) | 学校名 | 学生ID(PK) | 学生名 | バストサイズ | ランクコード | ランク名 |
|---|---|---|---|---|---|---|
| 000A | 半蔵学院 | 000A | 葛城 | 95 | 0D | 上忍 |
| 000A | 半蔵学園 | 000C | 飛鳥 | 90 | 0E | 中忍 |
無損失分解と情報の保存
正規化の逆操作は結合。
INNER JOINで戻せる- 第3正規形までは可逆的(無損失分解)
第3正規形~推移的関数従属
推移的関数従属
学生
| 学校ID(PK) | 学生ID(PK) | 学生名 | バストサイズ | ランクコード | ランク名 |
|---|---|---|---|---|---|
| 000A | 000A | 葛城 | 95 | 0D | 上忍 |
| 000A | 000C | 飛鳥 | 90 | 0E | 中忍 |
| 000A | 000D | 柳生 | 85 | 0E | 中忍 |
| 000C | 000A | 雪泉 | 92 | 0C | 特上忍 |
| 000C | 000B | 叢 | 96 | 0D | 上忍 |
| 000C | 000C | 夜桜 | 90 | 0E | 中忍 |
学校
| 学校ID(PK) | 学校名 |
|---|---|
| 000A | 半蔵学院 |
| 000C | 月閃女学館 |
- 現状、たまたま下忍がいないだけで、ランクとしては存在する
- 忍が一人もしないランクを現状の「学生」テーブルでは登録できない
- 推移的関数従属
{学校ID,学生ID} -> {ランクコード} -> {ランク名}
第3正規化を行う
学校
| 学校ID(PK) | 学生ID(PK) | 学生名 | バストサイズ | ランクコード |
|---|---|---|---|---|
| 000A | 000A | 葛城 | 95 | 0D |
| 000A | 000C | 飛鳥 | 90 | 0E |
| 000A | 000D | 柳生 | 85 | 0E |
| 000C | 000A | 雪泉 | 92 | 0C |
| 000C | 000B | 叢 | 96 | 0D |
| 000C | 000C | 夜桜 | 90 | 0E |
学校
| 学校ID(PK) | 学校名 |
|---|---|
| 000A | 半蔵学院 |
| 000C | 月閃女学館 |
ランク
| ランクコード(PK) | ランク名 |
|---|---|
| 0C | 特上忍 |
| 0D | 上忍 |
| 0E | 中忍 |
- 誰もいないランクも登録できるようになった
- 無損失分解
INNER JOINで戻せる
