B. Sample Database Tables and Referential Integrity
This appendix covers the following topics:
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: 
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 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
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 |
Last modified date: 11/04/2024