Answered You can hire a professional tutor to get the answer.


By using the Hotel database schema:

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 forEDC Hotel and the room price must RM150 and above is defined:SELECT r.roomNo, r.type, r.priceFROM Room r, Booking b, Hotel hWHERE r.roomNo = b.roomNo AND b.hotelNo = h.hotelNo ANDh.hotelName = ‘EDC Hotel’ AND r.price > 150;Question 1Draw a relational algebra tree (RAT) for each of the queries and use the heuristic rules totransform the queries into a more efficient form. Explain each step and state anytransformation rules used in the process. (4 marks)------------------------------------------------------------------------------------------------------------Question 2Assume 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) = 10000bFactor(Room) = 200nTuples(Hotel) = 50bFactor(Hotel) = 40nTuples(Booking) = 100000bFactor(Booking) = 60nDistincthotelNo(Room) = 50nDistincttype(Room) = 10nDistinctprice(Room) = 500minprice(Room) = 200maxprice(Room) = 500nLevelshotelNo(I) = 2nLevelstype(I) = 2nLevelsprice(I) = 2nLfBlocksprice(I) = 50STID5014/5043 – ADVANCE DATABASE DESIGN(a) Calculate the cardinality and minimum cost for each of the following Selectionoperations:i) σroomNo=1 ∧ hotelNo=1(Room) (1 mark)ii) σhotelNo=‘H02’(Room) (1 mark)(b) Calculate the cardinality and minimum cost for each of the following Join operations:i) Hotel⋈ hotelNo Room (1 mark)ii) Hotel⋈ hotelNo Booking (2 marks)(c) Calculate the cardinality and minimum cost for the Projection operationshotelNo(Room) (1 mark)

Show more
Ask a Question