Intercollegiate Athletic Database Tables
You will use the Intercollegiate Athletic database modules 2 to 5. This document describes the tables and relationships.
Table Description and Usage
The EventRequest table is the hub of the database. An event request represents an event scheduled at a facility. For example, a basketball game may be scheduled at the gymnasium. Events are sometimes scheduled several months in advance. Holding an event requires resources including personnel and equipment. Resources are assigned to specific locations of a facility. For example, guards may be required at the gates of the football stadium. The EventPlan table defines a plan for the setup, operation, and cleanup of an event. The EventPlanLine table contains the individual resources required in an event plan.
The Intercollegiate Athletic database supports the scheduling and operation of events. Customers initiate event requests with the Intercollegiate Athletic Department. The facility and date held are recorded on the event request. If an event request is denied, no additional action is taken. If an event request is approved, one or more event plans are made. Typically, event plans are made for the setup, operation, and clean up of an event. An employee is assigned to manage an event plan before the plan is executed. Initially, there may not be an assigned employee. An event plan consists of one or more event plan lines. In an event plan line, the resource, location, time, and number of resources (EventPlanLine.Number) are recorded.
Table Listings
Customer
| C100 |
Football |
Box 352200 |
Yes |
Mary Manager |
6857100 |
Boulder |
CO |
80309 |
| C101 |
Men's Basketball |
Box 352400 |
Yes |
Sally Supervisor |
5431700 |
Boulder |
CO |
80309 |
| C103 |
Baseball |
Box 352020 |
Yes |
Bill Baseball |
5431234 |
Boulder |
CO |
80309 |
| C104 |
Women's Softball |
Box 351200 |
Yes |
Sue Softball |
5434321 |
Boulder |
CO |
80309 |
| C105 |
High School Football |
123 AnyStreet |
No |
Coach Bob |
4441234 |
Louisville |
CO |
80027 |
Employee
Facility
| F100 |
Football stadium |
| F101 |
Basketball arena |
| F102 |
Baseball field |
| F103 |
Recreation room |
Location
| L100 |
F100 |
Locker room |
| L101 |
F100 |
Plaza |
| L102 |
F100 |
Vehicle gate |
| L103 |
F101 |
Locker room |
| L104 |
F100 |
Ticket Booth |
| L105 |
F101 |
Gate |
| L106 |
F100 |
Pedestrian gate |
ResourceTbl
| R100 |
attendant |
$10.00 |
| R101 |
police |
$15.00 |
| R102 |
usher |
$10.00 |
| R103 |
nurse |
$20.00 |
| R104 |
janitor |
$15.00 |
| R105 |
food service |
$10.00 |
EventRequest
| E100 |
25-Oct-2018 |
06-Jun-2018 |
F100 |
C100 |
08-Jun-2018 |
Approved |
$5,000.00 |
80000 |
B1000 |
| E101 |
26-Oct-2018 |
28-Jul-2018 |
F100 |
C100 |
|
Pending |
$5,000.00 |
80000 |
B1000 |
| E102 |
14-Sep-2018 |
28-Jul-2018 |
F100 |
C100 |
31-Jul-2018 |
Approved |
$5,000.00 |
80000 |
B1000 |
| E103 |
21-Sep-2018 |
28-Jul-2018 |
F100 |
C100 |
01-Aug-2018 |
Approved |
$5,000.00 |
80000 |
B1000 |
| E104 |
03-Dec-2018 |
28-Jul-2018 |
F101 |
C101 |
31-Jul-2018 |
Approved |
$2,000.00 |
12000 |
B1000 |
| E105 |
05-Dec-2018 |
28-Jul-2018 |
F101 |
C101 |
01-Aug-2018 |
Approved |
$2,000.00 |
10000 |
B1000 |
| E106 |
12-Dec-2018 |
28-Jul-2018 |
F101 |
C101 |
31-Jul-2018 |
Approved |
$2,000.00 |
10000 |
B1000 |
| E107 |
23-Nov-2018 |
28-Jul-2018 |
F100 |
C105 |
31-Jul-2018 |
Denied |
$10,000.00 |
5000 |
|
EventPlan
| P100 |
E100 |
25-Oct-2018 |
Standard operation |
Operation |
E102 |
| P101 |
E104 |
03-Dec-2018 |
Watch for gate crashers |
Operation |
E100 |
| P102 |
E105 |
05-Dec-2018 |
Standard operation |
Operation |
E102 |
| P103 |
E106 |
12-Dec-2018 |
Watch for seat switching |
Operation |
|
| P104 |
E101 |
26-Oct-2018 |
Standard cleanup |
Cleanup |
E101 |
| P105 |
E100 |
25-Oct-2018 |
Light cleanup |
Cleanup |
E101 |
| P199 |
E102 |
10-Dec-2018 |
Standard operation |
Operation |
E101 |
| P299 |
E101 |
26-Oct-2018 |
|
Operation |
E101 |
| P349 |
E106 |
12-Dec-2018 |
|
Cleanup |
E101 |
| P85 |
E100 |
25-Oct-2018 |
Standard operation |
Setup |
E102 |
| P95 |
E101 |
26-Oct-2018 |
Extra security |
Setup |
E102 |
EventPlanLine
| P100 |
1 |
8:00 |
17:00 |
2 |
L100 |
R100 |
| P100 |
2 |
12:00 |
17:00 |
2 |
L101 |
R101 |
| P100 |
3 |
7:00 |
16:30 |
1 |
L102 |
R102 |
| P100 |
4 |
18:00 |
22:00 |
2 |
L100 |
R102 |
| P101 |
1 |
18:00 |
20:00 |
2 |
L103 |
R100 |
| P101 |
2 |
18:30 |
19:00 |
4 |
L105 |
R100 |
| P101 |
3 |
19:00 |
20:00 |
2 |
L103 |
R103 |
| P102 |
1 |
18:00 |
19:00 |
2 |
L103 |
R100 |
| P102 |
2 |
18:00 |
21:00 |
4 |
L105 |
R100 |
| P102 |
3 |
19:00 |
22:00 |
2 |
L103 |
R103 |
| P103 |
1 |
18:00 |
21:00 |
2 |
L103 |
R100 |
| P103 |
2 |
18:00 |
21:00 |
4 |
L105 |
R100 |
| P103 |
3 |
19:00 |
22:00 |
2 |
L103 |
R103 |
| P104 |
1 |
18:00 |
22:00 |
4 |
L101 |
R104 |
| P104 |
2 |
18:00 |
22:00 |
4 |
L100 |
R104 |
| P105 |
1 |
18:00 |
22:00 |
4 |
L101 |
R104 |
| P105 |
2 |
18:00 |
22:00 |
4 |
L100 |
R104 |
| P199 |
1 |
8:00 |
12:00 |
1 |
L100 |
R100 |
| P349 |
1 |
12:00 |
15:30 |
1 |
L103 |
R100 |
| P85 |
1 |
9:00 |
17:00 |
5 |
L100 |
R100 |
| P85 |
2 |
8:00 |
17:00 |
2 |
L102 |
R101 |
| P85 |
3 |
10:00 |
15:00 |
3 |
L104 |
R100 |
| P95 |
1 |
8:00 |
17:00 |
4 |
L100 |
R100 |
| P95 |
2 |
9:00 |
17:00 |
4 |
L102 |
R101 |
| P95 |
3 |
10:00 |
15:00 |
4 |
L106 |
R100 |
| P95 |
4 |
13:00 |
17:00 |
2 |
L100 |
R103 |
| P95 |
5 |
13:00 |
17:00 |
2 |
L101 |
R104 |
Primary and Foreign Keys
The primary and foreign keys are depicted in Figure 1. An event request is related to many (one or more) event plans but only one customer. An event plan contains many event plan lines but only one supervising employee. An event plan line references a resource and location. A facility has many locations, but a location is specific to a facility.
Figure 1: Oracle Relational Database Diagram for the Intercollegiate Athletic Database
All foreign key columns are required except for EventPlan.EmpNo. When a column is required, the user must enter a valid value according to the specified integrity rules (including referential integrity). For example when entering a new row in the EventRequest table, the user must know the customer number.