Question

**************PLEASE COMPLETE PART E) ONLY**************

Consider the following relational database schema (primary keys are underlined) and SQL query:

Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)

SELECT g.guestNo, g.guestName
FROM Room r, Booking b, Hotel h, Guest g
WHERE h.hotelNo = b.hotelNo AND g.guestNo = b.guestNo AND h.hotelNo = r.hotelNo AND h.hotelName = "Ritz" AND dateFrom >= "Jan 01, 2001" AND dateTo <= "Dec 31, 2001";

(A) state what information they retrieve
(B) give a corresponding Relational Algebra expression
(C) draw the initial canonical query tree
(D) use heuristic optimization to transform it into the most efficient query tree possible
(E) Compare the cost of at least two different query plans for the given queries. You can make appropriate assumptions for the database statistics - ONLY ANSWER THIS ONE.
(F) Suggest what appropriate indexes should be built upon the given relations to improve the query performance. Justify your answer

Show all intermediate steps and transformations in detail. You can make appropriate assumptions for the database statistics to support your claim.

Answer 1

E)

Let us assume that each table has 100 entries.

Let us assume that the table Hotel has 4 entries associated with hotel name "Ritz".

And, 20 out of 100 bookings satisfy the condition dateFrom >= "Jan 01, 2001" AND dateTo <= "Dec 31, 2001".

In the original query, the number of rows scanned would be 100 * 100 * 100 * 100 = 10⁸

However, in the optimized query, the number of rows scanned would be 100 * 4 * 20 * 100 = 8 * 10⁵

Clearly, the optimized query has saved a lot of resources by scanning significantly lesser rows.