THE CASE:
Various physicians and surgeons working for Central Hospital are on an annual salary [o]. These doctors are identified by their respective employee numbers. The other descriptors of a doctor are: 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.
In Central Hospital IT system 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 cancelled. Data for patients include: 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 a 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.
QUESTION: Develop and design Entity and related attributes the based on given parameters. For each Entity, identify the primary key. Draw an ER diagram that shows the entity types and relationship types. Provide our solution in ER diagrams (Conceptual ER diagram symbols).
