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.
Page 1 of 12
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.
Page 2 of 12
REMEMBER you 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).
Page 3 of 12
Sample Documents
ADMISSION PROCEDURE CHARGE SHEET
Page 4 of 12
NURSE WARD ASSIGNMENT FORM
Page 5 of 12
TASKS
Please ENSURE your 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.
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 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 an INITIAL 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 forallrelationshipsmustbeshownonthediagram. Participationmustshowboth 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. Perform normalisation to 3NF for the data depicted in the sample Procedure Charge Sheet and Nurse Assignment Details.
During normalisation, you must:
○ Not add surrogate keys to the normalisation.
○ You must 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 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.
Page 6 of 12
3. Using LucidChart, prepare a FULL conceptual model (Entity Relationship Diagram) for Monash Hospital (MH).
○ For this FULL conceptual model, include what you see as identifiers (keys) for each entity, a ll required attributes and 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 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), 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.
Page 7 of 12
Submission Requirements
Assignment 1A:
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.
Assignment 1B:
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 correctly before submission.
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-
Late submission will incur penalties as outlined in the unit guide.
Page 8 of 12
Page 9 of 12
Marking Rubric Part A
Outstanding (Range D – HD)
Adequate (Range P – C)
Not Adequate (N)
Identified the required Entities (1)
● All/most entities identified.
● All/most keys are correctly identified.
● Majority of entities identified.
● Majority of keys are correctly identified.
● None or few of entities identified.
● None or few of keys are correctly identified
Identified the required Relationships (1)
● All/most required relationships identified.
● Majority of required relationships identified.
● None/few required relationships identified.
.
Identified correct Connectivity and Participation (1)
● All/Most of depicted relationships
Connectivity and Participation correctly identified.
● Majority of depicted relationships
Connectivity and Participation correctly identified.
● None/few of depicted relationships
Connectivity and Participation correctly identified.
Able to correctly use the required notation convention and be consistent in its usage. (2)
All notations in the model are consistent and follow FIT9132 ERD standards.
Most notations in the model are consistent and follow FIT9132 ERD standards.
Few notations in the model are consistent or follow FIT9132 ERD standards.
Page 10 of 12
Marking Rubric Part B
Outstanding (Range D – HD)
Adequate (Range P – C)
Not Adequate (N)
Identify the data requirements to support an organisations operations from the supplied case study and expresses these via a database conceptual model. (50)
All MH operations are supported.
● Required number of entities are present
● All/most required attributes and keys have been captured
● Surrogate keys have not been added
● All/most required relationships have been captured
● All/most required cardinality and
participation constraints have been captured
Some MH operations are not supported.
● Majority of required entities are present
● Majority of required attributes and keys have been captured
● Surrogate keys have not been
added
● Majority of
required relationships have been captured
● Majority of required
cardinality and participation constraints have been captured
Many of the MH operations are not supported.
● None or few of the required entities are present
● None or few of the required attributes and keys have been captured
● Surrogate keys have been added
● None or few of the required
relationships have
been captured
● None or few of the
required cardinality and participation constraints have been captured
Understand and follow a database design methodology. (25)
All/majority of the design processes have been correctly followed:
● All/most Normalisation
processes are correct
● Dependency diagrams
have been provided and
match normalisation.
● ER diagram mapped to
logical model with only
minor errors/omissions.
● SQL Developer
Relational model correctly generated from the logical model
● Sequences have been created to provide numeric primary keys where required
Some of the design processes have been correctly followed:
● Majority of
Normalisation
processes are correct
● Dependency
diagrams have been provided and match normalisation in the majority of situations.
● ER diagram mapped to logical model with only a small number of errors/omissions.
● SQL Developer Relational model correctly generated from the logical model
● Sequences have been created to provide numeric primary keys where required in the majority of situations
Few of the design processes have been correctly followed:
● Significant errors
during the Normalisation processes
● Dependency diagrams not provided or have major errors
● ER diagram mapped to logical model with errors/omissions.
● SQL Developer Relational model not correctly generated from the logical model
● Sequences have not been created to provide numeric primary keys where required
Page 11 of 12
Marking Rubric continued
Outstanding (Range D – HD)
Adequate (Range P – C)
Not Adequate (N)
Understand and apply the relational model principles into practice. (15)
All relational model principles have been followed:
● All/most entities are in
third normal form.
● All/most Primary and Foreign keys are
correctly identified.
● All/most data integrity
requirements (Entity, Referential, Domain) have been correctly identified.
Most relational model principles have been followed:
● Majority of entities are
in third normal form.
● Majority of Primary
and Foreign keys are
correctly identified.
● Majority of data
integrity requirements (Entity, Referential, Domain) have been correctly identified.
Few of the relational model principles have been followed:
● None or few of the
entities are in third
normal form.
● None or few of the
Primary and Foreign keys are correctly identified.
● None or few of the data integrity
requirements (Entity, Referential, Domain) have been correctly identified.
Able to generate and modify a schema given a logical model in SQL Developer. (5)
The DDL script was executed without errors.
The DDL script was executed with errors.
Able to correctly use the required notation convention and be consistent in its usage. (5)
All notations in the model are consistent.
Some notations in the model are consistent.
Few notations in the model are consistent.
Page 12 of 12