Problem: Temple Hospital has hired you, newly graduated from the Information Systems program at Harvard, to build a new database system to bring order into their chaotic, discombobulated pharmacy. At Temple, doctors prescribe medicines in specific dosages to their patients. The prescriptions are for a finite duration; for example: a six-month prescription of Diltiazem, 25mg, daily, starting on 3/14/18 until 9/14/18, issued by Dr. Alan Hirakawa on 3/13/18. After a prescription is expired a patient can no longer receive the medication unless the doctor issues a new prescription. Once the pharmacy has filled the prescription (or a fractional part of it, say one month of a six-month prescription) the patient cannot get another filling. Therefore, an unlimited supply of Vicodin shall not be possible. Old, expired prescriptions are not deleted from the system, they are archived so that there is a clear chain of custody for accounting of where these drugs have gone. Over their treatment course, a patient may have many prescriptions oftentimes from several different doctors. A doctor will have issued thousands of prescriptions during their tenure at a hospital.

For doctors, Temple keeps track of their medical specialty, name, their license renewal date, their office location, and their phone numbers. For drugs, Temple keeps track of the common name, the IUPAC name, the LD50: a measurement of the relative toxicity, and the MSDS safety data sheet information. For patients, Temple stores their names, contact information, insurance information, social security numbers, the name of their primary physician, birth dates, emergency contact details.

In addition to keeping track of patients, doctors, and the prescriptions, Temple needs to keep track of their inventory of drugs (how many units are in storage in the hospitals stock-room). They need to reorder before they run out because there is a significant lead time before the hospital receives the drug from the supplier. Sometimes drugs expire before they are used up and the hospital must dispose of these safely so they keep track of the acquisition date and the lots expiration date. Temple keeps the names and phone numbers of drug-company sales representatives, their affiliated drug companies, and for which drugs they manufacture. A single drug manufacturer can make more than one drug product. A single drug can be manufactured by more than one company.

Question: DRAW an entity relationship diagram (ERD) to show a model of a database schema that addresses the above complication. Make sure to indicate Primary Keys, Foreign Keys, separate "attributes" into "entities", draw tables for each "entity", connection lines between entities to show the relationships.