Question

** Please use elational algebra expressions **

HOTEL (hotelNo, hotelName, city)

ROOM (roomNo, hotelNo, type, price)

RESERVATION (hotelNo, guestNo, dateFrom, dateTo, roomNo)

GUEST (guestNo, guestName, guestAddress)

Consider the same schema from question 1 and express the following queries using relational algebra expressions:

i) List the price and type of all rooms at the “Governor Hotel”.

ii) List all guests who stayed at the “Governor Hotel” between 31^st December 2018 (check-in) and 1^stJanuary 2019 (check-out).

iii) List all room numbers and types in “Governor Hotel”.

Answer 1

Ans1: π _{R.Price, R.type} σ _{R.hotelNo = H.hotelNo and H.hotelName = 'Governor Hotel'} ρ _R Orders ⋈ ρ _H Customers

Ans 2: π _G.guestName σ_{G.guestNo=R.guestNo and R.hotelNo=H.hotelNo and H.hotelName='Governor Hotel' and R.dateFrom='2018-12-31' and R.dateTo='2019-01-01'} ρ _G Guest ⋈ ρ _R Reservation ⋈ ρ _H Hotel

Ans 3: π _R.roomNo σ _{R.hotelNo = H.hotelNo and H.hotelName = 'Governor Hotel'} ρ _R Orders ⋈ ρ _H Customers