Disegnare un database, la palestra
Tre domini da modellare, con i test
- 90–120 minuti di lavoro
Tre esercizi, dal ★ al ★★★. Questa volta non scrivi query: disegni lo schema. Ogni esercizio ti dà un foglio (le cose, cosa si sa, le azioni) e un contratto: i nomi delle tabelle e delle colonne che i test useranno. Tutto il resto lo decidi tu: i tipi, i vincoli, le colonne in più.
Gli esercizi da scaricare
Una cartella con i file da completare e i test già pronti. Come si usa: la guida in cinque passi.
Nello stesso zip c'è anche la storia del capitolo, Il panettone di Porta Codice: la trovi nella pagina dopo.
Prima di iniziare
Scompatta lo zip e apri il terminale nella cartella (su Windows quello di Ubuntu). Una volta sola:
cd ~/Downloads/sql-disegnare-un-database
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
pg_isready
createdb sql_disegnare_un_database
psql -d sql_disegnare_un_database -f linea_menta.sql
code .pg_isready deve rispondere accepting connections: se no, PostgreSQL è spento (come si accende). Il database della Linea Menta non serve agli esercizi (le tabelle da disegnare sono nuove), ma resta utile per guardare come è fatto uno schema vero con \d.
Ogni esercizio, sempre gli stessi cinque passi:
- Apri
palestra/es01.sql. In alto, nei commenti: la storia, il canvas, il contratto, come provi. - Sotto i commenti scrivi lo schema: prima il
DROP TABLE IF EXISTS(dalla più figlia alla più madre), poi iCREATE TABLE. - Provalo sul tuo database:
psql -d sql_disegnare_un_database -f palestra/es01.sql. Il file deve girare anche la seconda volta, senza errori. - Guarda come è venuto:
psql -d sql_disegnare_un_database, poi\dte\d prestiti. - Lancia il test:
pytest palestra/test_es01.py -v, oppure la provetta di VS Code. Rosso? Leggi cosa non torna.
I test creano un database loro, vuoto, e lanciano il tuo file lì. Poi ci scrivono dentro dei dati veri: un lettore, un libro, un prestito. Controllano le chiavi, i vincoli (provando a inserire cose sbagliate: se PostgreSQL le rifiuta, il vincolo c'è) e una «giornata tipo» in cui fanno la query che il programma farà davvero. Non toccano il tuo database.
| # | Esercizio | Cosa ti serve | Difficoltà |
|---|---|---|---|
| 1 | La biblioteca di Largo Lista | uno a molti, una data che può mancare | ★ |
| 2 | Il coro della Linea Menta | molti a molti, la tabella ponte | ★★ |
| 3 | Il bar di Piazza Query | il prezzo come fotografia | ★★★ |
1 · La biblioteca di Largo Lista ★
palestra/es01.sql
Accanto ai tornelli di Largo Lista c'è uno scaffale di libri che i passeggeri si prestano. Marta Villa vuole sapere chi ha cosa, e quali libri sono ancora fuori.
Le cose: i libri, i lettori, i prestiti. Le azioni: registrare un prestito, segnare la restituzione, elencare i libri ancora fuori.
Il contratto:
| Tabella | Colonne che i test usano |
|---|---|
libri | id, titolo, autore |
lettori | id, nome, cognome, email |
prestiti | id, libro_id, lettore_id, prestato_il, restituito_il |
Le regole sono scritte nell'intestazione del file: un prestito punta a un libro e a un lettore che esistono; il titolo è obbligatorio; l'email è unica; prestato_il è una data obbligatoria; restituito_il può mancare (il libro è ancora fuori) e non può essere prima di prestato_il.
Cosa fa la «giornata tipo»: il test registra un lettore, tre libri e due prestiti (uno restituito, uno no), e cerca i libri ancora fuori con restituito_il IS NULL. Se ti torna una sola riga, è giusto.
Come provi: pytest palestra/test_es01.py -v. A lavoro finito sono sette test verdi.
Aiutino 1
Aiutino 2
prestiti ha un id suo: lo stesso lettore può prendere lo stesso libro due volte, in giorni diversi, quindi la coppia non è unica.Aiutino 3
CHECK che confronta due colonne della stessa riga: CHECK (restituito_il >= prestato_il). Con restituito_il vuoto il confronto non dà falso, e la riga passa.2 · Il coro della Linea Menta ★★
palestra/es02.sql
Rosa Bellini canta nel coro dei dipendenti, che prova nelle sale sopra Piazza Query. Ogni brano si prova più volte, ogni corista viene a più prove. Rosa vuole sapere chi c'era e quanti minuti di ritardo ha fatto.
Le cose: i coristi, i brani, le prove, le presenze.
| Tabella | Colonne che i test usano |
|---|---|
coristi | id, nome, cognome, voce |
brani | id, titolo |
prove | id, brano_id, giorno |
presenze | corista_id, prova_id, ritardo_min |
Le regole: la voce è una tra soprano, contralto, tenore, basso. presenze è la tabella ponte: la chiave primaria è la coppia (corista_id, prova_id), così nessuno risulta presente due volte alla stessa prova. ritardo_min vale 0 se non lo scrivi e non è mai negativo.
Cosa fa la «giornata tipo»: il test fissa due prove dello stesso brano, segna tre presenti alla prima e uno alla seconda, e conta i presenti per giorno con un GROUP BY. Deve uscire: tre il 23 novembre, uno il 30.
Come provi: pytest palestra/test_es02.py -v. A lavoro finito sono otto test verdi.
Aiutino 1
brano_id?Aiutino 2
personale_patenti. Guarda il suo \d.Aiutino 3
CHECK con IN, come per l'esito dell'officina negli esercizi spiegati. Per il ritardo: NOT NULL DEFAULT 0 e un CHECK sul segno.3 · Il bar di Piazza Query ★★★
palestra/es03.sql
Il bar di Piazza Query cambia i prezzi a ogni stagione. Il gestore, Ernesto Pozzi (nei giorni liberi dal deposito), vuole sapere quanto ha incassato a novembre: con i prezzi di novembre, non con quelli di oggi.
Le cose: i prodotti, gli ordini, le righe degli ordini.
| Tabella | Colonne che i test usano |
|---|---|
prodotti | id, nome, prezzo |
ordini | id, tavolo, giorno |
righe_ordine | ordine_id, prodotto_id, quantita, prezzo_pagato |
Le regole: prezzo e prezzo_pagato sono euro con due decimali (NUMERIC(5,2)), sempre maggiori di zero. Il nome del prodotto è obbligatorio e non si ripete. La chiave primaria di righe_ordine è la coppia (ordine_id, prodotto_id): un prodotto compare una volta per ordine, e per averne di più si alza quantita, che vale 1 se non la scrivi e deve essere maggiore di zero.
Cosa fa la «giornata tipo»: il test apre un ordine con due caffè a 1,20 e una brioche a 1,50, poi cambia il prezzo del caffè nel listino a 1,40 e calcola il totale dell'ordine con SUM(quantita * prezzo_pagato). Deve restare 3,90.
Come provi: pytest palestra/test_es03.py -v. A lavoro finito sono otto test verdi.
Aiutino 1
Aiutino 2
prodotti.prezzo. Perché serve anche in righe_ordine? Rileggi l'esempio E degli esercizi spiegati.Aiutino 3
prezzo_pagato non c'è, è la fotografia che manca. Se dice che il totale non torna, controlla che tu non stia calcolando con prodotti.prezzo.Finiti i tre esercizi, passa lo schema a un compagno e fate le dieci domande uno all'altro. Poi lo schema di riferimento, spiegato, è nelle soluzioni.