Database Design Script Assignment

To satisfy the requirements for this assignment, complete the following steps:

1) Consider the following scenario: A small doctors office has three physicians, each of whom see many patients each day. The office is patronized by more than 500 different people, some of whom have come in for many appointments over the years, some of whom have only come in once and never returned. If a new patient sets up an appointment, any of their family members are also added to the clinic's database, just in case they schedule an appointment at some point in the future. Each appointment must keep track of date and time, who the appointment is for, which doctor will conduct the appointment, when the appointment was complete, and if the doctor recorded any notes from the appointment. Doctors may also write prescriptions for patients in association with the appointments. Not every appointment will result in a prescription being written, but some appointments will have two or more prescriptions written for a patient.

2) Using draw.io (Links to an external site.) (or another ER diagramming tool if you prefer and have access to it), create an Entity Relationship diagram for a database that would support the clinic's data needs, as described in step #1 above. Be sure to include Entities, Attributes, Data Types and Relationships with appropriate referential integrity. Create a Microsoft Word document and either place a screenshot of your ER diagram or embed your ER diagram into the Word document.

3) Below your ER diagram, write a DDL script in SQL Server syntax that will create your entities and their attributes (with data types), define keys, and enforce referential integrity.

Save and submit your Word document to Canvas by the due date.

I need help creating an ER diagram based on the above requirements.