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:
¡ð Capturetheoutputoftherunofyourschemastatementsusingthespoolcommand. ¡ð Ensureyourscriptincludesdroptablestatementsatthestartofthescript.
¡ð Nametheschemafileasmau_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
Outstanding (Range HD)
Adequate (Range P – D)
Not Adequate (N)
Understand and follow normalisation methodology [35 marks]
All/majority of the normalisation steps have been correctly followed:
¡ñ All/most normalisation
processes are correct
¡ñ Dependency diagrams
have been provided and
match normalisation.
¡ñ Normalisation result is
correctly integrated into logical model
Some of the normalisation steps have been correctly followed:
¡ñ Majority of
Normalisation
processes are correct
¡ñ Dependency diagrams
have been provided and match normalisation in the majority of situations.
¡ñ Majority of normalisation result is correctly integrated into logical model
Few of the normalisation steps have been correctly followed:
¡ñ Significant errors during
the Normalisation
processes
¡ñ Dependency diagrams
not provided or have
major errors
¡ñ Normalisation result is
not correctly integrated into logical model
Identify the data requirements to support an organisation’s operations from the supplied case study and express these via a database logical model.
[50 marks]
All MAU operations are supported:
¡ñ All/most required
relations identified.
¡ñ All relations are in 3NF
¡ñ All/most required
relationships have been captured by placing FK in correct relation
¡ñ All/most required cardinality and
participation have been
captured
¡ñ All/most data types and
data integrity requirements (Entity, Referential, Domain) have been correctly identified
Some of the MAU operations are supported:
¡ñ Majority of relations
identified.
¡ñ Majority of relations are
in 3NF
¡ñ Majority of required
relationships have been captured by placing FK in correct relation
¡ñ Majority of required cardinality and
participation have been
captured
¡ñ Majority of data types
and data integrity requirements (entity, referential, domain) have been correctly identified
Few of the MAU operations are supported:
¡ñ None/few of relations
identified.
¡ñ Majority of relations are
not in 3NF
¡ñ None/few required
relationships have been captured. Majority of FKs are placed in incorrect relations.
¡ñ None/few of required cardinality and
participation have been
captured
¡ñ None/few of data types
and data integrity requirements (entity, referential, domain) have been correctly identified
Page 8 of 9
Marking Rubric continued
Outstanding (Range HD)
Adequate (Range P – D)
Not Adequate (N)
Able to generate a relational model and schema given a logical model in SQL Developer. [ 10 marks]
All/majority of the schema generation processes have been correctly followed:
¡ñ SQL Developer
Relational model correctly generated from the logical model
¡ñ All drop commands, database comments and spool command included
¡ñ No ¡°extra¡± edit in schema file
¡ñ The DDL script was executed without errors.
Some of the schema generation processes have been correctly followed:
¡ñ SQL Developer
Relational model correctly generated from the logical model
¡ñ Someofdrop commands, database comments and spool command included
¡ñ The DDL script was executed without errors.
Few of the schema generation processes have been correctly followed:
¡ñ SQL Developer Relational model not correctly generated from the logical model
¡ñ There is ¡°extra¡± edit (other than identity information and set echo/spool commands) in schema file
¡ñ The DDL script was executed with errors.
Able to correctly use the required notation convention and be consistent in its usage.
[5 marks]
All notations in the model are consistent and follow FIT2094-FIT3171 Logical Model standards.
Most notations in the model are consistent and follow FIT2094-FIT3171 Logical Model standards.
Few notations in the model are consistent or follow FIT2094-FIT3171 Logical Model standards.
Able to correctly push all files to FITGitLab server with a development history of at least three pushes.
If less than three pushes showing a clear development history a grade deduction of 10 marks applied.
Page 9 of 9