Lesson 4

Task

Suppose you have been asked to manage the check-in system of a hotel. The hotel database stores the basic personal information of each guest but you also want to be able to match each guest to the number of his or her personal identity document.

Conceptual data model: ER

Logical data model: relational

guest (id, name, surname, street, streetNo, PostCode, city, province, telephNo)

document (id, type, number, expiryDate, municipality, idOspite)

Note.

In a relational data model, a 1-to-1 relationship is represented by introducing a foreign key in either one of the relations. The foreign key in a relation refers to the primary key in the other relation.

Note.

A foreign key is always underlined with a dotted line.

AN INSTANCE OF DATABASE

id	name	surname	street	streetNo	PostCode	city	province	telephNo
guest
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	type	number	data	municipality	idGuest
Document
4	Identity Card	AB 12345667	15/10/2022	Bologna	1
5	Identity Card	KK 62456739	19/04/2023	Bologna	2
6	Driving Licence	634839	7/2/2024	Bologna	3

Query - 1

Extract a list composed of surname, name and telephone number in alphabetical order.

select surname, name, telephNo
from guest
order by surname

surname	name	telephNo
Feola	Giorgio	3476644551
Feola	Beatrice	3201597532
Zancanella	Michela	3255666541

Query - 2

Find a list of the following details for all the guests: surname, name, type of document and number of document.

select surname, name, type, number
from guest, document
where guest.id = document.idOspite

surname	name	type	number
Feola	Giorgio	Identity Card	AB 12345667
Feola	Beatrice	Identity Card	KK 62456739
Zancanella	Michela	Driving Licence	634839

Query - 3

Find name and surname of all the guests who have been registered with their driving licence.

select name, surname
from guest, document
where (guest.id = document.idOspite) and (document.type = "Driving Licence")

name	surname
Michela	Zancanella

Query - 4

Extract the personal data of all the the members of the Feola family. Find also the expiry date of their documents.

select surname, name, data
from guest, document
where (guest.id = document.idOspite) and (surname= "Feola")
order by data

surname	name	data
Feola	Giorgio	15/10/2022
Feola	Beatrice	19/04/2023

Query - 5

Find the personal data and document numbers of all the guests whose surname begins with "Z".

select surname, name, province, type, number
from guest, document
where (guest.id = document.idOspite) and (surname LIKE "Z%")
order by name

surname	name	province	type	number
Zancanella	Michela	BO	Driving Licence	634839

Challenge

Challenge - 1

What table will the query below generate?

select *
from guest, document
where (guest.id = document.idOspite)
order by surname

Challenge - 2

What table will the query below generate?

select distinct document.municipality
from document

Challenge - 3

Create a query which will generate a table comprising the fields surname, name and address for all the guests whose document was released by the municipality of Firenze.

Challenge - 4

Create a query which will generate a table of all the documents stored in the database ordered by type and number. For each document find type, number, expiry date and the name of the bearer.

Challenge - 5

Extract a list of all the documents which are not driving licences.

Challenge - 6

Find the document numbers of all the guests whose first name ends with "o".