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.

4. Implement the database in Oracle SQLPlus on sphinx.murdoch.edu.a

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

Figure 1: Patient Registration Form

Figure 2: Report listing ward staff