Need help using the above entity relationship diagram to answer these questions

1. Write One SCRIPT to create this database (call it DocOffice) in SQL SERVER

2. Write a SQL statement for Doc Rob Belkin is retiring. We need to inform all his patients, and ask them to select a new doctor. For this purpose, Create a VIEW that finds the names and Phone numbers of all of Rob's patients.

3. Write a SQL statement to Create a view which has First Names, Last Names of all doctors who gave out a prescription for Panadol

4. Write a SQL statement to Create a view which Shows the First Name and Last name of all doctors and their specialtys

5. Write a SQL statement to Modify the view created in Q4 to show the First Name and Last name of all doctors and their specialties ALSO include doctors who DO NOT have any specialty

6. Write a SQL statement to Create a stored procedure that gives the Prescription name and the number of patients from the city of Tacoma with that prescription.

Example

| 20 | Aspirin |

| 2 | Ciprofloxacin |

7. Create trigger on the DoctorSpeciality so that every time a doctor specialty is updated or added

a new entry is made in the audit table. The audit table will have the following

(Hint-The trigger will be on DoctorSpecialty table)

Doctor FirstName

Action(indicate update or added)

Specialty

Date of modification

Person Personi FirstName Speciality Speciality SpecialityName LastName StreetAddress City Doctor Doctorid MedicalDegrees State Tip Personi PhoneNumber SSN DoctorSpeciality 8 Doctorib 8 Speciality D PVisitPrescription % Visit 8 PrescriptionID Patient 8 Patients SecPhone Number DOB Personi Patient Visit 8 VisitID Patienti Doctor VisitDate DocNote Prescription 8 Prescription PrescriptionName Test TestiD TestName PVisitTest 8 VisitD 8 TestID Person Personi FirstName Speciality Speciality SpecialityName LastName StreetAddress City Doctor Doctorid MedicalDegrees State Tip Personi PhoneNumber SSN DoctorSpeciality 8 Doctorib 8 Speciality D PVisitPrescription % Visit 8 PrescriptionID Patient 8 Patients SecPhone Number DOB Personi Patient Visit 8 VisitID Patienti Doctor VisitDate DocNote Prescription 8 Prescription PrescriptionName Test TestiD TestName PVisitTest 8 VisitD 8 TestID