Consider the Following Schema:
Pet (petNo, PetName, PetDescription, DateRegistered $\quad$

Essay

Consider the following schema:
Pet (petNo, petName, petDescription, dateRegistered, picture, surgeryNo, doctorStaffNo)
Staff (staffNo, sName, sAddress, position, surgeryNo)
Surgery (surgeryNo, surgeryAddress, surgeryTelNo)
where:
Pet $\quad$ contains details of pets and the pet number (petNo) is the key. The surgery where the pet is registered is given by the surgery number (surgeryNo). A pet can only be registered with one surgery at a time. The doctor who treats the pet is given by the doctorStaffNo. Picture contains an image of the pet.
Staff $\quad$ contains details of staff and staff number (staffNo) is the key.
Surgery $\quad$ contains details of each surgery and the surgery number (surgeryNo) is the key.
Now consider the following SQL:2003 query:
SELECT petNo, staffNo
FROM Pet p, TABLE StaffDoctors s
WHERE p.doctorStaffNo = s.staffNo AND
p.brownShortHairedTerrier(picture) AND s.surgeryNo = 'S3' AND
p.dateRegistered < '1-Jan-90';
The routine brownShortHairedTerrier() is an externally defined routine that searches the specified image for certain characteristics. The StaffDoctors() function is fully defined within SQL3 as:
CREATE FUNCTION StaffDoctors() RETURNS SET(Staff)
SELECT * FROM Staff WHERE position = 'Doctor';
Discuss how you would want a query optimiser within an ORDBMS to handle this type of query. Use a relational algebra tree to illustrate your answer.