Using the Hotel schema given at the start of the Exercises at the end of Chapter 4, determine whether the following queries are semantically correct:

Question

(a) SELECT r.type, r.price
FROM Room r, Hotel h
WHERE r.hotel_number = h.hotel_number AND
h.hotel_name = ‘Grosvenor Hotel’ AND
r.type > 100;
(b) SELECT g.guestNo, g.name
FROM Hotel h, Booking b, Guest g
WHERE h.hotelNo = b.hotelNo AND h.hName = ‘Grosvenor Hotel’;
(c) SELECT r.roomNo, h.hotelNo
FROM Hotel h, Booking b, Room r
WHERE h.hotelNo = b.hotelNo AND h.hotelNo = ‘H21’ AND
b.roomNo = r.roomNo AND type = ‘S’ AND b.hotelNo = ‘H22’;

Answer 1

(a) SELECT r.type, r.price
FROM Room r, Hotel h
WHERE r.hotel_number = h.hotel_number AND
h.hotel_name = ‘Grosvenor Hotel’ AND
r.type > 100;
Not semantically correct: hotel_number and hotel_name are not the names of
attributes in the schema; type is character string and so cannot be compared with an
integer value (100).
(b) SELECT g.guestNo, g.name
FROM Hotel h, Booking b, Guest g
WHERE h.hotelNo = b.hotelNo AND h.hName = ‘Grosvenor Hotel’;
Not semantically correct: g.name and h.hName not in schema; Guest table not
connected to remainder of query.
(c) SELECT r.roomNo, h.hotelNo
FROM Hotel h, Booking b, Room r
WHERE h.hotelNo = b.hotelNo AND h.hotelNo = ‘H21’ AND
b.roomNo = r.roomNo AND type = ‘S’ AND b.hotelNo = ‘H22’;
Not semantically correct: hotelNo cannot be both H21 in Hotel and H22 in Booking.