Vai al contenuto
code-to-code

Apro l'indice…

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

SQL7. Disegnare un databasep. 172

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.

sql-disegnare-un-database.zip40 KB

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:

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

  1. Apri palestra/es01.sql. In alto, nei commenti: la storia, il canvas, il contratto, come provi.
  2. Sotto i commenti scrivi lo schema: prima il DROP TABLE IF EXISTS (dalla più figlia alla più madre), poi i CREATE TABLE.
  3. Provalo sul tuo database: psql -d sql_disegnare_un_database -f palestra/es01.sql. Il file deve girare anche la seconda volta, senza errori.
  4. Guarda come è venuto: psql -d sql_disegnare_un_database, poi \dt e \d prestiti.
  5. 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.

#EsercizioCosa ti serveDifficoltà
1La biblioteca di Largo Listauno a molti, una data che può mancare★
2Il coro della Linea Mentamolti a molti, la tabella ponte★★
3Il bar di Piazza Queryil 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:

TabellaColonne che i test usano
libriid, titolo, autore
lettoriid, nome, cognome, email
prestitiid, 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
Tre tabelle, e una sola ha due frecce: quella dove c'è un libro e un lettore. Quale delle tre è «molti» da tutte e due le parti?
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
«Non può essere prima di» è un 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.

TabellaColonne che i test usano
coristiid, nome, cognome, voce
braniid, titolo
proveid, brano_id, giorno
presenzecorista_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
Un brano ha molte prove; una prova ha un brano solo. Dove va brano_id?
Aiutino 2
Un corista viene a più prove e a una prova vengono più coristi: è il caso della tabella ponte, come personale_patenti. Guarda il suo \d.
Aiutino 3
Per la voce serve un 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.

TabellaColonne che i test usano
prodottiid, nome, prezzo
ordiniid, tavolo, giorno
righe_ordineordine_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
Ogni riga di un ordine ha due frecce (verso l'ordine e verso il prodotto) e una chiave primaria sulla coppia: è una tabella ponte, con colonne sue.
Aiutino 2
Il prezzo c'è già in prodotti.prezzo. Perché serve anche in righe_ordine? Rileggi l'esempio E degli esercizi spiegati.
Aiutino 3
Se il test dice che la colonna 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.