サンプル データベース テーブルと参照整合性
この付録では以下の項目について説明します。
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 スクリプトに反映されます。
次の表は、これらの制約、およびテーブルと列の間の関係を示します。
表 43 参照整合性に関連するテーブルと列
制約 | 参照元テーブル | 外部キー | 参照先テーブル | 主キー |
|---|
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 テーブル
列 | データ型 | サイズ | キー |
|---|
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 | |
