STID5014/5043 - ADVANCED DATABASE DESIGN (SEMESTER A122)
ASSIGNMENT 2 (INDIVIDUAL)
By using the Hotel database schema:
Hotel (hotelNo, hotelName, address)
hotelNo is the Primary Key
Room (roomNo, hotelNo, type, price)
(roomNo + hotelNo) is the composite Primary Key
Booking (hotelNo, customerNo, dateFrom, dateTo, roomNo, employeeNo)
(hotelNo + guestNo + dateFrom) is the composite Primary Key
Customer (customerNo, title, fName, lName, Address)
customerNo is the Primary Key
Employee (employeeNo, hotelNo, title, name, position)
(employee + hotelNo) is the composite Primary Key
Based on this schema, a query for listing customers who made a booking and employee whose handle the booking for Bustani Hotel from 1/4/2013 until 30/4/2013 is defined as
follow:
SELECT e.name, c.customerNo, c.fName, c.lName
FROM Room r, Booking b, Hotel h, Customer c, Employee e
WHERE h.hotelNo=b.hotelNo AND c.customerNo=b.customerNo AND
h.hotelNo=r.hotelNo AND b.employeeNo=e.employeeNo AND
h.hotelName="Bustani" AND (dateFrom => "1/4/2013" AND
dateTo <= "30/4/2013")
Question
Draw a relational algebra tree (RAT) for this queries and use the heuristic rules to
transform the queries into a more efficient form. Explain each step and state any
transformation rules used in the process.