HSQLDBを使用したデータベース入門その１２　主キーと正規化

>>この記事には書き直した新しいページがあります。<<

<<前　 [TOP]

リレーショナル・データベースでデータベースを作成するということは、テーブルを作成することです。
どのような種類のテーブルを作って、そこにどの様な情報を持たせるかを決めることを、「データベースの論理設計」と呼びます。
データベースの論理設計は、テーブルの設計によって大きく変わって来ます。

リレーショナル・データベースのテーブルとして好ましくない性質を持つテーブルを、そのテーブルが持っている情報を変えずに他の適当なテーブルに変えることを「テーブルを正規化する」と言います。

テーブルは、重複した行がないように設計します。
重複した行がないということは、１行１行がそれぞれ違っているということです。

テーブルから、特定の１行を取り出すとしましょう。
重複した行がないので、すべての項目を指定すれば、ある１行を取り出せます。
また、すべての項目を指定しなくても、いくつかの項目を指定するだけで、特定の行を取り出せることもあります。
そうした項目の組を、「主キー候補キー」あるいは「候補キー」と呼びます。

「主キー候補キー」のうち重要な項目で、nullという値をとらないものを「主キー」と呼びます。
nullというのは、項目にデータが入っていない状態のことです。
また、「主キー」には、内容の重複はありえません。
具体的な例を見てみましょう。

【例１：テーブル books に含まれる項目】

項目名	意味
id	登録番号
title	タイトル
author	著者
publisher	出版社

テーブル「books」に含まれる項目では、「登録番号(id)」が１冊ずつ違った番号になるなら、「登録番号(id)」が主キーとなります。
もし主キーが「タイトル」では、「たまたま同じタイトルである本」というのが障害になってしまいます。
また、「タイトル」と「著者」と「出版社」の組み合わせを主キーとしても、同じ本が何冊か登録されることが起こりえます。
ですから、この場合は「登録番号(id)」を主キーとするのが正しいのです。

決められたルール通りにテーブル設計することを「正規化」と言います。
正規化によって、適切に分割され、保守しやすいテーブルを作成できます。
正規化されたテーブルを「正規形」と言います。
正規化にはさまざまな段階があります。
通常は第三正規形くらいまで正規化することで、問題はないはずです。

第一正規形
第二正規形
第三正規形
Boyce-Codd正規形
第四正規形
第五正規形
ドメインキー正規形

テーブルの項目には、複数の値を入れることはできません。
例えば「books」のテーブルに、複数の「キーワード」を付け加えるとしましょう。

【例２：第一正規化されていないテーブル】

id	title	author	publisher	keyword
1001	国語の本その１	国語一郎	国語出版	国語
1012	算数の本その２	算数次郎	算数出版	算数
1012	算数の本その２	算数次郎	算数出版	その２
1024	社会の本その４	社会四郎	社会出版	社会
1024	社会の本その４	社会四郎	社会出版	その４

このテーブルでは１つの項目に複数の値が入っています。
こうしたテーブルは、次の例のように書き直して、複数の値が入らないようにしなければいけません。

【例３：第一正規化されたテーブル】

id	title	author	publisher	keyword
1001	国語の本その１	国語一郎	国語出版	国語
1012	算数の本その２	算数次郎	算数出版	算数
1012	算数の本その２	算数次郎	算数出版	その２
1024	社会の本その４	社会四郎	社会出版	社会
1024	社会の本その４	社会四郎	社会出版	その４

書き直した結果、複数の値が入らなくなりました。
このように変更されたテーブルを、第一正規形と呼びます。
第一正規形では、どの項目にも、複数の値が入っていません。

第一正規化されたテーブルの主キーは「id」と「keyword」になります。
このとき、「タイトル」「著者」「出版社」といった項目の内容は、主キーの一つである「id」という項目があれば自動的に決まります。
もう一つの主キーである「keyword」はなくてもよいのです。
このことを「タイトル」「著者」「出版社」という項目が「id」に関数従属しているといいます。

主キーが複数あるとき、主キーの一部にだけ関数従属している項目を別のテーブルに移すことを「第二正規化」と言います。
この例では「タイトル」「著者」「出版社」という項目が「id」に関数従属しています。
そこで「id」「タイトル」「著者」「出版社」からなるテーブルを別に作成するのです。

第二正規化されたテーブルは[booksテーブル]と[booksのキーワードを表すテーブル]になります。

【例４：第二正規化されたテーブル】

id	title	author	publisher
1001	国語の本その１	国語一郎	国語出版
1012	算数の本その２	算数次郎	算数出版
1024	社会の本その４	社会四郎	社会出版

id	keyword
1001	国語
1012	算数
1012	その２
1024	社会
1024	その４

第二正規化された[booksテーブル]では、idが主キーになります。
このidは[booksのキーワードを表すテーブル]でも登場します。
「1024」というidを持つ図書は、「社会」というキーワードを持っていることがわかります。
[booksテーブル]と[booksのキーワードを表すテーブル]は、idという項目によって関連付けられているのです。
では、[booksテーブル]と[booksのキーワードを表すテーブル]の関係について考えてみましょう。

例えば、[booksテーブル]に「英語」という本のデータがないときに、[booksのキーワードを表すテーブル]に「英語」のキーワードがあったら変ですね。
[booksテーブル]にデータがないのに、その本の「キーワード」のデータがあるということはありえないのです。
逆に言うと、「キーワード」にデータを登録するときには、登録するデータのidは[booksテーブル]で既に登録されていなければなりません。
こうしたことを「参照整合性制約」と呼びます。
また、この例での[booksのキーワードを表すテーブル]のid項目のことを「外部キー」と呼びます。
参照整合性制約が設定されていると、次のような制約があります。

[booksテーブル]に「1045」という主キーを持つ行がないとき、[booksのキーワードを表すテーブル]に「1045」の外部キーを持つ行を追加できない。
[booksのキーワードを表すテーブル]に「1045」の外部キーを持つ行があるときに、[booksテーブル]から「1045」という主キーを持つ行を削除できない。

次のようなテーブルがあったとします。