Vai al contenuto
code-to-code

Apro l'indice…

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

SQL7. Disegnare un databasep. 166

Disegnare un database, esercizi spiegati

Cinque esempi, dall'officina alle tabelle

  • 25 minuti di lettura

Ernesto Pozzi vuole portare il quaderno dell'officina nel database, e ti chiede di disegnarlo. Cinque esempi, uno per ogni idea della teoria. Ognuno ha lo stesso ritmo:

  1. Prevedi: leggi la richiesta e il codice, e scrivi su un foglio cosa risponde PostgreSQL. Prima di aprire la risposta.
  2. In italiano: cosa fa il codice, a parole.
  3. Passo per passo: le decisioni di disegno, una alla volta.
  4. Cosa portiamo a casa: la cosa da ricordare.

Per provarli: createdb sql_disegnare_un_database, poi psql -d sql_disegnare_un_database -f linea_menta.sql (il file è nello zip della palestra), poi psql -d sql_disegnare_un_database e incolli.


A · Dal foglio alle tabelle

Il foglio di Ernesto, già visto: le revisioni (quale treno, quando, com'è andata), chi ha lavorato a una revisione e per quante ore. I treni e il personale ci sono già in linea_menta.sql.

Prima di scrivere: quante tabelle nuove servono? E quale colonna di revisioni porta la freccia verso treni?

Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.

Due tabelle nuove:

  • revisioni: una per ogni revisione. Porta la freccia verso i treni, con treno_id.
  • interventi: una per ogni coppia revisione-persona (la tabella ponte).

I treni e il personale non si rifanno: esistono. Dopo i CREATE TABLE, \d revisioni mostra:

                             Table "public.revisioni"
  Column  |  Type   | Collation | Nullable |                Default                
----------+---------+-----------+----------+---------------------------------------
 id       | integer |           | not null | nextval('revisioni_id_seq'::regclass)
 treno_id | integer |           | not null | 
 fatta_il | date    |           | not null | 
 esito    | text    |           | not null | 
Indexes:
    "revisioni_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "revisioni_esito_check" CHECK (esito = ANY (ARRAY['ok'::text, 'da rifare'::text]))
Foreign-key constraints:
    "revisioni_treno_id_fkey" FOREIGN KEY (treno_id) REFERENCES treni(id)
Referenced by:
    TABLE "interventi" CONSTRAINT "interventi_revisione_id_fkey" FOREIGN KEY (revisione_id) REFERENCES revisioni(id)

In italiano. «Una revisione ha un id, un treno (che deve esistere), una data e un esito. L'esito può essere solo ok o da rifare.»

SQL
CREATE TABLE revisioni (
    id        SERIAL PRIMARY KEY,
    treno_id  INTEGER NOT NULL REFERENCES treni(id),
    fatta_il  DATE NOT NULL,
    esito     TEXT NOT NULL CHECK (esito IN ('ok', 'da rifare'))
);

Passo per passo.

DomandaDecisione
Quali sono le cose?Le revisioni sono nuove, treni e persone no
Come si chiama la tabella?revisioni: minuscolo, plurale
Cosa si sa di una revisione?Quando: fatta_il DATE. Com'è andata: esito TEXT
Revisione e treno: chi è «molti»?Molte revisioni per treno: la freccia parte dalla revisione, treno_id
Cosa non deve entrare?Una data vuota (NOT NULL), un esito come «forse» (CHECK), un treno che non c'è (REFERENCES)

Guarda anche la riga Referenced by: PostgreSQL ti dice già che interventi punta a revisioni. Lo vedi dopo il secondo CREATE TABLE.

Cosa portiamo a casa. Ogni cosa nuova è una tabella; le cose che ci sono già non si rifanno, si puntano.


B · La freccia dalla parte del molti

Ernesto registra una revisione del treno 2 (il M12). Poi prova, per sbaglio, a registrarne una per il treno 99, una con esito «forse», e a cancellare il treno T31, che ha una revisione.

SQL
INSERT INTO revisioni (treno_id, fatta_il, esito) VALUES (2, '2026-11-10', 'ok');
INSERT INTO revisioni (treno_id, fatta_il, esito) VALUES (99, '2026-11-10', 'ok');
INSERT INTO revisioni (treno_id, fatta_il, esito) VALUES (8, '2026-11-12', 'forse');
INSERT INTO revisioni (treno_id, fatta_il, esito) VALUES (8, '2026-11-12', 'da rifare');
DELETE FROM treni WHERE codice = 'T31';

Quali dei cinque comandi funzionano? E per gli altri, cosa dice PostgreSQL?

Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
INSERT 0 1
ERROR:  insert or update on table "revisioni" violates foreign key constraint "revisioni_treno_id_fkey"
DETAIL:  Key (treno_id)=(99) is not present in table "treni".
ERROR:  new row for relation "revisioni" violates check constraint "revisioni_esito_check"
DETAIL:  Failing row contains (3, 8, 2026-11-12, forse).
INSERT 0 1
ERROR:  update or delete on table "treni" violates foreign key constraint "revisioni_treno_id_fkey" on table "revisioni"
DETAIL:  Key (id)=(8) is still referenced from table "revisioni".
  • Funzionano il primo e il quarto.
  • Il secondo: il treno 99 non esiste, la freccia non ha dove arrivare.
  • Il terzo: «forse» non è nel CHECK. L'id della riga rifiutata è 3, non 2: il SERIAL conta anche i tentativi falliti. Per questo gli id non si prevedono mai.
  • L'ultimo: il treno T31 (id 8) ha una revisione che lo punta, e non si può cancellare.

In italiano. Le regole del disegno sono sempre al lavoro. Il database non lascia entrare una revisione orfana (senza treno) e non lascia sparire un treno a cui ne punta una.

Passo per passo. Perché la freccia è in revisioni e non in treni?

ProvoCosa succede
Metto la colonna revisione_id in treniUn treno avrebbe una revisione sola: alla seconda dovrei cancellare la prima
Metto la colonna treno_id in revisioniOgni revisione ha un treno; un treno ha tutte le revisioni che vuole

La domanda per trovare il verso: «quanti ne ha, dall'altra parte?». Un treno ha molte revisioni, una revisione ha un treno solo: il numero va nella riga che ne ha uno, cioè nella revisione. In altre parole: la chiave esterna sta dove c'è il «molti».

Cosa portiamo a casa. La chiave esterna sta dalla parte del molti, e il REFERENCES fa rispettare il disegno ogni volta che si scrive.


C · La tabella ponte

A una revisione lavorano più meccanici, e un meccanico lavora a più revisioni. Ernesto vuole segnare, per ognuno, le ore. Dario Sala (id 8) e Ivo Brambati (id 2) hanno lavorato alla revisione 1.

SQL
CREATE TABLE interventi (
    revisione_id  INTEGER NOT NULL REFERENCES revisioni(id),
    personale_id  INTEGER NOT NULL REFERENCES personale(id),
    ore           NUMERIC(4,1) NOT NULL CHECK (ore > 0),
    PRIMARY KEY (revisione_id, personale_id)
);

INSERT INTO interventi (revisione_id, personale_id, ore) VALUES (1, 2, 3.5), (1, 8, 2.0);
INSERT INTO interventi (revisione_id, personale_id, ore) VALUES (1, 2, 1.0);

Ivo viene segnato due volte alla stessa revisione. Cosa risponde il database all'ultimo comando?

Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
INSERT 0 2
ERROR:  duplicate key value violates unique constraint "interventi_pkey"
DETAIL:  Key (revisione_id, personale_id)=(1, 2) already exists.

Il primo comando mette due righe. Il secondo è rifiutato: la coppia (revisione 1, persona 2) c'è già.

In italiano. Una riga di interventi dice: «questa persona ha lavorato a questa revisione per tante ore». La coppia identifica la riga, quindi la chiave primaria è la coppia.

Ora una query che usa il disegno: chi ha lavorato a quale treno?

SQL
SELECT t.codice, r.fatta_il, p.nome, p.cognome, i.ore
FROM revisioni r
JOIN treni t ON t.id = r.treno_id
JOIN interventi i ON i.revisione_id = r.id
JOIN personale p ON p.id = i.personale_id
ORDER BY p.cognome;
 codice |  fatta_il  | nome  | cognome  | ore 
--------+------------+-------+----------+-----
 M12    | 2026-11-10 | Ivo   | Brambati | 3.5
 M12    | 2026-11-10 | Dario | Sala     | 2.0
(2 rows)

Passo per passo.

DomandaDecisione
Chi è «molti» tra revisione e persona?Tutte e due
Dove va la freccia?Né in revisioni né in personale: in una terza tabella
Cosa ha di suo la tabella?Le ore: sono della coppia, non della persona né della revisione
Serve un id?No: la coppia non può ripetersi, e la sua chiave è già la chiave primaria
Come si cerca?Quattro tabelle, quattro JOIN, ognuna con la sua freccia: dal capitolo JOIN

Cosa portiamo a casa. Molti a molti: una tabella ponte con due chiavi esterne e la chiave primaria sulla coppia. Il doppione lo blocca PostgreSQL, non il tuo programma.


D · Niente liste dentro una colonna

Un meccanico alle prime armi propone una scorciatoia: «Perché non una colonna meccanici dentro revisioni, con i nomi separati da virgola?»

SQL
CREATE TABLE revisioni_brutte (
    id        SERIAL PRIMARY KEY,
    treno_id  INTEGER NOT NULL REFERENCES treni(id),
    meccanici TEXT
);
INSERT INTO revisioni_brutte (treno_id, meccanici) VALUES (2, 'Brambati, Sala'), (3, 'Sala'), (5, 'Sala, Pozzi');

Ernesto chiede: «Quante revisioni ha fatto Sala?» Quante risponde WHERE meccanici = 'Sala'? Quante sono davvero?

Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
SQL
SELECT COUNT(*) AS revisioni_di_sala FROM revisioni_brutte WHERE meccanici = 'Sala';
 revisioni_di_sala 
-------------------
                 1
(1 row)

Risponde 1: ha confrontato l'intero testo 'Brambati, Sala' con 'Sala', e solo la seconda riga è uguale. Ma Sala ha lavorato a tre revisioni.

In italiano. Il database non sa che quel testo è «una lista»: per lui è una parola lunga. Con LIKE '%Sala%' si aggiusta questo caso, ma poi servono altre scorciatoie per sapere le ore di ognuno, chi ha lavorato più di tutti, chi non ha lavorato mai.

Passo per passo. La cura è la stessa dell'esempio C.

PrimaDopo
Una casella con più valoriUna riga di interventi per ogni valore
meccanici TEXTpersonale_id con REFERENCES, e il nome sta in un posto solo
«Quante revisioni?» con LIKESELECT COUNT(*) FROM interventi WHERE personale_id = 8

Cosa portiamo a casa. Se una casella ha una virgola dentro, è una tabella che non hai ancora fatto.


E · Il prezzo come fotografia

L'officina compra i ricambi. Ernesto vuole sapere quanto ha speso, e il prezzo dei ricambi cambia ogni mese. Nel database della Linea Menta c'è già lo stesso problema, alla biglietteria: in vendite c'è prezzo_pagato, in tariffe il listino di oggi.

Quanto ha incassato la linea, per tipo di biglietto? Prevedi se, con il listino di oggi, la somma sarebbe uguale.

SQL
SELECT t.nome,
       COUNT(*) AS vendite,
       SUM(v.prezzo_pagato) AS incassato,
       SUM(t.prezzo) AS con_listino_oggi
FROM vendite v
JOIN tariffe t ON t.id = v.tariffa_id
GROUP BY t.nome
ORDER BY t.nome;
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
    nome     | vendite | incassato | con_listino_oggi 
-------------+---------+-----------+------------------
 giornaliero |       6 |     47.20 |            48.00
 ordinario   |      14 |     33.80 |            35.00
 ridotto     |       4 |      5.20 |             5.20
(3 rows)
  • Il ridotto non è cambiato: le due colonne coincidono.
  • L'ordinario e il giornaliero sì: fino al 31 ottobre costavano 2,20 e 7,60. Con il listino di oggi si «ritoccano» le vendite passate: 0,80 in più sul giornaliero, 1,20 sull'ordinario.

In italiano. «Per ogni biglietto venduto ricopio il prezzo che il cliente ha pagato. Il listino cambia: il biglietto venduto resta com'era.»

Passo per passo.

DomandaRisposta
Il dato è scritto in due posti?Sì: tariffe.prezzo e vendite.prezzo_pagato
Un giorno potrebbero non coincidere?Sì, ed è giusto
Se cambia l'originale, la copia deve cambiare?No: è un fatto storico. Quindi è una fotografia, non una copia da togliere

Per l'officina vale lo stesso: interventi avrebbe una colonna costo_pagato, e il listino dei ricambi cambierebbe quanto vuole.

Cosa portiamo a casa. Copiare un dato è sbagliato quando la copia deve restare uguale all'originale; è giusto quando la copia è la fotografia di un momento. Chiediti: «se l'originale cambia domani, questo dato cambia?»