この記事は何か
SEならだれでも一度は勉強する(よね？) データベースの正規化。でも、 関数従属とか言われてよく分からなくなる こと、ありますよね。
ということで、 もう少し具体的なイメージやキーワード を使って、分かりやすく記憶に残る説明ができないか、と考えて書いてみたのがこの記事です。
例えば、「 第2正規化 ってなに？」と聞かれたときに「 部分関数従属の解消 っすよね」と即答するのは、なかなか難しいと思うのです。
でも、この記事を読んでおくと「えーと。なんか細かくは覚えてないっすけど、明細テーブルからヘッダテーブル取り出せ、みたいなイメージのやつ っすよね」くらいの回答ができるようになります。少なくとも、そうなってほしいと思って書いています。ならなかったらスミマセン。
対象読者はSE 3年目くらい(DB、テーブル、レコードといった言葉が分かる方)を想定しています。
全体がとんでもなく長くなってしまったので、以下に各章へのリンクを貼っておきます
各章へのリンク
はじめに ：前提の説明など
第1正規形 ：セルを結合するな、ひとつのセルに複数の値を入れるな、全部バラせ
第2正規形 ：明細テーブルからヘッダテーブルを取り出せ
第3正規形 ：マスタ化できる列をマスタ化して取り出せ
ボイス-コッド正規形 ：より絞り込みの強い列で主キーを組みなおして取り出せ
第4正規形 ：単純掛け合わせテーブルを作るな
第5正規形 ：単純掛け合わせテーブルを作るな
第6正規形 ：非キー列は1列だけ
0. はじめに
各正規形の説明の前に、用語の準備や、本記事の参考情報を記載しておきます。
0-1. テーブルとその構成要素
本記事では、テーブルの構成要素として、レコード、列、セル、主キーという言葉を用います。レコード、列、セルのイメージは以下の通りです。（主キーについては後述します）
0-2. 主キーとは
主キーとは、その値を指定すれば、必ず1行のレコードを特定できるような列の組合せです。一つのテーブルに対して必ず一つ存在しなければならず、かつ、一つしか存在しません。
例えば、以下はチームと所属メンバーのテーブルですが、主キーは チーム と メンバー です。
チームだけではどのレコードか特定できず、
メンバーだけでもどのレコードか特定できませんが、
両方を指定すると、レコードが特定できます。
0-3. 単純掛け合わせテーブルについて
本記事を初めて読む方は、この章をスキップして先へ進んでください。もし本記事の後半を読んでいて「あれ？単純掛け合わせってなんだ？」と思ったら、ここに戻ってくればよいです。(特に疑問を抱かずに最後までこの記事を読めたなら、この章を読む必要はありません)
本記事では、後半に「単純掛け合わせ」という表現が出てきます。
これは、正確には「自然結合(Natural Join)」と呼ばれる処理で以下のように説明できます。
SQLを知っている方への説明
例えば以下の2テーブルに対して、
チーム
チームメンバー
以下のように、 両テーブルに存在する列を一致条件に、全列を取得するSQLを実行した結果 です。
select T1.チーム名, T1.担当, T2.メンバー, T2.学年 -- 全ての列を羅列。両テーブルにある列は一方から取得
from チーム T1, チームメンバー T2
where T1.チーム名 = T2.チーム名 -- 両テーブルに存在する列を一致条件に指定
結果は以下になります。
単純掛け合わせ結果
SQLを使わない説明
例えば以下の2テーブルがある場合に、両方に共通する列「チーム名」を使って、「チーム」に「チームメンバー」の列を追加していきます。
チーム
チームメンバー
まず、チーム名が「トラ」のレコードです。
以下のように、チームメンバーの列(すでに存在するチーム名を除く)を追加します。
次に、「サル」です。
サルの場合、「チーム」1レコードに対し、「チームメンバー」2レコードが存在します。
このように、一方のレコードが少ない場合、多い方のレコード数に合わせて複製してから、列を追加します。
最後に「アライグマ」です。
アライグマの場合、チームメンバーにレコードが存在しません。
このように、一方にしかレコードが存在しない場合は、全体からレコードを削除します。
出来上がりは以下となります。
0-4. 参考書
本記事の内容は私が起こしたものですが、用語の定義や使い方については、なるべく、以下の書籍にあわせています。
達人に学ぶDB設計 徹底指南書 Kindle版 (ミック著)
正直、この書籍が手放しでお勧めできるかといわれると、いろいろ思うことはあるのですが、DBの初学者が使いやすそうな書籍で、最低限まとまっているものが、私の調べた範囲ではこれくらいしか見当たらず。
ということで、より詳細な内容が知りたい方は、本記事を読んだ後に、上の書籍を読んでみてもいいかもです。（2024年10月現在、既に2版が出ているようなので、これから購入するならそちらがよいかと思います）
1. 第1正規形
実は、正確な説明が一番難しいのは第1正規形ではないかと思ったりします。
ですがまあ、それはそれとして説明を始めていきます。
1-1. 第1正規形のイメージ
第1正規形(第1正規化)のイメージは 「セルを結合するな、ひとつのセルに複数の値を入れるな、全部バラせ」 です。
セルを結合するな。バラせ
例えばこういう結合セルがあったら
こんな感じにバラして、各行に同じ値を埋めます。
ひとつのセルに複数の値を入れるな。バラせ
例えば、こんな感じで一つのセルに複数の値を持つセルがあったら
こんな感じで行にバラします。(他の列にはもともとの値をそのままコピーします)
1-2. なぜ第1正規形が必要なのか
いきなりアレですが、なぜ第1正規形が必要かを説明するのは結構難しいです。
ここでは、そもそもRDBが想定している「テーブル」は、セルの結合がなく、一つのセルに複数の値が入っていることもないのが大前提だから、という説明にさせてください。身も蓋もなくてスミマセン。
1-3. もう少し正確な説明
第1正規形の定義は 「一つのセルの中には一つの値しか含まない」 というものです。
これについては、あまり追加説明の必要はないかと思います。一つ目のイメージのようにセルを結合されていると、「一つのセル」が何なのか分からなくなるし、二つ目のイメージは定義そのものなので。
1-4. 補足
二つ目のイメージは以下のように列を横に展開してもよいのでは、と思った方がいるかもしれません。これも「一つのセルの中には一つの値」ではあるので。実際、参考書では、これを第1正規形として認めています。(私は認めない派ですが)
ですが、このレイアウトは、仮に第1正規形と認めたとしても、明確なアンチパターン1で、基本的に採用すべきではないです。同じ意味のものは一つの列のみ用意し、複数出現する場合は、レコードを追加して格納してください。
2. 第2正規形
主キー列と非キー列の関係を整理する正規形です。
2-1. 第2正規形のイメージ
第2正規形(第2正規化)のイメージは 「明細テーブルからヘッダテーブルを取り出せ」 です。
明細テーブルからヘッダテーブルを取り出せ
例えば、以下のテーブルを考えます。いつ・だれが・どんな商品を・いくつ注文したかを管理するテーブルです。
注文明細テーブル
なお、注文番号・明細番号の定義は以下とします。
- 注文番号：注文ごとに付与する一意な番号。この番号が同じなら同じ注文。違うなら別の注文
- 明細番号：各注文に対して、その商品が明細の何番目に載っていたかを表す番号
ここで、注文日・利用者・利用者連絡先・注文番号に注目すると、同じデータが複数行に渡って繰り返し格納されていることに気付くかと思います。
これは当然で、一回の注文において、注文日や利用者(注文者)等は、じゃがいもやタマネギの各明細レコードに依存せず、共通の値を取るからです。これらの「明細に依存しない列」を、以下のように1注文1レコードの「注文テーブル」に取り出して、繰り返しをなくすのが第2正規化です。
注文テーブル
※重複レコードを削除し、一意なレコードのみを残します。1注文1レコードになり、繰り返しが解消されます。
注文明細テーブル
※別テーブルへ分解した列は、元テーブルから削除します。ただし、どの注文かが分かるように注文番号だけは残します。
この注文テーブルのように、明細に依存しない共通データを持つテーブルを、明細テーブルに対して「ヘッダテーブル」や「見出しテーブル」のような呼び方をします。
ですので、第2正規化は 「明細テーブルからヘッダテーブルを取り出せ」 と言いかえることが可能です。
もちろん、これだけが第2正規形の作り方ではないのですが、実際の業務でもこのパターンは頻出するので、一旦これが第2正規化のイメージだ、と捉えてみてよいと思います。
2-2. なぜ第2正規形が必要なのか
第2正規形になっていないと、例えば1回の注文のデータが複数のレコードで保持されるため、以下のように矛盾したデータが作れてしまうからです。
第2正規形になっていれば、1回の注文のデータは1レコードになっている(分解したときに重複レコードを削除した)ため、矛盾データが発生しません。
このように、 本来1レコードで管理できるはずのデータを、複数行にわたって繰り返すことで矛盾データが作れてしまう、というのは正規化で非常に重要な観点 です。複数列にわたって同じデータの繰り返しを見つけたら、非正規形になっていないか疑ってみる必要があります。
2-3. もう少し正確な説明
第2正規形の定義は、そのテーブルが第1正規形、かつ、 「部分関数従属が解消されていること」 です。
関数従属 とは、ある列(複数列の組合せも可)の値が決まると、他の列の値が決まることです。
部分関数従属 は、主キーの一部の列に対して関数従属する列があることです。
以下、イメージで使用したテーブルを用いて、説明してみます。
まず前提として、このテーブルは一つのセルに一つの値しかないため、第1正規形です。
注文明細テーブル
既に説明したとおり、注文番号・明細番号の定義は以下です。
- 注文番号：注文ごとに付与する一意な番号。この番号が同じなら同じ注文。違うなら別の注文
- 明細番号：各注文に対して、その商品が明細の何番目に載っていたかを表す番号
この2列は、注文明細テーブルの 「主キー」 になっています。この2列の値が決まると1行が特定できるからです。
まず、 関数従属 です。
関数従属 は「ある列(複数列の組合せも可)の値が決まると、他の列の値が決まること」でした。
例えば、注文番号・明細番号の組合せ、つまり、そのテーブルの 主キーはすべての列を関数従属させる と言えます。主キーの値が決まると、1行が特定されるので、すべての列の値が決まるからです。
では、注文番号だけではどうでしょうか。
例えば、注文番号は、商品を関数従属させません。注文番号が決まっても、商品の値は決まらないからです。
しかし、注文番号は、注文日・利用者・利用者連絡先を関数従属させます。複数のレコードにまたがってはいますが、注文番号が決まると、これらの列の値が決まるからです。
次に、 部分関数従属 です。
部分関数従属 は、「主キーの一部の列に対して関数従属する列があること」でした。
既に見たように、主キーの一部である注文番号は、注文日・利用者・利用者連絡先を関数従属させます。このテーブルには 部分関数従属 が存在します。
これを、注文番号・注文日・利用者・利用者連絡先の4列からなるテーブル(注文テーブル)に取り出すことで解消するのが、第2正規化です。
注文テーブル
※ 重複レコードを削除し、一意なレコードのみを残します。
注文明細テーブル
※ 原因となる列を取り出すことで、部分関数従属が解消します。
2-4. 補足
この記事では、参考書に合わせて、第2正規化を「主キー に対する部分関数従属を解消する」というイメージで記載しています。が、厳密には「すべての候補キー に対する部分関数従属を解消する」が正しいです。
参考書では、おそらく、分かりやすさを優先して主キーで説明しており、本記事も同じ方針で記載していますが、正確に理解したい方は、例えば以下の記事を確認してください。
以降、第3正規化、ボイスコッド正規化についても、「主キー」で説明している部分は、厳密には「候補キー」が正しいです。
2-5. 補足2
第2正規形を作る例としてもう一つ典型的なのが、関連テーブルの分解 です。前述のヘッダテーブルを取り出す処理と合わせて、第2正規化は大体こいつらです。
詳しく説明しませんが、以下に例を書いておきます。気になる人は、「2-3. もう少し正確な説明」に沿って、自分でも正規化してみてください。
関連テーブルの分解 は、AとB(例では生徒と教科)の関連を管理している1テーブルを、AとBと関連の3テーブルに分解するイメージです。
○正規化前
履修情報テーブル
○正規化後
学生テーブル
教科テーブル
履修テーブル
3. 第3正規形
第2正規形では主キー列と非キー列の関係を見ましたが、第3正規形では非キー列同士の関係を見ます。
3-1. 第3正規形のイメージ
第3正規形(第3正規化)のイメージは 「マスタ化できるものをマスタ化して取り出せ」 以下です。
マスタ化できるものをマスタ化して取り出せ
以下は第2正規形で分解して残った注文明細テーブルです。一見、第2正規形に分解する前のような、複数列で同じ値を繰り返すデータは無いように見えます。
しかし、全体を商品列で並び変えてみるとどうでしょうか。実は、商品・商品分類・単価で同じデータの繰り返しが発生しています。
これも当然で、どの注文のどの明細であっても(もっと言えば、注文がなく明細が0行でも)、例えば、じゃがいもは野菜で330円だからです。
このように、そもそも当該テーブルから独立している情報を管理する列を、以下のように別テーブルに取り出すのが第3正規化です。
商品テーブル
※重複レコードを削除し、一意なレコードのみを残します。1商品1レコードになり、繰り返しが解消します。
注文明細テーブル
※別テーブルへ分解した列は、元テーブルから削除します。ただし、どの商品かが分かるように商品だけは残します。
この商品テーブルのように、実際の業務(注文など)から独立して、事前に決めておく情報(商品単価など)を保持するテーブルを、一般に「マスタテーブル」と呼びます。
ですので、第3正規化は 「マスタ化できるものをマスタ化して取り出せ」 と言いかえることが可能です。
第2正規形の場合と同じく、これだけが第3正規形の作り方ではないのですが、やはり頻出パータンではあるので、一旦これが第3正規形のイメージだ、と捉えてよいと思います。
3-2. なぜ第3正規形が必要なのか
第3正規形になっていないと、例えばひとつの商品のデータが複数のレコードで保持されるため、以下のように矛盾したデータが作れてしまうからです。
第3正規形になっていれば、1商品のデータは1レコードになっている(分解したときに重複レコードを削除した)ため、矛盾データが発生しません。
3-3. もう少し正確な説明
第3正規形の定義は、そのテーブルが第2正規形、かつ、 「推移的関数従属が解消されていること」 です。
関数従属 は「ある列(複数列の組合せも可)の値が決まると、他の列の値が決まること」でした。
推移的関数従属 は「主キー以外の列の間で関数従属が発生していること」です。
以下、イメージの説明で使用した注文明細テーブル(第2正規化済み)を用いて説明してみます。
先に確認したように、このテーブルにおいて、商品が決まると商品分類・単価が決まります。したがって、商品は商品分類・単価を関数従属させます。
さらに、商品・商品分類・単価はいずれも主キーではありません。よって、このテーブルには、推移的関数従属 が存在します。
これを以下のように分解し解消するのが、第3正規形(第3正規化)です。
商品テーブル
※重複レコードを削除し、一意なレコードのみを残します。
注文明細テーブル
※別テーブルへ分解した列は、元テーブルから削除します。ただし、どの商品かが分かるように商品だけは残します。商品が関数従属させる列を取り出すことで、推移的関数従属が解消します。
3-4. 補足
本記事では、推移的関数従属を「主キー以外の列の間で関数従属が発生していること」と定義しましたが、これだと何が「推移的」なのか分からないですよね。実はこれ、あまり一般的な定義ではなく、通常は以下のように定義します。(参考書の定義も、おおむねこちらです)
-
推移的関数従属 とは、以下のように推移的な(≒2段階の)関数従属が存在すること。
K：主キー(複数列の組合せ可)
A1：主キー以外の列 (複数列の組合せ可)
A2：KとA1のいずれにも含まれない列(複数列でもいいが、1列あれば十分)
ここで、KはA1を関数従属させる。さらに、A1はA2を関数従属させる。
具体的には以下のとおりです。
ただ、2-3.で見たように、主キーはそのテーブルすべての列を、必ず関数従属させますので、第1段階の条件は省略可能です。ということで、この記事では第2段階の条件のみを使って、「主キー以外の列A1が、主キーでもA1でもない列A2を関数従属させる」=「主キー以外の列の間で関数従属が発生している」ことを定義に採用しています2。
B. ボイス-コッド正規形
第2正規形とは反対に、非キー列からキー列への関係を整理する正規形です。
B-1. ボイス-コッド正規形のイメージ
ボイス-コッド正規形(ボイス-コッド正規化)のイメージは 「より絞り込みの強い列で主キーを組みなおして取り出せ」 です。
より絞り込みの強い列で主キーを組みなおして取り出せ
以下は体験入学の受講学部・講義を管理するテーブルです。
受講者は体験する学部を選択し、その学部の講義を一つだけ受講できるものとします。
この時、このテーブルの主キーは、受講者と学部になります。
体験入学受講テーブル
学部と講義で並べ替えると分かりますが、このテーブルにも複数列をまたいだ同じデータの繰り返しが見つかります。
これは、講義が決まると学部が決まるためで、例えば講義が「環境工学I」なら学部は必ず「工学部」となり、この組合せが繰り返してしまうからです。
ということで、第2正規化や第3正規化と同様に、値が繰り返すこの2列を、以下のように別テーブルへ取り出すことを考えます。
※重複しているレコードは削除し、一意なレコードのみを残す。
ところが、元のテーブルで主キー列だった「学部」はこのテーブルの主キーになれません。
このテーブルの主キーは、元のテーブルでは非キー列だった「講義」です。
このように、主キー列「学部」と、非キー列「講義」に以下が成立する場合に
- 「学部」は「講義」を1件に絞り込めない
- 「講義」は「学部」を1件に絞りこめる ( ※非キー列の方が絞り込みが強い! )
まず、以下のように主キーを絞り込みが強い方に入れ替え、
さらに、以下のように分解するのがボイス-コッド正規化のイメージです。
受講テーブル
講義テーブル
※重複しているレコードは削除し、一意なレコードのみを残します
ボイス-コッド正規化の 「より絞り込みの強い列で主キーを組みなおして取り出せ」 のイメージが伝わりますでしょうか。
B-2. なぜボイス-コッド正規形が必要なのか
ボイス-コッド正規形に分解していないと、以下のように、矛盾したデータを作れてしまうからです。
ボイスコッド正規形になっていれば、学部と講義の対応は1行で管理されるので、矛盾が発せしません。
B-3. もう少し正確な説明
ボイス-コッド正規形の定義は、そのテーブルが第3正規形、かつ、 「非キー列からキー列への関数従属が解消されていること」 です。
以下、イメージで挙げたテーブルを例に説明してみます。
体験入学受講テーブル
まず、細かく説明しませんが、このテーブルは第3正規形です。
- 一つのセルの中には一つの値しか含んでいない ⇒ 第1正規形
- 主キーの構成列である「受講者」と「学部」は、どちらも単独では「講義」の値を決定できない ⇒ 部分関数従属はない ⇒ 第2正規形
- 非キー列は一つしかないので、非キー列間の関数従属はない ⇒ 推移的関数従属はない ⇒ 第3正規形
さて、 関数従属 は「ある列(複数列の組合せも可)の値が決まると、他の列の値が決まること」でした。
既にみたように、このテーブルにおいて、非キー列である「講義」の値が決まると、主キー列である「学部」の値が決まります。このテーブルには 非キー列からキー列への関数従属 が存在します。
これを、まず主キーを入れ替えることで、非キー列から主キー列への関数従属をなくします。
しかし、このままでは、主キーの一部である「講義」の値が決まると「学部」の値が決まる、すなわち、部分関数従属が発生します。
そこで、第2正規化を行い、以下の2テーブルに分解します。
受講テーブル
講義テーブル
これが、ボイス-コッド正規化のもう少し正確な説明です。
B-4. 補足
ぶっちゃけ、ボイス-コッド正規化については、あえて正規化しないことの方が多いです。
一般にボイス-コッド正規化を行ったテーブルは、本来そのテーブルで管理すべき最も重要な対応関係を、失ってしまうことが多いからです。
例えば、体験入学について、以下の条件が追加になったとします。
- 各講義について、受講者をサポートするサポーターをつける
- サポーターは各講義に複数名割り当てるが、一人の学生には一人のサポーターが選任で対応する
- サポーターが複数の講義を掛け持ちすることはない(サポーターが決まれば講義が決まる)
このとき、以下のような体験入学受講テーブルを作れます
体験入学受講テーブル
そして、これをボイス-コッド正規化すると以下のようになります。
受講者担当サポーターテーブル
サポーター受け持ち講義テーブル
でも、これ、 元のテーブルレイアウトの方が自然 じゃないですか？
例えば、受講者が受講する講義は決めたけど、まだサポーターは決めていない、という状況を、 正規化後のテーブルは表現できません 。本来、メインとして管理されるべき受講者と講義の対応が、サポーターという副次的な要素を介してしか結び付けられないから3です。
この辺りは、正規化というよりは、データモデリングの話になってくるので、これ以上記載しませんが、ボイス-コッド正規化を行うかは、慎重に判断する必要があります。
とはいえ、第3正規形なのにボイス-コッド正規形になっていないテーブルが出てくること自体、ほとんどないのですが。
4. 第4正規形
主キーのみのテーブルに対し、主キー列同士の関係を整理する正規形です。
4-1. 第4正規形のイメージ
第4正規形(第4正規化)のイメージは 「単純掛け合わせテーブルを作るな」 です。
単純掛け合わせテーブルを作るな
通常、正規化は、非正規形のテーブルを正規化する順で説明しますが、第4正規形は逆順の方が分かりやすいです。
例えば、以下のように、チームと担当顧客、および、チームに所属する社員を管理しているテーブルがあるとします。
チーム所属社員マスタ
チーム担当顧客マスタ
この時、これらのテーブルを単純に掛け合わせた4、以下のようなテーブルを考えることができます。
チーム社員顧客マスタ
このテーブルでも、例えば、後藤さんがチームAに所属していることは分かるし、チームBが●●株式会社を担当していることも分かります。
さらに、このテーブルは主キーのみで構成されるため、ボイス-コッド正規形になっています。(第2正規形、第3正規形、ボイス-コッド正規形が、非キー列を評価していたことを思い出してください)
では、このテーブルでチームと所属社員と担当顧客の対応を管理していいのか。ダメ。元の2テーブルに分解して管理しなさい、というのが第4正規形です。
4-2. なぜ第4正規形が必要なのか
第4正規形に分解していないと、以下のように、矛盾したデータを作れてしまうからです。
もとの2テーブルに分解していれば、社員ごとに複製されたレコードが無いため、このような矛盾データは作れません。
チーム担当顧客マスタ
4-3. もう少し正確な説明
第4正規形の定義は 「多値従属が解消されていること」 です。
多値従属 とは、例えば、K1,K2,K3の3列からなるテーブルについて、K1が決まるとK2がどの値をとってもK3に出現する値の組合せが決まること、と定義できます。
例えば、例で挙げたテーブルを見ると、チームがAに決まると、社員がどの値をとっても、顧客の値は「〇〇株式会社、××株式会社、△△株式会社」の組合せになります。
以上のように、チームが、顧客の取りうる値の組合せを、社員に依存せず決定するのが、多値従属です。
ちなみにこの時、チーム・顧客でデータを並べ替えてみると、チームは社員も多値従属させることが分かります。
一般に、K1,K2,K3の3列からなるテーブルについて、K1がK3を多値従属させるとき、K1はK2も多値従属させます。例えば、チームがAに決まると、Aに所属する全社員に対して、Aの担当する全顧客のレコードが出てくるので、逆もまた同様になるからです。
そして、これを元の2テーブルに分解して解消するのが、第4正規形(第4正規化)です。
チーム所属社員マスタ
チーム担当顧客マスタ
4-4. 補足
参考書では、例えばK1、K2、K3の3列からなるテーブルに対して、K1がK3を多値従属させることを、K2を考慮せずに決定できるように記載しています。が、この記事では「K1が決まると K2がどの値をとっても K3に出現する値の組合せが決まること」と定義したように、実際にはK2に対する評価が必要です。
参考書には、不正確な説明が複数存在しますが、大体は、分かりやすさを優先するためにごまかしたんだなぁ、と理解できるものです。ですが、この部分は、さすがにやりすぎな気がします。
5. 第5正規形
コンセプトは第4正規形と同じで、評価する内容が若干違います。こちらも、主キーのみのテーブルに対して評価します。
5-1. 第5正規形のイメージ
第5正規形(第5正規化)のイメージも 「単純掛け合わせテーブルを作るな」 です。
単純掛け合わせテーブルを作るな
通常、正規化は、非正規形のテーブルを正規化する順で説明しますが、第5正規形も逆順の方が分かりやすいです。
例えば、以下のように、チームと担当顧客、チームと所属する社員が決まっているとします。ここまでは第4正規形の時と同じです。
チーム所属社員マスタ
チーム担当顧客マスタ
ここでさらに、以下のように、各社員が担当可能な顧客が決まっているとします。
社員担当顧客マスタ
この時、これらのテーブルを単純に掛け合わせた5、以下のようなテーブルを考えることができます。
チーム社員顧客マスタ
第4正規形の時と同様に、このテーブルでも、例えば、後藤さんがチームAに所属していることは分かります。
さらに、このテーブルは多値従属がなく、第4正規形です。(チームがAに決まっても、社員が後藤さん佐々木さんかによって、顧客が取りうる値の組合せが変わるので)
では、このテーブルでチームと社員と顧客の対応を管理していいのか。ダメ。元の3テーブルに分解して管理しなさい、というのが第5正規形です。
5-2. なぜ第5正規形が必要なのか
第5正規形に分解していないと、矛盾したデータを作れてしまうからです。
例えば、佐々木さんが△△株式会社も担当可能になったとして、うっかり以下のようにレコードを足してしまうと、チームBも△△株式会社を担当できることになってしまいます。
チーム社員顧客マスタ
テーブルが分解されていれば、佐々木さんと顧客のレコードは社員担当顧客マスタに追加するだけなので、チームに影響が及びません。
社員担当顧客マスタ
5-3. もう少し正確な説明
第5正規形の定義は 「結合従属が解消されていること」 です。
結合従属 とは、あるテーブルが、複数の別テーブルの単純掛け合わせになっていることです。第5正規形については、イメージの説明がそのまま定義として使えます。そしてこれを元の複数テーブルに分解して解消するのが、第5正規化です。
5-4. 補足
第5正規化の定義が「単純掛け合わせテーブルを元のテーブルに分解すること」なら、第4正規化と何が違うの？と思った方がいるかと思います。
これはその通りで、第5正規化というのは、以下のように第4正規化の一般化であり、本質的にやっていることは同じです。
・第4正規化：多値従属という特殊な前提を満たす掛け合わせテーブルの分解
・第5正規化：前提を問わず掛け合わせテーブルならとにかく分解
さらに言うなら、実は、第2正規化、第3正規化、ボイス-コッド正規化も、関数従属という特殊な前提を満たすテーブルの分解であり、分解後のテーブルを単純掛け合わせすると、もとのテーブルに戻ります。つまり、 結合従属の解消は正規化の概念そのもの と捉えることもできます。
6. 第6正規形
キー列・非キー列を問わず、全体の結合従属性を解消します。
6-1 第6正規形とは
第6正規形は実務で使わないので、ざっくり行かせてください。
第6正規形の定義は、全ての結合従属性が解消されている ことです。
まず、第5正規形のテーブルは第6正規形でもあります。
主キーのみのテーブルに限定されてはいますが、結合従属性は解消されているからです。
では、非キー列を含むテーブルを第6正規化するにはどうしたらよいか。
ボイス-コッド正規化まで行ったテーブルを以下のように、非キー列ごとに 非キー列を1列しか持たないテーブルへ分解 します。
○第6正規化前
注文明細
○第6正規化後
注文明細_商品
注文明細_数量
このように、非キー列を1列1列分解しても、単純掛け合わせでもとのテーブルには戻りますので、これも結合従属性の解消ではあるわけです。
正直、あまり意味のない分解ではあるのですが、正規化の概念そのものともいえる結合従属性の解消を、最後まで推し進めるとこうなります、というのが第6正規形です。
最後に
ということで、正規化の説明でした。
頭の中では、もっとシンプルにまとまっていたはずなのですが、実際アウトプットしてみたら、とんでもなく長くなったうえに、そもそも分かりやすい説明になったのかも自信がなくなってしまいました…。
この記事が少しでも、お役に立てばよいのですが…。
-
マルチカラムアトリビュートという、立派な(?)アンチパターン名がついています。例えば、必要な列数(子供は何人までいるか)は、簡単には決められないですよね。(ギネス記録は69人らしいですが、養子とか考えると、無限に増やせそう)。あと、「〇〇さんの親」を探すSQLが絶望的にめんどくさい感じになります。 ↩
-
とはいえ、「主キー以外の列の間で関数従属が発生している」の「主キー以外」という部分を正確に定義しようとすると、結局、K(1段目)の評価が必要なので、省略というよりは言い換えといった方が正しいかもしれません。 ↩
-
この問題は、テーブルの主キーは受講者と講義にして、サポーターは受講者・講義に関数従属している方が自然ではないか、と言い換えることもできます。この観点で、 ボイス-コッド正規化は元のテーブルの関数従属を喪失する のように表現することがあります。 ↩
-
両テーブルに共通する「チーム」列をキーとして、以下のSQLで結合するイメージです。
select T1.チーム, T1.社員, T2.顧客
from チーム所属社員マスタ T1, チーム担当顧客マスタ T2
where T1.チーム = T2.チーム ↩ -
3テーブルを、同じ列が一致すること条件に、以下のSQLで結合するイメージです。
select T1.チーム, T1.社員, T2.顧客
from チーム所属社員マスタ T1, チーム担当顧客マスタ T2, 社員担当可能顧客マスタ T3
where T1.チーム = T2.チーム and T2.顧客 = T3.顧客 and T3.社員 = T1.社員 ↩