Question

Case Background Spock Service Centre has been servicing cars over the last 40 years. They started storing data in a file-based system and then transitioned into using spreadsheets. As years progressed, the number of cars they service has grown exponentially leading to an increase in number of branches and volume of data. Spock Service Centre finds it quite difficult to manipulate and share the data across branches. You are employed to develop a database model to replace the current spre You have been provide d with the following business rules about Spock Service Centre Each customer is provided with a unique identifier. Details of a customer include: name, address, phone number and email address. A customer could sign up for the loyalty program. A loyal customer would get a set discount rate which is recorded along with the signup date. .Some details about a car would include: registration number, modl type (sedan/hatchback), number of doors (two/four) are stored name, hotline, primary contact person name are also stored branch include: name, address, and phone number Each car manufacturer has their own unique identifier. Details of a manufacturer include- Every branch of Spock Service Centre is provided with a unique identifier. Details of a .Branches employ two different types of employees- Managers and Mechanics. Each employee is provided with a unique identifier. This identifier is used across the all of the branches. Details of an employee would include: name, address, phone number, and annual salary. If the employee is a manager, then their start date is stored. If the employee is a mechanic, then their highest qualification is stored. A mechanic may either work full y work per week is For part time mechanics, the number of hours e or part time. also recorded. The "salary" stored for the part time mechanic is the annual amount they would earn based on the number of hours they are working per week. A service can be one of many different types. Each type of service has a unique identifier. Any service type that is offered is available at any branch. Each service type needs to include: a name, a description and cost of service. A customer must make a booking to service their car. A unique booking id is generated when the customer makes a booking. As part of the booking process, there are some details which need to be included such as: the car to be serviced, the branch that service will happen at, the type of service, the primary mechanic who services the car, and the date and time of service. Policy dictates that a booking is for one service type only. .Only one payme nt is carried out for a booking. Eac h payment has a unique identifier. The mode of payment and payment date are also recorded. A customer can own many cars. However, a car belongs to one customer only A car is manufactured by a single manufacturer A manager can work for one branch only. A branch is managed by one manager only. A full-time mechanic can work for one branch only. A single branch may employ multiple full-time mechanics. .A part-time mechanic may work for multiple branches. A single branch may employ multiple part-time mechanics. Number of hours they work at each branch is recorded. A payment is for a particular booking. A booking has only one payment. Policy dictates that no split payments are allowed .

Q1.a) Based on the business rules, you are expected to construct an Entity-Relationship (ER) diagram. The ER diagram should include entities, attributes (if there are less than three specified in the business statements, please add some extra attributes), identifiers and corresponding data types, the relationships among entities with cardinality and associative entities.

.b) Extend Q1.a) to apply generalization/specialization technique to construct an Enhanced-ER (EER) diagram. The EER diagram should specify the complete (total) and disjoint (mutuallyexclusive) constraints on the generalization/specialization. You do not have to draw the entire diagram again. You have to include entities that are part of inheritance. If these entities have relationships with other entities, include those relationships and related entities also.

.c) Create relational model for these specific relationships. The relational model should include relations, fields, primary and foreign keys. 1)Booking and payment 2)Part-time mechanics and branch 3) Manufacturer and car

Answer 1

given by Custo Cust tale roper rofe Cout Cusfo 1 OLD Proferty Profe Cus -Р人.num ericim escrifti Tackage n Packaje rodi acK-Tim ackage Address Inal rnt Services Price erviCe menu