E-R Diagram 04: Medical Clinic

Consider a medical clinic that has the following business rules:

         Patients make appointments with doctors. Patient names are not necessarily unique. Doctor names are not necessarily unique. A doctor may have information in the database without necessarily having had any appointments with patients.

         A patient can make many appointments with many different doctors in the clinic, and a doctor can accept appointments with many patients. However, each appointment is made with only one doctor, and each appointment is for a single patient. An appointment consists of a patient, a doctor, and a scheduled date/time.

         Emergency cases do not require an appointment to be made in advance. However, an emergency visit is entered into the appointment book as an "unscheduled" appointment, so it looks just like any other appointment; it just wasn't booked in advance.

         Not all appointments are kept. If kept, an appointment yields a visit with the doctor specified in the appointment. The visit yields a diagnosis (one diagnosis per visit) and (possibly) a treatment (there may be more than one treatment per diagnosis; e.g. (1) exercise, (2) take 2 aspirin). A possible diagnosis is "nothing wrong". This diagnosis will have no treatments.

         If an appointment is not kept (patient can't make it), a record is still kept of the fact that the appointment had been scheduled. Do not worry about a doctor being unable to keep an appointment. Assume that if a doctor cannot keep an appointment that another doctor will be assigned to the appointment. There is no need to keep track of the doctor who failed to keep the appointment.

         Every time a patient keeps an appointment, the patient's medical history is updated so that a complete history of all visits by a given patient and all diagnoses and treatments can be generated. Every visit has a diagnosis, and every diagnosis can have 0 or more treatments.

         Each patient visit generates a bill. Each bill may be paid in installments. A payment may be applied to more than one visit. The clinic needs to keep track of all payments and which visit they are applied to.

         Visits and appointments are similar but not the same. If a patient forgets to keep an appointment, no visit occurs, but there was still an appointment (un-kept).

The clinic needs to be able to

         Print a schedule for a doctor for a given day.

         Call a patient.

         Call a doctor.

         Send bills to a patient.

         Send checks to doctors.

         Look up a patient's scheduled appointment.

         Look up any appointments that a patient may have missed.

         Look up both a diagnosis for a patient and the recommended treatment(s).

         Generate bills and look up a patient's payment record.

         Look up a patient's medical history (a list of all visits (date), doctor seen, and diagnosis for that visit).

Do the following:

         Identify each entity class.

         Identify all attributes for each entity class.

         Identify all determinants. Note that this may require you to re-think your entity classes.

         Identify a key for each entity class that will uniquely identify a single entity. Note that the key may be more than a single field. Underline the key field.

         Identify each foreign key (not all entity classes will have a foreign key). Make foreign keys italic.

         For each entity class, identify relationships between it and other entity classes.

         Draw the ER diagram using MySQL Workbench

Updated 2018.03.22