FIT9132 Introduction to Databases
2019 Semester 1
Assignment 1 – Database Design – Monash Hospital (MH)
Monash hospital treats patients who are identified by a unique patient id. When a patient is admitted to the hospital, the hospital records the patients first and last name, address, date of birth and emergency contact number (if they are not already on the system). They also record the date and time of admission. The system needs to maintain a record of all admissions for a particular patient. When a patient is discharged, the date and time of their discharge for this admission is recorded.
While in the hospital patients are located in a ward. The ward is identified by a ward code. Monash Hospital wishes to record the total number of beds in each ward and the number of currently available (empty) beds. Beds located in a ward are assigned a bed number within that ward – thus, for example, each ward has a bed number 1. The bedside telephone number and bed type are also recorded. Beds are classified (their bed type) as either fixed or adjustable. Not all beds are supplied with a bedside telephone.
During a patient’s admission, they may need to be moved from one bed to another, possibly in a different ward. If this occurs the date and time the patient is assigned to the new bed/ward are recorded (a history of all such bed assignments during admission is required).
While in the hospital each patient is assigned one doctor (identified by a doctor id) as their supervising doctor. A patient’s supervising doctor may be in charge of many admissions. The hospital records each doctor’s first and last names and phone number. A doctor may have one or more specialisations (eg. Orthopaedic, Renal, etc), but not all doctors who work at the hospital have a specialisation.
During their admission, patients are prescribed procedures as part of their care by doctors. Procedures consist of tests such as “X-Rays”, “Blood Tests” etc, they also include medical procedures which might be required such as “Shoulder Replacement”. A patient may have procedures prescribed by their supervising doctor or any other doctor working in the hospital.
A procedure is identified by a procedure code. Each procedure has a name (such as “Wrist X-Ray”) and includes a description of what the procedure involves, the time required for the procedure and the current standard patient cost for this procedure. When a particular procedure is prescribed during a patient’s admission, the date and time when the procedure is carried out is also recorded. A particular procedure is completed before any further procedures are run (two procedures cannot occur simultaneously). Some procedures, such as blood tests are carried out by technicians, more complex procedure may require a doctor to perform the procedure.
If a procedure is carried out by a technician the hospital does not record the details of the technician who completed the procedure.
If a doctor carries out the procedure, the doctor who completes the procedure is recorded (the doctor who completes the procedure may be different from the doctor who prescribes it). Even if a team of doctors is involved in the procedure, only one doctor (the doctor in charge) is recorded as completing the procedure.
Monash Hospital only record the details of a procedure carried out on an admission after the procedure has been completed.
Not all admissions require a procedure to be carried out.
Procedures may require “extra” items such as syringes or swabs. Each item held in stock is assigned an item code. The item description, current stock and price are recorded. For accounting purposes, each item is assigned to a unique cost centre, such as Pharmacy, Radiography or Patient Aids. A cost centre is identified by a cost centre code and has recorded the cost centre title and managers name. The quantity of each item used in a particular procedure is recorded.
Patients are billed for the cost for the procedure itself and also any “extra” items which are used as part of a procedure. The billed charge is based on the procedure/item cost at the date and time of the procedure.
Monash Hospital also records details of its nursing staff and their allocation to work in the wards. A nurse cannot work in (be assigned to) different wards on any given day.
A nurse is identified by a unique numeric nurse id. The hospital also records the nurses first and last name. The initial date a nurse is assigned to work in a ward is recorded. Nurses may be moved between wards as staffing requirements change. When a nurse finishes an allocation with a particular ward the date they finished is also recorded. Within these changes, a nurse may return to a ward they previously worked in, if they do so, a new allocation is recorded.
REMEMBERyou must keep up to date with the Moodle assignment 1 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.
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).
ADMISSION PROCEDURE CHARGE SHEET
NURSE WARD ASSIGNMENT FORM
Please ENSUREyour name and ID are shown on every page of any document you submit. If a document is a multipage document, such as for the normalisation, please also make sure you include page numbers on every page.
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 Assignments/Ass1A and Assignments/Ass1B folders and regularly pushed to the FIT GitLab server to build this history of development.
Moodle Part A Submission: [ 5 marks]
1. Using LucidChart, prepare anINITIAL conceptual model(Entity Relationship Diagram) for Monash Hospital (MH).
○ For this initial conceptual model, include what you see as identifiers (keys) for each entity only (other attributes are not required) and all relationships.
○ Surrogate keys must not be added to this model. Connectivity and Participation for all relationships must be shown on the diagram. Participation must show both minimum and maximum values (using a single line for 1:1 is not acceptable for this unit).
This initial conceptual model must be submitted to Moodle as Assignment 1 Part A by 8 PM Monday of week 6. If this submission is not made by this date you will not be able to submit Assignment 1 Part B.
Your tutor will provide feedback and guidance based on your submitted initial model which should be integrated into your continuing work in Part B.
Moodle Part B Submission: [100 marks]
2. Performnormalisation to 3NFfor the data depicted in the sample Procedure Charge Sheet and Nurse Assignment Details.
During normalisation, you must:
○ Notadd surrogate keys to the normalisation.
○ You mustinclude all attributes (you must not removeany attribute as derivable)
○ Clearly show UNF, 1NF, 2NF and 3NF.
○ Clearly identify the Primary Key in all relations.
○ Clearly identify the partial and transitive dependencies (if they exist) in all 1NF relations. You may use a dependency diagram or alternative notation (see the normalisation tutorial sample solution for a possible alternative representation).
Your attribute names as used in your normalisation and those on your conceptual/logical models must be consistent i.e. the same name used on each for the same property.
3. Using LucidChart, prepare aFULL conceptual model(Entity Relationship Diagram) for Monash Hospital (MH).
○ For this FULL conceptual model, include what you see as identifiers (keys) for each entity, all required attributesand all relationships.This full model will be based on your feedback from your Part A submission, the normalisation above and further reading of the case study. It may be necessary to revise/update this model while developing your logical model in part 4 below.
○ Surrogate keys must not be added to this model. Participation and connectivity for all relationships must be shown on the diagram.
4. Based on your final full version of your conceptual model, prepare a logical level design for the Monash Hospital 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.
○ All entities depicted must be in 3NF
○ All attributes must be commented in the database.
○ Sequences must be used to generate numeric primary keys and check clauses must be applied to attributes where appropriate.
○ Be sure to 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. If your model is added to
GIT without a clear development history IT WILL NOT BE MARKED
5. Generate the schema for the databasein Oracle Data Modeler and use the schema to create the database in your Oracle account. Theonlyedit you are permitted to carry out to the generated schema file is to add header comment/s containing your details (student name/id), drop sequence commands and the commands to spool/echo your run of the script.
○ Capture the output of the schema statements using the spool command.
○ Ensure your script includes drop table and sequence statements at the start of the script.
Name the schema file as mh_schema.sql.
Due: Monday 8th April 2019 (Week 6) 8 PM
The following files are to be submitted and must exist in your FITGitLab server repo:
● A single page pdf file containing your initial version of your conceptual model. Name the file mh_initial_conceptual.pdf. This file must be created via File – Download As – PDF from LucidChart (do not use screen capture) and must be able to be accessed with a development history via GIT. You can create this development history by downloading and committing/pushing to GIT as you work on your model.
Due: Monday 29th April 2019 (Week 8) 8 PM
The following files are to be submitted and must exist in your FITGitLab server repo:
● A single page pdf file containing your final version of your conceptual model. Name the file mh_conceptual.pdf. This file must be created via File – Download As – PDF from LucidChart (do not use screen capture), again must be able to be accessed with a development history via GIT. .
● A pdf document showing your full normalisation of the sample Procedure Charge Sheet and Nurse Assignment Details documents showing all normal forms (UNF, 1NF, 2NF and 3NF). Name the file mh_normalisation.pdf
● A single page pdf file containing the final logical Model you created in Oracle Data Modeller. Name the file mh_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 mh_oraclemodel.zip.
○ This model must be able to be opened by your marker and contain your full model otherwise your task 4 will not be marked. For this reason, you should carefully check that your model is complete – you should take your submission archive, copy it to a new temporary folder, extract your submission parts, extract your model and ensure it opens correctlybeforesubmission.
● A schema file (CREATE TABLE statements) generated by Oracle Data Modeller. Name the file mh_schema.sql
● The output from SQL Developer spool command showing the tables have been created. Name the file mh_schema_output.txt
● A pdf document containing any assumptions you have made in developing the model or comments your marker should be aware of. Name the file mh_assumptions.pdf
Note that there are seven required files. These files must be zipped into a single zip file named a1-<yourauthcateid1>.zip e.g., a1-xyz123.zip before the assignment due date/time. Submit the a1-xyz123.zip to Moodle before the due date.
Late submission will incur penalties as outlined in the unit guide.