The rising cost of veterinarian care

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.