DIS 622-001

Business Data Systems Analysis and Design

Assignment 3

Due: March 25, 2005 at midnight

1.     How will your logical design in Task1 of 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 three groups: supervisor, nurse and staff.  Only supervisor has the attribute Years of Experience.  Only nurse has the attribute Certificate, which indicates the qualification.  Only staff has the attribute Skills.  Each staff can have many skills.

(f)    Mountain View Hospital wants to make sure its staff are well trained in their Internet and communication skills.  A number of trainers are hired to provide certain skill training for all the staff.  Each trainer is responsible for one skill training only.  A staff can receive training on many skills but can only be assigned to at most one trainer for one particular skill training at a time.

2.     Refer to the composite usage map on page 284 (page 252 if you are using the 6th edition nof the text) and the logical design in Task1 of 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 in Task1 of Assignment 2:

 

PATIENT(Patient#, PatientName, Physician#)

 

PHYSICIAN(Physician#, PhysicianName)

 

CARECENTER(ID, Name, Emp#)

Emp# identifies the designated nurse-in-charge for that care center

 

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)

 

NOTE:  Save all your work electronically as a file called ####HW3 where #### is the last four digits of your student number.  Submit your ####HW3 file through blackboard.