[DBデザイン#41] データベースに与える制約-続き

前回はNULL制約、そして一意制約について説明しました。もう1つ重要な制約は、外部キー制約です。サンプルに出てきた販売明細と商品の間では、外部キーフィールドである販売明細の商品IDフィールドには、主キーである商品の商品IDの値を入れることで、販売明細の1つのレコードにおいてどの商品を販売したのかを記録するということができました。この時、外部キーに、主キーにある値以外のものを入力できなくするのが外部キー制約です。例えば、次のようにテーブル定義の時に、FOREIGN KEYで外部キー制約を定義します。CONSTRAINTキーワードを使った書き方もありますが、省略することが多いと思われます。FREIGN KEYでの記述では、まず、商品IDフィールドが外部キー制約をかけると言うことを指定し、REFERENCES以降で、制約をかけたフィールドに入るべき値が用意されているテーブルのフィールドを記述します。ここでは1つのフィールドだけですが、複数のフィールドを指定することもできます。

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

# DROP TABLE IF EXISTS 商品;
CREATE TABLE 商品 (
  商品ID INT AUTO_INCREMENT,
  商品名 VARCHAR(50),
  単価 INT,
  PRIMARY KEY(商品ID),
);

INSERT INTO 商品(商品名, 単価) VALUES('A', 500);
INSERT INTO 商品(商品名, 単価) VALUES('B', 600);

最後のINSERTを実行すると、商品IDが、1と2のレコードが作成されます。その状態で、販売明細に新しいレコードを作る時に、商品IDに4などの商品テーブルに存在しない数値を指定すると、エラーとなって挿入処理はキャンセルされます。こうして、「参照先が存在しない」と言う状況を作らないようにガードしてくれる機能がデータベースにあります。

mysql> insert into 販売明細 (納品書ID, 商品ID) values(1,4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test_db`.`販売明細`, CONSTRAINT `販売明細_ibfk_1` FOREIGN KEY (`商品ID`) REFERENCES `商品` (`商品ID`))

ただし、ここでNULLの扱いをきちんと把握しなければなりません。外部キー制約に関してはNULLは対象外となっていて、NULLの入力は可能です。NULLはもちろん、参照先で何かのレコードにマッチするわけはなく、いわば「未参照」の状態を記録する値でもあります。もし、NULLになるのがまずいのであれば、NOT NULLをここでの販売明細の商品IDの定義行に追加しないといけません。NOT NULLするかしないか、これはシステム自体の実装や用途、ワークフローに応じて決められることになるでしょう。納品書の明細行を、商品未選択の状態で用意して、そこからポップアップメニューで選択するような感じのUIだと、NOT NULLでない方が都合が良いでしょう。もっとも、その場合でも、NOT NULLを指定しつつ、「商品未選択」と言う特殊な商品を用意しておくという手法もあり得ます。どちらにしても、データベース以外のところでの決め事があって、それに従って進めると言うことになります。

外部キー制約の定義で、ON DELETE CASCADE、あるいはON UPDATE CASCADEと言う記述を追加する場合もあります。ON DELETE CASCADEがあると、主キーの側のレコードが削除されると、それを外部キーで参照している側のレコードも削除されるという動作です。商品マスターと明細では設定することは多分ないでしょう。商品がなくなると、売上の記録も無くなってしまうからです。一般に、商品マスターは販売しなくなっても残しておくものかと思われます。しかしながら、納品書と販売明細ではどうでしょうか。販売明細の納品書IDは外部キーとして納品書テーブルを参照し、これによって1つの納品書レコードと、複数の販売明細のレコードが関連づけられています。納品書を作ったけど、完全に間違えたなどの場合、納品書そのものを破棄したいかもしれません。その時、関連付けられている販売明細のレコードはどうなるかと言うと、単に納品書のレコードを削除しただけなら、販売明細のレコードは残ります。それでも、納品書IDの値を再利用つまり、後日別の納品書に利用するようなことをしない限りは、単なる参照先のないレコードが残るだけです。適切なユーザインタフェースを作ってあれば、それが目に触れることはありませんが、集計等では無視するような作りが必要になります。と言うことで、納品書を消したときに、販売明細のレコードも消えてくれる方が妥当かつ明快であるとも言えます。

ON UPDATE CASCADEは、主キーの側の値を変更したら、参照している外部キーの値も変更されるという設定です。一般に主キーは変更しないという前提があるのですが、データベースにはこのようなキーがあり、ある意味で、主キーの変更までもサポートしています。もし、この設定がないとして、主キーの値を変更したらどんな問題があるでしょう。例えば、商品ID=2を5に変えたとします。主キーなので、この変更後の値は他のレコードに存在しない値であることは保証されています。しかしながら、このテーブルを参照している外部キーはまだ2のままです。そして、外部キーのあるテーブルの該当するフィールドの値を全て、2から5に変更する必要があります。複数のレコードにわたって変更をする必要があります。また、それを複数のテーブルに渡って行う必要があるかもしれません。しかしながら、ON UPDATE CASCADEにより、それを自動的に行なってくれます。

これなら、主キーは変えてもいいじゃないかと言う話もあるかもしれません。もちろん、システム全体にわたって、ON UPDATE CASCADEがきちんと機能するならいいのですが、懸念されることは、通常システムの実装においては、主キーの値をどこかに一時的に保持することはよくあることです。例えば、Webアプリケーションで、あるレコードの値を変更するようなページがあるとしたら、データベースからデータを読み込み、ページを構築はもちろん行います。そして、画面上のデータを変更したときにサーバーに送り込んで更新をデータベースに反映させないといけません。その時、主キーの値を手がかりに、修正したレコードを特定した上で、更新処理を行います。そうなれば、ページを表示して、修正する前での間に、主キーの値が変化してしまうと、更新処理での元レコードの特定ができない、つまり特定してもその値ではなくなってしまうので、多分存在しないレコードを更新しようとして、エラーになってしまうという問題があります。データベースの都合上、確かにON UPDATE CASCADEは便利なのですが、システム全体のことを考えると原則として、主キーは変更しないというルールは守るのが良いでしょう。そして、主キーを変えるような作業を頻繁に行わないようなワークフローとデータベース設計を行い、どうしても変えないといけないような場合は、誰も利用しない時間帯にメンテナンスとして実施するか、あるいはUI上の処理を禁止するような仕組みを組み込んでおくなど、運用上、あるいは実装上の工夫は必要になります。

ユーザインタフェース側で、あり得ない値を設定するような仕組みを排除し、可能な限り検証をしたデータをサーバに送り込んでいるので、外部キー制約は不要であるといった考え方もあります。もちろん、そのような状況においては、外部キー制約に引っかかることは全くないと言えるかもしれません。それでも、制約は設定すべきという意見の方が合理的です。まず、ユーザインタフェースにバグがあった時、そのバグによって整合性が崩れたデータが入力されてしまうよりも、謎のエラーで(できればそうならないのがいいのですが)止まってしまってデータ入力されない方が、データベースとしては好ましいと言えるからです。また、外部からのデータをCSVファイル等で入力するような場合には、結果的に制約の検証は事前に確実に行われているかどうかは怪しいもので、おかしなデータがやってくるなんて言うのはザラにある話です。その場合、データを検証するプログラムを通してから読み込むと言うこともあるかもしれませんが、外部キーのフィールドについては外部キー制約の制限をデータベースに与えておいて、読み込みながら検証する方がより効率的であるのではないでしょうか。と言うことで、参照制約をサボる理由はあまりなく、設定しておくことでのメリットの方が多いので、早い段階から設定はしておくべきです。

2回に渡って制約の話を行いましたが、これらの制約をうまく利用することで、データベース内のデータが整合が取れた状態をキープできます。もっとも、何を持って整合が取れているのかと言うことは、きちんと把握が必要になります。どうなって欲しいのかということを設計では考慮しますが、どうなって欲しくないのかと言うことにも気を配ると、いろいろな解答が得られます。

この場を利用して、小田嶋隆先生がお亡くなりになられたことに対して、お悔やみ申し上げます。先生との関わりは、新卒で入った日経BPでの原稿の発注先でした。いくつかの会社に発注していたのですが、後楽園のマンションにあるオフィスに緊張しながら初めて伺ったのが懐かしい思い出です。その後、移転、さらにご自宅での執筆になるくらいまでお仕事をお願いしていたのですが、原稿できるまで張り付くと言うことが常になっていて、明け方に奥様の運転で自宅に送っていただいたき、途中で焼き肉を奢ってもらったことも懐かしい思い出です。その後、私は原稿を発注していた別の会社に転職することになり私もライター業を主業務にしていたこともあったのですが、編集者として張り付いていた時に、ライターとしての働きぶりを、かなりリアルに見せていただいたことが自分にとっての大きな糧になっていました。先生ありがとうございます、安らかにお眠りください。(その頃に原稿をお願いしていた人たちが、もう何人も鬼籍に入っているなぁ。)

[DBデザイン#40] データベースに与える制約

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

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

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

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

このインデックスは、値の重複を許す場合と、許さない場合があり、インデックス作成時に指定をします。もちろん、前者は検索の高速化が主な用途ですが、後者は主キーに設定するために利用できます。そして、フィールドは、1つだけではなく、フィールドの組み合わせに対してもインデックスは作成できます。例えば、{商品名, 顧客名, 個数}に対して重複を許さないインデックスを作成すると、この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側とデータベース側の2重対処をしたことになります。そのことが冗長なのか、それとも確実な動作を支える実装なのかは意見が別れるところですが、設計とはそういう場合にどうするのかと言うこことを決める作業に他なりません。そのようなことを早い段階から検討できるようになりましょう。

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は「どのレコードも異なる」と判定されてしまいます。つまり、主キーフィールドの1つでもNULLがあれば、見かけは同一のレコードも作成できてしまいます。もちろん、主キーフィールドはNULLにならないと言うのが「原則」という考え方もできるのですが、ワークフロー上NULLである期間を許すとなると、落とし所は難しくなります。もちろん、現実の案件では、色々な要求に対する実現手法や実現度合いを見計らいトレードオフを行なって最終決定するしかないでしょう。

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

[DBデザイン#39] クラス図で設計を書く

UMLのクラス図は、ER図とよく似ています。ER図ではテーブルを1つのボックスで記述しますが、テーブルをクラスであるとみなせば、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>>というステレオタイプがついています。そして、非依存は単なる線であって一般的な関連、依存は黒いダイアがあってコンポジションとなっています。依存と非依存の区別が2重な感じがありますが、明確にしたかったのでしょう。納品書の構成要素として販売明細の集合は必須であるということから、確かに、依存関係があれば、コンポジションで表現することは妥当です。

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

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

ここで、「顧客」と「納品書」の間の線を見てください。関連付けの線を引いた場合、「0」や「0..*」などの多重度を間違いなく記述するのは必須です。この情報が欠けると設計情報としては未完成と判断されます。ここでは納品書と販売明細の関係において1..*すなわち、販売明細がないということはないという設計情報があります。多くの場合0..*ではあってもなくてもいいということですが、1..*だとそちら側に対応するレコードが必ず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>>というステレオタイプをつけるということを提唱していますので、明示したい場合にはこのステレオタイプをつけておくと良いでしょう。

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

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

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

[DBデザイン#38] ER図のニーズ

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

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

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

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

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

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

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

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

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

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

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

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

[DBデザイン#37] NULLについて

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

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

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

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

NULLは論理式の中に存在すると、普通のデータとは挙動が異なります。式の中に1つでも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という3通りの値を取るということになってしまって、後で説明しますが、それも混乱しやすいところです。ちなみに、NULLは基本的に値がないことを示すものですが、「値がない」をNULLとする方法と、「値がないことを示す値」を使うを使う方法があり、後者だとNULLにならなくても良いとも言えます。ディレクトリサービスの世界では、値に対するNULLという概念がなく、値がないことを示すために非常に古い時代に「99」を使った経緯があります。システムによって「値がない」ことを示す数値や文字列は違いますが、どんな値を選択しても、その値がデータだったら…という危険性は常に付き纏います。そこで、NULLを使わないで値の有無を管理したい場合は、データのフィールドと、そのデータの有無を判定する論理値のフィールドの、2つのフィールドを使うのが正しい設計になります。そうなると、値の編集では2つのフィールドを処理しないといけなくなり、複雑さは増します。

データベースとのやりとりをプログラミング言語等で実装する場合は、フィールドの値が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」のような複合条件になってしまいます。それに、そんな不一致があるシステムは2通りで済んでいるかどうかは怪しく、さらに調査して、判定式の変更も必要になるかもしれません。結果的に論理値で済むフィールドは、最初からNULLを禁止した論理値のフィールドとして定義しておくことで、メンテナンス性も高まるはずです。

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

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

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

[DBデザイン#36] 素なデータを見つける:履修登録の設計はどうする?

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

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

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

そして、たくさんの学生の履修登録が終わった後、教員の割り当てに変化が発生したとします。例えば、教員担当科目の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のものという判断ができるので、「履修登録一覧表_教員未選択」はこうしたルールを利用すれば作成が可能です。「履修登録」を直接作ってしまう方が、データの変更などの処理がやりやすくなるのであれば、もちろんワークフローの確認は必要だとしても、テーブルを分けない方法も選択肢としては有力だということです。

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

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

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

[DBデザイン#35] 素なデータを見つける:第五正規形と3つの関係

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

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

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

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

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

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

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

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

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

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

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

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

[DBデザイン#34] 素なデータを見つける:第四正規形と3つの存在

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

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

では、以下のような表「履修登録」を考えてみます。表の中身は、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」だけを書くことが多いようです。関数従属から矢印が1つ増えただけですね。この多値従属に関しても公理系が定義されているため、多値従属は数学的な意味で確実に証明可能な手がかりにもなりますが、ちょっと複雑なので、そういうものもあるというだけで留めます。

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

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

ここで、分割した表から元の表が再現できるかを、分割した表の合成をおこなってみて検討しましょう。まず、教員担当科目と学生履修科目を結合して合成してみます。手順は、左から1つレコードを取り、照合するフィールドが同じものを右側で探して、それらのフィールドをくっつけたものを結果として残す、という作業を繰り返した結果です。つまり、{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} を追加することで、3フィールドがある履修登録表に無事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つの科目しか担当しないという、現実にはあり得ない状況がデータにはあったのです。たまたま、そういう状況が何年か続いてシステムが動いていたとしても、突然、1人の教員が複数の科目を担当するという状況になったときその状況をデータとして表現できないことにもつながり、システムは破綻してしまいます。ですが、設計上のことを考えれば、主キーが{科目, 学生}であり、他に{教員} → {科目} という関数従属性があるとしたら、これはどこかで見たことがありますね。主キーに関係しないフィールドから、主キーの一部に関数従属があるということで、ボイス-コッド正規形を満たさないパターンです。つまり、{教員}はスーパーキーではないということで、{教員} → {科目} の関数従属に関する表を分離すれば良いということになります。ということで、正規形の世界を前進しているつもりがちょっと後退してしまいました。ここで行った「教員担当科目」と「教員担当学生」への分割は、第四正規形を満たしていないからというよりも、ボイス-コッド正規形を満たしていないからというのがより正しい言い方になります。

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

[DBデザイン#33] 素なデータを見つける:スーパーキーが絡むボイス-コッド正規形

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

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

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

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

利用部門と支払に関する関数従属があり、以下それぞれを(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}というレコードは、もともと存在しなければならないということになります。すなわち「事実」が既に記録されている必要があるという見方ができます。であれば、頑張ってレコードを作るということになるかというと、ここでわざわざ時間枠という例を出したのは、論理的には無限大の数のレコードが必要になるということがまずわかります。今月からむこう1年のようなルールがないと有限になりません。

また、仮に部屋=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つのレコードのうち、予約が入った後はそのうちの1つのレコードだけがあれば事は足りるのに、会議室予約に存在しない組み合わせのレコードも保持することになります。つまり、支払判定の全てのレコードが使われるわけではないということも気になります。

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

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

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

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

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

[DBデザイン#32] 素なデータを見つける:第一正規形と関数従属

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

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

この状態の時、主キーになっている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}, {販売日, 販売先顧客, 商品名}} となっているということを先に示しておきます。

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

ここで、まず、候補キーに絡んでいない、個数や輸送会社フィールドを見てみます。これらは、すでに入力されているので、1行目は何、2行目は何かということは決まっています。つまり、行を特定すれば、個数や輸送会社は特定できるので、{販売明細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, 輸送会社} → {販売日, 販売先顧客名, 商品名, 個数, 輸送会社}

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

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

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