Question

need new and unique answers, please. (Use your own words, don't copy and paste), Please Use your keyboard (Don't use handwriting) Thank you..

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 (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)

ER Diagram goes next: (1 Mark)

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

In order to design an Entity Relationship (ER) diagram, we have to first make note of the Entities and their respective attributes and the symbols with which we can depict them.

Here, Patient, Doctor , Pharmaceutical Company and each drug is considered as an entity. Every entity is shown as a rectangular box and their attributes are shown in oval shapes below:

ENTITIES AND ATTRIBUTES IN ER

RELATIONSHIPS AND ATTRIBUTES IN ER : Lets define how each of the above created entities and their attributes (properties) relate to one another.

Each relation is symbolized by the diamond shape in the diagram below.

For example, In the above diagram the Doctor and Patient are related by the fact that the doctor "Prescribes drugs".

So we depict it as,

Here , many to many relationship is depicted, many patients can see many doctors and many doctors can provide prescriptions for many patients.

The interpretations of each of the constraints represented on the edge labels in ER are defined below in rectangular boxes.

ER Diagram :