FIT2094-FIT3171 Databases
2020 Semester 1
Assignment 1B - Full Database Model and Implementation - Monash
Art Union (MAU)
Assignment weighting 15% - Lecturer in Charge: Dwi Rahayu
This task continues the work you have started in assignment 1A by refining/extending the model you
developed and implementing it as a set of tables under your Monash Oracle database account.
Since this is an ongoing development process based on your assignment 1A submission and marker
feedback, you must ensure that your submission and the marker feedback remains
confidential and is only seen by yourself and the FIT2094-FIT3171 teaching staff .
Assignment 1B's brief must be read in conjunction with the assignment 1A brief - i.e. your final
model must encompass both sets of requirements.
You may modify your assignment 1A conceptual model in any manner you wish as you work through
assignment 1B, provided your final model meets both sets of requirements.
In developing your final logical data model, composite attributes present on your conceptual model
must be expanded into their component simple attributes. If the supplementary material presented in
this document does not guide you in deciding the components you may make any reasonable
decision on the component simple component attributes.
To simplify tracking the status of an artwork, MAU would like to have recorded the status of each
artwork at the current point in time. MAU would like to record five possible values for this status:
● in MAU storage at the MAU central warehouse
● in transit (being shipped to/from a gallery)
● on display by a gallery
● sold, or
● returned to the artist
These five possible values are fixed and will not need to be extended.
Any artwork located in the MAU central warehouse is not available for sale. Artwork can only be sold
by a gallery from where it is on display.
Page 1 of 9
MAU has provided the following documents which they make use of in managing their business:
● MAU ArtWork Status History Report - this reports the status of a given artwork at a particular
point in time, and
● MAU Gallery Display Report - this reports the MAU artwork displayed in a given gallery over
time
Sample MAU ArtWork Status History Reports
Sample 1:
Sample 2:
Page 2 of 9
Sample MAU GalleryDisplay Report
Note in this report only partial data has been shown, the … represent rows of similar data
which have been excluded
REMEMBER you must keep up to date with the Moodle assignment 1B forum where further
clarifications may be posted (this forum is to be treated as your client).
Please be careful to ensure you do not post anything which includes your reasoning, logic
or any part of your work to this forum, doing so violates Monash plagiarism/collusion rules
and has significant academic penalties.
You are free to make assumptions if needed however they must align with the details here
and in the assignment forums and must be clearly documented (see the required
submission files).
Page 3 of 9
TASKS
Please ENSURE your name and ID are shown on every page of any document you submit . If a
document is a multipage document, please also make sure you include page numbers on every
page.
GIT STORAGE
All working files, as you work on this assignment task, must be stored in GIT and must show a
clear history of development . Your work for this task MUST be saved in your local repo in your
Assignment 1B folder and regularly pushed to the FIT GitLab server to build this history of
development. Any submission with less than three pushes for your model will incur a grade penalty
of 10 marks (a 10 mark deduction).
Before submission via Moodle you must log into the web interface of the FIT GitLab server and
ensure your files are present.
All source documents must be available in your FIT GitLab server account and must not be
modified in any manner after you have made your Moodle submission. For example with your
normalisation you are required to submit a PDF copy of your work, however your source documents
(MS Word, Pages or an MS Word export from Google Docs) must exist in your FIT GitLab
account for your work to be acceptable for marking.
Task to complete:
1. Perform normalisation to 3NF for the data depicted in the sample MAU reports. Note that
only one normalisation is required for the ArtWork Status History Report, you have been
provided with two samples so you can appreciate some of the variety which occurs.
The approach you are required to use is the same approach as shown in the normalisation lab
solution. The normalisation must be carried out form by form, beginning by you representing
the document you are working on as a single UNF form .
During normalisation, you must:
○ Not add surrogate keys.
○ Include all attributes (you must not remove any attribute as derivable)
○ Clearly show UNF, 1NF, 2NF and 3NF.
○ Clearly identify the Primary Key in all relations.
○ Clearly identify all dependencies at the various nomalisation stages (Partial at 1NF,
Transitive at 2NF and Full at 3NF). You may use a dependency diagram or alternative
notation (see the normalisation tutorial sample solution for a possible alternative
representation). If none exist you must note this by stating: No partial dependencies
present and/or No transitive dependencies present
○ If required, carry out attribute synthesis.
The attribute names used in your normalisation and those on your subsequent logical model
must be the same.
Page 4 of 9
2. Based on your assignment 1A conceptual model, your markers feedback, your reading of this
case study and the normalisations you carried out in step 1 above, prepare a logical level
design for the Monash Art Union 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. Your logical
model must not show datatypes.
○ All relations depicted must be in 3NF
○ You are required to add at least one surrogate key to your design (you are free to
select the most appropriate relation to make this change in)
○ All attributes must be commented in the database (ie. the comments must be part of
the table structure, not simply comments in the schema file).
○ Check clauses/look up tables must be applied to attributes where appropriate.
○ You MUST include the legend as part of your model.
○ Note that your GIT repository must clearly indicate your development history with
multiple commits/pushes as you work on your model.
3. Generate the schema for the database in Oracle Data Modeler and use the schema to
create the database in your Oracle account. The only edits you are permitted to carry out to
the generated schema file is to add header comment/s containing your details (student
name/id) and the commands to turn on and off spool/echo for your script. Ensure you:
○ Capture the output of the run of your schema statements using the spool command.
○ Ensure your script includes drop table statements at the start of the script.
○ Name the schema file as mau_schema.sql.
Page 5 of 9
Submission Requirements
Assignment 1B:
Due: Wednesday 13th May 2020 (Week 8) 5 PM
The following files are to be submitted and must exist in your FITGitLab server repo, along with the
source documents from which they were generated :
● A pdf document showing your full normalisation of the sample MAU documents showing all
normal forms (UNF, 1NF, 2NF and 3NF). Name the file mau_normalisation.pdf
● A single page pdf file containing the final logical Model you created in Oracle Data Modeller.
Name the file mau_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 mau_oraclemodel.zip.
○ Part of the assessment of your submission will involve your marker extracting your
model from this zip, opening it in SQL Developer Data Modeller, engineering to a new
Relational model and from this your marker will generate a schema which will then be
compared with your submitted schema (they must be the same for your schema to be
accepted). For this reason your model must be able to be opened by your marker
and contain your full model otherwise your task 2 and 3 will not be able to be
marked resulting in the loss of 60 marks . For this reason, you MUST carefully check
that your model is complete - ensure you 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 mau_schema.sql
● The output from SQL Developer spool command showing the tables have been created.
Name the file mau_schema_output.txt
● A pdf document containing any assumptions you have made in developing the model or
comments your marker should be aware of. If you have made no assumptions submit the
document with a single statement saying "No assumptions made". Name the file
mau_assumptions.pdf
These files must be submitted as individual files ie. you must upload to Moodle six separate files
as named above (the six files must not be zipped into a single archive) before the assignment due
date/time.
Late submission will incur penalties as outlined in the unit guide (5 marks deduction per 12
hours or part thereof) .
Page 6 of 9
Please note we cannot mark any work on the FITGitLab Server , you need to ensure that you
submit correctly via Moodle since it is only in this process that you complete the required student
declaration without which work cannot be assessed .
It is your responsibility to ENSURE that the files you submit are the correct files - we strongly
recommend after uploading a submission, and prior to actually submitting in Moodle, that you
download the submission and double-check its contents.
Your assignment MUST show a status of "Submitted for grading" before it will be marked.
If your submission shows a status of "Draft (not submitted)" it will not be assessed and will incur
late penalties after the due date/time .
Please carefully read the documentation under "Assignment/Tutorial Task Submission" on the
Moodle Assessments page.
Page 7 of 9
Marking Rubric