Esercizio - Interrogazioni sulla mensa universitaria (tema d'esame 13 febbraio 2012)

Esame

In questa pagina 6

Testo (compito d'esame del 13 febbraio 2012, "Mensa universitaria", dal corso di Basi di Dati, Ing. Informatica UniPD). La mensa vuole gestire i rifornimenti del magazzino e i pasti per gli studenti:

di ogni ingrediente acquistato si vuole sapere se si trova nel magazzino grande e/o nei due magazzini piccoli; i magazzini differiscono per posizione (edifici diversi), metri quadri e quantità massima di ingredienti conservabili; un ingrediente ha un costo unitario e può essere presente in quantità diverse nei tre magazzini;
ogni giorno la mensa può preparare piatti a scelta da due menù: vegetariano e non; ogni menù è composto da quattro piatti che hanno un nome e appartengono a una categoria (primo, secondo, contorno, dolce); ogni piatto ha un costo (il prezzo che paga lo studente); un menù può essere riproposto in giorni diversi; di ogni piatto si sa da quanti e quali ingredienti è composto;
ogni giorno si registra quali piatti tra i menù disponibili sono stati ordinati dagli studenti (uno studente non deve prendere necessariamente tutti i piatti del menù).

Interrogazioni, in algebra e SQL:

la quantità totale di ogni ingrediente immagazzinato;
i piatti del menù vegetariano di oggi;
quanto hanno speso in media gli studenti che hanno ordinato un primo di un menù non vegetariano.

Teoria: SQL - interrogazioni, join e aggregazioni, Algebra relazionale estesa - divisione, aggregazione e outer join) conta le tuple, i NULL formano un gruppo a sé. Gli outer join (left, right, full) mantengono con NULL le tuple dangling del join; i NULL nella chiave di join non si accoppiano mai. La divisione R÷S dà gli x tali che (x,y) sta in R per ogni y di S, e si ottiene con π, × e −. Ricette: massimo = aggregazione + join con il massimo, "tutti" = divisione o complemento, "almeno due" = self-join.Algebra relazionale estesa - divisione, aggregazione e outer join →. La progettazione ER (menù del giorno come entità, "scelta" tra studente, menù del giorno e piatto, gerarchia dei piatti da non modellare con sottoclassi grande/piccolo) è nell'esercizio di progettazione della stessa traccia.

Progettazione ER e schema relazionale della stessa traccia: Esercizio - Progettazione ER e schema relazionale della mensa universitaria (tema d'esame 13 febbraio 2012).

Schema relazionale (dalla soluzione del corso)

Chiavi in grassetto.

Relazione	Attributi	Note
`STUDENTE`	matricola
`MENU`	id, veg	`veg` vero per il menù vegetariano
`MENU_GIORNO`	id $\to$ `MENU`, giorno	il menù `id` è proposto il `giorno`; la coppia (id, giorno) identifica il "menù del giorno"; lo stesso menù compare in più giorni; vincolo: per ogni giorno due menù, uno vegetariano e uno no
`PIATTO`	nome, prezzo, categoria
`COMPOSTO`	menu $\to$ `MENU`, piatto $\to$ `PIATTO`	vincolo: ogni menù ha 4 piatti, uno per categoria; un menù vegetariano ha solo piatti vegetariani (si controlla dagli ingredienti)
`SCEGLIE`	menu, giorno, piatto, studente $\to$ `STUDENTE`; $(\text{menu},\text{giorno})\to$ `MENU_GIORNO`; $(\text{menu},\text{piatto})\to$ `COMPOSTO`	ternaria studente–menù del giorno–piatto; il piatto scelto deve appartenere al menù
`INGREDIENTE`	nome, prezzo_unitario, descrizione
`PREPARATO`	piatto $\to$ `PIATTO`, ingrediente $\to$ `INGREDIENTE`, quantita	quanto di ogni ingrediente serve per un piatto
`MAGAZZINO`	codice, mq, qta_max, indirizzo	grande/piccolo non è una sottoclasse (sarebbe un errore di modellazione: non hanno attributi o relazioni diverse), si ricava da `mq` e `qta_max`
`CONSERVA`	magazzino $\to$ `MAGAZZINO`, ingrediente $\to$ `INGREDIENTE`, quantita	un ingrediente è in al più 3 magazzini; 0 magazzini = ingrediente terminato

Due osservazioni di progetto: la categoria del piatto è un attributo (non quattro sottoclassi: nel menù sarebbe un'enorme complicazione, con quattro relazioni uno-a-uno per ogni menù) e il giorno non è un'entità: è una proprietà del "menù del giorno".

Dati inventati. Studenti 1, 2, 3, 4. Menù 1 e 3 vegetariani, 2 e 4 no. MENU_GIORNO: 10/12: menù 1 e 2; 11/12: menù 3 e 2 (il menù 2 è riproposto); 12/12: menù 1 e 4. Piatti (nome, prezzo, categoria): pasta al pomodoro 4,0 primo; risotto ai funghi 4,5 primo; lasagne 5,0 primo; carbonara 5,5 primo; frittata 4,0 secondo; cotoletta 6,0 secondo; parmigiana 5,5 secondo; arrosto 6,5 secondo; insalata 2,0 contorno; patate 2,5 contorno; spinaci 2,5 contorno; gelato 2,5 dolce; tiramisù 3,0 dolce; torta 3,0 dolce. COMPOSTO: menù 1: pasta al pomodoro, frittata, insalata, gelato; menù 2: lasagne, cotoletta, patate, tiramisù; menù 3: risotto ai funghi, parmigiana, spinaci, torta; menù 4: carbonara, arrosto, insalata, torta. CONSERVA (magazzino, ingrediente, quantità): (M1, farina, 100), (M2, farina, 30), (M1, pomodoro, 50), (M3, uova, 20), (M1, riso, 80), (M2, riso, 10), (M3, riso, 5); l'ingrediente funghi non è in nessun magazzino.

SCEGLIE (studente: menù, giorno, piatto): 1: (2, 10/12, lasagne), (2, 10/12, cotoletta), (3, 11/12, risotto ai funghi), (3, 11/12, torta); 2: (1, 10/12, pasta al pomodoro), (1, 10/12, frittata), (1, 10/12, gelato), (4, 12/12, carbonara), (4, 12/12, torta); 3: (2, 11/12, cotoletta), (2, 11/12, patate), (2, 10/12, lasagne); 4: (4, 12/12, arrosto), (4, 12/12, insalata). Un tentativo di inserire una scelta di un piatto non del menù, per esempio (1, 10/12, lasagne, 4) (le lasagne sono del menù 2), è rifiutato dalla chiave esterna $(\text{menu},\text{piatto})\to$ COMPOSTO (provato).

Query 1: la quantità totale di ogni ingrediente immagazzinato

Idea. Un ingrediente può stare in più magazzini, ciascuno con la sua quantità: si somma sul gruppo di tutte le righe di CONSERVA dello stesso ingrediente.

Algebra. $_{\text{ingrediente}}\mathcal{F}_{\text{SUM}(\text{quantita})}(\text{CONSERVA})$ .

SQL.

sql

SELECT ingrediente, SUM(quantita) AS qta
FROM conserva
GROUP BY ingrediente;

Risultato: farina 130 ( $100+30$ ), pomodoro 50, riso 95 ( $80+10+5$ ), uova 20. Gli ingredienti non presenti in nessun magazzino (funghi) non compaiono perché CONSERVA non ha righe per loro. Se serve anche il "0", si parte da INGREDIENTE:

sql

SELECT i.nome, COALESCE(SUM(c.quantita), 0) AS qta
FROM ingrediente AS i LEFT JOIN conserva AS c ON c.ingrediente = i.nome
GROUP BY i.nome;

(funghi 0). Per la richiesta "immagazzinato" la prima forma è già corretta: un ingrediente non immagazzinato non ha quantità da riportare.

Idea. Il menù vegetariano è un MENU con veg = vero; "oggi" lo sceglie MENU_GIORNO; i piatti stanno in COMPOSTO. Il collegamento è MENU $\bowtie$ MENU_GIORNO (su id) $\bowtie$ COMPOSTO (su id = menu).

Algebra.

$\text{MENU\_VEG}\leftarrow\sigma_{\text{veg}=\text{vero}}(\text{MENU})\qquad\text{MENU\_OGGI}\leftarrow\sigma_{\text{giorno}=\text{oggi}}(\text{MENU\_GIORNO})$ $\pi_{\text{piatto}}\big(\text{MENU\_VEG}\bowtie\text{MENU\_OGGI}\bowtie_{\text{id}=\text{menu}}\text{COMPOSTO}\big)$

Il primo join tra MENU_VEG e MENU_OGGI è naturale (l'unico attributo comune è id); il secondo è un equi-join con nomi diversi.

SQL.

sql

SELECT c.piatto
FROM menu AS m
     INNER JOIN menu_giorno AS mg ON m.id = mg.id
     INNER JOIN composto AS c ON mg.id = c.menu
WHERE m.veg = TRUE AND mg.giorno = CURRENT_DATE;

Risultato con giorno = '2025-12-11' al posto di CURRENT_DATE: il menù vegetariano dell'11/12 è il 3: parmigiana, risotto ai funghi, spinaci, torta. Il menù 2 non vegetariano, riproposto lo stesso giorno, è escluso da veg = TRUE.

Osservazioni. (1) "Oggi" in SQL è CURRENT_DATE, non una data scritta a mano come nella soluzione. (2) Le date vanno scritte in formato ISO '2025-12-11': il formato '13-12-2025' della soluzione dipende dalle impostazioni del DBMS (DateStyle in PostgreSQL) e non è portabile. (3) Nelle tre versioni della soluzione la data cambia (13-12-2025, 03-12-25, 10-12-2025): è un refuso, la struttura della query è quella.

Idea. Prima si individuano gli studenti che hanno scelto almeno un primo da un menù non vegetariano; poi si calcola "quanto hanno speso in media". La richiesta ha più letture:

(A) per ogni studente, la media del prezzo dei primi presi da menù non vegetariani (è ciò che fa la prima soluzione del corso);
(B) la spesa totale di ciascuno di quegli studenti (somma dei prezzi di tutti i piatti che hanno ordinato) e poi la media di queste spese: un numero solo, "quanto hanno speso in media gli studenti";
(C) per ogni studente, il prezzo medio dei piatti (la "opzione 2" del corso).

Soluzione del corso, lettura (A) (algebra).

$\text{MENU\_NON\_VEG}\leftarrow\sigma_{\text{veg}=\text{falso}}(\text{MENU})\qquad\text{SCELTI}\leftarrow\text{MENU\_NON\_VEG}\bowtie_{\text{id}=\text{menu}}\text{SCEGLIE}$ $\text{PRIMI}\leftarrow\text{SCELTI}\bowtie_{\text{piatto}=\text{nome}}\sigma_{\text{categoria}='\text{primo}'}(\text{PIATTO})\qquad{}_{\text{studente}}\mathcal{F}_{\text{AVG}(\text{prezzo})}(\text{PRIMI})$

sql

SELECT s.studente, AVG(p.prezzo) AS media_prezzo_primi
FROM menu AS m
     INNER JOIN scegle AS s ON m.id = s.menu
     INNER JOIN piatto AS p ON s.piatto = p.nome
WHERE m.veg = FALSE AND p.categoria = 'primo'
GROUP BY s.studente;

Risultato: studente 1: 5,0 (lasagne), studente 2: 5,5 (carbonara), studente 3: 5,0 (lasagne). Lo studente 4 non ha preso primi: non compare.

Lettura (B): la spesa degli studenti (corretta per "quanto hanno speso"). Il filtro (primo da menù non vegetariano) seleziona gli studenti; la spesa si calcola su tutte le loro scelte:

sql

SELECT s.studente, SUM(p.prezzo) AS spesa
FROM scegle AS s INNER JOIN piatto AS p ON s.piatto = p.nome
WHERE s.studente IN (SELECT s2.studente
                     FROM scegle AS s2
                          INNER JOIN menu AS m ON m.id = s2.menu
                          INNER JOIN piatto AS p2 ON p2.nome = s2.piatto
                     WHERE m.veg = FALSE AND p2.categoria = 'primo')
GROUP BY s.studente;

Spese: studente 1: $5+6+4{,}5+3=18{,}5$ ; studente 2: $4+4+2{,}5+5{,}5+3=19$ ; studente 3: $6+2{,}5+5=13{,}5$ . Per avere una sola media si racchiude la query in SELECT AVG(spesa) FROM (...) AS t: $(18{,}5+19+13{,}5)/3=\mathbf{17}$ . L'algebra procede allo stesso modo: $\pi_{\text{studente}}(\text{PRIMI})$ seleziona gli studenti, un join con SCEGLIE e PIATTO ne porta tutti i piatti, $_{\text{studente}}\mathcal{F}_{\text{SUM}(\text{prezzo})}$ dà le spese e $\mathcal{F}_{\text{AVG}}$ la media. Con AVG al posto di SUM nella query interna si ha la lettura (C) (studente 1: 4,625; 2: 3,8; 3: 4,5).

Osservazione sul materiale del corso. La soluzione "opzione 2 (difficile)" del corso calcola, con un join di più tabelle, i piatti di ciascuno studente e applica $\mathcal{F}_{\text{AVG}(\text{prezzo})}$ per studente: calcola quindi un prezzo medio per piatto, e non la spesa. La domanda "quanto hanno speso" corrisponde meglio a una somma; il testo non dice con certezza quale lettura intendere, perciò conviene dichiararla. Per tutte e tre le letture il GROUP BY è sull'identificatore dello studente.

Passi da non saltare.

Il join con PIATTO deve avvenire dopo aver individuato lo studente, e per la lettura (B) la categoria 'primo' filtra gli studenti, non i piatti da sommare: se si lasciasse categoria = 'primo' nella query della spesa, si sommerebbero solo i primi (è la lettura (A) con SUM).
SCEGLIE contiene anche il giorno: la query non lo usa, quindi somma le scelte di tutti i giorni; per il "mese" o "oggi" basterebbe una condizione su giorno.
Gli studenti senza primi non vegetariani (qui lo studente 4) sono esclusi dalla sottoquery.

Errori nel materiale del corso (riassunto)

Query 2: data scritta a mano e in formato non portabile; date diverse nelle tre versioni.
Query 3: la lettura (A) calcola la media del prezzo dei primi, mentre il testo parla di quanto hanno speso gli studenti; la "opzione 2" media i prezzi dei piatti, non somma la spesa.

Versione ripasso

Schema (soluzione del corso): STUDENTE(matricola), MENU(id, veg), MENU_GIORNO(id, giorno), PIATTO(nome, prezzo, categoria), COMPOSTO(menu, piatto), SCEGLIE(menu, giorno, piatto, studente), INGREDIENTE(nome, prezzo_unitario, descrizione), PREPARATO(piatto, ingrediente, quantita), MAGAZZINO(codice, mq, qta_max, indirizzo), CONSERVA(magazzino, ingrediente, quantita). Teoria: SQL - interrogazioni, join e aggregazioni, Algebra relazionale estesa - divisione, aggregazione e outer join) conta le tuple, i NULL formano un gruppo a sé. Gli outer join (left, right, full) mantengono con NULL le tuple dangling del join; i NULL nella chiave di join non si accoppiano mai. La divisione R÷S dà gli x tali che (x,y) sta in R per ogni y di S, e si ottiene con π, × e −. Ricette: massimo = aggregazione + join con il massimo, "tutti" = divisione o complemento, "almeno due" = self-join.Algebra relazionale estesa - divisione, aggregazione e outer join →.

Quantità totale per ingrediente. $_{\text{ingrediente}}\mathcal{F}_{\text{SUM}(\text{quantita})}(\text{CONSERVA})$ : farina $130$ ( $100+30$ ), pomodoro $50$ , riso $95$ ( $80+10+5$ ), uova $20$ . Il funghi, in nessun magazzino, non compare; con un LEFT JOIN da INGREDIENTE e COALESCE compare con 0.

sql

SELECT ingrediente, SUM(quantita) AS qta FROM conserva GROUP BY ingrediente;

Con LEFT JOIN da INGREDIENTE e COALESCE(SUM(c.quantita), 0) il funghi compare con 0.

Piatti del menù vegetariano di oggi. $\pi_{\text{piatto}}\big(\sigma_{\text{veg}=\text{vero}}(\text{MENU})\bowtie\sigma_{\text{giorno}=\text{oggi}}(\text{MENU\_GIORNO})\bowtie_{\text{id}=\text{menu}}\text{COMPOSTO}\big)$ . Con i dati, l'11/12 il menù vegetariano è il 3: parmigiana, risotto ai funghi, spinaci, torta. Il menù 2, non vegetariano e riproposto lo stesso giorno, è escluso.

sql

SELECT c.piatto FROM menu AS m
     INNER JOIN menu_giorno AS mg ON m.id = mg.id
     INNER JOIN composto AS c ON mg.id = c.menu
WHERE m.veg = TRUE AND mg.giorno = CURRENT_DATE;

"Oggi" è CURRENT_DATE. Le date vanno scritte in formato ISO, per esempio '2025-12-11': la soluzione del corso usa un formato che dipende dal DBMS.

Spesa media degli studenti che hanno preso un primo da un menù non vegetariano. Le letture sono tre:
- (A) media del prezzo dei primi per studente, come nel corso: 5,0; 5,5; 5,0 (lo studente 4 non ha primi ed è escluso);
- (B) spesa = somma del prezzo di tutti i piatti scelti: 18,5; 19; 13,5, quindi media 17;
- (C) prezzo medio per piatto: 4,625; 3,8; 4,5.
La categoria 'primo' filtra gli studenti, non i piatti da sommare. Lettura (A):

sql

SELECT s.studente, AVG(p.prezzo) AS media_prezzo_primi
FROM menu AS m INNER JOIN scegle AS s ON m.id = s.menu
     INNER JOIN piatto AS p ON s.piatto = p.nome
WHERE m.veg = FALSE AND p.categoria = 'primo'
GROUP BY s.studente;

Lettura (B) in SQL: una sottoquery sceglie gli studenti con un primo da menù non vegetariano; la query esterna somma il prezzo di tutti i loro piatti per studente; un AVG sopra questo risultato dà la media unica, 17. Spese: studente 1 $5+6+4{,}5+3=18{,}5$ ; studente 2 $4+4+2{,}5+5{,}5+3=19$ ; studente 3 $6+2{,}5+5=13{,}5$ .
In algebra: $\text{SCELTI}\leftarrow\sigma_{\text{veg}=\text{falso}}(\text{MENU})\bowtie_{\text{id}=\text{menu}}\text{SCEGLIE}$ , poi $\text{PRIMI}$ con il join su PIATTO filtrata per categoria 'primo'.

Errori tipici

Filtrare la categoria anche nella somma della spesa.
Confondere SUM e AVG.
Date non in formato ISO.
Ingredienti senza magazzino dimenticati nella query 1.
Categoria modellata con sottoclassi invece che con un attributo.

Esercizi su questo argomento

Esercizio - Progettazione ER e schema relazionale della mensa universitaria (tema d'esame 13 febbraio 2012)