Part $3$: Entity Relationship Diagram $(20$ points$)$

Draw an ERD based on the following information. The model represents the operations of a pharmaceutical chain where data needs to be kept on every transaction involving pharmaceutical companies, drugs, patients, and doctors.

$1.$ Patients are identified by IDs, their names, addresses, and birthdates $($that allows us to know the patient$$s age$).$

$2.$ Every patient has one primary doctor.

$3.$ Every doctor has at least one patient.

$4.$ Doctors are identified by IDs, name, specialty, and years of experience that is calculated using the doctors date of first service.

$5.$ Doctors prescribe drugs for patients.

$6.$ A doctor could prescribe one or more drugs for several patients, and a patient could obtain one or more prescriptions from the doctor.

$7.$ 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 prescription needs to be stored.

$8.$ For each drug, the trade name and formula must be recordered. Each drug is sold by a given pharmaceutical company, and the tradename 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.

$9.$ Each pharmaceutical company is identified by a company code, name and phone numbers $($it is common for a pharmaceutical company to have multiples phone numbers$).$

Make sure that you show:

$$ All the attributes listed above including multivalued and derived attributes. $(5$ points$)$

$$ The primary key and foreign keys. $(5$ points$)$

$$ The relationship strength $($strong vs$.$ weak$)(4$ points$)$

$$ The relationship participation $($optional vs$.$ mandatory$).(3$ points$)$

$$ The entity strength $($strong vs$.$ weak$)(3$ points$)$Part $3$: Entity Relationship Diagram $(20$ points$)$

Draw an ERD based on the following information. The model represents the operations of a pharmaceutical chain where data needs to be kept on every transaction involving pharmaceutical companies, drugs, patients, and doctors.

$1.$ Patients are identified by IDs, their names, addresses, and birthdates $($that allows us to know the patient$$s age$).$

$2.$ Every patient has one primary doctor.

$3.$ Every doctor has at least one patient.

$4.$ Doctors are identified by IDs, name, specialty, and years of experience that is calculated using the doctors date of first service.

$5.$ Doctors prescribe drugs for patients.

$6.$ A doctor could prescribe one or more drugs for several patients, and a patient could obtain one or more prescriptions from the doctor.

$7.$ 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 prescription needs to be stored.

$8.$ For each drug, the trade name and formula must be recordered. Each drug is sold by a given pharmaceutical company, and the tradename 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.

$9.$ Each pharmaceutical company is identified by a company code, name and phone numbers $($it is common for a pharmaceutical company to have multiples phone numbers$).$

Make sure that you show:

$$ All the attributes listed above including multivalued and derived attributes. $(5$ points$)$

$$ The primary key and foreign keys. $(5$ points$)$

$$ The relationship strength $($strong vs$.$ weak$)(4$ points$)$

$$ The relationship participation $($optional vs$.$ mandatory$).(3$ points$)$

$$ The entity strength $($strong vs$.$ weak$)(3$ points$)$