NU Hospital database application: ER Diagram, Relational Model, and SQL queries

You should submit two files. The first file is a Word document that contains your ER Diagram, Relational Model, and SQL queries. For the SQL queries, screenshot the input (20 SQL queries) and the output (

Mission statement: The purpose of the NU Hospital database application is to maintain the data that we generate and to provide an efficient, effective, and friendly health care provision service for our elderly patients.

Mission objectives:

To maintain data on wards.

To maintain data on staff.

To maintain data on patients and their immediate family contacts.

To maintain data on local doctors.

To maintain data on patient appointments.

To maintain data on out-patients.

To maintain data on in-patients.

To maintain data on both surgical and non-surgical supplies, and their suppliers.

To maintain data on ward requisitions.

Provide a user’s requirements specification for the global views:

Examine the following in detail and identify and compile a user specification for the Medical Director’s and the Charge Nurse’s views.  Create one conceptual data model (EER) for both of the user views. State any assumptions necessary to support your design. Of course, to complete this exercise will require that you may make some assertions about the precise requirements of each user view. Any assumption should be documented along with the view.

Medical Director:

The Director is responsible for the overall management of the hospital and must maintain control over the use of resources (including staff, beds, and supplies) in the provision of cost-effective treatment for all patients.

The hospital is composed of many wards. Each ward is managed by a Charge Nurse. The information to be held on each ward includes the ward name, number (e.g. W1), phone number, location (e.g. Block E), number of beds, and the name of the Charge Nurse.
Each ward is allocated staff including (e.g. Charge nurse, senior and junior nurses, doctors, consultants, auxiliaries).
The hospital maintains stock of general supplies. These supplies include both surgical (e.g. syringe, bandages) and non-surgical (e.g. plastic bags, aprons). The details of the general supplies includes item number and name, item description, quantity in stock, re-order level, cost per unit. The hospital also maintains a stock of pharmaceutical supplies (e.g. antibiotics, pain killers). The details of pharmaceutical supplies includes drug number and name, description, dosage, quantity in stock, re-order level, cost per unit.
The details of the suppliers of the surgical, non-surgical and pharmaceutical items are stored. The information stored includes the supplier name and number, address, phone and fax number. Assume that each supplier provides a unique subset of supplies (i.e. no two suppliers provide the same item).
Patients are normally referred to the hospital for treatment by their local doctor. The details of local doctors are stored including their name, clinic number, address, and phone number. Some local doctors are also on staff at the hospital, but many maintain only their private practice.
The details of patients referred to the hospital includes the patient number, name (first and last name), address, phone number, date of birth, marital status, and immediate family contacts (name, relationship, address, and phone number).
When a patient is referred by his/her doctor to attend the hospital, the patient is given an appointment and is examined by a consultant. The details of the appointment are stored including the consultant’s name and number, appointment number, date and time, and examination room (e.g. Room E112). As a result of the examination, the patient is either recommended to attend the outpatient clinic or placed on a waiting list until a bed can be found in a particular ward.
The details of outpatients are stored. The information stored includes the patient details as stated earlier (see 6) and the date and time of the appointment at the outpatient clinic.
The details of patients currently placed in a ward and those on the waiting list for a place on a ward are stored. The information stored includes the patient details as stated earlier (see 6) and the date placed on waiting list, ward required, expected duration of stay, date placed in the ward, and date left the ward.

Charge Nurse

The Charge Nurse has overall responsibility for the management of a single ward. 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 Charge Nurse and other senior medical staff are responsible for the allocation of beds to patients on the waiting list.

The information to be held on each ward includes the details of staff allocated to each ward including the staff number, name, address, phone number, position, and number of hours worked per week and shift (e.g. early, late).
The information stored on each patient on the waiting list includes the patient number, name (first and last name), address, phone number, date of birth, marital status, immediate family contacts, date placed on waiting list, required ward, date placed in ward, expected duration of stay, and date left ward.
When a patient enters the ward they are allocated a bed with a unique bed number. Each patient is prescribed medication and the details of this medication includes the patient number, staff number (of the prescribing staff doctor), drug number and name, units per day, start and finish date. The medication (pharmaceutical supplies) given to each patient is monitored.
Staff are allocated to work in wards, as required. The Charge Nurse of each ward is responsible for allocating nursing staff to each ward. This ensures that the correct complement of staff are on duty for each shift. It is the responsibility of the Charge Nurse to create a staff schedule on a monthly basis. This schedule includes the date, shift (early, late, night), and the staff on duty for that date.

In addition, a patient requiring specialist care can be assigned a specific nurse during a particular shift. This requires the Charge Nurse to maintain a list of specialties for her staff.

When required the Charge Nurse may obtain surgical, non-surgical and pharmaceutical supplies from the central stock of supplies held by the hospital. The information to be stored includes the requisition number, staff name and number, ward number, item number (or drug number), quantity required, date ordered and date received.

), and include both in the word document. The second file is a notepad that only contains your table create commands, insert commands, and your SQL queries (again, but without the output this time