Database design

The logical phase of the database design process has provided the following relations for a database that was proposed to support the administration of a Private Surgery in Liverpool Patient (P Code, P Surname, P Firstname, P Sex, PDoBirth, P Occupation, P Height, P Weight, P Address, P MobileNo Doctor (D Code, D SurName, D FirstName, D PrivateAddress, DMobileNo, Function) Operation (OCode, P Code, D Code, Date, Time, Result, O Type) Is_Examined By (P_Code, D Code, Date, Time Location). Write the following queries in "RELATIONAL ALGEBRA", for the above relations 1. Find the Operation Types that have been done on the 22d of December of 2018 and have been successful. 2. Find the name, gender and age of all patients that have been seen by "Mr. Alex" and have been operated on by doctor "Dr. Henson", assuming that there are no doctors with the same surname 3. Find the first Name, Last Name, Address and Gender of the patients that have been operated on by doctor "D7679" and results have been successful. 4. Find the Full details of the patients that have had at least an operation with broken arm, with full details of the doctor which has been assigned to look after this patient. 5. Find the details of all patients that have shared any doctors with the patient that has the code "P898". This should include the name of the doctor and his contact detail Part four (Physical Database Design) (a) Write SQL statements to create and populate (with suitable suggested test data) the database tables for the relations considered in part two (b) Write SQL statements to implement each of the following queries 1. Find how many patients have been examined on the 4" of November 2017 by doctor Number "D989" 2. Find the full name, gender, address and age of patients that had an operation on the 20th of November 2018. List the details of all doctors that were involved with this operation. 3. Find the surname of the patients (with their code) that have been seen by at least one of the doctors that have examined patient "P999" 4. List the details of all patients and doctors (who are assigned to examine them or operate on them) for the following dates, 21/11/2016, 23/12/2018 and 24/12/2018 5. Find the full details of the patients that have been operated on by doctor "D909". 6. Find the contact details of all patients and their doctors that have had at least one operation with broken arm