Q1. Consider a relational database regarding hotels, customers (or guests) and their bookings which is maintained by an online hotel booking company. The database comprises of the given tables (primary keys are underlined):
Hotel (hId, hName, hAddress, hCity)
Guest( gId, gName, gAddress, gCity)
Room( hid, roomNo, type, price )
Booking(gId, hId, roomNo, fromDate, year, noOfDays)
Here, hId and gId are identifiers for the hotels and guests and the Booking relation points out that a guest booked a hotel room for a specified number of days (noOfDays) starting from Date of a given year. For instance, a tuple < g12345, h5555, 220, Jan05, 2005, 8 > in Booking points out that guest g12345 booked room 220 of the h5555 hotel for 8 days starting on Jan 5, 2005.
A) Write down a relational algebra expression which returns the ids of the hotels located in the Vancouver which were not booked at all in year 2005.
B) Write down a relational algebra expression which returns the ids of the guests who have booked at least one room of type “suite” in every hotel situated in Vancouver.
C) Write down a Tuple Relational Calculus (TRC) query which finds the ids and names of the hotels for which every one of our guests had made a booking throughout the year 2004.
Q2. Differentiate between the term RDBMS and DBMS.