CSE2/4DBF Assignment 1 - Dealership Database System

AIMS AND OBJECTIVES:
✓ to represent a problem description given in natural language as an (Enhanced) EntityRelationship model
This is an individual Assignment. You are not permitted to work as a group when writing
this assignment.
Copying, Plagiarism: Plagiarism is the submission of somebody else’s work in a manner that gives
the impression that the work is your own. The Department of Computer Science and Information
Technology treats plagiarism very seriously. When it is detected, penalties are strictly imposed.
No extensions will be given: Penalties are applied to late assignments (5% of total assignment
mark is deducted per day, accepted up to 5 days after the due date only). If there are circumstances
that prevent the assignment being submitted on time, an application for special consideration may
be made. See Student Handbook for details. Note that delays caused by computer downtime cannot
be accepted as a valid reason for a late submission without penalty. Students must plan their work
to allow for both scheduled and unscheduled downtime.
SUBMISSION GUIDELINES:
This assignment is to be submitted in soft-copy (either PDF or JPEG) format using
the CSE2/4DBF submission link on LMS, by 10:00 am Wednesday August 18, 2021.
The submission link can be found under “Assignment 1 – Part 1” component in the
“Assessment” section of the subject’s LMS page.
SUBMISSION CHECKLIST:
✓ Your (Enhanced) Entity-Relationship Model (EER) for the proposed database
Students are referred to the Department of Computer Science and Information Technology’s
Handbook and policy documents about plagiarism and assignment return, and also to the section
of ‘Academic Integrity on the subject learning guide.

PROBLEM DESCRIPTION:
Dealership Database System
Archie’s Luxury Motors is a car dealership that specializes in high-end luxury vehicles in
addition to offering the biggest selection of local and international vehicles from the world’s
most popular manufacturers in all price brackets. Rather than being situated in the middle of
the hustle and bustle of a large metropolis, Archie's Luxury Motors enjoys an idyllic rural
setting in the beautiful open countryside.
The dealership currently records its data manually, keeping records in hardcopy, with
individual files being held for different car sale transactions. This process is slow, laborious,
and not very secure. As repeating fields and human error are being attributed to most of the
errors occurring, this reflects badly on the company’s image. The dealership would therefore
like to turn its manually kept information into a computerized database to help automate
processes. In addition, at this point in time due to the amount of manual transactions, there is
a large cost associated with keeping track of the cars currently in the dealership’s possession.
The owner, Archie, would like to reduce the losses and cut down unwanted costs and expenses
by keeping track of all car sale transactions and information regarding the dealership’s
customers and sales agents. To do this effectively, Archie has decided to automate all
administrative duties in relation to the process of recording car sale transactions and car
display shows.
Currently, when a customer is first interested in purchasing a car from the dealership, they
complete a form that contains their personal details along with their desired car features (see
Appendix 1 – “New Customer Questionnaire Form"). The dealership’s business premise
is to match cars with customers based on the features that a car has, and the features that a
customer desires. The form lists the most common car features, such as, keyless start, fuel
type, etc., but the complete list is far more extensive. For each feature, the dealership records
an ID of the feature, a short description, and the category of the feature (e.g., comfort &
convenience, safety & security, etc.). Alternatively, some customers are motoring enthusiasts
and leave the features section blank on the form. Instead, they inquire directly to the sales
agent about which car they are after.
Furthermore, the dealership not only records which features a car contains, but also its model
and the car manufacturer. In the car industry, many manufacturers follow what is known as a
‘product line architecture’. This means that rather than wasting time and money on a brandnew model design, the manufacturer would rather make small changes to an already markettested model. For each model, its model number, name, and type must be recorded (e.g., sedan,
4WD, etc.) and its previous model, if applicable. For each manufacturer, its name and region
(i.e., Europe, Asia, Oceania, etc.) must be recorded.
Archie offers a VIP program where customers can subscribe to become VIPs of the dealership.
This program is designed to entice customers to have a long-term relationship with the
company. In addition, the dealership organizes gala dinner every month where all VIPs are
invited.
Once the customer has decided to purchase a specific vehicle after conversing with one of the
dealership’s sales agents, a Bill Of Sale is completed to follow regulations (see Appendix 2 – “Vehicle Bill Of Sale Form"). The car’s 17-digit VIN number is recorded, along with the
customer’s and sales agent’s details.
In addition to the fields contained in Appendix 2, the dealership also keeps track of the date
that the car entered the dealership, the price that the dealership paid for the car, and the price
that the car is on sale for.
At the dealership, there is a hierarchy amongst the sales agents. Senior agents are the most
experienced and best negotiators. Each senior agent is assigned to supervise at least one junior
agent. Each junior agent has only one supervisor. When a junior agent is promoted to senior,
the date of promotion must be recorded. This is due to the commission policy at Archie’s
Luxury Motors. Senior agents receive a commission for each sale where the agreed price with
the customer is greater than the “for sale” price in the database (the asking price). The actual
amount of commission depends on how many years that the agent has been a senior. For
example, for an agent who has been a senior for 5 years, the commission for a sale would be
calculated as (AgreedPrice – AskingPrice) * 5%, where the agreed price is greater than the
asking price. Note that the percentage amount is always calculated in full years. For all sales
agents, their ID, name, and date of birth are also recorded.
Given that the dealership is situated on an extensive block of land, Archie decided to construct
a multi-million-dollar car showroom and test track to boost revenue. The dealership offers the
chance for a group of people (known as a Viewing Party) to come and view/test drive exotic
cars on-premise (see Appendix 3 – “Viewing Party Form"). To boost market research,
Archie offers special deals to viewing parties who are part of an organization or those who are
international guests. It is important to note that some cars are so breathtaking that it is common
for the same viewing party to view a particular car more than once. 

TASKS:
1. You are required to develop an EER model for the above problem description. The EER
should contain all necessary information such as entities, attributes, primary keys,
relationships (including specialization/union if any), cardinalities, and participation
(including (min, max)). Any assumptions should also be stated clearly. If assumptions are
made, it is important to make sure that the assumptions reflect possible real practice for a
similar business and do not contradict with the problem description above. Assignment
should be typed, not written/drawn by hand. Use any software to draw figures in your
assignment. However, make sure to follow the notations introduced in this subject.
[100%]
Please note the following important points before you get started:
• You are NOT required to transform your EER diagram into a set of tables for this
part of the assignment.
• You are also NOT required to perform normalization of the user views provided
in the appendices. The forms in the appendices should be used as a source of
supplementary information for this assignment. For example, Appendix 1 may
give you some clarity about what information is required to be stored for a
customer (among other things).