Draw an ERD Diagram For the following Case:

Business Description (Scenario)

Success Motors is an established car service company. It has a number of service centres across various cities in Korea. With good business, Success Motors CEO, Nam has decided to develop a new central database system to store, integrate and manage all relevant data and to computerize its operations. The new database will be called as SPEED. Upon launch of this new central system, each service centre will be connected to this central database and their data-related operations will be governed by this central database system. You have been asked to design a database that satisfies many user requirements provided by the management. General business description and various user requirements are summarized here.

Success Motors has a head office in Seoul and the various service centres are located in multiple cities across Korea. The central database keeps information about service centres, staff, equipment, customers and their cars, and visit information. Each service centre has an ID number that uniquely identifies the service centre and the authorized users of the database should be able to track the service centre’s name, location and details of employees who currently work for the specific service centre.

Each service centre of Success Motors is supervised by a manager. It is expected that SPEED database will be used to generate reports for various HR (Human Resource) related jobs. For instance, an authorized user should be able to generate a report to show the current employee records of a specific service centre, a report to show the employee history of a service centre including all records of previous managers and other employees, or a report to compare how much total salary has been paid for each service centre in a particular year.

Some of the employees are professional technician staff and others are admin staff. Most of these staff are permanently employed by Success Motors and their employment is governed centrally, but some local staff are employed at a service centre level temporarily (called as “part-time workers”). In particular for all permanently-employed staff, SPEED database needs to keep personnel information of all of these staff and information about relevant qualification of all technician staff so that an authorized user of the database can generate a report to show the status of various professional human resources that SPEED database keeps currently. The information about part-time workers are recorded in the database but only basic information (name, address, DOB, start date, end date, payment rate, work hours etc.) are required to be kept and managed.

All Success Motors employees (technician staff and admin staff) are provided a mobile phone and some of them use a personal tablet computer, both of which are supplied to each employee when they join the company, and which they return when they leave. Part-time workers are provided a mobile phone but not a tablet computer. Each mobile phone and computer has a unique number for stock-take purposes. Mobile phones or computers are fully managed by Success Motors for any issue including repairing. Employees can return their hired items when needed to replace with other items. The authorized user of the database should generate a report showing, for each mobile phone, the brand, specific model, date of purchase and the name of the employee it has been hired to. In a similar way, the user should be able to generate a report that shows for each computer: the computer number, type, model, other specifications, history of repairs (if exists) and the name of the employee(s) it has been hired by.

With respect to customer engagement, Success Motors also wants to keep all customer-relevant data in centralized manner so that the head office can manage

2

status of all customers and their cars of each service centre and can generate a report to compare each service centre’s customer status (in terms of current numbers, growing rate, etc.). Success Motors runs an incentive system to reward annually the service centre which achieved the most significant growth rate throughout the year.

Customer information includes contact details to enable newsletters and other information to be mailed to the customer. The service centre has different billing rates depending on the nature of customer. Staff members receive a 30% discount on all car servicing and spare parts while valued customers may qualify for a 15% discount. The discount rates are reviewed each year and are open to change.

Information stored on cars includes brand, model, color, warranty type and warranty expiry date. The technician would also like to be able to store a general service related comment about the car with their record as well. If a car has not been in for a visit in the last 10 years then the car’s records are removed from the database. The service center encourages yearly servicing of all their customer cars - if a car has not been in for a visit in a year, then a reminder notice is mailed to the owner.

Information about every car service visit should be recorded to keep details about repairs performed and spare parts changed. All customers receive an itemized account of each servicing visit. All visits incur a 12% Goods and Services Tax which is shown on the invoice.

The SPEED database needs to provide graphical user interfaces for facilitating the entry of the information by the service reception staff.

The system you design needs to be able to produce a number of reports which can be used by authorized users. These include a current cars and owners directory, service invoicing reports and mailing labels. Samples of what the client expects these reports to look like are shown in the next page of this document. Please note that these are just some report samples and more various reports should be able to be created by the various service centre admin staff or management once the SPEED database is fully implemented.

Draw the ERD Diagram and SQL Code for Tables in MySQL Workbench
 

1) Report that shows Total Salary paid to employees for each service centre by year : ServiceCentrelD SVC100 SVC101 SVC102 SVC100 2) Report that shows all the Mobile Phone details and name of employee it has been assigned to : EquipmentI D EQP1002 EQP1004 EQP1011 EQP1018 City Total Salary Paid Year Busan $ 456,345.00 2021 Daegu $ 873,234.00 2021 Incheon $ 35,765.00 2021 Busan $ 735,643.00 2020 Customer Info : Equipme nt Type Mobile Phone Mobile Phone Mobile Phone Mobile Phone Brand Nokia Nokia Samsun g Xiaomi 3) Invoice to Customer : Will Smith 44, Third Street, Seoul 765436 Mode G21 G21 Galax y Ultra Type of Service Repair Spare part change Purchas e Date 20/12/21 11/10/21 7/6/22 15/7/21 29/5/22 25/2/21 Invoice No: Invoice Date: Car Number : Date Assigne d Total Discount (15%) SubTotal GST (12%) Amount Due : 12/2/22 13/1/22 Details Brakes not working Engine replaced Date Returne d 3/6/22 Dough Munul 4/4/22 Short DAEG3005 15/12/21 SE765K Employee Name Candice Shew Marlow Hugh Finan Price $ 6,532.00 $ 235.00 $ 6,767.00 $ 1,015.05 $ 5,751.95 $ 690.23 $ 6,442.18