After completing the database management course, you have been hired for a summer position by Queensway    Carleton Hospital - Ottawa. You have to work as apart of 2 persons-team to develop a high-level Database E-R diagram for the hospital. You conduct interviews with a number of hospital administrators and staff to identify   the key entities in the hospital. After a short time, your team has identified the following possible entities:

•    Care center - a treatment center within the hospital. Examples of care centers are maternity and

emergency care. Each care center has a care center ID (identifier) and name (there are 4 centers in the hospital-shown in the excel sheet).

•    Patient - a person who is either admitted to the hospital or is registered in an outpatient program. Each patient has a patient number (identifier) name, address and tel number.

•    Physician - A member of the hospital medical staff who may admit patients to the hospital and who may administer medical treatments. Each physician has a physician ID (identifier), name, address, &Tel

Number.

•    Bed - a hospital bed that maybe assigned to a patient who is admitted to the hospital. Each bed has abed number (identifier) a room number and a care center ID.

•    Item - any medical or surgical item that maybe used in treating a patient. Each item has an item number (identifier) description and unit cost.

•    Employee - any person employed as part of the hospital staff. Each employee has an employee number and name.

•    Treatment - any test or procedure performed by a physician on behalf of a patient. Each treatment has a treatment ID that consists of two parts: treatment number and treatment name.

•     You have to add the intersection tables to your ERD

The hospital used an Excel workbook to gather and manipulate data for reporting and other planning (Given).  The team next recorded the following information concerning relationships:

•    Each hospital employee is assigned to work in one or more care centers. Each care centre has at least one employee, and may have any numbers of employees. The hospital records the number of hours per week  that a given employee works in a particular care center.

•    A given patient may or may not be assigned to abed (since some patients are outpatients). A bed may or may not be assigned to a patient.

•    A patient must be referred to the hospital by exactly one physician. A physician may refer any number of patients or may not refer any patients.  Patients are admitted on a specific date and a specific week (1 -

52).

To find the number of Stay_days in the hospital simple do the following: Dispatch Date – Admission Date.

To find the week number use the following formula as example (depend where you include the start date):

SELECT DatePart('ww',PATIENT_CARE.StartDate) AS TreatmentStartWeek………

•    Physicians may perform any number of treatments on behalf of any number of patients or may not  perform any on behalf of patients. A patient may have the treatments performed by any number of  physicians. For each treatment performed on behalf of a given patient by a particular physician, the hospital records the following information: treatment date, treatment time, and results.