Question

Create a series of ER Diagrams using MySQL Workbench. Each question below is a different version of the same database, with each version increasing in complexity. You need to submit an ER diagram for each version, in pdf or png format. The easiest would be to export your diagrams as png format and insert them into an MS Word document.

For each ER diagram clearly indicate primary keys, foreign keys, and relationships, including cardinality (i.e. 1-to-many). Indicate in your diagram that all relationships are mandatory. Since we have not studied data types in detail, you will not be assessed on which data type you choose, although certainly try your best.

The database will store data about patients receiving drugs in a clinical trial.

1. This is version 1.
   Patients have the following attributes: patient_id, first name, last name, DOB and gender.
   Drugs simply have a name.
   Patients can receive >1 drug.
   
   Hint: Your design should have two tables.
   
2. This will be version 2 of the database. Modify your design to include the following requirements:
   
   Drugs have the following attributes: drug_id, drug_name, and manufacturer_name.
   Patients can receive >1 drug.
   Each drug is administered to >1 patient.
   
   Hint: This is an example of a many-to-many relationship. Review the readings to resolve this issue.
   
3. This will be version 3 of the database. Modify your design to include the following requirements:

For each drug administration, also store drug-dosage and date.

Hint: This does not require additional tables; the question is where to add these new attributes.

Answer 1

If you have any doubts, please give me comment..