はじめに
DB設計に関してちゃんと書籍を通して学習したいと思い、「達人に学ぶDB設計 徹底指南書」を手にしました。
今回は本書でも中心的に書かれていた「正規化」ついて自分なりにまとめました。
正規化とは何か？その目的
DB設計のステップの一部(エンティティの抽出→エンティティの定義→正規化→ER図の作成)で、
抽出したエンティティをシステムで利用可能にするための作業。
データベース設計にて冗長性と非一貫性の問題を解決するために考案された方法論。
ここでいう冗長性と非一貫性は以下になります。
- 冗長性
- 一つの情報が複数のテーブルに存在してしまっている無駄なデータ領域や面倒な更新処理
- 非一貫性
- データの不整合の発生、データを登録することができないようなテーブル
正規化は第一正規形から第五正規形まであり、第三正規形まで出来ていれば十分に正規化が行われたと言っても良い。
なので、今回は「正規化」の中でも第三正規形までまとめてみようと思います。
第一正規形「スカラ値の原則」
スカラ値(scalar value)の原則というのは 「一つのセルの中には一つの値しか含まない」 というもの。
スカラ値のスカラ(scalar)は「単一の」という意味の英単語。
第一正規形が行われていない場合
以下のテーブルでは社員それぞれの子供の人数とその名前を把握するテーブルです。
現実世界でもこのような一つのセルに複数の値を入れ込む形の表はあるが、
リレーショナルデータベースの世界でダメ。
なぜなら、スカラ値の原則に違反しているから。
非正規系 正規化が行われていない社員テーブル
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 加藤 | 達夫 伸二 |
| 000B | 藤本 | |
| 001F | 三島 | 敦 陽子 清美 |
達人に学ぶDB設計 徹底指南書より
第一正規形を行う
「スカラ値の原則」に従ってテーブルを正規化してみました。
その１のテーブルでは子供の数だけ列を増すパターン。
対して、その２は子供の数だけ列を増やすパターン。
どちらでも、第一正規形の条件を満たしており、立派な第一正規化されたテーブルだが、
基本的にはその２を採用するのが望ましい。
単純にその１では子供を4人養っている社員が入社した際には列を追加しなければならない事もあります。
第一正規形その１
| 社員ID | 社員名 | 子1 | 子2 | 子3 |
|---|---|---|---|---|
| 000A | 加藤 | 達夫 | 伸二 | |
| 000B | 藤本 | |||
| 001F | 三島 | 敦 | 陽子 | 清美 |
第一正規形その2
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 加藤 | 達夫 |
| 000A | 加藤 | 伸二 |
| 000B | 藤本 | |
| 001F | 三島 | 敦 |
| 001F | 三島 | 陽子 |
| 001F | 三島 | 清美 |
達人に学ぶDB設計 徹底指南書より
なぜ「スカラ値の原則」ではないとダメなのか？
セルに複数の値を入れ込めば、 値を一意に決めることができない 。
正規化が行われていないテーブルにて社員の加藤さんのお子さん達夫くんを特定しようとしても、
「達夫、伸二」の二人を抽出してしまう。
このように Xの列が決まれば、Y列の値が決まる という形を第一正規形では取らなくてはならず、
この概念を 「関数従属性」 と言います。
第二正規形「部分的関数従属性」の解消
第二正規形ではテーブル内での部分関数従属を解消し、
完全関数従属のみのテーブルを作成することが目的としてあります。
第二正規形をどう作るか？
このテーブルの主キーが会社コード・社員IDとすると、
「会社名」の列の{ 会社コード → 会社名 }のように関数従属性があるのが見えてくる。
そして、部分的関数従属性を解消する手段として、テーブルの分割が必要であり、
「会社名」の列を独立させることによって解消していきます。
第一正規形(第二正規形ではないテーブル)
| 会社コード | 会社名 | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| C0001 | A商事 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | A商事 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | A商事 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | B商事 | 000A | 斉藤 | 47 | D03 | 営業 |
| C0002 | B商事 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | B商事 | 010A | 渋谷 | 33 | D04 | 総務 |
達人に学ぶDB設計 徹底指南書より
第二正規形されたテーブル
社員テーブル
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | 000A | 斉藤 | 47 | D03 | 営業 |
| C0002 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | 010A | 渋谷 | 33 | D04 | 総務 |
会社テーブル
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B商事 |
達人に学ぶDB設計 徹底指南書より
なぜ、第二正規形を行わないとダメなのか
従業員はいないけど、新たに会社名だけ登録したい時などに困ってしまいます。
データの誤登録のリスクも増してしまいますし、NULLを含んだレコードを挿入するわけにもいきません。
ダミーの値を登録しておくなどの回避策もありますが、根本的な解決にはなりません。
第三正規形 隠れた「推移的関数従属性」の解消
主キー以外の項目同士で依存関係を持っているもので隠れた関数従属性のことを 推移的関数従属性 と呼びます。
これらを解消していくのが第三正規形の目的。
推移的関数従属性とは？
推移的関数従属とは、関数従属関係から、新たな関数従属が成立する関係をいいます。
例えば、X → Y（YがXに関数従属）及びY → Z（ZがYに関数従属）の関係があるとき、
X → Z（ZがXに関数従属）が成立する関係が推移的関数従属になります。
データベース用語集>推移的関数従属より
下の社員テーブルでいう所の{ 会社コード、会社名 } → { 部署コード } → { 部署名 } のように
段階的な関数従属性があることを言います。
第二正規形 (第三正規形が行われていないテーブル)
社員テーブル
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | 000A | 斉藤 | 47 | D03 | 営業 |
| C0002 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | 010A | 渋谷 | 33 | D04 | 総務 |
会社テーブル
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B商事 |
達人に学ぶDB設計 徹底指南書より
第三正規形をどう作るか？
では、推移的関数従属性をどう解消していくか？
それはやはりテーブル分割になります。
「部署コード」と「部署名」を独立させます。
第三正規形のテーブル
社員テーブル
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード |
|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 |
| C0001 | 000B | 藤本 | 32 | D02 |
| C0001 | 001F | 三島 | 50 | D03 |
| C0002 | 000A | 斉藤 | 47 | D03 |
| C0002 | 009F | 田島 | 25 | D01 |
| C0002 | 010A | 渋谷 | 33 | D04 |
会社テーブル
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B商事 |
部署コード
| 部署コード | 部署名 |
|---|---|
| D01 | 開発 |
| D02 | 人事 |
| D03 | 営業 |
| D04 | 総務 |
達人に学ぶDB設計 徹底指南書より
さいごに
今回は「達人に学ぶDB設計 徹底指南書」の正規化について自分用でまとめました。
今まで感覚的に行っていた部分の視野が広がった気がします。
この書籍の中では他にも多数のDB設計に関する項目が記載されているので、ぜひ一度読んでみることをお勧めします。