A chain of pharmacy stores has asked you to design its database. Here$$s

the information that you gather:

$1.$ Patients are identified by their ID$.$ For each patient, the ID$,$ first

name, last name, address, and date of birth must be recorded.

$2.$ Doctors are identified by their ID$.$ For each doctor, the ID$,$ first

name, last name, specialty, and years of experience must be

recorded.

$3.$ Each clinic is identified by its name and city. For each clinic, the

name, city and address, phone number are recorded.

$4.$ Health insurance companies are identified by their ID$.$ For each

health insurance company, its ID$,$ name, address and phone number

must be recorded.

$5.$ Doctors work at clinics. Each doctor should be associated with at

least one clinic.

$6.$ Every patient has one primary physician. Every doctor has at least

one patient.

$7.$ Each drug company is identified by its ID$.$ For each drug company,

its ID$,$ name, address, and phone number are recorded.

$8.$ For each drug, the name and formula must be recorded. Each drug

is made by a given drug company, and the drug name identifies a

drug uniquely among the products of that company. If a drug

company is deleted, its products $($drugs$)$ should also be deleted.

$9.$ Each pharmacy has a unique ID$.$ For each pharmacy, its ID$,$ name,

address and phone number are recorded. A pharmacy can either be

an in$-$store pharmacy $($which has an address$)$ or an online

pharmacy $($which has a webURL$).$

$10.$ 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.

$11.$ Every patient has an insurance policy that was purchased from a

health insurance company. The policy has a unique policyID. For

each policy, the effective date and expiration date are recorded.

$12.$ Doctors write prescriptions for patients. A doctor can write

prescriptions for many patients and a patient could obtain

prescriptions from multiple doctors.

$13.$ A doctor could prescribe one or more drugs in a prescription. Each

prescription has a prescription number and a date associated with

it$.$ Each prescription is given to the patient in a certain clinic and

the prescription number is unique within the clinic it is given.

$14.$ A drug prescribed in a prescription may or may not be covered by

a patient$$s insurance policy. No additional details need to be

recorded on the drug$$s coverage by the insurance policy.

$15.$ Drug companies have long$-$term contracts with in$-$store

pharmacies. A drug company can have contracts with several in$-$

store pharmacies, and an in$-$store pharmacy can have contracts

with several drug companies. For each contract, you have to store a

start date, an end date, and the text of the contract.

$16.$ Pharmacies appoint a supervisor for each contract. There must

always be a supervisor for each contract.

Questions:

$1.$ a$)$ Complete ER Diagram $[85$ pts$]$: Draw an ER diagram for the

pharmacy database. Make sure that your design captures all of

implications of the business model, including:

$-$ all of the relevant entities and their attributes, including keys;

$-$ all the relevant relationships and associated attributes, appropriate

key constraints for the relationships, and appropriate participation

constraints for the relationships.

$1.$ b$)$ Partial ER Diagram $[10$ pts$]$: How would your design change if

each drug must be sold at a fixed price by all pharmacies? Draw the ER

diagram of just the entities and relationships that would need to be

updated.

$1.$ c$)$ Style $[5$ pts$]$: Make sure that your diagram is drawn digitally and is

clear and easy to read. You may use MS Word, Apple Pages, LucidChart,

or another software to draw the diagram. Make sure to use the ER

notations covered in the lecture notes. Do not use UML or Crow$$s Foot

notation. Do not draw the ER diagram by hand and take its picture.