Description

FIT2094-FIT3171 Databases
Assignment 1B – Full Database Model and Implementation Monash Movement (MM)
FIT2094 Learning Outcomes: 2, 4, 5, 7 (see Unit Preview) FIT3171 Learning Outcomes: 2, 4, 6, 8 (see Unit Preview) Assignment weighting 15%
Assignment marked out of 100 and released as a grade out of 15

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 assignment 1A 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.
Monash Movement management have indicated the following further information which they wish considered within your design.
● Staff appointed to a centre are required to provide a mobile number, also members are required to provide a phone number they can be contacted on.
● The current types of classes Monash Movement run include Core, Gym, HIT, Pilates, Swimming, Spin, Yoga and Zumba. This list is added to regularly as new types of classes are added at their various centres.
● In the past, members have shared concerns about unwanted attention during mixed classes and have requested gendered-classes so they can focus on achieving their fitness goals in a supportive environment, for example HIT for Women. In order to ensure all members can focus on their fitness goals in a supportive environment without unwanted attention, Monash Movement would like to run a number of restricted-gender classes (for example HIT for Women). Management asks that your model allows them to specify the gender specific or unrestricted nature of each class.
(fixed) list:
○ Administration (A)
○ Cleaner (C)
○ Manager (only one per centre) (M)
○ Sales (S)
○ Security (T)
Monash Movement Sample forms
1. Class Staffing Report

2. Class Attendance ReportSample 1:

Sample 2:

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).
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 MM reports. Note that only one normalisation is required for the Class Attendance 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 tutorial 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 and then moving through 1NF, 2NF and 3NF.
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 normalisation stages (Partial at 1NF, Transitive at 2NF and Full at 3NF). You should use the same notation as depicted in the normalisation sample solutions, for example: attr1 -> attr2, attr3
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.
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 Movement 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 on this model 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 mm_schema.sql.
Submission Requirements
Assignment 1B:
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 MM documents showing all normal forms (UNF, 1NF, 2NF and 3NF). Name the file mm_normalisation.pdf
● A single page pdf file containing the final logical Model you created in Oracle Data
Modeller. Name the file mm_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 mm_oraclemodel.zip.
○ Part of the grading 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. Please view the video on Moodle under week 6 which demonstrates this process.
● A schema file (CREATE TABLE statements) generated by Oracle Data Modeller. Name the file mm_schema.sql
● The output from SQL Developer spool command showing the tables have been created. Name the file mm_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 mm_assumptions.pdf
Late submission will incur penalties at the rate of 5 marks deduction per 12 hours or part thereof.
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.

Please carefully read the documentation under “Assignment/Tutorial Task Submission” on the Moodle Assessments page.
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 MM 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 connectivity have been captured
● All/most data types and
data integrity requirements (Entity, Referential, Domain) have been correctly identified Some of the MM 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 connectivity have been captured
● Majority of data types and data integrity requirements (entity, referential, domain) have been correctly identified Few of the MM 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 connectivity have been captured
● None/few of data types and data integrity requirements (entity, referential, domain) have been correctly identified

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
● Some of drop 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. See page 11-13 of the week 6 tutorial notes. [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 of your model. If less than three pushes (for the model) showing a clear development history, a grade deduction of 10 marks applied.
Page