Given the following relational schemas of a Hospital database, express the queri

Question

Given the following relational schemas of a Hospital database, express the queries using Relational Algebra. Hospital Database Doctor (Doc_id, Name, Designation, Area_of_Expertise) Employee (Emp_id, Phone, DoB, Office_Room, Address, Salary, Gender) Patient (Pat_id, Name, DoB, Address, Gender) Patient History (Pat_id, Problem_Area) Sees (Pat_id, Doc_id, Date) List the names of all the doctors and their area of expertise. Find the names of doctors with annual salary > 150k. List the patients (names and ids) of doctor "Johnson". List the female patients treated by female doctors. Find the staffs information that are not doctor. Find all the doctors names and their area of expertise seen by a patient with Pat_id = "P_1110000" Find all the patients name and gender who are diagnosed/treated for "Cancer". Find the office (room number) of doctor "Smith" who is an expert in "Diabetes & Obesity". Find all the doctors, staffs, and patients name along with id and address. If the hospital is expanded to more than one building, how will you adjust the information in this hospital database?

Explanation / Answer

ans 1: Name , Area_of_Expertise(Doctor)

ans 2 :Name (Doc_id=Emp_idsalary>150k(Doctor X Employee))

ans 3: Pt_id,Name(new.Pt_id=Patient.Pat_id(new(Pat_id(Doctor.Doc_id=Sees.Doc_idName="Johnson"(Doctor X Sees))) X Patient))

ans 4: take cross product of doctor and employee to find the female doctors and then find the patients treated by them by take cross product with sees and then find the patients which are female by taking cross product with patient.

ans 5: Emp_id,Phone,DoB,Office_room,Address,Salary,Gender(Employee) -   Emp_id,Phone,DoB,Office_room,Address,Salary,Gender(Emp_id=Doc_id(Employee X Doctor)

ans 6: Name,Area_of_Expertise(Doctor.Doc_id=Sees.Doc_id Pat_id="P_111000"(DoctorXSees))

ans 7: Name,Gender(Patient.Pat_id=Patient_History.Pat_id Problem_Area="Cancer"(Patient X Patient_History))

ans 8: Office_room(name="Smith" Doc_id=Emp_id Area_of_Expertise="Diabetes & Obesity"(Doctor X Employee))

ans 9: staff's name is not give in the data base. but we can find doctor's and patient's name id and addresses by using union operation as given below :

name,Address,Pat_id(Patient) Name,address,emp_id(emp_id=doc_id(Doctor X Employee))

to find staff's id and address we can use address,emp_id(Employee)

ans 10: if hospital is expanded to more than one building then we can add one attribute building_id to employee to make info about the room in a particular building.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.