Esercizio - Algebra relazionale estesa su pizzeria, ristoranti e conteggi con NULL (esercizi di algebra estesa del corso)

---

1. Incasso per tipo di pizza, totale e massimo

L'incasso di una pizza è $\text{Prezzo}\cdot\text{Quantita}$: un valore calcolato tra attributi della stessa tupla, quindi una proiezione generalizzata.

$$\text{INCOME}\leftarrow\rho_{\text{Totale}}\Big(\pi_{\text{Pizza},\ \text{Prezzo}\cdot\text{Quantita}}(\text{Pizzeria})\Big)$$

SQL: SELECT Pizza, Prezzo * Quantita AS Totale FROM Pizzeria;.

Incasso totale e massimo. Su INCOME si applica un'aggregazione con due funzioni: una sola tupla con due attributi.

$$\mathcal{F}_{\text{SUM}(\text{Totale}),\ \text{MAX}(\text{Totale})}(\text{INCOME})\quad\Rightarrow\quad(376,\ 200)$$

perché $80+200+18+78=376$ e il massimo è quello della Capricciosa. Per dare nomi leggibili: $\rho_{\text{TotIncome},\text{MaxIncome}\leftarrow\text{SUM}(\text{Totale}),\text{MAX}(\text{Totale})}$. In SQL basta una query: SELECT SUM(Prezzo*Quantita) AS TotIncome, MAX(Prezzo*Quantita) AS MaxIncome FROM Pizzeria; (le due funzioni lavorano su un'espressione, quindi la proiezione intermedia non serve).

Errore nel materiale del corso. Nella slide introduttiva la Capricciosa ha prezzo 11 (e totale 220), mentre le slide con la soluzione usano 10 (totale 200, somma 376). I risultati 376 e 200 valgono con il prezzo 10; con 11 sarebbero 396 e 220 (verificato). È una discrepanza tra una slide e le successive.

2. Incasso di ogni sede

Sedi: $(1,\text{Vicenza},\text{Margherita},8,10)$, $(2,\text{Padova},\text{Capricciosa},11,20)$, $(3,\text{Padova},\text{Margherita},9,2)$, $(4,\text{Vicenza},\text{Marinara},6,13)$.

Il dato che serve non è un attributo, e va sommato su un gruppo: raggruppamento per Sede con la somma dell'incasso di ogni riga.

$$_{\text{Sede}}\mathcal{F}_{\text{SUM}(\text{Incasso})}\Big(\pi_{\text{Sede},\ \text{Incasso}\leftarrow\text{Prezzo}\cdot\text{Quantita}}(\text{Pizzeria})\Big)$$

Passi: la proiezione generalizzata calcola 80, 220, 18, 78; i gruppi sono Vicenza ($80+78=158$) e Padova ($220+18=238$). Grado $n+m=1+1=2$, cardinalità 2 (due gruppi).

SQL: SELECT Sede, SUM(Prezzo * Quantita) AS Incasso FROM Pizzeria GROUP BY Sede;.

3. Ristoranti: totale e media per ristorante

Restaurant: $(R_1,10/12,80,100)$, $(R_2,10/12,90,200)$, $(R_2,11/12,70,300)$, $(R_1,11/12,100,150)$, $(R_3,11/12,60,70)$ (con Name abbreviato).

Passo 1: l'incasso di ogni riga (menù per posti):

$$\text{INCOME}\leftarrow\rho_{\text{Income}\leftarrow\text{PriceMenu}\cdot\text{Seats}}\Big(\pi_{\text{Name},\text{Date},\ \text{PriceMenu}\cdot\text{Seats}}(\text{Restaurant})\Big)$$

Passo 2: raggruppamento per ristorante con somma e media:

$$_{\text{Name}}\mathcal{F}_{\text{SUM}(\text{Income}),\ \text{AVG}(\text{Income})}(\text{INCOME})$$

$R_3$ ha un solo giorno: somma e media coincidono. Si può rinominare con $\rho_{\text{Total},\text{Average}\leftarrow\text{SUM}(\text{Income}),\text{AVG}(\text{Income})}$. In SQL: SELECT Name, SUM(PriceMenu*Seats) AS Total, AVG(PriceMenu*Seats) AS Average FROM Restaurant GROUP BY Name;.

Errore nel materiale del corso. Le slide riportano per le prime quattro righe incassi di 800, 1800, 2100 e 1500, e quindi totali 2300, 3900 (e medie 1150, 1950). Con i dati della tabella $80\cdot100=8000$, $90\cdot200=18000$, $70\cdot300=21000$, $100\cdot150=15000$: i valori corretti sono 8000, 18000, 21000, 15000, con totali 23000 e 39000 e medie 11500 e 19500. Solo la riga di $R_3$ ($60\cdot70=4200$) è giusta nella slide (verificato su SQLite). La procedura delle slide è comunque quella giusta.

Estensione: il ristorante con l'incasso totale massimo (pattern del massimo della nota di teoria). Con $T\leftarrow\rho_{\text{Total}\leftarrow\text{SUM}(\text{Income})}\big({}_{\text{Name}}\mathcal{F}_{\text{SUM}(\text{Income})}(\text{INCOME})\big)$ (totale per ristorante) e $M\leftarrow\rho_{\text{MaxT}\leftarrow\text{MAX}(\text{Total})}\big(\mathcal{F}_{\text{MAX}(\text{Total})}(T)\big)$ (il massimo dei totali):

$$\pi_{\text{Name},\text{Total}}\big(T\bowtie_{\text{Total}=\text{MaxT}}M\big)=\{(R_2,39000)\}$$

In SQL: ... GROUP BY Name HAVING SUM(PriceMenu*Seats) = (SELECT MAX(t) FROM (SELECT SUM(PriceMenu*Seats) AS t FROM Restaurant GROUP BY Name) AS q): restituisce solo $R_2$ (provato; in PostgreSQL l'alias q è obbligatorio).

4. NULL e aggregazione

Esami. Exam(Student, Date, Mark, Course): $(123456,10/12,20,\text{Analysis})$, $(123456,10/12,25,\text{Databases})$, $(384752,11/12,\text{NULL},\text{Analysis})$, $(384752,11/12,18,\text{Physics})$, $(3841345,11/12,\text{NULL},\text{Physics})$.

Con COALESCE(Mark, 0) la media diventerebbe $63/5=12{,}6$: contare il "nessun voto" come zero è una scelta di significato, non un dettaglio.

Lavoratori. Worker(Badge, Name, Surname, Car): $(123456,\text{Mario},\text{Rossi},\text{Volvo})$, $(384762,\text{Matteo},\text{Verdi},\text{NULL})$, $(382320,\text{Marco},\text{Verdi},\text{NULL})$, $(596830,\text{John},\text{Rossi},\text{NULL})$, $(192092,\text{Mary},\text{Bianchi},\text{Fiat})$, $(384135,\text{Isabel},\text{Bianchi},\text{Fiat})$.

Il raggruppamento tratta i NULL come un gruppo unico: i tre lavoratori senza auto stanno nel gruppo $\text{NULL}$ con conteggio 3 (verificato in SQLite con GROUP BY Car).

Errore nel materiale del corso. La slide scrive $\text{COUNT}(\text{car})=2$ e $\text{COUNT}(\text{surname},\text{car})=4$. Con la semantica SQL standard $\text{COUNT}(\text{Car})$ conta i valori non nulli, duplicati compresi, e vale 3; il valore 2 è quello di $\text{COUNT}(\text{DISTINCT Car})$. Il $4$ del conteggio su due attributi coincide con il numero di coppie distinte $(\text{Surname},\text{Car})$ (compresa quella con NULL), e in SQL non esiste un $\text{COUNT}$ su più colonne senza DISTINCT e parentesi di riga. I numeri della slide vanno letti come conteggi di valori distinti.