In this assignment you will develop an Extended Entity Relationship Diagram (EERD) for Orthopedic Alliance of Plano (OAP). The database will be used to keep track of all aspects of its healthcare business as described below.
OAP is a small 7-physician clinic specializing in knee, elbow, hand, shoulder, hip, foot, ankle and other orthopedic problems. Besides seven physicians, OAP staff includes four nurses, two secretaries, and other employees filling various positions. Employee information including employee id, name, address, age, salary, should be recorded.
Patients make appointments to visit a physician by calling a secretary. When a patient arrives for their first visit, a record of this patient is created where a patient ID is assigned and personal information including name, sex, DOB, address, SSN, etc., is recorded. Each patient is also asked to provide one or more contact persons and personal information of these contacts for emergencies. During the first visit the patient is assigned to a physician who then becomes her primary care physician. On subsequent visits (if any), a patient may be treated by other physicians but her primary care physician will not be changed.
On the first and subsequent visits (if any), a patient needs to fill a form to provide the reason of visit. A nurse pre-examines the patient for heart rate, temperature, etc. and then a physician exams the patient and gives a diagnosis. For each visit, a visit id is assigned and the information described above (reason of visit, pre-examines results, and diagnosis) as well as date of visit is recorded.
For each visit, the physician may write one or more prescriptions. Each prescription is recorded with a prescription id and contains information about the drug used and dosage. OAP also maintains data on drugs. Each drug has a unique id and its name, manufacturer, and price are recorded. Any drug listed in the database may have interactions with a number of other drugs. Description of this interaction must be captured.
For each visit, if the problem requires a certain type of surgery, it is scheduled at one hospital in the city. OAP maintains data on related types of surgery, each with a surgery id and corresponding descriptions. The data on involved hospitals is also recorded with hospital id and related information such as name, address and descriptions. 1. A hospital may or may not offer a certain type of surgery. OAP also want to keep track of each specific surgery, i.e. which visit led to what surgery and the hospital in which it was performed and other details.
Some additional Business Rules/Assumptions:
1. It is not necessary to track the secretary who schedules the appointment.
2. It is desired to keep track of the nurse who do pre-examinations and the physician who do the examination.
3. There are other types of employees in the clinic, but nurses and physicians are the only ones who have special attributes and relationships. OAP keeps track of the specialty of each physician and the years of experience of each nurse.
4. Contact information will be stored only as long as an individual is a patient at the clinic.
Draw an extended ER diagram for the problem described above. Select appropriate attributes based on the information provided in this document and your understanding of the problem domain. Clearly state any more assumptions you make about the business rules.
All entities

The entities should be named as given in the problem
Weak entities, if any should be indicated as such
Attributes

Note that all identifiers should be underlined
Composite attributes should be shown as appropriate
Relationships

Be sure you include all the relationships described in the problem
Choose appropriate names for the relationships
Indicate all minimum and maximum cardinality constraints for all relationships.
For those cases where the constraints are not explicitly indicated, make suitable assumptions and clearly write them in your solution.
A many-many relationship with attributes should be shown as an associative entity.
A ternary relationship should be shown as an associative entity.
Subtype-Supertype hierarchy
Show the disjointness and specialization constraints. Clearly indicate the attributes of the supertype and the attributes of the subtypes. Also show the relationships as appropriate. The attributes of the supertype should also include the subtype discriminator