Esercizio - Interrogazioni sulla compagnia di treni (tema d'esame 17 giugno 2013)

Query 1: il fornitore che fornisce più pezzi degli altri

Idea. "Fornisce" i pezzi nel senso di OFFRE (sono i pezzi che mette in catalogo): si conta per fornitore e si prende il massimo. È il pattern del massimo: (1) conteggio per gruppo, (2) massimo dei conteggi, (3) join per tenere i gruppi con quel valore.

Algebra.

$$R_1\leftarrow\rho_{\text{NPezzi}\leftarrow\text{COUNT}(*)}\big({}_{\text{fornitore}}\mathcal{F}_{\text{COUNT}(*)}(\text{OFFRE})\big)$$ $$\text{MAXF}\leftarrow\rho_{\text{MaxPezzi}\leftarrow\text{MAX}(\text{NPezzi})}\big(\mathcal{F}_{\text{MAX}(\text{NPezzi})}(R_1)\big)$$ $$\pi_{\text{fornitore}}\big(R_1\bowtie_{\text{NPezzi}=\text{MaxPezzi}}\text{MAXF}\big)$$

Nella soluzione del corso l'ultimo passo è scritto $\sigma_{\text{NPezzi}=\text{MAXF}}(R_1)$, dove MAXF è una relazione e non un valore: è un'abbreviazione, la forma corretta è il join (o il prodotto seguito dalla selezione), che il corso indica come alternativa valida.

SQL.

SELECT fornitore, COUNT(*) AS npezzi
FROM offre
GROUP BY fornitore
HAVING COUNT(*) = (SELECT MAX(n)
                   FROM (SELECT COUNT(*) AS n FROM offre GROUP BY fornitore) AS t);

Risultato sui dati: f2 con 5 pezzi (f1 ne ha 4, f3 uno). In caso di parità la query restituisce tutti i fornitori a pari merito.

Commenti.

• Si può leggere "fornisce più pezzi" anche come pezzi realmente venduti per i veicoli (VENDE): con COUNT(DISTINCT pezzo) su VENDE si ottengono f1: 4, f2: 5, f3: 1: stesso risultato qui, ma i due significati possono differire. Occorre dichiarare quello usato.
• Un fornitore senza offerte (cardinalità $(0,N)$ nell'ER) non compare in OFFRE: la query lo ignora, il che va bene per il massimo. Per elencare anche loro, con conteggio 0, si userebbe un LEFT JOIN da FORNITORE e COUNT(o.pezzo).

Query 2: il costo complessivo di ciascuna composizione

Con l'attributo derivato costo (la scelta della soluzione, comoda per questa query):

$$\pi_{\text{codice},\text{costo}}(\text{COMPOSIZIONE})\qquad\text{SQL: SELECT codice, costo FROM composizione;}$$

È una semplice proiezione, ma il prezzo da pagare è la ridondanza: costo va ricalcolato (a mano, da un trigger o dall'applicazione) ogni volta che cambiano i veicoli della composizione, i pezzi venduti o i prezzi di OFFRE. Per questo il testo del corso lo marca come "attributo derivato (per Q2)".

Senza l'attributo derivato, il costo si calcola: la composizione contiene i veicoli, per ogni veicolo ci sono le righe di VENDE (quali pezzi, da quale fornitore), e il prezzo di ciascun pezzo è in OFFRE per la coppia (fornitore, pezzo). Il costo è la somma di tutti questi prezzi.

$$\text{VO}\leftarrow\text{VENDE}\bowtie\text{OFFRE}\ \ (\text{su fornitore e pezzo})\qquad\text{CV}\leftarrow{}_{\text{veicolo}}\mathcal{F}_{\text{SUM}(\text{prezzo})}(\text{VO})$$ $$_{\text{composizione}}\mathcal{F}_{\text{SUM}(\text{CV})}\big(\rho_{\text{vid}\leftarrow\text{veicolo}}(\text{CV})\bowtie_{\text{vid}=\text{id}}\pi_{\text{id},\text{composizione}}(\text{VEICOLO})\big)$$

SELECT cm.codice, cm.descrizione, SUM(o.prezzo) AS costo
FROM composizione AS cm
     INNER JOIN veicolo AS v ON v.composizione = cm.codice
     INNER JOIN vende AS ve ON ve.veicolo = v.id
     INNER JOIN offre AS o ON o.fornitore = ve.fornitore AND o.pezzo = ve.pezzo
GROUP BY cm.codice, cm.descrizione;

Risultato sui dati: K1: 2430, K2: 2110, K3: 1050, K4: 2460. Passaggi di K1: m1 ha p1 di f1 (1000) e p5 di f1 (100), m2 ha p2 di f1 (500) e p5 di f2 (110): 1100 + 610; c1 ha p3 di f1 (200) e p4 di f2 (300): 500; c2 ha p3 di f2: 220; totale $1100+610+500+220=2430$.

La join con OFFRE deve essere sulla coppia (fornitore, pezzo): il prezzo cambia con il fornitore, quindi unire solo per pezzo darebbe un prodotto sbagliato (si sommerebbero i prezzi di tutti i fornitori di quel pezzo). Il costo non comprende il "prezzo" dei veicoli in sé: il testo non lo prevede, conta solo la fornitura dei pezzi.

Query 3: la tratta dei treni con due motrici fornite da f1

Idea. Si cercano le composizioni in cui due motrici distinte hanno almeno un pezzo fornito da f1. "Almeno un pezzo per ciascuna motrice": basta una riga di VENDE per motrice. Il punto delicato è che una motrice può avere più pezzi di f1 (più righe in VENDE): si devono contare le motrici distinte, non le righe.

Algebra (soluzione del corso). Con la scelta del veicolo unico:

$$\text{VEICOLI}\leftarrow\text{COMPOSIZIONE}\bowtie_{\text{codice}=\text{composizione}}\text{VEICOLO}$$ $$\text{MOTRICI}\leftarrow\sigma_{\text{fornitore}='f1'\ \wedge\ \text{tipo}='\text{motrice}'}\big(\text{VENDE}\bowtie_{\text{veicolo}=\text{id}}\text{VEICOLI}\big)$$ $$\text{MOT}\leftarrow\pi_{\text{composizione},\text{veicolo},\text{fornitore}}(\text{MOTRICI})$$ $$\text{NUMFOR}\leftarrow\rho_{\text{NumF}\leftarrow\text{COUNT}(*)}\big({}_{\text{composizione},\text{fornitore}}\mathcal{F}_{\text{COUNT}(*)}(\text{MOT})\big)$$ $$\pi_{\text{composizione},\text{origine},\text{destinazione}}\big(\sigma_{\text{NumF}=2}(\text{NUMFOR})\bowtie\text{TRATTA}\big)$$

(join naturale: l'unico attributo in comune è composizione).

La proiezione che costruisce MOT elimina i duplicati: una motrice con due pezzi di f1 genera due righe in MOTRICI ma una sola in MOT. Perciò COUNT(*) su MOT conta le motrici distinte di f1 per composizione. La condizione $\text{NumF}=2$ scarta sia le composizioni con una sola motrice (non potranno mai arrivare a 2), sia quelle in cui f1 fornisce pezzi a una sola delle due motrici: la soluzione del corso lo nota ("evita di aggiungere attributi derivati extra"). Il risultato è una tratta per composizione, e il join con TRATTA dà origine e destinazione.

SQL.

SELECT t.origine, t.destinazione
FROM tratta AS t
WHERE t.composizione IN (
  SELECT v.composizione
  FROM veicolo AS v INNER JOIN vende AS ve ON ve.veicolo = v.id
  WHERE v.tipo = 'motrice' AND ve.fornitore = 'f1'
  GROUP BY v.composizione
  HAVING COUNT(DISTINCT v.id) = 2);

Risultato sui dati: la sola tratta Padova-Venezia (K1: m1 ha pezzi di f1, m2 anche). K2 ha una sola motrice di f1 (m3, mentre m4 è di f2), K3 una sola motrice in tutto, K4 ha m6 con due pezzi di f1 ma m7 nessuno.

La trappola. Con COUNT(*) al posto di COUNT(DISTINCT v.id) la query restituirebbe Torino-Milano (K4: le due righe di VENDE di m6 sembrano "due motrici") e scarterebbe Padova-Venezia (m1 ha due righe, m2 una: tre). Provato sui dati. La proiezione dell'algebra evita l'errore; in SQL serve DISTINCT dentro l'aggregato.

Un'alternativa senza raggruppamento è un self-join sulle motrici della stessa composizione con a.id < b.id ed EXISTS su VENDE per ciascuna (stesso risultato, provato).

Errori nel materiale del corso (riassunto)

• Query 1: $\sigma_{\text{NPezzi}=\text{MAXF}}(R_1)$ confronta un attributo con una relazione; vale solo come abbreviazione del join.
• Query 2: la soluzione presuppone l'attributo derivato costo; la versione senza derivato qui sopra mostra che il costo si ricava da VENDE e OFFRE.
• Query 3: la traduzione diretta in SQL richiede COUNT(DISTINCT ...); con COUNT(*) si contano le righe di VENDE e non le motrici. Inoltre l'espressione usa tipo = 'motrice', valida solo con la scelta di ristrutturazione 2 (veicolo unico).