The Prescriptions-R-X chain of pharmacies has offered to give you
a free lifetime supply of medicine if you design its database.
Given the rising cost of health care, you agree. Here's the information that you gather:
- Patients are identified by an SSN.
For each patient, the name, address, and age must be recorded.
- Doctors are identified by an SSN.
For each doctor, the name, specialty, and years of experience must be recorded.
- Each pharmaceutical company is identified by name and has a phone number.
- For each medicine, the trade name and formula must be recorded.
Each medicine is made by a given pharmaceutical company,
and the trade name identifies a medicine uniquely from among the products of that company.
- Each pharmacy is identified by name and has an address and a phone number.
- Every patient can have several doctors. Every doctor has at least one patient.
- Each pharmacy sells several medicines and has a price for each.
A medicine could be sold at several pharmacies, and the price could vary from one pharmacy to another.
- Doctors prescribe medicines for patients.
A doctor could prescribe one or more medicines for several patients,
and a patient could obtain prescriptions from several doctors.
Each prescription has a date and a quantity associated with it.
You can assume that, if a doctor prescribes the same medicine for the same patient more than once,
only the last such prescription needs to be stored.
- Pharmaceutical companies have long-term contracts with pharmacies.
A pharmaceutical company can contract with several pharmacies,
and a pharmacy can contract with several pharmaceutical companies.
For each contract, you have to store a start date, an end date, and the text of the contract.
- Pharmacies appoint a supervisor for each contract.
There must always be a supervisor for each contract,
but the contract supervisor can change over the lifetime of the contract.