Author: admin

前回は、第五正規形の例として、履修登録を示しましたが、表に分割はできるものの、更新処理が複雑怪奇になりそうということで、本当にそういう表分割をするのかどうかという根源的な問題が出てきてしまいました。例えば、商品マスターを分離するというのは、そのメリットはわかりやすいと思われます。あっちこっちにある商品単価が、一元的に管理されているので、単価が変わったら１箇所の修正で、あちらこちらの商品単価が自動的に更新されるというあたりです。ところが、履修登録を３つの表に分離したら、ちょっと様子が変わりました。まず、こんな履修登録があるとします。教員と科目は多対多の関係にあり、1人の教員が複数の科目を持つこともあれば、1科目を複数の教員が担当することもあります。

ここで、データそのものと、関係性を分離して考えるために、教員、科目、学生は、まずそれぞれ単独の表に記載します。そして、ID番号をここでは重複しないように適当に降ります。いずれもID以外にフィールドは1つだけですが、実際のデータベースでは、それぞれ、たくさんのフィールドを持つことになるのではないかと思われます。そして、その関係性を表に分離したのが後半の3つです。前回に、第五正規形の例として示した表と根本的には同じですが、実際の運用に近い感じにして見ました。つまり、合計６つの表で、履修登録の管理をしようということになります。

ここで、新たに新規登録がどんどん進む場面は、そんなにややこしくないでしょう。学生がある科目を履修したいとしたら、「学生履修科目」にまずは追加し、そして教務の担当者が、教員担当科目を参照しながら担当教員を適当に割り当てて、教員担当学生に行を追加します。おっと、ここで、「担当教員を適当に割り当てて」という人手が入ってしまっていますが、そういうワークフローにしましょう。

そして、たくさんの学生の履修登録が終わった後、教員の割り当てに変化が発生したとします。例えば、教員担当科目の4行目、X先生の科目Pの担当がなくなったとします。しかし、それは学生の履修がなくなるのならまだしも、通常は無くならないと思うので、X先生のクラスで科目Pを取ろうとしていた学生をZ先生など別のクラスに振り分けないといけません。教員担当学生はどう処理しましょう？ これは元の「履修登録」の表に戻って考えないといけません。X先生に関するレコードは3つありますが、最初の二つは科目Mなので関係ありません。最後の科目Pのものについて担当をZ先生に切り替えたいわけです。ここで「教員担当科目」は単に、{101, 202} が消えるだけになります。「教員担当学生」はどうなるかといえば、{302, 101} を {302, 103} にしてしまうと、この学生が、X先生の科目Mを履修しているという情報がなくなります。結果的に、{302, 101}はそのままに{302, 103}を追加するという処理になります。ややこしいですね。自由に編集ができそうな気がしません。

このような問題がある場合は、現実のワークフローをよくチェックして、頻繁にあることは容易に進められるようにするのが、通常は考えることになります。そして、あり得ないことに対する対処は諦めます。滅多にないことは、何か対策を考えます。バッチ処理などということもあるかもしれません。

履修関係となると、一般にどの学校も、まずどんな科目があるかを決めて、それは履修登録開始時には確定していて、よほどのことがない限りその科目の存在は無くなったり増えたりはしないのが一般的です。同様に、担当する教員についても同様です。どんな科目があって、誰が担当するのか、つまり「教員担当科目」には、履修登録が開始されると変更はないという仮定があると考えられます。その上で、次のような表で構成することを考えました。履修登録は元の通りですが、履修登録前はレコードがないので、すでに決まっている科目と担当教員の情報を記録するために「教員担当科目」は必要になります。冗長ではあるかと思いますが、ユーザインタフェースを考えると、このような構成が良いと考えられます。

ある学生Aが科目Mを登録したいと希望があった場合、まず、履修登録に{201, 101, null} を追加します。締め切り後にCSVファイルを読み込むなどの方法だとそのような状態になると思われます。そして、教員IDについては、ポップアップメニュー等で、選択できるようにすれば、1人1人は手作業での指定になりますが、確定はできます。ポップアップメニューは「教員担当科目」の情報を利用することで、科目が、MならX、PならX, Y, Zが選択肢として登場するようにすればいいでしょう。また、履修登録と「教員」の表を結合して「教員名」を参照すると、決まっていない履修登録は、教員名がnullのものという判断ができるので、「履修登録一覧表_教員未選択」はこうしたルールを利用すれば作成が可能です。「履修登録」を直接作ってしまう方が、データの変更などの処理がやりやすくなるのであれば、もちろんワークフローの確認は必要だとしても、テーブルを分けない方法も選択肢としては有力だということです。

教員担当科目のレコードが１つなくなるような事態になった場合でも、履修登録をいじらなければ、その学生がその科目を履修しているという情報は保持されます。しかしながら、教員IDは対応する科目IDがないままになります。この場合、「教員担当科目」と「履修登録」のテーブル結合を、教員IDと科目IDの両方のフィールドを照合して行います。その時、「履修登録」のレコードは必ず残る照合を行います。その時、「履修登録」「教員担当科目」の順に記述し、前の方だけを残すので、「左結合」と呼ばれています。そして、右側の表にあるフィールドを参照すると、nullになっているものが、「教員ID」を正しく選択していないレコードというように判定が可能です。この方法で、教員IDを選択していないフィールドの判定も可能です。そうすれば、X先生が科目Pの担当を外れた後、教員IDを振り直す履修登録のレコードが絞り込まれるので、それらについて、地道にポップアップメニューを選択してもらうというワークフローが確立します。

どちらの設計が良いのかは、なかなか議論は収束しないかもしれませんが、結果的には、ユーザの要求が全ての出発点であり、実現しなければならないワークフローをきちんと考えて、そのワークフローが無理なく進められる設計に収束させる必要があるということです。開発作業自体が大変なのは仕方ないとしても、よりシンプルに開発できる選択肢があるのなら、常にそちらを目指すのが基本です。複雑なものよりシンプルなものの方が、ソフトウエアの品質が高くなる可能性が高いからです。もちろん、シンプルにするとワークフローが成り立たないとなれば、複雑化は免れないのですが、ワークフローを変えられないのかをユーザと折衝するなどして、開発は進むことになります。折り合いをつけるのは難しいですし、結局、何かこれから検討すべきことのコミットを求められるなど、厳しい局面もあるかもしれませんが、現在のソフトウェア技術とビジネス環境では、完成品を見ないと判断できないユーザが多いのも事実で、仕方ない面もあります。結果的に、プロジェクトをうまく進めるという能力に高いお金が払われるということにもなっています。

正規化の話は一旦ここで区切りとします。補足したいこともあれこれあるのですが、この先は少しランダムな感じでいくつか話題を振りたいと思います。

第四正規形の説明をしたときの表を改めて示しながら、第五正規形を説明しましょう。履修科目登録は3つのフィールドがあり、それぞれ何かのルールに従って入力されているような感じでした。

6行目を追加する前の状態で、かつ、{教員} → {科目} という関数従属性、つまりは、教員が決まれば科目は一意に決まる。つまり、ある先生は１つの科目しか持たないというルールを入れれば、「教員担当科目」が単独の表として存在でき、その表を引き算した結果は、残るフィールド「学生」と照合可能な教員フィールドを持つ「教員担当学生」の表の、これらの２つの表を結合すれば元の表が得られました。しかしながら、主キーとは関係のない関数従属性があるということで、すでにボイス-コッド正規形ではないということになりました。

ここで、この関数従属はないものとして、「教員担当科目」は、先生と科目の対応表というように仕組みを拡張したとします。その結果、前のように、X先生はMにもPにも受け持つということになります。表として分解した結果は以下の通りです。「教員担当学生」に {B, X} が追加されていないのは元々その情報が表に存在していたからです。

前回はこのうち、2つの表を結合していました。２つを結合すれば、少なくともフィールドは３つ揃うのでうまく元の表が合成できないかを考えたのですが、関数従属があるという前提で、その関数従属に絡んだ分割でないと元には戻りませんでした。

そこで、３つの表を全て結合してみます。前から順に行きましょう。まず、以下のように表Tを求めました。もちろん、元の表よりもかなり多くなっています。もちろん、科目同士を照合します。

このTに残る表を結合すると、なんと、元の表に戻ります。ここではそれぞれの表の {学生, 教員} を手がかりに照合します。Tの1行目は手がかりが {A, X} なので、教員担当学生にある {A, X} を探すとあります。もちろん、ここでフィールドを合体してもいいのですが、教員担当学生によって新たなフィールドは登場しないので、「照合可能なレコードが存在する」というのは、言い換えれば結果の表にそのまま追加されるということです。ちなみに、Tの4行目{Y, P, C}に対して、照合するフィールド{C, Y}のレコードが教員担当学生の表にあるかというとありません。ということで、照合可能なレコードがない場合は照合されるものがなかったということで消えてしまって、 Tの4行目{Y, P, C}を元にしたレコードは結果には存在しないということになります。これが結合の処理のルールです。ちょうど、「担当教員学生」の表によってあたかもフィルターのような効果が見られるということになります。

ということで、実は３つのフィールドある場合、３つのテーブルへの分割は、どうやら可能だということがわかったわけです。ざっくりと言えば、この分割をした結果が第五正規形です。もちろん、要求事項によっては前回に見たように２つで事足りかもしれません。３フィールドだから必ず3つということではありません。

第五正規形を正しく議論するとき、多値従属性をさらに拡張した結合従属性という考え方を基礎にすることになるのですが、かなり難しくなるようで、詳細が説明されていない場合が多いです。ただ、Abiteboul先生の「Foundations of Databases」での第四正規形の見出しは「無関係な情報を同一の表に入れるんじゃない」というような趣旨になっていて、言い得て妙なのではないでしょうか。第四、第五正規形のサンプルで最初に出した、３つのフィールドのある表は、ワークしそうな気はしますが、実は３つのフィールドから２つのフィールドを取り出した場合、もちろん３通りの組み合わせがあるのですが、それぞれに意味のある情報であったということになります。つまり、３つの関係性に関わる情報が、当初は１つの表に込められていたけど、それらの関係は分離できるということを第四、第五正規形は言っているのです。

３つの表に分解した結果を改めて見てみましょう。{教員, 科目}のフィールドを持つ「教員担当科目」は、教員と科目の関係なので、例えば、教員1人ずつを見ると、その先生が持つ科目の一覧になりますし、科目１つに絞り込めばその科目を担当する先生の一覧になります。つまり、教員と科目について対応関係があるかどうかということを記述している表になります。同様に「学生履修科目」は、学生がどの科目を履修しているかを表現する表です。そして、「教員担当学生」はどの先生のクラスに学生が属しているかという表になります。３つのフィールドはそれぞれ関連性がありますが、一方で、３種類の関連性があってそれらはバラバラにすることができるということになります。「教員担当学生」だけが単独で存在するのは少し違和感があるかもしれませんが、「教員担当科目」と「学生履修科目」だけでは、情報が少ないのです。つまり、この2つの対応関係だけだと、学生がどの先生のクラスなのかが確定しないということになります。

更新整合性についても検討しましょう。新たにクラスに追加するということを行うとき、「学生履修科目」と「教員担当学生」にレコードを追加することになります。これは、学生がどの科目を履修するのかという情報と、どの先生のクラスで履修するのかという2つの事実を記録するためであって、2つのレコードを記述するのは、2つの新たな関係性を記録するという意味では不整合はないと言えます。ここで、科目は自由に選べるとしても、担当する先生は「教員担当科目」によって規定された範囲でなければなりません。例えば、新たな学生が科目Mを選択するときに、その学生がZ先生であってはいけません。言い換えれば、「教員担当学生」に追加可能なレコードは、「教員担当科目」によって制限されるということになります。新たにクラスを作るとしたら、「教員担当科目」にレコードを追加しなければなりません。これは、実用上は単純なことではないでしょう。その制約をどこで解決するか、つまりUIでうまく制限するなり処理途中で確認をするなどの対処は設計上検討が必要です。

また、この表では学生Bは、2つの教科ともX先生のクラスですが、科目PはZ先生のクラスにするということになりました。では、「教員担当学生」の{B, X}を{B, Z}にするかというとそうではありません。この場合、たまたま2科目分の情報が同一だったので1レコードになったのですが、それぞれ違う先生のクラスになるということは、{B, X}はそのままにして、{B, Z}を追加する必要があります。そうすれば、表の結合で所望の表は得られるのですが、１項目の変更なのに、追加をするという必要が出てきます。つまり、クラス変更については、単に更新処理だけでは済まないということになり、ここもシステム構築の複雑さを増すことになります。つまり、データによって更新処理が異なるというのはある意味で不整合と言えるでしょう。この点は正規形によって解決できる箇所ではなく、むしろ発生した新たな問題と言えます。

このような表が欲しい場合、設計はどうすればいいでしょうか？ 次回はそのことを考えてみたいのですが、少し間が開くと思います。

ボイス-コッド正規形でも十分難しいのに、さらに第四・・などとなると、もうだめだーと思われるかも知れませんが、正規形の考え方はデータベース設計の基礎でもあるので、頑張って理解しましょう。第四と第五は、大雑把な意味ではまとめて理解してもいいのですが、ここでは段階的に話を進めたいと思います。ただし、第四正規形のサンプルは、説明のためのサンプルが結構多いような気がします（その意味で言えば、全部説明っぽいとも言えるのですが）。なので、１つのサンプルを色々いじってみて、最終的に第五正規形を満たすことを考えて見たいと思います。

まず、前提として、第四、第五正規形は、3つの対象（エンティティ）が絡み合う場合が前提です。3つというのはフィールドが3つという意味ではありません。１つの表にまとまるものが3つあるという状況だと考えてください。そして、その3つの対象がそれぞれ関連性を持っているということで、少なくとも2つの関連性が存在します。あるいは3つの関連性があるかも知れません。それらを別々に表で管理しなさいというのが、実は第四、第五正規形の結論です。データが三竦み状態になるようなことはよくありませんか？ もちろん、二組ずつに分解が「簡単に」できれば、多分、それは第三正規形までの議論で終わっていると思います。つまり、関数従属が２つ存在するような場合に相当するでしょう。しかし、関数従属がうまく存在してくれない場合、あるいは従属性が見つけにくいとでも言えばいいでしょうか。そういう場合が第四正規形以降で議論する対象になります。このようなケースは意外にあります。分解が難しいと思ったら、三竦みになっていないかを考えると良いでしょう。ただ、三竦みの登場人物をきちんと把握しないと間違った設計に行ってしまいます。とにかく、「事実を表にする」というのが基本です。

では、以下のような表「履修登録」を考えてみます。表の中身は、Mではなく「数学」などとそれらしく書いてもいいのですが、だんだん、こうした変数っぽく書く方がわかりやすくなってくるのは感じてもらえると思います。もちろん、ここでは、XやAは集合ではなく、何らかのデータです。基本、どれも「ラベル」ですね。入るべき文字列がどんなものか、フィールド名から明白に定義可能です。まず、学生は科目を履修するのは良いとして、その結果、{科目, 学生} について、重複するレコードは存在しないということになります。つまり、学生Aが科目Mを2つ取得するという事はなく、1つあるいは存在しないのどちらかになるので、{科目, 学生} は候補キーに含まれるべきフィールドです。教員はある科目を担当するのですが、ここではまず、単一の科目に複数の異なる教員が割り当てられているという前提で話を進めます。Mは置いておいて、科目Pは、どうやら、Y先生とZ先生の「クラス」がある模様です。大きな大学なら、必須科目なんかで同一の科目が先生ごとにいくつか開催されて、さらに所属する学科でどの先生の講義を取るかを決められているようなこともあります。そんな感じです。ともかく、そいういう場面を想定してください。科目と学生が決まれば、教員は自動的に決まるかというと、まずはそうではなく、後から教務課が適当に割り振ったという状況にしましょう。もちろん、どの先生がどの科目を担当するのかという情報がどうもこれだけだと曖昧な感じがしますが、そこは実は焦点となります。ただ、科目Pの状況を見るとYかZということになり、{科目} → {教員}という関数従属はとりあえずなさそうです。ということで、学生がどれかの科目の履修をするとしたら、教員は必ず割り当てられるということを考えれば、{科目, 学生} → {教員} という関数従属があると考えられるので、候補キー、及び主キーは{科目, 学生} となります。他に関数従属がない場合はボイス-コッド正規形を満たしているということになります。これ以上の表の分割はできないのでしょうか？

更新整合性が損なわれることはそんなに難しい話ではないと思います。今まで通りの考え方で、関数従属はとりあえず不明な感じですが、この表は、フィールドになっている3つの対象に関する関係を記述していると言えるので、例えば、3行目を削除すると、Y先生が科目Pを担当しているという情報が落ちるなど、状況は色々考えられます。

ここで、第四正規形であるかどうかを評価する指標として「多値従属性」という考え方があります。きちんと数学的に定義はされているのですが、大まかに言うと、2つの表に分解し、その分解した結果から表の結合をすることで元に戻せるという性質があれば、多値従属性があるとしています。多値従属性は、{X, Y, Z}からなる表がある場合、{X, Y} と {X, Z}の表に分解できるということでもあります。これをMD: X→→Y|Z のように書きますが、Zの存在は明確なので、「X→→Y」だけを書くことが多いようです。関数従属から矢印が１つ増えただけですね。この多値従属に関しても公理系が定義されているため、多値従属は数学的な意味で確実に証明可能な手がかりにもなりますが、ちょっと複雑なので、そういうものもあるというだけで留めます。

では、前の表は、分解可能でしょうか？ 実はだめなんです。いや、できるかもしれません。それをこれから説明します。第四正規形に移行可能なサンプルと思ってしまった皆さん、ごめんなさい、この辺りの内容は、要するに説明が難しいのですよ。ということで、あえて、この表を3つの表にまず分解したのが以下の表です。３つのフィールドから２つのフィールドを取り出すということで、3C2 = (3 x 2) / (2 x 1) = 3つの表が出てきます。ただし、一番左の「教員担当科目」については、1行目と2行目、4行目と5行目が同一の事実を表している（前者は、教員Xが科目Mを担当するという実々）ので、レコードとして2つ存在している必要はないと考えて、同じレコードを捌きます。他はどうやらそういったレコードの重複はなさそうです。教員担当科目については、先の議論がやりやすいようにフィールドの順序が入れ替わっているので、そのつもりで見てください。

右の2つの表については、存在する2つのフィールドの組みがいずれも主キーになります。まずは、関数従属性がいずれの表にもないということを考えます。実は、学生と教員、科目と学生については、いずれも「多対多」の関係にあり、データベース設計ではそちらの考え方でテーブル設計を進める方がわかりやすいのですが、ここではともかく表を考えることにします。

ここで、分割した表から元の表が再現できるかを、分割した表の合成をおこなってみて検討しましょう。まず、教員担当科目と学生履修科目を結合して合成してみます。手順は、左から１つレコードを取り、照合するフィールドが同じものを右側で探して、それらのフィールドをくっつけたものを結果として残す、という作業を繰り返した結果です。つまり、{X, M}に対して、右側に{M. A}があるので、{X, M, A}を結果に残します。また、右側の2行目に{M, B}があるので、{X, M, B}も残します。ここまでは元の表にあったレコードばかりです。ところが、左側の2行目{Y, P}に対して、照合をかけると科目Pのものは右側に3つあります。つまり、左側の2行目に対して、{Y, P, A} {Y, P, C} {Y, P, D}が得られます。{Y, P, A} は元の表にありましたが、{Y, P, C} {Y, P, D} は元の表にないレコードです。合成した結果を以下に示しますが、背景がピンクの行は元の表に存在しない行です。分解して戻そうとしても元には戻りませんでした。つまり、{科目} →→ {教員}|{学生} という多値従属はなかったということになります。

では、左端の教員担当科目と、右端の教員担当学生を、結合したらどうでしょうか。もちろん、共通の教員を元に照合をします。教員担当科目の1行目{X, M}と、教員担当学生を照合した結果、{X, M, A} {X, M, B}が得られます。2行目の{Y, P}に対しては、{Y, P, A}のひとつだけが得られます。全て行うと、確かに元の表が得られるので、現在得られているデータが全てだと仮定すると、{教員} →→ {科目}|{学生} という多値従属が存在するとも言えます。

この2つの表に分解ができたとまずは結論づけます。ここで、教員担当科目はもう職員会議で決まったもので、今年は一切変更がないとします。その後、学生がどんどんと履修するのですが、例えば、E君は科目Mを取りたいとすると、事務の人が教員担当科目を見て {E, X} しか割り当てられないので、つまり、数学は1人の先生しかいないので、教員担当学生に {E, X} を追加することで、３フィールドがある履修登録表に無事1つのレコードが追加されました。F君が科目Pを取りたいとして、事務の人は、Y先生かZ先生かを決めて、例えば{F, Z}を教員担当学生の表に追加します。そして、結合した結果、履修登録では、{P, F, Z} が新たに登場します。つまり、分割した表を処理することで、元の表を処理することができるという状態になっています。

ということで、第四正規形は終わります、と言いたいところですが、そうは行きません。仮に元データが一部のデータだったらどうでしょう。ここで、反証として、元の表に、{P, B, X} が追加されたとします。物理学Pの履修者が多く、学生Bを登録したときに、既存のクラスY、Zでは賄いきれないと思って、数学のX先生にも物理学Pの担当をお願いしてクラスを増やしたような状況です。

この状況で、単純に表を3つに分割します。「教員担当科目」の表に{X, P}が増えます。学生履修科目の表にも{P, B}が増えます。教員担当学生の表には{B, X}が増えるかと思うと、元々そのレコードがあるので重複があるので追加の必要はありません。分割した表では重複するレコードは排除します。

この結果、レコード追加前であれば元の表が再現できた「教員担当科目」と「教員担当学生」の結合の結果Sは、以下のようになり、余分なレコードが増えます。つまり、分解から戻しても、元の表が再現されていないので、{教員} →→ {科目}|{学生} という多値従属はやっぱりありませんでした。分解した結果を元には戻せないということからそう結論づけることができます。

ここで、すでに多値従属性はないという結論が出ている上で、第四正規化の話をしたいのですが、第四正規化の条件は、表に存在する多値従属性の左辺が全てスーパーキーであるというものです。ボイス-コッド正規形の条件に似ていますが、判定するのは関数従属性ではなく多値従属性です。

最初の表が仮に「全データ」だとしたら、{教員} →→ {科目}|{学生} という多値従属はありますが、{教員} は表に対するスーパーキーではありませんので、第四正規形ではないという結論になります。そして、多値従属があるのだからテーブルを「教員担当科目」「教員担当学生」の分離できるということになり、その結果それぞれの表は2フィールドずつになりもはや多値従属性はないので、第四正規形を満たしていると言えるということになります。第四についての厳密な議論はあえて避けます。次に説明するように、現実的な問題点は別のところにあります。

ここで、行を追加する前の履修登録について何か見落としていないかを考えます。すると、すでに答えは書いてありますが、教員担当科目の表を見ると、{教員} → {科目} という関数従属が存在していました。要求事項として出ていませんが、データを見ると明白です。つまり、ある先生は１つの科目しか担当しないという、現実にはあり得ない状況がデータにはあったのです。たまたま、そういう状況が何年か続いてシステムが動いていたとしても、突然、1人の教員が複数の科目を担当するという状況になったときその状況をデータとして表現できないことにもつながり、システムは破綻してしまいます。ですが、設計上のことを考えれば、主キーが{科目, 学生}であり、他に{教員} → {科目} という関数従属性があるとしたら、これはどこかで見たことがありますね。主キーに関係しないフィールドから、主キーの一部に関数従属があるということで、ボイス-コッド正規形を満たさないパターンです。つまり、{教員}はスーパーキーではないということで、{教員} → {科目} の関数従属に関する表を分離すれば良いということになります。ということで、正規形の世界を前進しているつもりがちょっと後退してしまいました。ここで行った「教員担当科目」と「教員担当学生」への分割は、第四正規形を満たしていないからというよりも、ボイス-コッド正規形を満たしていないからというのがより正しい言い方になります。

しかしながら、後から修正した表は、{教員} → {科目} の関数従属は存在しません。教員と科目には関係はありそうですが、どうやら多対多の関係のようです。このようなときに表に分解できないのかということになりますが、それを可能にするのが第五正規形になります。

今回は、ある意味、分割しない方が良さそうな表のサンプルを見てみることにしましょう。その表は、ボイス-コッド正規形ではありますが、分割する方が更新不整合が起きるというか、分割することでかえってデータのメンテナンスが大変になりそうな事例です。なお、今回の事例は、完全に自分で考えたもので、どこかにあるものをアレンジしたものではないため、もしかしたら、間違いがどこかに潜んでいるかも知れません。間違っていたら、遠慮なく教えてください。

以下、「会議室予約」という表があります。前提として、ある組織で、会議室が101〜103まで3室あります。利用部門はA, B, C, 外部の4つになっています。時間枠は原則として１時間としてありますが、現実的な制限として、１日あたり7:00〜18:00までの12枠あるとしましょう。まず、会議室を利用したい場合には、この表にレコードを作るということが原則です。皆さんの会社のように、予約せずに使うわ、予約したのに使ってないわ、一覧表は更新されてないわ、ということはないということでお願いします。

ここで、当たり前の制限として、同一の部屋、同一の時間枠で、複数の部門が使うことはないという前提にします。つまり、部屋と時間枠が決まれば、利用部門が決まるということです。すなわち、{部屋, 時間枠} → {利用部門}という関数従属があるというのがビジネスルールから決まっているということです。ここにレコードのない時間枠は誰も予約していません。また、言い換えれば、部屋と時間枠がいずれも同一の2つのレコードは存在していないという意味です。

一方、支払はさらに厄介です。支払に関しては、なぜかこの会社が不思議なルールを持っていて、利用部門が外部の場合、101と102室に関して、17:00以降の時間枠であれば、お金を取るということが決まっています。支払はYESないしはNOで示していますが、実質的には論理値です。つまり、支払がYESかNOかを決めるには、部屋、時間枠、利用部門の情報が必要です。１つでも欠ければ、判定できません。ということで、{部屋, 時間枠, 利用部門} → {支払} という関数従属があるという考え方ができます。

利用部門と支払に関する関数従属があり、以下それぞれを(1)と(2)としてここからアームストロングの公理系を適用すると、以下のような結論が出てきます。

{部屋, 時間枠} → {利用部門} (1)
{部屋, 時間枠, 利用部門} → {支払} (2)

(1)に増加律を適用すると、次が成り立つ

{部屋, 時間枠}⋃{利用部門} → {利用部門}⋃{利用部門}

すなわち次の通りまとめられる。

{部屋, 時間枠, 利用部門} → {利用部門} (3)

合併律を(2)と(3)に適用することで、

{部屋, 時間枠, 利用部門} → {利用部門, 支払} (4)

(1)に増加律を適用すると、次が成り立つ。

{部屋, 時間枠}⋃{部屋, 時間枠} → {利用部門}⋃{部屋, 時間枠}

すなわち次のようにまとめられる。

{部屋, 時間枠} → {利用部門, 部屋, 時間枠} (5)

(4)と(5)を推移律に適用することで、次の結果が得られる。

{部屋, 時間枠} → {利用部門, 支払}

全てのフィールドが関数従属の定義に登場しており、{部屋, 時間枠}が候補キーであることを示している。

つまり、{部屋, 時間枠} が残りのフィールドの値を確定できるので、候補キーはこれになり、当然ながら、主キーも同一のものとなります。(2)の関数従属も、よくみると、全部のフィールドが登場しているので、候補キーの1つとして{部屋, 時間枠, 利用部門}が言えるということになりそうですが、{部屋, 時間枠} は {部屋, 時間枠, 利用部門} の部分集合なので、「最小構成のフィールドのセット」という考え方を使うと、{部屋, 時間枠, 利用部門}の3つのフィールドのセットには冗長なものがあるということになり、候補キーからは外れることになります。ということで、主キーも候補キーも、{部屋, 時間枠}という集合だけということになります。もちろん、{部屋, 時間枠, 利用部門}は、候補キーにフィールドを追加したものと見ればスーパーキーになります。

ここで、ボイス-コッド標準形の定義として、初期の頃の「候補キーからの関数従属しかない」という定義ではなく、のちに改められた「スパーキーからの関数従属ないしは自明な関数従属しかない」という定義があることを紹介します。自明な関数従属とは、X → Yにおいて、Y⊆Xの場合です。つまり、キーフィールドの一部がキーフィールドからの関数従属がある場合です。スーパーキーではなく、自明な関数従属であるという例を考え切れなかったので、今回は「全ての関数従属性はスパーキーをキーとするものである」という定義で進めてみます。

主キーや候補キーの拠り所として求めた {部屋, 時間枠} → {利用部門, 支払} 以外に、{部屋, 時間枠, 利用部門} → {支払} という関数従属もあります。これらは同一のものを指すという見方をするのか、別々のものを指すという見方をするのか、ここが迷うところなのですが、別々のものとみなします。なぜなら、仮に「メモ」みたいなフィールドがさらに増えるとしたら、それはビジネスルールに基づく{部屋, 時間枠, 利用部門} → {支払} という関数従属とは関係ないことになり、この関数従属は「レコード全体」を指さなくなります。つまり、これは部分的なフィールド間の関数従属をさしているものであって、候補キーによる関数従属とは別に、{部屋, 時間枠, 利用部門} → {支払} という関数従属が表には存在するということが言えます。

すると、前の表には、2つの関数従属がありますが、 {部屋, 時間枠}は候補キー、そして {部屋, 時間枠, 利用部門}は候補キーではなくスーパーキーになります。よって、前の表はボイス-コッドの正規形を満たしているので、分解する必要はないのでしょうか？ 実は分解は可能ではありますが、おそらくこの先で説明する第四正規形による判断で分割は可能です。

ということで、利用部門と支払に関してのそれぞれの関数従属性を元に分解すると次のようになります。ここで、この2つの表について、部屋、時間枠、利用部門の3つのフィールドで照合すると、とりあえず見えている範囲では元の表に戻ってくれそうです。しかし、支払判定の表の最後の行には：つまり点々があって、もっとたくさんのフィールドがあることを示唆しています。

まず、このような表を作った場合に矛盾を感じる場面を考えてみます。例えば、会議室予約に新たな予約{102, 2022-06-12 08:00, B}を入れたとしましょう。すると、支払判定にも、{102, 2022-06-12 08:00, B, NO}というレコードを追加する必要があります。つまり、会議室予約をするのに2重にデータ入力をする必要が出てきます。言い換えれば、支払判定の部分集合が会議室予約のような構造になっていて、何か矛盾を感じます。予約に関するフィールドが増えれば分離している意味は見えてくるかも知れませんが、これだけのフィールドでは会議室予約や支払判定の完全な部分集合です。

本来は、「支払判定」は、支払うかどうかの事実を記録したものと見ることができます。であれば、{102, 2022-06-12, B, NO}というレコードは、もともと存在しなければならないということになります。すなわち「事実」が既に記録されている必要があるという見方ができます。であれば、頑張ってレコードを作るということになるかというと、ここでわざわざ時間枠という例を出したのは、論理的には無限大の数のレコードが必要になるということがまずわかります。今月からむこう１年のようなルールがないと有限になりません。

また、仮に部屋=101、時間枠=2022-06-12 07:00というレコードが会議室予約にありますが、支払判定を機能させるには、{101, 2022-06-12 07:00, A, NO}だけでなく、{101, 2022-06-12 07:00, B, NO}{101, 2022-06-12 07:00, C, NO}{101, 2022-06-12 07:00, 外部, NO}という4つのレコード、つまり、利用部門として取りえる全てのパターンを用意しておく必要が出てきます。すると、1日あたり、3 x 12 x4 = 144レコードを確保しないといけません。全く予約のない日でも、144レコードは存在します。そうしないと、未来にどの部門が利用するか分からないので、会議室予約にレコードを追加した段階で支払を確定させるということはできません。また、{101, 2022-06-12 07:00}である4つのレコードのうち、予約が入った後はそのうちの１つのレコードだけがあれば事は足りるのに、会議室予約に存在しない組み合わせのレコードも保持することになります。つまり、支払判定の全てのレコードが使われるわけではないということも気になります。

支払判定と会議室予約は、フィールド構成については部分集合だけど、レコードの集合として見た時にも、部分集合になっています。つまり、将来に会議室予約に登場しそうなレコードが、既に支払判定の表に存在しているという状況でもあります。それはなんとかなるとしたら冗長です。しかも、本来必要なものは会議室予約なわけで、謎なビジネスルールを実現させるものの性能や保守の問題が出そうなでっかい表を使うのかどうかという問題は設計時には頭を悩ませる問題です。すなわち更新整合性を確保するために、大きな犠牲を払う可能性があるということです。

ちなみに、支払を論理値として見るのであれば、YESになるものだけのレコードを作っておくという効率化は可能です。レコードがないものは照合したときにnullになるので、nullはfalseという判断ができていれば、1日あたり、3 x 2 x 4 =24レコードで済みそうです。いずれにしても、あらかじめレコードを作っておくというメンテナンス作業が発生します。

現実にこのようなことが発生するとどうしているでしょうか？ おそらく、表に分けるという人はほぼいないと思います。もちろん、もっと複雑怪奇で、かつ気まぐれなルールだと表に分離する意味があるかも知れませんが、これ以上奇抜なのは「支払は上長が決済する」みたいな感じでしょうか。そうなると、支払フィールドに関する関数従属性はすっかり消えて単なるフィールドになってしまいます。

現状のルールをどのように実装するのが良いでしょうか？ それは、SQLデータベースではビューを定義して、支払フィールドの値を、新たに生成すれば良いのです。支払フィールドの値は、同一レコードの他のフィールドから数式で生成可能ですので、単にビューを作ればOKです。利用部門の値が変われば、改めてビューの値を持ってくれば、支払フィールドの値も更新されます。FileMakerでは計算フィールドとして「支払」を定義すれば良いでしょう。文字列比較が入る面倒な式ですが、開発を生業としている人にとっては楽勝な範囲ではないでしょうか。ただ、UIが絡むとさらに作業は増えそうです。例えば、部門等を選択したら、即座に支払が判定されているような画面を作りたいような場合です。

候補キーではなく、スーパーキーが絡むような例を作ってみたら、思わぬ結果となりました。ちなみに、ビューや計算フィールドはいずれも計算式を利用した導出フィールドの追加ということになります。これらの機能を使うと、意味的には、{部屋, 時間枠, 利用部門} → {支払} という関数従属を、計算式で実現したことになります。つまり、２つの表に分けた場合の「支払判定」は、データベースソフトウェアが持つ多彩な機能をうまく利用すれば、表である必要はないということになります。そうしたビジネスルールをどこで定義するのが望ましいのかということは、開発や保守作業を効率的に進めるための問題として提起出来そうです。いずれにしても、正規化の考え方は重要であり、抽象的な議論ができるとは言え、ビューや計算フィールドを利用するという結論までは導いてくれません。だから正規化は不要ということではなく、考え方としては非常に役立っています。ここでの支払も別の関数従属であるということから、「対処の必要性がある」ということが示されているのです。

ボイス-コッド正規形の話はまだ続きがあるのですが、ここで少し正規形の段階を前に遡って第一正規形の話をしたいと思います。通常、関数従属性については、第二正規形以降で出てくる話なのですが、その議論を進めた後だと、第一正規形もフィールド間に関数従属性を評価できる状態にするための変換であるという言い方もできるということを説明したいと思います。以前に説明した時は、フィールドにあるべき値は元々その全ての値の集合があって、その１要素が入るという前提があるという理由でしたが、見方を変えると関数従属性も理由として言えるということです。

関数従属性は、あるフィールドの値が決まると、別のフィールドの値も確定されるという関係性があるということでした。ここで、今までに出てきていませんが、データはどこかで見たことがあるような第一正規形を満たしていない表を示します。情報化を進める上で「納品書」をターゲットにしつつ、納品書そのものから頭が離れられないと、結果的にこういうデータ構造を作ってしまうでしょう。もちろん、販売明細に商品と個数（通常のカッコで記述）のセットが複数あったり単独であったりとしています。とりあえず主キーは {販売日, 販売先顧客} としますが、同一日に同一顧客に販売しないなどの制約あるとしましょう。ただし、説明の上では便宜的に行番号で行を参照することにします。

この状態の時、主キーになっている2つのフィールドはいいとして、販売明細に対して関数従属性が言えるかどうかはいかがでしょうか？ どうやら商品名が見えていますが、主キーから商品名に対して関数従属があるでしょうか？例えば1行目は「ロボットいか2号」が存在しているので、その商品に決定できるという言い方もできますが、実は他の商品も明細にあるので、{2021-11-09, トイザラシ} → {ロボットいか2号} という関数従属があるとは言いづらいです。つまり、販売明細フィールドの値を1つの塊があるとしたら、{2021-11-09, トイザラシ} → {ロボットいか2号(5), ロボットねずみ3号(3)} という関数従属があるとは言えるとは思いますが、→の右側は明らかに複数のフィールド、複数のレコードという状況を示していて、仮にそれを許すとしてもドメインの記述が複雑怪奇になり、また称号もしづらそうなデータです。ここで、明らかに「商品名」「個数」という複数のフィールドがあることも明白です。

1行目は、部分的に見れば、{2021-11-09, トイザラシ} → {ロボットいか2号} と {2021-11-09, トイザラシ} → {ロボットねずみ3号} の関係性を現状では保持するとしたら、それらを別々のレコードに分離しましょう。その時、商品名と個数の組み合わせのものは、別々のフィールドで記述可能です。そうであれば、第一正規形を満たすものとして、次の表を作ることができます。この場合、4つのフィールドが全部主キーフィールドになります。区別したい基準について考えればその結論は出てくるでしょう。{販売日, 販売先顧客名} だけなら、重複したレコードがあります。つまり、何をいくつ買ったのかという情報がキーとして存在しないと、重複するレコードが存在するということになります。

商品名も個数も主キーになってしまいましたが、いずれも、関数従属性の評価が可能なフィールドになったと言えるでしょう。フィールドに重複の値があったら、複数値が意味があるのか、それらの個別の要素に意味があるのか、明確ではないということも言えます。単独の値のみであれば、関数従属性を検討する土台としては確定した値を扱えるとも言えます。

関数従属性はボイス-コッド正規形までの議論では重要な役割を持っているのですが、第一正規形を経ないと、関数従属性を議論できないというのが逆に追った場合に見えている特徴と言えます。

ここで、関数従属性についてさらに理解を深めるために、公理系の話をします。いきなり数学になり引くかもしれませんが、数学的な証明が可能な世界、つまり理屈が正しいということを言える世界が広がっていることはともかく理解してください。関数従属性についてはアームストロングの公理系としてまとめられています。以下、大文字のアルファベットは、属性の集合です。集合の基本的な記述についての説明はすっ飛ばします。

反射律：Y⊆Xのとき、X→Yが成り立つ
増加律：X→Yのとき、X⋃Z→Y⋃Zが成り立つ
推移律：X→Y及びY→Zのとき、X→Zが成り立つ

これらの規則から、次の規則を導くことができます。

合併律：X→Y及びX→Zのとき、X→Y⋃Zが成り立つ
擬推移律：X→Y及びW⋃Y→Zのとき、X⋃W→Zが成り立つ
分解律：X→Y及びZ⊆Yのとき、X→Zが成り立つ

これらの規則を利用すれば、全ての関数従属性が求められることが証明されているため、関数従属性は、表の特徴を完全に記述することが可能ということの理論的な背景になっていると言えます。

ここで改めて、今までに説明してきたことをさらに細かく説明をします。以下の表は途中で出てきた表ですが、輸送会社が増えているということと、前提を少し変えます。販売明細IDは連番を振っているので、これが主キーになるのはいいかと思います。一方、同一販売日に、同一顧客に対して、同一の商品は2度以上は売らない、つまり、{販売日, 販売先顧客, 商品名} についても候補キーになりうるということにします。「輸送会社」は商品に紐づいているように見えますが、ここでは顧客が指定した先ということにしますので、その意味では、同一日に同じなどのルールはないとして、その都度、レコードごとに決められるということにします。つまり、候補キーとしては {{販売明細ID}, {販売日, 販売先顧客, 商品名}} となっているということを先に示しておきます。

ちなにみ、この表は、ボイス-コッド標準形を満たしています。候補キー以外の関数従属性はありません。ちなみに、そのために、「単価」を取り除いていたりします。

ここで、まず、候補キーに絡んでいない、個数や輸送会社フィールドを見てみます。これらは、すでに入力されているので、１行目は何、２行目は何かということは決まっています。つまり、行を特定すれば、個数や輸送会社は特定できるので、{販売明細ID} → {個数}、{販売明細ID} → {輸送会社} という関数従属があるという見方をします。矢印の右側に同じ「シロネコ」や「4個」というデータがあるとしても、その行ではその値に決まっている。つまりは表にそのように入力されているということで、主キーから決定できる状態にあるという見方をします。

では、候補キーに絡んでいるものはどうでしょう。{販売明細ID} → {販売日}、{販売明細ID} → {販売先顧客名}、{販売明細ID} → {商品名} という関数従属があると見ることができます。これは連番を入力した販売明細IDが、レコードの特定が可能なので、候補キーに絡んでいないフィールドについてと同様に言えるということです。ということで、→の右側は、単独のフィールドとして、主キー以外のものが全部登場しました。

ここで、合併律を考えてみると、{販売明細ID} → {販売日}、{販売明細ID} → {販売先顧客名} の2つの関数従属性は、{販売明細ID} → {販売日, 販売先顧客名} にまとめることができます。これを販売明細IDを除く全てのフィールドに繰り返すと、{販売明細ID} → {販売日, 販売先顧客名, 商品名, 個数, 輸送会社} と合成ができます。つまり、{主キー} → {主キー以外のフィールド} という関数従属性が導かれて結果的に右側はレコードそのものとなります。もちろん、販売明細IDは入っていませんが、入れたところで主キーであることは変わりないので、それも結果的に同じとみなします。すなわち、矢印の左右に主キーを追加しても合併律により関数従属は整理値、左側は同一集合を合同しているので{主キー}のまま、右は全フィールドになるのです。結果的に、候補キーは以下のように、左右の全フィールドを合成すれば表の全部のフィールドになるような記述が可能であり、これによって、個別のフィールドが関数従属していることと、候補キーになりうることを示していると言えます。ちなみに、2つ目の→右側にある「商品明細ID」は、左側の3つのフィールドで十分に候補キーであるということで、右側に記述しても矛盾は生じません。各行で違うので明白かと思われます。

FD1 : {販売明細ID} → {販売日, 販売先顧客名, 商品名, 個数, 輸送会社}
FD2 : {販売日, 販売先顧客名, 商品名} → {販売明細ID, 個数, 輸送会社}

スーパーキーを考えるとき、これら候補キーから、公理系を当てはめて考えれば、スーパーキー自体も関数従属の→の左側に存在可能ということが言えます。例えば、1行目に対して、フィールド輸送会社について増加律を当てはめると、次のようになりますが、矢印の右側はすでに存在しているフィールドを追加することになります。集合なので同一の要素は存在できません。よって矢印の右側は同一になります。

増加律により、以下が成り立つ

{販売明細ID} U {輸送会社}
 → {販売日, 販売先顧客名, 商品名, 個数, 輸送会社} U {輸送会社}

部分集合とのUは元の集合と変わらないので、右辺は次のようになる

{販売日, 販売先顧客名, 商品名, 個数, 輸送会社} U {輸送会社}
 = {販売日, 販売先顧客名, 商品名, 個数, 輸送会社}

すなわち、次の関数従属性も成り立つ

{販売明細ID, 輸送会社} → {販売日, 販売先顧客名, 商品名, 個数, 輸送会社}

このように、候補キーに対して、キーに存在しないフィールドを１つあるいは２つ以上の組み合わせで追加しても、いずれも、→の右側は残りの全フィールドとなり、レコードを特定可能であることに変わりはありません。この時の{販売明細ID, 輸送会社}は、主キーと同様にレコードの特定が可能と言えるので、スーパーキーでもあります。ただ、全部記述していると、大変なので、スーパーキーは頭の中で展開して、その中で、最小のフィールド構成である候補キーを求めるということがまずは必要になるということです。

ちなみに、前回、ボイス-コッド正規形のルールとして、「候補キーからの関数従属性ではない関数従属性は存在しない」言い換えれば「全ての関数従属は候補キーからである」というルールを紹介しました。これは、もともとコッド先生の論文で、ボイス先生と共同で提唱しているルールであり、最初はこれが第三正規形と読んでいたものです。ですが、現在のさまざまな教科書に書かれているボイス-コッド正規形のルールは「スーパーキーからの関数従属性ではない関数従属性は存在しない」言い換えれば「全ての関数従属はスーパーキーからである」となっていて、見かけの上ではより広い範囲を指すようになっています。これはコッド先生より少し後の時代、1982年にZaniolo先生が発表した論文で記述されているルールであり、現在はそちらの記述がスタンダードになっています。

次回は、スーパーキーであることを考慮しないといけない例を出して、ボイス-コッド整形を引き続き紹介します。

さて、第三正規形まで辿り着きましたが、次には第四ではなく、ボイス-コッド正規形（略してBCNF）があります。増永先生の「リレーショナルデータベース入門」によると、ボイス先生は第四と呼びたかったのにコッド先生はこれは第三の改良だろうといった議論をしていると、別の人が第四を提唱してしまったという経緯があるそうです。なので、第3.5正規形という言い方もされるようですが、ここでは「ボイス-コッド標準形」と呼びましょう。ただ、この辺りまで来ると、そもそもの定義を説明してもいきなりは理解できない領域に来ています。ということで、まず、どんな表が第三正規形を満たしているのだけどボイス-コッド正規形を満たしていないと判断するのかを紹介し、その分解の手がかりを与えているのかということを具体的に説明します。ただ、そのサンプル自体がかなり奇抜と思われる可能性は高いので、構えてください（笑。

以下の表「顧客対応」は、あるシステムで、顧客との面談対応のスケジュールを管理しているとしてください。例えば、ZOOM使っているとして、時間枠は１時間ごとに確保できるとします。となると、顧客対応の１レコードは１つの「面談」を示していると言えます。顧客に対して、何回も面談はあるでしょうけど、同じ時間には面談がないとします。つまり、顧客は同時間枠で2つ以上は確保しないという想定にします。となると、それ以外に担当フィールドを用意しましたが、例えばZOOMのURLや、メモ欄などのフィールドを用意しても、{顧客, 時間枠}によってレコードは特定できるということになります。つまり、FD1: {顧客, 時間枠} → {顧客, 時間枠, 担当}という関数従属性があり{顧客, 時間枠}が候補キーと見做せるので、主キーは{顧客, 時間枠}になります。

ところが（ここからが重要）、顧客対応する社員について、担当のフィールドに入っているように見えていますが、ここで、担当者が決まると顧客が決定するという縛りを入れます。つまり、FD2: {担当} → {顧客} という関数従属性があるということです。

ちょっと強引な設定のように思われるかもしれませんが、この状態は、キーフィールド以外から、キーフィールドの一部に、関数従属があるという状況になります。第三正規形で出てきた推移的な関数従属ではないのかと考えるかもしれませんが、実は一連の記事では正確な記述をしていない箇所がありまして、第三正規形は、{主キー} → {主キー外のフィールド} → {主キー外のフィールド} という推移的な関数従属があってはいけないというのが正しい規則だったのです。この例では、{主キー} → {主キー外のフィールド} → {主キー内のフィールド} という推移はあるとは言え、第三正規形においては想定していなかったということです。

先に更新不整合を確認しましょう。いずれも、FD2の存在を問題にすれば考えやすいです。まず、前の表から3行目のAmazenさんとの面談がキャンセルされたとします。すると、YがAmazenの担当であるという情報も失われます（削除不整合）。新たな担当者Kがトイザラシも担当することになったとします。しかし、Kが面談にアサインされるまで、その情報をこの表に入れることはできません（挿入不整合）。担当のXが退社して、トイザラシの担当がLになったとします。すると、複数のレコードに渡って修正をしなければなりません（更新不整合）。ということで、不整合はあるということが確認できました。

いずれにしても、１つの表に複数の関数従属があり、それらを表として分離することで、正規形であることを満たすことができるという考え方になります。ということで、以下のように分離できます。まず、FD2から、「顧客担当」という新しい表が登場します。元は4行でしたが、1、2行目は同一の関連性を表現しているのでまとめてしまって3行になります。そして、担当から顧客を求めることができるので、元の「顧客対応」表では「顧客」フィールドは不要になります。しかしながら、そうなると主キーを構成するフィールドの1つがなくなりますが、ここで再度考え直せば、{時間枠, 担当}を主キーとすることで顧客対応の表の１行は、「面談」を示すことができるので、この2つの表に分解した結果は、元の表が表現していることと同一とみなすことができます。つまり、担当を参照の手がかりにして結合すれば、元の表になります。分解後の顧客担当はもちろん単一の関数従属性しかありませんし、顧客対応についてもこの場合はキーフィールドしかなく、キーの間での関数従属はない、つまり時間が決まれば担当が決まるというわけではない（逆も同様）となっています。

ここで、ボイス-コッド正規形の定義として紹介したいのは、Date先生の「An Introduction to Database Systems, Vol.1」に記載されている「表に存在する関数従属性FD: {X} → {Y}について、Xは候補キーである」というものです。候補キーつまりは表のレコードを特定可能なキーフィールドの集合です。候補キーによるもの以外の関数従属、すなわち関係性があってはいけないということです。今回の最初の表において、FD2のキーである{担当}という集合は、候補キーの中にはなかったということで、元の表はボイス-コッド正規形ではないことになります。

ここまで、段階的に見てきましたが、このボイス-コッド標準形の「候補キー以外のキーを持つ関数従属を分離する」という考え方は、実は第二、第三正規形の時も「成り立っている」と言えます。第二正規形で出した例はこれです。この表の候補キーは、{販売日, 販売先顧客名, 商品名, 個数} でした。この表に、FD: {商品}→{単価} という関数従属性が存在していたのですが、{商品}というキー集合は候補キーにはありませんので、分離をしたのでした。

第三正規形のサンプルは以下の表で、候補キーは{販売明細ID}でした。前の表と前提を少し変えて、同一販売日、同一顧客、同一商品、同一個数の複数のレコードが発生しうるとしており、区別するためにシリアル番号を振ったという前提が加わっています。この場合も、FD: {商品}→{単価} という関数従属性が存在していたのですが、候補キーには{商品}というキーの集合は存在しないので、分離をしたのでした。

つまり、ボイス-コッド正規形の考え方によって、第二、第三正規形の示している問題点と分解のルールは導き出せるということになります。その結果、第二、第三正規形「不要である」と結論づけている書籍もあります（Darwen, An Introduction to Relational Database Theory, 3rd Ed.）。

ところで、今回の最初に示したような表に当たったことありますか？ 個人的にはかなり不思議なビジネスルールではないかと思います。このような場合、最初から表を分離してかからないでしょうか？ もちろん、不思議なビジネスルールは気付きにくいということもあってのここまでの標準形の旅をしてきているということもあるのかもしれませんが、分離を前提に進めていれば、第三だけどボイス-コッド標準形ではないというような状況にはなかなかお目にかかれないと思われます。個人的にも、「あ！これは第三だけどボイス-コッド標準形ではない！」という気づきがあったことは実はありません。そういうこともあって、第三正規形までで十分説もあるのではないかと思います。ですが、第二や第三については、表の中に埋もれている関数従属性についての詳細な検討をする機会にもなるので、学習する上では順番に進めるのが良いと思われます。

Abiteboul先生らの「Foundations of Databases」では、ボイス-コッド正規形の解説の見出しに「同一の事実を2度書くな」（Do Not Represent the Same Fact Twice）と書いてあり、これが正規形を導く本質なのではないでしょうか。つまり、前の表であれば、ロボットいか2号が800円という事実が複数登場するわけです。しかし、表を分ければそれは１回の記述で済むということです。ちなみに、Foundations of Databasesはこちらのサイトより個人利用に限ってダウンロードできます。

ここまでの表の分解は、結果的に「多対１」を探してきて分解をしているということに他ならないわけです。その１に対応する存在を見つける手がかりが、関数従属性という考え方になります。一旦表にしてから検討するということをずっと続けてきましたが、理屈を理解すれば、１対多の関係を見つけて表に分解するということを早期に可能ですし、分解したものが元に戻る、つまり複数の関係を絡めた表が作れるということも考えながら進めることで、データベース設計に進むことができます。しかしながら、表を分割できるということの理論的な背景は、正規化の議論の中にあります。つまり、表は分割可能であるということが証明されているというところで、現在のリレーショナルデータベースが存在していることが重要で、その理論を詳細に追うと、正規化の議論は避けて通れないということです。詳細を知らなくても設計はできるという考え方もできるのですが、その時に出てくるさまざまなノウハウっぽいことは、実は全て背景には正規化を始めとしたデータベースのさまざまな理論が関わっているのです。

第三正規形までやってきました。よく、「第三正規形まではともかくやっておけばいい」みたいな、一つの到達点のように言われています。つまり、それ以上の正規形が難しい、あるいは実用上は滅多にお目にかかれないということがあって、必須の知識は第三くらいかなという意図もあるかもしれません。と言うことで、なんだか重要そうです。

ここで、第二正規形の説明をしたときに示した表をもう一度示します。２つあったのですが、一応説明した順に示します。まず、この表は、もともと１つだった表を２つに分けたもので、第二正規形を満たしているという状態になっています。販売明細の候補キーそして主キーは{販売日, 販売先顧客名, 商品名, 個数} で、要するに全てのフィールドです。一方、商品は候補キー、主キーともに {商品名} です。元の表には２つの異なる関数従属性がありました。つまり、レコードを特定する主キー以外に、FD: {商品名} → {単価} という関数従属性を切り離したのです。こちらを「サンプル１」と呼びます。

第二正規形の説明の時には、連番フィールドを増やしてみるた次の表も示しました。こちらを「サンプル２」としますが、サンプル２においては、サンプル１の主キーだった４つのフィールドが同一のレコードも保存をするというように状況が変わったとしています。したがって、販売明細の候補キー及び主キーは {販売明細ID} となります。サンプル１で主キーだった {販売日, 販売先顧客名, 商品名, 個数} は、もはや、キーとは関係ないフィールドであるとみなします。

サンプル２で存在する感数十属性は、まず主キーに絡めた FD1: {販売明細ID} →{販売日, 販売先顧客名, 商品名, 個数} がありますが、やはり、FD2: {商品名} → {単価} という関数従属性が存在します。しかし、第二正規形は、関数従属の手がかり（→の左側）が、候補キーに含まれているフィールドの部分集合からの別の関数従属が存在する場合には満たさないと定義されています。よって、商品名は候補キーの{販売明細ID}の部分集合ではないので、第二正規形は満たしていると言えます。しかし、サンプル１とサンプル２は基本、同一の表なので、更新整合性の問題は残ります。

ここで、FD1が成り立つ場合は、→の右側は、その部分集合についても成り立つことが言えます。つまり、FD1′: {販売明細ID} →{販売日}や、FD1”: {販売明細ID} →{商品名, 個数}と言う関数従属も存在するはずです。→の右側の１つ１つのフィールドについて、矢印の左側のフィールドが決まれば確定するという意味が関数従属だからです。

ここで、サンプル２について、FD1からFD3: {販売明細ID} →{商品名} と言う関数従属があると考えれば、FD3の→の右側と、FD2の→の左側が同じ集合になっています。したがって、{販売明細ID} → {商品名} → {単価} といった関数従属性の連鎖がこの表では発生しています。このような関数従属は推移的に関数従属すると呼ばれます。

そして、第三正規形は、第二正規形である場合において、推移的な関数従属がない状態のことです。つまり、サンプル２では第二正規形の評価では表の分離は行わなかったものの、第三正規形で、推移的な関数従属がある部分を別の表にすることで、無事にサンプル１と同様、いわゆる「商品マスター」が分離できました。

第二、第三正規形、いずれも、結果的には異なる関数従属が１つの表にまとまっていると言うことを嫌っているのです。言い換えれば、主キーによる関数従属以外は排除したいと言うことでもあります。しかしながら、それを段階的に追うと、主キー以外の関数従属におけるキーフィールドは、主キーの部分集合か、主キー以外にあると言うことになります。それらをフォーマルな形で表現したのがそれぞれ、第二と第三正規形になると言うことです。

ここまでの正規化の部分については、いろんなサイトでも参照されている増永先生の「データベース入門」（サイエンス社）をもとにしています。書籍には正確な記述がなされているのですが、一連の記事ではそのエッセンスをいただいているという大変失礼な使い方になってしまっています。ごめんなさい。ただ、多くのサイトや書籍を見ても、大体、第三正規形までは同じことが記載されています。表現が違うだけとは言いませんが、流れは同じです。ところがその先に出てくるボイス-コッド標準形は、書籍やサイトでかなり言っていることが違います。それでも、示していることは同じなのですが、このことは次回に説明しましょう。また、第二、第三、ボイス-コッド標準形は、それぞれ後のものが前のものの性質を保持していることもあって、そこで色々な考え方が出てきます。番号がついていれば、順番に理解しないといけないだろうと思うのは普通かと思いますし、飛ばして理解するのはなんかチートっぽいので、一連の記事でも順番に説明をしています。ところが、北川先生の「データベースシステム」（昭晃堂）だと、順序があることは説明がありますが、第一の次はいきなり第三を説明し、その時に中間として第二があると言うことを説明しています。つまり、ここでのサンプル１においては、第二正規化する前は、{販売日, 販売先顧客名, 商品名, 個数} → {商品名} → {単価} といった推移的な関数従属性があると見ることもできるので、第二と第三はセットにして考えても良いと言うことです。ちなみに北川先生の書籍では第三正規形の定義では推移的関数従属というキーワードでの説明をしているわけではありません。→の左側がスーパーキーか、→の右側が候補キーの要素であるような場合を第三正規形であると定義しています。上記はサンプル１を第二正規形を経ずに解釈するとしたらどうなるかという私の見解です。

すごく昔に、データベースに興味を持ち、早くからFileMakerなんかを使っていたのですが、4Dの登場もあってリレーショナルデータベースの勉強を始めたのでした。最初に買ったのが北川先生の書籍だったのですが、正直なところ全く理解ができず、他の書籍を読み漁り、いろんな角度で理解ができた上で、北川先生の本がやっと理解できるようになったということを思い出します。もちろん、先生の書籍の問題ではなく、私自身と私の勉強の仕方の問題です。若い頃に通った大学院でもデータベースの講義があったのですが、リレーショナルデータベースのことは書籍の最後にちょろっとだけ書いてあって「今後は出てくるかもしれないけど、講義ではざっくりね」みたいな時代でした。自分としてはそういう講義も受けていてそれなりに知識があったつもりだったのですが、数学の知識やあるいはデータベースシステムの実際など、書籍の理解の前提に至っていないところがあったわけです。古い言い方ですが、カード型のデータベースをいじっているだけの知識では相当狭かったのだと思います。それで、SQLのサンプルを含めて、詳細に書いている本でまずはSQLを理解し、システム構成上何が必要とされているのかと言うことを分かった上で、理論を追っていくことで、理解ができたと言うことがあります。インターネット前の時代だけに、気軽に使えるリレーショナルデータベースもなく、一方で使えるリレーショナルデータベースを標榜する製品が本当にリレーショナルデータベースと言えるのかという議論もされていたような時代で、結果的には書籍のサンプルを「動かさずに」必死に追うと言うのが最初に手がけたことでした。その頃に改めて数学の勉強もしましたが、情報むけの数学は電気科を出ている私にとってはカバー範囲の外だったりしたのでした。一連の記事では色々な学習のパスがつながって理解できるという体験を共有できればと考えて書いています。そして、自分なりのデータベース設計に対する理解を、数式を追わなくてもわかるような説明ができればと考えて続けています。

次回はボイス-コッド正規形に進みますが、ちょっと何日か開くと思います。ネタはほぼ調べきっているのですが、作図と別件の作業があります。お楽しみに。

前回は第二正規化を説明しました。定義を抽象的に言えば難しそうで敬遠しがちかもしれませんが、本質は、関数従属性が1つのテーブルに複数存在しているということにより、何かが問題であるということがあります。今日はその問題とされる「何か」を説明したいのですが、その問題の総称は「更新不整合」あるいは「更新時異状」と呼ばれます。英語ではupdate anomalyですが、日本語の記述は書籍によって異なります。更新、つまり、修正、追加、削除の時に問題があるということになります。

改めて、第二正規化前の表を示すと次のとおりです。この表の名前とともに、どんなフィールドがあるかということを記述する方法として、「販売明細(販売日, 販売先顧客, 商品名, 単価, 個数)」といった表記もされます。一連の記事ではわかりやすく表にしているのですが、より簡潔にはこういう書き方もあります。販売明細は、結果的に、いつ誰に何をどれだけ売ったのかの記録なので、単価を除く {販売日, 販売先顧客, 商品名, 個数} という4つのフィールドが候補キーであり、主キーになるというのが#27の時に説明した内容です。なお、スーパーキーとしては他に、全フィールドを示す{販売日, 販売先顧客, 商品名, 単価, 個数}もあります。

ここで、前の表のレコードに対する主キーは、関数従属の1つの表現であります。この表には、主キーに対するレコードへの関数従属とは少し異なる FD: {商品名} → {単価}、つまり、商品名が決まれば単価は自動的に決まるという関数従属がありました。こちらの関数従属は、主キーによる関数従属とは別の関数従属であり、このデータ持つ性質です。

第二正規化した結果は次のとおりでした。新しくできた「商品」の表は、候補キーとしては{商品名}の1つになり、これを主キーとします。そうすると、元は販売明細に存在した FD: {商品名} → {単価} という関数従属性は無くなっています。当然ながら、単価フィールドを無くしたからです。この関数従属性は「商品」の表に移動しており（あるいは分離した）、それぞれの表は1つの関数従属性を保持するデータとなっています。以下の分割した表から元の表は、「商品名」で照合して、商品の「単価」をくっ付ければOKということは何度も説明しています。このようなことができる特徴を「無損失結合分解」や「情報無損失分解」と呼ばれ、これも数学的に定義されています。ここでは、実体を表で見ながら考えてみて納得感は得られるのではないかと思うので、詳細な数学的な議論は説明しませんが、興味ある方はその辺りもガッツリ勉強しましょう。

正規化前の表で、データの変更を行いたいということが発生したとします。いくつかパターンはあると思いますが、まず、単価が間違っていたとかあるいは発売前に他社製品よりも安くしたいので値下げといけないということがあったとします。例としては「ロボットねずみ3号」を900から850にするといったような事情です。もちろん、正規化前の表の単価のフィールドを変えればいいのですが、おそらくは複数のたくさんのレコードにわたってフィールド修正を行うことになります。これが、正規化後であればどうでしょうか？ いずれの変更処理も、商品の表の1レコードを変更するだけでOKです。

同様な考えで、2行目の販売が、実は「ロボットねずみ3号」ではなく、「ロボットさんま1号」だったとします。この場合、正規化していないと2つのフィールドの変更が発生しますが、正規化後は商品名だけを変えればよく、１つのフィールドの更新だけで済みます。このような性質を「修正不整合」などと呼ばれます。そして、第二正規化により、修正不整合は解消されたと言えます。

ただ、ここでツッコミどころはあると思います。「商品名」が変わったら、正規化後の表においても複数のレコードに渡って変更しなければならなくなります。これは後で説明します。また、もう一つツッコミどころがあって、「販売先顧客名」は変わってもいいのかということもあると思います。これも商品名と関連性がありますが、やはり後で説明をします。

次に正規化前の表に、レコードを追加したいと思います。もちろん、新たに販売明細が発生すると、それは追加できます。ここで、新しい商品「ロボットうさぎ4号」が、990円で発売されたとします。正規化前の表だと、発売日、販売先顧客名、個数が決まるまで、つまりは売れるまでその情報が登場できません。これら、主キーフィールドはnullではいけないというルールがあるからです。つまり、商品名と単価以外のフィールドが空欄のレコードは、登場しないという前提で話をしているのです。ですが、正規化後は、販売明細はそのままで、商品の方に「ロボットうさぎ4号」「990」というレコードを追加することができます。「販売明細」の方には「ロボットうさぎ4号」はまだ登場していないので、元の合成した表に戻した時には、商品にある「ロボットうさぎ4号」は消えてくれます。正規化前にレコード追加の問題があることを、「挿入不整合」などと呼びます。「新しい顧客が発生したら？」というツッコミは、やはり後で説明します。

さらに、正規化前の表からレコードを削除することを考えてみます。たまたま「ロボットさんま1号」は3行目に1つしかありませんが、これを削除したとします。すると、「ロボットさんま1号」が700円であるという情報が、表から欠け落ちます。しかしながら、正規化後の表で、販売明細の3行目を削除しても、商品の方に「ロボットさんま1号」が700円であるという情報が残ります。この問題は、「削除不整合」と呼ばれます。これも、「顧客だって削除したら残っていない」というツッコミがあると思いますが、やはりすぐ後に説明します。

次回から第三正規形以降に移るのですが、こうした正規形を求めるモチベーションは、修正不整合、挿入不整合、削除不整合を合わせた更新不整合の発生を抑えるというところにあります。

ここで、不整合の発生が、単独のフィールドではなく、ここでは商品と単価のように、フィールドの組み合わせに対して発生している点にまず注意をしてください。正規化理論は、あらゆる不整合に対処しようとしているのではありません。フィールドとフィールドの関係がある部分、ここでは理論の上では「リレーション」に対してと言えますし、ここまでの議論では関数従属性を持つフィールドに対してという言い方もできます。よって、ここでの「販売先顧客名」のフィールドに対しては、このフィールドは主キーフィールドの１つですが、このフィールド単独でキーとなるような関数従属はとりあえず見つかっていないので、正規化の理論の枠外と言ってしまっていいでしょう。商品については{商品名} → {単価}という関数従属性が一連のデータにはあるという見方をしているので、そこに対しての更新不整合を問いたいというのが基本にあります。では、単独のフィールドの整合性は関係ないのかというと、もちろん、設計上は大いに関係があります。ですが、理屈の上では、フィールドにあるデータは元々は取り得る値の集合があって、そのどれかの要素がフィールドに来るという考え方がありました。記述しているかどうかはさておいて、少なくとも概念的には元になる集合を考えている「はずである」というこれも前提になります。現実のデータベースにその値を外さないようにする手法はいろいろ組み込まれているので、それを使って不正な値を入らないようにするなどは可能なのです。そこは正規化の理論とは関連は大いにありますが、前提部分の話であったりします。単独の値の保持ではなく、関係性の保持が、更新不整合での注目点となっています。

一方、もう１つ検討すべきこととして「主キーに使われている値は変更しない」という前提もあります。いや、前提かどうかは若干微妙ですが、まず、そう考えるとまさに整合するということがあります。ここでの「販売先顧客名」のフィールドにある「Amazen」などの名前は、そんな前提は無理ですが、ここで間違えることはないという前提がある、つまり、そのフィールドに入るべき集合から取り出すなどするので、間違えるはずはないという前提があるとすればどうでしょう？ 「販売先顧客名」のフィールドの変更は、要するにこの世界にはないと言い切ってしまうわけです。もちろん、『そのフィールドに入るべき集合から取り出す』というあたりで、すでに別のテーブルへの分割が示唆されているものの、更新不整合の問題ではどうやら目を瞑るようです。

ということで、更新不整合はリレーションシップつまり、関数従属の枠内での主キーフィールドでないフィールドの更新に限られるとしたら、修正不整合のところでのツッコミの1つ「商品名の変更はどうよ」ということに答えられていると思います（単に封じているだけと言いたいかもしれません）。また、「顧客」については、フィールドが1つだけなので、関係性は論じられていないので、もう1つのツッコミにも答えていると思います。そして、後の理由は、挿入不整合、削除不整合のツッコミにも答えているでしょう。つまり、削除をおこなって「Amazen」がなくなったとしてAmazenが顧客である情報がなくなったということを問題視するのか、いや、売上無くなったのだし顧客じゃないよとドライにバイバイするのかということになると、後者の考え方を取ります。もし、そのうちAmazenにまた販売があれば、また、「Amazen」と1つのフィールドに1つの書き込み、つまり通常の（不整合とは言えない）編集処理の範囲内で付け加えることができるので、顧客については挿入不整合や削除不整合とは考えないということになります。

現実のデータベース設計ではどうでしょうか？ 「顧客」の名前だけを管理するなんてことは皆無だと思われます。顧客の住所や担当者、電話番号などなど顧客に対する様々な属性をフィールドとして追加しないと、実務は回りません。そもそも、同一名義の会社は存在するので、会社名自体を主キーにするのは実用上はあり得ません。同一名や、同一会社で異なる販売先などといろんなバリエーションがあり、結果的に「顧客番号」みたいなIDとなる値を振ることになります。ということで、顧客も商品も、たくさんの主キーでないフィールドを持つことになるので、そこで顧客に関わる関数従属が発生して、「顧客」が独立した表になるかと思われます。

フィールド１つのリレーションというのは全くないわけではないものの、どちらかといえば滅多に出ません。これは１つの話題としては面白いので、覚えていたらまた書いてみたいと思います。ただ、ここで、無理やり、顧客マスターが登場する理屈を考えるとしたら、自分自身への関数従属があるでしょう。つまり、FD: {販売先顧客名} → {販売先顧客名} という関数従属です。当然、1対1に対応するので関数従属性はあると言えばあるのですが、これを言い出すと、実は全てのフィールドについて言えてしまうのですが、ともかく販売先顧客名だけを考えて、自分自身に関数従属しているとしたら、顧客(販売先顧客名, 販売先顧客名) という表を作ることができます。しかし、2つのフィールドの内容は同一なので、表としては顧客(販売先顧客名)であれば十分である、すなわち表として持ち得る情報は等価であると言えるので、これにより顧客マスターの分離は可能です。明細の最初の2行を削除しても、顧客に「トイザラシ」が存在することは残り、挿入不整合、削除不整合は防げそうです。ただし、この場合だと、販売先顧客名は主キーフィールドなので、修正不整合の問題は残ります。この考えを一般的に適用すると、「個数」には「個数マスター」に登録しないと明細に登録できないというようなことになります。もっとも「個数」の場合は、整数というプリミティブな型なので、他に日付も加えてそれを別の表で一旦定義して使うというのは、結果的にかえって処理手間が増えるだけになるので、もちろん現実的ではありません。数値や付随情報があるということは一般には考えにくいです。日付は隠れた関係性を持つことがあって要注意なのですが、日付もやはり単独の値です。いずれにいしても、このように、一般化しすぎると明白におかしな方向に行くので、元々関数従属性の定義では、R(X, Y, Z)において、XによってYが決定するということで、XとYが最初から分離しているということを示唆しているあたりで、自身への関数従属については除外されているとも考えられます。ただし、Y⊆Xの場合は「自明な関数従属性」と呼ばれ、Y = Xの場合も含まれるので、関数従属という考え方は可能です。つまり、選択的に自分自身への関数従属性を考えれば、現状での「販売明細」でも「顧客」テーブルの分離はできるとも言えるかもしれません。そうなると、今度は「選択基準」が欲しくなりますが、現状では、要求があるから、あるいは問題が出る可能性があるから、ということでしか言えないでしょう。以前にフィールドの値はラベルか測定値だと結論づけていますが、ラベルには付帯する属性はあることが多いのですが、測定値はその値が単独で意味を持つとも言えます。であれば、「ラベルを値に持つフィールドは自身への関数従属とみなして良い」みたいなルールを与えれば、第二正規形の段階で、顧客は別の表に分離できそうですが、もちろん、直感で言っていて、それが体系の中で問題ないかは全然検証していませんのでご理解ください。

理論には必ず前提があります。データベースの理論は一度数学の世界に入っているところで完全な理論体系が確立されているのですが、その考え方を現実のシステムに適用するあたりで、前提は忘れがちになります。しかしながら、その前提が重要であることは言うまでもありません。

このシリーズの記事は、最初はわかりやすい話が多いのですが、だんだん難しくなってきています。Facebookのいいねが次第に減り、Twitterのリツイートする顔ぶれが変わってきていますが、頑張って続けたいと思います。

さて、前回のキーフィールドの話をしましたが、キーフィールドはnullを取らない前提があるという話を書いておくべきでした。nullは何と比較してもfalseになるので、照合できないから、キーフィールドの値に使えないのです。nullを許すと、照合出来ない、すなわち検索が出来ないレコードが発生するので、キーフィールドによってレコードを特定するという前提が崩れるのです。ちなみに、MySQLだと、論理式の値にNULLがあれば、結果は必ずNULLになります。「SELECT NULL=NULL;」は、NULLを返し、結果的にfalseとみなします。PHPだと通常はnullかどうかを確認するのはis_null関数を使いますが、null == nullの結果は1つまりtrueになります。SQLと言語ではnullの考え方に違いがあります。

前回に関数従属性という考え方を紹介しましたが、ここで、さらに厳密な「完全関数従属」という考え方をまず説明します。関数従属は、あるフィールドの値が、別のフィールドの値により決まるという考え方でした。この時、元になるフィールドの集合の真部分集合をキーフィールドとして関数従属性が成り立っていないような場合を、完全関数従属していると表現します。ざっくり言えば、フィールド同士の関連の中に、異質な関連が混ざっているような感じでしょうか？ そして、第二正規形は、第一正規形が成り立つと同時に、キーフィールドに含まれていないフィールドが、それぞれの候補キーに対して完全関数従属している場合のことです。

ややこしいですね。具体例で細かく見ていきましょう。以下は前回の登場した販売明細の表です。赤いボックスは主キーフィールドを意味しています。

これだけのフィールドしかない場合は、候補キー、主キーとも、{販売日, 販売先顧客名, 商品名, 個数} の4つのフィールドを含むものしかありません。よってこの1つの集合に関して完全関数従属しているかどうかを調べるのですが、ここで、FD: {商品名} → {単価} という関数従属があるとします。つまり、商品が決まると、単価が確定すると考えることにします。この辺り、前提条件次第で結果が変わりますので、前提条件をきちんと把握してください。

新たに認識した関数従属のキーフィールドである{商品名}は、候補キーとなっている{販売日, 販売先顧客名, 商品名, 個数}の真部分集合です。完全従属性の定義を検討すると、『元になるフィールドの集合の真部分集合をキーフィールドとして関数従属性が成り立って』います。完全関数従属はそれが否定されないといけないので、完全関数従属していません。また、候補キーに含まれていない{単価}が決定されるという関数従属なので、第二正規形の定義が崩れます。よって、この表は第二正規形を満たしていません。

まず、第二正規形を満たすにはどうすれば良いかということを考えますが、結論は表を分離します。キーフィールドの真部分集合の関数従属が表の中に登場しないように表を分離します。つまり、単価を切り離せば良いということになります。ここで、表は分離して、後から結合できるという事実があり、この辺りは数学で記述するのはかなり難しい話になりますが、このシリーズのだいぶん前に実例で感覚的に説明をしているので、これは既知の事実だとします。つまり、以下のようになります。新たに出てきた商品の表の候補キー、主キーはいずれも{商品名}であるとします。

販売明細は、候補キー、主キーとも、{販売日, 販売先顧客名, 商品名, 個数} の4つのフィールドの集合になります。ただし、そのどの真部分集合を考えても、関数従属性は存在しません。よって、単価を分離し、商品名で照合して元のデータが得られるという状況にすることで完全関数従属性が成り立ち、第二正規形を満たすことになります。

要するにマスターを分離することか？と言われればそうです。ただ、この段階で、全てのマスターが分離できるわけではありません。さらに別の正規化のルールも必要になります。

前回、同じような表で以下のようなものも提示しました。こちらは「販売明細ID」フィールドがあります。ここで、この表の前提を少し拡張します。前回のキーフィールドの議論では、販売日〜個数のフィールドには重複するレコードがないという前提を引き継ぎましたが、それをやめてみましょう。良し悪しの問題はちょっと目を瞑って、番号を振ったのだから、区別できるでしょうという大雑把な設計方針が導入されたと思ってください。{販売明細ID}を候補キー、主キーとして、{販売日, 販売先顧客名, 商品名, 個数}は、もはや候補キーではないと前提を変えてみます。

単一のフィールドが候補キーであり主キーであるような状況は意外にシンプルです。ここで、関数従属性はどうなるかというと、例えば、販売明細IDが1003だと、販売先はAmazen、商品名はロボットさんま1号など、残りのフィールド全て、1つのフィールド販売明細IDが決まれば決まっているということになります。つまり、関数従属としては、FD: {販売明細ID} → {販売日}、FD: {販売明細ID} → {販売先顧客名}、FD: {販売明細ID} → {商品名}、FD: {販売明細ID} → {単価}、FD: {販売明細ID} → {個数}、といった関係があるとみなされます。これも厳密に記述すると、FD: {販売明細ID} → {販売日, 販売先顧客名, 商品名, 単価, 個数} ということになり、矢印の右側の集合の部分集合を持つ関数従属性が大量に存在するということになります。つまり、販売明細ID単独で、レコードの特定が可能となっているということです。

であれば、候補キーの真部分集合はありますか？ 単一の集合の真部分集合は空集合を除いてあり得ません。空集合は実はnullなので、これはキーとしては排除するのが定義でした。ということで、真部分集合の関数従属は存在しません。もう一度、完全関数従属の定義に立ち返ると『元になるフィールドの集合の真部分集合をキーフィールドとして関数従属性が成り立っていない』であり、まさにその通りなので、実はこの販売明細IDを主キーにした表は、すでに第二正規形が成り立っています。なお、商品が決まれば単価が決まるという事実は存在していて、FD: {商品名} → {単価} という関数従属性はあるとしても、こちらの表では{商品名}という集合は、候補キーの集合{販売明細ID}の新部分集合ではないので、完全従属性は保持されています。不思議ですが、ここで、{販売日, 販売先顧客名, 商品名, 個数}は主キーではなく、それらが重複するようなレコードの存在を肯定したという状況の変化があったので、実は表としての性質が変わってしまっているということです。

ふと思うのは、Excelの表って、最初から行番号が振られているので、第一正規形をクリアすれば、行番号を主キーと見做せば自動的に第二正規形も成り立つってことになりますね。

あれ？じゃあ、マスターって分離できないのか？と思われるかと思いますが、後半に示した表から商品マスターが取り出されるのは、実は第三正規形を満たすためのテーブル分離で実現されます。次回は、なぜ正規化が必要なのかという議論に必要な更新時異常という考え方を紹介し、さらにその後に第三正規形の説明をしようと思います。

前回は、データはラベルか測定値だろうという話をしましたが、これはあるセミナーでここで説明しているような設計の話をしたとき、受講された方の一番の疑問だったらしく、細かく説明できなかったところなのです。こうしてブログに書く機会ができて個人的にはなんとなく一段落です。今回から、データの集まりである表についての話です。フィールドには名前が付いているので、その名前を手がかりにデータを取り出せますが、レコードにはそうした識別のための材料が、あるのかないのか？そこがポイントです。言い換えれば、レコードを特定する、あるいは一定の条件のレコードに絞り込むなどのいわゆる検索ができる状態の表でなければなりません。今回はとりあえず、架空の会社の例で出てきたこの表からスタートしましょう。営業部門が販売した明細を記録しているという想定です。

もちろん、いろんな意味で省略は多いのですが、この表では5つのフィールドがあってそれでこの会社の販売データが記録されているとします。今、たまたま、５つのフィールドが全て同一のレコードは存在しませんが、さらに3行目と同じレコードが6行目に追加されるという状況はあるでしょうか？ あるいかないかは、結果的にその会社の状況によると思います。つまり、前提条件次第で変わります。ロボットさんま1号が非常によく売れて、Amazonは11月11日に何回も注文したとしましょう。個数は毎回違うかもしれませんが、同じかもしれません。個数まで同じなら、行の区別がつかなくなります。ちなみに、そういう状況にしますか？ しないでしょう。それを手入力しているとしたら、完全に同じレコードが発生する状況で、「さっきの注文入れたっけ？ まだだっけ？」という確認ができません。もちろん、その日の発注を頭からチェックするということもあるかもしれませんが、かえって効率を落とします。現実にはどうしましょう？ 多分、販売日と同じかあるいは「入力日時」などのタイムスタンプのフィールドを増やすなど、同じ内容のレコードが発生しないような工夫をすると思います。ID番号を登場させるのは反則かもしれませんが、例えば、納品書IDがあれば、おそらくは区別できるでしょう。ただ、これも、同一の納品書で同じ商品に対する明細は２つ以上存在する事はない、つまり、単一の納品書では、明細には１つの商品は１回登場するか、全く登場しないかのいずれかになるという前提もあります。

まず、こうした表では完全に重複したレコードがないことを前提にします。その結果、表はレコードの集合であると考えることができるわけです。集合は重複した要素を持たないというのは定義です。ただ、センサー記録などでは、タイムスタンプを落としたとしたらそういうこともあるかもしれませんが、これは、センサー入力が重複しないことを前提としているからです。つまり、データ収集が重複がないということがほぼ保証できるような自動化プログラムで行われているような場合には、後から集計した結果が重要であり、元データに重複はあるかもしれませんが、現実は正しく記録されているとも言えます。今回はそのような状況は特殊事例として除外してください。もっとも、そのような場合でもタイムスタンプなどを記録することで完全な重複がないようにするのはおそらく一般的であると思います。

ここで「関数従属性」という考え方を導入します。あるフィールドXの値が決まると、別のフィールドYの値が決まるという考え方です。Wikipediaに倣って、「FD: X → Y 」と記述しましょう。X、Yはいずれも属性の集合です。前の販売明細の表で言えば、商品名に対する単価は、商品名が決まれば単価がどうやら確定するようなので、「FD1: {商品名} → {単価}」とみなします。あるフィールドの値が別のフィールドの値で決まってしまうという特徴が、表全体に渡って成り立つかどうかを検討します。では、販売先顧客名と単価はどうでしょう？ たまたまある顧客が同一の商品しか注文しないと表の中では関数従属性があるように見えるかもしれませんが、顧客は複数の商品から選択するという前提があれば、単価は顧客では決まらず商品によってのみ決まるので、一般には関数従属性はないとみなすのが自然です。「関数従属性」と言うと名前が難しそうで、かつ数学的に記述するとさらに難しそうですが、「現実はどうなっている」ということが前提にある概念です。関数だから、その内部の計算方法は？などと疑問に持つかもしれませんが、ここでの関数は「入力を与えれば値が返る」という意味の抽象的な関数で、一般にはプログラムや数式では書けなくはないものの完全には書きにくいものです。データの集合がそういう性質を持つという議論です。

この関数従属性を手がかりにして、「レコードを一意に特定できるフィールドの集合」を考えます。この「レコードを」というところがまずポイントで、この性質を見極めておかないと、実用上支障が出そうです。そして、「一意に」というのも重要です。何か特定のデータに特定できるということです。販売明細は5つのフィールドがあるので、つまりはそれら全てのフィールドを決定可能なフィールドの集合Xとして、「FD2: X → {販売日, 販売先顧客, 商品名, 単価, 個数}」を満たすXを認識する必要があります。この概念を一般に「キー」と呼びます。例えば、Xには矢印の先そのもの、つまり全部のフィールドは多分含まれるでしょう。つまり、Xの1つは「A1 = {販売日, 販売先顧客, 商品名, 単価, 個数}」になります。

販売日がこれ、販売先顧客はこれ、商品名はこれ、単価はこれ、個数はこれ、と列挙すれば、その値を持つレコードを探してきて１つのレコードが特定できると言うことです。販売日がなかったらどうでしょう。同一顧客に同一商品を同一個数、別の日に販売した場合、どっちのレコードなのかが区別がつかなくなり、キーの役割を果たしません。ここですでにFD1が見つかっているとすれば、単価は商品名から求められるため、キーの構成要素としてなくてもいいということになります。つまり、単価を知らなくても、どのレコードかは特定可能なので、「FD2: {販売日, 販売先顧客, 商品名, 個数} → {販売日, 販売先顧客, 商品名, 単価, 個数}」となり、「A2 = {販売日, 販売先顧客, 商品名, 個数}」もFD2のXとして成り立ちます。

このように、キーは複数のフィールドの組み合わせも取り得ます。考えられるありとあらゆるキーを集めたものは「スーパーキー」と呼ばれます。ここまでの議論だと、{A1, A2}がスーパーキーです。その中で、余分なものがないもの、つまりなるべく要素数を少なくとしたものが「候補キー」と呼ばれます。つまり、候補キーとしては「{A2}」となります。さらに、候補キーの中で、実際にシステムの中でレコードの特定に使うものが「主キー」と呼ばれます。ここではA2しかないので、A2を主キーとすることになりますが、現実にはフィールドが増えた時などにもう少し候補キーを探すことになるでしょう。4つのフィールドで常に検索するというのはちょっと面倒ですね。

ここで、IDフィールドを販売明細に加えてみます。現実にやっていることの説明が必要ですね。「販売明細ID」フィールドを追加して、一意な数値をつけてみました。

ここで新たに、A3 = {販売明細ID}が、スーパーキー、候補キーの仲間に入りました。スーパーキーを全部挙げるのは実は大変です。A4 = {販売明細ID, 販売日}、A5 = {販売明細ID, 販売先顧客名} … などと、結果的には販売明細IDとの他のフィールドとのあらゆる組み合わせが、レコードの特定が可能です。全部列挙大会をやってもいいのですが、疲れるのでやめます。販売明細ID単体でレコードの特定が可能なので、冗長性を無視して「可能である」と言う点だけに注目すれば、そうなります。候補キーは冗長なものが全部排除されるとしたら、{A2, A3}となるでしょう。そして主キーはより単純なA3になると言うのが一般的な考え方の1つになります。それがいいかどうかは、結果的にどんなシステムを作るかと言うことと擦り合わせは必要ですが、キーの定義はこのようになります。スーパーキーに関して、一度は手で全部書くのが学習になるかもしれませんが、実用的にはスーパーキーを全部列挙するような仕様書は誰も読みません。場合によっては候補キーを挙げて、２つのうちどちらを選んだかと言う理由を記述する場合もあるかもしれませんが、設計者は候補キーが頭の中でサッと浮かび、サクッと主キーをどうするかという議論に入ります。今回の場合A3のように決まったことしか仕様書に書いてないとしても、A2のような実データフィールドの主キーはあるものの、番号振ってあって一意だからこっちでいいよね的なフィーリングは行間にしか書かれてないことが普通です。

キーの中でも３種類もあってややこしいと思われるかもしれませんが、設計上は主キーを特定できればそれでOKです。しかしながら、なぜ、主キーが存在する意味があるのかと言うことを定義するために、関数従属性という考え方、あらゆるキーから意味のあるキーの選択といったことを経て、主キーの考え方に至ります。スーパーキーのように一度「全て」を考えて絞る、ここでは冗長性のないもの、そして実用性を考えるということに分解して仕組みが成り立っているということは、結構重要なのではないでしょうか。