Subject- Database system

Use the following scenario as a precursor to the questions below:

A company has asked you to design a database for a pharmacy. They have given you a list of requirements for the database. Here is the list of requirements they send you:

- Each patient has a name, social security number (SSN), address, and date of birth (DOB). A patient can be uniquely identified by their SSN.

- Each doctor has a name, SSN, specialty, and year started. A doctor can be uniquely identified by their SSN.

- Every patient can have several doctors. Every doctor has at least one patient.

- Each pharmaceutical company is identified by its name. Each company also has a phone number.

- Each pharmacy has a name, address, and phone number. The pharmacy is identified by its name.

- For each medicine that is manufactured, the medicine name and formula must be recorded. Each medicine is made by a given pharmaceutical company, and the medicine name identifies a medicine uniquely from among the products of that pharmaceutical company.

- Each pharmacy sells several medications and has a price for each. A medicine could be sold at several pharmacies, and the price could vary from one pharmacy to another.

- A doctor prescribes medications for patients. A doctor could prescribe one or more medicines for several patients, and a patient could obtain prescriptions from several doctors. Each prescription has a date and a quantity associated with it.

1. Convert the scenario into an Entity Relation diagram to represent the database system. Use the correct lines to indicate cardinality of relationships as well as any weak entities/total participation.

2. From your answer in question 1, convert the ER diagram to tables as schema statements.