Subject : Database Concept and design

Question 1 - Entity Relationship Diagram (20 Marks) The owners of a small device repair shop would like to keep track of the repair jobs for device they repair, the labour costs for each repair job, the repairmen performing each repair job, and the total cost of each repair job. When customers bring their devices in to be repaired, they make a deposit on the repair job and are given a date to collect their devices. Repairmen then perform repairs on the customers' computers based on the repair job, and detail the labour costs. Items are also categorized as Computer, Laptop, Tablet. An item must belong to one of these categories and it cannot belong to more than one at the same time. When customers return, they pay the total cost of the repair job (minus deposit), collect a receipt for their payment, and collect the repaired devices using the payment receipt. a) List the main entities. [4 marks] b) Identify the main relationship types and specify the cardinality for each relationship. [4 marks] c) Identify one supertype and its subtypes from the case above and state its constraints. [4 marks] d) Using your answers for (a), (b) and (c) draw a Crow's Foot ERD to represent the data requirements for the company. [8 marks]