Data modeling using the entity-relationship (ER) model

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.

Answer:

Specific Requrieement/Constraint Type
Requirements and Constraints from the ER diagram
Entities and attributes
in ER
Patients(PSSN, pname, paddress, page)
Doctors(DSSN, dname, dspecialty, year_ofexperience)
PCompany(Cname, Cphone)
Drug(Tradename, formula, madeby)
Pharmacy(Phname, phaddress, phphone)
Relationships and attributes
 in ER
Treats (DSSN , PSSN)
Sells (Phname, Tradename Cname, price)
Prescribes (DSSN, PSSN , Tradename, date, qty)
Contracts (Cname, Phname, supervisor, startdate, enddate, ctext)

Interpretation of each of the constraints represented on the edge labels in ER
1. Each patient can have only 1 primary doctor
2. Each doctor can treat several patients.
3. Each doctor must have at least one patient to exist.
4. Each doctor can prescribe several drugs to several patients.
5. Each patient can obtain several prescriptions from several doctors.
6. Each pharmaceutical company can sell several drugs to several pharmacies.
7. Each pharmaceutical company can have several contracts with several pharmacies.
8. Each pharmacy can have several contracts with several pharmaceutical companies.
9. Each contract has only one supervisor enforced through key constraint.
Correct use of symbols in ER, etc (5 marks)
Show use of correct use of symbols for attributes, relationships, participations, etc.