This vignette is a small excerpt from a comprehensive case about a clinic. Various physicians and surgeons working for a clinic are on an annual salary [o]. These doctors are identified by their respective employee numbers. The other descriptors of a doctor are the name, gender, address, and phone. Each physician’s specialty and rank [o] are captured; each surgeon’s, specialty and skill are also captured; a surgeon may have one or more skills?
Every physician serves as a primary care physician for at least seven patients; however, no more than 20 patients are allotted to a physician. Every patient is assigned one physician for primary care. Some patients need surgeries; others don’t. Surgeons perform surgeries for the patients in the clinic. Some do a lot of surgeries; others do just a few. The date and operation theater [o] for each surgery needs to be recorded, too. Removal of a surgeon from the clinic database is prohibited if that surgeon is scheduled to perform any surgery. However, if a patient chooses to pull out of the surgery schedule, all surgeries scheduled for that patient are canceled.
Data for patients include the patient number (the unique identifier of a patient), name, gender, date of birth, blood type, cholesterol (consisting of HDL, LDL, and triglyceride), blood sugar, and the code and name of allergies if any.
Physicians may prescribe medications to patients; thus, it is necessary to capture which physician(s) prescribe(s)s what medication(s) to which patient(s) along with dosage and frequency. In addition, no two physicians can prescribe the same medication to the same patient. If a physician leaves the clinic, all prescriptions written by that physician should also be removed because this information is retained in the archives.
A patient may be taking several medications, and a particular medication may be taken by several patients. Despite its list price, a medication’s cost varies from patient to patient, perhaps because of the difference in insurance coverage. The cost of a medication for a patient needs to be captured. A medication may interact with several other medications. When a medication is removed from the system, its interaction with other medications, if any, should be voided. When a patient leaves the clinic, all the medication records for that patient are removed from the system.
Medications are identified by either their unique medication codes or by their unique names. Other attributes of medication are its classification, list price, and manufacturer [o]. For every medication, either the medication code or the medication name must be present—not necessarily both.
Note: [o] indicates optionality of value for the attribute. Develop an ER model for this scenario.
Deletion constraints enclosed in a box (highlighted in red) reflect erroneous/conflicting deletion (business) rules. A few different ways of correction are possible. One such correction is shown in the next ER diagram