DBの正規化を理解したい
ご高覧いただきありがとうございます。
株式会社Gizumoで長期研修生として研修を受け、文系大学卒、未経験からエンジニアとして現場に飛び出ていった駆け出しエンジニアです。
今回のテーマは、
DBの正規化まとめてみた
です。
データベーススペシャリストの資格を勉強している際に、
あれ？正規化の部分読んでるだけじゃ頭の中ゴチャゴチャしてよくわからないなと、、、
というわけでアウトプットしながらまとめていきます！
今回は第1正規化から第3正規化までをお届けさせていただきます。
目次
対象とする読者
本記事で対象とする読者は以下のような方を想定しています。
- データベーススペシャリストの学習をしている方
- DBやテーブル設計をこれからできるようになっていきたい方
- 今まで正規化を感覚でやっていたけど、しっかり理論から見直したい方
正規化とは
一般的には、一定のルールに従って変形することを正規化と言います。
リレーショナルデータベース(RDB)では、正規化理論に従って関係を分解するという変形を行なっていき、一つのデータは1カ所のみに存在する「1カ所1事実」が実現できます。
このように、データの重複をなくすことで、整合性のあるDBを設計していくことをDBの正規化と言います。
またその他にも、レコードの挿入、更新、削除時に起こる更新時異状を排除することも正規化の目的になります。
ただし、このとき情報無損失分解を意識して正規化することが重要です。
正規化の種類
正規化は6種類あり、第1正規形〜第5正規形とボイスコッド正規形です。
一般的には第3正規形までの正規化を行い、それよりも高次の正規化は必要な場合にしかやらないので、普段あまり見かけることはないです。
第1正規形 〜直積集合と巾集合の排除
第1正規形とは、シンプルなドメイン上で定義された関係のこと。
引用元：徹底攻略データベーススペシャリスト教科書(令和3年度)
シンプルなドメインとは、属性が全て単一値を取ることです。
次の案件テーブルを例に考えていきます。
案件テーブル
| 案件ID | 企業名(企業ID) | 言語ID | 言語名 | 人数 |
|---|---|---|---|---|
| 101 | ツクル開発(14) | 1,4 | PHP, JavaScript | 1,2 |
| 102 | デキタテ(株)(29) | 1,2,5 | PHP, Java, C++ | 2, 4, 3 |
| 103 | IT屋(5) | 3 | Python | 6 |
| 104 | IT屋(5) | 3 | Python | 2 |
このテーブルは非正規形と呼ばれ、これを第1正規形にするためにドメインをシンプルにしていきます。
まず直積集合を排除します。
直積集合とは、上の「企業名(企業ID)」のように一つのカラムに複数の値（情報）が入っている状態のことを指します。
これを排除すると以下のようになります。
直積集合を排除した案件テーブル
| 案件ID | 企業ID | 企業名 | 言語ID | 言語名 | 人数 |
|---|---|---|---|---|---|
| 101 | 14 | ツクル開発 | 1,4 | PHP, JavaScript | 1,2 |
| 102 | 29 | デキタテ(株) | 1,2,5 | PHP, Java, C++ | 2, 4, 3 |
| 103 | 5 | IT屋 | 3 | Python | 6 |
| 104 | 5 | IT屋 | 3 | Python | 2 |
次に巾(べき)集合を排除します。
巾集合とは、案件ID「101」に対する言語ID「1, 4」のように、一つの値に対して複数の値（情報）が対応するような状態のことです。
これを排除すると以下のようになります。
直積集合を排除した案件テーブル
| 案件ID | 企業ID | 企業名 | 言語ID | 言語名 | 人数 |
|---|---|---|---|---|---|
| 101 | 14 | ツクル開発 | 1 | PHP | 1 |
| 101 | 14 | ツクル開発 | 4 | JavaScript | 2 |
| 102 | 29 | デキタテ(株) | 1 | PHP | 2 |
| 102 | 29 | デキタテ(株) | 2 | Java | 4 |
| 102 | 29 | デキタテ(株) | 5 | C++ | 3 |
| 103 | 5 | IT屋 | 3 | Python | 6 |
| 104 | 5 | IT屋 | 3 | Python | 2 |
このテーブルが第1正規形のテーブルになります。
第1正規化で行うことは、
- 直積集合の排除
- 巾集合の排除
です。この二つをすることで、属性が全て単一値を取る形に変形できます。
第2正規形 〜候補キーと完全関数従属との出会い
第2正規形とは、全ての非キー属性が各候補キーに完全関数従属している関係のこと。
引用元：徹底攻略データベーススペシャリスト教科書(令和3年度)
・・・？？？😟
非キー属性とは、どの候補キーにも当てはまらない属性のことです。
先ほどの案件テーブルの場合、候補キーは「案件ID、言語ID」と「案件ID、言語名」の二つです。
非キー属性は「企業ID、企業名、人数」の三つになります。
まず、人数から見ていきます。
「案件ID、言語ID」→ 「人数」
この関係を真部分集合で成り立つかどうか考えていきます。
「案件ID」→ 「人数」
「言語ID」→ 「人数」
「案件ID101」に対して「人数1、2」の2レコード
「案件ID102」に対して「人数2、4、3」の3レコード
と、この関係ではレコードを一意に絞り込めません。
「言語ID1」に対して「人数1、2」の2レコード
「言語ID3」に対して「人数6、2」の2レコード
と、こちらも一意には絞り込めません。
全ての真部分集合で関係が成り立たないので、「人数」は候補キーに完全関数従属していると言えます。
同様に「案件ID、言語ID」→「企業ID、企業名」を考えていきます。
（企業IDに対応する企業名は重複しないとする）
「案件ID」→「企業ID、企業名」
「言語ID」→「企業ID、企業名」
「案件ID101」に対して「企業ID14、企業名ツクル開発」の1レコード
「案件ID102」に対して「企業ID29、デキタテ(株)」の1レコード
「案件ID103」に対して「企業ID5、IT屋」の1レコード
「案件ID104」に対して「企業ID5、IT屋」の1レコード
と、一つの案件IDには一つの「企業ID、企業名」が対応しています。
つまり、
「案件ID」が決まれば「企業ID、企業名」が決まるという関係が成り立ちます。
つまりつまり、
これでは完全関数従属にならないので、排除しなければいけません。
つまりつまりつまり、
「案件ID」「企業ID」「企業名」とその他の情報でそれぞれ別テーブルに分けましょう。
案件テーブル
| 案件ID | 企業ID | 企業名 |
|---|---|---|
| 101 | 14 | ツクル開発 |
| 102 | 29 | デキタテ(株) |
| 103 | 5 | IT屋 |
| 104 | 5 | IT屋 |
案件詳細テーブル
| 案件ID | 言語ID | 言語名 | 人数 |
|---|---|---|---|
| 101 | 1 | PHP | 1 |
| 101 | 4 | JavaScript | 2 |
| 102 | 1 | PHP | 2 |
| 102 | 2 | Java | 4 |
| 102 | 5 | C++ | 3 |
| 103 | 3 | Python | 6 |
| 104 | 3 | Python | 2 |
はい！ここまでが第2正規形です！
ものすごく中途半端感が否めませんが、これがギアセカンドです。
第3正規形 〜推移的関数従属性の切り離し
第3正規形とは、全ての非キー属性が候補キーに推移的に関数従属しない関係のことです。
引用元：徹底攻略データベーススペシャリスト教科書(令和3年度)
・・・？？？😟
案件テーブルを例に見ていきます。
推移的に関数従属しない関係を作るには、
「案件ID(X)」が決まれば「企業ID(Y)」が決まるという関係を残し、
「企業ID(Y)」が決まれば「企業名(Z)」が決まるという関係を切り離していきます。
なので、ここでは「企業ID(Y)」→「企業名(Z)」を別のテーブルに分けていきます。
「案件ID(X)」が決まれば「企業ID(Y)」が決まるという関係は残さなければいけないので、「企業ID(Y)」は元の案件テーブルに外部キーとして残します。
案件テーブル
| 案件ID | 企業ID |
|---|---|
| 101 | 14 |
| 102 | 29 |
| 103 | 5 |
| 104 | 5 |
企業テーブル
| 企業ID | 企業名 |
|---|---|
| 5 | IT屋 |
| 14 | ツクル開発 |
| 29 | デキタテ(株) |
はい！！これが第3正規形です！！
やっとギアサードまでできるようになりました。
さらに、案件詳細テーブルから言語テーブルを切り離して正規化します。
全体のテーブル構成の流れをまとめてみると以下のようになるかと思います。
普段見ているようなテーブルになったのではないでしょうか？
まとめ
みなさまここまでお付き合いいただきありがとうございます。
今回は非正規形から第3正規形までの道のりをまとめさせていただきました。
まだまだ僕自身が理解しながら書いているので、読みづらい部分は多々あるかと思いますがご了承ください🙇
今後もアウトプットしながら理解を深めていけたらと思います。
最後までご高覧いただきありがとうございました。
キーワード
更新時異状
更新時異状とは、DBのデータが更新によって本来の姿とは異なった状態になること。
RDBでは正規化を行わないと更新時にさまざまな異状が発生します。
この時の更新とは、単にデータを修正することだけではなく、データの挿入(タプル挿入時異状)、データの更新(タプル更新(修正)時異状)、データの削除（タプル削除時異状）の3つを指します。
関係従属性
関係従属性とは、ある属性Xの値が決まれば、別の属性Yの値が一意に決まるという性質のこと。この時Xを決定項、Yを従属項と言います。
先ほどの案件テーブルで考えると、会社の社員名簿のテーブルがあれば、社員IDが決定項で、名前が従属項となります。
情報無損失分解
情報無損失分解とは、分解した関係を自然結合した時に元に戻せる分解の仕方のことです。
正規化を行うときに、更新時異状だけを避けるために分解すると、元のデータが失われてしまうことがあります。
正規化する場合は、分解した関係を復元できる必要があることに注意しましょう。
候補キー
候補キーとは、レコードを一意に識別できる属性または属性の組みのうち極小のもののことです。
極小とは、もう一つが欠落した時に条件を満たさなくなるということです。
先ほどの案件テーブルで考えると、
案件IDと言語IDの両方が同じ属性のレコードはなく、どちらか片方だけの場合にはレコードを特定できなくなります。
なので、「案件ID、言語ID」は候補キーの一つになります。
また、言語IDに対して言語が重複しないとすると、「案件ID、言語名」も候補キーとして成り立ちます。
完全関数従属
完全関数従属とは、関数従属性X→Yにおいて、Xの全ての真部分集合X’について、X’→Yが成立しないこと。
真部分集合とは、全体集合以外の組み合わせのことです。
案件テーブルでいうと、候補キー「案件ID、言語ID」の場合は、「案件ID」と「言語ID」がそれぞれ真部分集合となります。
推移的関数従属性
推移的関数従属性とは、三つの属性に関して、「Xが決まるとYが決まる」「Yが決まるとXに関係なくZが決まる」「Yが決まってもXは決まらない」という関係のことです。
つまり、X→Y→Zのように一つずつ流れを見ていけば値がわかるという関係のことです。
