Case Study
Assessment
item 1—Assignment 1 (COIT20247 T2 2018)
Objectives
•
To develop a conceptual data model diagram
•
To perform logical design (optional)
Assignment Specification
In this assignment, you need to perform the following tasks for the
given case study:
•
Draw an Entity Relationship (ER) diagram
•
Perform logical design (optional)
•
Create 3NF Relations (optional)
Case
Study: Q Rentals database system
Q
Rentals (QR) is an Australian-owned business located in a large city that
specializes in car hire service. The management of QR has approached you to
build a relational database that will be used as the most critical backend
software component in their enterprise management system so that the relevant
business data will be recorded appropriately and further information query will
be processed efficiently. The proposed database system requires efficiently to
track all QR’s vehicles, customers, employees and relevant rental activities.
Consider the following user requirements carefully and design a database
conceptual schema (i.e. E-R model) to support such an application.
The
company owns a large number of cars for hire. These cars are usually parked in
a number of locations (belonging to QR’s property) in the city-wide, each
location at a distinct address. Each car has a unique registration number which
can used as an identifier. The car’s make and model, manufactured year, as well
as the number of seats must be recorded. All cars in this case study
hypothetically fall into a number of categories such as minivan, SUV, sedan,
ute, luxury limos, etc. The daily hire rate of a vehicle is dependent on its
category; for example the daily price of an ordinary sedan is approximately $35
while hiring a minivan may need paying up to $70 per day. In addition, each car
has been commercially insured. The related insurance details are required to
store in the database, which include the policy number, amount of premium,
insurer name, policy start date and expiry date.
The
system also needs to handle two different kinds of people: QR’s employees and
customers. An employee, whose main duty is to process booking request from
customers, is identified by a unique identification number, and has a name
consisting of first name and last name. Other data on employees need to record
are their birthdate, phone, skill level, annual salary and the working location
which normally is located at where the
QR cars have been parked. For each customer, in addition to an identification
number assigned, it is necessary to record his/her full name, license number,
address, and phone number. In addition, the customers are generally categorized
into two distinct types: individual and corporate. For an individual customer,
whether the customer has a personal life insurance or not is recorded. For a
corporate customer, the organization name, Australian Business Number (ABN) and
whether the business travel special deal is applicable or not are recorded.
To rent a car from QR, the customer must
contact the company by a phone call. An employee of QR will make a booking
through the phone. QR often also stores details of some customers who have made
enquiries through phone calls but have not made actual booking due to
availability issue or a factor of price. The database keeps their details on
record so that QR may follow up with them at a later stage particularly in
promotional activities. For each booking, a reservation number will be created
automatically. A brief description of reservation, the required category of car
and the booking date will be recorded. Then the customer will go to a
particular location to pick up the car at the scheduled start date (there is no
concern what time to pick up it). After the rental activity ends, the customer
returns the car to a location (the so called drop off location) which may be
different from the pickup location. The database must keep track of relevant
rental details such as pick up/drop off location, start date, end date as well
as the cost in the system. Finally, the database also stores an image for each
vehicle so that the prospective customers can conveniently view them online
before their hiring.
Assignment
Requirements
In this assignment, you need to create an Entity
Relationship (ER) diagram relevant to the above case study and optionally perform logical design to
produce appropriate 3NF Relations.
The attributes for various entities have not
been purposely described in the case study so that the students are encouraged
to further research and list the pertinent attributes in addition to the
required identifier for each and every entity in their ERD.
1 Draw an appropriate ER diagram
Use the symbols as prescribed in your
unit-textbook to draw the ER diagram (ERD) for the above case study.
Your ERD must
•
show all necessary entities, attributes and
relationships
•
show identifiers/identifying attributes as
necessary
•
show multi-valued attributes, if any
•
show participation and cardinality
•
show associative entities, if appropriate
•
show weak entities, if any
•
use the notation described in the set text
• use consistent and appropriate naming for
entities and attributes as specified in the unit text book
Some
business rules or other aspects of the case study may not be clear to you when
you read the case study. If this is the case, then you should either approach
your lecturer or tutor for clarification, or you may simply make an assumption
and then develop your ERD accordingly. For example, the case study might not
mention all relevant participation information (also called minimum
cardinalities). If so, you may make an assumption about what the minimum
cardinalities might reasonably be, and then show them in your ERD accordingly.
You should justify each assumption in terms of the business, for example: in
the case study regarding the E-R model for customers placing an order it is
assumed that each customer must have at least one order because it is assumed
that the business does not record customer details until the customer places an
order. To get yourself started, ask yourself, 'If I were running this business,
what things I need to keep a list of?' Write those things down. For each thing,
what information would you need to record about it? How can it be identified?
The answers to these questions will help you to develop your ERD.
2 Assumptions and Business rules
Provide assumptions and business rules relevant to your ERD.
3 Create Logical Design
(Optional):
If you would like to perform this optional
component of this assignment, you can map your Entity Relationship diagram
(ERD) into relations and make sure that all the relations are in 3NF. Provide
all the relations in the following format (for example):
Customer (CustomerId, Customer Name,
Street, Suburb, State, PostCode, Email)
Invoice (Invoice Number, Invoice Date, Total, CustomerId)
foreign key (CustomerId)
references Customer(CustomerId)
How and what to
submit:
You must make an electronic submission for this
assignment using the appropriate assignment submission link in the unit web
site.
You should submit a Microsoft Word document containing:
• A copy of your ERD.
• A list of business rules and assumptions
including their justifications.
• 3NF Relations – optional.
Your document should contain appropriate
identifying information (your student number, name, unit number, etc.) and
should contain appropriate headings for each section. Note: We recommend you to
use Microsoft Visio to draw the E-R diagram and then copy & paste it to the
Word document.
Important points
• You must work individually on this assignment.
• The model
solution to this assignment will be released approximately TWO (2) weeks after
the due date.
• You must
state in your assignment (at the end under the heading Acknowledgements is a
good place) all materials and sources you used. This may be as simple as 'This
assignment was completed using only the COIT20247 unit materials' to mention
that you have used only the items provided by the unit COIT20247.
• Before
submitting your assignment, review the marking criteria that are available in
the assignment specification. Ensure that all the criteria are addressed in
your submission.
• After
submitting your assignment, please verify that your submission was successful,
i.e. download your submitted files to ensure that they are correct.
• Penalty
related to late submission and plagiarism is handled as per the university
policies.
Comments
Post a Comment