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 OSPITE numero metratura piano STANZA tipologia(...) OCCUPA (1,N) (0,N) maxLettiAggiuntivi lettiAggiunti al dal nome cap RISIEDE (1,1) (0,N) LOCALITA' SI TROVA IN nome PROVINCIA (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

provincia (id, nome)

località (id, nome, cap, idProvincia)

ospite (id, nome, cognome, via, civico, numTelefono, idLocalità)

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

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

Esempio di istanza

provincia
id nome
1 Trento
2 Caserta

località
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

ospite
id nome cognome via civico numTelefono idLocalità
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

stanza
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

occupa
id dal al lettiAggiuntivi idOspite idStanza
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

Interrogazione - 1

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

select distinct numero
from località, ospite, stanza, occupa
where
    (località.nome = 'Caserta') and
    (località.id = ospite.idLocalità) and
    (ospite.id = occupa.idOspite) and
    (occupa.idStanza = stanza.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 stanza
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 ospite as h, stanza as s, occupa as k
where (h.id = k.idOspite) and (s.id = k.idStanza) and (s.metratura > 25)
nome cognome
Raffaele Cennamo

Interrogazione - 4

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

select dal as inizio, al as fine
from ospite, occupa
where (ospite.cognome = "De Santis") and (ospite.id = occupa.idOspite)
inizio fine
20/08/2011 5/09/2011

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 stanza as s, occupa as k
where (k.idStanza = s.id) and (occupa.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 località.*
from località, provincia
where (località.idProvincia = provincia.id) and (provincia.nome = "Caserta")
order by località.nome

Challenge - 2

Qual è il risultato di questa select?

select località.nome, provincia.nome
from località, provincia
where (località.idProvincia = provincia.id) order by località.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.