Gli attributi, le soluzioni
Gli schemi di riferimento, il ragionamento, le trappole
Per ogni esercizio, sempre lo stesso ordine: in italiano → lo schema → le trappole.
Le strade giuste sono più di una: i test guardano i nomi del contratto, i tipi, i vincoli e il comportamento, non come hai scritto il file. Puoi aver aggiunto una colonna note, o aver scritto VARCHAR(80) dove qui c'è TEXT: va bene. 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.
1 · L'agenda del veterinario
In italiano.
- Due cose:
pazientievisite. Una visita punta a un animale che esiste (paziente_id). nomeespeciesono obbligatori. La specie è una lista fissa di quattro parole: unCHECK ... IN.nato_ilè una data e può mancare: dei trovatelli non si sa, e il vuoto vuol dire «non lo sappiamo». L'età non si salva: si calcola danato_il.peso_kgèNUMERIC(5,2), facoltativo, e se c'è è maggiore di zero.microchipè un testo (non ci fai conti) facoltativo e unico:UNIQUElascia passare più vuoti.- In
visite,visita_alleè unTIMESTAMP(serve l'ora),motivoè obbligatorio,costoèNUMERIC(6,2)mai negativo.
DROP TABLE IF EXISTS visite, pazienti;
CREATE TABLE pazienti (
id SERIAL PRIMARY KEY,
nome TEXT NOT NULL,
specie TEXT NOT NULL CHECK (specie IN ('cane', 'gatto', 'coniglio', 'altro')),
nato_il DATE,
peso_kg NUMERIC(5,2) CHECK (peso_kg > 0),
microchip TEXT UNIQUE
);
CREATE TABLE visite (
id SERIAL PRIMARY KEY,
paziente_id INTEGER NOT NULL REFERENCES pazienti(id),
visita_alle TIMESTAMP NOT NULL,
motivo TEXT NOT NULL,
costo NUMERIC(6,2) NOT NULL CHECK (costo >= 0)
);Il microchip è l'unico attributo che è insieme facoltativo e unico: se ti è venuto un NOT NULL, la colonna avrebbe bloccato la registrazione dei cuccioli senza chip. L'età dell'animale si calcola così: date_part('year', age(CURRENT_DATE, nato_il)).
Le trappole.
| Il codice sbagliato | Cosa succede | Il test rosso |
|---|---|---|
costo INTEGER NOT NULL | Il costo di 35,50 euro entra come 36: l'incasso del giorno è sbagliato, e il tipo non è quello del contratto | test_es01_l_agenda_del_giorno_e_l_eta_si_calcolano, test_es01_le_colonne_e_i_tipi |
una colonna eta INTEGER | L'età è giusta il giorno in cui la scrivi e sbagliata il compleanno dopo: nessuno la aggiorna e nessun errore avvisa | test_es01_l_eta_non_si_salva |
microchip TEXT senza UNIQUE | Due animali con lo stesso microchip: cercando dal numero, l'agenda mostra quello sbagliato | test_es01_il_microchip_non_si_ripete_ma_puo_mancare |
nato_il DATE NOT NULL | Un cane trovato per strada non si può registrare: non si sa quando è nato. (Rompe anche i test che inseriscono animali senza data) | test_es01_data_di_nascita_e_peso_possono_mancare, test_es01_il_microchip_non_si_ripete_ma_puo_mancare, test_es01_il_peso_e_maggiore_di_zero, test_es01_la_specie_e_una_delle_quattro |
specie TEXT NOT NULL senza CHECK | Ci entra «drago», e anche «Gatto» con la maiuscola: i filtri per specie si spezzano | test_es01_la_specie_e_una_delle_quattro |
2 · La biblioteca del condominio
In italiano.
librieprestiti; un prestito punta a un libro (libro_id).inventarioè il codice sul dorso: obbligatorio e unico.titoloeautoreobbligatori.annoè un intero facoltativo, con unCHECK ... BETWEENche lo vuole credibile. Con l'anno vuoto ilCHECKnon dà falso e la riga passa: è quello che vuoi.condizioneha tre parole possibili e unDEFAULT 'buona': chi aggiunge un libro non deve pensarci.preso_ilhaDEFAULT CURRENT_DATE;reso_ilè facoltativo (vuoto: il libro è ancora fuori) e non può essere prima dipreso_il.- Non ci sono «in prestito» né «in ritardo»: il primo è
reso_il IS NULL, il secondo èreso_il IS NULLepreso_ilpiù vecchio di trenta giorni.
DROP TABLE IF EXISTS prestiti, libri;
CREATE TABLE libri (
id SERIAL PRIMARY KEY,
inventario TEXT NOT NULL UNIQUE,
titolo TEXT NOT NULL,
autore TEXT NOT NULL,
anno INTEGER CHECK (anno BETWEEN 1450 AND 2100),
condizione TEXT NOT NULL DEFAULT 'buona' CHECK (condizione IN ('ottima', 'buona', 'da riparare'))
);
CREATE TABLE prestiti (
id SERIAL PRIMARY KEY,
libro_id INTEGER NOT NULL REFERENCES libri(id),
inquilino TEXT NOT NULL,
preso_il DATE NOT NULL DEFAULT CURRENT_DATE,
reso_il DATE,
CHECK (reso_il >= preso_il)
);Il test finale lo mostra: il libro è «in ritardo» perché reso_il è vuoto e preso_il è lontano, senza nessuna colonna che lo dica.
Le trappole.
| Il codice sbagliato | Cosa succede | Il test rosso |
|---|---|---|
anno INTEGER senza CHECK | Entrano 1066 e 2500: un refuso nell'anno resta per sempre | test_es02_l_anno_puo_mancare_ma_deve_essere_credibile |
condizione TEXT CHECK (...), senza NOT NULL e DEFAULT | Un libro registrato senza condizione resta con il vuoto, e un NULL esplicito entra senza proteste | test_es02_la_condizione_vale_buona_se_non_la_scrivi |
una colonna in_prestito BOOLEAN in libri | La colonna va aggiornata a ogni prestito e a ogni reso: alla prima dimenticanza, un libro risulta fuori quando è sullo scaffale | test_es02_cio_che_si_calcola_non_si_salva |
reso_il DATE NOT NULL | Un libro appena prestato non si può registrare: non ha ancora una data di ritorno | test_es02_chi_e_in_ritardo_si_legge_dai_prestiti, test_es02_il_prestito_ha_un_libro_vero_e_un_inquilino, test_es02_la_data_del_prestito_vale_oggi_se_non_la_scrivi, test_es02_non_si_rende_prima_di_aver_preso |
preso_il DATE NOT NULL senza DEFAULT | Chi registra il prestito senza scrivere la data riceve un errore, invece di «oggi» | test_es02_il_prestito_ha_un_libro_vero_e_un_inquilino, test_es02_la_data_del_prestito_vale_oggi_se_non_la_scrivi |
3 · Il car pooling dei dipendenti
In italiano.
- Tre tabelle:
dipendenti,viaggie il ponteprenotazioni. Un viaggio ha un autista (un dipendente). emailobbligatoria e unica: con quella si riconosce il dipendente.partenza_alleè unTIMESTAMP: data e ora.postiè un intero tra 1 e 8 (l'autista è escluso). Non c'è «posti liberi»: si calcola dapostimeno le prenotazioni.contributosono soldi:NUMERIC(5,2), mai negativi,DEFAULT 0. Non c'è la «quota a testa»: si calcola.bagagli_ammessiè unBOOLEAN NOT NULL DEFAULT true: una domanda con due risposte, mai «non so».prenotazioniha la chiave primaria sulla coppia: nessuno prenota due volte lo stesso viaggio.
DROP TABLE IF EXISTS prenotazioni, viaggi, dipendenti;
CREATE TABLE dipendenti (
id SERIAL PRIMARY KEY,
nome TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
);
CREATE TABLE viaggi (
id SERIAL PRIMARY KEY,
autista_id INTEGER NOT NULL REFERENCES dipendenti(id),
da_dove TEXT NOT NULL,
partenza_alle TIMESTAMP NOT NULL,
posti INTEGER NOT NULL CHECK (posti BETWEEN 1 AND 8),
contributo NUMERIC(5,2) NOT NULL DEFAULT 0 CHECK (contributo >= 0),
bagagli_ammessi BOOLEAN NOT NULL DEFAULT true
);
CREATE TABLE prenotazioni (
viaggio_id INTEGER NOT NULL REFERENCES viaggi(id),
passeggero_id INTEGER NOT NULL REFERENCES dipendenti(id),
PRIMARY KEY (viaggio_id, passeggero_id)
);La quota a testa del test è contributo / (prenotati + 1): l'autista paga come gli altri.
Le trappole.
| Il codice sbagliato | Cosa succede | Il test rosso |
|---|---|---|
bagagli_ammessi BOOLEAN | Un viaggio senza risposta resta con il vuoto: né sì né no. Chi legge non sa se può portare la valigia | test_es03_i_bagagli_sono_ammessi_se_non_dici_altro |
contributo NUMERIC(5,2) CHECK (...), senza NOT NULL e DEFAULT | Il contributo non scritto resta vuoto e non zero: una somma o una divisione lo salta o lo rende vuoto | test_es03_il_contributo_vale_zero_se_non_lo_scrivi |
prenotazioni con id SERIAL PRIMARY KEY al posto della coppia | Con un id suo ogni riga è diversa, anche se la coppia è la stessa: Dario prenota lo stesso viaggio due volte e occupa due posti | test_es03_le_chiavi, test_es03_nessuno_prenota_due_volte_lo_stesso_viaggio |
una colonna posti_liberi INTEGER | Il numero va aggiornato a ogni prenotazione e ogni annullamento: alla prima dimenticanza la bacheca mostra posti che non ci sono | test_es03_cio_che_si_calcola_non_si_salva |
partenza_alle DATE NOT NULL | L'ora di partenza si perde: due viaggi dello stesso giorno non si distinguono, e la bacheca non può ordinarli | test_es03_le_colonne_e_i_tipi |
4 · I corsi di cucina
In italiano.
- Tre tabelle:
corsi,iscrizioni(un'iscrizione punta a un corso),allergie. - In
corsitutto è obbligatorio. Illivelloè una lista di tre parole (CHECK), laquotaèNUMERIC(6,2)maggiore di zero,postiun intero maggiore di zero. pagataè unBOOLEAN NOT NULL DEFAULT false: un'iscrizione nuova non ha ancora pagato.UNIQUE (corso_id, email): la stessa persona non si iscrive due volte allo stesso corso, ma può iscriversi a corsi diversi.- Le allergie sono una lista: ogni iscritto ne ha zero, una o tante. Una colonna di testo non va bene: la lista della spesa non si potrebbe contare. Ogni allergia è una riga di
allergie, con la chiave primaria sulla coppia (iscrizione, allergene). - Non ci sono «iscritti», «posti liberi» e «incassato»: si contano e si sommano dalle iscrizioni.
DROP TABLE IF EXISTS allergie, iscrizioni, corsi;
CREATE TABLE corsi (
id SERIAL PRIMARY KEY,
titolo TEXT NOT NULL,
livello TEXT NOT NULL CHECK (livello IN ('base', 'medio', 'avanzato')),
quota NUMERIC(6,2) NOT NULL CHECK (quota > 0),
inizia_il DATE NOT NULL,
posti INTEGER NOT NULL CHECK (posti > 0)
);
CREATE TABLE iscrizioni (
id SERIAL PRIMARY KEY,
corso_id INTEGER NOT NULL REFERENCES corsi(id),
nome TEXT NOT NULL,
email TEXT NOT NULL,
pagata BOOLEAN NOT NULL DEFAULT false,
UNIQUE (corso_id, email)
);
CREATE TABLE allergie (
iscrizione_id INTEGER NOT NULL REFERENCES iscrizioni(id),
allergene TEXT NOT NULL,
PRIMARY KEY (iscrizione_id, allergene)
);Nel test, quota * COUNT(...) FILTER (WHERE pagata) calcola l'incasso solo con chi ha pagato. Se salvassi incassato nel corso, qualcuno dovrebbe aggiornarlo a ogni pagamento.
Le trappole.
| Il codice sbagliato | Cosa succede | Il test rosso |
|---|---|---|
allergie TEXT in iscrizioni, senza la tabella allergie | Il testo 'glutine, lattosio' non si può contare né cercare: la lista della spesa è sbagliata (e allergie non c'è più) | test_es04_le_allergie_sono_righe_e_non_si_ripetono, test_es04_le_chiavi, test_es04_le_colonne_e_i_tipi, test_es04_posti_liberi_e_incasso_si_calcolano, test_es04_una_lista_non_sta_in_una_colonna |
niente UNIQUE (corso_id, email) | La stessa persona si iscrive due volte allo stesso corso e occupa due posti | test_es04_la_stessa_email_non_si_iscrive_due_volte_allo_stesso_corso |
una colonna incassato NUMERIC(8,2) in corsi | Va aggiornato a ogni pagamento. Alla prima dimenticanza l'incasso del corso non torna con il registro | test_es04_i_totali_non_si_salvano |
pagata BOOLEAN | Un'iscrizione nuova ha pagata vuota, non falsa: chi filtra gli iscritti che non hanno pagato non la trova | test_es04_l_iscrizione_ha_un_corso_vero_e_pagata_parte_da_falso |
quota REAL NOT NULL | I soldi in virgola mobile accumulano decimali di troppo, e il tipo non è numeric(6,2) | test_es04_le_colonne_e_i_tipi |
5 · Lo sportello abbonamenti
In italiano.
- Quattro tabelle:
operatori,clienti,tariffe,abbonamenti, che punta a un cliente, a una tariffa e all'operatore che l'ha venduto. operatori: email unica epassword_hash, mai la password.clienti:nato_ilobbligatoria (non l'età). Del documento solodocumento_finale: quattro caratteri, con unCHECKche rifiuta tutto il resto.tariffe: nome unico, prezzoNUMERIC(6,2)e durata in giorni, tutti e due maggiori di zero.abbonamenti:venduto_ilvale oggi se non lo scrivi,valido_dalè obbligatoria.prezzo_pagatoè una fotografia: il prezzo della tariffa di quel giorno. Se la tariffa aumenta, gli abbonamenti già venduti non cambiano.- La scadenza non c'è: è
valido_dal + durata_giorni, e il test la calcola con unaJOINsutariffe.
DROP TABLE IF EXISTS abbonamenti, tariffe, clienti, operatori;
CREATE TABLE operatori (
id SERIAL PRIMARY KEY,
nome TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL
);
CREATE TABLE clienti (
id SERIAL PRIMARY KEY,
nome TEXT NOT NULL,
cognome TEXT NOT NULL,
nato_il DATE NOT NULL,
documento_finale TEXT NOT NULL CHECK (length(documento_finale) = 4)
);
CREATE TABLE tariffe (
id SERIAL PRIMARY KEY,
nome TEXT NOT NULL UNIQUE,
prezzo NUMERIC(6,2) NOT NULL CHECK (prezzo > 0),
durata_giorni INTEGER NOT NULL CHECK (durata_giorni > 0)
);
CREATE TABLE abbonamenti (
id SERIAL PRIMARY KEY,
cliente_id INTEGER NOT NULL REFERENCES clienti(id),
tariffa_id INTEGER NOT NULL REFERENCES tariffe(id),
operatore_id INTEGER NOT NULL REFERENCES operatori(id),
venduto_il DATE NOT NULL DEFAULT CURRENT_DATE,
valido_dal DATE NOT NULL,
prezzo_pagato NUMERIC(6,2) NOT NULL CHECK (prezzo_pagato > 0)
);La scadenza si calcola dalla durata della tariffa: per questo una tariffa non cambia la durata, se ne crea una nuova. Il prezzo, invece, cambia nel tempo, ed è proprio quello che prezzo_pagato protegge.
Le trappole.
| Il codice sbagliato | Cosa succede | Il test rosso |
|---|---|---|
una colonna documento TEXT accanto a documento_finale | Lo sportello tiene i documenti dei clienti: un rischio che il canvas non chiedeva | test_es05_il_documento_intero_e_l_eta_non_si_salvano |
documento_finale TEXT NOT NULL senza CHECK | Chi riempie il modulo scrive il documento intero nel campo «ultime quattro cifre», e il database lo accetta | test_es05_del_documento_bastano_quattro_cifre |
una colonna eta INTEGER | L'età è giusta il giorno in cui la scrivi e sbagliata il compleanno dopo: nessuno la aggiorna e nessun errore avvisa | test_es05_il_documento_intero_e_l_eta_non_si_salvano |
password TEXT NOT NULL al posto di password_hash | Chi apre il database legge tutte le password degli addetti; e il contratto del login cerca password_hash | test_es05_il_prezzo_pagato_non_cambia_con_il_listino, test_es05_l_abbonamento_ha_tutto_e_vale_oggi_se_non_dici_altro, test_es05_l_operatore_ha_email_unica_e_impronta, test_es05_la_password_non_si_salva_in_chiaro, test_es05_la_scadenza_e_gli_anni_si_calcolano, test_es05_le_colonne_e_i_tipi |
una colonna scade_il DATE | La scadenza è valido_dal più la durata della tariffa: se cambia una delle due, la colonna è sbagliata senza dare errori | test_es05_la_scadenza_non_si_salva |
abbonamenti senza prezzo_pagato | Il prezzo si legge da tariffe.prezzo: con il primo rincaro, ogni abbonamento già venduto cambia prezzo | test_es05_il_prezzo_pagato_non_cambia_con_il_listino, test_es05_l_abbonamento_ha_tutto_e_vale_oggi_se_non_dici_altro, test_es05_la_scadenza_e_gli_anni_si_calcolano, test_es05_le_colonne_e_i_tipi |
tariffe.nome TEXT NOT NULL senza UNIQUE | Due tariffe «mensile» con prezzi diversi: quale vende lo sportello? | test_es05_la_tariffa_ha_nome_unico_prezzo_e_durata_positivi |