Reference no: EM132425134

Cessnock Community Hospital is a not for profit general hospital with 100 beds. The community is about 25,000 with a growth rate of 5 % per year. It attracts a lot of retirees and hence is planning to expand by adding another 100 beds in the next 5 years. There is also a plan to for assisted living facilities.

The hospital not only admits patients but also has an out-patient department (OPD). Laboratory procedures, deliveries and emergency services are also provided. Hospital employs full-time, part-time staff of nurses, physicians, specialists and super specialists. There are a lot of volunteers as well who help out at the hospital. Hospital provides general medical and surgical care, intensive care and many other diagnostic services.

Some of the entities identified are:

Facility, Physician, Patient, Diagnostic Unit, Ward, Staff, Prescription, Service/Drug, Medical/Surgical Item, Supply Item and Vendor Business rules governing relationships amongst these entities are:

1. FACILITY maintains one or more DIAGNOSTIC UNITs (radiology, cardiac, clinical etc)

2. FACILITY contains a number of WARDs (Gynaecology, Obstetrics, Oncology etc)

3. Each WARD is assigned a number of STAFF members (nurses, secretaries etc); a STAFF member may be assigned to multiple WARDs

4. FACILITY staffs its medical team with a number of PHYSICIANs. PHYSICIAN may be staff of more than one FACILITY.

5. PHYSICIAN treats PATIENTs, PATIENT can be treated by a number of PHYSICIANs.

6. PATIENT is diagnosed by any number of PHYSICIANs, PHYSICIAN diagnoses PATIENTs

7. PATIENT may be assigned to a WARD (except outpatients)

8. PATIENT uses MEDICAL/SURGICAL ITEMs supplied by VENDORs, VENDOR also provides SUPPLY ITEMs used for housekeeping and maintenance purposes(disinfectants, cleaning chemicals etc)

9. PHYSICIAN writes one or more PRESCRIPTIONs for a PATIENT. Each PRESCRIPTION is for one PATIENT only and a PATIENT may have many PRESCRIPTIONs

10. PRESCRIPTION can be for diagnostic test (lab test, imaging - X-ray, MRI etc )or a drug

Questions:

1. Create a data model.

2. Create an ER Diagram.

3. Convert the relation to BCNF, for this you have to show the normalization starting from 1 NF

4. Create tables, add constraints (PK, FK)

5. Create Views based on these tables to show:

a. Details of staff members working in wards

b. Patients being treated by Physicians

c. Supply Items that have reached below threshold (for this assume quantity < 10) and display results accordingly

d. Diagnosis and Prescription of patient

e. Ward's requirement of Medical/Surgical Items (Hint: Find the patient in the ward and join with prescription)

Attachment:- Case Study-Group Project.rar