$1)$ Draw the conceptual ERD, $(2)$ draw the logical data model, and $(3)$

write the tables out marking the key fields and listing the foreign keys.

along with what table they are associated with.

You've been hired the Pharmacological Association of America to design a new

database system for tracking drug companies and pharmacies. The head of IT

meets with you to give you the parameters of the design. The following list

contains the notes you took during that meeting.

$($a$)$ A pharmaceutical company manufactures one or more drugs, and each drug

is manufactured and marketed by exactly one pharmaceutical company. For

example, Singulair, an asthma drug, is produced and sold only by the

pharmaceutical company Merck.

$($b$)$ Drugs are sold in pharmacies. Each pharmacy has a unique identification.

Every pharmacy sells one or more drugs, but some pharmacies do not sell

every drug. Rite Aid and Walgreens are local pharmacies. They sell many

drugs, but not all drugs.

$($c$)$ Drug sales must be recorded by prescription, which are kept as a record by

the pharmacy. A prescription clearly identifies the drug, physician, and

patient, as well as the date it is filled. Rite Aid and Walgreens need to be

extremely careful with their records. Every prescription needs to be on file,

electronically. $($Assume that some small pharmacies in the database have

drugs in stock that have never been prescribed.$)$

$($d$)$ Information about the doctors should be held in the database. For example,

the doctor's ID and name, along with their business address, phone and Email

should be recorded.

$($e$)$ Information about the patients should be held in the database by the

patient's ID and name, address, date of birth, phone and Email. Any patient

under the age of $18$ also has a guardian, who must be recorded along with

the guardian's name, phone and Email.

$($f$)$ Doctors prescribe drugs for patients. A doctor can prescribe one or more

drugs for a patient and a patient can get one or more prescriptions, but a

prescription is written by only one doctor. And a prescription is written for

only one patient with only one drug. The prescription should also include the

dose and dosage form, e$.$g$.$ acetaminophen $650$ mg tablets.

$($g$)$ Pharmaceutical companies may have long$-$term contracts with pharmacies

and a pharmacy can contract with zero, one, or more pharmaceutical

companies. Each contract is uniquely identified by a contract number.

$($h$)$ Pharmacies employee both full time and part time pharmacists. Full time

pharmacists earn a yearly salary; part time pharmacists earn an hourly wage.

In each pharmacy, one of the pharmacists manages the other pharmacists.