INFS1200 Module

COMP2714 Module1 1Case

CaseStudy
Study

Focus
Purpose: The purpose of this task is to develop experience in applying the Entity Relationship
Model to a real-world scenario.

Task
correspondence on the pages below, complete
Using the correspondence complete the
the following
following tasks.
tasks. Please ask your tutors
for help if you require clarification on any aspects of the brief.

Section A – Extended Entity Relationship (EER) Diagrams

Please read Correspondence 1, Correspondence 2 and Correspondence 3 before attempting

this section.

Using the correspondence on the pages below, create an EER diagram for Dirt Road Driving. Note:
There may be several correct ways to construct an EER diagram for this case study. You are
strongly encouraged however to justify your decisions as a group. Although there may not be one
correct answer for a given problem, there may be a more efficient/scalable solution.

You may either hand draw or digitally create your EER diagram. If you hand draw, your drawing must
be clear and neat. If you wish to create your EER diagram digitally, we recommend you use [Link]
however you may use other software if you prefer. Regardless of the software or method used to
create your EER diagram, it must conform to the notation standards taught in this course.

The University of Queensland ABN: 63 942 912 684

Brisbane QLD 4072 Australia CRICOS PROVIDER NUMBER 00025B
Correspondence
Correspondence 1:
From: peter@[Link]
infs1200@[Link]
To: comp2714@[Link]
Date: 17/3/2020 11:42 AM
Subject: Student Support for Industry Project
Subject: Student Support for Industry Project
Dear COMP2714
INFS1200 Student
StudentTeam,
Team,

I am emailing in regard to your lecturer’s offer for student assistance in a database project our
company is undertaking. I am the cofounder of a rural rideshare company called “Dirt Road Driving.”
We specialise in offering our clients rideshare services in locations which are not serviced by other
companies such as DiDi or Uber.

We are currently storing our data in an excel spreadsheet, however, to accommodate future growth
we would like to implement a database system to track our ridesharing activities. I have tried to
summarise the data stored currently in the excel sheet below. Would you please be able to create
an Entity-Relationship diagram for us to use in our planning phase?

Users: When a user signs up to create an account with us we ask them to provide their full name
and DOB. We also assign them a unique id number. A user can also register emergency contact(s)
specific to their account. Each emergency contact includes a name and contact details, with the
name being unique per user account.

Staff: We split staff into two categories, Administration and Drivers. All staff members have a staffID
but for administration staff their desk number is also recorded. By law we are required to record the
licence number of each driver. A staff member could work both as a driver and in administration.

Vehicles: We store the VIN number, make and model of each car used in our ride sharing service.
However, if the car is a 4WD then we also record the ride height and wheel type. For 2WD cars we
record if the car is front wheel drive or back wheel drive.

Trip: A trip is made up of a user, a vehicle and a driver as well as a time stamp of when the booking
is made. Two additional timestamps demonstrating the start and end of the trip are also recorded.
This allows us to calculate a ride fare. In a trip, the user may request to stop at several locations,
which are all recorded for safety purposes. After a trip is made the user can submit a rating.

On behalf of the company I would like to extend our sincere thanks for your help and assistance.

Kind regards,
Peter Thompson
Director of Innovation | Dirt Road Driving

CRICOS Provider No: 00025B 2

Correspondence 2:
infs1200@[Link]
From: comp2714@[Link]
To: peter@[Link]
Date: 18/3/2020 04:23 PM
Subject: RE: Student Support for Industry Project

Hi Peter,

Thank you so much for your email! The student cohort will indeed be very excited to assist with your
company’s project.

In order to produce an Extended Entity Relationship diagram which best meets Dirt Road Driving’s
needs, I have a few follow-up questions I want to ask to clarify some points of ambiguity.

Users: What “contact details” are stored for an emergency contact?

Staff: Do you store any personal information about staff members (name, DOB, address) in addition
to their staffID?

Trip: Just out of interest, why do you record the vehicle used for each trip? Wouldn’t a driver just
drive the same vehicle each time?

Ratings: You mentioned in your previous email that after each trip is completed the user provides a
rating? Could you please provide some more information on how this works? For example, does the
user rate the driver, vehicle or both? Is the rating stored in a numerical format or as a text-based
response? Also, if a user takes another trip with the same driver or vehicles does a new rating get
stored or is the old one updated?

Once we can clarify these points, I will forward our correspondence onto our student teams. We look
forward to working with you and your company!

Your sincerely,
Elaine Smith
INFS1200 Student
COMP2714 StudentRepresentative
Representative

CRICOS Provider No: 00025B 3

Correspondence 3:
From: peter@[Link]
To: infs1200@[Link]
Date: 27/3/2020 08:56 AM
Subject: RE: Student Support for Industry Project

Hi Elaine,

Sorry for my late response. Thank you for your email requesting clarification. After checking with
our administration staff, I can provide the following information in response to your questions.

What “contact details” are stored for an emergency contact?

We just store an email address and a phone number.

Just out of interest, why do you record the vehicle used for each trip? Wouldn’t a driver just drive
the same vehicle each time?
Actually, a driver could potentially driver any vehicle in our system when they complete a trip.
Drivers often switch cars depending on the trip, especially in areas where there are no roads or just
dirt tracks. As such we need to record the vehicle used for each individual trip as well. A driver is
never permanently assigned to a specific vehicle.

Do you store any personal information regarding staff members (name, DOB, address) in addition
to their staffID?
Yes, we record each staff members full name (first, middle and last), DOB, address and their
phone number(s) as well.

Regarding Ratings…
After a trip is completed the user is encouraged to rate the driver and the vehicle. However, they
rate the driver and the vehicle separately. Each rating is stored as an integer out of 10. If a user
takes another trip with the same driver or vehicle and decides to submit another rating, a new
rating is not created, instead their old rating is updated.

I hope this helps! Please feel free to contact me if you require any more information/clarifications. I
look forward to viewing the student responses!

Kind regards,
Peter Thompson
Director of Innovation | Dirt Road Driving

CRICOS Provider No: 00025B 4