Base Tables^^^^^

describe Room; + --------- +------- --+---+- ----+------ ---+------- + | Field | Type Null | Key | Default Extra + --------- +------- --+---+- ----+------ ---+------- + | roomNo Tint(3) | hotelNo int(4) | type varchar(9) | price decimal(5,2) YES | YES YES YES | NULL NULL, NULL NULL T | | + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - + describe Guest; + -------------- -------------+------+-----+--------+--+ | Field | Type | Null | Key | Default | Extra + ------------ ----------+----+---------+-------+ | | guestNo I int(7) YES | guestName Tvarchar(30) | YES I guestAddress | varchar(50) | YES NULL NULL NULL + - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - + describe Booking; +----------+--------+------+-----+---------+.......+ | Field Type Null | Key | Default | Extra + - - - - - - - - - - + - - - - - - - + - - - - - + - - - - + - - - - - - - - + - - - - - - - + I | hotelNo int(4) | YES I guestNo Tint(7) | YES dateFrom date YES | dateTo date YES | roomNo int(3) | YES NULL NULL NULL NULL | NULL + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - + 7. Consider the following view defined on the Hotel schema: CREATE VIEW RoomBookingCount (hotelNo, roomNo, bookingCount) AS SELECT b.hotelNo, r.room No, COUNT(*) FROM Room r, Booking b WHERE r.roomNo = b.roomNo AND r.hotelNo = b.hotelNo GROUP BY b.hotelNo, r.roomNo; For each of the following queries, state whether the query is valid and for the valid ones show how each of the queries would be mapped onto a query on the underlying base tables. Base your decision on whether a query is valid or invalid on the criteria presented in class and in the book, not on whether the query runs in mysql. All three queries shown below will run in mysql, but they may or may not be valid according to the SQL standard. (a) SELECT hotelNo, roomNo FROM RoomBookingCount WHERE hotelNo = 1; (b) SELECT hotelNo, SUM(bookingCount) FROM RoomBooking Count GROUP BY hotelNo; (C) SELECT * FROM RoomBookingCount ORDER BY bookingCount