University Murdoch University (MU)
Subject ICT 285: Databases

Assignment Brief:

In Assignment 01, you designed a database for the Felicity Stillwell Hospital. You are now expected to implement the database. There have been some changes in the requirements from Assignment 01 needed in order to support the transactions and views listed below. You will need to incorporate these changes and any changes you may have made as a result of the feedback you received on Assignment 01.

The major changes made to the requirements for the system are as follows:

  • You do NOT need to address the processing requirements listed in Transactions e, f, j, k and l in Assignment 01. This means that you do NOT need to include:
    1. Ward Requisitions/Surgical and Non-Surgical Supplies/Suppliers or any associated data in your database
    2. Anything to do with the Outpatients Clinic

An important note about SCOPE:

  • Please read the “What you need to do” section below CAREFULLY. This is the list of things on which you are being marked!
  • What you need to do:

1. Create and submit the ERD that you are using as the basis of your implementation.

2. A one-page explanation of the changes you have made to the ERD you submitted in Assignment 01.

3. Create a data dictionary that lists at least each of the tables, their columns, domains and any other constraints that apply.

Buy high-quality essays & assignment writing as per particular university, high school or college by Singapore Writers

4. Implement the database in Oracle SQLPlus on

  • All tables should be created as per your ERD; the marker will check your ERD against your tables.
  • All entity and referential integrity constraints should be created and appropriately named.
  • All columns should be of an appropriate domain/size and be set as required or not as appropriate.
  • All tables should be populated with sample data that will allow the marker to test that your database fulfills the application requirements as specified and support the transactions and views listed below.
  • SELECT, UPDATE and DELETE permissions should be GRANTED on all database objects (particularly tables and views) to the user MARKET. This is the most important. If you do not grant this permission, the marker will not be able to mark all or part of your assignment.

5. Provide all the SQL statements that are required for the following transactions to be executed:

  • Create a record for a new staff member with the following details: Moira Samuels, 49 School Road, Bedford, W.A., 6052. She is female, her date of birth is 30 May 1990, and her telephone number is 0150-456-3357. Her current position is Charge Nurse in Ward 11. Her current salary is $68760 and she is working 37.5 hours per week on a permanent basis. She is paid monthly. She has a BSc in Nursing from Curtain University which was awarded to her on 12th July 2010. She also has a Post Graduate Diploma in Geriatrics from Enid Blyton University which as awarded on 22nd July 2012. Prior to starting at FSH, she worked at Eastern Hospital as a Charge Nurse between 23rd January 2011 and 1st May 2011. From 1st June 2011 until 23rd Feb 2012, she worked at Eastern Hospital as a Charge Nurse.
  • Register a new patient (Anne Phelps) as per the details in Figure 1 below
  • Allocate the staff to Ward 11 as per Figure 2 below. You do not need to include the statements required to create the records of the staff named in the Figure.

6. Provide VIEWS for the following (Views should be named as ViewA, ViewB, etc):

  • VIEW A: Staff currently allocated to each ward, grouped by ward. (1 is too many)
  • VIEW B: List of all patients referred to the hospital in the current month.
  • VIEW C: List of all patients referred to Ward 11 in the last 12 months, including the date they were placed and the date they left, and the bed they occupied. (not supposed to delete the record)
  • VIEW D: List of local doctors (including address, contact details, and provider number) who have referred patients in the last 6 months, and the number of patients they have referred.

7. Provide an explanation (and any code you have used) as to how you would implement (or, if you were not able to successfully implement, how you would have implemented) the following constraints:

  • A Patient can only be allocated to award if there are beds available.
  • Only Staff in the role of the Personnel Officer is permitted to make updates to staff records. When a staff record is updated, the username of the user responsible must be recorded.
Felicity Stillwell Hospital
Patient  Registration Form
Patient Number:P10234
Personal Details
Family Name:Phelps
Given Names:Anne
Street Address:67 Wellmeaning Way
Gender:FDate of Birth:10/12/1955
Marital Status:MTelephone No:0131-332-4158
Next-of-Kin Details
Full Name:James Phelps
Street Address:67 Wellmeaning Way
Relationship:SpouseTelephone No:0131-332-4158
Local Doctor Details
Full Name:Dr Helen Pearson
Street Address:47 Kennedy Street
Provider No:1455784LTelephone No:0131-332-6282

Figure 1: Patient Registration Form


Felicity Stillwell Hospital
Ward Staff Allocation
Week Beginning:9/01/2013
Ward Number:11Ward Name:Orthopaedic
Location:Block ETelephone Extension:7711
Charge Nurse:Moira SamuelStaff Number:S4576
Staff Allocation Listing
Staff No:NamePositionShift
S0980Carol CummingsStaff NurseLate
S1257Morgan RussellNurseLate
S1458Robin PlevinStaff NurseEarly
S2356Amy O’DonnellConsultantNight
S3649Laurence BurnsNurseEarly

Figure 2: Report listing ward staff

Get Help By Expert

If you need professional assignment help for ITC 285: Databases course at Murdoch University, then we are your best choice. We will provide you with high-quality assignment writing online service and full support. You can rely on our services without any worries, as our team of experts has years of experience, hence they provide very effective help.

Looking for Plagiarism free Answers for your college/ university Assignments.

Hire An Assignment Writer

Add More