正規化について勉強したことをまとめてみる 〜その２〜

経緯

こちらの記事の続きです。

１つの記事にするには文章量が多すぎたため、２つに分けています。

対象者

• DB設計を勉強している、または興味がある人
• 第１〜３正規形までを勉強済みの人（まだの方は、前回の記事を先に読んでみてください）
• ボイス-コッド〜第５正規形を勉強したい人
• 達人に学ぶDB設計を読んだことがある、または興味がある人

記事の内容

この記事で書くこと

ボイス-コッド〜第５正規形について（順番に章を分けてまとめます）

また、以下はこの記事で扱うキーワードです。（前回の記事と同じなので飛ばしても大丈夫です）

---

キーワードの定義

1. 以下のような表のことを「テーブル」と呼ぶ

項目１	項目２	項目３
データA	データB	データC

2. ↑のテーブルの縦列または「項目１」のようなデータの項目のことを「カラム」と呼ぶ
3. ↑のテーブルの横列のことを「レコード」と呼ぶ
4. ↑のテーブルの「データA」のような値が入るマスを「セル」と呼ぶ

---

本文

今回の記事では「高次正規形（ボイス-コッド〜第５正規形）」と呼ばれる正規形についてまとめます。

ボイス-コッド正規形

この章でまとめるのは「ボイス-コッド正規形」です。
これまでの第１、第２・・・といった単純なナンバリングとは外れた命名がされていますね。

これは、ボイスさんとコッドさんという2人の考案者の名前から取られているんだそうです。
第３正規形と第４正規形の間に位置する正規形なので、非公式ではありますが第3.5正規形と呼ぶ人もいます。

では、なぜこれだけ命名規則から外れた名前になっているのかというと、ボイス-コッド正規形は第３正規形をより厳密にしたもの＝第３正規形の仲間だからです。

前置きが長くなりましたが、そろそろボイス-コッド正規形について詳しく見ていきましょう。

まずは、ボイス-コッド正規形にするための条件を紹介します。

非キーから（主）キーへの関数従属が存在しない

非キーとは、その名の通りキーではないものを指す名称です。

それでは、具体的にどういうことか確認していきます。

以下は、ボイス-コッド正規形を満たしていないテーブルです。

社員ID	チームコード	チーム補佐
001	T1	010
001	T2	094
002	T1	010
003	T1	031
004	T2	079

このテーブルの主キーは社員IDとチームコードです。

このテーブルについて簡単に説明すると、

- 社員とその所属チームの関係を示しており、社員は複数のチームに属することができる。
- チームにはチーム補佐という役割があり、この役割を持つ人はチーム内の社員の補佐を担当する。
  - このチーム補佐は、チーム内に複数人いる場合もある。
    一方で、1人が複数チームの補佐を担当することはできない（チームと補佐は１対多の関係）。

のようになる想定です。

この時、このテーブルには以下の関数従属があると言えます。

{社員ID, チームコード} -> {チーム補佐}
{チーム補佐} -> {チームコード}

このテーブルには、部分関数従属も推移的関数従属もありません。よって、第３正規形の条件を満たしています。
しかし、まだボイス-コッド正規形とは呼べません。

理由は、非キーからキーへの関数従属が存在するからです。

どこを指しているのかというと、

{チーム補佐} -> {チームコード}

この関数従属です。

先ほど、「このテーブルの主キーは社員IDとチームコードである」と説明しました。
つまり、チーム補佐は主キーではありません。

主キーでないということは、非キーであるということ。
よって、このテーブルはボイス-コッド正規形の条件を満たしていません。

この状態の問題点として、以下の３つが挙げられます。

1. チーム補佐がチームを変える場合に複数行の更新処理が発生する（データの冗長性）
2. 社員がチームに参加するまで、チーム補佐とチームの関連を登録できない
3. 社員がチームから外れた場合、レコードを削除するとチーム補佐とチームの関連も削除される可能性がある

こうした不整合を解消する方法が、ボイス-コッド正規形なのです。

ボイス-コッド正規形の条件を満たすためには、テーブルの分割が必要です。
非キーからキーへの関数従属を解消できる形で、テーブルを分割してみましょう。

例えば、以下の２つが考えられそうですね。

例１）社員とチーム補佐を別テーブルに分割する

• 社員-チーム

  社員ID	チームコード
  001	T1
  001	T2
  002	T1
  003	T1
  004	T2

• チーム補佐-チーム

  チーム補佐	チームコード
  010	T1
  031	T1
  094	T2
  079	T2

例２）社員とチームを別テーブルに分解する

• 社員-チーム補佐

  社員ID	チーム補佐
  001	010
  001	094
  002	010
  003	031
  004	079

• チーム補佐-チーム

  チーム補佐	チームコード
  010	T1
  031	T1
  094	T2
  079	T2

上記の例では、どちらもチーム補佐の方を主キーとすることで、非キーから主キーへの関数従属を解消しています。

さて、これらは、決してどちらを採用してもいいというわけではありません。

例１には、致命的な問題が含まれています。元のテーブルに戻すことができないのです。

試しに、チームコードを結合キーとして例１の２つのテーブルを結合させてみると、以下のようになります。

社員ID チームコード チーム補佐
----- --------- --------
001   T1        010
001   T1        031        ×
001   T2        094
001   T2        079        ×
002   T1        010
002   T1        031        ×
003   T1        010        ×
003   T1        031
004   T2        094        ×
004   T2        079

元のテーブルよりも明らかにレコード数が増えていることがわかるでしょうか？

×を書いたレコードは、元のテーブルには存在しなかったレコードです。
結合を行ったことで、存在しないはずのデータが生まれてしまっています。

原因は、チームコードをキーにした結合では、テーブル同士が多対多の関係になってしまっていることです。

第２正規形でも説明した通り、正規化は可逆的でなければならないという観点から、常に１対多の関係になっていなければなりません。

ボイス-コッド正規形では、注意しておかないとこのような非可逆な分解を行なってしまう危険があるのです。

では、なぜテーブルが多対多の形になってしまったのかというと、元のテーブルにあったチーム補佐とチームコードの関数従属性を失ってしまったからです。

言い換えれば、元のテーブルにある関数従属性が保存できていれば、１対多の状態を保つことが可能ということです。

さて、ここまでで、例１が条件を満たしていないことがわかりました。
例２はどうでしょう？ボイス-コッド正規形の条件を満たしているでしょうか。

例２は社員とチームをそれぞれ別テーブルに分割させています。

関数従属性を確認すると、それぞれ以下のようになっていますね。

• 社員-チーム補佐

  {社員ID} -> {チーム補佐}
  

• チーム補佐-チーム

  {チーム補佐} -> {チームコード}
  

元の関数従属性が全て保存されています。

つまり、このテーブル分割は可逆的なので、正規形の条件を満たしています。

さらに、これら２つのテーブルは、全てのカラムが主キーに従属しています。
非キーからキーへの従属は１つもありません。

したがって、例２はボイス-コッド正規形の条件を満たしています！

あり得ないデータの登録を防ぐ

ボイス-コッド正規形は、以上で完成しました。
これ以上テーブルを操作する必要はありません。

一方で、実はこのままだとデータベースに本来あり得ないデータが登録されてしまう恐れがあるのです。

ではもう一度、例２のテーブルを見ていきましょう。

• 社員-チーム補佐

  社員ID	チーム補佐
  001	010
  001	094
  002	010
  003	031
  004	079

• チーム補佐-チーム

  チーム補佐	チームコード
  010	T1
  031	T1
  094	T2
  079	T2

例えば社員-チーム補佐テーブルには以下のようなデータを登録することが可能です。

社員ID：002
チーム補佐：010

しかし、この組み合わせはチーム補佐-チームと見比べるとおかしい部分が見つかります。

社員ID：002の社員は、もともとチームコード：T2のみに所属しています。

チーム補佐：010は、チームコード：T1のチームに所属しています。

そして、このチーム補佐：010が社員ID：002と紐付くということは、社員ID：002の社員がチームコード：T1のチームに所属するようになってしまうことを暗黙に意味してしまうことになります。

これは、一概に誤りとは断言できません。
というのも、上記の仕様が業務ルール上で認められているのであれば、何の問題もないからです。

一方で、業務ルールとして存在しない場合は、アプリケーション側のロジックで、上記のようなデータが登録されないよう制御する必要があります。

このように、ボイス-コッド正規形は、テーブルを作っただけで完成した気になっていたら痛い目を見そうな正規形のようです。

業務で使わなければならない場面に遭遇したら、上記の例のような状態になる場合があることを踏まえて設計し、必要であればアプリケーション側で制御したり、ボイス-コッド正規形を使わない判断をするようにしましょう。

第４正規形

続いて、第４正規形について見ていくのですが、その前に１つ、第４正規形を理解するために重要な前知識を紹介します。

関連エンティティ

例として使用するのは以下のテーブルです。

• 社員-チーム

  社員ID	チームコード
  001	T1
  001	T2
  002	T1
  003	T1
  004	T2

社員ID：001のように複数のチームに所属している社員もいれば、社員ID：002のように１つのチームにだけ所属している社員もいます。

社員とチームとの関係は、多対多です。

したがって、この社員とチームの所属関係を正確に表すには、社員IDとチームコードの２つを主キーとして見る必要があります。
つまり、このテーブルは主キーのみで構成されているということです。

このように、複数のエンティティ同士の関連を表現するエンティティのことを、関連エンティティと呼びます。

第４正規形、第５正規形は、この関連エンティティに対して発生する正規形であり、第３正規形までで扱ってきた具体的なエンティティによる正規化では発生しません。

さて、前知識が確認できたので、話を先に進めましょう。

詳細を説明する前に、第４正規形を満たすための条件を紹介します。

１つのテーブルに、独立な多値従属性が１つだけ存在していること。

「多値従属性」という新しいワードが出てきましたね。

まずは、この多値従属性についての説明から始めていきます。

多値従属性

先ほどの社員-チームテーブルは、第３正規形の条件（正確に言うとボイス-コッド正規形まで）を満たしていることがわかるでしょうか。

このテーブルは、部分関数従属も推移的関数従属もありません。（さらに、非キーからキーへの関数従属もありません。）

では、このテーブルにカラムを付け足して、次のように変更してみます。

• 社員-チーム-製品

  社員ID	チームコード	製品コード
  001	T1	PRD111
  001	T2	PRD222
  002	T1	PRD111
  002	T1	PRD222
  003	T1	PRD222
  003	T2	PRD222
  004	T2	PRD111

追加したカラムについて、想定している仕様を少し説明します。

- 製品コードは、社員が開発に携わっている製品を表す
- 1人の社員が複数の製品を担当することもあるし、
  複数の社員が１つの製品を共同で開発していることもある

こんな感じでしょうか。

そして更に、このテーブルも第３正規形を満たしています。

部分関数従属も推移的関数従属もなく、非キーからキーへの従属もありません。

一方で、このテーブルには不都合な点があります。

例えば、社員ID：002の社員がチームを移動することになった場合、２つのレコードに更新をかける必要が出てきます。

これは、データベース構造が冗長であることを示していますね。

また、全てのカラムに値が入っていないと新しいデータを追加できないという点もあります。

開発には携わっているのにチームに所属していない社員がいた場合、この社員がチームに所属するまでレコードの追加ができません。

なぜこのようなことが起こっているのかというと、このテーブルが関数従属性と異なる特徴を持っているからです。

このテーブルの従属性は、キーと非キーの間ではなく、キーと「集合」の間に成立しています。

例えば、社員ID１つに対して紐づくチームコードは複数です（１対多）。
同様に、製品コードも、社員IDを１つに対して複数紐づきます。

この複数のグループを、集合と呼んでいます。

つまり、社員IDというキーに、チームや製品という集合が対応しているということです。

このようなキーと集合との対応を、多値従属性と呼びます。

多値従属性は、以下のように->->で表します。

{社員ID} ->-> {チームコード}
{社員ID} ->-> {製品コード}

また、チームと製品の間には特に関係がないため、従属性もありません。
このような場合は２つの従属性を|で区切ってまとめて表記することもできます。

{社員ID} ->-> {チームコード | 製品コード}

第４正規形は、上記のような独立な多値従属性が複数存在するテーブルを従属性ごとに分割することで作られるのです。

今回の例では、{社員ID} ->-> {チームコード}と{社員ID} ->-> {製品コード}をそれぞれ分割してやれば良さそうですね。

• 社員-チーム

  社員ID	チームコード
  001	T1
  001	T2
  002	T1
  003	T1
  003	T2
  004	T2

• 社員-製品

  社員ID	製品コード
  001	PRD111
  001	PRD222
  002	PRD111
  002	PRD222
  003	PRD222
  004	PRD111

こんな感じです。
それぞれのテーブルには、独立な多値従属性が１つだけ存在しています。

これで、例えば社員ID：002の社員がチームを移動した場合も、更新するレコードは１つで済みますね。

本来、複数の多値従属性を１つのテーブルで表現するのは、かなり無理のある設計です。

しかし、先ほどの社員-チーム-製品テーブルは、第３正規形を満たしていながら冗長性のあるテーブルとして成立しているため、論理的には作ることが可能なのです。

このような状態になるのを明示的に禁止するのが第４正規形の意義です。

以下のルールを守っていれば、テーブルは「自然と」第４正規形になります。

関連エンティティを作る場合は、そこに含まれる関連は、１つだけにすること

第５正規形

第５正規系は、第4正規形を発展させたものです。

第５正規形の満たすためには、次のような設計上のルールが必要になります。

関連がある場合は、それに対応する関連エンティティを作ること

第４正規系の章でも少し触れた通り、第５正規系も関連エンティティに対して発生する正規形です。

具体的にどのような作業が必要なのか、以下で説明していきます。

第４正規形で使った社員-チーム-製品のテーブルを覚えているでしょうか？

この例では、以下のような従属性がありました。

{社員ID} ->-> {チームコード | 製品コード}

では、ここに次のような多値従属性を追加したらどうでしょうか？

{チーム} ->-> {製品}

業務上のルールで考えると、「チームによって扱う製品が決まっている」のようになるでしょうか。

こうなると、第４正規形で作ったテーブルでは、チームと製品の関係まで表すことが難しくなります。
チームIDと製品IDは、同じテーブル上にないんですから。

ではどうすればいいかというと、関連エンティティを新しく作ってやればいいのです。
第４正規形でもやったように、テーブルを１つ増やしてみましょう。

• 社員-チーム

  社員ID	チームコード
  001	T1
  001	T2
  002	T1
  003	T1
  003	T2
  004	T2

• 社員-製品

  社員ID	製品コード
  001	PRD111
  001	PRD222
  002	PRD111
  002	PRD222
  003	PRD222
  004	PRD111

• チーム-製品　←new!

  チーム	製品コード
  T1	PRD111
  T1	PRD222
  T2	PRD111
  T2	PRD222

チーム-製品のテーブル、つまり関連エンティティを新しく作成しました。

これで、第５正規系は完了です。
第４正規形までを理解している方なら、結構簡単だったんじゃないでしょうか。

第４正規形との違い

第４正規形から発展させただけあって、どっちがどっちだったか分からなくなりそうです。
（僕だけ？）

なので、今一度、第4正規形と第５正規形がそれぞれ何をしていたのか振り返ってみたいと思います。

第４正規形
独立な多値従属性が複数存在するテーブルを、従属性ごとに分割する

第５正規形
関連がある要素に対応する関連エンティティを、新しく作成する

どちらも「関連エンティティを作成する」という点では同じ作業をしていますが、それぞれ目的が異なるようですね。

この違いを自分の言葉で言語化できるようになると、理解が進みそうな気がします。

最後に

この記事では、達人に学ぶDB設計をを参考に、ボイス-コッド〜第５正規形についてまとめました。

最後に、前回の記事と同様に、これまで例として登場したテーブルを順番に並べてみようと思います。
せっかくなので、第１正規形から第５正規形まで全部並べちゃいましょう！

【正規化前】

社員ID	社員名	子供
001	山田 太郎	次郎、三郎、四郎
002	佐藤 花子	桜子、百合子
003	中村 和夫	玲子
004	藤井 春子

【第１正規形】

社員ID	社員名	子１	子２	子３
001	山田 太郎	次郎	三郎	四郎
002	佐藤 花子	桜子	百合子
003	中村 和夫	優子
004	藤井 春子

または

社員ID	社員名
001	山田 太郎
002	佐藤 花子
003	中村 和夫
004	藤井 春子

社員ID	子
001	次郎
001	三郎
001	四郎
002	桜子
002	百合子
003	玲子

【第２正規形】

会社コード	社員ID	社員名	年齢	部署コード	部署名
c-1	001	山田 太郎	50	d01	営業
c-2	002	佐藤 花子	37	d02	人事
c-2	003	中村 和夫	23	d03	開発
c-1	004	藤井 春子	41	d04	総務

会社コード	会社名
c-1	A商事
c-2	B工業
c-2	B工業
c-1	A商事

【第３正規形】

会社コード	社員ID	社員名	年齢	部署コード
c-1	001	山田 太郎	50	d01
c-2	002	佐藤 花子	37	d02
c-2	003	中村 和夫	23	d03
c-1	004	藤井 春子	41	d04

会社コード	会社名
c-1	A商事
c-2	B工業
c-2	B工業
c-1	A商事

部署コード	部署名
d01	営業
d02	人事
d03	開発
d04	総務

【ボイス-コッド正規形】

社員ID	チーム補佐
001	010
001	094
002	010
003	031
004	079

チーム補佐	チームコード
010	T1
031	T1
094	T2
079	T2

【第４正規形】

社員ID	チームコード
001	T1
001	T2
002	T1
003	T1
003	T2
004	T2

社員ID	製品コード
001	PRD111
001	PRD222
002	PRD111
002	PRD222
003	PRD222
004	PRD111

【第５正規形】

社員ID	チームコード
001	T1
001	T2
002	T1
003	T1
003	T2
004	T2

社員ID	製品コード
001	PRD111
001	PRD222
002	PRD111
002	PRD222
003	PRD222
004	PRD111

チーム	製品コード
T1	PRD111
T1	PRD222
T2	PRD111
T2	PRD222

どどん！

多いですね！！

これらの特徴を覚えるのも大事だと思いますが、実際に正規化をやってみて、経験で覚えるのが一番だと思います。

ということで皆さん、正規化をしましょう！