ICT285: The Felicity Stillwell Hospital (FSH) is a Small Hospital located in the Southern Suburbs of Perth: Databases Assignment, MU, Singapore

Assignment Brief:

This assignment requires you to design and implement a database for the Felicity Stillwell Hospital.

The Felicity Stillwell Hospital (FSH) is a small hospital located in the southern suburbs of Perth, specialising in the health care of elderly patients. As the first phase of a complete information systems redevelopment, you have been contracted to design and implement the database that will support its data and processing requirements.

Detailed information on the data recorded, maintained and accessed by hospital staff to support the management and operations of the hospital is described below (see FSH Detailed Requirements, FSH Processing Requirements and FSH Sample Forms and Reports below), including some of the paper forms and reports currently in use, and the main transaction requirements are summarised there. This description of data and processing should be the basis for your design.

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!
• Part of understanding a system in sufficient enough detail to model well involves asking appropriate questions. If you are not sure about some detail of the case study,

What you need to do:

1. Create and submit the ERD that you are using as the basis of your implementation. (15 marks)
2. Create a data dictionary that lists at least each of the tables, their columns, domains and any other constraints that apply. (10 marks)
3. Implement the database in Oracle SQLPlus on arion.murdoch.edu.au (20 marks)
   1. All tables should be created as per your ERD; the marker will check your ERD against your tables.
   2. All entity and referential integrity constraints should be created and appropriately named.
   3. All columns should be of an appropriate domain/size and be set as required or not as appropriate.
   4. All tables should be populated with sample data that will allow the marker to test that your database fulfils the application requirements as specified and support the transactions and views listed below.
   5. 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.
4. Provide all the SQL statements that are required for the following transactions to be executed
   6052. 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 12^th July 2010. She also has a Post Graduate Diploma in Geriatrics from Enid Blyton University which as awarded on 22^nd July 2012. Prior to starting at FSH, she worked at Eastern Hospital as a Charge Nurse between 23^rd January 2011 and 1^st May 2011. From 1^st June 2011 until 23^rd Feb 2012, she worked at Eastern Hospital as a Charge Nurse.
   6053. Register a new patient (Anne Phelps) as per the details in Figure 1 below.
   6054. 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.
5. Provide VIEWS for the following (Views should be named as ViewA, ViewB etc)
   1. VIEW A: Staff currently allocated to each ward, grouped by ward.
   2. VIEW B: List of all patients referred to the hospital in the current month.
   3. 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.
   4. 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.
6. 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 (20 marks):
   1. A Patient can only be allocated to award if there are beds available.
   2. 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.

Please note the following about the marking of this assignment:

1. The marker will view your documentation and then match your documentation to your implementation. This means for example, that tables, columns and constraints should be named in your database as they are in your documentation. Relationships defined in your ERD should be defined in your database.
2. The marker will view the sample data in your tables.
3. The marker will check that the data as per the transactions in 6 above have been included.
4. The marker will execute each of the views created for 7 above.
5. AGAIN, please ensure that you GRANT the appropriate privileges on all relevant database objects to the user MARKET. If you do not do this, the marker will not be able to mark part of your assignment (and you may be awarded 0 for this section).

FSH Detailed Requirements

Wards FSH has 17 wards with a total of 240 beds available for short and long-stay patients and an outpatient clinic. Each ward is uniquely identified by a number (for example Ward 11) and also has award name (for example, Orthopaedic), location (E-Block), the total number of beds, and telephone extension number (for example, Extn 7711).

Staff

FSH has a Medical Director, who has overall responsibility for the management of the hospital. The Medical Director maintains control over the use of the hospital resources (including staff, beds and supplies) in the provision of cost-effective treatment for all patients.

FSH has a Personnel Officer who is responsible for ensuring that the appropriate number and type of staff are allocated to each ward and the outpatient clinic.

The information stored on each member of staff includes a staff number, name (first and last), full address, telephone number, date of birth, gender, tax file number (TFN), a position held, current salary, and salary scale. It also includes each staff member’s qualifications (which includes the date of qualification, type, name of institution), and work experience details (which includes the name of the organisation, position and start and finish dates).

The type of employment contract for each member of staff is also recorded including the number of hours worked per week, whether the member of staff is on a permanent or temporary contract, and the type of salary payment (weekly/monthly).

An example of an FSH form used to record the details of a member of staff called Moira Samuel working in Ward 11 is shown in Figure 1 below.

Each ward and the outpatient clinic has a member of staff with the position of Charge Nurse. The Charge Nurse is responsible for overseeing the day-to-day operation of the ward/clinic. The Charge Nurse is allocated a budget to run the ward and must ensure that all resources (staff, beds, and supplies) are used effectively in the care of patients. The Medical Director works closely with the Charge Nurse to ensure the efficient running of the hospital.

A Charge Nurse is responsible for setting up a weekly staff roster and must ensure that the ward/clinic has the correct number and type of staff on duty at any time during the day or night. In a given week, each member of staff is assigned to work an early, late or night shift.

As well as the Charge Nurse, each ward is allocated senior and junior nurses, doctors and auxiliaries. Specialist staff (for example, consultants, podiatrists) are allocated to several wards or the clinic.

An example of an FSH report listing the details of the staff allocated to ward 11 is shown in Figure 2 below.

Patients

When a patient is first referred to the hospital he or she is allocated a unique patient number. At this time, additional details of the patient are also recorded including the name (first and last name), address, telephone number, date of birth, gender, marital status, date registered with the hospital, and the details of the patient’s next-of-kin.

Patient’s Next-of-Kin

The details of the patient’s next-of-kin are recorded, which includes the next-of-kin’s full name, relationship to the patient, address, and telephone number.

Local Doctors

Patients are normally referred to the hospital for treatment by their local doctor. The details of local doctors are held including their full name, provider number, address, and telephone number. The provider number is unique. An example of an FSH patient registration form used to record details of a patient called Anne Phelps is shown in Figure 3 below.

Patient Appointments

When a patient is referred by his/her doctor to attend FSH, the patient is given an appointment for an examination by a hospital consultant.

Each appointment is given a unique appointment number. The details of each patient’s appointment are recorded and include the name and staff number of the consultant undertaking the examination, the date and time of the appointment and the examination room (for example, Room E252).

As a result of the examination, the patient is either recommended to attend the outpatient clinic or is placed on a waiting list until a bed can be found in the award.

Outpatients

The details of outpatients are stored and include the patient number, name (first and last name), address, telephone number, date of birth, gender, and the date and time of the appointment at the Outpatient Clinic.

In-patients

The Charge Nurse and other senior medical staff are responsible for the allocation of beds to patients on the waiting list. The details of patients currently placed in a ward and those on the waiting list for a place on a ward are recorded. This includes the patient number, name (first and last name), address, telephone number, date of birth, gender, marital status, the details of the patient’s next-of-kin, the date placed on the waiting list, the ward required, expected duration of stay (in days), date placed in the ward, date expected to leave the ward, and the actual date the patient left the ward, when known.

When a patient enters the ward he or she is allocated a bed with a unique bed number. An example of an FSH report listing the details of patients allocated to Ward 11 is shown in Figure 4 below.

Surgical and Non-Surgical Supplies

FSH maintains a central stock of surgical (for example, syringes, sterile dressings) and non-surgical (for example, plastic bags, aprons) supplies. The details of surgical and non-surgical supplies include the item number and name, item description, quantity in stock, reorder level, and cost per unit. The item number uniquely identifies each type of surgical or non-surgical supply. The supplies used by each ward are monitored.

Ward Requisitions

When required, the Charge Nurse may obtain surgical, non-surgical, and pharmaceutical supplies from the central stock of supplies held by the hospital. This is achieved by ordering supplies for the ward using a requisition form. The information detailed on a requisition form includes a unique registration number, the name of the member of staff placing the requisition and the number and name of the ward. Also included is the item or drug number, name, description, dosage and method of administration (for drugs only), cost per unit, quantity required, and the date ordered. When the requisitioned supplies are delivered to the ward, the form must be signed and dated by the Charge Nurse who initiated the order. An example of an FSH requisition form used to order supplies of morphine forward 11 is shown in Figure 5 below.

Suppliers

The details of the suppliers of the surgical, non-surgical, and the pharmaceutical items are stored. This information includes the supplier’s name and number, address, telephone, and fax number. The supplier number is unique for each supplier.

FSH Processing Requirements

The following high-level transactions are undertaken to ensure that the appropriate information is available to enable the staff to manage and oversee the day-to-day running of FSH. Each transaction is associated with a specific function within the hospital. These functions are the responsibility of members of staff with particular job titles (positions). The main user or group of users of each transaction is given in brackets at the end of the description of each transaction. Your design should be able to support all of these transactions.

1. Create and maintain records recording the details of members of staff (Personnel Officer)
2. Search for staff who have particular qualifications or previous work experience (Personnel Officer)
3. Produce a report listing the details of staff allocated to each ward (Personnel Officer)
4. Create and maintain records recording the details of patients referred to the hospital (all staff)
5. Create and maintain records recording the details of patients referred to a particular ward (Charge Nurse)
6. Produce a report listing the details of patients currently located in a particular ward (Charge Nurse and Medical Director)
7. Produce a report listing the details of patients currently on the waiting list for a particular ward (Charge Nurse and Medical Director)
8. Create and maintain records detailing requisitions for supplies for particular wards (Charge Nurse)
9. Produce a report listing the details of supplies provided to specific wards (Charge Nurse and Medical Director)