学生向けプログラミング入門

学生向けにプログラミングを解説。Java、C++、Ruby、PHP、データベース

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

<<前  [TOP]


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


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


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


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


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


【例1:テーブル books に含まれる項目】

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


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


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


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


【例2:第一正規化されていないテーブル】
idtitleauthorpublisherkeyword
1001国語の本その1国語一郎国語出版国語
1012算数の本その2算数次郎算数出版算数
その2
1024社会の本その4社会四郎社会出版社会
その4


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


【例3:第一正規化されたテーブル】
idtitleauthorpublisherkeyword
1001国語の本その1国語一郎国語出版国語
1012算数の本その2算数次郎算数出版算数
1012算数の本その2算数次郎算数出版その2
1024社会の本その4社会四郎社会出版社会
1024社会の本その4社会四郎社会出版その4


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


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


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


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


【例4:第二正規化されたテーブル】
idtitleauthorpublisher
1001国語の本その1国語一郎国語出版
1012算数の本その2算数次郎算数出版
1024社会の本その4社会四郎社会出版
idkeyword
1001国語
1012算数
1012その2
1024社会
1024その4


第二正規化された[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」という主キーを持つ行を削除できない。


次のようなテーブルがあったとします。
idタイトル著者出版社出版社の所在地
1045英語の本その5英語五郎英語出版東京


このテーブルでは、「出版社の所在地」が「出版社」に関数従属しています。
主キーではないキーに関数従属している項目を、別のテーブルに移すことを「第三正規化」と言います。
第三正規化されたテーブルは次のようになります。


【例5:第三正規化されたテーブル】
idタイトル著者出版社
1045英語の本その5英語五郎英語出版
出版社出版社の所在地
英語出版東京


<<前  [TOP]