Draw the E-R Diagram for the following scenario:
The Intercollegiate Athletic database supports the scheduling and the operation of events along with tracking customers, facilities, locations within facilities, employees, and resources to support events. To schedule an event, a customer initiates an event request with the Intercollegiate Athletic Department. If an event request is approved, one or more event plans are made. Typically, event plans are made for the setup, the operation, and the cleanup of an event. An event plan consists of one or more event plan lines.

For each event request, the database records the unique event number, the date held, the date requested, the date authorized, the status, an estimated cost, the estimated audience, the facility number (required), and the customer number (required). Each facility will have many event requests, and each event request is for one facility.

For each event plan, the database records the unique plan number, notes about the plan, the work date, the activity (setup, operation, or cleanup), the employee number (optional), and the event number (required). Each event plan is for one event request, but an event request can have multiple event plans.

For each event plan line, the database records the line number (unique within a plan number), the plan number (required), the starting time, the ending time, the resource number (required), the location number (required), and the quantity of resources required. In other words, event plan is for a location with a resource. Each resource can be used by many event plans, and each location can have many events plans.

For each customer, the database records the unique customer number, the name, the address, the contact name, the phone, the e-mail address, and the list of events requested by the

1

customer. A customer is not stored in the database until submitting an event request. A customer can submit many event requests, but each event request is from a single customer.

For each facility, the database records the unique facility number, the facility name, and the list of events in which the facility is requested.

For each employee, the database records the unique employee number, the name, the department name, the e-mail address, the phone number, and the list of event plans supervised by the employee. Each employee supervises many event plans, and each event plan is supervised by only one employee.

For each location, the database records the related facility number, the location number (unique within a facility), the name, and the list of event plan lines in which the location is used.

For each resource, the database records the unique resource number, the name, the rental rate, and the list of event plan lines in which the resource is needed.

2. Logical Model: (30% of the assignment grade)
Please draw the logical model for the data model you created in the first part. Please show all the referential integrity constraints using proper arrows between foreign and primary keys in with the right direction in the arrows.

Example: Location

Location no

Location Name

Answer rating: 100% (QA)

Solution Logical Model for the Intercollegiate Athletic Database Entities EventRequest eventNumber dateHeld dateRequested dateAuthorized status estimatedCost estimatedAudience facilityNumber customerN... View the full answer