Task 2: Designing an Entity-Relationship Model

Part A: Initial Design Quick Service Case Study Quick Service is an Australian vehicle repair centre. You are asked to design a database for managing the vehicle service details. Requirements for the database are as follows:

Vehicles are brought in for service. For each vehicle the database records the registration number, make, model and year.

The date, time, duration and fee of the service are also recorded, along with the details of the mechanic who performed the service (employee id and name).

During the service a log of issues that were identified are recorded. For each issue, the name, description and outcome are stored.

For each issue identified, the systems records the parts that were installed. The parts have a partNo, supplier, description and cost. Note that the same part may be available from more than one supplier. The same part may be used as part of different issues. Based on the given description, model the business rules of Quick Service, and present your model as an EntityRelationship (ER) diagram. Carefully state any assumptions that you make. In your ER diagram, you must properly denote all applicable concepts, including weak or strong entities, keys, composite or multi-valued attributes, relationships and their cardinality and participation constraints. If you cannot represent any of this information in the ER model, clearly explain what limitations in the ER model restrict you from representing your model. Avoid introducing unnecessary artificial keys. You must use UML notation and the diagramming tool Lucidchart to draw your diagram. Your diagram must be drawn to a high standard with minimal clutter. You are not required to map the ER model to relational model. A special note: This is an open-ended question with many different models that can be derived. Your model is assessed based on how accurately it represents business rules described above.

Part B: Client Adjustments After presenting your ER model to Quick Service management, you are asked if it can be used to perform the following additional tasks.

Several mechanics may be involved in the service of a vehicle. The system need to be able to identify which mechanic installed each part and resolved each issue during the service.

1 The system needs to record not just the cost of parts but also the installation fee.

2 Some mechanics have higher seniority and may supervise other mechanics. Mechanics cannot have more than one supervisor. The date when they began supervising others is recorded.

3 The same issue could come up with a different description or outcome during various services of vehicles.

For each one of the tasks specified above, explain how your ER diagram is supporting it. If it is not possible to achieve any of the tasks above given your current design, state why, modify the model, provide the modified ER diagram (in addition to your original ER diagram), and explain how the new model achieves the missing requirements