Problem
Scenario
Westlake Research Hospital A hospital is conducting a double-blind test of a new depression drug. It will involve about 20 doctors and about 400 patients. Half of the patients will get the new drug and half will get traditional Prozac. Neither the doctors nor the patients will know who is getting which drug. Only two test supervisors will know who is getting what. The test will last about 18 months. Each doctor will see 20 patients initially, though it is expected some patients will drop out over time. Each patient will be coming in twice a month for a checkup and interviews with their doctor. The drugs will be dispersed in a generic bottle by the two supervisors one of whom is a pharmacist.
To track this study, the hospital will need a database. It will need to track patients' information from their first screening through each of their interviews. In particular, they are looking at whether the patient seems more depressed or less, what their appetite is like, are they sleeping, and what kind of activities they are engaged in, if any. Also, they will be looking for specific physical side effects such as rashes, high blood pressure, irregular heart rhythms, or liver or kidney problems. Doctors need to be able to see their own patient's information, but not that of any other doctor's patients. They also need to be able to enter blood pressures, blood test results, the depression indicators, their own notes, and so on for each session. Patients should be able to see their own medical profile, the doctor's notes, and nothing else. Only the two researchers should be able to see everything: all patient information, all doctors' notes, and which drug each patient is being given. There is always some danger of spying by other companies interested in similar drugs, so in addition to the security of the blind test, the database needs to be secured against outside intrusion as well.
Task
A. Define the entities, attributes, and relations that exist between them.
B. Create a logical model using crow's feet notation in Visio.
C. Identify the key fields for each entity and the foreign keys.
D. Analyze the diagram, identify which role (i.e., domain, linking, lookup, or weak) each entity plays in your database.