By using the Hotel database schema:
Hotel (hotelNo, name, address)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Where,
? Hotel contains hotel details and hotelNo is the Primary Key (PK).
? Room contains room details for each hotel and roomNo, hotelNo forms the PK.
? Booking contains details of the bookings and HotelNo, guestNo and dateFrom forms the PK.
Based on the schema, a query for selecting room number, room type and room price for EDC Hotel and the room price must RM150 and above is defined:
SELECT r.roomNo, r.type, r.price
FROM Room r, Booking b, Hotel h
WHERE r.roomNo = b.roomNo AND b.hotelNo = h.hotelNo AND
h.hotelName = 'EDC Hotel' AND r.price > 150;
Question 1
Draw a relational algebra tree (RAT) for each of the 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. (4 marks)
------------------------------------------------------------------------------------------------------------
Question 2
Assume the following indexes exist in the Hotel database schema:
• A hash index with no overflow on the primary key attributes, roomNo+hotelNo in Room;
• A clustering index on the foreign key attributes hotelNo in Room;
• A B+-tree index on the price attribute in Room;
• A secondary index on the attribute type in Room.
nTuples(Room) = 10000
bFactor(Room) = 200
nTuples(Hotel) = 50
bFactor(Hotel) = 40
nTuples(Booking) = 100000
bFactor(Booking) = 60
nDistincthotelNo(Room) = 50
nDistincttype(Room) = 10
nDistinctprice(Room) = 500
minprice(Room) = 200
maxprice(Room) = 500
nLevelshotelNo(I) = 2
nLevelstype(I) = 2
nLevelsprice(I) = 2
nLfBlocksprice(I) = 50
STID5014/5043 - ADVANCE DATABASE DESIGN
(a) Calculate the cardinality and minimum cost for each of the following Selection
operations:
i) σroomNo=1 ∧ hotelNo=1(Room)
ii) σhotelNo='H02'(Room)
(b) Calculate the cardinality and minimum cost for each of the following Join operations:
i) Hotel? hotelNo Room
ii) Hotel? hotelNo Booking
(c) Calculate the cardinality and minimum cost for the Projection operations
?hotelNo(Room)