Question

please unique answer, no handwriting, for safeAssign Pilgirism submission.API style

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 (1 Mark)
Relationships and attributes in ER (0.75 Mark)
Interpretation of each of the constraints represented on the edge labels in ER (0.75 Mark)

Answer 1

The above diagram is the er diagram

1,All the entities are represented by square boxes and their attributes in ovals.Doctor,patient,drugs,pharmacy,pharmaceutical company are considered as entities here.

2.Attributes used to identify the rows are called primary key and they are represented with a underline.here ssn for patient,ssn for doctor,trade name for drugs,name for pharmaceutical company are primarykeys.

3.Er diagram consists of 3 relationships and are represented by rhombus. prescribes relationshib is between doctor and patient.sold by relationship is between drugs and pharmacy.contract relationship between pharmacy and pharmaceutical company.

4.M indicates many to many relationship and is required for all 3 relationships in both directions.