
Lezione 4
Problema
Vogliamo gestire un servizio di check-in alberghiero; più nello specifico vogliamo potere abbinare ad ogni nostro ospite (per il quale conserviamo un'anagrafica base) gli estremi del documento di riconoscimento che viene presentato alla reception. Per "estremi del documento" si intende il tipo (che stabiliamo potrà essere soltanto "carta d'identità", "patente", "passaporto"), il numero, la data di scadenza e il comune di emissione.
Schema Concettuale: ER
Nota.
Su ciascun arco di una relazione vi è un etichetta con due valori in parentesi: il primo indica la quantità minima di istanze che partecipano alla relazione (solitamente 0 o 1), il secondo la quantità massima (ha senso distinguere tra 1 e N). E' questo ultimo valore che caratterizza la cardinalità della relazione.
Schema logico: relazionale
ospiti (id, nome, cognome, via, civico, cap, localita, provincia, numTelefono)
documenti (id, tipo, numero, data, comune, idOspite)
Nota.
Nello schema relazionale una relazione 1:1 viene resa introducendo una chiave esterna sull'altra relazione in una delle due relazioni.
Nota.
La chiave esterna è sottolineata con una linea tratteggiata.
Esempio di istanza
ospiti | ||||||||
id | nome | cognome | via | civico | cap | localita | provincia | numTelefono |
---|---|---|---|---|---|---|---|---|
1 | Giorgio | Feola | Modena | 82 | 40100 | Bologna | BO | 3476644551 |
2 | Beatrice | Feola | Modena | 82 | 40100 | Bologna | BO | 3201597532 |
3 | Michela | Zancanella | Genova | 4 | 40026 | Imola | BO | 3255666541 |
documenti | ||||||||
id | tipo | numero | data | comune | idOspite | |||
---|---|---|---|---|---|---|---|---|
4 | Carta d'identità | AB 12345667 | '2022-10-15' | Bologna | 1 | |||
5 | Carta d'Identità | KK 62456739 | '2023-4-19' | Bologna | 2 | |||
6 | Patente | 634839 | '2024-2-7' | Bologna | 3 |
Nota.
Gli id assumono valori stabiliti dal DBMS che ci garantise così che le righe siano tutte diverse: gli id della tabella documento potrebbero tranquillamente partire da 1 (così come da 5000).
Interrogazione - 1
Mostrare un elenco formato da cognome, nome e numero di telefono in ordine alfabetico.
from ospiti
order by cognome, nome
cognome | nome | numTelefono |
---|---|---|
Feola | Beatrice | 3201597532 |
Feola | Giorgio | 3476644551 |
Zancanella | Michela | 3255666541 |
Interrogazione - 2
Mostrare un elenco di tutti gli ospiti indicando cognome, nome, tipo di documento e numero di documento.
from ospiti, documenti
where ospiti.id = documenti.idOspite
cognome | nome | tipo | numero |
---|---|---|---|
Feola | Giorgio | Carta d'identità | AB 12345667 |
Feola | Beatrice | Carta d'identità | KK 62456739 |
Zancanella | Michela | Patente | 634839 |
Interrogazione - 3
Mostrare nome e cognome di tutti gli ospiti che hanno utilizzato una patente di guida come documento di riconoscimento.
from ospiti, documenti
where (ospiti.id = documenti.idOspite) and (documenti.tipo = "Patente")
nome | cognome |
---|---|
Michela | Zancanella |
Interrogazione - 4
Mostrare dati anagrafici e date di scadenza dei documenti di tutti i componenti della famiglia Feola (ordinare la tabella per scadenza del documento).
from ospiti, documenti
where (ospiti.id = documenti.idOspite) and (cognome= "Feola")
order by data
cognome | nome | data |
---|---|---|
Feola | Giorgio | 15/10/2022 |
Feola | Beatrice | 19/04/2023 |
Interrogazione - 5
Mostrare dati anagrafici ed estremi del documento di tutti gli ospiti il cui cognome inizia con la lettera "Z".
Ordinare tutto per nome.
from ospiti, documenti
where (ospiti.id = documenti.idOspite) and (cognome like "Z%")
order by nome
cognome | nome | tipo | numero | data | comune |
---|---|---|---|---|---|
Zancanella | Michela | Patente | 634839 | 7/2/2024 | Bologna |
Interrogazione - 6
Vogliamo recuperare tutti i dati presenti nel database che sono relativi al Sig. Giorgio Feola.
from ospiti, documenti
where (ospiti.id = documenti.idOspite) and (nome = "Giorgio") and (cognome = "Feola")
id | nome | cognome | via | civico | cap | localita | provincia | numTelefono | id | tipo | numero | data | comune | idOspite |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Giorgio | Feola | Modena | 82 | 40100 | Bologna | BO | 3476644551 | 4 | Carta d'identità | AB 12345667 | 15/10/2022 | Bologna | 1 |
Challenge
Challenge - 1
Qual è il risultato di questa select?
from ospiti, documenti
where (ospiti.id = documenti.idOspite)
order by cognome
Challenge - 2
Qual è il risultato di questa select?
from documenti
Challenge - 3
Creare una query che mostri cognome, nome e indirizzo di tutti gli ospiti che hanno un documento emesso dal comune di Firenze.
Challenge - 4
Creare una query che stampi l’elenco di tutti i documenti presenti in archivio in ordine di tipo e di numero: per ciascun documento si indichi il tipo, il numero, la data di scadenza e il nominaitvo dell'intestatario (cognome e nome).
Challenge - 5
Creare una query che stampi l'id di tutti i documenti che non siano delle patenti di guida. Si mostrino i dati dal più grande al più piccolo.
Challenge - 6
Creare una query che stampi il tipo e il numero del documento di tutti gli ospiti il cui nome di battesimo finisce con "o". Si mostrino i dati in ordine di data di scadenza.
Challenge - 7
Creare una query che stampi cognome, nome e numero di documento di tutte le persone che vivono in una località il cui nome finisce per "Valsugana" (o il cui documento sia stato emesso da un comune il cui nome finisce per "Valsugana").
Challenge - 8
Creare una query che stampi in ordine alfabetico cognome e nome di tutte le persone che vivono in una località il cui nome contiene la parola "Valle" e il cui documento sia una patente. Si mostrino i dati in ordine alfabetico (prima per cognome e poi a parità di cognome ordinati per nome).
Challenge - 9
Creare una query che stampi il numero di telefono di tutte le persone il cui documento sia una patente scaduta (possiamo assumere che la data di oggi sia il 15/10/2021).
Challenge - 10
Creare una query che stampi cognome e nome di tutte le persone che vivono in una località diversa da quella che ha emesso il loro documento e che non abitino a Roma.