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 operationshotelNo(Room) (1 mark)
