Ex. 1 (20 pts)
The information about the prescriptions-R-X chain of pharmacies is given in the following. Draw an E/R diagram that captures the given information.
Patients are identified by a healthcare number, and their names, addresses, and ages must be recorded. Doctors are identified by a doctor id number. For each doctor, the name, specialty, and years of experience must be recorded. Every patient has a primary physician. Each pharmaceutical company is identified by name and has a phone number. For each drug, the trade name and formula must be recorded.
Each drug is produced by a given pharmaceutical company, and the trade name identifies a drug only among the products of that company.
Each pharmacy has a name, address, and phone number. Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another.
Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors. Each prescription has a date and a quantity associated with it. We need to know who has prescribed what for whom. You can assume that if a doctor prescribes the same drug for the same patient more than once, only the last such prescription needs to be stored.
Translate the E/R diagram for the above exercise to tables. Specify primary keys.
Write the SQL statements for the creation of the tables.
Then write SQL INSERT statements to insert at least one tuple (that you create) into each table.
Ex. 2 (15 pts) Suppose we have two kinds of doctors: hospital doctors and family physicians. In addition to the doctor’s id number, name, specialty, and years of experience, we want to record the hospital name for the hospital doctors, and the office address for the family physicians. There can be doctors that are working in a hospital who are at the same time family physicians in their free time. Also there can be doctors for whom we don’t know whether they are working in a hospital and/or whether they are family physicians or whether they are not working at all.
Draw an E/R diagram capturing the doctor class hierarchy.
Translate your E/R diagram into tables and write the SQL statements for the table creation. Specify primary keys.
Write SQL INSERT statements to insert at least one tuple (that you create) into each table.
