All Pets Clinic Pharmacy has offered to give you a free lifetime supply of medicine if you
design its database.
Given the rising cost of veterinarian care, you agree.
The following is the information you gathered:
• Pets are identified by a code #, and their names, addresses, and ages must be
recorded
• Veterinarians are identified by an SSN. For each veterinarian, the name. specialty,
and years of experience must be recorded.
• Each pharmaceutical company is identified by name and has a phone number
• For each medication, the trade name and formula must be recorded. Each
medication is sold by a given pharmaceutical company, and the trade name
identifies a medication uniquely from among the products of that company. Each
pharmacy has a name, address, and phone number
• 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.
• Each pharmacy sells several medications and has a price for each. A medication
could be sold at several pharmacies, and the price could vary from one pharmacy
to another.
• Each medication has a date and a quantity associated with it. You can assume
that, if a veterinarian prescribes the same medication for the same pet more than
once, only the last such medication needs to be stored.
1. Draw an ER diagram that captures the preceding information. Identify any
constraints not captured by the ER diagram (10pts.)
2. How would your design change if each medication must be sold at a fixed
price by the pharmacies? (Requirement: Provide a written explanation)
(20pts.)
3. How would your design change if the design requirements change as follows:
If a veterinarian prescribes the same medication for the same pet more than
once, several such prescriptions may have to be stored? (Requirement:
Provide a written explanation) 20pts.