Lesson 8
Task
We want to design a database for keeping track of all the rooms in a hotel and of current and past booking information.
For each room the data that we want to store is: number, measurement (in square metres),
floor, type (single or double), and the maximum number of beds that can be added.
Moreover, for each room we want to be able to retrieve
the list of all the guests that have ever stayed in it.
Obviously, our database also stores the personal information of all our guests.
Conceptual data model: ER
Logical data model: relational
province (id, name)
city (id, name, postCode, idProvince)
guest (id, name, surname, street, streetNo, TelephNo, idCity)
room (id, number, measurement, floor, type, maxAddedBeds)
stay (id, from, to, addedBeds-, idGuest, idRoom)
An Instance of database
province | |||||||||
id | name | ||||||||
---|---|---|---|---|---|---|---|---|---|
1 | Trento | ||||||||
2 | Caserta |
city | |||||
id | name | postCode | idProvince | ||
---|---|---|---|---|---|
3 | Pergine Valsugana | 38057 | 1 | ||
4 | Borgo Valsugana | 38051 | 1 | ||
5 | Arco | 38062 | 1 | ||
6 | Mezzolombardo | 38017 | 1 | ||
7 | Madonna di Campiglio | 38084 | 1 | ||
8 | Caserta | 81100 | 2 | ||
9 | Maddaloni | 81024 | 2 | ||
10 | Casagiove | 81022 | 2 | ||
11 | San Nicola La Strada | 81020 | 2 |
guest | |||||||||
id | name | surname | street | streetNo | TelephNo | idCity | |||
---|---|---|---|---|---|---|---|---|---|
12 | Giorgio | Feola | Modena | 82 | 3476644551 | 4 | |||
13 | Beatrice | Feola | Roma | 182 | 3201597532 | 6 | |||
14 | Michela | Zancanella | Genova | 4 | 3255666541 | 5 | |||
15 | Stefano | De Santis | Machiavelli | 40 | 3957698541 | 8 | |||
16 | Raffaele | Cennamo | Milano | 1 | 0823547858 | 8 |
room | |||||
id | number | measurement | floor | type | maxAddedBeds |
---|---|---|---|---|---|
17 | 100 | 20 | 1 | single | 2 |
18 | 101 | 30 | 1 | double | 1 |
19 | 102 | 30 | 1 | double | 2 |
20 | 200 | 20 | 2 | single | 2 |
21 | 201 | 30 | 2 | double | 0 |
22 | 202 | 30 | 2 | double | 0 |
23 | 300 | 60 | 3 | double | 0 |
stay | |||||
id | from | to | addedBeds | idGuest | idRoom |
---|---|---|---|---|---|
24 | 10/08/2010 | 20/08/2010 | 1 | 14 | 20 |
25 | 15/08/2010 | 30/08/2010 | 16 | 23 | |
26 | 29/07/2011 | 10/08/2011 | 16 | 22 | |
27 | 12/08/2011 | 18/08/2011 | 1 | 14 | 17 |
28 | 20/08/2011 | 5/09/2011 | 2 | 15 | 17 |
29 | 28/07/2012 | 13/08/2012 | 16 | 21 | |
30 | 1/08/2012 | 15/08/2012 | 12 | 22 |
Query - 1
List the numbers of the rooms that have ever been occupied by guests from Caserta.
from city, guest, room, stay
where
(city.name = "Caserta") and
(city.id = guest.idCity) and
(guest.id = stay.idGuest) and
(stay.idRoom = room.id)
number |
---|
300 |
202 |
100 |
201 |
Query - 2
Generate a table which contains all the rooms ordered by floor. For each room display number and measurement.
from room
order by floor
floor | number | measurement |
---|---|---|
1 | 100 | 20 |
1 | 101 | 30 |
1 | 102 | 30 |
2 | 200 | 20 |
2 | 201 | 30 |
2 | 202 | 30 |
3 | 300 | 60 |
Query - 3
List name and surname of all the guest who have ever occupied a room larger than 25 sq m.
from guest as g, room as r, stay as s
where (g.id = s.idGuest) and (s.idRoom = r.id) and (r.measurement > 25)
name | surname |
---|---|
Raffaele | Cennamo |
Query - 4
Extract a list of all the periods when the guests "De Santis" stayed at the hotel.
from guest, stay
where (guest.surname = "De Santis") and (guest.id = stay.idGuest)
start | end |
---|---|
20/08/2011 | 5/09/2011 |
Query - 5
Retrieve a list of all the rooms where an extra bed was ever added. For each room display the number of beds that were added.
from room as r, stay as s
where (s.idRoom = r.id) and (s.addedBeds is not NULL)
order by number
number | maxAddedBeds | addedBeds |
---|---|---|
100 | 2 | 1 |
100 | 2 | 2 |
200 | 2 | 1 |
Note.
When an optional field is not filled in, it contains the special value NULL. In order to verify whether a field is or is not null you can use the keyword is or is not.
Challenge
Challenge - 1
What result will the query below give?
from city, province
where (city.idProvince = province.id) and (province.name = "Caserta")
order by city.name
Challenge - 2
What result will the query below give?
from city, province
where (city.idProvince = province.id) order by city.name
Challenge - 3
Create a query which will give the surname, name and full address of all the guests stored in our database.
Challenge - 4
Create a query which will give a list of all the guests (surname, name and full address) who have ever occupied a room on the second floor.
Challenge - 5
Create a query which will retrieve the number of the room(s) that the guest "Feola" occupied in 2010.