The following tables form part of a database held in a relational DBMS: Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)
where Hotel contains hotel details and hotelNo is the primary key;
Room contains room details for each hotel and (roomNo, hoteINo) forms the primary key;
Booking contains details of bookings and (hoteINo, guestNo, dateFrom) forms the primary key;
Guest contains guest details and guestNo is the primary key.
4.8 Identify the foreign keys in this schema. Explain how the entity and referential integrity rules apply to these relations.
4.9 Produce some sample tables for these relations that observe the relational integrity rules. Suggest some general
constraints that would be appropriate for this schema.
4.10 Analyze the RDBMSs that you are currently using. Determine the support the system provides for primary keys,
alternate keys, foreign keys, relational integrity, and views.
4.11 Implement the above schema in one of the RDBMSs you currently use. Generate two
For Exercises 6.7–6.28, use the Hotel schema defined at the start of the Exercises at the end of Chapter 4.
Simple queries
6.7 List full details of all hotels.
6.8 List full details of all hotels in London.
6.9 List the names and addresses of all guests living in London, alphabetically ordered by name.
6.10 List all double or family rooms with a price below £40.00 per night, in ascending order of price.
6.11 List the bookings for which no dateTo has been specified.
Aggregate functions
6.12 How many hotels are there?
6.13 What is the average price of a room?
6.14 What is the total revenue per night from all double rooms?
6.15 How many different guests have made bookings for August?
Subqueries and joins
6.16 List the price and type of all rooms at the Grosvenor Hotel.
6.17 List all guests currently staying at the Grosvenor Hotel.
6.18 List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the
room is occupied.
6.19 What is the total income from bookings for the Grosvenor Hotel today?
6.20 List the rooms that are currently unoccupied at the Grosvenor Hotel.
6.21 What is the lost income from unoccupied rooms at the Grosvenor Hotel?
Grouping
6.22 List the number of rooms in each hotel.
6.23 List the number of rooms in each hotel in London.
6.24 What is the average number of bookings for each hotel in August?
6.25 What is the most commonly booked room type for each hotel in London?
6.26 What is the lost income from unoccupied rooms at each hotel today?
Populating tables
6.27 Insert rows into each of these tables.
6.28 Update the price of all rooms by 5%.