テーブルの定義について

情報のテーブル化

では、管理したい情報をどのように実際の DB で表現していくかについて見ていきましょう。

例題として、複数メンバーの予定を管理するアプリケーション用の DB を設計してみましょう。予定としては「日時、用事」を管理することとし、各メンバー毎に「学籍番号、名前、所属、電話番号」を管理するとします。また、予定の時刻は省略可能とします

• アプリケーションの完成イメージ
  

  名前	1	2	3	4
  てん子	meeting
  けい子	meeting			１日署長
  しょう太	meeting		10:00 出初め式

• 個人情報の閲覧結果
  

  学籍番号	名前	所属	電話番号
  0001	てん子	気象衛星課	117
  0002	けい子	警察課	110
  0003	しょう太	消防課	119

リレーションの定義

まず、予定を管理するリレーションと個人情報を管理するリレーションは分割するものとします。最初に、個人情報を管理するリレーションを定義しましょう。

リレーションを定義するにあたっては、何を出力したいのかを思い描くと良いでしょう。この場合、先に示した例が作りたいリレーションのイメージです。

表現したいことが決まったので、実際のデータ型を定義してみましょう。

• 学籍番号(student_id) ... char(4)、0 を格納するために文字型、重複は存在しないとする
• 名前(name) ... 文字列
• 属性(attribute) ... 文字列
• 電話番号(phone_number) ... 文字列

  CREATE TABLE personal (
        student_id char(4),
        name text,
        attribute text,
        phone_number text ); 

とりあえずのデータ定義ではこんな感じでしょう。
次は、予定の方のテーブル定義です。予定の方は、何も考えずにテーブルを定義するとこんな感じでしょうか。

• 名前(name) ... 文字列
• 予定の開始時刻(begin_time) ... 日付・時刻
• 予定の終了時刻(end_time) ... 日付・時刻
• 予定の表示名(title) ... 文字列

  CREATE TABLE schedule (
        name text,
        begin_time timestamp,
        end_time timestamp,
        title text ); 

さて、ここで問題ですが、上記２つのテーブルを改善するとしたら、ずばりどこ？
ちょっと考えてみて下さい。

.
.
.

では、解答編です。とりあえず列挙します。

• personal.student_id を主キーとして扱う
• personal.name に NOT NULL 制約を適用する
• schedule.name は一意である保証がないので、student_id に変更する
• schedule.student_id には personal.student_id に存在するものしか使わせない
• schedule.title に NUT NULL 制約を適用する
• schedule.begin_time に NOT NULL 制約を適用する

以下、それぞれについての説明です。

---

主キー

主キーとはリレーション毎の概念で、そのリレーションのレコードを一意に指定できる要素のことです。この場合、学籍番号の重複が存在しないので、学籍番号は主キーたりえます。主キーはそのリレーション内に１つしか存在しません。また、主キーから同じレコードの他の要素を指定することもできます。例えば、「0001 の人の電話番号は 117」とか、「0001 の人の所属は 気象衛星課」といった感じです。

---

NOT NULL 制約

その項目が空ではないことを保証します。この場合、personal テーブルにおける名前や、schedule テーブルにおける予定の表示名がない、ということはそのレコード自体に意味がなくなるので、NOT NULL 制約を付けましょう。予定の日付についても、NOT NULL 制約を適用します。

---

外部キー

これは、その要素が他のテーブルに必ず存在することを保証します。今回の schedule は「誰か」の予定を表しているはずなので、その「誰か」は必ず存在することを保証します。

---

Default 制約

今回は出てきませんでしたが、レコード追加時に要素が省略された場合、その要素の初期値を指定できます。

---

宿題

以下のテーブル定義、サンプルデータが与えられたときに、以下の SQL 問い合わせを作成せよ。

• 個人情報の一覧を取得する SQL 問い合わせ
• 名前を指定してその人の予定一覧を取得する SQL 問い合わせ

• 作り直した個人情報のテーブル定義
  

    CREATE TABLE db2_personal (
          student_id char(4) PRIMARY KEY,
          name text NOT NULL,
          attribute text,
          phone_number text); 
  

• 作り直した予定情報のテーブル定義
  

    CREATE TABLE db2_schedule (
          student_id char(4),
          begin_time timestamp NOT NULL,
          end_time timestamp,
          title text NOT NULL,
          FOREIGN KEY (student_id) REFERENCES db2_personal); 
  

• サンプルデータ
  個人情報のサンプル, 予定のサンプル

• 実行結果の例
  

     name   |     begin_time      |      end_time       |  title   
  ----------+---------------------+---------------------+----------
   しょう太 | 2005-11-01 00:00:00 | 2005-11-01 00:00:00 | meeting
   しょう太 | 2005-11-03 00:00:00 | 2005-11-03 00:00:00 | 出初め式 
  

以上です。