開発者リファレンス : プログラミング方法の概要 : Programmer's Guide : サンプル データベース テーブルと参照整合性
  
このページをシェアする                  
サンプル データベース テーブルと参照整合性
この付録では以下の項目について説明します。
Demodata サンプル データベースの概要
Demodata サンプル データベースの構造
Demodata データベースの参照整合性の例
Demodata サンプル データベースのテーブル設計
Demodata サンプル データベースの概要
サンプル データベースの Demodata は Zen 製品の一部として提供されており、データベースの概念と技法を図解するためにマニュアルで頻繁に使用されます。Zen に関しては既によく理解されていると思いますが、この付録の情報をもう一度見直して、新しいサンプル データベースに慣れてください。
大学関連の環境で仕事をしていなくても、これらのサンプル データベースの例をテンプレートおよび参考として利用すれば、独自の情報システムの設計と開発を容易に行えます。ここに示す例は実際の生活の場面を反映しているので、この例に示すサンプル クエリなど機能を利用することができます。
Demodata サンプル データベースの構造
データベースの物理構造は、リレーショナル データベースの要素であるテーブル、列、行、キー、インデックスで構成されています。
このデータベースに含まれる 10 個のテーブル間にはさまざまな関係があります。このデータベースには、学生、教職員、授業、登録などに関するデータが含まれています。
前提条件
以下に、データベースを構築したときのいくつかの前提条件を示します。
データベースの適用範囲は 1 学期です。
学生は、同じコースを何回も受講できません。たとえば、学生は Algebra I の Sections 1 と 2 に受講登録できません。
教職員は学生でもかまいませんが、同じ授業での講義と受講登録は行えません。
どのコースも 1 つの学部だけから提供されます。
学生が評価を受けるには、学生を授業に受講登録し、その授業を教える教職員を任命しなければなりません。
教職員が所属する学部は 1 つですが、複数の学部で講義を行うことができます。
すべての学生は、アメリカの社会保険番号基準に基づいた学生 ID を持っています。
すべての教職員は、アメリカの社会保険番号基準に基づいた教職員 ID を持っています。
その他すべての職員は、アメリカの社会保険番号基準に基づいた個人 ID を持っています。
教室は、同じビル内で固有です。
2 つの授業を同じ教室で同時に教えることはできません。
教職員は、与えられた時間に 1 つの授業しか教えることができません。
授業に受講登録するための前提条件は必要ありません。
学部は専攻を意味します。
1 つの授業は、学期を通じて 1 人の教職員しか担当できません。
電話番号または郵便番号と州には相関関係がありません。
教務係は、教職員または学生であってはいけません。
ある人がデータベースに入力されると、すべての質問に答えなければならない調査か、あるいは、質問にまったく答える必要がない調査を行うことができます。
コースの履修単位時間は、必ずしも、授業が行われる時間数と同じではありません。
電子メール アドレスは、一意のアドレスである必要はありません。
エンティティの関係
エンティティは、データベース内の主なコンポーネントを記述するオブジェクトです。データベースを設計する場合、エンティティとそれらの相互の関係を定義してから先に進むことが大切です。Demodata データベースでは、CLASSES、STUDENTS、FACULTY、GRADES などがエンティティです。エンティティとそれらの相互関係について、次の図で概説しています。
図 3 エンティティの関係
GRADES は弱いエンティティです。このエンティティは授業を受ける学生に依存しているので、その存在は他のエンティティの有効性に依存しています。STUDENT テーブルと FACULTY テーブルが共通の情報を作成するのは、学生が教職員になったり、教職員が学生になる場合があるからです。共通の情報は、PERSON テーブルにあります。
Demodata データベースの参照整合性の例
このトピックでは、Demodata サンプル データベースに適用される参照整合性設計について説明します。また、Demodata サンプル データベースにこの設計を実装できる SQL スクリプトが含まれています。
次の図は、Demodata 内のテーブル間の一連の参照を示しています。ボックスはテーブルを表します。矢印は、親テーブルから参照元テーブルへの参照制約を示します。たとえば、制約 1 では、親テーブルの外部キーは Billing テーブルの主キーを参照しています。
メモ:この図は、依存関係グラフの役割も果たし、どのような順序でテーブルを作成すれば、テーブルの相互の参照が有効になるかを示します。この順序は SQL スクリプトに反映されます。
次の表は、これらの制約、およびテーブルと列の間の関係を示します。
表 42 参照整合性に関連するテーブルと列
制約
参照元テーブル
外部キー
参照先テーブル
主キー
1
BILLING
Registrar_ID
PERSON
ID
2
STUDENT
ID
PERSON
ID
3
FACULTY
ID
PERSON
ID
4
STUDENT
Tuition_ID
TUITION
ID
5
FACULTY
Building_Name、Room_Number
ROOM
Building_Name、Number
6
DEPT
Building_Name、Room_Number
ROOM
Building_Name、Number
7
CLASS
Building_Name、Room_Number
ROOM
Building_Name、Number
8
FACULTY
Dept_Name
DEPT
Name
9
DEPT
Head_Of_Dept
FACULTY
ID
10
STUDENT
Major
DEPT
Name
11
STUDENT
Minor
DEPT
Name
12
COURSE
Dept_Name
DEPT
Name
13
BILLING
Student_ID
STUDENT
ID
14
ENROLLS
Student_ID
STUDENT
ID
15
CLASS
Faculty_ID
FACULTY
ID
16
CLASS
Course_Name
COURSE
Name
17
ENROLLS
Class_ID
CLASS
ID
次のスクリプトにより、このトピックで説明されている参照が実装されます。スクリプトをコピーして ZenCC で貼り付け、Demodata に適用することができます。Demodata のコピーを作成し、そのコピーに対してスクリプトを使用することをお勧めします。
ALTER TABLE Person (ADD PRIMARY KEY (ID)); -- 既存の PersonID インデックスを使用します
ALTER TABLE Billing ADD CONSTRAINT Billing_Person FOREIGN KEY (Registrar_ID)
   REFERENCES Person ON DELETE RESTRICT; -- 新しい Billing_Person インデックスを作成します
ALTER TABLE Student ADD CONSTRAINT Student_Person FOREIGN KEY (ID)
   REFERENCES Person ON DELETE RESTRICT; -- 既存の StudentID インデックスを使用します
ALTER TABLE Faculty ADD CONSTRAINT Faculty_Person FOREIGN KEY (ID)
   REFERENCES Person ON DELETE RESTRICT; -- 既存の FacultyID インデックスを使用します
ALTER TABLE Tuition (ADD PRIMARY KEY (ID)); -- 既存の UK_ID インデックスを使用します
ALTER TABLE Student ADD CONSTRAINT Student_Tuition FOREIGN KEY (Tuition_ID)
   REFERENCES Tuition ON DELETE RESTRICT; -- 既存の TuitionID インデックスを使用します
ALTER TABLE Room (MODIFY Building_Name CHAR(25) NOT NULL); -- 主キーは NULL でない必要があります
ALTER TABLE Room (MODIFY Number UINTEGER NOT NULL); -- 主キーは NULL でない必要があります
ALTER TABLE Room (ADD PRIMARY KEY (Building_Name, Number)); -- 既存の Building_Number インデックスを使用します
ALTER TABLE Faculty ADD CONSTRAINT Faculty_Room FOREIGN KEY (Building_Name, Room_Number)
   REFERENCES Room ON DELETE RESTRICT; -- 既存の Building_Room インデックスを使用します
ALTER TABLE Dept ADD CONSTRAINT Dept_Room FOREIGN KEY (Building_Name, Room_Number)
   REFERENCES Room ON DELETE RESTRICT; -- 既存の Building_Room インデックスを使用します
ALTER TABLE Class ADD CONSTRAINT Class_Room FOREIGN KEY (Building_Name, Room_Number)
   REFERENCES Room ON DELETE RESTRICT; -- 新しい Class_Room インデックスを作成します
ALTER TABLE Dept (ADD PRIMARY KEY (Name)); -- 既存の Dept_Name インデックスを使用します
ALTER TABLE Faculty ADD CONSTRAINT Faculty_Dept FOREIGN KEY (Dept_Name)
   REFERENCES Dept ON DELETE RESTRICT; -- 既存の Dept インデックスを使用します
ALTER TABLE Student ADD CONSTRAINT Student_Dept_Major FOREIGN KEY (Major)
   REFERENCES Dept ON DELETE RESTRICT; -- 新しい Student_Dept_Major インデックスを作成します
ALTER TABLE Student ADD CONSTRAINT Student_Dept_Minor FOREIGN KEY (Minor)
   REFERENCES Dept ON DELETE RESTRICT; -- 新しい Student_Dept_Minor インデックスを作成します
ALTER TABLE Course ADD CONSTRAINT Course_Dept FOREIGN KEY (Dept_Name)
   REFERENCES Dept ON DELETE RESTRICT; -- 既存の DeptName インデックスを使用します
ALTER TABLE Faculty (ADD PRIMARY KEY (ID)); -- 既存の FacultyID インデックスを使用します
ALTER TABLE Dept ADD CONSTRAINT Dept_Faculty FOREIGN KEY (Head_Of_Dept)
   REFERENCES Faculty ON DELETE RESTRICT; -- 既存の Dept インデックスを使用します
ALTER TABLE Class ADD CONSTRAINT Class_Faculty FOREIGN KEY (Faculty_ID)
   REFERENCES Faculty ON DELETE RESTRICT; -- 新しい Class_Faculty インデックスを作成します
ALTER TABLE Student (ADD PRIMARY KEY (ID)); -- 既存の StudentID インデックスを使用します
ALTER TABLE Billing ADD CONSTRAINT Billing_Student FOREIGN KEY (Student_ID)
   REFERENCES Student ON DELETE RESTRICT; -- 新しい Billing_Student インデックスを作成します
ALTER TABLE Enrolls ADD CONSTRAINT Enrolls_Student FOREIGN KEY (Student_ID)
   REFERENCES Student ON DELETE RESTRICT; -- 既存の StudentID インデックスを使用します
ALTER TABLE Course (ADD PRIMARY KEY (Name)); -- 既存の Course_Name インデックスを使用します
ALTER TABLE Class ADD CONSTRAINT Class_Course FOREIGN KEY (Name)
   REFERENCES Course ON DELETE RESTRICT; -- 新しい Class_Course インデックスを作成します
ALTER TABLE Class (ADD PRIMARY KEY (ID)); -- 既存の UK_ID インデックスを使用します
ALTER TABLE Enrolls ADD CONSTRAINT Enrolls_Class FOREIGN KEY (Class_ID)
   REFERENCES Class ON DELETE RESTRICT; -- 新しい ClassID インデックスを作成します
Demodata サンプル データベースのテーブル設計
以下に、Demodata サンプル データベースのテーブルに関するガイドを示します。以下の情報は、各テーブルに収録されています。
テーブル内の列
各列のデータ型
列のサイズまたは長さ(バイト数)
キー(列がキーでない場合は空白)
インデックス(列にインデックスがない場合は空白)
BILLING テーブル
CLASS テーブル
COURSE テーブル
DEPT テーブル
ENROLLS テーブル
FACULTY テーブル
PERSON テーブル
ROOM テーブル
STUDENT テーブル
TUITION テーブル
BILLING テーブル
 
データ型
サイズ
キー
Student_ID
UBIGINT
8
主キー、外部キー
Transaction_Number
USMALLINT
2
主キー
Log
TIMESTAMP
8
 
Amount_Owed
DECIMAL
7.2
 
Amount_Paid
DECIMAL
7.2
 
Registrar_ID
UBIGINT
8
外部キー
Comments
LONGVARCHAR
65500
 
CLASS テーブル
 
データ型
サイズ
キー
ID
IDENTITY
4
主キー
Name
CHARACTER
7
外部キー
Section
CHARACTER
3
 
Max_Size
USMALLINT
2
 
Start_Date
DATE
4
 
Start_Time
TIME
4
 
Finish_Time
TIME
4
 
Building_Name
CHARACTER
25
外部キー
Room_Number
UINTEGER
4
外部キー
Faculty_ID
UBIGINT
8
外部キー
COURSE テーブル
 
データ型
サイズ
キー
Name
CHARACTER
7
主キー
Description
CHARACTER
50
 
Credit_Hours
USMALLINT
2
 
Dept_Name
CHARACTER
20
外部キー
DEPT テーブル
 
データ型
サイズ
キー
Name
CHARACTER
20
主キー
Phone_Number
DECIMAL
10.0
 
Building_Name
CHARACTER
25
外部キー
Room_Number
UINTEGER
4
外部キー
Head_of_Dept
UBIGINT
8
外部キー
ENROLLS テーブル
 
データ型
サイズ
キー
Student_ID
UBIGINT
8
主キー、外部キー
Class_ID
INTEGER
4
主キー、外部キー
Grade
REAL
4
 
FACULTY テーブル
 
データ型
サイズ
キー
ID
UBIGINT
8
主キー、外部キー
Dept_Name
CHARACTER
20
外部キー
Designation
CHARACTER
10
 
Salary
CURRENCY
8
 
Building_Name
CHARACTER
25
外部キー
Room_Number
UINTEGER
4
外部キー
Rsch_Grant_Money
FLOAT
8
 
PERSON テーブル
データ型
サイズ
キー
ID
UBIGINT
8
主キー
First_Name
VARCHAR
15
 
Last_Name
VARCHAR
25
 
Perm_Street
VARCHAR
30
 
Perm_City
VARCHAR
30
 
Perm_State
VARCHAR
2
 
Perm_Zip
VARCHAR
10
 
Perm_Country
VARCHAR
20
 
Street
VARCHAR
30
 
City
VARCHAR
30
 
State
VARCHAR
2
 
Zip
VARCHAR
10
 
Phone
DECIMAL
10.0
 
Emergency_Phone
CHARACTER
20
 
Unlisted
BIT
1
 
Date_Of_Birth
DATE
4
 
Email_Address
VARCHAR
30
 
Sex
BIT
1
 
Citizenship
VARCHAR
20
 
Survey
BIT
1
 
Smoker
BIT
1
 
Married
BIT
1
 
Children
BIT
1
 
Disability
BIT
1
 
Scholarship
BIT
1
 
Comments
LONGVARCHAR
65500
 
ROOM テーブル
 
データ型
サイズ
キー
Building_Name
CHARACTER
25
主キー
Number
UINTEGER
4
主キー
Capacity
USMALLINT
2
 
Type
CHARACTER
20
 
STUDENT テーブル
 
データ型
サイズ
キー
ID
UBIGINT
8
主キー、外部キー
Cumulative_GPA
DECIMAL
5.3
 
Tuition_ID
INTEGER
4
外部キー
Transfer_Credits
DECIMAL
4.0
 
Major
CHARACTER
20
外部キー
Minor
CHARACTER
20
外部キー
Scholarship_Money
DECIMAL
19.2
 
Cumulative_Hours
SMALLINT
2
 
TUITION テーブル
 
データ型
サイズ
キー
ID
INTEGER
4
主キー
Degree
VARCHAR
4
 
Residency
BIT
1
 
Cost_Per_Credit
REAL
4
 
Comments
LONGVARCHAR
65500