PART I SQL Data Control Language and Views:

The users of the Medical database include three nurses (nurse_1, nurse_2, and nurse_3), three physicians (doc_1, doc_2, and doc_3), and one manager (manager_1), all of whom have been created by the DBA. You are asked to use SQL to create necessary roles and grant the indicated privileges to roles/users.

All nurses can read and modify (insert, delete, and update) the InsuranceComp, HasPolicyWith, Patient, and OfficeVisit tables.

All nurses can read from and insert into the Took table.

All nurses can read from the Physician and LabTest tables.

All physicians can read from and insert into the LabTest and Physician tables.

All physicians can read and modify (insert, delete, and update) the Took and OfficeVisit tables.

The manager of the clinic has all privileges on all the tables.

Create a view that allows the billing department to access patient billing information for office visits including patient's ID, first and last names, phone number, and physician's first and last names, and the charge of each office visit.

InsuranceComp ID Name PhoneNum Patient ID Fname Lname BirthYear Sex Zipcode PhoneNum OfficeVisit PatID DocID Month Day Year Outcome Charge HasPolicyWith Pat ID InsCompId StartDate Took PatID DocID Physician ID Fname Lname OfficeNum Specialty Result LabTest ID Name Charge Description InsuranceComp ID Name PhoneNum Patient ID Fname Lname BirthYear Sex Zipcode PhoneNum OfficeVisit PatID DocID Month Day Year Outcome Charge HasPolicyWith Pat ID InsCompId StartDate Took PatID DocID Physician ID Fname Lname OfficeNum Specialty Result LabTest ID Name Charge Description