Entity-Relationship (ER) diagram. The ER diagram should includeentities, attributes (if there are less than three specified in thebusiness statements, please add some extra attributes), identifiersand corresponding data types, the relationships among entities withcardinality and associative entities.
Task 2: EER Diagram Extend Task 1 to applygeneralization/specialization technique to construct an Enhanced-ER(EER) diagram. The EER diagram should specify the complete (total)and disjoint (mutuallyexclusive) constraints on thegeneralization/specialization. You do not have to draw the entirediagram again. You have to include entities that are part ofinheritance. If these entities have relationships with otherentities, include those relationships and related entitiesalso.
Task 3: Relational Model Create relational model for thesespecific relationships. The relational model should includerelations, fields, primary and foreign keys. 1) Booking and payment2) Part-time mechanics and branch 3) Manufacturer and car
Assumptions can be made.
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 . Show transcribed image text