Esercizio - Interrogazioni sulle formule scientifiche (tema d'esame 22 gennaio 2025)

Query 1: le formule attribuite a scienziati con cognome "Bernoulli"

Idea. Si parte da SCIENZIATO filtrato per cognome, si passa per $R_3$ (chi ha definito cosa) e si arriva a FORMULA: una catena di due join.

Algebra. La rinomina degli attributi omonimi (id, nome) di SCIENZIATO evita ambiguità con FORMULA:

$$\text{SC}\leftarrow\rho_{\text{ids},\text{nomes},\text{campos}\leftarrow\text{id},\text{nome},\text{campo}}(\text{SCIENZIATO})$$ $$\pi_{\text{id},\text{nome},\text{descrizione},\text{espressione},\text{anno}}\Big(\text{FORMULA}\bowtie_{\text{id}=\text{formula}}R_3\bowtie_{\text{scienziato}=\text{ids}}\sigma_{\text{cognome}='\text{Bernoulli}'}(\text{SC})\Big)$$

(la soluzione del corso proietta $\text{id},\text{descrizione},\text{espressione},\text{anno}$ e dimentica nome; il nome della formula è utile e l'SQL del corso lo seleziona: conviene tenerlo in entrambi.) La proiezione elimina i duplicati.

SQL.

SELECT DISTINCT f.id, f.nome, f.descrizione, f.espressione, f.anno
FROM formula AS f
     INNER JOIN r3 AS r ON f.id = r.formula
     INNER JOIN scienziato AS s ON r.scienziato = s.id
WHERE s.cognome = 'Bernoulli';

Risultato: formule 2 (equazione di Bernoulli dei fluidi, di Daniel), 3 (legge dei grandi numeri, di Jacob) e 6 (equazione di Bernoulli per le EDO, di Jacob e Daniel).

Errore nella soluzione del corso. L'SQL del corso manca del DISTINCT. La formula 6 è definita da due Bernoulli, quindi il join produce due righe per essa: senza DISTINCT la query restituisce 4 righe (2, 3, 6, 6) mentre l'algebra, dove la proiezione elimina i duplicati, ne dà 3. Algebra e SQL del corso non sono equivalenti. L'esempio è realistico: quando una proiezione scarta la chiave del join (qui scienziato), i duplicati sono da attendersi.

Query 2: scienziati di Fisica che hanno definito formule di Matematica

Idea. Due condizioni sul "campo", che sono due cose diverse: il campo principale dello scienziato (SCIENZIATO.campo) e il campo della formula (FORMULA.campo). Entrambi sono identificatori di CAMPO_STUDIO, non nomi: per confrontarli con 'Fisica' e 'Matematica' bisogna passare per CAMPO_STUDIO due volte (due ruoli diversi, quindi due alias).

Soluzione del corso (algebra).

$$\text{FISICA}\leftarrow\sigma_{\text{campo}='\text{Fisica}'}(\text{SCIENZIATO})\qquad\text{FORM\_MAT}\leftarrow\sigma_{\text{campo}='\text{Matematica}'}(\text{FORMULA})$$ $$\text{FORM\_MAT}\leftarrow\rho_{\text{IDF},\text{nomeF}\leftarrow\text{id},\text{nome}}\big(\pi_{\text{id},\text{nome}}(\text{FORM\_MAT})\big)\qquad\pi_{\text{id},\text{nome},\text{cognome}}\big(\text{FORM\_MAT}\bowtie_{\text{IDF}=\text{formula}}R_3\bowtie_{\text{scienziato}=\text{id}}\text{FISICA}\big)$$

SELECT s.id, s.nome, s.cognome
FROM scienziato AS s
     INNER JOIN r3 AS r ON s.id = r.scienziato
     INNER JOIN formula AS f ON r.formula = f.id
WHERE f.campo = 'Matematica' AND s.campo = 'Fisica';

Errori nella soluzione del corso. (1) campo è un identificatore (chiave esterna verso CAMPO_STUDIO.id), non il nome del campo: f.campo = 'Matematica' confronta un intero con una stringa e non trova nulla (provato: 0 righe). Bisogna fare il join con CAMPO_STUDIO e filtrare su nome. (2) Come nella query 1, manca il DISTINCT: uno scienziato che ha definito due formule di Matematica comparirebbe due volte. (3) La rinomina $\rho_{\text{IDF},\text{nomeF}\leftarrow\dots}$ è corretta, ma serve anche a evitare il conflitto tra gli id di FORMULA e di SCIENZIATO.

Soluzione corretta (SQL).

SELECT DISTINCT s.id, s.nome, s.cognome
FROM scienziato AS s
     INNER JOIN campo_studio AS cs ON cs.id = s.campo           -- campo principale dello scienziato
     INNER JOIN r3 AS r ON r.scienziato = s.id
     INNER JOIN formula AS f ON f.id = r.formula
     INNER JOIN campo_studio AS cf ON cf.id = f.campo           -- campo della formula
WHERE cs.nome = 'Fisica' AND cf.nome = 'Matematica';

campo_studio compare due volte, con alias cs e cf perché ha due ruoli. Risultato: Daniel Bernoulli (Fisica; ha definito la formula 6, di Matematica) e Isaac Newton (Fisica; ha definito il binomio di Newton, di Matematica). Jacob Bernoulli e Gauss hanno la Matematica come campo principale: esclusi; Curie ha la Chimica.

Algebra corretta. Si seleziona sul nome del campo e si porta l'id con un join, con le rinomine per gli id:

$$\text{FIS}\leftarrow\pi_{\text{id},\text{nome},\text{cognome}}\Big(\text{SCIENZIATO}\bowtie_{\text{campo}=\text{cid}}\rho_{\text{cid},\text{cnome}\leftarrow\text{id},\text{nome}}\big(\sigma_{\text{nome}='\text{Fisica}'}(\text{CAMPO\_STUDIO})\big)\Big)$$ $$\text{FM}\leftarrow\rho_{\text{fid}\leftarrow\text{id}}\Big(\pi_{\text{id}}\big(\text{FORMULA}\bowtie_{\text{campo}=\text{mid}}\rho_{\text{mid},\text{mnome}\leftarrow\text{id},\text{nome}}(\sigma_{\text{nome}='\text{Matematica}'}(\text{CAMPO\_STUDIO}))\big)\Big)$$ $$\pi_{\text{sid},\text{snome},\text{scogn}}\Big(\rho_{\text{sid},\text{snome},\text{scogn}\leftarrow\text{id},\text{nome},\text{cognome}}(\text{FIS})\bowtie_{\text{sid}=\text{scienziato}}\big(\text{FM}\bowtie_{\text{fid}=\text{formula}}R_3\big)\Big)$$

Provato con un interprete di algebra: stesso risultato dell'SQL (Daniel Bernoulli e Newton).

Query 3: lo scienziato che ha definito il maggior numero di formule

Idea. Il pattern del massimo: (1) numero di formule per scienziato, (2) il massimo di questi numeri, (3) gli scienziati con quel valore.

Algebra.

$$\text{CONTEGGIO\_F}\leftarrow\rho_{\text{scienziato},\text{conteggio}}\big({}_{\text{scienziato}}\mathcal{F}_{\text{COUNT}(*)}(R_3)\big)\qquad\text{MAX\_C}\leftarrow\rho_{\text{maxc}\leftarrow\text{MAX}(\text{conteggio})}\big(\mathcal{F}_{\text{MAX}(\text{conteggio})}(\text{CONTEGGIO\_F})\big)$$ $$\pi_{\text{scienziato},\text{conteggio}}\big(\text{CONTEGGIO\_F}\bowtie_{\text{conteggio}=\text{maxc}}\text{MAX\_C}\big)$$

La soluzione del corso scrive l'ultimo passo come $\sigma_{\text{conteggio}=\text{MAX\_C}}(\text{CONTEGGIO\_F})$, con MAX_C una relazione: è una forma abbreviata del join.

SQL del corso (con vista):

CREATE VIEW Conteggio_F AS
SELECT scienziato, COUNT(*) AS conteggio
FROM r3
GROUP BY scienziato;

SELECT scienziato, conteggio
FROM Conteggio_F
WHERE conteggio = (SELECT MAX(conteggio) FROM Conteggio_F);

Risultato sui dati: scienziato 3 (Newton), con 3 formule (1, 7, 9); gli altri ne hanno 2 (Daniel, Jacob, Gauss) o 1 (Curie). La vista evita di ripetere la tabella derivata. La query restituisce tutti gli scienziati a pari merito: se Gauss avesse 3 formule, comparirebbero entrambi (se ne avesse 4 vincerebbe lui, provato).

Per avere nome e cognome basta unire con SCIENZIATO:

SELECT s.id, s.nome, s.cognome, COUNT(*) AS formule
FROM scienziato AS s INNER JOIN r3 ON r3.scienziato = s.id
GROUP BY s.id, s.nome, s.cognome
HAVING COUNT(*) = (SELECT MAX(n) FROM (SELECT COUNT(*) AS n FROM r3 GROUP BY scienziato) AS t);

(Si raggruppa per s.id: si evitano omonimi fusi in un gruppo.) Uno scienziato senza formule non compare in $R_3$, ma per il massimo non conta. Nell'ER del corso ogni formula ha almeno uno scienziato; il viceversa non vale.

Domanda 2 (per la lode): modifiche per rispondere più in fretta

• Query 3: serve contare le righe di $R_3$ per scienziato e prendere il massimo. Un attributo derivato n_formule in SCIENZIATO (aggiornato con un trigger a ogni inserimento o cancellazione in $R_3$) rende la query una selezione: più veloce in lettura, ma ridondanza da mantenere coerente. Alternativa senza ridondanza: indice su $R_3(\text{scienziato})$ e una vista materializzata dei conteggi.
• Query 2: il campo di studio è un'entità, quindi servono due join con CAMPO_STUDIO solo per confrontare i nomi. Se il nome del campo fosse l'identificatore (chiave naturale), SCIENZIATO.campo e FORMULA.campo conterrebbero già 'Fisica' e 'Matematica' e i due join sparirebbero; il prezzo è una chiave testuale ripetuta (spazio) e la difficoltà di rinominare un campo (aggiornamenti a cascata).
• Query 1: un indice su SCIENZIATO(cognome) e su $R_3(\text{scienziato})$ rende veloce la ricerca per cognome; nessuna modifica di schema.

In ogni caso va valutato il carico: quanto spesso si fa la query rispetto a quanto spesso si modificano i dati (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: SQL senza DISTINCT (formula con due Bernoulli compare due volte); nome della formula assente dall'algebra ma presente nell'SQL.
• Query 2: confronto di campo (un identificatore) con una stringa: manca il join con CAMPO_STUDIO; manca DISTINCT.
• Query 3: $\sigma$ con una relazione al posto del join (abbreviazione); il resto è corretto.