Vai al contenuto
code-to-code

Apro l'indice…

↑ ↓ per scegliere, Invio per aprire, Esc per chiudere

SQL7. Disegnare un databasep. 181

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.

sql-disegnare-un-database-soluzioni.zip50 KB

La palestra

1 · La biblioteca di Largo Lista

In italiano.

  1. Tre cose, tre tabelle: libri, lettori, prestiti.
  2. Ognuna ha il suo id (SERIAL PRIMARY KEY).
  3. 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.
  4. prestiti ha un id suo: lo stesso lettore può riprendere lo stesso libro, in un altro giorno.
  5. Un vincolo per ogni dato che può essere sbagliato: titolo e email obbligatori, email unica, un CHECK sulle date. restituito_il non è NOT NULL: vuoto vuol dire «ancora fuori».
SQL
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 sbagliatoCosa succedeIl test rosso
libro_id INTEGER NOT NULL e lettore_id INTEGER NOT NULL, senza REFERENCESSono 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 NULLUn libro appena prestato non si può registrare: non esiste ancora una data di ritornotest_es01_un_prestito_non_punta_nel_vuoto, test_es01_i_libri_ancora_fuori
Nessun CHECK sulle dateUn libro torna prima di essere uscito: il database accetta e la contabilità è assurdatest_es01_non_si_restituisce_prima_di_aver_preso
email TEXT NOT NULL senza UNIQUEDue lettori con la stessa email: chi riceve il sollecito?test_es01_la_mail_e_unica

2 · Il coro della Linea Menta

In italiano.

  1. Quattro cose, quattro tabelle: coristi, brani, prove, presenze.
  2. Un brano ha molte prove, una prova ha un brano: prove.brano_id.
  3. Un corista viene a molte prove, e a una prova vengono molti coristi: serve la tabella ponte presenze, con le due frecce.
  4. Nessuno può essere presente due volte alla stessa prova: la chiave primaria è la coppia (corista_id, prova_id), non serve un id.
  5. Il ritardo è una colonna della coppia: non è del corista né della prova, è di quel corista a quella prova.
SQL
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 sbagliatoCosa succedeIl test rosso
presenze con un id SERIAL PRIMARY KEY al posto della chiave sulla coppiaIl corista Villa può essere segnato due volte alla stessa prova: l'id è diverso, il database non vede il doppionetest_es02_le_chiavi, test_es02_nessuno_e_presente_due_volte_alla_stessa_prova
ritardo_min INTEGER CHECK (ritardo_min >= 0) senza DEFAULTSe non scrivi il ritardo resta vuoto, non 0: le somme e le medie lo saltanotest_es02_il_ritardo_parte_da_zero
voce TEXT NOT NULL senza CHECKCi entra «baritono», e anche «Soprano» scritto male: i conteggi per voce si spezzanotest_es02_la_voce_e_una_delle_quattro
Nessun CHECK sul ritardoUn corista arriva con meno cinque minuti di ritardotest_es02_il_ritardo_non_e_negativo

3 · Il bar di Piazza Query

In italiano.

  1. Tre cose, tre tabelle: prodotti, ordini, righe_ordine.
  2. Un ordine ha molte righe, un prodotto compare in molte righe: la riga è dalla parte del molti, con due frecce. È una tabella ponte.
  3. La coppia (ordine_id, prodotto_id) è la chiave primaria: un prodotto è una riga sola per ordine, e per averne di più si alza quantita.
  4. 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.
SQL
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 succedeprodotti.prezzo del caffèrighe_ordine.prezzo_pagato
Il bar inserisce il caffè nel listino1,20
Un cliente ordina due caffè1,201,20
Il gestore alza il listino con un UPDATE1,401,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 sbagliatoCosa succedeIl test rosso
righe_ordine senza prezzo_pagatoIl prezzo si può leggere solo dal listino: ogni aumento riscrive il passato. I test non trovano la colonnatest_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 pezzitest_es03_la_quantita_vale_1_e_non_e_zero
Nessun CHECK sui prezziUn prodotto a zero euro, una riga a prezzo negativotest_es03_i_prezzi_sono_maggiori_di_zero
righe_ordine con un id suo, senza la chiave sulla coppiaLo stesso prodotto compare due volte nello stesso ordine: due righe da contare insiemetest_es03_le_chiavi, test_es03_un_prodotto_una_volta_per_ordine
nome TEXT NOT NULL senza UNIQUEDue «caffè» nel listino, con prezzi diversitest_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.»

SQL
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 sbagliatoCosa succedeIl test rosso
LIKE '%panettone%'Perde «Panettone» e «PANETTONE»: LIKE distingue le maiuscoletest_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 novembregli stessi due
Senza ORDER BY oraLe righe escono come stanno nella tabella, non per oratest_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;.

SQL
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 4

E 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 sbagliatoCosa succedeIl test rosso
Ivo con reso_alle a NULLIvo 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 quattroManca Dario Sala: il foglio è incompletotest_livello2_le_quattro_righe_del_foglio, test_livello2_non_ci_sono_righe_di_troppo

Livello 3 · Il carrello che non è tornato

In italiano.

  1. Parto dai prelievi.
  2. Aggiungo la persona e il carrello (una JOIN per freccia).
  3. Tengo il 7 dicembre, tra le 05:00 (compresa) e le 06:00 (esclusa), con reso_alle vuoto.
  4. In ordine di ora.
SQL
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 sbagliatoCosa succedeIl test rosso
Senza reso_alle IS NULLEntrano 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 = NULLUn confronto con NULL non è mai vero: zero righe, nessun erroregli stessi due
Senza la fascia 05:00–06:00Entra chiunque abbia un carrello non riportato, a qualsiasi oragli 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.»

SQL
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:

PezzoCosa resta
FROM corse cTutte 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 sbagliatoCosa succedeIl test rosso
Senza il giornoAlle 05:54 parte un treno ogni mattina: ne escono tretest_livello4_il_treno_delle_0554
da.id = c.a_stazione_idPartenza e arrivo scambiatitest_livello4_il_treno_delle_0554

Livello 5 · Le bancarelle del mercatino

In italiano.

  1. Parto dalle vendite, e aggiungo bancarella e prodotto.
  2. Tengo le vendite del 7 dicembre della «fetta di panettone».
  3. Raggruppo per bancarella e conto.
  4. Tengo i gruppi con almeno 30 fette.
  5. Dal più grande.
SQL
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 sbagliatoCosa succedeIl test rosso
Senza il giornoIl 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(*) > 30Sparisce il Forno di Largo Lista, che ne ha vendute proprio 30gli stessi due
Senza il filtro sul prodottoSi contano anche cioccolata e torrone: entrano bancarelle che di fette ne hanno vendute pochegli stessi due
COUNT(*) >= 30 nel WHEREPostgreSQL risponde con un errore: il WHERE lavora sulle righe, prima dei gruppigli 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.

  1. Parto dalle bancarelle, e scendo ai soci: bancarelle_soci e persone.
  2. Aggiungo con LEFT JOIN le forniture di panettone intero: le bancarelle che non ne hanno ricevute restano, con le colonne delle forniture vuote.
  3. Tengo quelle con le colonne vuote.
SQL
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  | 1

Dolci 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 sbagliatoCosa succedeIl test rosso
JOIN forniture al posto di LEFT JOINLe bancarelle senza forniture spariscono prima del WHERE: zero righetest_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 righelo stesso
LEFT JOIN senza AND f.prodotto_id = 1Cerca chi non ha mai ricevuto niente: Dolci Sotterranei ha ricevuto altro, e spariscelo stesso

Il finale

  1. Livello 3: due persone non hanno riportato il carrello: Pietro Galli (C-6) e Oreste Galimberti (C-4).
  2. 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.
  3. 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.
  4. 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.
  5. Livello 4: il macchinista del 05:54 era Tommaso Greco: ha guidato il treno, non ha spinto il carrello.

Il colpevole è Oreste Galimberti.

Terminale
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.