FIT9132代做、Databases留学生代写、SQL程序语言调试、SQL代做

日期：2019-04-27 11:11

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 11

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 11

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 11

Sample Documents

ADMISSION PROCEDURE CHARGE SHEET

Page 4 of 11

NURSE WARD ASSIGNMENT FORM

Page 5 of 11

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 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 11

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, 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 11

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 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-<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.

Page 8 of 11

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 9 of 11

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 10 of 11

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 11 of 11