FIT9132讲解、Databases留学生辅导、SQL程序语言调试、SQL讲解
解析C/C++编程|解析Haskell程序
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 11A 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 11REMEMBER 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 11Sample Documents
ADMISSION PROCEDURE CHARGE SHEET
Page 4 of 11NURSE WARD ASSIGNMENT FORM
Page 5 of 11TASKS
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 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 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
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. 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 113. 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, all 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 11Submission 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 documents A, B and C 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-.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.
Page 8 of 11Marking 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 9 of 11Marking 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 10 of 11Marking 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 11 of 11