Progettazione: relazione 1:N

Interrogazione: costrutto as

Lezione 5

Problema

Vogliamo creare un piccolo database per una biblioteca.
Per ogni libro vogliamo tenere traccia dei seguenti dati: titolo, autore, genere e anno della prima edizione. Inoltre, per ciascun autore vogliamo poter gestire nome, cognome, sesso, vogliamo sapere se è in vita o meno e - se è in vita - quanti anni ha.

Schema Concettuale: ER

Nota.

L'attributo età è un attributo calcolato e, per questa ragione, sarebbe un errore inserilo nel db. Pertanto inseriamo l'attributo dataDiNascita.

Schema logico: relazionale

autori (id, nome, cognome, sesso, dataDiNascita, inVita)

libri (id, titolo, genere, anno, idAutore)

Nota.

Nello schema logico una associazione 1:N viene resa aggiungendo una chiave esterna alla relazione che mappa l'entità dal lato 1.

Esempio di istanza

id	nome	cognome	sesso	dataDiNascita	inVita
autori
1	Niccolò	Ammaniti	M	'1966-09-25'	true
2	William	Shakespeare	M	'1564-04-26'	false
3	Fabio	Volo	M	'1972-06-23'	true

id	titolo	genere	anno	idAutore
libri
4	Esco a fare due passi	Romanzo	2001	3
5	Un posto nel mondo	Romanzo	2006	3
6	Il giorno in più	Romanzo	2007	3
7	La strada verso casa	Romanzo	2013	3
8	Amleto	Tragedia	1603	2
9	Sogno di una notte di mezza estate	Commedia	1596	2
10	Macbeth	Tragedia	1623	2
11	Romeo e Giulietta	Tragedia	1597	2
12	Otello	Tragedia	1622	2
13	Io non ho paura	Romanzo	2003	1
14	Io e te	Romanzo	2012	1
15	Come Dio comanda	Romanzo	2008	1
16	L'ultimo capodanno	Romanzo	1998	1

Interrogazione - 1

Mostrare un elenco di tutti i romanzi presenti nel db.
Per ciascun romanzo si mostri il titolo ed il nome e cognome dell'autore.

select titolo as romanzo, nome, cognome
from libri, autori
where (libri.idAutore = autori.id) and (libri.genere = 'Romanzo')

romanzo	nome	cognome
Esco a fare due passi	Fabio	Volo
Un posto nel mondo	Fabio	Volo
Il giorno in più	Fabio	Volo
La strada verso casa	Fabio	Volo
Io non ho paura	Niccolò	Ammaniti
Io e te	Niccolò	Ammaniti
Come Dio comanda	Niccolò	Ammaniti
L'ultimo capodanno	Niccolò	Ammaniti

Interrogazione - 2

Mostrare un elenco di tutte le opere degli autori ancora in vita presenti nel db.
Per ciascuna opera si mostri il titolo ed il nome e cognome dell'autore.

select titolo as opera, nome, cognome
from libri, autori
where (libri.idAutore = autori.id) and (autori.inVita = true)

opera	nome	cognome
Esco a fare due passi	Fabio	Volo
Un posto nel mondo	Fabio	Volo
Il giorno in più	Fabio	Volo
La strada verso casa	Fabio	Volo
Io non ho paura	Niccolò	Ammaniti
Io e te	Niccolò	Ammaniti
Come Dio comanda	Niccolò	Ammaniti
L'ultimo capodanno	Niccolò	Ammaniti

Interrogazione - 3

Mostrare titolo e anno di tutti i libri prodotti dal 1600 al 2000 in ordine cronologico.

select titolo, anno
from libri
where (anno >= 1600) and (anno <= 2000)
order by anno

titolo	anno
Amleto	1603
Otello	1622
Macbeth	1623
L'ultimo capodanno	1998

Interrogazione - 4

Mostrare nome e cognome di tutti gli autori che hanno prodotto un'opera tra il 1600 ed il 2000, in ordine alfabetico.

select distinct a.cognome, a.nome
from autori as a, libri as l
where (a.id = l.idAutore) and (l.anno >= 1600) and (l.anno <= 2000)
order by a.cognome, a.nome

a.cognome	a.nome
Ammaniti	Niccolò
Shakespeare	William

Nota.

Il costrutto as si può usare anche per rinominare una tabella coinvolta nella query.

Interrogazione - 5

Mostrare tutti i dati dei libri di autrici donne.

select libri.*
from libri as l, autori as a
where (a.id = l.idAutore) and (a.sesso = 'F')

id	titolo	genere	anno	idAutore

Challenge

Challenge - 1

Qual è il risultato di questa select?

select cognome, cognome, cognome
from autori
where (nome = 'Fabio')
order by cognome

Challenge - 2

Qual è il risultato di questa select?

select l.titolo, a.cognome as autore
from autori as a, libri as l
where (a.id = l.idAutore)
order by l.titolo

Challenge - 3

Qual è il risultato di questa select?

select l.titolo
from autori as a, libri as l
where (a.id = l.idAutore) and (a.cognome = 'Ammaniti')
order by l.anno

Nota.

Notiamo che qui è la prima volta che nella clausola order by compare un campo che non è coinvolto dalla clausola select: specifichiamo che sebbene sia improbabile trovarsi in una situazione simile tecnicamente è lecito.

Challenge - 4

Creare una query che abbini a cognome, nome e data di nascita di tutti gli autori presenti nel db, l'anno di pubblicazione di ciascuna delle loro opere.

Challenge - 5

Creare una query che restituisca tutti i generi presenti nel db in ordine alfabetico.

Challenge - 6

Creare una query che generi una tabella con gli anni di pubblicazione delle opere degli autori ancora in vita. Gli anni devono comparire in ordine decrescente.

Challenge - 7

Creare una query che riporti titolo e autore di tutte le opere che non siano delle tragedie.

Challenge - 8

Creare una query che riporti il titolo di tutte le opere recenti di autori giovani. Nello specifico vogliamo le opere edite da 2020 in poi di autori nati dal 2000 in poi.