CSC 370 –Assignment 1


5/5 - (3 votes)

CSC 370 –Assignment 1
(submit assignment in class or slip it under my door ECS 556 or e-submit in connex;
SQL statements must be e-submitted in connex)
Ex. 1 (20 pts)
1. 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.
a) 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.
b) Each drug is produced by a given pharmaceutical company, and the trade
name identifies a drug only among the products of that company.
c) 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.
d) 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.
2. Translate the E/R diagram for the above exercise to tables. Specify primary keys.
a) Write the SQL statements for the creation of the tables.
b) 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.
1. Draw an E/R diagram capturing the doctor class hierarchy.
2. Translate your E/R diagram into tables and write the SQL statements for the table
creation. Specify primary keys.
3. Write SQL INSERT statements to insert at least one tuple (that you create) into
each table.

PlaceholderCSC 370 –Assignment 1
Open chat
Need help?
Can we help?