Developer Reference : Getting Started : Programmer's Guide : Sample Database Tables and Referential Integrity
 
Sample Database Tables and Referential Integrity
This appendix covers the following topics:
Overview of the Demodata Sample Database
Structure of the Demodata Sample Database
Example of Referential Integrity in the Demodata Database
Table Design of the Demodata Sample Database
Overview of the Demodata Sample Database
The sample database Demodata is provided as part of the Zen product and is frequently used in the documentation to illustrate database concepts and techniques. Even if you are already familiar with Zen, you may want to review the information in this appendix in order to become acquainted with the new sample database.
Even though you may not be working in an academic environment, you can use the sample database examples both as a template and a reference to help you design and develop your own customized information systems. You can use the sample queries and other aspects included in our example, since it reflects a real-life scenario.
Structure of the Demodata Sample Database
The physical structure of the database consists of the elements of a relational database: tables, columns, rows, keys, and indexes.
The database contains 10 tables with various relationships between them. It contains data on students, faculty, classes, registration, and so forth.
Assumptions
Following are some assumptions around which the database was built:
The scope of the database is one semester.
A student cannot take the same course more than once. For example, a student cannot enroll in Algebra I, Sections 1 and 2.
A faculty member can be a student, but a faculty member cannot teach and enroll in the same class.
Any course is offered by only one department.
In order for a student to receive a grade, they must be enrolled in a class, and a faculty member must be assigned to teach the class.
Faculty members belong to a single department, but they can teach for many departments.
All students have a Student ID that is based on the US standard of a social security number.
All faculty members have a Faculty ID that is based on the US standard of a social security number.
All other persons have a Person ID that is based on the US standard of a social security number.
Rooms are unique within the same building.
Two classes cannot be taught in the same room at the same time.
A faculty member can only be teaching one class at a given time.
Prerequisites are not required for enrollment in a class.
Departments imply majors.
A course can only be taught by one faculty member throughout the semester.
A telephone number or zip code does not correlate to a state.
A registrar cannot be a faculty member or student.
When a person is entered into the database, they can complete a survey of which they must answer all the questions or none of the questions.
Credit hours for a course are not necessarily equal to the number of hours that a class convenes.
An e-mail address does not have to be unique.
Entity Relationships
Entities are objects that describe primary components in the database. When designing a database, it is important to define the entities and their relationships to one another before proceeding further. In the Demodata database, CLASSES, STUDENTS, FACULTY, GRADES, and so forth are entities. The entities and their relationships to one another are outlined in the following diagram:
Figure 3 Entity Relationships
GRADES is a weak entity. It is dependent upon a student taking a class, so its existence is dependent upon the validity of other entities. The STUDENT and FACULTY tables create common information, since a student could be a faculty member and vice versa. The common information is in the PERSON table.
Example of Referential Integrity in the Demodata Database
This topic describes a referential integrity design applied to the Demodata sample database and includes a SQL script that can implement the design in the Demodata sample database.
The following diagram depicts a set of references among tables in Demodata. Boxes represent tables. Arrows indicate a referential constraint from a parent table to a referencing table. For example, in constraint 1, a foreign key in the Person table references a primary key in the Billing table.
Note This diagram also serves as a dependency graph to indicate the order in which tables must be populated for their references to one another to be valid. This order is reflected in the SQL script.
The following table lists these constraints and the relationships among the tables and columns.
Table 43 Tables and Columns Involved with Referential Integrity
Constraint
Referencing Table
Foreign Key
Referenced Table
Primary Key
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
The following script implements the references described in this topic. You can copy and paste the script in ZenCC to apply it to Demodata. We recommend you make a copy of Demodata and use the script on the copy.
ALTER TABLE Person (ADD PRIMARY KEY (ID)); -- uses existing PersonID index
ALTER TABLE Billing ADD CONSTRAINT Billing_Person FOREIGN KEY (Registrar_ID)
   REFERENCES Person ON DELETE RESTRICT; -- creates a new Billing_Person index
ALTER TABLE Student ADD CONSTRAINT Student_Person FOREIGN KEY (ID)
   REFERENCES Person ON DELETE RESTRICT; -- uses existing StudentID index
ALTER TABLE Faculty ADD CONSTRAINT Faculty_Person FOREIGN KEY (ID)
   REFERENCES Person ON DELETE RESTRICT; -- uses existing FacultyID index
ALTER TABLE Tuition (ADD PRIMARY KEY (ID)); -- uses existing UK_ID index
ALTER TABLE Student ADD CONSTRAINT Student_Tuition FOREIGN KEY (Tuition_ID)
   REFERENCES Tuition ON DELETE RESTRICT; -- uses existing TuitionID index
ALTER TABLE Room (MODIFY Building_Name CHAR(25) NOT NULL); -- Primary key must be NOT NULL
ALTER TABLE Room (MODIFY Number UINTEGER NOT NULL); -- Primary key must be NOT NULL
ALTER TABLE Room (ADD PRIMARY KEY (Building_Name, Number)); -- uses existing Building_Number index
ALTER TABLE Faculty ADD CONSTRAINT Faculty_Room FOREIGN KEY (Building_Name, Room_Number)
   REFERENCES Room ON DELETE RESTRICT; -- uses existing Building_Room index
ALTER TABLE Dept ADD CONSTRAINT Dept_Room FOREIGN KEY (Building_Name, Room_Number)
   REFERENCES Room ON DELETE RESTRICT; -- uses existing Building_Room index
ALTER TABLE Class ADD CONSTRAINT Class_Room FOREIGN KEY (Building_Name, Room_Number)
   REFERENCES Room ON DELETE RESTRICT; -- creates a new Class_Room index
ALTER TABLE Dept (ADD PRIMARY KEY (Name)); -- uses existing Dept_Name index
ALTER TABLE Faculty ADD CONSTRAINT Faculty_Dept FOREIGN KEY (Dept_Name)
   REFERENCES Dept ON DELETE RESTRICT; -- uses existing Dept index
ALTER TABLE Student ADD CONSTRAINT Student_Dept_Major FOREIGN KEY (Major)
   REFERENCES Dept ON DELETE RESTRICT; -- creates a new Student_Dept_Major index
ALTER TABLE Student ADD CONSTRAINT Student_Dept_Minor FOREIGN KEY (Minor)
   REFERENCES Dept ON DELETE RESTRICT; -- creates a new Student_Dept_Minor index
ALTER TABLE Course ADD CONSTRAINT Course_Dept FOREIGN KEY (Dept_Name)
   REFERENCES Dept ON DELETE RESTRICT; -- uses existing DeptName index
ALTER TABLE Faculty (ADD PRIMARY KEY (ID)); -- uses existing FacultyID index
ALTER TABLE Dept ADD CONSTRAINT Dept_Faculty FOREIGN KEY (Head_Of_Dept)
   REFERENCES Faculty ON DELETE RESTRICT; -- uses existing Dept index
ALTER TABLE Class ADD CONSTRAINT Class_Faculty FOREIGN KEY (Faculty_ID)
   REFERENCES Faculty ON DELETE RESTRICT; -- creates a new Class_Faculty index
ALTER TABLE Student (ADD PRIMARY KEY (ID)); -- uses existing StudentID index
ALTER TABLE Billing ADD CONSTRAINT Billing_Student FOREIGN KEY (Student_ID)
   REFERENCES Student ON DELETE RESTRICT; -- creates a new Billing_Student index
ALTER TABLE Enrolls ADD CONSTRAINT Enrolls_Student FOREIGN KEY (Student_ID)
   REFERENCES Student ON DELETE RESTRICT; -- uses existing StudentID index
ALTER TABLE Course (ADD PRIMARY KEY (Name)); -- uses existing Course_Name index
ALTER TABLE Class ADD CONSTRAINT Class_Course FOREIGN KEY (Name)
   REFERENCES Course ON DELETE RESTRICT; -- creates a new Class_Course index
ALTER TABLE Class (ADD PRIMARY KEY (ID)); -- uses existing UK_ID index
ALTER TABLE Enrolls ADD CONSTRAINT Enrolls_Class FOREIGN KEY (Class_ID)
   REFERENCES Class ON DELETE RESTRICT; -- creates a new ClassID index
Table Design of the Demodata Sample Database
The following guide to the tables in the Demodata sample database. This information is included with each table:
Columns in the table
Data types for each column
Size, or length, of the column in bytes
Keys (blank if the column is not a key)
Indexes (blank if the column does not have an index)
BILLING Table
CLASS Table
COURSE Table
DEPT Table
ENROLLS Table
FACULTY Table
PERSON Table
ROOM Table
STUDENT Table
TUITION Table
BILLING Table
 
Column
Data Type
Size
Keys
Student_ID
UBIGINT
8
PRIMARY, FOREIGN
Transaction_Number
USMALLINT
2
PRIMARY
Log
TIMESTAMP
8
 
Amount_Owed
DECIMAL
7.2
 
Amount_Paid
DECIMAL
7.2
 
Registrar_ID
UBIGINT
8
FOREIGN
Comments
LONGVARCHAR
65500
 
CLASS Table
 
Column
Data Type
Size
Keys
ID
IDENTITY
4
PRIMARY
Name
CHARACTER
7
FOREIGN
Section
CHARACTER
3
 
Max_Size
USMALLINT
2
 
Start_Date
DATE
4
 
Start_Time
TIME
4
 
Finish_Time
TIME
4
 
Building_Name
CHARACTER
25
FOREIGN
Room_Number
UINTEGER
4
FOREIGN
Faculty_ID
UBIGINT
8
FOREIGN
COURSE Table
 
Column
Data Type
Size
Keys
Name
CHARACTER
7
PRIMARY
Description
CHARACTER
50
 
Credit_Hours
USMALLINT
2
 
Dept_Name
CHARACTER
20
FOREIGN
DEPT Table
 
Column
Data Type
Size
Keys
Name
CHARACTER
20
PRIMARY
Phone_Number
DECIMAL
10.0
 
Building_Name
CHARACTER
25
FOREIGN
Room_Number
UINTEGER
4
FOREIGN
Head_of_Dept
UBIGINT
8
FOREIGN
ENROLLS Table
 
Column
Data Type
Size
Keys
Student_ID
UBIGINT
8
PRIMARY, FOREIGN
Class_ID
INTEGER
4
PRIMARY, FOREIGN
Grade
REAL
4
 
FACULTY Table
 
Column
Data Type
Size
Keys
ID
UBIGINT
8
PRIMARY, FOREIGN
Dept_Name
CHARACTER
20
FOREIGN
Designation
CHARACTER
10
 
Salary
CURRENCY
8
 
Building_Name
CHARACTER
25
FOREIGN
Room_Number
UINTEGER
4
FOREIGN
Rsch_Grant_Money
FLOAT
8
 
PERSON Table
Column
Data Type
Size
Keys
ID
UBIGINT
8
PRIMARY
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 Table
 
Column
Data Type
Size
Keys
Building_Name
CHARACTER
25
PRIMARY
Number
UINTEGER
4
PRIMARY
Capacity
USMALLINT
2
 
Type
CHARACTER
20
 
STUDENT Table
 
Column
Data Type
Size
Keys
ID
UBIGINT
8
PRIMARY, FOREIGN
Cumulative_GPA
DECIMAL
5.3
 
Tuition_ID
INTEGER
4
FOREIGN
Transfer_Credits
DECIMAL
4.0
 
Major
CHARACTER
20
FOREIGN
Minor
CHARACTER
20
FOREIGN
Scholarship_Money
DECIMAL
19.2
 
Cumulative_Hours
SMALLINT
2
 
TUITION Table
 
Column
Data Type
Size
Keys
ID
INTEGER
4
PRIMARY
Degree
VARCHAR
4
 
Residency
BIT
1
 
Cost_Per_Credit
REAL
4
 
Comments
LONGVARCHAR
65500