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

image/svg+xml Layer 1 nome cognome sesso (...) dataDiNascita AUTORE titolo genere(...) anno LIBRO SCRIVE (1,N) (1,1) inVita

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

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

libro (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

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

libro
id titolo genere anno idAutore
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 libro, autore
where (libro.idAutore = autore.id) and (libro.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 libro, autore
where (libro.idAutore = autore.id) and (autore.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 libro
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 autore as a, libro 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 libro.*
from libro as l, autore 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 autore
where (nome = 'Fabio')
order by cognome

Challenge - 2

Qual è il risultato di questa select?

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

Challenge - 3

Qual è il risultato di questa select?

select l.titolo
from autore as a, libro 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.