You will focus on creating SQL database using the given description of the required database. Throughout this assignment, you will apply your skills to specify the database, create tables, set relationships, add and update data to tables.

$$

$$

Instructions

Consider a physician's office, where multiple patients are treated by multiple physicians. The physicians can be general practice or specialists. Patients must make an appointment to see a physician. When they come to the office, the attending physician may prescribe one or more medicines. After the appointment, medical records must be updated. The basic structure of the database is as follows:

Patient

PatientID, PK

PatientFirstName

PatientLastName

PatientDOB

PatientGender

PatientTel

PatientEmail

InsuranceProvider

InsuranceAccountNum

Visit

VisitID, PK

PatientID

PhysicianID

VisitDate

VisitTime

VisitReason

Status

Physician

PhysicianID, PK

FirstName

LastName

Speciality

PhysicianPhone

PhysicianEmail

$$

Prescription

PrescriptionID, PK

EMRID

MedicationName

Dosage

RefillAllowed

ExpirationDate

MedicalRecord

EMRID, PK

PatientID

PhysicianID

VisiID

Diagnosis

Notes

$$

$$

Identify Foreign Keys

$$ Review carefully the database structure, identify the relationships between the tables and describe the foreign keys that you would need.

Constraints

$$ Several constraints are needed in this database for data integrity, for example, when adding a record to the visit table, patientID and PhysicianID must be already in the database.

SQL Statement

$$ Write SQL DDL statements that you can use to create all the tables in this database.

Add Data

$$ Add at least three records to each table using SQL DML statements

$$

Make sure you include all SQL statements and screenshots of the query execution results.