FIT2094 Databases
2018 Semester 2
Assignment 1 – Database Design – Monash Cabins (MC)
Monash Cabins is a chain of resorts (holiday destinations) located around Australia. At each of
these resorts, MC provides cabin-based accommodation for its guests – any given resort consists
of several independent cabins. A resort is located in a particular town. MC maintain details of
points of interest (POI) in the local area that guests might wish to visit. Each POI is associated with
only one town.
For each town in which a resort or point of interest is located MC records the town name, the state
the town is located in, the latitude and longitude of the centre of the town, the average summer and
winter day temperatures, the town’s elevation above sea level and its population. MC only records
details of towns which have a resort or point of interest. MC does not record a town’s postcode
since they are only interested in it as a location, not as a postal location.
For points of interest MC records the street and town in which it is located, the name of the point of
interest (eg. Merimbula Aquarium), a brief description of the point of interest, its opening hours (if
appropriate), and the type of point of interest (café, museum, restaurant, national park etc). The
opening hours are recorded as the time at which the POI opens and the time at which it closes,
only one set of these times are recorded (ie the POI is assumed to open and close at the same
time all year). Each POI is only classified as one type of point of interest, for example, a cafe in a
national park will be classified as a cafe. A scan of the potential MC data has indicated that some
towns contain two POIs of the same name.
Each resort is assigned a unique resort id. MC has several resorts in some towns and only a single
resort in others depending on the location’s popularity. Each resort has a name (eg. Merimbula
Beach Cabins). A resort’s street address and town name are recorded. MC also records for each
resort the star rating of the resort, which is determined from the average of all guest reviews,
whether guests may bring their pet dog/s while staying at the resort and if the resort manager lives
at the resort. Each resort has a single manager. MC assigns a manager id to each manager and
records the manager’s name, postal address (including town and postcode) and the manager’s
contact phone number. Some managers live on site (ie. at the resort); others live at their own
private residence, however, MC does not need to record the manager’s residential address, only
their postal address. Where a location has several resorts, a manager may manage several
different resorts.
Page 1 of 8
Each resort consists of a number of cabins – the cabins are numbered starting from cabin 1 at
each resort. MC records how many bedrooms there are in each cabin, the sleeping capacity of the
cabin (how many people it can sleep) and a description of the cabin to provide potential guests
with some details to assist their decision making.
MC guests, those staying at the resorts, are assigned a unique guest number when they first
register with MC. The guest name, postal address, country, email and contact phone number are
recorded. A guest makes a booking with MC by choosing the resort they wish to stay at and the
cabin they wish to stay in. Guests are required to provide the date they wish to book from and the
date they wish to stay to. They must also supply MC with the number of adults, the number of
children and the number of pets (if applicable) who will be staying. MC record the total charge for
the particular booking, based on the cabin rate and the number of days of the guests stay.
Guests are offered the opportunity to provide a review of the resort – they are not required to do so,
but if they do, they provide a comment and a rating from 1 (poor) to 5 (outstanding). These reviews
are treated as general reviews of the resort rather than being related to a particular cabin or stay. A
guest may complete many reviews of a particular resort during their stay. The review may also be
made after the guest has left the resort. The date of the review is recorded (a guest is not permitted
to complete two reviews on the same day).
The rates charged for a cabin depend on the cabin itself (some cabins have special features such
as a spa) and the time of the year in which the guest is staying. For example, for seaside resorts,
the highest rates are charged in the peak summer period (eg. Jan – Feb) when demand is at its
highest. The rate for each cabin is recorded for each of these charge periods – rate periods may
vary between different locations. Where a booking spans several rate periods, the booking is
charged at the rate which applies on the first day of the booking.
When a guest vacates a cabin, MC use contract cleaners to clean the cabin. MC maintain a
running sheet, for each resort, to record cleaning activity (a small sample of this is shown below):
Document A
Page 2 of 8
A cleaning contractor is assigned a unique contractor number when first taking up work with MC.
Contract cleaners may work as casual staff or fixed term staff. Fixed-term staff sign a contract to
clean for a set period of time such as three or six months. Casual cleaners are not locked into any
fixed period to clean, they are available on a weekly basis as work is available or suits them.
Casual cleaners are contacted when work is available to clean a particular cabin and may accept
or reject the job. Contract cleaners may shift between these two modes depending on what suits
them better.
MC maintain a record of the contract history for all cleaners ( a small sample of this record is
shown below):
Documents B and C
Page 3 of 8
TASKS
Please ENSURE your name and ID are shown on every page of any document you submit. If a
document is a multipage document, such as for the normalisation, please also make sure you
include page numbers on every page.
Moodle Part A Submission:
1. Using LucidChart, prepare an INITIAL conceptual model (Entity Relationship Diagram) for
Monash Cabins (MC).
○ For this initial conceptual model, include what you see as identifiers (keys) for each
entity only (other attributes are not required) and all relationships.
○ Surrogate keys must not be added to this model. Participation and connectivity
for all relationships must be shown on the diagram.
This initial conceptual model must be submitted to Moodle as Assignment 1 Part A
by 5 PM Monday of week 6. If this submission is not made by this date you will not be
able to submit Assignment 1 Part B.
No marks are awarded for this submission, your tutor will provide feedback and guidance
based on your submitted initial model which should be integrated into your continuing work
in Part B (which is graded).
Moodle Part B Submission:
2. Perform normalisation to 3NF for the data depicted in the sample Cleaning Running Sheet
(Document A) and the sample Contract History (Documents B and C – note that documents
B and C are two samples of the same document).
During normalisation, you must:
○ Not add surrogate keys to the normalisation.
○ You should include all attributes (not remove any attribute as derivable)
○ Clearly show UNF, 1NF, 2NF and 3NF.
○ Clearly identify the Primary Key in all relations.
○ Clearly identify the partial and transitive dependencies (if they exist) in all 1NF
relations. You may use a dependency diagram or alternative notation (see the
normalisation tutorial sample solution for a possible alternative representation).
Your attribute names as used in your normalisation and those on your conceptual/logical
models must be consistent i.e. the same name used on each for the same property.
3. Using LucidChart, prepare a FULL conceptual model (Entity Relationship Diagram) for
Monash Cabins (MC).
○ For this FULL conceptual model, include what you see as identifiers (keys) for each
entity, all required attributes and all relationships. This full model will be based on
your feedback from your Part A submission, the normalisation above and further
reading of the case study. It may be necessary to revise/update this model while
developing your logical model in part 4 below.
○ Surrogate keys must not be added to this model. Participation and connectivity
for all relationships must be shown on the diagram.
Page 4 of 8
4. Based on the final full version of your conceptual model, prepare a logical level design for
the Monash Cabins database.
○ The logical model must be drawn using the Oracle Data Modeler. The information
engineering or Crow’s foot notation must be used in drawing the model.
○ All entities depicted must be in 3NF
○ All attributes must be commented in the database.
○ Sequences must be used to generate numeric primary keys and check clauses
must be applied to attributes where appropriate.
○ Be sure to include the legend as part of your model.
5. Generate the schema for the database in Oracle Data Modeler and use the schema to
create the database in your Oracle account. The only edit you are permitted to carry out to
the generated schema file is to add header comment/s containing your details (student
name/id) and drop sequence commands.
○ Capture the output of the schema statements using the spool command.
○ Ensure your script includes drop table and sequence statements at the start of the
script.
○ Name the schema file as mc_schema.sql.
Page 5 of 8
Submission Requirements
Due: Monday 27th August (Week 6) 5PM
The following files are to be submitted:
● A single page pdf file containing your initial version of your conceptual model. Name the
file mc_initial_conceptual.pdf. This file must be created via File – Download As – PDF
from LucidChart, do not use screen capture.
Due: Wednesday 12th September (Week 8) 5PM
The following files are to be submitted:
● A single page pdf file containing your final version of your conceptual model. Name the file
mc_conceptual.pdf. This file must be created via File – Download As – PDF from
LucidChart, do not use screen capture.
● A pdf document showing your full normalisation of documents A, B and C showing all
normal forms (UNF, 1NF, 2NF and 3NF). Name the file mc_normalisation.pdf
● A single page pdf file containing the final logical Model you created in Oracle Data Modeller.
Name the file mc_logical.pdf. This pdf must be created via File – Data Modeler – Print
Diagram – To PDF File from within SQL Developer, do not use screen capture.
● A zip file containing your Oracle data modeler project (in zipping these files be sure you
include the .dmd file and the folder of the same name). Name the file mc_oraclemodel.zip.
○ This model must be able to be opened by your marker and contain your full model
otherwise your task 4 will not be marked. For this reason, you should carefully
check that your model is complete – you should take your submission archive, copy it to
a new temporary folder, extract your submission parts, extract your model and ensure it
opens correctly before submission.
● A schema file (CREATE TABLE statements) generated by Oracle Data Modeller. Name the
file mc_schema.sql
● The output from SQL Developer spool command showing the tables have been created.
Name the file mc_schema_output.txt
● A pdf document containing any assumptions you have made in developing the model or
comments your marker should be aware of. Name the file mc_assumptions.pdf
Note that there are seven required files. These files must be zipped into a single zip file named
a1-
a1-xyz123.zip to Moodle before the due date.
Late submission will incur penalties as outlined in the unit guide.
Page 6 of 8
Marking Rubric
Outstanding (Range D – HD) Adequate (Range P – C) Not Adequate (N)
Identify the data
requirements to
support an
organisations
operations from the
supplied case study
and expresses
these via a
database
conceptual model.
(50)
All MC operations are
supported.
● Required number of
entities are present
● All/most required
attributes and keys
have been captured
● Surrogate keys have
not been added
● All/most required
relationships have
been captured
● All/most required
cardinality and
participation
constraints have
been captured
Some MC operations are
not supported.
● Majority of
required entities
are present
● Majority of
required attributes
and keys have
been captured
● Surrogate keys
have not been
added
● Majority of
required
relationships have
been captured
● Majority of
required
cardinality and
participation
constraints have
been captured
Many of the MC
operations are not
supported.
● None or few of the
required entities
are present
● None or few of the
required attributes
and keys have
been captured
● Surrogate keys
have been added
● None or few of the
required
relationships have
been captured
● None or few of the
required
cardinality and
participation
constraints have
been captured
Understand and
follow a database
design
methodology.(25)
All/majority of the design
processes have been
correctly followed:
● All/most Normalisation
processes are correct
● Dependency diagrams
have been provided and
match normalisation.
● ER diagram mapped to
logical model with only
minor errors/omissions.
● SQL Developer
Relational model
correctly generated from
the logical model
● Sequences have been
created to provide
numeric primary keys
where required
Some of the design
processes have been
correctly followed:
● Majority of
Normalisation
processes are correct
● Dependency
diagrams have been
provided and match
normalisation in
majority of situations.
● ER diagram mapped
to logical model with
only small number of
errors/omissions.
● SQL Developer
Relational model
correctly generated
from the logical model
● Sequences have been
created to provide
numeric primary keys
where required in the
majority of situations
Few of the design
processes have been
correctly followed:
● Significant errors
during the
Normalisation
processes
● Dependency diagrams
not provided or have
major errors
● ER diagram mapped
to logical model with
errors/omissions.
● SQL Developer
Relational model not
correctly generated
from the logical model
● Sequences have not
been created to
provide numeric
primary keys where
required
Page 7 of 8
Marking Rubric continued
Outstanding (Range D – HD) Adequate (Range P – C) Not Adequate (N)
Understand and
apply the relational
model principles
into practise. (15)
All relational model
principles have been
followed:
● All/most entities are in
third normal form.
● All/most Primary and
Foreign keys are
correctly identified.
● All/most data integrity
requirements (Entity,
Referential, Domain)
have been correctly
identified.
Most relational model
principles have been
followed:
● Majority of entities are
in third normal form.
● Majority of Primary
and Foreign keys are
correctly identified.
● Majority of data
integrity requirements
(Entity, Referential,
Domain) have been
correctly identified.
Few of the relational
model principles have
been followed:
● None or few of the
entities are in third
normal form.
● None or few of the
Primary and Foreign
keys are correctly
identified.
● None or few of the
data integrity
requirements (Entity,
Referential, Domain)
have been correctly
identified.
Able to generate
and modify a
schema given a
logical model in
SQL Developer. (5)
The DDL script was
executed without errors.
The DDL script was
executed with errors.
Able to correctly
use the required
notation convention
and be consistent in
its usage. (5)
All notations in the model
are consistent.
Some notations in the
model are consistent.
Few notations in the
model are consistent.
Page 8 of 8