CSC 443 Data Base Management Systems

Dr. R. M. Siegfried

Assignment 10 - The database specified on p. 108 and p. 172-3/6.7-6.18, 6.22, 6.23

Due Wednesday, November 20, 2013

Using the database table structure that we have already seen in Assignment #9, create the database tables in MySQL using the organization shown in Assignment #9, and be prepared to justify the datatype choices that you have made as well as the constraints that you have chosen.

Afterward, populate the tables with the data below and write and run the MySQL queries in the exercises:

6.7	List full details of all hotels.
6.8	List full details of all hotels in London.
6.9	List the names and address of all guests living in London.
6.10	List all double rooms or suites with a price between £40.00 per night, in ascending order of price.
6.11	List the bookings for which no dateTo has been specified.
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 booking for August?
6.16	List the price and type of all rooms at the Grosvenor Hotel.
6.17	List all guests currently staying (on November 1, 2013) at the Grosvenor Hotel.
6.18	List the detail of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied.
6.22	List the number of rooms in each hotel.
6.23	List the number of rooms in each hotel in London.

The Database

Hotel
hotelNo	hotelName	city
101	Grosvenor	London
102	Swallow	London
201	Waldorf Astoria	New York
202	Plaza	New York
401	Sofitel	Minneapolis

Room
roomNo	hotelNo	Type	Price
201	101	Single	30
202	101	Double	40
203	101	Single	30
204	101	Double	40
205	101	Double	40
206	101	Suite	100
201	102	Single	25
202	102	Double	30
203	102	Single	25
204	102	Double	30
205	102	Double	30
206	102	Suite	80
201	201	Single	80
202	201	Double	100
203	201	Single	80
204	201	Double	100
205	201	Double	100
206	201	Suite	300

Guest
guestNo	guestName	guestAddress
40	John Smith	10 E 42 St, New York, NY
41	Mary Jones	110 Elm St, Garden City, NY
52	Thomas Smith	40-40 172 St, Flushing, NY
53	Joseph Schmo	1052 E 85 St, Brooklyn, NY
60	Ima Sample	3010 Broadway, New York, NY
61	Shesa Sample	2510 Utica Ave, Minneapolis, MN
62	George Seaver	41 Arschloch Rd, Grenewich, CT
63	Jerome Koosman	54 Morris Ave, Stillwater, MN

Booking
hotelNo	guestNo	dateFrom	dateTo	roomNo
101	40	10/12/13	10/14/13	201
101	52	10/12/13	10/13/13	204
101	63	10/24/13	10/27/13	206
201	40	10/24/13	10/26/13	205
201	40	10/31/13	11/2/13	203
201	52	10/28/13	10/31/13	201

[Back to the Assignment Page]