Reference no: EM132895649

For the following problem, define an ERD for the initial requirements and then revise the ERD for the new requirements. Your solution should have an initial ERD, a revised ERD, and a list of design decisions for each ERD. In performing your analysis, you may want to follow the approach presented in Section 6.1. In this problem, you should focus on consistency between the problem narrative and your ERD. Errors involve inconsistency between your ERD and the problem narrative. Do not substitute your judgement to override details in the problem narrative. If a problem narrative omits details, you are free to provide your own interpretation in the ERD. I recommend design documentation to indicate your judgement when a problem narrative omits details relevant to your ERD. (40%)

Design a database for an intercollegiate athletic department to approve event requests and manage event plans for holding approved events.

A customer has a unique customer identifier, name (typically organization name), a billing address (street, city, state, and zip), email address, phone number, and a collection of submitted event requests.

For event requests, the database records the unique event request number, date held, date requested, date authorized, status (pending, approved, denied), estimated cost, estimated audience, facility (required), and customer (required). For approved event requests, the database records the list of associated event plans.

For each facility, the database records the unique facility number, facility name, and the list of related event requests.

For each event plan, the database contains the unique plan number, notes about the plan, work date, activity (setup, operation, or cleanup), employee number (optional), and event request (required).

After reviewing your initial design, the athletic department decides to extend the requirements to manage resources and locations used in event plans.

Make a separate ERD to show your refinements. Refine your original ERD to support the following new requirements.

Expand the employee number in an event plan to record details about an employee. For each employee, the database records the unique employee number, name (first and last), department name, email address, phone number, and list of event plans supervised by the employee.

Extend event plans with details about event plan lines to record resources and locations required. Each event plan must have one or more event plan lines.

For each event plan line, the database records the line number (unique within a plan number), event plan (required), starting date/time, ending date/time,

resource (required), location (required), and quantity of resources required.

?

For each location, the database records the related facility, location number

(unique within a facility), location name, and list of event plan lines in which

the location is used.

?

For each resource, the database records the unique resource number, resource

name, rental rate, and list of event plan lines in which the resource is needed.