Case Background

Spock service Center 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 Center finds it quite difficult to manipulate and share the data across branches. You are employed to develop a database model to replace the current spreadsheets. You have been provided with the following business rules about Spock Service Center

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, model, type (sedan/hatchback), number of doors (two/four) are stored.

Each car manufacturer has their own unique identifier. Details of a manufacturer include name, hotline, primary contact person name are also stored.

Every branch of Spock Service Center is provided with a unique identifier. Details of a branch include: name, address, and phone number.

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 time or part time. For part time mechanics, the number of hours they work per week is 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 payment is carried out for a booking. Each 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 Task Descriptions Your tasks in this assignment are as follows:

Task 1: ER Diagram

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.

Task 2: EER Diagram

Extend Task 1 to apply generalization/specialization technique to construct an Enhanced-ER (EER) diagram. The EER diagram should specify the complete (total) and disjoint (mutually exclusive) 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.