Case Background
Spock Service Centre has ben servicing cars over the last 40 years. They started storing data in
a file-based system and then transitioned into using spreadshets. As years progresed, the
number of cars they service has grown exponentialy 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 curent
spreadshets.
You have ben provided with the folowing busines rules about Spock Service Centre
• Each customer is provided with a unique identifier. Details of a customer include: name,
addres, phone number and email adres. 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 dors (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 Centre is provided with a unique identifier. Details of a
branch include: name, addres, and phone number.
• Branches employ two diferent types of employes- Managers and Mechanics. Each
employee is provided with a unique identifier. This identifier is used acros the al of the
branches. Details of an employee would include: name, addres, phone number, and
annual salary. If the employe is a manager, then their start date is stored. If the employe
is a mechanic, then their highest qualification is stored. A mechanic may either work ful
time or part time. For part time mechanics, the number of hours they work per wek 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 diferent types. Each type of service has a unique identifier.
Any service type that is ofered 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 boking id is generated
when the customer makes a boking. As part of the booking proces, there are some
details which need to be included such as: the car to be serviced, the branch that service
wil hapen 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.
ISYS114 2018-Assignment 1
4
• A ful-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 boking has only one payment. Policy dictates
that no split payments are alowed
Task Descriptions
Your tasks in this asignment are as folows:
Task 1: ER Diagram (50 marks)
Based on the busines rules, you are expected to construct an Entity-Relationship (ER) diagram.
The ER diagram should include entities, attributes (if there are les than three specified in the
busines statements, please ad some extra atributes), identifiers and corresponding data types,
the relationships among entities with cardinality and asociative entities.
Task 2: EER Diagram (32 marks)
Extend Task 1 to apply generalization/specialization technique to construct an Enhanced-ER
(EER) diagram. The ER diagram should specify the complete (total) and disjoint (mutualy-
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.
Task 3: Relational Model (9 marks)
Create relational model for these specific relationships. The relational model should include
relations, fields, primary and foreign keys.
1) Service/Booking and payment
2) Part-time mechanics and branch
3) Manufacturer and car
Task 4: Normalisation (19 marks)
Use the table below to answer these questions
1) Which normal form. is this table in?
2) Decompose the table into 3NF. Show the proces of conversion
3) Draw a relational model for the question-you must show the relations, PKs and
ISYS114 2018-Assignment 1
5
CarRego
Manufacturer
ID
Manufacturer
Name
Service
Date
Car
Model
Serviced
BranchID
Serviced
BranchName ServiceID Type of Service
C1WE23 Man1 Holden 3/5/18 Barina Branch1 Burwood S1 Exhaust change
C1WE23 Man1 Holden 3/9/18 Barina Branch1 Burwood S2
Log bok
service
C1WE23 Man1 Holden 3/9/19 Barina Branch1 Burwood S2
Log bok
service
G3DEW1 Man2 Toyota 3/5/18 Corolla Branch1 Burwood S3
Tyre
replacement
G3DEW1 Man2 Toyota 4/6/18 Corolla Branch2 Ashfield S1 Exhaust change
PWE23G Man3 Audi 2/3/18 C5 Branch3 Marsfield S4
Oil
check/change
PWE23G Man3 Audi 3/9/18 C5 Branch4 Epping S2
Log bok
service
D96EWQ Man1 Holden 3/9/18 Barina Branch4 Epping S4
Oil
check/change
Report:
• To present your answers to these tasks, please use the template provided in the
assignment folder.
• Fil out the details provided on the first page of the template.
• For each task, remove the coments and add your model in.
• Assumptions: If you have any asumptions, please list them down in the last page of the
report
• If your images lack clarity, you wil not be given any marks. Tutors can zom in to check
the diagram. But we wil not be able to increase the quality of the image. Whatever is
submited is the final submission. So, please make sure your image is readable.
Submision
You are allowed to use either a profesional CASE tol, such as PowerDesigner or a simple online
tool like www.draw.io, to draw the data models for Tasks 1-3.
Please submit your work on iLearn (there is a submision link in the Assignments section) as a
report (as a .docx, .doc or .pdf) by the deadline. You need to rename the template provided using
this format (e.g., LastName_FirstName_StudID).
ISYS114 2018-Assignment 1
6
Marking Rubric
Marks Topic Explanation
Task 1 (ER)
50
marks
Entities Existence of corect entities?
Attributes
Correct atributes?
Correct identification of primary identifier?
Relationships
Existence of corect relationships?
Correct cardinalities?
Correct optional/mandatory values?
Task 2 (EER)
32
marks
Correct Super
types/Subtypes
Correct Super types/Subtype entities?
Valid reuse of atributes?
Relationships Valid relationships?
Specialization Constraints Correct disjointnes/completenes?
Task 3(Relational Model)
marks
Relations Existence of corect relations?
PKs Correct PK?
FKs Correct FK?
Task 4 (Normalisation)
22
marks
Q 1 Correct normal form?
Q 2 Correct second level conversion?
Q 2 Correct next level conversion?
Q 3 Relational schema
ISYS114 2018-Assignment 1
7
FAQs from last semester
1. Is PowerDesigner the mandatory case tool to use? Can I just hand draw and submit the
diagrams?
No, you can use any tols available. Beware of tols that alow you to use FKs in ER model.
You could use www.draw.io if you cannot use PowerDesigner
You are not alowed to submit hand drawn images. You wil NOT receive any marks for it
2. Can I use a diferent version of PowerDesigner?
Of course J
3. Can I make my own attributes up?
Yes, you can. Please make a note in your assumptions/justifications
4. Can I make my own data types up?
Yes, you can. Please make a note in your asumptions/justifications
5. Are the labs locked over the wekend, and if so, how are they unlocked?
No, they are not. Labs are open over the wekend -9 AM to 5 PM
6. I want to ad a PK in my asociative entity. PowerDesigner is being mean not leting me
do it.
Associative entities canot have identifiers in PowerDesigner. If you find an entity that
has identifiers but looks like an asociative one, you need to mention that in your
justification for the asignment. Use a normal entity
7. Every time, I try to put my data model on word it apears blur, please help
Go to your model, do Cntrl-A(select al), Cntrl-C(copy) and Cntrl-V(paste) on word
document. It is a high-res image with PowerDesigner. You could export the diagram as
image from draw.io website as wel
8. Do we ned to have a cover page on the document we are uploading?
Nope. Just use the template
9. I don’t understand the diference betwen service type and service
Here’s an analogy: Think about ‘servicetype’ as product category and ‘service’ as the
appointment(boking).
10. Do I have to save history of which managers have managed the branch?
Historical data about managers maintaining the branches are not within the scope of this
assignment
11. Do I have to show single-valued/multi-valued/composite attributes in my model?
No, we are fine with any atributes. You could list that in asumptions section
12. Do I have to ad domain in my asignment?
No, you don’t have to. Having a datatype would sufice
13. Can I use any other tool to draw the diagram?
Of course, whatever suits you. Visio/Word. We are testing your knowledge. Only
requirement is we canot acept hand-drawn diagrams
14. I want to show a weak entity in my diagram
It can be done using relationships, lok at Workshop 3 materials, please. If you think you
have one, just add a note in asumptions
15. Some of the relationships are vague from the case study. Can I make assumptions?
Yes, you can.
ISYS114 2018-Assignment 1
16. Is PowerDesigner lab on iLab?
No, it is to heavy to be made available online.
17. Can I have mandatory constraints on both sides in a relationship?
No, it wil make the data entry proces dificult.
18. Do I have to have mandatory/not nul attributes?
No, you don’t have to.
Notes about PowerDesigner
Trial version that is available for free (trial version for 15 days only).
Download from:
http:/PowerDesigner.de/en/trial-version-2/
Fil out the details on the form. You wil receive an email with instructions to download.
Works only on Windows Client 32-bit, 64-bit, Windows for IA64, X86, X86_64.
Sorry Macbook users! You wil have to use the lab computers to use PowerDesigner or use