Westwood Research Hospital is conducting a double blind test of a new depression drug. It will involve about 20 doctors and approximately 800 patients. Half of the patients will get the new drug and half will get traditional drug that has been tested and on the market for several years. 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 two years. Each doctor will see 40 patients initially, though it is expected some patients will drop out of the study 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 initial screening through each of their follow up visits. 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 will be able to view their own patients information, but not that of any other doctors 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 follow-up session. Patients will not be given any information until the study has concluded. In addition to the security of the blind test, the database needs to be secured against outside intrusion as well.

The doctors and directors of the study were reluctant to allow any observations with an actual patient, but one of the doctors, Dr. Cooper, explained the process of a patient visit:

The first thing done in the morning is review the days appointments. The attending nurse sends an email with a table of the patients and the times of the appointments. The list is printed and then the files of the individual patients for review are retrieved from the cabinet. Their medical histories and notes from previous visits are reviewed. She then makes notes on a notepad for each patient.

When the first patient arrives, the doctor greets them and asks how they are doing. This is a casual conversation, but notes any complaints or signs of deepening depression. She then goes through the Patient Visitation Form. The nurse has already taken the patients blood pressure, heart rate, and weight. If the blood pressure is high or if there has been a dramatic change in one of the measures since the last visit, she asks the patient about it. She then asks about their depression. She may not use the exact words on the form, but is careful to cover all of the items. She then records a few notes in a notebook while the patient talks, but waits until the patient leaves to write most of the summary. She also waits until the end to make her recommendation to continue or to drop the patient from the study; this is a judgement call. The date of the visit is recorded.

Most of the time, its in the patients interest to continue with the study, but if the patient is showing signs of significant side effects or if the patient seems in eminent danger of doing harm to himself or herself, the recommendation would be that the patient be dropped and given alternative or more aggressive treatment. Some patients are dropped because of a lack of participation, not showing up for scheduled appointments or are not in compliance with taking the medication. The doctor always worries that such patients were possibly the most depressed and needed the most help.

Data required:

Doctors (Dr#, DFName, DLName, Specialty, Yrs in Practice, # of patients for the study)

Patients (Patient Study#, all info on initial medical history form)

Patient Visit Information (see patient visit form)

Doctors availability for appointments (Dr#, day, time can use military time)

Appointments (Dr#, Patient Study #, date)

Symptoms (see patient visit form)

Supervisors (S#, SLName)

Drugs Dispensed (Patient Study#, drug given) this data is visible only to the supervisors

Study Drug (SD#, SDName)

Any other data necessary to capture all information of the study (Nurses)

Review the business rules described in your project files and create an EERD illustrating the entities/relationships needed to support your database design. Use any graphical tool you prefer but be consistent. No gridlines - keep it clean. Make sure to follow all rules of EER diagramming. If you see additional files (e.g., patient visit form, initial medical history form), you need to consider including the data from those forms into your database model as well. Include all entities, label relationships if there are multiple relationships between 2 entities, include all attributes and constraints where necessary and identify all primary and foreign keys as discussed. For full credit, your submission must include: 1. EERD incorporating all entities identified in the project business rules (see your project specifics) 2. Relationships between those entities are drawn with min/max cardinalities and constraints where required. All primary and foreign keys will be identified. 3. Do not break the rules of EER diagramming. Some Tips for Database Experience Project ERD: 1. Capture all the entity types listed in the Data required section of the business rules. 2. In addition, capture NURSES, MEDICALHISTORY, AND PATIENTSYMPTOM 3. Two sets of entity attributes can be derived from the medical history form a. Attributes for PATIENT entity type b. Attributes for MEDICALHISTORY entity type 4. Some of the entity types have supertype/subtype relationships a. APPOINTMENT is a supertype with disjointed subtypes - MEDICALHISTORY and VISITINFO. b. PATIENT is a supertype also with disjointed subtypes such as DEAD and ALIVEDROPPED. Similar logic as in point 4.a. above can be applied. Read section 4.1.2 of the text book for additional reference. C. The subtypes will assume the primary key of the supertypes they belong to. 5. Information that cannot be explicitly modeled in the ERD such as drugs dispensed only being visible to the supervisors can be captured in the Semantic Integrity Constraints. 6. Remember attributes with multiple options such as side effects, and allergies will be captured as multivalued attributes. 7. There is no need to worry about deletion rules as no information is given about this. 8. While there is room for flexibility as everyone will present the information differently, ensure all fundamental business rules stated in the instructions are captured