Assessment item 1—Assignment 1 (COIT20247 T2 2018)
| Due date: Weighting: Length: |
Friday of Week 5 (11:45 pm AEST, August 10, 2018) 30% N/A |
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: QRentals database system
QRentals (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 aboutit? 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, CustomerName, Street, Suburb, State, PostCode, Email)
Invoice (InvoiceNumber, InvoiceDate, 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.
• Assignments that are submitted after the release of the model solution will receive
zero marks.
• 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.
Marking Criteria:
| Item | Maximum Marks | |
| 1 | Identified entities (including super type and sub type entities), associative entities, weak entities (if any) and named appropriately |
6 |
| 2 | Provided appropriate attributes | 5 |
| 3 | Provided identifiers/identifying attributes | 3 |
| 4 | Drawn correct relationships between entities and named them appropriately |
6 |
| 5 | Used proper symbols for • entities including super type and sub type; associative entities; weak entities if any • relationships • identifying attributes • min and max cardinalities for the relationships |
6.5 |
| 6 | Provided appropriate justification for assumptions and business rules |
3.5 |
| Penalty due to plagiarism/copying | ||
| Late Penalty: Less 5% of available marks per calendar day |
||
| Total | 30 |
The post QRentals (QR) is an Australian-owned business located in a large city that specializes appeared first on My Assignment Tutor.
-
- Assignment status: Resolved by our Writing Team
- Source@PrimeWritersBay.com
GET THIS PAPER COMPLETED FOR YOU FROM THE WRITING EXPERTS CLICK HERE TO ORDER 100% ORIGINAL PAPERS AT PrimeWritersBay.com
Comments
Post a Comment