Database Modeling 2

Part One: ERD Design (30 points)

A company asks you to create an ERD for managing the task assignments on a work order. A work order records the set of tasks requested by a customer at a specified location.

• A customer has a unique customer identifier, a name, a billing address (street, city, state, and zip), and a collection of submitted work orders.
• A work order has a unique work order number, a creation date, a date required, a completion date, a customer, an optional supervising employee, a work address (street, city, state, zip), and a set of tasks.
• Each task has a unique task identifier, a task name, an hourly rate, and estimated hours. Tasks are standardized across work orders so that the same task can be performed on many work orders.
• Each task on a work order has a status (not started, in progress, or completed), actual hours, and a completion date. The completion date is not entered until the status changes to complete.

After reviewing your initial design, the company decides to revise the requirements. Refine your ERD to support the following new requirements:

• The company wants to maintain a list of materials. The data about materials include a unique material identifier, a name, and an estimated cost. A material can appear on multiple work orders.
• Each work order uses a collection of materials. A material used on a work order includes the estimated quantity of the material and the actual quantity of the material used.
• The estimated number of hours for a task depends on the work order and task, not on the task alone. Each task of a work order includes an estimated number of hours.

Using a drawing tool, create an ERD that reflects the final requirements. Start with the entities shown below, and to facilitate my grading, put the entities in your diagram in the positions shown below (you may add space between them as needed). Diagrams that do not conform to that requirement will be rejected and required to be redone in order to be graded. Then, document / defend your assumptions and design decisions in a bulleted list of brief statements. 30 points.

Keep the following in mind:

• Indicate primary keys by underlining.
• Add more entities as needed.
• Use meaningful verbs for relationship names (avoid “has”) and show both minimum and maximum cardinality indicators.
• Remember not to show foreign keys on the model.
• Determine which attributes may contain null values (assume nulls are not allowed unless the text indicates the value is optional or you can infer that it must be optional) and indicate this on the diagram by marking nullable attributes with a trailing question mark.

Part Two: ERD Review (30 points)

For the Intercollegiate Athletic ERD shown below, identify aspects of the diagram that do not conform to the specifications listed beneath, or which have diagram errors. Revise the diagram to correct the errors and conform to the specifications.

For convenience, here is a draw.io diagram for the above. Revise this diagram; do not reposition the entities.

• 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).
• 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).
• 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.
• 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 customer. A customer is not stored in the database until submitting an event request.
• 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 email address, the phone number, and the list of event plans supervised by the 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

Submission

Turn in a printed copy of your report.