wrk of database

1. Describe the relations that would be produced by the following relational algebra operations
a) ПhotelNo(σprice>100(Room))
It will produce a single attribute (hotelNo) giving the number of hotels room prices greater 100.
b) σHotel.hotelNo=Room.hotelNo(Hotel X Room)
A join produce of hotel and room containing all the attributes of both.
c) ПhotelName(Hotel Hotel.hotelNo=Room.hotelNo (σprice>100(Room)))
It is a join of hotel and room with the price greater than 100.
d) Guest(σdateTo>’1-June-12’(Booking)
It produces a join of guest and booking with an end date greater than June 12.

2. Provide the equivalent tuple relational calculus expressions for each of the relational algebra queries given in question 1
a) ПhotelNo(σprice>100(Room))

TRC: {R.hotelNo | Room(R) /\ R.price > 100}
b) σHotel.hotelNo=Room.hotelNo(Hotel X Room)
TRC: {H, R | Hotel(H) /\ (R) (Room(R) /\ (H.hotelNo = R.hotelNo))}
c) ПhotelName(Hotel Hotel.hotelNo=Room.hotelNo (σprice>100(Room)))
TRC: {H.hotelName | Hotel(H) /\ (R) (Room(R) /\ (H.hotelNo = R.hotelNo) /\ (R.price > 100))}
d) GuestσdateTo>’1-June-12’(Booking)
TRC: {G.guestNo, G.guestName, G.guestAddress, B.hotelNo, B.dateFrom, B.dateTo, B.roomNo | Guest(G)  (B) (Booking(B) /\ (G.guestNo = B.guestNo)/\(B.dateTo > ‘1-June-12’))}

3. Describe the relations that would be produced by the following tuple relational calculus expressions:
a) {H.hotelName | Hotel(H)  H.city = ‘London’} This will produce a relation containing the names of all hotels in London.
b) {H.hotelName | Hotel(H)  (R) (Room(R)  H.hotelNo  R.hotelNo R.price  80)} This will produce a relation containing the names of all hotels that have a room price above 80.
c) {H.hotelName | Hotel(H)  (B) (G) (Booking(B)  Guest(G)  H.hotelNo  B.hotelNo B.guestNo = G.guestNo  G.guestName = ‘Mike Brown)}
This will produce a relation containing the names of all hotels that have a booking for a guest called Mike Brown.

4. Generate the