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.

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

ospite (id, nome, cognome, via, civico, cap, località, provincia, numTelefono)

documento (id, tipo, numero, dataScadenza, 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

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

id	tipo	numero	data	comune	idOspite
documento
4	Carta d'identità	AB 12345667	15/10/2022	Bologna	1
5	Carta d'Identità	KK 62456739	19/04/2023	Bologna	2
6	Patente	634839	7/2/2024	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 ospite
order by cognome

cognome	nome	numTelefono
Feola	Giorgio	3476644551
Feola	Beatrice	3201597532
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 ospite, documento
where ospite.id = documento.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 ospite, documento
where (ospite.id = documento.idOspite) and (documento.tipo = "Patente")

nome	cognome
Michela	Zancanella

Interrogazione - 4

Mostrare dati anagrafici e date di scadenza dei documenti di tutti i componenti della famiglia Feola (in ordine crescente)

select cognome, nome, data
from ospite, documento
where (ospite.id = documento.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".
Ordina tutto per nome.

select cognome, nome, tipo, numero, data, comune
from ospite, documento
where (ospite.id = documento.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.

select *
from ospite, documento
where (ospite.id = documento.idOspite) and (nome = "Giorgio") and (cognome = "Feola")

id	nome	cognome	via	civico	cap	località	provincia	numTelefono	id	tipo	numero	data	comune	idOspite
1	Giorgio	Feola	Modena	82	40100	Bologna	BO	3476644551	4	Identity Card	AB 12345667	15/10/2022	Bologna	1

Challenge

Challenge - 1

Qual è il risultato di questa select?

select *
from ospite, documento
where (ospite.id = documento.idOspite)
order by cognome

Challenge - 2

Qual è il risultato di questa select?

select distinct documento.comune
from documento

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.

Challenge - 5

Creare una query che stampi l'id di tutti i documenti che non siano delle patenti di guida.

Challenge - 6

Creare una query che stampi gli estremi del documento di tutti gli ospiti il cui nome di battesimo finisce con "o".