Question 5 [7 Points]: You are asked to design a pharmacy database application for a chain of
pharmacies with the following requirements.
1. There are three main items in the database: pharmaceutical companies, pharmacies, and drugs.
2. Each pharmaceutical company is uniquely identified by its name and has one or more phone
numbers.
3. For each drug, the trade name and formula must be recorded. Each drug is produced by a
pharmaceutical company, and the trade name identifies a drug uniquely from among products of
that company. If the pharmaceutical company is deleted, you no longer need to keep track of its
products.
4. A drug may course allergy (bad side effect) for a given patient if it is taken with other drugs (i.e., a
given drug should not be taken with these drugs). For each drug, the database should maintain all
allergic drugs (if any) that may cause allergy if they are taken with the specified drug.
5. Each pharmacy has a name, address as (city, state, and P.O. Box), and a phone number. It is
uniquely identified by its name and address.
6. Each pharmacy sells several drugs, and has a price for each. A drug could be sold at several
pharmacies, and the price could vary (different) from one pharmacy to another.
7. Pharmaceutical companies have long term contracts with several pharmacies, and a pharmacy can
have contracts with several pharmaceutical companies. For each contract, you need to store the
start day and end date.
Design an ER diagram for the application. You need to specify all entity types, attributes,
relationships, maximum and minimum cardinalities, and other applicable constraints. Also, you
need to justify your decisions and state additional assumptions if necessary;
(1) [1 Points]Specify the main entities with their attributes.
(2) [1 Point] Specify the main relationships with their constraints and participating entities.
