Assignment 3

Due: March 24, 2004 at 4:30pm

1. How will your logical design in Assignment 2 (given below) be changed under each of the following circumstances:

(a) A doctor can have multiple specialties.

(b) A referral history is kept for a patient.

(c) A doctor can prescribe multiple treatments for a patient in one day.

(d) The cost per treatment varies depending on a particular physician. Mountain View Community Hospital wants to provide a complete cost analysis for a patient who was prescribed certain treatment.

(e) Employees are subdivided into two groups: nurse and staff. Only nurse has the attribute Certificate, which indicates the qualification. Only staff has the attribute Skill.

(f) Many employees can be assigned to a care center. Each care center is under the care of one nurse_in_charge who may supervise more than one care center. An employee can work in many care centers but can only be assigned to at most one care center under the supervision of a given nurse_in_charge.

2.  Refer to the composite usage map on page 252 and the logical design in Assignment 2 (given below) for the following query: "For each treatment performance in the past two weeks, list in order by treatment ID and within ID by date in reverse chronological order, the physicians performing each treatment (grouped by treatment) and the number of times this physician performed that treatment that day."

(a) Devise a query plan for the above query.

(b) Identify ways in your physical database design that can optimize the performance of the above query.

Logical design for Mountain View Community Hospital:

PATIENT(Patient#, PatientName, Paddress, Physician#)

PHYSICIAN(Physician#, PhysicianName, Specialty)

CARECENTER(ID, Name, Emp#)

ASSIGNS(ID, Emp#, Hours)

EMPLOYEE(Emp#, EmployeeName)

BED(Bed#, ID, Room#, Patient#)

PERFORMS(Patient#, Physician#, Treatment#, TreatmentName, Date, Time, Result)

TREATMENT(Treatment#, TreatmentName)

USES(Patient#, Item#, Date, Quantity)

ITEM(Item#, Description, UnitCost)

Submission Options:

1. Turned in a printed copy of your answer, clearly labeled.

2. Turned in an electronic copy of your answer as a word or zip file via the digital drop box.