RDBMSの正規化
この記事は私が今度受ける大学の試験に向けたアウトプットになります。勉強したことをNotionにまとめていたところ、noteでまとめておけば万が一間違えてる時に誰かが指摘してくれるかもしれないという淡い期待を込めて書いています。なので参考にならない可能性もありますが、RDBMSについて理解を深めたいよという方はぜひ読んでいただけると嬉しいです。
今回はRDBMSの正規化についてまとめていきます。
正規化とはデータベース設計の手法になります。データベースは常に大量のデータを扱うため、効率的かつ効果的に管理するための設計は非常に重要になってきます。正規化では主に6段階あり、今回は第一〜第三正規形、BCNFを見ていきます。
第一正規形（1NF）
第一正規形はデータベース正規化において最も基本的な部分になります。
ここでは主に3つの項目を満たしている必要があります。
① フィールドごとに一つ以上の値を含めてはいけない
各テーブルの列には単一の値を持つべきとされており、RDBMSの関係でも説明した通り、以下のような複数の値を含むことはできません。
② 全てのフィールドのデータのタイプ（型）は同じであること
テーブルの各カラムにはそれぞれタイプ（型）が設定されています。
例えば上記のStudentテーブルのfirstnameカラムが生徒の名前を格納するVARCHARという型に設定している場合、DATE型のデータを挿入することはできません。
③ 各行は一意なキーによって識別可能であること
テーブルの各行は唯一無二であることがわかるようなユニークなキーを設定している必要があります。これは上記のテーブルの場合だとidとして主キーが与えられています。主キーは必ず一意であり、NULL値を持つことはできません。
第二正規形（2NF）
第二正規形では前提として第一正規形の項目を満たしている必要があります。そして、部分的な関数依存が排除されていることが重要になってきます。
そもそも部分関数従属とは？
部分関数従属とは文字通り部分的な関数依存のことを指しますが、部分関数従属とは複合キー (A,B) があり、その一部Aに他の属性Cが依存している場合、CはAの部分関数従属となります。実はこれRDBMSの関係で多対多の関係の時に説明したパターン①になります。以下はその際に説明した例になります。
Studentテーブルはidとclass_idを組み合わせて複合キーとなっています。理由としてはidだけだと同じ生徒が複数存在するため一意のキーとして成り立たないためです。
このidをAとし、class_idをBとした複合キー（A,B）があり、firstname,lastnameはAにのみ依存しています。依存というのはこのidからfirstname,lastnameを導き出すことができるという意味です。しかし、class_idからfirstname,lastnameを割り出すことができないため、部分関数従属となります。第二正規形ではこの状態を排除しておく必要があります。理由としてはRDBMSの関係でも説明しましたが、主にデータベースの冗長性や、更新・削除の面で効率的ではないことが挙げられます。
解決策として中間テーブルを以下のように作成し、第二正規形の要件を満たすことができます。
第三正規形（3NF）
第三正規形ではまず前提として第二正規形までをクリアしている必要があります。そして、非主要キー属性は他の非主要キー属性に依存しない、即ち推移的依存関係が排除されていることになります。
そもそも推移的依存関係とは？
例えば以下のような従業員のテーブルがあるとします。
まず、このテーブルにあるidが主キーとなっており、主キーから従業員名が割り出せます。その従業員名から部署名(Department)も決定されるため、関係があると言えます。ではIDから部署の所在地(Address)は決定されるのでしょうか？これは部署名が決定されない限り知ることができないため、部署名を通して関係しているということになります。これが推移的依存関係となります。
要約すると、
ID → Employee Name：各IDは一意の従業員名に直接関連しています。これはテーブルの主キーによる基本的な関数従属です。
Employee Name → Department：仮に従業員名が一意であれば、それに基づいて従業員が属する部署が決定されます。
Department → Address：部署名に基づいて、その部署の所在地（アドレス）が決定されます。（部署が一意に一つの場所に所在している場合）
となり、
ID → Employee Name
Employee Name → Department
Department → Address
ここで、従業員のIDから部署のアドレスを知るには、まず従業員名を経由し、その次に部署名を経由する必要があります。つまり、IDから直接アドレスを導くことはできず、部署名を介して間接的にアドレスが導かれるのです。これが推移的従属性であり、ID → Department → Address の形で示されます。
これを解決する場合、以下のようにテーブルを分けます。
新しくDepartmentテーブルを作成し、Department NameカラムとAddressカラムを設定しています。そしてDepartmentテーブルの主キーをEmployeeテーブルに関連付けることで部署名の変更が行われてもDepartmentテーブルだけを変更すればいいので効率的です。また間接的な関係であったAddressはテーブルを分けたことで不要な関係を断つことができ、冗長性を防ぐこともできます。
ボイス・コッド正規形（BCNF）
ボイス・コッド正規形はデータの完全性をさらに保証するために、厳しい条件が設定されています。第三正規形までを満たしていることに加えて、スーパーキーでない属性同士の関数従属があってはならないという決まりがあります。このスーパーキーとはテーブル内の各行を一意に識別できる属性の組み合わせです。それは一つまたは複数の属性から構成されることがありますが、重要なのは一意に識別できるという点になります。
例えば以下のようなテーブルがあるとします。
上記のテーブルはStudent ID, Major, Advisorカラムがあり、どの生徒がどの科目を取っており、どのアドバイザーが担当かがわかるようになっています。
このStudent IDが各行を識別できるスーパーキーとなります。
推移的依存の評価
第三正規形では推移的依存を解消させる必要がありました。この場合、Student IDからMajorが決定され、MajorからAdvisorが決定されます。しかしStudent IDからAdvisorは決定されません。Student IDだけではJohnかもしれないし、Amyの可能性もありえます。そのため
Student ID → Major (直接従属)
Major → Advisor (直接従属)
Student ID → Advisor (推移的従属はしていない)
となり、第三正規形はクリアしていると言えます。
スーパーキーでない属性同士の関数従属
この場合、Majorがわかれば自ずとAdvisorが決定されてしまうため、BCNFに違反してしまいます。しかし、複数の科目を同じAdvisorが担当している場合は違反になりません。テーブルの例で行くとJohnはMathematicsだけでなくComputer Scienceも担当し、AmyはPhysicsだけでなくMathematicsも担当している場合、MajorでMathematicsだとわかったところで必ずJohnになるとは限らなくなってきます。そうなるとこれは関数従属ではないためBCNFに違反していないことになります。
Majorがわかれば自ずとAdvisorが決定されてしまい、BCNFに違反している場合は以下のようにテーブルを分けることが推奨されます。
新たにMajor Advisorのテーブルを作成し、Student IDとの紐付けは中間テーブルを作成して行っています。
BCNF以降の正規形も考慮すべきか
高度に正規化されたデータベースでは、データを取り出すために多くのテーブル間の結合が必要になります。その結果、クエリがより複雑になり、また実行速度も遅くなるため、特に大きなデータセットを扱う場合にパフォーマンスの低下を引き起こす原因にもなるそうです。データベース設計は奥が深い。。。普段からDB設計をされている方には頭が上がりません。
この記事が気に入ったらサポートをしてみませんか？