Progettazione: relazione 1:1 - chiave esterna
Interrogazione: confronto con pattern

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.

image/svg+xml Layer 1 nome cognome indirizzo numTelefono cap località civico via provincia OSPITI tipo(...) numero data DOCUMENTI comune HANNO (1,1) (1,1)

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.

select cognome, nome, numTelefono
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.

select cognome, nome, tipo, numero
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.

select nome, cognome
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).

select cognome, nome, data
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.

select cognome, nome, tipo, numero, data, comune
from ospiti, documenti
where (ospiti.id = documenti.idOspite) and (cognome like "Z%")
order by nome
cognome nome tipo numerodatacomune
ZancanellaMichelaPatente6348397/2/2024Bologna

Interrogazione - 6

Vogliamo recuperare tutti i dati presenti nel database che sono relativi al Sig. Giorgio Feola.

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

select *
from ospiti, documenti
where (ospiti.id = documenti.idOspite)
order by cognome

Challenge - 2

Qual è il risultato di questa select?

select distinct documenti.comune
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.