FIT2094-FIT3171 Databases
2021 Semester Summer B
Assignment 1B – Normalisation and Logical Database Design Monash Dental (MD)
FIT2094 Learning Outcomes: 2, 3, 4 (see Unit Preview) FIT3171 Learning Outcomes: 2, 4, 5 (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 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, 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.
Further discussions with Monash Dental have revealed the points listed below:
● MD has a strict policy that patients must pay for an appointment immediately on its completion. Non private insured patients will pay 100% of the cost of the appointment. Patients who have dental insurance will have part of their account paid by the insurer
and will be required to pay the remainder (the balance), if any, of the account themselves. An account may be paid in full by the insurer depending on the policy the patient holds. Where a payment is made by an insurer, MD is only interested in recording that the payment was made by an insurer, they do not need to record which insurer made which payment. A unique payment number is assigned to each payment.
● MD records, for each provider, the charges month to date that they have generated and the charges year to date that they have generated. Both these figures are an accumulation of the actual charges made by the provider month to date and year to date.
Monash Dental have provided several forms, depicted below, which they make use of, showing some of the data they wish to record:
● a sample ‘Daily Provider Running Sheet’ which shows appointments for a specific dentist on a particular day (Figure A), and
● two sample ‘Appointment Completion’ reports (Figures B1 and B2).
MONASH DENTAL
DAILY PROVIDER RUNNING SHEET
Report Date: 10/01/2021
Provider Code: XIL002 Provider Name: Dr Jane Xilu
Provider Registration Number: DEN10242
Provider First Registered Date: 25 September 2007
Provider Registration Status: ☑ Registered ☐ Suspended ☐ Cancelled
Provider Month to Date Total Charge: $2528 Provider Year to Date Total Charge: $2528
Appointment Date Time
Patient Number
Patient Name
10/01/2021 9:15
852
Jack Wilsons
10/01/2021 9:45
924
Lily Wu
10/01/2021 10:30
1001
Mary Wilhelm
10/01/2021 11:30
1013
Kate Hills
10/01/2021 14:15
998
William Kohl
Figure A
MONASH DENTAL
APPOINTMENT COMPLETION REPORT – Sample 1
Patient Number: 1001 Patient Name: Mary Wilhelm Appointment: Date: 10th January 2021 Time: 10:30 AM
Provider Code: XIL002 Provider Name: Dr Jane Xilu Appointment Location (Room): 8 Appointment Type: Long
Service Code
Description
Standard Fee
Fee Charged
9875
Check up
54.85
54.85
2348
Extraction
134.08
165.00
1207
Filling
109.70
145.60
1091
Scale and Clean
86.50
86.50
Total Appointment Cost
$451.95
APPOINTMENT PAYMENT:
Payment No
Payment From
Amount Paid
123456
Insurer
$205.00
123457
Patient
$246.95
Figure B1
MONASH DENTAL
APPOINTMENT COMPLETION REPORT – Sample 2
Patient Number: 1002 Patient Name: Peter Green Appointment: Date: 10th January 2021 Time: 10:30 AM
Provider Code: WUM001 Provider Name: Dr Mary Wu Appointment Location (Room): 6 Appointment Type: Short
Service Code
Description
Standard Fee
Fee Charged
9875
Check up
54.85
54.85
Total Appointment Cost
$54.85
APPOINTMENT PAYMENT:
Payment No
Payment From
Amount Paid
123612
Patient
$54.85
Figure B2
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
ENSURE your name and ID are shown on every page of any document you submit. If a document is a multipage document (such as the normalisation), 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 M UST 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 to the FITGitLab server will incur a grade penalty of 10 marks.
Before submission via Moodle you must log into the web interface of the 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 following your 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 documents. Note that only one normalisation is required for the function room evaluation, 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,
b e g i n n i n g b y y o u r e p r e s e n t i n g t h e d o c u m e n t y o u a r e w o r k i n g o n a s a s i n g l e U N F f o r m .
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 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.
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 Dental 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. 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 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 the commands to spool/echo your run of the script. In generating your schema file ensure you:
○ Capturetheoutputoftherunofyourschemastatementsusingthespool command.
○ Ensureyourscriptincludesdroptablestatementsatthestartofthescript. ○ Nametheschemafileasmd_schema.sql.
Submission Requirements
Assignment 1B:
Due: Wednesday 3rd February 2021 4 PM AEDT
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 Monash Dental documents showing all normal forms (UNF, 1NF, 2NF and 3NF). Name the file md_normalisation.pdf
● A single page pdf file containing the final logical Model you created in Oracle Data Modeler. Name the file md_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 md_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 fully marked resulting in significant loss of 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 session 6 which demonstrates this process.
● A schema file (CREATE TABLE statements) generated by Oracle Data Modeller. Name the file md_schema.sql
● The output from SQL Developer spool command showing the tables have been created. Name the file md_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 md_assumptions.pdf
These files must be submitted as individual files i.e. 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.
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
Outstanding (Range HD)
Adequate (Range P – D)
Not Adequate (N)
Understand and follow normalisation methodology [30 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 MD 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 MD 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 MD 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
● 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. See pages 12 and 14 of the session 6 tutorial document [10 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.