Rationale

This assignment has been designed to assess students’ ability to model a database, by constructing an Entity-Relationship Diagram (ERD) for a particular business scenario. This assignment addresses the following learning objectives for this subject:

•        Develop a database model using the entity-relationship model

•        Apply the techniques of normalisation

Requirements (Tasks)

You are given a business scenario and are required to draw an ERD for the scenario.

•    Task 1 : Write a brief discussion of your solution, i.e. how you approached the modelling problem and any issues you may have encountered (maximum 1⁄2     page)

•    Task 2 : Draw an Entity-Relationship Diagram (ERD), which is fully labelled   and implementable,  based on the business descriptions. Include all entities,        relationships, optionalities, connectivities, cardinalities and constraints. You must  use Crow’s foot notation and MySQL Workbench to create the ERD. A Hand- drawn ERD will NOT be accepted . A sample ERD can be found in Appendix A of this document. The ERD created using the drawing tool (e.g. MySQL Workbench)  will need to be saved (or exported) as an image file(PNG) and then be included in your document file to be submitted .

•    Task 3 : A point-form summary to describe the major justifications,               assumptions and limitations related to your database design. For example:         Assumption/justifications for optionality, connectivities, constraints data type and data domain; and Special cases or data integrity issues that cannot be handled.

Submission Items (2): 

Include the following in your submission (via LearnJCU):

1.  The original Workbench file containing your ERD (.mwb). Please name the file as

LastnameFirstname-A1.mwb

2.  A document file (DOCX or PDF format) which contains your answers for Tasks 1, 2 and 3. Please name the file as LastnameFirstnameA1.docx or              LastnameFirstname-A1.pdf.

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

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.

Samples of few of the required reports are below : 

1)  Report that shows Total Salary paid to employees for each service centre by year :

2) Report that shows all the Mobile Phone details and name of employee it has been assigned to :

3) Invoice to Customer :