Question

Q3:

You have been hired to design a database for prescriptions for RX pharmacies and your first job now is to design an ER model for this database using the following description of that world.

i. patients are identified by their SSN and have other attributes as names, addresses, and ages.

ii. Doctors are identified by their SSN and have other attributes as names, specialty, and years of experience.

iii. Each pharmaceutical company is identified by name and has a phone number.

iv. For each drug, the trade name and formula must be recorded. Each drug is sold by a given pharmaceutical, and the trade name identifies a drug uniquely from among the products of that company. If a pharmaceutical company is deleted, you need not keep track of its products any longer.

v. 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.

vi. 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. You can assume that if a doctor prescribes the same drug for the same patient more than once, only the last such prescriptions needs to be stored.

vii. Pharmaceutical companies have contracts with pharmacies. A pharmaceutical company can have contracts with several pharmacies and a pharmacy can have contracts with several pharmaceutical companies. For each contract, you need to store a start date, an end date and the contract text.

viii. Pharmacies appoint a supervisor for each contract. There must always be a supervisor for each contract.

Hint: Present the conceptual design first, showing (1) all the entities and their attributes, (2) all the relationships and their attributes, (3) all the constraints before drawing your ER.

Specific Requirement /Constraint Type	Requirements and Constraints from the ER diagram
Entities and attributes in ER
Relationships and attributes in ER
Interpretation of each of the constraints represented on the edge labels in ER

ER Diagram goes next:  

You have to draw it digitally and attach. Note that in the ER diagram, the foreign key attributes that are part of the relationship schemas are not explicitly listed with the relationship but inherited from the entity the relationships are connected to.

Ans.

Answer 1

Q3)