せっかくなので閃乱カグラのデータで勉強する
正規化とはなにか?
正規形の定義
正規形のレベルは第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
で戻せる