Vai al contenuto
code-to-code

Apro l'indice…

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

Il progetto3. Gli attributip. 85

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.

progetto-gli-attributi-soluzioni.zip25 KB

1 · L'agenda del veterinario

In italiano.

  1. Due cose: pazienti e visite. Una visita punta a un animale che esiste (paziente_id).
  2. nome e specie sono obbligatori. La specie è una lista fissa di quattro parole: un CHECK ... IN.
  3. 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 da nato_il.
  4. peso_kg è NUMERIC(5,2), facoltativo, e se c'è è maggiore di zero.
  5. microchip è un testo (non ci fai conti) facoltativo e unico: UNIQUE lascia passare più vuoti.
  6. In visite, visita_alle è un TIMESTAMP (serve l'ora), motivo è obbligatorio, costo è NUMERIC(6,2) mai negativo.
SQL
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 sbagliatoCosa succedeIl test rosso
costo INTEGER NOT NULLIl costo di 35,50 euro entra come 36: l'incasso del giorno è sbagliato, e il tipo non è quello del contrattotest_es01_l_agenda_del_giorno_e_l_eta_si_calcolano, test_es01_le_colonne_e_i_tipi
una colonna eta INTEGERL'età è giusta il giorno in cui la scrivi e sbagliata il compleanno dopo: nessuno la aggiorna e nessun errore avvisatest_es01_l_eta_non_si_salva
microchip TEXT senza UNIQUEDue animali con lo stesso microchip: cercando dal numero, l'agenda mostra quello sbagliatotest_es01_il_microchip_non_si_ripete_ma_puo_mancare
nato_il DATE NOT NULLUn 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 CHECKCi entra «drago», e anche «Gatto» con la maiuscola: i filtri per specie si spezzanotest_es01_la_specie_e_una_delle_quattro

2 · La biblioteca del condominio

In italiano.

  1. libri e prestiti; un prestito punta a un libro (libro_id).
  2. inventario è il codice sul dorso: obbligatorio e unico. titolo e autore obbligatori.
  3. anno è un intero facoltativo, con un CHECK ... BETWEEN che lo vuole credibile. Con l'anno vuoto il CHECK non dà falso e la riga passa: è quello che vuoi.
  4. condizione ha tre parole possibili e un DEFAULT 'buona': chi aggiunge un libro non deve pensarci.
  5. preso_il ha DEFAULT CURRENT_DATE; reso_il è facoltativo (vuoto: il libro è ancora fuori) e non può essere prima di preso_il.
  6. Non ci sono «in prestito» né «in ritardo»: il primo è reso_il IS NULL, il secondo è reso_il IS NULL e preso_il più vecchio di trenta giorni.
SQL
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 sbagliatoCosa succedeIl test rosso
anno INTEGER senza CHECKEntrano 1066 e 2500: un refuso nell'anno resta per sempretest_es02_l_anno_puo_mancare_ma_deve_essere_credibile
condizione TEXT CHECK (...), senza NOT NULL e DEFAULTUn libro registrato senza condizione resta con il vuoto, e un NULL esplicito entra senza protestetest_es02_la_condizione_vale_buona_se_non_la_scrivi
una colonna in_prestito BOOLEAN in libriLa colonna va aggiornata a ogni prestito e a ogni reso: alla prima dimenticanza, un libro risulta fuori quando è sullo scaffaletest_es02_cio_che_si_calcola_non_si_salva
reso_il DATE NOT NULLUn libro appena prestato non si può registrare: non ha ancora una data di ritornotest_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 DEFAULTChi 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.

  1. Tre tabelle: dipendenti, viaggi e il ponte prenotazioni. Un viaggio ha un autista (un dipendente).
  2. email obbligatoria e unica: con quella si riconosce il dipendente.
  3. partenza_alle è un TIMESTAMP: data e ora.
  4. posti è un intero tra 1 e 8 (l'autista è escluso). Non c'è «posti liberi»: si calcola da posti meno le prenotazioni.
  5. contributo sono soldi: NUMERIC(5,2), mai negativi, DEFAULT 0. Non c'è la «quota a testa»: si calcola.
  6. bagagli_ammessi è un BOOLEAN NOT NULL DEFAULT true: una domanda con due risposte, mai «non so».
  7. prenotazioni ha la chiave primaria sulla coppia: nessuno prenota due volte lo stesso viaggio.
SQL
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 sbagliatoCosa succedeIl test rosso
bagagli_ammessi BOOLEANUn viaggio senza risposta resta con il vuoto: né sì né no. Chi legge non sa se può portare la valigiatest_es03_i_bagagli_sono_ammessi_se_non_dici_altro
contributo NUMERIC(5,2) CHECK (...), senza NOT NULL e DEFAULTIl contributo non scritto resta vuoto e non zero: una somma o una divisione lo salta o lo rende vuototest_es03_il_contributo_vale_zero_se_non_lo_scrivi
prenotazioni con id SERIAL PRIMARY KEY al posto della coppiaCon un id suo ogni riga è diversa, anche se la coppia è la stessa: Dario prenota lo stesso viaggio due volte e occupa due postitest_es03_le_chiavi, test_es03_nessuno_prenota_due_volte_lo_stesso_viaggio
una colonna posti_liberi INTEGERIl numero va aggiornato a ogni prenotazione e ogni annullamento: alla prima dimenticanza la bacheca mostra posti che non ci sonotest_es03_cio_che_si_calcola_non_si_salva
partenza_alle DATE NOT NULLL'ora di partenza si perde: due viaggi dello stesso giorno non si distinguono, e la bacheca non può ordinarlitest_es03_le_colonne_e_i_tipi

4 · I corsi di cucina

In italiano.

  1. Tre tabelle: corsi, iscrizioni (un'iscrizione punta a un corso), allergie.
  2. In corsi tutto è obbligatorio. Il livello è una lista di tre parole (CHECK), la quota è NUMERIC(6,2) maggiore di zero, posti un intero maggiore di zero.
  3. pagata è un BOOLEAN NOT NULL DEFAULT false: un'iscrizione nuova non ha ancora pagato.
  4. UNIQUE (corso_id, email): la stessa persona non si iscrive due volte allo stesso corso, ma può iscriversi a corsi diversi.
  5. 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).
  6. Non ci sono «iscritti», «posti liberi» e «incassato»: si contano e si sommano dalle iscrizioni.
SQL
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 sbagliatoCosa succedeIl test rosso
allergie TEXT in iscrizioni, senza la tabella allergieIl 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 postitest_es04_la_stessa_email_non_si_iscrive_due_volte_allo_stesso_corso
una colonna incassato NUMERIC(8,2) in corsiVa aggiornato a ogni pagamento. Alla prima dimenticanza l'incasso del corso non torna con il registrotest_es04_i_totali_non_si_salvano
pagata BOOLEANUn'iscrizione nuova ha pagata vuota, non falsa: chi filtra gli iscritti che non hanno pagato non la trovatest_es04_l_iscrizione_ha_un_corso_vero_e_pagata_parte_da_falso
quota REAL NOT NULLI 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.

  1. Quattro tabelle: operatori, clienti, tariffe, abbonamenti, che punta a un cliente, a una tariffa e all'operatore che l'ha venduto.
  2. operatori: email unica e password_hash, mai la password.
  3. clienti: nato_il obbligatoria (non l'età). Del documento solo documento_finale: quattro caratteri, con un CHECK che rifiuta tutto il resto.
  4. tariffe: nome unico, prezzo NUMERIC(6,2) e durata in giorni, tutti e due maggiori di zero.
  5. abbonamenti: venduto_il vale oggi se non lo scrivi, valido_dal è obbligatoria.
  6. prezzo_pagato è una fotografia: il prezzo della tariffa di quel giorno. Se la tariffa aumenta, gli abbonamenti già venduti non cambiano.
  7. La scadenza non c'è: è valido_dal + durata_giorni, e il test la calcola con una JOIN su tariffe.
SQL
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 sbagliatoCosa succedeIl test rosso
una colonna documento TEXT accanto a documento_finaleLo sportello tiene i documenti dei clienti: un rischio che il canvas non chiedevatest_es05_il_documento_intero_e_l_eta_non_si_salvano
documento_finale TEXT NOT NULL senza CHECKChi riempie il modulo scrive il documento intero nel campo «ultime quattro cifre», e il database lo accettatest_es05_del_documento_bastano_quattro_cifre
una colonna eta INTEGERL'età è giusta il giorno in cui la scrivi e sbagliata il compleanno dopo: nessuno la aggiorna e nessun errore avvisatest_es05_il_documento_intero_e_l_eta_non_si_salvano
password TEXT NOT NULL al posto di password_hashChi apre il database legge tutte le password degli addetti; e il contratto del login cerca password_hashtest_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 DATELa scadenza è valido_dal più la durata della tariffa: se cambia una delle due, la colonna è sbagliata senza dare erroritest_es05_la_scadenza_non_si_salva
abbonamenti senza prezzo_pagatoIl prezzo si legge da tariffe.prezzo: con il primo rincaro, ogni abbonamento già venduto cambia prezzotest_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 UNIQUEDue tariffe «mensile» con prezzi diversi: quale vende lo sportello?test_es05_la_tariffa_ha_nome_unico_prezzo_e_durata_positivi