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

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

HSQLDBを使用したデータベース入門その12 主キーと 正規化(2020年5月更新)

<<前  [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]