FIT2094-FIT3171 Databases

2021 Semester 1

Assignment 1B - Full Database Model and Implementation

Animal Doctors (AD) - Version 1.1

FIT2094 Learning Outcomes: 2, 3, 4, 7 (see Unit Preview)

FIT3171 Learning Outcomes: 2, 4, 5, 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. 

You may change your assignment 1A conceptual model in any manner you wish as you work through assignment 1B, provided your final logical model meets both sets of requirements. These changes, if any, to your conceptual model are not required to be submitted, they simply provide the basis for your working as you proceed with the modelling.

In developing your final logical data model, composite attributes present on your conceptual model must be expanded into their component simple attributes, unless otherwise directed. If the supplementary material presented in this document does not guide you in deciding the components you may make any reasonable decision on their simple component attributes.

The Animal Doctors management have indicated:

● they do not require the clinic addresses or any phone numbers to be broken down into component attributes

● they would like to be able to easily expand:

○ the vet specialisations, as they employ further vets with new specialisations, and

○ the types of animals, as they treat a new type which they have not previously seen

● the services which are provided during a visit are identified by a service code and have a standard service cost which must be recorded as part of the database. Every visit incurs at least one service charge. During a visit (treatment of a patient - the animal) the servicing vet may vary the service standard cost depending on their professional judgement,

● the drugs which are provided during a visit have a standard cost which must be recorded as part of the database, however like services this cost may be varied by the vet for a particular visit

● when a drug is used as part of the visit (eg. an injection that the vet gives the patient in the clinic) the frequency is not recorded (frequency is only used only for drugs given to the owner to be administered to their pet following the visit eg. a twice daily antibiotic tablet), and

● management would like a count maintained, as part of the stored database data, which indicates how many times a given specialist vet has serviced a visit in a clinic which is not their home clinic.

Animal Doctor have provided several forms, depicted below, which they make use of, showing some of the data they wish to record:

● a sample 'Pet Ownership Details' which shows pets owned by a specific owner, and the owner’s preferred vet, and

● two samples 'Visit Invoice'.

Animal Doctors Sample forms

1. Pet Ownership Details

2. Visit Invoice

Sample 1:

Sample 2:

REMEMBER you must keep up to date with the Ed 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).

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 to Moodle, 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 AD reports. Note that only one normalisation is required for the Visit Invoice, 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 Animal Doctors 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. If your laptop username is a nickname please edit the legend panel to show your actual name

○ 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 ad_schema.sql.

Submission Requirements

Assignment 1B:

Due: Wednesday 28th April 2021 (Week 8) at 5 PM Melbourne Time (AEST)

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 AD documents showing all normal forms (UNF, 1NF, 2NF and 3NF). Name the file ad_normalisation.pdf

● A single page pdf file containing the final logical Model you created in Oracle Data Modeller. Name the file ad_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 ad_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. 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 ad_schema.sql

● The output from SQL Developer spool command showing the tables have been created. Name the file ad_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 ad_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 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. 

Submission status

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.

Marking Rubric

Marking Rubric continued