Esercizio - Interrogazioni sugli ordini di una pizzeria (tema d'esame 1 marzo 2013)

Query 1: la somma delle birre e delle pizze ordinate il 2013-03-01

Idea. Occorrono le pietanze ordinate nelle comande di quel giorno (COMANDA $\bowtie$ PIETANZA_ORDINATA), poi la categoria della pietanza (PIETANZA), poi un filtro sulla categoria e una somma delle quantità.

Algebra. Con $\text{PO}$ per PIETANZA_ORDINATA:

$$\text{COM}\leftarrow\sigma_{\text{data}='2013-03-01'}(\text{COMANDA})\bowtie_{\text{numero}=\text{comanda}}\text{PO}$$ $$\text{PB}\leftarrow\sigma_{\text{categoria}='\text{pizze}'\ \vee\ \text{categoria}='\text{birre}'}(\text{PIETANZA})$$ $$_{\text{categoria}}\mathcal{F}_{\text{SUM}(\text{qta})}\big(\text{COM}\bowtie_{\text{pietanza}=\text{numero}}\text{PB}\big)$$

(Nel join tra COM e PB l'attributo numero è quello della pietanza; nel primo join quello della comanda: va rinominato o proiettato via, per esempio $\pi_{\text{id},\text{pietanza},\text{comanda},\text{qta}}(\text{COM})$ prima del secondo join, come fa la soluzione.) Per avere un solo numero (pizze e birre insieme) si toglie la categoria dal raggruppamento: $\mathcal{F}_{\text{SUM}(\text{qta})}(\dots)$.

SQL.

SELECT p.categoria, SUM(po.qta) AS totale
FROM comanda AS c
     INNER JOIN pietanza_ordinata AS po ON c.numero = po.comanda
     INNER JOIN pietanza AS p ON po.pietanza = p.numero
WHERE p.categoria IN ('pizze', 'birre') AND c.data = '2013-03-01'
GROUP BY p.categoria;

Risultato sui dati: birre 4 (3 nella comanda 1 e 1 nella 4), pizze 5 (2 margherite nella 1, 1 capricciosa nella 2, 2 capricciose nella 4). Con un unico totale (togliendo p.categoria dalla SELECT e il GROUP BY) il risultato è 9.

Commenti.

• La richiesta "la somma delle birre e delle pizze" è ambigua: due numeri (uno per categoria) o uno solo? La soluzione del corso raggruppa per categoria; si può fare una query sola con GROUP BY e un'altra senza. Meglio dichiarare la scelta.
• Si somma qta, non si conta con COUNT(*): ogni riga ordinata ha una quantità (3 birre in una riga).
• "Pizze speciali" è una categoria diversa da "pizze": è corretto escluderla con IN ('pizze','birre'), ma se la richiesta le comprendesse servirebbe LIKE 'pizze%'.
• Errore nel materiale del corso: nella soluzione a mano il valore 'Bme' al posto di 'Birre' nel WHERE è un refuso che non restituirebbe nessuna birra.
• Algebra e SQL sono stati confrontati: stesso risultato.

Query 2: la fattura numero 10, con piatti e prezzo

Idea. Dalla fattura (CONTO con numero_fattura = 10) si risale alla comanda, alle pietanze ordinate, e per ogni pietanza si calcola il prezzo unitario = prezzo base + prezzo delle sole aggiunte; l'eliminazione di un ingrediente non cambia il prezzo (il testo conta il prezzo "nell'aggiunta"). L'importo di una riga è qta per il prezzo unitario.

Soluzione del corso.

$$\text{PIETANZE10}\leftarrow\Big(\pi_{\text{comanda}}\big(\sigma_{\text{fattura}=10}(\text{CONTO})\big)\Big)\bowtie_{\text{comanda}}\text{PO}\qquad\text{PIETANZE10}\bowtie\text{MODIFICA}\bowtie_{\text{pietanza}=\text{numero}}\text{PIETANZA}$$

SELECT * FROM conto AS c
  INNER JOIN pietanza_ordinata AS po ON c.pietanza = po.pietanza   -- la soluzione scrive così
  INNER JOIN modifica AS m ON po.id = m.id AND po.pietanza = m.pietanza
  INNER JOIN pietanza AS p ON po.pietanza = p.numero
WHERE fattura = 10;

Errori della soluzione del corso. (1) CONTO non ha l'attributo pietanza: il collegamento con PIETANZA_ORDINATA è c.comanda = po.comanda (SQLite: no such column: c.pietanza). (2) Il join interno con MODIFICA elimina le pietanze senza modifiche: sui dati, la fattura 10 ha 4 righe ordinate, ma solo 2 hanno una modifica, quindi la query ne restituisce 2 (le due margherite) e perde birre e tiramisù. Serve un left outer join. (3) Non si calcola nessun prezzo: SELECT * mostra solo le righe. Il testo chiede "il prezzo (comprese le aggiunte/eliminazioni)".

Soluzione corretta (SQL).

SELECT po.id, p.nome, po.qta, p.prezzo,
       COALESCE(SUM(CASE WHEN m.aggiunge THEN i.prezzo END), 0) AS extra,
       po.qta * (p.prezzo + COALESCE(SUM(CASE WHEN m.aggiunge THEN i.prezzo END), 0)) AS importo
FROM conto AS c
     INNER JOIN pietanza_ordinata AS po ON po.comanda = c.comanda
     INNER JOIN pietanza AS p ON p.numero = po.pietanza
     LEFT JOIN modifica AS m ON m.id = po.id AND m.pietanza = po.pietanza
     LEFT JOIN ingrediente AS i ON i.nome = m.ingrediente
WHERE c.numero_fattura = 10
GROUP BY po.id, po.pietanza, p.nome, po.qta, p.prezzo;

Il LEFT JOIN conserva le pietanze senza modifiche (con m e i a NULL); CASE WHEN m.aggiunge THEN i.prezzo END prende il prezzo solo per le aggiunte (le eliminazioni danno NULL e SUM le ignora); COALESCE(..., 0) trasforma il NULL (nessuna aggiunta) in 0. Si raggruppa per l'identificatore della pietanza ordinata, perché il join con MODIFICA moltiplica le righe (una per modifica): senza GROUP BY si avrebbero righe duplicate.

Risultato sulla fattura 10 (comanda 1):

La somma degli importi è 30,5, uguale al totale memorizzato nel conto: la verifica che l'attributo derivato è coerente.

Algebra relazionale (corretta). Si evita il NULL con un'unione:

$$\text{P10}\leftarrow\pi_{\text{id},\text{pietanza},\text{qta}}\Big(\rho_{\text{c10}\leftarrow\text{comanda}}\big(\pi_{\text{comanda}}(\sigma_{\text{numero\_fattura}=10}(\text{CONTO}))\big)\bowtie_{\text{c10}=\text{comanda}}\text{PO}\Big)$$ $$\text{ADD}\leftarrow\pi_{\text{id},\text{pietanza},\text{pi}}\Big(\sigma_{\text{aggiunge}=\text{vero}}(\text{MODIFICA})\bowtie_{\text{ingrediente}=\text{ing}}\rho_{\text{ing},\text{pi}\leftarrow\text{nome},\text{prezzo}}(\text{INGREDIENTE})\Big)$$ $$\text{EXTRA}\leftarrow\rho_{\text{extra}\leftarrow\text{SUM}(\text{pi})}\big({}_{\text{id},\text{pietanza}}\mathcal{F}_{\text{SUM}(\text{pi})}(\text{ADD})\big)\ \cup\ \Big(\big(\pi_{\text{id},\text{pietanza}}(\text{P10})-\pi_{\text{id},\text{pietanza}}(\text{ADD})\big)\times\{0\}\Big)$$

(dove $\{0\}$ è la relazione con il solo attributo extra e la sola tupla $(0)$.) $$\pi_{\text{id},\text{nome},\text{qta},\text{prezzo},\text{extra},\ \text{qta}\cdot(\text{prezzo}+\text{extra})}\big(\text{P10}\bowtie\text{EXTRA}\bowtie\rho_{\text{pietanza}\leftarrow\text{numero}}(\text{PIETANZA})\big)$$

Il primo passo isola le pietanze ordinate della fattura; il secondo i prezzi delle sole aggiunte; il terzo somma per pietanza ordinata e aggiunge $(\text{id},\text{pietanza},0)$ per quelle senza aggiunte (la differenza del terzo passo), così il join finale non perde nessuna riga; l'ultimo è una proiezione generalizzata che calcola l'importo. Il risultato coincide con quello SQL (verificato).

Query 3: la media dei conti del tavolo 5

Idea. Il conto non conosce il tavolo: si passa dalla comanda. Poi si media il totale.

Algebra (la soluzione del corso, con una rinomina necessaria: CONTO e COMANDA hanno entrambe data):

$$\text{TAVOLO5}\leftarrow\sigma_{\text{tavolo}=5}\big(\text{COMANDA}\bowtie_{\text{numero}=\text{comanda}}\rho_{\text{id}',\text{data}'\leftarrow\text{id},\text{data}}(\text{CONTO})\big)\qquad\mathcal{F}_{\text{AVG}(\text{totale})}(\text{TAVOLO5})$$

SQL.

SELECT AVG(c.totale) AS media_conti
FROM conto AS c INNER JOIN comanda AS co ON c.comanda = co.numero
WHERE co.tavolo = 5;

Sui dati: i conti del tavolo 5 sono quelli delle comande 1, 3 e 4, con totali $30{,}5$, $9{,}5$, $20$: media $(30{,}5+9{,}5+20)/3=\mathbf{20}$. Se il tavolo non ha conti la media è NULL.

Senza l'attributo derivato totale, bisogna ricalcolarlo per ogni comanda (somma per pietanza ordinata di qta per prezzo con le aggiunte, come nella query 2) e mediare i totali: stesso 20 (verificato). L'attributo derivato fa risparmiare un join e un raggruppamento, a costo di dover mantenere coerente il totale quando la comanda cambia (un candidato a trigger, vedi SQL avanzato - indici, transazioni e triggerUn file di record può essere heap (inserimento in ordine, ricerca per uguaglianza in media n/2 accessi), ordinato (log2 n), hash (costante, solo uguaglianza) o B-tree (log_F n, anche intervalli). Un indice è una struttura ausiliaria di coppie (chiave K, puntatore P) ordinate, molto più piccola della tabella: l'indice primario è sul campo di ordinamento del file ordinato (una chiave per blocco, non denso, log2(m)+1 accessi), l'indice secondario è su un campo qualsiasi (denso). CREATE [UNIQUE] INDEX, anche su espressioni; PostgreSQL usa tabelle heap con indici secondari B-tree (predefinito) o hash, e PRIMARY KEY e UNIQUE creano un indice implicito. Gli indici velocizzano le letture ma costano nelle scritture e in spazio. Una transazione è una sequenza atomica di operazioni con proprietà ACID (atomicità, consistenza, isolamento, durabilità): START TRANSACTION, COMMIT, SAVEPOINT, ROLLBACK [TO SAVEPOINT]. Un trigger è una regola evento-condizione-azione (ECA): BEFORE/AFTER, ROW/STATEMENT, WHEN, con funzione PL/pgSQL che usa NEW e OLD; serve per vincoli complessi e attributi derivati.SQL avanzato - indici, transazioni e trigger →).

Errori nel materiale del corso (riassunto)

• Query 1: refuso 'Bme'; richiesta ambigua (una somma o una per categoria).
• Query 2: join interno con MODIFICA che elimina le pietanze senza modifiche; ON c.pietanza = ... su una colonna inesistente; nessun calcolo del prezzo.
• Query 3: nel join COMANDA $\bowtie$ CONTO gli attributi omonimi (data) vanno rinominati.