View the step-by-step solution to:

# STID5014/5043 - ADVANCE DATABASE DESIGN (SEMESTER A121) INDIVIDUAL ASSIGNMENT 2...

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.

STID5014/5043 – ADVANCE DATABASE DESIGN (SEMESTER A121) INDIVIDUAL ASSIGNMENT 2 ------------------------------------------------------------------------------------------------------------ By using the Hotel database schema: Hotel (hotelNo, name, address), contains hotel details and hotelNo is the Primary Key (PK). Room (roomNo, hotelNo, type, price), contains room details for each hotel and roomNo, hotelNo forms the composite PK. Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo), contains details of the bookings and hotelNo, guestNo and dateFrom forms the composite PK. Based on this 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.name = ‘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. (40 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 nDistinct hotelNo (Room) = 50 nDistinct type (Room) = 10 nDistinct price (Room) = 500 nDistinct guestNo (Booking) = 1000 min price (Room) = 200 max price (Room) = 500 nLevels hotelNo (I) = 2 nLevels type (I) = 2 nLevels price (I) = 2 nLfBlocks price (I) = 50
STID5014/5043 – ADVANCE DATABASE DESIGN (SEMESTER A121) (a) Calculate the cardinality and minimum cost for each of the following Selection operations: i) σ roomNo=1 hotelNo=’H100’ (Room) (10 mark) ii) σ hotelNo=‘H200’ (Room) (10 mark) (b) Calculate the cardinality and minimum cost for each of the following Join operations. Assume buffer is 200. i) Hotel hotelNo Room (15 mark) ii) Hotel hotelNo Booking (15 marks) (c) Calculate the cardinality and minimum cost for the Projection operations guestNo (Booking) (10 mark) Notes: Please submit your assignment before or on 15 December 2012. End of Questions

### Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

### -

Educational Resources
• ### -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

Browse Documents
• ### -

Question & Answers

Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

Ask a Question