Disegnare un database, le soluzioni
Lo schema di riferimento, il ragionamento, le trappole
Per ogni esercizio, sempre lo stesso ordine: in italiano → lo schema → le trappole. Per i più difficili, anche il passo per passo.
Questa volta le strade giuste sono molte più di una: i test guardano i nomi del contratto, le chiavi e il comportamento, non come hai scritto il file. Puoi aver scelto TEXT dove qui c'è TEXT, VARCHAR(80) dove qui non c'è limite, aver aggiunto una colonna note: va bene. Quello che conta è il ragionamento: confrontalo con quello qui sotto, e leggi comunque le trappole.
Le soluzioni complete
La stessa cartella, con il codice al posto di pass. Aprila dopo averci provato: i test sono gli stessi.
La palestra
1 · La biblioteca di Largo Lista
In italiano.
- Tre cose, tre tabelle:
libri,lettori,prestiti. - Ognuna ha il suo
id(SERIAL PRIMARY KEY). - Un prestito riguarda un libro e un lettore, ma un libro (e un lettore) ha molti prestiti: le due frecce partono da
prestiti, che è dalla parte del molti. prestitiha unidsuo: lo stesso lettore può riprendere lo stesso libro, in un altro giorno.- Un vincolo per ogni dato che può essere sbagliato: titolo e email obbligatori, email unica, un
CHECKsulle date.restituito_ilnon èNOT NULL: vuoto vuol dire «ancora fuori».
DROP TABLE IF EXISTS prestiti, lettori, libri;
CREATE TABLE libri (
id SERIAL PRIMARY KEY,
titolo TEXT NOT NULL,
autore TEXT
);
CREATE TABLE lettori (
id SERIAL PRIMARY KEY,
nome TEXT NOT NULL,
cognome TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
);
CREATE TABLE prestiti (
id SERIAL PRIMARY KEY,
libro_id INTEGER NOT NULL REFERENCES libri(id),
lettore_id INTEGER NOT NULL REFERENCES lettori(id),
prestato_il DATE NOT NULL,
restituito_il DATE,
CHECK (restituito_il >= prestato_il)
);Il DROP TABLE va dalla figlia alle madri: prestiti per prima, perché punta alle altre due. Con \d prestiti vedi tutto quello che hai deciso:
Table "public.prestiti"
Column | Type | Collation | Nullable | Default
---------------+---------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('prestiti_id_seq'::regclass)
libro_id | integer | | not null |
lettore_id | integer | | not null |
prestato_il | date | | not null |
restituito_il | date | | |
Indexes:
"prestiti_pkey" PRIMARY KEY, btree (id)
Check constraints:
"prestiti_check" CHECK (restituito_il >= prestato_il)
Foreign-key constraints:
"prestiti_lettore_id_fkey" FOREIGN KEY (lettore_id) REFERENCES lettori(id)
"prestiti_libro_id_fkey" FOREIGN KEY (libro_id) REFERENCES libri(id)Se restituito_il è vuoto, il CHECK non dice «falso»: un confronto con un vuoto non è né vero né falso, e PostgreSQL lascia passare la riga. È proprio quello che serve.
Le trappole.
| Il codice sbagliato | Cosa succede | Il test rosso |
|---|---|---|
libro_id INTEGER NOT NULL e lettore_id INTEGER NOT NULL, senza REFERENCES | Sono due numeri qualunque: \d prestiti non ha più le righe Foreign-key constraints, e un prestito può puntare a un libro che non c'è | test_es01_le_chiavi, test_es01_un_prestito_non_punta_nel_vuoto |
restituito_il DATE NOT NULL | Un libro appena prestato non si può registrare: non esiste ancora una data di ritorno | test_es01_un_prestito_non_punta_nel_vuoto, test_es01_i_libri_ancora_fuori |
Nessun CHECK sulle date | Un libro torna prima di essere uscito: il database accetta e la contabilità è assurda | test_es01_non_si_restituisce_prima_di_aver_preso |
email TEXT NOT NULL senza UNIQUE | Due lettori con la stessa email: chi riceve il sollecito? | test_es01_la_mail_e_unica |
2 · Il coro della Linea Menta
In italiano.
- Quattro cose, quattro tabelle:
coristi,brani,prove,presenze. - Un brano ha molte prove, una prova ha un brano:
prove.brano_id. - Un corista viene a molte prove, e a una prova vengono molti coristi: serve la tabella ponte
presenze, con le due frecce. - Nessuno può essere presente due volte alla stessa prova: la chiave primaria è la coppia (
corista_id,prova_id), non serve unid. - Il ritardo è una colonna della coppia: non è del corista né della prova, è di quel corista a quella prova.
DROP TABLE IF EXISTS presenze, prove, brani, coristi;
CREATE TABLE coristi (
id SERIAL PRIMARY KEY,
nome TEXT NOT NULL,
cognome TEXT NOT NULL,
voce TEXT NOT NULL CHECK (voce IN ('soprano', 'contralto', 'tenore', 'basso'))
);
CREATE TABLE brani (
id SERIAL PRIMARY KEY,
titolo TEXT NOT NULL
);
CREATE TABLE prove (
id SERIAL PRIMARY KEY,
brano_id INTEGER NOT NULL REFERENCES brani(id),
giorno DATE NOT NULL
);
CREATE TABLE presenze (
corista_id INTEGER NOT NULL REFERENCES coristi(id),
prova_id INTEGER NOT NULL REFERENCES prove(id),
ritardo_min INTEGER NOT NULL DEFAULT 0 CHECK (ritardo_min >= 0),
PRIMARY KEY (corista_id, prova_id)
);L'ordine del DROP: presenze (punta a coristi e prove), poi prove (punta a brani), poi le madri.
Le trappole.
| Il codice sbagliato | Cosa succede | Il test rosso |
|---|---|---|
presenze con un id SERIAL PRIMARY KEY al posto della chiave sulla coppia | Il corista Villa può essere segnato due volte alla stessa prova: l'id è diverso, il database non vede il doppione | test_es02_le_chiavi, test_es02_nessuno_e_presente_due_volte_alla_stessa_prova |
ritardo_min INTEGER CHECK (ritardo_min >= 0) senza DEFAULT | Se non scrivi il ritardo resta vuoto, non 0: le somme e le medie lo saltano | test_es02_il_ritardo_parte_da_zero |
voce TEXT NOT NULL senza CHECK | Ci entra «baritono», e anche «Soprano» scritto male: i conteggi per voce si spezzano | test_es02_la_voce_e_una_delle_quattro |
Nessun CHECK sul ritardo | Un corista arriva con meno cinque minuti di ritardo | test_es02_il_ritardo_non_e_negativo |
3 · Il bar di Piazza Query
In italiano.
- Tre cose, tre tabelle:
prodotti,ordini,righe_ordine. - Un ordine ha molte righe, un prodotto compare in molte righe: la riga è dalla parte del molti, con due frecce. È una tabella ponte.
- La coppia (
ordine_id,prodotto_id) è la chiave primaria: un prodotto è una riga sola per ordine, e per averne di più si alzaquantita. - La fotografia.
prodotti.prezzoè il listino di oggi.righe_ordine.prezzo_pagatoè quello che il cliente ha pagato: una copia voluta, perché domani il listino cambia e l'ordine di oggi no.
DROP TABLE IF EXISTS righe_ordine, ordini, prodotti;
CREATE TABLE prodotti (
id SERIAL PRIMARY KEY,
nome TEXT NOT NULL UNIQUE,
prezzo NUMERIC(5,2) NOT NULL CHECK (prezzo > 0)
);
CREATE TABLE ordini (
id SERIAL PRIMARY KEY,
tavolo INTEGER NOT NULL,
giorno DATE NOT NULL
);
CREATE TABLE righe_ordine (
ordine_id INTEGER NOT NULL REFERENCES ordini(id),
prodotto_id INTEGER NOT NULL REFERENCES prodotti(id),
quantita INTEGER NOT NULL DEFAULT 1 CHECK (quantita > 0),
prezzo_pagato NUMERIC(5,2) NOT NULL CHECK (prezzo_pagato > 0),
PRIMARY KEY (ordine_id, prodotto_id)
);Passo per passo, la giornata tipo che fa il test:
| Cosa succede | prodotti.prezzo del caffè | righe_ordine.prezzo_pagato |
|---|---|---|
| Il bar inserisce il caffè nel listino | 1,20 | |
| Un cliente ordina due caffè | 1,20 | 1,20 |
Il gestore alza il listino con un UPDATE | 1,40 | 1,20 |
Il totale con SUM(quantita * prezzo_pagato) | 2 × 1,20 + 1 × 1,50 (la brioche) = 3,90 |
Il totale è quello di quel giorno, qualunque cosa succeda al listino dopo. Se il calcolo usasse prodotti.prezzo, uscirebbe 4,30: un incasso che non è mai esistito.
Le trappole.
| Il codice sbagliato | Cosa succede | Il test rosso |
|---|---|---|
righe_ordine senza prezzo_pagato | Il prezzo si può leggere solo dal listino: ogni aumento riscrive il passato. I test non trovano la colonna | test_es03_le_tre_tabelle_con_le_loro_colonne, test_es03_i_prezzi_sono_maggiori_di_zero, test_es03_un_prodotto_una_volta_per_ordine, test_es03_la_quantita_vale_1_e_non_e_zero, test_es03_la_riga_punta_a_cose_che_esistono, test_es03_il_totale_non_cambia_se_cambia_il_listino |
quantita senza CHECK (quantita > 0) | Una riga da zero pezzi | test_es03_la_quantita_vale_1_e_non_e_zero |
Nessun CHECK sui prezzi | Un prodotto a zero euro, una riga a prezzo negativo | test_es03_i_prezzi_sono_maggiori_di_zero |
righe_ordine con un id suo, senza la chiave sulla coppia | Lo stesso prodotto compare due volte nello stesso ordine: due righe da contare insieme | test_es03_le_chiavi, test_es03_un_prodotto_una_volta_per_ordine |
nome TEXT NOT NULL senza UNIQUE | Due «caffè» nel listino, con prezzi diversi | test_es03_il_nome_del_prodotto_non_si_ripete |
Il primo errore dà molti rossi insieme perché quasi tutti i test usano prezzo_pagato. Il test che lo racconta meglio è l'ultimo, test_es03_il_totale_non_cambia_se_cambia_il_listino: dice «la colonna prezzo_pagato non c'è» e te lo spiega in italiano.
La storia
I sei livelli del panettone, con il ragionamento. Se non l'hai ancora risolta, fermati: l'ultima sezione dice il nome.
Livello 1 · Cosa dicono le segnalazioni
In italiano. «Dalle segnalazioni tengo quelle del 7 dicembre che contengono "panettone", scritto come capita. Le metto in ordine di ora.»
SELECT ora, testo
FROM segnalazioni
WHERE giorno = '2026-12-07' AND testo ILIKE '%panettone%'
ORDER BY ora;Nel caso vero escono 8 righe, e raccontano la notte: alle 05:35 la teca è piena, alle 05:50 vuota; il lettore del deposito è guasto e Nadia ha un foglio; un carrello con un telo bianco è sceso a Ponte Ciclo dal treno delle 05:54; Pietro Galli giura che il suo C-6 è a Largo Lista con dei cavi.
Le trappole.
| Il codice sbagliato | Cosa succede | Il test rosso |
|---|---|---|
LIKE '%panettone%' | Perde «Panettone» e «PANETTONE»: LIKE distingue le maiuscole | test_livello1_le_segnalazioni_del_7_dicembre_sul_panettone, test_livello1_dalla_piu_presto |
Senza giorno = '2026-12-07' | Entra anche il panettone smarrito del 30 novembre | gli stessi due |
Senza ORDER BY ora | Le righe escono come stanno nella tabella, non per ora | test_livello1_dalla_piu_presto |
Livello 2 · Il foglio di Nadia
In italiano. «Per ognuna delle quattro righe del foglio: l'id del carrello, l'id della persona, il giorno, le ore. Dove la casella è vuota, NULL.» Gli id si trovano con SELECT id, codice FROM carrelli; e SELECT id, cognome FROM persone;.
INSERT INTO prelievi_carrelli (carrello_id, persona_id, giorno, preso_alle, reso_alle) VALUES
(2, 2, '2026-12-07', '05:20', '05:50'),
(6, 3, '2026-12-07', '05:31', NULL),
(4, 6, '2026-12-07', '05:38', NULL),
(1, 8, '2026-12-07', '05:12', '05:44');INSERT 0 4E se lo lanci una seconda volta sul caso vero:
psql:storia/livello2.sql:19: ERROR: duplicate key value violates unique constraint "prelievi_carrelli_carrello_id_giorno_preso_alle_key"
DETAIL: Key (carrello_id, giorno, preso_alle)=(2, 2026-12-07, 05:20:00) already exists.È la regola UNIQUE (carrello_id, giorno, preso_alle) del disegno: lo stesso carrello non può essere preso due volte nello stesso istante. Il foglio è già dentro.
Le trappole.
| Il codice sbagliato | Cosa succede | Il test rosso |
|---|---|---|
Ivo con reso_alle a NULL | Ivo ha riportato il C-2 alle 05:50, ma risulta che no: al livello 3 sarebbe un sospettato in più | test_livello2_le_quattro_righe_del_foglio |
| Solo tre righe su quattro | Manca Dario Sala: il foglio è incompleto | test_livello2_le_quattro_righe_del_foglio, test_livello2_non_ci_sono_righe_di_troppo |
Livello 3 · Il carrello che non è tornato
In italiano.
- Parto dai prelievi.
- Aggiungo la persona e il carrello (una
JOINper freccia). - Tengo il 7 dicembre, tra le 05:00 (compresa) e le 06:00 (esclusa), con
reso_allevuoto. - In ordine di ora.
SELECT p.nome, p.cognome, c.codice, pr.preso_alle
FROM prelievi_carrelli pr
JOIN persone p ON p.id = pr.persona_id
JOIN carrelli c ON c.id = pr.carrello_id
WHERE pr.giorno = '2026-12-07'
AND pr.preso_alle >= '05:00' AND pr.preso_alle < '06:00'
AND pr.reso_alle IS NULL
ORDER BY pr.preso_alle; nome | cognome | codice | preso_alle
--------+------------+--------+------------
Pietro | Galli | C-6 | 05:31:00
Oreste | Galimberti | C-4 | 05:38:00
(2 rows)Ivo e Dario hanno preso un carrello in quella fascia, ma lo hanno riportato. Restano Pietro e Oreste (se lo lanci dopo il livello 2).
Le trappole.
| Il codice sbagliato | Cosa succede | Il test rosso |
|---|---|---|
Senza reso_alle IS NULL | Entrano anche quelli che il carrello l'hanno riportato (nel test: Lucia Ferri) | test_livello3_chi_non_ha_riportato_il_carrello, test_livello3_dal_prelievo_piu_presto |
reso_alle = NULL | Un confronto con NULL non è mai vero: zero righe, nessun errore | gli stessi due |
| Senza la fascia 05:00–06:00 | Entra chiunque abbia un carrello non riportato, a qualsiasi ora | gli stessi due |
Quando il test è rosso, il messaggio ti dice cosa manca o cosa c'è di troppo. Questo è quello della trappola senza IS NULL:
livello3.sql: il risultato non è quello giusto.
C'è una riga di troppo:
('Lucia', 'Ferri', 'C-3', '05:10:00')
La tua query (3 righe):
('Lucia', 'Ferri', 'C-3', '05:10:00')
('Marta', 'Villa', 'C-5', '05:25:00')
('Franco', 'Valli', 'C-2', '05:48:00')
Il test si aspetta (2 righe, in qualsiasi ordine):
('Marta', 'Villa', 'C-5', '05:25:00')
('Franco', 'Valli', 'C-2', '05:48:00')Livello 4 · Il treno delle 05:54
In italiano. «Dalla corsa del 7 dicembre alle 05:54 voglio: il codice del treno, il nome del macchinista, la stazione di partenza e quella di arrivo.»
SELECT t.codice, p.nome, p.cognome, da.nome AS partenza, a.nome AS arrivo
FROM corse c
JOIN treni t ON t.id = c.treno_id
JOIN persone p ON p.id = c.macchinista_id
JOIN stazioni da ON da.id = c.da_stazione_id
JOIN stazioni a ON a.id = c.a_stazione_id
WHERE c.giorno = '2026-12-07' AND c.partenza = '05:54'; codice | nome | cognome | partenza | arrivo
--------+---------+---------+--------------+-------------
M20 | Tommaso | Greco | Porta Codice | Ponte Ciclo
(1 row)Passo per passo, dal FROM:
| Pezzo | Cosa resta |
|---|---|
FROM corse c | Tutte le corse |
JOIN treni t … | A ogni corsa il suo treno |
JOIN persone p … | A ogni corsa il macchinista |
JOIN stazioni da … e JOIN stazioni a … | La stessa tabella due volte: una per la partenza, una per l'arrivo, con i soprannomi da e a |
WHERE … | Una sola corsa: quella del 7 dicembre alle 05:54 |
SELECT … | Le cinque colonne |
Il treno è il M20, guidato da Tommaso Greco da Porta Codice a Ponte Ciclo: il carrello con il telo bianco è sceso lì. Ma il macchinista non spinge i carrelli.
Le trappole.
| Il codice sbagliato | Cosa succede | Il test rosso |
|---|---|---|
| Senza il giorno | Alle 05:54 parte un treno ogni mattina: ne escono tre | test_livello4_il_treno_delle_0554 |
da.id = c.a_stazione_id | Partenza e arrivo scambiati | test_livello4_il_treno_delle_0554 |
Livello 5 · Le bancarelle del mercatino
In italiano.
- Parto dalle vendite, e aggiungo bancarella e prodotto.
- Tengo le vendite del 7 dicembre della «fetta di panettone».
- Raggruppo per bancarella e conto.
- Tengo i gruppi con almeno 30 fette.
- Dal più grande.
SELECT b.nome, COUNT(*) AS fette, ROUND(AVG(v.prezzo_pagato), 2) AS prezzo_medio
FROM vendite_bancarelle v
JOIN bancarelle b ON b.id = v.bancarella_id
JOIN prodotti p ON p.id = v.prodotto_id
WHERE v.giorno = '2026-12-07' AND p.nome = 'fetta di panettone'
GROUP BY b.nome
HAVING COUNT(*) >= 30
ORDER BY fette DESC; nome | fette | prezzo_medio
----------------------+-------+--------------
Pasticceria Bellini | 61 | 4.00
Dolci Sotterranei | 44 | 2.00
Forno di Largo Lista | 30 | 4.00
(3 rows)Il listino della fetta, in prodotti, è 4,00. Dolci Sotterranei la vende a 2,00: la fotografia prezzo_pagato dice che metà del listino era il prezzo di ogni vendita. Perché?
Le trappole.
| Il codice sbagliato | Cosa succede | Il test rosso |
|---|---|---|
| Senza il giorno | Il Bar del Ponte sembra entrare: 18 fette il 7 dicembre, ma 15 il giorno prima, e 33 fa 30 o più | test_livello5_le_bancarelle_con_almeno_30_fette, test_livello5_da_chi_ne_ha_vendute_di_piu |
COUNT(*) > 30 | Sparisce il Forno di Largo Lista, che ne ha vendute proprio 30 | gli stessi due |
| Senza il filtro sul prodotto | Si contano anche cioccolata e torrone: entrano bancarelle che di fette ne hanno vendute poche | gli stessi due |
COUNT(*) >= 30 nel WHERE | PostgreSQL risponde con un errore: il WHERE lavora sulle righe, prima dei gruppi | gli stessi due |
L'ultimo errore, preso da psql:
psql:storia/livello5.sql:7: ERROR: aggregate functions are not allowed in WHERE
LINE 5: ...2026-12-07' AND p.nome = 'fetta di panettone' AND COUNT(*) >...
^Livello 6 · I soci
In italiano.
- Parto dalle bancarelle, e scendo ai soci:
bancarelle_sociepersone. - Aggiungo con
LEFT JOINle forniture di panettone intero: le bancarelle che non ne hanno ricevute restano, con le colonne delle forniture vuote. - Tengo quelle con le colonne vuote.
SELECT p.nome, p.cognome
FROM bancarelle b
JOIN bancarelle_soci s ON s.bancarella_id = b.id
JOIN persone p ON p.id = s.persona_id
LEFT JOIN forniture f ON f.bancarella_id = b.id AND f.prodotto_id = 1
WHERE f.id IS NULL; nome | cognome
--------+------------
Oreste | Galimberti
Gianna | Ripamonti
(2 rows)Passo per passo. Cosa fa la LEFT JOIN con il prodotto 1 nell'ON:
nome | f.prodotto_id
------------------------------+--------------
Pasticceria Bellini | 1 (due forniture)
Forno di Largo Lista | 1
Dolci Sotterranei | (vuoto) ← nessuna fornitura di panettone
Bar del Ponte | 1
Torroncini di Via Variabile | 1Dolci Sotterranei riceve cioccolata, vin brulé e torrone, ma mai un panettone intero. Eppure ha venduto 44 fette, a metà prezzo. Dove le ha prese?
Le trappole.
| Il codice sbagliato | Cosa succede | Il test rosso |
|---|---|---|
JOIN forniture al posto di LEFT JOIN | Le bancarelle senza forniture spariscono prima del WHERE: zero righe | test_livello6_i_soci_della_bancarella_senza_panettoni |
WHERE f.prodotto_id = 1 AND f.id IS NULL (la condizione sul prodotto nel WHERE) | Il WHERE chiede insieme «ha il prodotto 1» e «non ha niente»: impossibile, zero righe | lo stesso |
LEFT JOIN senza AND f.prodotto_id = 1 | Cerca chi non ha mai ricevuto niente: Dolci Sotterranei ha ricevuto altro, e sparisce | lo stesso |
Il finale
- Livello 3: due persone non hanno riportato il carrello: Pietro Galli (C-6) e Oreste Galimberti (C-4).
- Livello 1: Pietro giura che il suo C-6 è a Largo Lista, con dei cavi dei segnali. Dal livello 6: Pietro non è socio di nessuna bancarella.
- Livello 5 e 6: la bancarella che ha venduto 44 fette a metà prezzo, Dolci Sotterranei, non ha mai ricevuto un panettone intero. I suoi soci sono Gianna Ripamonti e Oreste Galimberti.
- Incrocio: Gianna è socia, ma non ha preso nessun carrello. Oreste ha preso il C-4 alle 05:38, non l'ha riportato, ed è socio della bancarella che vende un panettone che nessuno gli ha venduto.
- Livello 4: il macchinista del 05:54 era Tommaso Greco: ha guidato il treno, non ha spinto il carrello.
Il colpevole è Oreste Galimberti.
python storia/verifica.py "Oreste Galimberti"Il commissario Mapelli si spazzola lo zucchero a velo dalla giacca.
«È lui. Ha preso un carrello dal deposito, ha svuotato la teca, è salito sul treno
delle 05:54 e alle sette vendeva le fette alla sua bancarella, a metà prezzo.
Un panettone che nessuno gli aveva mai venduto. Andiamo a fare due chiacchiere.»
Caso chiuso.