DB design: relationship attributes
Query: is - is not - null

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

Layer 1 name surname address TelephNo streetNo street GUEST number measurement floor ROOM type(...) STAY N N maxAddedBeds addedBeds - to from name postCode LIVE N 1 CITY LOCATED name PROVINCE 1 N

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.

select distinct number
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.

select floor, number, 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.

select distinct g.name, g.surname
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.

select from as start, to as end
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.

select r.number, r.maxAddedBeds, s.addedBeds
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?

select city.*
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?

select city.name, province.name
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.