Blog by msyk

データベースを稼働させるには、色々な前提があるということはこれまでにも説明してきました。例えば、主キーという存在が必要であるということは、主キーとして認識しているフィールドのセットに対して、それらが重複するレコードが存在しないことが前提となります。これを、「後はよろしく」と言われても、実装が込み入るとか、バグがあったらどうするなど懸念点ばかりが思いつきます。しかしながら、データベースには、前提が必ず満たされているということを保証するための制約の機能があります。この機能を利用することで、前提が崩れるデータはデータベースには存在しないと保証できると思っていただいていいでしょう。データベースにバグはないのと思われるかもしれませんが、今時のデータベースソフトは長年の開発によって、こうした「前提の前提」のような発生しては大いに困ってしまうような箇所のバグはまずないと思ってOKです。

前提を満たさない状況にしようとしたときにエラーを出してデータの書き込みをしないというのが一般的な動作になりますが、システム側はそうしたデータベースのエラーを適切に処理する必要があります。システムが止まってはいけません。そしておそらく書き込もうとしたデータが正しくないわけで、そのことがわかるようなユーザインタフェースを作る必要があることも言うまでもありません。結果的に、そうしたユーザインタフェースをまともに作るとなると、「あらかじめ前提を満たすかどうかをチェックする」と言うことに究極的にはなってしまって、それじゃあ別にデータベースに制約機能なくてもいいじゃないかと思ってしまうかもしれません。このことはむしろ、データベースとUI側の両方に制約をかけることで、より確実にシステムが動作すると言うことで前向きに捉えるべきでしょう。いずれにしても、データベースが制約をかけているから「大丈夫」と放置するのではなく、きちんとシステム側の特にUIにもきちんとした対処は必要であることは認識すべき事柄です。

データベースのフィールドは、NULLと言う特殊な値を取ることができますが、まず、「NULLにはならない」と言う制約が付与できます。CREATE TABLE文のフィールド定義で、NOT NULLと言うキーワードを付与するのが一般的です。NULLの書き込みは意図的なことをしない限りは通常はなく、NOT NULLはフィールドの初期値に関して考えておくことになるのではないでしょうか。一般にはフィールドの初期値がNULLになりますが、この制約をつけても初期値はNULLのままです。つまり、既定値は許されない状態になるので、必ず値を与えるか、フィールドの定義部分で「個数 INT NOT NULL DEFAULT 1,」などとして、DEFAULTに続いて値を指定するか、何らかの対処が必要になります。

そして、主キーフィールド等で利用できる「一意制約」の機能もあります。この機能はインデックスの仕組みを利用するのが一般的です。インデックスは、フィールドのデータがどのレコードにあるのかを特定できる情報をデータベース側で自動的に作ってくれる機能です。その実現方法も興味深いところですが、完全にブラックボックス的に動いてくれるので、要するに「検索結果を早く提供してくれる機能」と割り切って考えれば良いでしょう。例えば、文字列を検索する場合、インデックスがないとしたら、全データを最初から最後までチェックしないといけません。インデックスがあると、その文字列があるレコードは、これとこれと…と特定してくれるので、当然ながら検索は速くなります。現在のコンピュータ環境では、ちょっとしたデータであればあまりインデックスの効果はないと思っている方もいらっしゃると思いますが、長いデータや大量のデータでは十分にインデックスによるスピードアップは期待できます。少量のデータだからといってインデックスがあれば遅くなるようなことはないので、検索に使うフィールドにはインデックスを作っておくのは基本中の基本になります。仮にインデックスを作らなくてもサクサク動いているとしても、インデックスのない検索はデータに比例して時間がかかるようになるため、いつか耐えられないくらいになってしまう可能性を秘めています。問題がないと思っても将来に備えてインデックスを作っておくべきです。

このインデックスは、値の重複を許す場合と、許さない場合があり、インデックス作成時に指定をします。もちろん、前者は検索の高速化が主な用途ですが、後者は主キーに設定するために利用できます。そして、フィールドは、１つだけではなく、フィールドの組み合わせに対してもインデックスは作成できます。例えば、{商品名, 顧客名, 個数}に対して重複を許さないインデックスを作成すると、この3つのフィールドが全部同じレコードは作成できなくなります。商品名が同じものがいくつもあるのは構いません。ちなみに、レコードを追加したりフィールドを変更した時に、インデックスは更新されるのですが、インデックスが正しく更新されないと、追加や変更の処理もキャンセルされてしまいます。インデックスはテーブルのデータと連携をしていて、「制約」として動作すると言うわけです。もちろん、データベースからはエラーがレポートされるので、適切に処理をしなければなりません。

MySQLを例にとって説明しますが、例えば、次のようなテーブル定義があったとします。とりあえず、販売明細IDを主キーとし、連番を入力することにします。記述方法の1つとして、( ) 内の最後にPRIMARY KEYとしてどのフィールドが主キーかを指定しています。これにより、自動的に重複を許さないインデックスが作成され、フィールド「販売明細ID」の値と連携することになります。もちろん、ある数値を与えれば、どのレコードなのかと言うことを瞬時に返すインデックスの意味も持ちますが、販売明細IDに既に入力してある値を新たに挿入しようとしてもエラーを返す返す一意制約のためのインデックとしても機能します。ちなみに、これによりPRIMARYと言う名前のインデックスが作成されます。インデックスは、テーブル名とフィールド名の情報を持ち、テーブルごとに管理されるので、結果的にデータベース内にPRIMARYと言う名前のインデックスがいくつも作られることになるでしょう。

# DROP TABLE IF EXISTS 販売明細;
CREATE TABLE 販売明細 (
  販売明細ID INT AUTO_INCREMENT,
  納品書ID INT NOT NULL,
  商品ID INT,
  個数 INT NOT NULL DEFAULT 1,
  PRIMARY KEY(販売明細ID)
);

MySQLでは主キーを設定すると自動的にインデックスが作られるので、例えば、「CREATE UNIQUE INDEX 販売明細_販売明細ID ON 販売明細 (販売明細ID);」のようなSQL文で新たにインデックスを作成する必要はありません。ちなみに、重複を許さないという特性は、UNIQUEと言うキーワードで指定をします。INDEXの後はインデックス名、そして、ONの後に「テーブル名（フィールド名）」と言う記述を行います。

なお、ここで、モデルの上で、同一の納品書の明細には、1つの商品は1行だけ登場するか、全く登場しないかどのどちらかであるという制約があったとします。そうすると、次のような重複を許さないインデックスを作成しておくことで、主キーの制約をかけることができます。おそらく、納品書のUIで、明細行を追加し（その時には既に納品書IDは確定しているので入力済みになる）、商品を選択したときに既に同じ商品がその納品書の明細に存在していれば、制約違反のエラーが出てきます。それを避けるために、商品の選択肢に既に入力してある商品が出てこないようにするのが1つの方法ですが、そこまでの対処をUIで行うと、UI側とデータベース側の２重対処をしたことになります。そのことが冗長なのか、それとも確実な動作を支える実装なのかは意見が別れるところですが、設計とはそういう場合にどうするのかと言うこことを決める作業に他なりません。そのようなことを早い段階から検討できるようになりましょう。

CREATE UNIQUE INDEX 販売明細_主キー ON 販売明細 (納品書ID, 商品ID);

なお、UNIQUEなインデックスを設定するだけだと、NULLは許可されている状態になります。前述のようなユーザインタフェースだと、商品IDがNULLの期間が発生するので、NULL許可はワークフロー上は必要であるとも言えます。ですが、いずれにしても、NULLにならないようにしたいということであれば、テーブル定義にNOT NULLを指定します。MySQLのUNIQUE INDEXは、例えばこの場合だと、納品書=1、商品ID=NULLというレコードは複数存在できるようになっています。つまり、NULLの値がある場合には、一意性の評価はしないと見ることができます。これは、NULL = NULLはFALSEであると評価できるところから来ており、NULLは「どのレコードも異なる」と判定されてしまいます。つまり、主キーフィールドの１つでもNULLがあれば、見かけは同一のレコードも作成できてしまいます。もちろん、主キーフィールドはNULLにならないと言うのが「原則」という考え方もできるのですが、ワークフロー上NULLである期間を許すとなると、落とし所は難しくなります。もちろん、現実の案件では、色々な要求に対する実現手法や実現度合いを見計らいトレードオフを行なって最終決定するしかないでしょう。

さらに「外部キー制約」のこともあるのですが、ちょっと長くなったので、これは次回に説明しましょう。

ER図やクラス図で、テーブル設計を記述することはよく行われています。その結果をもとに、CREATE TABLE文を駆使して、テーブル定義のためのSQL文を記述し、データベースに読み込ませて使えるようにします。文法的な間違いは随時直すとして、設計の変更などが発生してフィールドを追加するなど、データベースの変更は開発中には付き物です。ということで、モデルとテーブル定義のSQL文が分離した瞬間、両方をメンテナンスするということになり、変更を適用するための記述はSQL側になるので、やはりモデルの修正はおろそかになってしまいます。常に、モデルから生成できればいいのですが、データベース特有の記述を毎回手で治すような事態だと、別々にしているのと手間は変わりません。ただ、最近のWebアプリケーションフレームワークは、モデルをコードで記述することで、自動的にデータベースの生成や、あるいは設計が変わった時のデータの移行なども含めてやってくれるので、その意味ではモデルベースではありますが、ERやクラス図と、モデルのコードの変換や対応付というまた同じ問題に直面します。この辺り、なかなか良い方法はないというか、労力をかけないで、ドキュメントをメンテナンスするということへの工夫をとにかく頑張って凝らさないといけないところではあります。ERやクラス図を最初に作り、その後、SQLやコードなど実装が進むと、ER/クラス図が放置されるのが常かもしれませんが、開発が落ち着いたら、実情と合うようにモデルを修正しておくようにしたいところです。

実際に開発を始めると、テーブルだけでなく、ビューも使います。テーブルのやりとりだけだと、何度もアクセスが必要になります。そこで、SELECT文でいくつかのテーブルを結合した結果が欲しくなるのですが、ソースコードにSELECT文を展開する場合もあるかもしれませんが、ビューを定義して、あたかも「1つのテーブル」のように扱える仕組みは便利です。1つのビューをいくつかの目的に使い回しもできます。ビューを最初から用意するというのが１つの理想的な状況ですが、現実には開発をしながらビューを追加することが多いと思われます。つまり、実際にユーザインタフェースのコードを記述するときに、「このページにはこのフィールドがいるけど…」と検討を始めて、「このテーブルと、あのテーブルと、そちらのテーブルを結合しないといけないな」などを考えて、CREATE VIEW AS SELECT …などと書いていることが多いと思われます。実際にUIのコードと対峙しないと、必要なビューというのが見えてこないというか、気合を入れて考える気がしないのではないでしょうか。そして、フィールドが足りないとビューの定義を書き直して、DROP VIEWしてまたCREATE VIEWをするという手順でメンテナンスを繰り返します。テーブルを変更するときはALTER TABLEコマンドを使わないと、DROP TABLEしたらテーブルが消えてしまいます。ですが、ビューは、一時的にはどこかに結果はあるとしても、設計情報だけが必須のものなので、DROPしてCREATEできる点では気軽に変更できます。

気軽に作成した結果、何が起こるかというと、ありがちなのは似たようなビューをいくつも作っていたり、名前の付け方が統一されていなかったりといったことが起こりがちです。動いていればいいという考え方もありますが、できれば、後からのメンテナンスをやりやすいように構成されていたいところです。ということで、ビューも、テーブル設計に続いて設計内容として記述できればと思ってしまいます。ER図作成ソフトの中にはER図にビューという要素をエンティティの1つとして配置できるものもありますが、結果的に構造がテーブルと同じということで、テーブルの代わりに置けなくもないという感じの機能です。むしろ、ER図ではビューは記述しないのが一般的と言えるかと思われますが、実のところ、この理由を記載した記事などはみたことがありません。一番考えられるのは、ビューの設計は、SQL文でテキストで記述してしまうので、それを確認するのがとにかく確実で早いということがあるからではないかということです。当然、そこにはビューの設計情報が全部あり、テキストなので検索も確実に引っ掛かります。しかしながら、テキストファイルに大量にビューの定義があると、前に説明したように、同じような別のビューを定義してしまうということにもつながります。開発で頭が爆発しそうな時でも、冷静に既存のビューを参照できるようにするために、クラス図にビューも記述するのが良いのではないかと考えて若干、実践をしてみましたが、ただ、やり始めてあまり時間が経過していないので、効果の程はまだ未知数です。前回に出てきたテーブル設計のクラス図が上半分の6つのクラスにあるものと同様です。新たに、ビューとして2つ定義したことを書き加えました。ビューは下の2つです。まず、ビューの名前には、<<view>>というステレオタイプを記述し、通常のテーブルと区別できるようにしました。ついでに色分けもしてあります。

ビューが定義されている状態の何を知りたいのかということですが、まずは、どのテーブルあるいはビューをもとにしているのかという情報かと思います。つまり、FROM句に並ぶテーブルです。販売明細テーブルの内容に、商品の情報をくっつけた「販売情報_商品付」というビューが定義されています。このビューは、販売情報と商品の2つのテーブルをもとに作られていることが分かります。左側のビューとも繋がっていますが、この線は、左側のビューが「販売情報_商品付」をもとに作られているものですので、ビューとビューを結ぶ線はどちらが大元なのかをチェックする必要があります。「販売情報_商品付」は、基本的に「販売明細」にあるレコードが、そのままビューの側にも存在するということを期待しています。つまり販売明細テーブルの拡張版なのです。そこで、黒いダイア付きの線、コンポジションを利用して、「販売情報_商品付」が販売明細と同一のレコード構造、つまり、販売明細の１レコードが、「販売情報_商品付」の1レコードになることを示唆しています。コンポジションの意味から若干外れるのですが、販売明細の１つ１つのレコードをパーツとして、それをそのまま組み立てているというイメージで、当たらずとも遠からずかと思われます。そして、商品からの線は、「販売情報_商品付」ビューが商品とも結合されていることを意味してますが、通常の結合でない場合、ここでは左結合の場合は、<<left-join>>というステレオタイプをつけておきます。ここで、コンポジションに当たる販売管理に対して、商品が左結合するということになります。そして、属性のところには存在するフィールド名を記述します。主キーを明示したい場合は、ステレオタイプの<<PK>>などを記述しても良いでしょう。そして、最後の「金額」フィールドは計算フィールドです。計算フィールドの名前の前に / が付けられていますが、派生と呼ばれる記号で、他のフィールドの値から導出可能であることを示しています。もちろん、この場合はさらに同一レコードの値から導出可能であるので、派生の記号のまさに使い所です。式はどうするか、ここではメモに記述していますが、左側のビューのように、制約として記述する方法もあります。長くなると、メモの方がみやすくなるかもしれません。最も、ここに式をあまりに細々と記述してみづらくなるのであれば、式はいっそのこと省略して、CREATE VIEWを参照するということでも良いでしょう。

「納品書_合計付」ビューは納品書の１レコードが、ビューの１レコードになることを期待しているので、納品書テーブルからの線はコンポジションとなります。そして、顧客名などを取り出したいので、顧客テーブルを左結合します。しかしながら、ここで、明細の合計を取りたいのですが、「販売情報_商品付」ビューから値を取ってきて合計を取ります。まさに、「販売情報_商品付」ビューの結果を集約するので、白いダイアマークのアグリゲーションが適切です。ただ、この時、どのフィールドでグループ化するのかもステレオタイプ<<group-by>>で示しておきます。そして、合計金額の派生フィールドが、SUM関数を使った式を持っているので、そこに関連した明細の「金額」フィールドの合計の値が求められることになります。

以上のビューを実際のSQL文で記述するとこんな感じです。なお、このままのSQL文は通らないかもしれません。アグリゲーションがある場合、アグリゲーションに関係ないフィールドをSELECT句に入れてはいけないというルールで運用されている場合が最近は増えています。

CREATE VIEW 販売情報_商品付 AS
SELECT 販売明細ID, 納品書ID, 商品ID, 商品名, 単価, 個数,
  単価 * 個数 AS 個数
FROM 販売明細
  LEFT JOIN 商品 ON 販売明細.商品ID = 商品.商品ID

CREATE VIEW 納品書_合計付 AS
SELECT 納品書ID, 顧客ID, 顧客名, 販売日,
  SUM(金額) AS 合計金額,
  SUM(金額) * 0.1 AS 消費税額,
  SUM(金額) * 1.1 AS 総計
FROM 納品書
  LEFT JOIN 顧客 ON 納品書.顧客ID = 顧客.顧客ID
  INNER JOIN 販売情報_商品付 ON 納品書.納品書ID = 販売情報_商品付.納品書ID
GROUP-BY 納品書.納品書ID

ビューとビューの間の線が、コンポジションでもアグリゲーションでもない場合は、どちらが元になっているのか分かりにくいので、この例では記述がありませんが、もとになっている側に矢印の矢尻、つまり、導出可能の記号を付けておくのが良いでしょう。最も、矢尻はコンポジションやアグリゲーションの記号の反対側なので、常につけるでもいいような気がします。

図に、テーブル間結合の式は必要かというと、その式は、大まかにはすでにテーブル間結合の図で記述されています。どのテーブルを持ってくるのかということから、関連のための条件式はほぼ決まります。最も、リレーションシップを検索のように使う場合もあるので、そのようなテーブル間の関係性として定義していないリレーションシップでの結合を行う場合は、結合の線に対してメモで条件を記述すると良いでしょう。

実際案件で、ビューも一緒に記述してみました。やはりというか、線だらけになってしまいました。なので、ビューへの結合線は薄い色にするなど、本来のテーブル間の関連付けの線を目立つようにしています。ですが、ほとんど同じビューが２つあって（これは意図的なんですが）、そういった定義でちょっとだけ違うのがどこなのかは一目瞭然になります。ただ、やはりというか、１つ１つのビューの用途については、箱の存在だけではピンとは来ません。そこで、メモでビューの用途や意図を記述をする必要性はあります。最も、これはテーブルについても同様かもしれません。

クラス図にビューを記述することは、前回紹介したAmbler先生のページにもありますが、今回紹介した記述方法はいくつかルールを追加しています。ビューも設計のうちですし、管理したいということもあるかと思いますが、テーブルの設計と併せて記述するのはあまり支持してもらえないかもしれません。みづらくなるだけだという感想を持たれるかもしれません。一方で、離れていた開発から戻った時や、メンテナンス開発の時などは状況の把握には有効でしょう。結局、その案件の中では、テーブルとビューを両方記述しましたが、両方記述した図と、テーブルだけを記述した図の2通りの出力を出すことにしました。

UMLのクラス図は、ER図とよく似ています。ER図ではテーブルを１つのボックスで記述しますが、テーブルをクラスであるとみなせば、UMLのクラス図でのクラスでの記述も可能です。クラス図の方が汎用的であるので、いろいろな書きようがあるため、クラス図で記述する場合のルールは自分自身やあるいはチームとして決めておく必要があります。クラス図での書き方の提案としては、早い時期からあるものとしては、書籍の「
Refactoring Databases: Evolutionary Database Design」の筆者の一人による「A UML Profile for Data Modeling (Scott W. Ambler)」があります。かなり広範囲にわたってルールは作られているのですが、逆に言えば、どこまでを自分で利用するときに記述として取り入れるかを取捨選択するガイドになると思われます。

ところで、astha*professionalは、クラス図とER図を相互に変換することができます。まず、その機能を見てみましょう。前回に示したER図からクラス図を生成すると次のようになりました。

まず、テーブル名がクラス名になります。そして、フィールド定義が、クラスの属性になります。クラスの中の操作については、全部空欄になります。ER図ではクラスの操作に対応する情報はないので、当然かと思いますが、見栄えの上からは操作区画は非表示にするのが良いでしょう。属性の型については非表示になっていますが、定義した結果は背後で記録しています。ただ、図の上で型は見えてなくてもいいのであれば、全部intのままでもいいかもしれません。

そして、属性の中に、<<PK>> というステレオタイプが設定されたものがあります。もちろん、これが主キーを示します。ただ、これだと外部キーの情報が欠けるので、その対処は後で説明しましょう。テーブル間の関連は線で引かれていますが、非依存の関連は<<Non-Indentifying>>、依存は<<Indentifying>>というステレオタイプがついています。そして、非依存は単なる線であって一般的な関連、依存は黒いダイアがあってコンポジションとなっています。依存と非依存の区別が２重な感じがありますが、明確にしたかったのでしょう。納品書の構成要素として販売明細の集合は必須であるということから、確かに、依存関係があれば、コンポジションで表現することは妥当です。

そして、カラスの足跡は残念ながらクラス図では記述ルールにないので、それぞれ、線の端に、「1」や「0..*」などで、個数を示します。つまり、「顧客」と「納品書」の関係は、1対多であり、1つの顧客のレコードに対して、納品書の数は0〜複数個を取り得るということを示しています。この記述は「多重度」と呼ばれます。ここで、線の横に「-顧客」と記載されたものは、「納品書クラスで、顧客を参照できる」ということを意味しています。この外に出た「顧客」がクラス図の定義からは属性の1つになるので（astha*では属性とは別のプロパティになる）ボックスの中に、外部キーが登場しないという状況になります。これはER図上で明示されていたものが消えてしまうというようにしか見えないですし、結合処理の時になんというフィールドを照合するのかという具体性がやや欠けています。もちろん、主キー名と外部キー名を同じにするというルールがあれば、納品書テーブルに顧客IDフィールドがあるのだろうなというのはわかりますが、それは記述をした方が良いのではないかと考えます。

そこで、元々あった外部キーのフィールドも、属性としてボックスの中に定義して、<<FK>>というステレオタイプを設定することで、ボックス内に定義したフィールドが見えることになります。依存と非依存の情報は一旦ここでは消してありますが、黒いダイアのマークのコンポジションは、テーブルのライフサイクルを考える上では重要な情報になるので、必要な箇所にはつけておくべきです。ただ、関連の線の上に<<Indentifying>>というステレオタイプを記述するのは、図がちょっとうるさい感じになるので書かなくてもいいのではと思います。以下のように、クラス図を変更してみました。

ここで、「顧客」と「納品書」の間の線を見てください。関連付けの線を引いた場合、「0」や「0..*」などの多重度を間違いなく記述するのは必須です。この情報が欠けると設計情報としては未完成と判断されます。ここでは納品書と販売明細の関係において1..*すなわち、販売明細がないということはないという設計情報があります。多くの場合0..*ではあってもなくてもいいということですが、1..*だとそちら側に対応するレコードが必ず1個以上あるようにしないといけません。つまり、納品書のレコードを作ったときに、販売明細のレコードは少なくとも１つは作っておく必要があるということを設計上示唆しているとも言えますし、明細のない納品書はあり得ないということも示唆しています。

そして、照合するフィールドを、線の横に「-顧客ID」などと記述をします。この時、納品書側にある「顧客ID」は、顧客テーブルに定義した主キーの顧客IDと同じものを意味します。実は、ここで、同じものを2つ定義しているのですが、ここからテーブル定義のSQLを自動生成するのでなければ問題はないかと思われます。クラス図では、照合するフィールドを、線の横に書くのですが、「相手側に書く」のがルールです。ちょっとわかりにくいというか、最初は慣れないかと思いますが、ルールだとして気をつけていればすぐに慣れます。なお、このように、双方とも「顧客ID」フィールドであるような場合は、例えば、納品書と販売明細のように、一方だけ書いておくことでも判別はできます。あるいは、PKとFKのステレオタイプから、どのフィールドで照合しているかは明確であるとも言えるので、線の横に照合するフィールド名を記述することを省略するのもあり得るでしょう。その辺りは、好みや設計の複雑さなどと折り合いをつけて自分でルールを決めてしまっていいでしょう。

なお、複雑なリレーションシップがあるような場合にどう記述するのかと言えば、線の隣のフィールド記述だけではわからないので、自由記述が可能なメモを書いて、線の部分を参照しておくと良いでしょう。クラス図にそういう機能がないのかといえば、実はあります。関連の線に対して「制約」という記述が書けます。原則はOCLという言語による式を書くのですが、作図だけならコンパイルするわけではないので、その意味では自由に記述できます。その記述結果は、線の上に { } で囲われて表示されます。もちろんこの方法でも良いのですが、メモで書く方が手軽ということもあって、追加情報はメモをともかく活用するというのがポイントになります。

キーフィールドについては、PKとFKだけを書いていますが、ここでサロゲートキー、つまり連番の自動入力を使うなどして入力したデータとは直接関係ない主キーフィールドを使う場合も考慮します。サロゲートキーは、参照制約などの説明をしてから改めて議論しますが、この例だと例えば、販売明細の販売明細IDは、サロゲートキーで、本来のモデル上の主キーあるいは候補キーとしては {納品書ID, 商品ID, 個数} であったとします。この時、納品書IDと商品IDは、<<FK>>だけでなく、<<CK>>というステレオタイプを付与し、個数にも<<CK>>をステレオタイプに追加します。ステレオタイプは複数設定でき、属性名の前に併記されます。この記述があれば、モデル上の主キーは<<CK>>がついたものだけど、サロゲートキーによる主キーが<<PK>>で示されているということがわかります。もっとも、Ambler先生のサイトでは、サロゲートキーについては<<sarrogate>>というステレオタイプをつけるということを提唱していますので、明示したい場合にはこのステレオタイプをつけておくと良いでしょう。

クラス図で記述した場合のメリットは、次の図のようなオブジェクト図を記述できることです。クラス図は抽象度が高い図であり、コンパクトに設計情報はまとまっていますが、一方で具体的にきちんと正しいデータが格納されるのかということは頭の中で追っていくことになります。ここで例で出しているような納品書などの例ではそれもそんなに難しくはありませんが、もっと複雑なデータ構造の場合は、データがどのように格納され、変化するのかを具体的に記述してみて、確認する必要が出てきます。

オブジェクト図は、クラスに定義した結果をもとに１つのボックスを１つのオブジェクトとして記述するものです。データベースの場合、１つのボックスが１つのレコードとなります。もちろん、今までに主張してきたように「表にする」でもいいのですが、表だとレコード間の関連付けをビジュアル化しづらいこともあります。レコード間の関連を明示するのはオブジェクト図の方が適しています。一方、オブジェクト図は場所を取ります。ある程度まとまった数のレコードを作っていくとかなり大きな図になり、それはそれでみづらいです。ですが、例えば、ある種のワークフローがスタートして、途中、いろいろとデータが変わって、最後に終わるというところまでを、具体的なデータを当てはめて検証するということができるので、設計の妥当性を確認するツールとしては役に立つものです。

次回は、ビューをモデル化するのかという議論と、SQLのテーブル定義やビュー定義と設計の関係を説明しましょう。

今までは、「ともかく表にしよう」ということで、表を基礎にして、データがどのように振る舞うかを見てきました。表は非常に便利で、まず、データを掲示できる点で、具体性があります。さらに、どんなフィールドがあるかということも記述できるので設計情報も記録できます。完全ではないとしても、これで十分と思ってしまうかもしれません。しかしながら、実際の開発で使うような表（正確には「テーブル」です）にあるフィールドは非常に大量にあります。最近は大画面ディスプレイが一般的なので、少々フィールド数が多くてもそれなりに一覧性はありますが、やはり横スクロールが多くなると見落としがちですし、当然ながら俯瞰して見ることはできません。また、実データを集める必要はあるものの、たくさんのデータを集めると、それはそれで縦スクロールが必要になりやはり俯瞰性は落ちるということになります。

システムの開発に携わる人たちは、もっとコンパクトに、どんなテーブルが用意されているのかということを知りたいわけです。そのために、設計結果をモデルとして記述することになります。代表的な図はERですが、その前に、今までにどういう設計情報が出てきたでしょうか？ まとめると次のようになります。

• 表を特定するための表の名前
• どんな名前のフィールドがあるのか
• どのフィールドが主キーなのか
• どのフィールドが外部キーなのか
• 表同士の関連性はあるのかないのか
• あるとしたら、どのフィールドを照合するのか
• １対多の関係はどこにあるのか

こうした情報をコンパクトに示せれば、モデルとして有益になります。例えば、架空の会社の例をER図に記載するとしたら、次のようになります。なお、ER図にもいろいろなルールがありますが、ここではIE（Information Engineering）記法と言われるものを紹介します。

ボックス１つ１つが表、すなわちテーブルになります。ボックスの上に記載されたのがテーブル名となります。そして、ボックス内部は線で区切られた上部と下部に分かれていて、上部には主キー、下部には残りのキーを記載します。外部キーとなるものはフィールド名の右に(FK)と書かれています。つまり、箱にフィールド名を記載することで、表の骨格を示すことができています。もちろん、実データはありませんが、このER図を読み解く時には、ボックスを表としてみなして、記載されていませんけど、いろんなデータがそこにずらずらとレコードとして存在するということを想像するのは基本です。なお、データベースではフィールドの型を指定する必要がある場合が一般的なのですが、記載可能なER図もあるものの、コンパクトに表現したい場合は省略されている方が都合が良いので、図の中には記載されない場合が多いでしょう。

そして、テーブル間に線が引かれていて、独特のデザインになっています。まず、線があるということは「関連性がある」ということを意味しています。もう少し詰めた言い方をすれば、「直接的な関連性がある」ということです。ともかく、ある表の内容と、別の表の内容が関連している場合には、線を引きます。販売明細と商品が関連していますが、販売明細側ではどの商品への明細なのかを記録するためには商品IDフィールドの値だけを覚えています。ここで、商品名や、図にはありませんが、単価など商品にまつわるざまざまな情報を明細で必要になった場合、販売明細のテーブルと、商品のテーブルを合成、すなわち結合をして、この場合は販売明細に対して商品の情報を商品テーブルから供給するような動作ができたことを思い出してください。要するに「商品マスター」なのですが、いずれにしても、商品名などの実データは、販売明細には存在せず、商品テーブルから取ってくることができるということを示しているのが関連です。

関連の線の一方は単なる線ですが、全てがもう一方の端は3つに分かれています。この形から「カラスの足跡」のような言い方がされるのですが、これは、１対多の関係であることを示しています。この表記が、非常に分かりやすく直感的であることが、ER図を利用する理由の１つであります。いろいろな表を作った時、どのテーブルと、どのテーブルが関連性があり、１対多の関係がどうなっているのか（つまり、どっちが多なのか）ということが明確にわかるからです。関連の線にはそれ以外に、線に垂直の短い棒と、◯があります。この短い棒は数字の１を示していて、◯は0を意味しています。ですが、1と0と読むのではなく、◯は対応するレコードとして、0個〜複数個あり得るということを記載しています。また、1個以上や、0ないしは1個のような書き方もできる場合があり、個数自体をきちんと記述しようとする仕組みも持っています。例えば、関連性の双方が、1対1以上のような表記の場合、多の側に対応するレコードがないということはあり得ないということを設計上想定している可能性が高く、データベースの設計というよりもプログラム等の実装においてそのことを考慮して、開発を進めないといけない場合も出てきます。このことに限らず、データベースの設計は単にデータモデルということだけでなく、システム全体がどのように動くのかを期待する場面も含めて記述しているとも言えます。

そして、ER図を見ながら、実際に納品書を作って、製造部門が製造をして…といったデータの発生や変更などを頭の中に巡らせるのがまずはこの図を読み解く基本です。その時、あるデータを別のテーブルに保存するとしたら、おそらく、１対多、つまり、あるデータに対して、複数の別のテーブルのレコードが関連して、そのためには、外部キーのこのフィールドに、この値を入れて…ということが想像できるということが期待されています。そして、忘れている１対多の関係がないかとか、ここは１対多ではなく、多対多ではないかなど、テーブル間の関連性の検証を一生懸命行います。結果的に各テーブルでの１レコードが何に相当するのかということの把握も行うことになり、１レコードという素なデータの存在意味を加味した上で、関連付けによって現実の世界の表現に至っているかという点をこの図を見ながら検証するのです。

あと、関連性の線に点線と実践があります。前者は非依存、後者は依存と呼ばれています。関連が依存の場合、依存性がある方のテーブルは、角丸のデザインで表記されます。点線で記述する非依存の関連は、関連のないレコードがあっても良いということを意味しています。一方、依存するとは、関連性のないレコードは存在しないということを意味しています。関連づけは、主キーと外部キーで行なっています。つまり、納品書テーブルの納品書IDと、販売明細テーブルの納品書IDで関連づけを行なっています。これらが同一のデータが、納品書とおそらく複数個存在しそうな明細のレコードの関連づけの手がかりになっています。ここで、単に関連づけを行うだけなら、外部キーであれば良く、販売明細テーブルの納品書IDのフィールドがNULLであれば、対応する納品書はないレコードになります。多分、そういうレコードは意味がない、あるいはない方が望ましいということを考えれば、依存があるようにしておくことで、そういうレコードの作成時にエラーが出て作成されなくなります。つまり、データベース自身が設計に反する状態にならないように排除してくれているのです。外部キーを主キーに入れることによって、その値がNULLでないことが期待されます。そして、通常は対応する主キーに存在する値、つまり外部キーに入力して関連づけが可能な値を、外部キーに入れることになるでしょう。その結果、依存関係が確実になりということで、設計時に依存か非依存かをきちんと把握するという意味で、ER図は設計を記述できるということになります。ただ、この依存性と外部キーが主キーになってしまう点については異論がある方もいらっしゃるかと思いますが、ER図ではともかくそういうルールで記述されます。外部キーが主キーになるのを嫌う場合には、全ての関連を非依存で記述して、自分でキーフィールドの定義などを管理すれば良いでしょう。もちろん、依存がある場合にはそれなりの対処はされると思います。

こうしてER図を書けば、実はデータベースに理解可能なテーブル定義のためのSQL文を作成可能です。言い換えれば、テーブル作成のSQL文を、図として表現したのがER図でもあると言えるでしょう。ツールでSQL文を生成できるのは一般的になっています。もちろん、特定のデータベースソフト向けのオプションなどは自分で手で直すことになりますが、ともかくフィールドを書き並べるといった単純作業を一気にやってくれる機能と思えば便利です。また、論理設計と物理設計という２面的な機能があって、画面では日本語などの日常会話で使っている単語で「商品名」などと示し、実際のデータベース上では「prod_name」みたいな変数名のようなキーワードを使うという仕組みも持っているのが一般的です。

ということで、ER図を書きましょうということになるかというと、不便な場面もあります。まず、実際の開発で出てくる表にはフィールドが大量にあって、そのままでは縦に長い表が作られてしまいます。最も、私が使っているastha*professionalだと、フィールドのエリアを非表示にできるので、テーブル名と主キーだけを表示した図にしておくことも可能です。仕様書に記載する時にはフィールド名と主キーだけを表示したボックスにして、フィールドの定義は表にするくらいがちょうど見やすいです。やはり関連図でもあるERは一覧性が欲しいです。全フィールドを収めるとかなりでかい紙に印刷しないといけなくなりますが、フィールドを主キーだけにすると、比較的巨大な設計でも、A4１枚に収まってくれるかもしれません。よく、仕様書にフィールドの表があっても無意味という話もありますが、ER図でフィールドを省略したら、やはりあったほうが良く、フィールドの存在意図や使用方法についての注釈など、ワープロの表に記載する方がよほど自由に記載できて便利です。そういう仕様書の書き方をすれば、フィールドの表には意味があります。

ER図を否定する訳ではありませんが、次回はクラス図を使ってモデル記述する方法について紹介してみたいと思います。

この話はもう少し前にしたほうがよかったですね。何かと話題のNULLについてです。

まず、NULLあるいはそれに近い概念はプログラミング言語で扱われていて、それはそれで必要だから実装されていることもあって是非の議論はされていませんが、データベースでは是非論は検索すればいろいろ出てくるかと思います。

ですが、そのコアな議論の前に、日本での発音についてまずは指摘したいです。Wikipediaによると、「ヌル」あるいは「ナル」と呼ばれているとされていますが、実際にはヌルと呼ぶ人が多いです。アメリカ人の発音を聞くと、「ナラ」って感じで、ラは正確にはLの発音だけでからナのAが影響してラとも聞こえます。ただし、日本語的に妥協するとラよりルですね。nu*は、nutsとか、numberとか、大体ナではないかと思います。なのに、日本人だけローマ字読みしてヌと言ってしまっている。もちろん、日本人は正確には発音できないからなんでもいいということも言えなくもないのですが、ヌルはちょっと遠すぎませんか、せめてナルかナラあたりで発音しておくのがいいかと思います。

データベースでは、フィールドを定義するとき、型を指定するのが一般的ですが、どんな型であっても、その型にはまった値以外にNULLという値を設定できるようになっています。また、一般には初期値はNULLになっています。ここで、NULLという値は取らないという設定、そして実際にNULLを入力しようとするとエラーを出して入力できないようにするという機能もあり、設計者が自由にNULLに対する挙動はセットアップできるのが今時のデータベースです。ただ、メンテナンス開発などでは、「ここはNULLにならないようにした！」と思っても、データベースの設計変更はしてはならないという掟があって、そのままの状態でデータベースを使って改変しないといけないかもしれあません。

NULLは論理式の中に存在すると、普通のデータとは挙動が異なります。式の中に１つでもNULLがあると、結果はNULLになると考えれば良いでしょう。NULL自体は論理値として判定するとFALSEになります。すると、FALSE = NULL という論理式の結果はNULLが入っているので自動的にNULLになります。右辺はNULLだからFALSEと思ってしまうと、FALSE = FALSEの結果がFALSEになってしまって、ちょっとおかしいなということになります。簡単な式なので、まあ普通はわかるだろうと思いがちですが、複雑な式の一部だとなかなかわかりにくいです。このように、式の判定にNULLが絡むと思った通りの結果にならないということもあって、ある値がNULLかどうか、NULLでないかを判定するための演算子があります。「field IS NULL」「field IS NOT NULL」のように記述します。右端のNULLが値のように見えますが、正確にはIS NULLあるいはIS NOT NULLという演算子ということになっています。理想的にはNULLかどうかの判定はこれらの演算子で行い、NULLでない値だけで論理式を組み立てているくらいの対応が望まれます。別の人が作った論理式を理解しないといけないような場合で、NULLを加味しないといけない場合と、しなくていい場合を考えれば、後者の方がよほど楽で把握にミスが入りづらいと言えます。

そういうことで、まず、重要なことは、データベースシステムに関わる人は、NULLという特別な値になる可能性があるということを知っておく必要があります。フィールドの定義を見て、NULLにならない設定をしているフィールドとしていないフィールドを把握しましょう。文字列でも、数値でもそうなりますが、論理値でもNULLがあります。その結果、論理値なのに、TRUE/FALSE/NULLという３通りの値を取るということになってしまって、後で説明しますが、それも混乱しやすいところです。ちなみに、NULLは基本的に値がないことを示すものですが、「値がない」をNULLとする方法と、「値がないことを示す値」を使うを使う方法があり、後者だとNULLにならなくても良いとも言えます。ディレクトリサービスの世界では、値に対するNULLという概念がなく、値がないことを示すために非常に古い時代に「99」を使った経緯があります。システムによって「値がない」ことを示す数値や文字列は違いますが、どんな値を選択しても、その値がデータだったら…という危険性は常に付き纏います。そこで、NULLを使わないで値の有無を管理したい場合は、データのフィールドと、そのデータの有無を判定する論理値のフィールドの、２つのフィールドを使うのが正しい設計になります。そうなると、値の編集では２つのフィールドを処理しないといけなくなり、複雑さは増します。

データベースとのやりとりをプログラミング言語等で実装する場合は、フィールドの値がNULLだった場合の挙動がどうなるかをしっかり確認します。もちろん、それによって表示を切り替える必要があるかもしれません。NULLかどうかの判定は、どの言語もいろいろ事情があるので、安易な条件判定で失敗しがちです。ともかく、NULLに対峙するには、言語上でのNULLの扱いについて、まずはしっかり確認しましょう。文字列データは、比較的安直でいいかもしれません。画面上に「NULL」とか「null」と出てしまうのは言語道断としても（と言っても、時々見ますね）、NULLだと””にするあたりで大体解決するかもしれません。要するに、NULLと””を同じだと考えるということをシステム全体に渡って進めてしまうと、NULLを許可してもそんなに混乱はないと思われます。数値だと、NULLだと0でもいいのかもしれませんが、未入力であることを示すために意図的にNULLを使うということもあるでしょう。そうなると、やはりNULLであれば””と見るというような対処は必要です。いずれにしても、未入力という状態を扱うのかどうかということが、きちんと仕様に記載されていることが期待されます。一方、書き込みの場合がいろいろややこしいです。文字列や数値が確定していればそれを書き込めばいいのですが、逆にどういう場合にNULLにしないといけないのかということがきちんと仕様になっている必要があります。この辺り、きちんと仕様が記述されている必要があることながら、実際の開発ではかなり大雑把に進むことが多いという印象があります。おそらく多くの方は「当たり前」と判断しているのでしょうけど、解釈に若干の揺らぎがあるような領域では、当たり前では通じない可能性もあります。ですので、その「当たり前」を明文化するのが仕様書であることは言うまでもありません。いずれにしても、実データ以外にNULLを書き込む必要がある場合には、そのための処理は記述が必要になります。

概して、NULLが不要なら、NULLの保存ができないようにフィールド定義で指定するのが良いと言えます。例えば、あるフィールドはチェックボックスの処理と連動しているとしたら、論理値にしてNULLは許可しないようにします。そうしないと、TRUEはオンだけど、FALSEとNULLはオフのような解釈になって、何のために論理値を型として選んでいるのか意味がなくなってしまいます。ただ、このオンがTUREだけなものはまだ良くて、文字列フィールドをチェックボックスに割り当ててしまい、ある画面ではオン/オフが1/0、別の画面ではYES/NULLのようなことになってしまうこともあり得ます。もちろん、バグなのですが、メンテナンス開発ではそれを直せないとしたら、この状態で、機能を組み込まないといけないかもしれません。そうなると、オンかオフかの単純な判定であっても、「field = 0 OR field IS NULL」のような複合条件になってしまいます。それに、そんな不一致があるシステムは２通りで済んでいるかどうかは怪しく、さらに調査して、判定式の変更も必要になるかもしれません。結果的に論理値で済むフィールドは、最初からNULLを禁止した論理値のフィールドとして定義しておくことで、メンテナンス性も高まるはずです。

照合するフィールドについて、外部キーに相当する方のフィールドについても、NULLの挙動を検討する必要があります。外部キーフィールドに「必ず対応する主キーのフィールドの値がなくてはいけない」という制限を付けることもでき、その場合はテーブル間の関連付けが常に何かのレコードに対して設定されていることを期待しているものです。その場合は、NULLでなく、加えて主キーの値のどれかが入っているということになります。一方、外部キーにNULLを許すと、対応するレコードがまだ未確定であるということを示すことができます。もちろん、未確定なのが困るということもあるかもしれませんが、ワークフロー上未確定になってしまう場合もあるので、一概に「必ず値を入れなければならない」ということでは済まされません。未確定で一定の期間過ぎてから、何か対応する値が設定されるというようなワークフローであれば、外部キーはNULLであっても良いというか、NULLの特徴をうまく利用できているとも言えます。

ここからはFileMakerの話です。FileMakerにはNULLはありません。その一方で、事実上””をNULLと見做すような実装がなされています。しかしながら、””は空文字列というデータである場合もあります。””は検索や、>等での比較はできますが、唯一、リレーションシップでの=の処理において、”” = “” の結果はfalseになります。つまり、照合データとしては””は使えないということです。何か、数値なり文字列なりが入ったフィールド同士でないと照合できません。言い換えれば、照合されないようにするには””を利用するという意味で、この場面だけNULL相当のデータが存在しています。

NULLの話は尽きないですが、とりあえずこの辺りで今日は終わりましょう。

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

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