Progettazione: attributi sulle releazioni
Interrogazione: is - is not - null

Lezione 8

Problema

Vogliamo creare un database che tenga traccia di tutte le stanze e dei dati relativi alle loro prenotazioni presenti e passate.

Per ciascuna stanza ci interessano le seguenti informazioni: il numero, la metratura, il piano, la tipologia (se singola o matrimoniale) e la quantità massima di letti aggiuntivi possibile.
Inoltre, per ciascuna stanza si vuole poter risalire a tutti i clienti che nel tempo l'hanno occupata.
Ovviamente, si gestisce l'anagrafica di tutti i clienti.

Schema Concettuale: ER

image/svg+xml Layer 1 nome cognome indirizzo numTelefono civico via OSPITI numero metratura piano STANZE tipologia(...) OCCUPANO (1,N) (0,N) maxLettiAggiuntivi lettiAggiunti al dal nome cap RISIEDONO (1,1) (0,N) LOCALITA SONO IN nome PROVINCE (0,N) (1,1) (0,1)

Nota.

Qui il campo lettiAggiuntivi viene forzatamente definito opzionale: quando vengono aggiunti letti avrà un valore significativo, quando no avrà valore NULL. In realtà in questo caso il valore potrebbe tranquillamente essere 0 ma per poter introdurre più tardi in questa lezione dei costrutti per le query lo abbiamo definito opzionale.

Schema logico: relazionale

province (id, nome)

localita (id, nome, cap, idProvincia)

ospiti (id, nome, cognome, via, civico, numTelefono, idLocalita)

stanze (id, numero, metratura, piano, tipologia, maxLettiAggiuntivi)

ospitiSuStanze (id, dal, al, lettiAggiunti*, idOspite, idStanza)

Esempio di istanza

province
id nome
1 Trento
2 Caserta

localita
id nome cap idProvincia
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

ospiti
id nome cognome via civico numTelefono idLocalita
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

stanze
id numero metratura piano tipologia maxLettiAggiuntivi
17 100 20 1 singola 2
18 101 30 1 matrimoniale 1
19 102 30 1 matrimoniale 2
20 200 20 2 singola 2
21 201 30 2 matrimoniale 0
22 202 30 2 matrimoniale 0
23 300 60 3 matrimoniale 0

ospitiSuStanze
id dal al lettiAggiuntivi idOspite idStanza
24 '2010-8-10' '2010-8-20' 1 14 20
25 '2010-8-15' '2010-8-30'   16 23
26 '2011-7-29' '2011-8-10'   16 22
27 '2011-8-12' '2011-8-18' 1 14 17
28 '2011-8-20' '2011-9-5' 2 15 17
29 '2012-7-28' '2012-8-13'   16 21
30 '2012-8-1' '2012-8-15'   12 22

Interrogazione - 1

Mostrare i numeri delle stanze occupate nel tempo da tutti gli ospiti provenienti da 'Caserta'.

select distinct numero
from localita, ospiti, stanze, ospitiSuStanze
where
    (localita.nome = 'Caserta') and
    (localita.id = ospiti.idlocalita) and
    (ospiti.id = ospitiSuStanze.idOspite) and
    (ospitiSuStanze.idStanza = stanze.id)
numero
300
202
100
201

Interrogazione - 2

Mostrare un elenco (per piano) di tutte le stanze che fornisca piano, numero e metratura.

select piano, numero, metratura
from stanze
order by piano
piano numero metratura
1 100 20
1 101 30
1 102 30
2 200 20
2 201 30
2 202 30
3 300 60

Interrogazione - 3

Mostrare nome e cognome di tutti gli ospiti che hanno occupato nel tempo stanze più grandi di 25 mq.

select distinct h.nome, h.cognome
from ospiti as h, stanze as s, ospitiSuStanze as k
where (s.metratura > 25) and (s.id = k.idStanza) and (k.idOspite = h.id)
nome cognome
Raffaele Cennamo
Giorgio Feola

Interrogazione - 4

Mostrare i periodi di permanenza presso la struttura della famiglia 'De Santis'.

select dal as inizio, al as fine
from ospiti, ospitiSuStanze
where (ospiti.cognome = "De Santis") and (ospiti.id = ospitiSuStanze.idOspite)
inizio fine
'2011-8-20' '2011-9-5'

Interrogazione - 5

Mostrare un elenco ordinato di tutte le stanze a cui è stato in qualche circostanza aggiunto un letto. Per ciascuna stanza si mostrino tutte le aggiunte.

select s.numero, s.maxLettiAggiuntivi, k.lettiAggiuntivi
from stanze as s, ospitiSuStanze as k
where (k.idStanza = s.id) and (ospitiSuStanze.lettiAggiuntivi is not NULL)
order by numero
numero maxLettiAggiuntivi lettiAggiuntivi
100 2 1
100 2 2
200 2 1

Nota.

Quando non si riempie un campo opzionale questo contiene il valore speciale NULL. Per verificare che un valore sia uguale o non uguale a null si può usare il costrutto is o is not.

Challenge

Challenge - 1

Qual è il risultato di questa select?

select localita.*
from localita, province
where (localita.idProvincia = province.id) and (province.nome = "Caserta")
order by localita.nome

Challenge - 2

Qual è il risultato di questa select?

select localita.nome, province.nome
from localita, province
where (localita.idProvincia = province.id) order by localita.nome

Challenge - 3

Creare una query che mostri cognome, nome e indirizzo completo di tutti gli ospiti del nostro database.

Challenge - 4

Creare una query che mostri cognome, nome e indirizzo completo di tutti gli ospiti che nel tempo hanno soggiornato in una stanza al secondo piano.

Challenge - 5

Creare una query che restituisca il numero della stanza (o delle stanze) in cui hanno soggiornato i clienti della famiglia "Feola" nell'anno 2010.