You have been commissioned to design a database to manage the i
- Page 1 -
Note: This is an individual assignment. While it is expected that students will
discuss their ideas with one another, students need to be aware of their
esponsibilities in ensuring that they do not deliberately or inadvertently
plagiarize the work of others.
Assessment 1 – Database (conceptual) modelling
Due date: End of Week 8 (Sunday, 13th November 2022)
Assessment Weight: CP XXXXXXXXXX% CP XXXXXXXXXX%
Rationale
This assignment has been designed to assess students’ ability to model a database,
y constructing an entity-relationship diagram for a particular business scenario. This
assignment addresses the following learning objectives for this subject:
• Develop a database model using the entity-relationship model and
normalisation techniques
Requirements
You are given a business scenario and are required to draw an ERD for each scenario. You
must use crow’s foot notation and are only allowed to use MySQL Workbench to draw your
ERDs. A hand-drawn ERD or ERD drawn using other tools will NOT be accepted.
The completed ERD must be fully labelled and implementable by presenting all necessary
components including entities, relationships, connectivities, cardinalities, optionalities and
constraints. Each entity should contain basic but necessary attributes including primary
keys (PKs), foreign keys (FKs) and other attributes. You do not need to add unnecessarily
additional attributes to an entity by your own imagination. To decide some optionalities,
you may need to make your own assumptions due to the lack of information you can
achieve from the given text scenarios. You are required to accompany the list of
assumptions you made to the ERD.
Submission
Include the following in your submission (via LearnJCU):
• A document file (MS Word or PDF format) which contains the image of ERD and list
of assumptions.
• The original Workbench file containing your ERD (.mwb)
You can submit as many times as you want until the submission due, but only the last
submission (made before the submission due) is graded. If your (first) submission is made
after the submission due, only the first submission is graded and the late submission
penalty will be applied as per the policy. Refer to the subject outline for the policy on late
submissions.
- Page 2 -
To help your understanding about the requirements of this assignment, a sample ERD
solution for a given sample scenario is provided below.
Sample Task and Sample Solution
o Task (sample)
Imagine you are asked to create a conceptual database model by drawing an
ERD for a business scenario as follows:
A caravan park has several types of accommodation: On-site vans ($30), Standard
cabins ($33), En-suite cabins ($45) and Deluxe cabins ($49). For each type of
accommodation, there are several sites available in the caravan park. For example,
sites 2, 3 and 11 are all Standard cabins.
When a customer first a
ives or makes a booking, they are assigned a customer
code. They are asked their name and expected departure date and are booked into a
site.
o Solution ERD (for the sample task)
The sample solution ERD and all assumptions made for this model are as
follows:
Note: The red and green star symbols are not part of the ERD, but are used here to indicate points about
which an assumption has been made. The optionality side (green star symbol) was decided based on the
information provided in the business scenario. For the decision on the red star symbol sections, we needed
to create our own assumptions. Three assumptions made for this sample solution are described below.
Assumptions:
1) A site can have zero or more booking records.
There can exist
and new sites that have never been booked before. In these cases, the
site record is stored in the SITE entity but no co
esponding booking record exists in the
BOOKING entity.
2) A site can only be included in one or no type.
Some sites can exist without being recorded as any type of accommodation. The site’s
ecord is stored in the SITE entity but no co
esponding TYPE record may exist.
3) An accommodation type can have zero or more co
esponding sites.
Some accommodation types are recorded with a description and price in the database (TYPE
entity), but the caravan park may not have any sites allocated to that type.
- Page 3 -
To reach a solution, the following steps must be followed:
1. Identify all necessary entities.
By reading through the scenario, you will find essential entities to be included in your model:
CUSTOMER, TYPE and SITE
2. Consider relationships between any pair of entities.
If you find a relationship between two entities, those two entities must be connected via the
elationship line and proper name (generally ‘has’) in the ERD. You also have to consider the
elationship’s cardinality (generally 1:1, 1:M or M:N or a specific number of occu
ences should be
considered; e.g., 1:M (but minimum 3, maximum 10)).
For example, for the given scenario, you found that a relationship must exist between CUSTOMER and
SITE considering that a customer may have booked (and stayed) one or many times. Here, do not forget
that a relationship is always bi-directional. This means that you should consider both the relationship
CUSTOMER to SITE, determined as 1:M, and the other direction of the relationship, from SITE to
CUSTOMER. Consider how many customers can be related to one site. Obviously, one site may have
een booked by many customers; thus the one-directional relationship from SITE to CUSTOMER should
also be 1:M. As a result, a combined bi-directional relationship between SITE and CUSTOMER would be
M:N.
In the same way, you can determine the relationship between TYPE and SITE as 1:M considering that an
accommodation type can have many co
esponding sites and one site must be allocated as one
accommodation type only.
For 1:M relationships, you do not need to consider further. For M:N relationships, however, you need
to implement the M:N relationship on your ERD by creating two separate 1:M relationships, which
forces creation of a new intermediate
idge (composite) entity. For this
idging purpose, a new entity
BOOKING is created. Consequently, the relationship between CUSTOMER and BOOKING is set as 1:M,
as is the relationship between SITE and BOOKING.
3. Identify attributes for each entity (including a
idge entity if created in the previous step).
Set PK first and then set other necessary attributes by refe
ing to the scenario outlined. Also, add FKs
to entities appropriately to ensure entities are integrated co
ectly.
4. Consider optionality for each side of the relationship.
Once a relationship has been determined in terms of cardinality (1:1, 1:M or M:N), it is time to
determine the optionality for each side of the relationship.
For example, the relationship between CUSTOMER and BOOKING is connected by 1:M relationship.
There are two optionalities to be determined: one for the CUSTOMER side and the other for the
BOOKING side. For the CUSTOMER side, you need to consider two possibilities: 1) a booking record
may have no co
esponding customer (the customer value can be ‘null’ for one booking record), or 2) a
ooking record must have the co
esponding customer value filled in (the customer value should not
e ‘null’ in every booking records). The decision should be made on the basis of the business scenario
provided. Does the scenario clearly indicate that a booking record must be created only when the
customer is identified? If yes, the optionality of the CUSTOMER side should be ‘mandatory’ and
annotated as ‘I’ on the ERD. Alternatively, can a booking record be stored without customer details? If
yes, you should put ‘o’ to denote the optional participation of the relationship.
If you are not sure, you will need to make your own assumptions for each decision about optionality.
The assumptions must be clearly described.
5. Check and present weak/strong relationships.
Finally, check if the relationship line is co
ectly presented (dotted line or solid line). For this sample
solution, the relationship between BOOKING and SITE is presented as a solid line (strong relationship
ecause the PK of SITE is used as a part of the PK of BOOKING).
- Page 4 -
Business Description (Scenario)
A company named FNQ UNITED APARTMENTS (FUA) cu
ently owns twelve (12) different
apartment complexes in many locations spread over northern Queensland areas - Cairns,
Townsville, Port Douglas, Ingham, Tully, etc.
Each apartment complex has a number of separate apartments, of varying sizes. All
apartments are leased with a six month or a year-long lease.
It is the company’s practice to hire one of the tenants to manage each apartment complex. As
managers they need to admit new tenants to the building, collect rents from existing tenants,
and finalise leases.
The manager also needs to maintain the apartments conducting any repairs, replacements, or
enovations. For most cases, the manager needs to a
ange relevant local trade person or
company to conduct necessary works. These can be billed back to the parent company (FUA).
For acting as manager, the tenant gets free rent and a stipend. The stipend varies depending
on the size of the apartment complex managed.
Each manager is expected to send a report to the FUA company head office in Townsville every
quarter. This report summarizes the occupancy rate, the total revenues in rent, the total
expenses in maintenance and repairs, etc. As FUA does not run an established database
system, cu
ently managers fill out a paper form and mail it back to head office. Many
apartment managers have complained that preparing this report is a very difficult and time-
consuming process. Also, the managers at the head-office also have expressed concerns about
the accuracy and verifiability of the reports.
To reduce these concerns and to improve the ease and efficiency with which the apartment
managers conduct their daily business, the company is proposing to develop a centralized
database that can be used by the managers to track the daily business of their apartment
uilding and to prepare their reports. You have been asked to design a database that satisfies
general business description