データベース: RDBと第1~3正規形について
Created at Sun, Apr 19, 2015正規形は, リレーショナルデータベース設計における理論の1つです. 正規化を行うことで, データの重複をなくし, 効率良くデータを保持することが出来ます.
この記事では, リレーショナルデータベースについて説明し, 第1-3正規化について説明します.(BCNF, 第4-6正規化は次の記事で説明します)
リレーショナルデータベースモデル(RDB)について
最初にリレーションについて説明します. リレーションは見出し(heading)と本体(body)からなります. 見出しは, SQLでいうところの属性に相当します. 例えば, 生徒なら下のようなデータを持つと考えられます.
| 学生番号 | 名前 | 年齢 |
|---|---|---|
| 001 | 田中 | 17 |
| 002 | 多田野 | 19 |
| 003 | 木下 | 19 |
| 004 | 西岡 | 18 |
上記表がリレーションの一例になります. {学生番号, 名前, 年齢}が見出し, {{001, 田中, 17}, {002, 多田野, 19}, {003, 木下, 19}, {004, 西岡, 18}}が本体になります. ここで{}を使っているのは, リレーショナルにおける見出し, 本体はそれぞれタプルで表現されるためです. そのため各要素には順序が存在せず, 重複が許されません(重複があっても意味がない).
ここで集合という言葉が出てきましたが, RDBは集合論 に基づいた理論です. データを集合で表現し, 集合演算を行いデータを操作します.
データの表現の仕方が分かったので, 次にどのようにしてデータを操作するかについて説明します. SQLでいうところの, SELECT, INSERT, JOINなどのクエリに当たります. それらはクエリは, RDBの世界では集合演算で説明することができます.(OUTER JOINなど一部定義されていないものもあります)
集合演算の有名なものには以下があります.
- 直積(デカルト積): SQLの
FROMに相当. あるリレーションT1とT2のタプルを組み合わせたリレーションを返す - 差: 片方のリレーションにのみ含まれるリレーションを返す
- 積: T1, T2, 両方のリレーションに含まれるリレーションを返す
- 和: T1, T2のリレーションに含まれるタプルを含んだリレーションを返す
- 射影: SQLの
SELECT hogeのhoge部分に相当. T1に含まれるタプルの属性を選択したリレーションを返す - 制限: SQLの
WHEREに相当. 指定した条件を満たすリレーションを返す
上記演算を使うことで, 適切なデータを取得することが出来ます.
次に, 候補キー について説明します. 候補キーはタプルを一意に特定することが出来る見出しの最小の属性組 になります. 例えば, 生徒の通勤手段を表現するリレーションがあるとします.
| @学生番号 | @通勤手段 | 名前 | 年齢 |
|---|---|---|---|
| 001 | 電車 | 多田野 | 19 |
| 001 | バス | 多田野 | 19 |
| 002 | 電車 | 木下 | 19 |
| 003 | 自転車 | 西岡 | 18 |
上記リレーションの場合, {学生番号, 通勤手段}が候補キーになります. その2つの属性を指定することで, 一意にタプルを選択することが可能なためです.(候補キーには@をつけています)
また, 候補キーに似た概念にスーパーキーがあります. 上記で候補キーは「見出しの最小の属性組」と説明しました. しかし, スーパーキーは「最小」でなくて良く, タプルを特定できれば, いくつ見出しを含んでもかまいません. {学生番号, 通勤手段, 名前}, {学生番号, 通勤手段, 名前, 年齢}などがスーパーキーになります. 実際にリレーションを作るときは, どの属性組が候補キーになるかを考慮することが大切です.
第1正規形
第1正規形はリレーションであることの必須条件になります. 第1正規形の定義は以下になります.
- 繰り返しデータがない, データが一意に定まる
- ある属性に複数の要素(リスト)が含まれていると, あるタプルを選択した時に, リスト内のどの要素を示しているかによって値が一意に求まらないので, 第1正規形を満たさなくなります. また, NULLを持つ場合にも, データが一意に定まらないため, 第1正規形を満たさなくなります.
- データに重複がない
- 順序が定まっていない
例えば, 下のリレーションは第1正規形ではありません.
| 学生番号 | 通勤手段 | 名前 | 年齢 |
|---|---|---|---|
| 001 | 電車,バス | 多田野 | 19 |
| 002 | 電車 | 木下 | 19 |
| 003 | 自転車 | 西岡 | 18 |
なぜなら, 通勤手段が一意に定まらないためです. なので, 下のように変換して上げる必要があります.
| @学生番号 | @通勤手段 | 名前 | 年齢 |
|---|---|---|---|
| 001 | 電車 | 多田野 | 19 |
| 001 | バス | 多田野 | 19 |
| 002 | 電車 | 木下 | 19 |
| 003 | 自転車 | 西岡 | 18 |
これで, {学生番号, 通勤手段}を指定することで, 一意にタプルが指定できるようになりました.
余談: NULLについて
最初に言っておくと, NULLは値ではありません . NULLは値がまだ決まっていないことを指し示すマーカーのようなものです. 空文字列などとは全く異なる概念です. NULLがなにかというと, いってみればどんな値でも入り得る箱のようなものです. “Hello"が入るかもしれないし, “プギャー"が入るかもしれない. それは, 一意に値が定まらないことを意味しています. データが一意に決まらないということは, RDBの理論から外れてしまうので, 最大限NULLが入らないような論理設計にすべきだと思います.
第2正規形
最初に関数従属について説明します. 候補キーの真部分集合T1から, 非キー属性(候補キー以外の属性)T2が一意に求まるときに, T2はT1に関数従属しているといいます. 第2正規形は, 関数従属を取り除く作業になります.
実際に第2正規化を行って説明します. まず最初に, 関数従属があると何が問題かについて説明します.
| @学生番号 | @通勤手段 | 名前 | 年齢 |
|---|---|---|---|
| 001 | 電車 | 多田野 | 19 |
| 001 | バス | 多田野 | 19 |
| 002 | 電車 | 木下 | 19 |
| 003 | 自転車 | 西岡 | 18 |
一見, 問題がなさそうなリレーションに見えます. しかしある操作をすると, 問題があることが分かります. 例えば, 2行目の多田野をTDNに更新します.
| @学生番号 | @通勤手段 | 名前 | 年齢 |
|---|---|---|---|
| 001 | 電車 | 多田野 | 19 |
| 001 | バス | TDN | 19 |
| 002 | 電車 | 木下 | 19 |
| 003 | 自転車 | 西岡 | 18 |
学生番号001の学生の名前が{多田野, TDN}になってしまいました. これは明らかにおかしいです. これは, 非キー属性{名前}は, {学生番号}に従属しているためです. 分かりやすくいうと, リレーション内にデータの重複があるとためです. なので, リレーションを分割します.
| @学生番号 | @通勤手段 | 年齢 |
|---|---|---|
| 001 | 電車 | 19 |
| 001 | バス | 19 |
| 002 | 電車 | 19 |
| 003 | 自転車 | 18 |
| @学生番号 | 名前 |
|---|---|
| 001 | 多田野 |
| 002 | 木下 |
| 003 | 西岡 |
また年齢も, {学生番号}に関数従属しているので分割します.
| @学生番号 | @通勤手段 |
|---|---|
| 001 | 電車 |
| 001 | バス |
| 002 | 電車 |
| 003 | 自転車 |
| @学生番号 | 名前 | 年齢 |
|---|---|---|
| 001 | 多田野 | 19 |
| 002 | 木下 | 19 |
| 003 | 西岡 | 18 |
これで第2正規形の完成です. これで関数従属によるデータの重複, データ不整合を防ぐことが出来ます.
第3正規形
最初に推移的関数従属について説明します. 候補キーT1から, 非キー属性T2が決まり, さらに非キー属性T3が決まるとします. その時, T3は推移的従属性があると言います. T3は, T1, T2から推移的に従属しているためです. 第3正規形では推移的従属性を取り除く作業になります.
実際に第3正規化を行い説明します. 下のリレーションがあるとします.
| @学生番号 | 名前 | 年齢 | 授業名 | 講師名 |
|---|---|---|---|---|
| 001 | 多田野 | 19 | 数学 | 遠野 |
| 002 | 木下 | 19 | 国語 | 谷岡 |
| 003 | 西岡 | 18 | 数学 | 遠野 |
これも一見良さそうですが問題があります. 学生番号001の講師名を英語に更新してみます.
| @学生番号 | 名前 | 年齢 | 授業名 | 講師名 |
|---|---|---|---|---|
| 001 | 多田野 | 19 | 数学 | 田所 |
| 002 | 木下 | 19 | 国語 | 谷岡 |
| 003 | 西岡 | 18 | 数学 | 遠野 |
数学の教師が{田所, 遠野}の2人になってしまいました. これは問題があります.(同じ授業には同じ講師が出る暗黙的ルールがあるとします) これは問題です. これは, 講師名は, 授業名から一意に求めることが出来る, {学生番号} => {授業名} => {講師名}の推移的従属性があるためです. なので, リレーションを分割します.
| @学生番号 | 名前 | 年齢 | 授業名 |
|---|---|---|---|
| 001 | 多田野 | 19 | 数学 |
| 002 | 木下 | 19 | 国語 |
| 003 | 西岡 | 18 | 数学 |
| @授業名 | 講師名 |
|---|---|
| 数学 | 遠野 |
| 国語 | 谷岡 |
これで, 推移的関数従属がなくなり, 第3正規形を満たしました.
まとめ
第1~3正規形をまとめると,
- 第1正規形: リレーションになるための最低条件
- 第2正規形: 関数従属性の除去
- 第3正規形: 推移的関数従属性の除去
になります.
第1~3正規化は重複を防ぐための, 最低限のリレーション変換なので慣れておく必要があります.
リレーショナルデータベースは, 歴史も長く, 数学的な理論に裏付けされた素晴らしい理論だと思います. NoSQLなどの技術が台頭しても, RDBの理論, 技術は大切だと思うので, 忘れずに今後も磨いていきます. BCNF以降の正規形については, 後日アップします.