1) Study the Wellmeadows Hospital case study presented in Appendix B.3 (attached).

a) In what ways would a DBMS help this organization?

b) What do you think are the main objects that need to be represented in the database

(mention 5)?

c) For each of the objects, what details (attributes) do you think need to be stored in the

database?

d) What queries do you think are required (list 5)?

2) The following tables form part of a database held in a relational DBMS: Hotel (hotelNo, hotelName, city)

Room (roomNo, hotelNo, type, price)

Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

Guest (guestNo, guestName, guestAddress)

where Hotel contains hotel details and hotelNo is the primary key; Room contains room details for each hotel and (roomNo, hoteINo) forms the primary key; Booking contains details of bookings and (hoteINo, guestNo, dateFrom) forms the primary key; Guest contains guest details and guestNo is the primary key.

Identify the foreign keys in this schema. Explain how the entity and referential integrity rules apply to these relations

3) Kindly see attached images

4) Kindly see attached images

5) Kindly see attached images

The Wellmeadows Hospital Case Study:

This case study describes a small hospital called Wellmeadows, which is located in Edinburgh. The Wellmeadows Hospital specializes in the provision of health care for elderly people. Listed in these sections is a description of the data recorded, maintained, and accessed by the hospital staff to support the management and day- to-day operations of the Wellmeadows Hospital.

B.3.1 Data Requirements

Wards

The Wellmeadows Hospital has 17 wards with a total of 240 beds available for short- and long-term patients, and an outpatient clinic. Each ward is uniquely identified by a number (for example, ward 11) and also a ward name (for example,

B.3 The Wellmeadows Hospital Case Study

| B-5

B-6 | Appendix B Other Case Studies

Orthopedic), location (for example, E Block), total number of beds, and telephone

extension number (for example, Extn. 7711).

Staff

The Wellmeadows Hospital 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.

The Wellmeadows Hospital has a Personnel Officer, who is responsible for ensur- ing that the appropriate number and type of staff are allocated to each ward and the outpatient clinic. The information stored for each member of staff includes a staff number, name (first and last), full address, telephone number, date of birth, gender, insurance number, position held, current salary, and salary scale. It also includes each members qualifications (which includes date of qualification, type, and name of institution), and work experience details (which includes the name of the organization, 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 permanent basis or temporary contract, and the type of salary payment (weekly/ monthly). An example of a Wellmeadows Hospital form used to record the details of a member of staff called Moira Samuel working in ward 11 is shown in Figure B.1.

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 effec- tively in the care of patients. The Medical Director works closely with the Charge Nurses to ensure the efficient running of the hospital.

A Charge Nurse is responsible for setting up a weekly staff rotation, 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, doc- tors and auxiliaries. Specialist staff (for example, consultants and physiotherapists) are allocated to several wards or the clinic. An example of a Wellmeadows Hospital report listing the details of the staff allocated to ward 11 is shown in Figure B.2.

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 name (first and last), address, telephone number, date of birth, gender, marital status, date registered with the hospital, and the details of the patients next-of-kin.

Patients next-of-kin

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

B.3 The Wellmeadows Hospital Case Study | B-7

Figure B.1

Wellmeadows Hospital staff form.

Local doctors

Patients are normally referred to the hospital by their local doctor. The details of local doctors are held, including their full name, clinic number, address, and telephone number. The clinic number is unique throughout the United Kingdom. An example of a Wellmeadows Hospital patient registration form used to record the details of a patient called Anne Phelps is shown in Figure B.3.

Patient appointments

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

B-8 | Appendix B

Other Case Studies

Figure B.2

The first page of the Wellmeadows Hospital report listing ward staff.

Each appointment is given a unique appointment number. The details of each patients appointment are recorded and include the name and staff number of the consultant performing 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 an appro- priate ward.

Outpatients

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

Inpatients

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), address, telephone number, date of birth, gender, marital status, the details of the patients 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 a Wellmeadows Hospital report listing the details of patients allocated to ward 11 is shown in Figure B.4.

B.3 The Wellmeadows Hospital Case Study | B-9

Figure B.3

Wellmeadows Hospital patient registration form.

Gender

Patient medication

When a patient is prescribed medication, the details are recorded. This includes the patients name and number, drug number and name, units per day, method of administration (for example, oral, intravenous (IV)), start and finish date. The medication (pharmaceutical supplies) given to each patient is monitored. An exam- ple of a Wellmeadows Hospital report used to record the details of medication given to a patient called Robert MacDonald is shown in Figure B.5.

Surgical and nonsurgical supplies

The Wellmeadows Hospital maintains a central stock of surgical (for example, syringes and sterile dressings) and nonsurgical (for example, plastic bags and

B-10 | Appendix B

Other Case Studies

Figure B.4

The first page of the Wellmeadows Hospital report listing ward patients.

Figure B.5

Wellmeadows Hospital patients medication report.

aprons) supplies. The details of surgical and nonsurgical 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 nonsurgical sup- ply. The supplies used by each ward are monitored.

Pharmaceutical supplies

The hospital also maintains a stock of pharmaceutical supplies (for example, antibiotics and painkillers). The details of pharmaceutical supplies include drug number and name, description, dosage, method of administration, quantity in

stock, reorder level, and cost per unit. The drug number uniquely identifies each type of pharmaceutical supply. The pharmaceutical supplies used by each ward are monitored.

Ward requisitions

When required, the Charge Nurse may obtain surgical, nonsurgical, and pharma- ceutical 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 informa- tion detailed on a requisition form includes a unique requisition 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 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 a Wellmeadows Hospital requisition form used to order supplies of morphine for ward 11 is shown in Figure B.6.

Suppliers

The details of the suppliers of the surgical, nonsurgical, and pharmaceutical items are stored. This information includes the suppliers name and number, address, email, and telephone and fax numbers. The supplier number is unique to each supplier.

B.3 The Wellmeadows Hospital Case Study

| B-11

Figure B.6

Wellmeadows Hospital ward requisition form.

B-12 | Appendix B Other Case Studies

B.3.2 Transaction Requirements (Sample)

The following transactions are undertaken to ensure that the appropriate informa- tion is available to enable the staff to manage and oversee the day-to-day running of the Wellmeadows Hospital. 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.

(a) Create and maintain records recording the details of members of staff (Personnel Officer).

(b) Search for staff who have particular qualifications or previous work experience (Personnel Officer).

(c) Produce a report listing the details of staff allocated to each ward (Personnel Officer and Charge Nurse).

(d) Create and maintain records recording the details of patients referred to the hospital (all staff).

(e) Create and maintain records recording the details of patients referred to the outpatient clinic (Charge Nurse).

(f) Produce a report listing the details of patients referred to the outpatient clinic (Charge Nurse and Medical Director).

(g) Create and maintain records recording the details of patients referred to a particular ward (Charge Nurse).

(h) Produce a report listing the details of patients currently located in a particular ward (Charge Nurse and Medical Director).

(i) Produce a report listing the details of patients currently on the waiting list for a particular ward (Charge Nurse and Medical Director).

(j) Create and maintain records recording the details of medication given to a particular patient (Charge Nurse).

(k) Produce a report listing the details of medication for a particular patient (Charge Nurse).

(l) Create and maintain records recording the details of suppliers for the hospital (Medical Director).

(m) Create and maintain records detailing requisitions for supplies for particular wards (Charge Nurse).

(n) Produce a report listing the details of supplies provided to specific wards (Charge Nurse and Medical Director).

3) Describe the relations that would be produced by the following relational algebra operations: (a) hotowo(price> so ( Room )) (b) Hotelndobilis= Hoomhotaino ( Hotel Room) (d) Guest > ( ( dateTotJan-200r (Booking)) (e) Hotel D Hotolhotoliko - Hocm hotowo (price>50( Room)) 4) Describe the relations that would be produced by the following tuple relational calculus expressions: (a) { H.hotelName Hotel(H) H.city = 'London' (b) { H.hotelName Hotel(H) ( (R) (Room(R) H.hotelNo = R.hotelNo R.price >50)} (c) (H.hotelName | Hotel(H) ( () ( (G) (Booking(B) Guest(G) H.hotelNo = B.hotelNo B.guestNo = G.guestNo G.guestName = 'John Smith')\} (d) \{H.hotelName, G.guestName, B1.dateFrom, B2.dateFrom | Hotel(H) Guest(G) Booking(B1) Booking(B2) H.hotelNo = B1.hotelNo G.guestNo =B1.guestNo B2.hotelNo = B1.hotelN B2 guestNo =B1. guestNo B2.dateFrom =B1. dateFrom 5) Generate the relational algebra, tuple relational calculus, and domain relational calculus expressions for the following queries: (a) List all hotels. (b) List all single rooms with a price below 20 per night. (c) List the names and cities of all guests. (d) List the price and type of all rooms at the Grosvenor Hotel. (e) List all guests currently staying at the Grosvenor Hotel. (f) List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied. (g) List the guest details (guestNo, guestName, and guestAddress) of all guests staying at the Grosvenor Hotel