Note: This is an individual assignment. While it is expected that students will discuss their ideas with one another, students need to be aware of their responsibilities in ensuring that they do not deliberately or inadvertently plagiarize the work of others.
CP2404 : SP52-2022
Assignment 1 – Database (conceptual) Modelling
Assessment Weight: 20%
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 Handdrawn 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 :
ServiceCentreID City Total Salary Paid Year
SVC100 Busan $ 456,345.00 2021
SVC101 Daegu $ 873,234.00 2021
SVC102 Incheon $ 35,765.00 2021
SVC100 Busan $ 735,643.00 2020
2) Report that shows all the Mobile Phone details and name of employee it has been assigned to :
EquipmentI D Equipme
nt
Type Brand Mode
l Purchas e Date Date Assigne d Date Returne d Employee Name
EQP1002 Mobile Phone Nokia G21 20/12/21 12/2/22 3/6/22 Candice Shew
EQP1004 Mobile Phone Nokia G21 11/10/21 7/6/22 Marlow Hugh
EQP1011 Mobile Phone Samsun
g Galax y 15/7/21 29/5/22 Finan
Dough
EQP1018 Mobile Phone Xiaomi Ultra 25/2/21 13/1/22 4/4/22 Munul Short
3) Invoice to Customer :
Customer Info : Will Smith Invoice No: DAEG3005
44, Third Street, Invoice Date: 15/12/21
Seoul 765436 Car Number : SE765K
Type of Service Details Price
Repair Brakes not working $ 6,532.00
Spare part change Engine replaced $ 235.00
Total $ 6,767.00
Discount (15%) $ 1,015.05
SubTotal $ 5,751.95
GST (12%) $ 690.23
Amount Due : $ 6,442.18
Note about the example ERD provided above:
• You are not required to specify extra information like FK1, FK2, U1, I1, I2 etc. Just ignore the notation from the example ERD given above. You are required to specify PK and FK clearly. MySQL Workbench will present PK or FK using the colour icons. If you use other ERD drawing tools you may notate PK and FK using underline ___ or square bracket [ ] respectively.
• You are not required to specify obvious cardinalities like (0,M), (1,1), (1,M) etc. You are required to specify specific cardinalites which are not presented using crow’s foot notation like (1,3), (4,10), (10,M) etc.
• If you want to present the supertype-subtype relationships using an extendedERD (EERD), you will need to draw the necessary EERD notations using facilities provided by the drawing tool software you use. Alternatively, you can present the supertype-subtype relationships using multiple 1:1 relationships having same PKs for all related entities and describe the special relationships in your document.
- Page 5 -