Consider the following relation schemas. The primary keys are underlined. All attributes are of type string...

Transcribed Image Text:

Consider the following relation schemas. The primary keys are underlined. All attributes are of type string if not indicated otherwise. Use multi-step and multi-line queries to ease the formulation of queries. Use the rename operator p to give intermediate query results a name (some textbooks use the equivalent notation). Aggregate functions as you can find them defined in some textbooks are not allowed, neither in homework assignments nor in exams. • Hotels (hID, name, location, phone, avgRating: integer) • Rooms(rID, HID, type, price, availability: boolean) • Bookings(blD, rID, hID, gID, startDate, endDate, totalCost: integer) • Guests(gID, name, phone, address) • Amenities (amenityID, name, description) • Hotel Amenities (hID, amenityID) •Hotel Reviews (hID, gID, rating: integer, review) (a) Find the names of hotels, their available room types, and prices. The hotels should be located in Orlando and should have amenities such as free breakfast and a pool (amenity name) available. (b) Find the names of guests who have made more than one booking at HotelA (unique name). (c) Find the names of guests who have made a booking with HotelA (unique name) but have not reviewed the hotel yet. (d) Find the reviews and ratings of HotelA (unique name) given by either GuestA or GuestB (unique names). (e) Find the names of hotels in Orlando with the highest average rating. Display the hotel names and ratings together. (f) Find the names of guests who have booked every hotel in Orlando. Consider the following relation schemas. The primary keys are underlined. All attributes are of type string if not indicated otherwise. Use multi-step and multi-line queries to ease the formulation of queries. Use the rename operator p to give intermediate query results a name (some textbooks use the equivalent notation). Aggregate functions as you can find them defined in some textbooks are not allowed, neither in homework assignments nor in exams. • Hotels (hID, name, location, phone, avgRating: integer) • Rooms(rID, HID, type, price, availability: boolean) • Bookings(blD, rID, hID, gID, startDate, endDate, totalCost: integer) • Guests(gID, name, phone, address) • Amenities (amenityID, name, description) • Hotel Amenities (hID, amenityID) •Hotel Reviews (hID, gID, rating: integer, review) (a) Find the names of hotels, their available room types, and prices. The hotels should be located in Orlando and should have amenities such as free breakfast and a pool (amenity name) available. (b) Find the names of guests who have made more than one booking at HotelA (unique name). (c) Find the names of guests who have made a booking with HotelA (unique name) but have not reviewed the hotel yet. (d) Find the reviews and ratings of HotelA (unique name) given by either GuestA or GuestB (unique names). (e) Find the names of hotels in Orlando with the highest average rating. Display the hotel names and ratings together. (f) Find the names of guests who have booked every hotel in Orlando.

Answer rating: 100% (QA)

a To find the names of hotels in Orlando with available room types and prices that have amenities li... View the full answer