Esercizio - Interrogazioni sulla piattaforma di streaming musicale (tema d'esame 12 febbraio 2024)

Query 1: il numero totale di ascolti di ciascun brano

Idea. Il numero di ascolti di un brano è la somma, sugli utenti, di numero in $R_3$: un raggruppamento per brano.

Algebra. $_{\text{brano}}\mathcal{F}_{\text{SUM}(\text{numero})}(R_3)$ (grado 2, una tupla per brano ascoltato).

SQL.

SELECT brano, SUM(numero) AS totale
FROM r3
GROUP BY brano;

Risultato: brano 1: 4; 2: 3; 3: 5; 4: 3; 5: 5; 6: 3; 7: 1; 9: 3; 10: 3. Il brano 8 (mai ascoltato) non compare: con la soluzione del corso si ottengono solo i brani con almeno un ascolto. Se si vuole ogni brano, anche con totale 0, si parte da BRANO con un left join:

SELECT b.codice, b.titolo, COALESCE(SUM(r.numero), 0) AS totale
FROM brano AS b LEFT JOIN r3 AS r ON r.brano = b.codice
GROUP BY b.codice, b.titolo;

(8 compare con 0; COALESCE trasforma il NULL dell'aggregato su un gruppo vuoto in 0.) Nota: il brano è chiamato brano in $R_3$ e codice in BRANO; nella soluzione del corso la query scrive codice per l'attributo di $R_3$, un'incoerenza di nomi.

Query 2: la band rock ascoltata dal maggior numero di utenti

Idea. Si passa da rock $\to$ brani rock $\to$ band autrici $\to$ ascolti, poi si conta per band e si prende il massimo. Il testo precisa "il maggior numero di utenti (non il maggior numero di ascolti)": si contano gli utenti distinti per band.

Soluzione del corso (algebra).

$$\text{GEN\_BRANO}\leftarrow\rho_{\text{cod\_brano}\leftarrow\text{codice}}\big(\sigma_{\text{genere}='\text{Rock}'}(R_2)\bowtie_{\text{brano}=\text{codice}}\text{BRANO}\big)$$ $$\text{ART\_GEN\_BRANO}\leftarrow\rho_{\text{art\_code}\leftarrow\text{codice}}\big(\text{GEN\_BRANO}\bowtie_{\text{codice\_artista}=\text{codice}}\sigma_{\text{tipo}='\text{band}'}(\text{ARTISTA})\big)$$ $$\text{ASCOLTI}\leftarrow\rho_{\text{artista},\text{totale}}\big({}_{\text{art\_code}}\mathcal{F}_{\text{COUNT}(*)}(\text{ART\_GEN\_BRANO}\bowtie_{\text{cod\_brano}=\text{codice}}R_3)\big)$$ $$\text{MAXA}\leftarrow\mathcal{F}_{\text{MAX}(\text{totale})}(\text{ASCOLTI})\qquad\sigma_{\text{totale}=\text{MAXA}}(\text{ASCOLTI})$$

CREATE VIEW Ascolti AS
SELECT a.codice AS artista, COUNT(*) AS totale
FROM R2 AS r INNER JOIN BRANO AS b ON r.brano = b.codice
     INNER JOIN ARTISTA AS a ON b.codice_artista = a.codice
     INNER JOIN R3 AS r3 ON b.codice = r3.brano
WHERE genere = 'Rock' AND tipo = 'band'
GROUP BY a.codice;

SELECT artista, totale FROM Ascolti WHERE totale = (SELECT MAX(totale) FROM Ascolti);

Errore nella soluzione del corso. COUNT(*) conta le righe del join, cioè le coppie (utente, brano) di $R_3$: un utente che ha ascoltato due brani della stessa band viene contato due volte. Il testo chiede il numero di utenti, non di ascolti (e nemmeno di coppie). Sui dati: I Fulmini (1) ha 3 utenti ($u_1$, $u_2$, $u_5$) ma 4 coppie; Nebbia (3) ha solo 2 utenti ($u_2$, $u_3$) ma 6 coppie (ciascuno ascolta 3 brani della band). La query del corso restituisce Nebbia (6), quella corretta I Fulmini (3). Lo stesso errore è nell'algebra: $\mathcal{F}_{\text{COUNT}(*)}$ dopo il join conta le tuple del join.

Soluzione corretta (SQL). Si contano gli utenti distinti per band:

SELECT a.codice, a.nome, COUNT(DISTINCT r3.utente) AS utenti
FROM r2 AS r
     INNER JOIN brano AS b ON r.brano = b.codice
     INNER JOIN artista AS a ON b.artista = a.codice
     INNER JOIN r3 ON b.codice = r3.brano
WHERE r.genere = 'rock' AND a.tipo = 'gruppo'
GROUP BY a.codice, a.nome
HAVING COUNT(DISTINCT r3.utente) = (
  SELECT MAX(n) FROM (
    SELECT COUNT(DISTINCT r3.utente) AS n
    FROM r2 AS r INNER JOIN brano AS b ON r.brano = b.codice
         INNER JOIN artista AS a ON b.artista = a.codice
         INNER JOIN r3 ON b.codice = r3.brano
    WHERE r.genere = 'rock' AND a.tipo = 'gruppo'
    GROUP BY a.codice) AS t);

(Più leggibile con una vista come nella soluzione del corso, sostituendo COUNT(*) con COUNT(DISTINCT r3.utente).) Risultato: I Fulmini, 3 utenti. Le Onde ha 1 utente (via il brano 7, che è sia pop sia rock), Nebbia 2.

Algebra corretta. In algebra si ottengono i distinti con una proiezione (che elimina i duplicati) sulle coppie (band, utente) prima di contare:

$$\text{AU}\leftarrow\pi_{\text{art\_code},\text{nome},\text{utente}}\big(\text{ART\_GEN\_BRANO}\bowtie_{\text{cod\_brano}=\text{brano}}R_3\big)$$ $$U\leftarrow{}_{\text{art\_code},\text{nome}}\mathcal{F}_{\text{COUNT}(*)}(\text{AU})\ \ (\text{attributo rinominato }n)\qquad M\leftarrow\mathcal{F}_{\text{MAX}(n)}(U)\ \ (\text{rinominato }m)\qquad\pi_{\text{art\_code},\text{nome}}\big(U\bowtie_{n=m}M\big)$$

Provato con un interprete di algebra: stesso risultato dell'SQL (I Fulmini).

Query 3: i cantanti leader dei gruppi "pop" dei brani ascoltati da utenti nati dopo il 2000

Idea. Utenti con anno_nascita > 2000 $\to$ i loro ascolti ($R_3$) $\to$ i brani pop ($R_2$) $\to$ l'artista del brano, che deve essere un gruppo $\to$ il suo leader (un altro artista).

Soluzione del corso (algebra).

$$\text{BRANI\_POP}\leftarrow\sigma_{\text{anno\_nascita}>2000}(\text{UTENTE})\bowtie R_3\bowtie\big(\sigma_{\text{genere}='\text{Pop}'}(R_2)\bowtie_{\text{brano}=\text{codice}}\text{BRANO}\big)$$ $$\pi_{\text{leader}}\Big(\sigma_{\text{leader IS NOT NULL}}\big(\rho_{\text{art\_code}\leftarrow\text{codice}}(\text{ARTISTA})\bowtie_{\text{art\_code}=\text{codice\_artista}}\text{BRANI\_POP}\big)\Big)$$

La condizione leader IS NOT NULL serve per tenere i gruppi (i cantanti singoli non hanno leader); l'espressione restituisce i codici dei leader.

Imprecisioni della soluzione del corso. (1) La versione SQL è priva di DISTINCT: un leader compare una volta per ogni ascolto di un brano pop del suo gruppo (la proiezione dell'algebra elimina i duplicati, SELECT no): sui dati la query restituisce 4 righe (12, 14, 12, 12) invece di 2 leader. (2) anno_nascita può essere ambiguo se anche ARTISTA ha un attributo con quel nome (qui anno_nascita_c): si qualifica con l'alias. (3) Il risultato è il solo codice del leader: per "i cantanti leader" è meglio un join con ARTISTA per ottenere nome e cognome. (4) anno_nascita > 2000 scarta l'utente con anno NULL (u5), come è giusto.

SQL corretto.

SELECT DISTINCT l.codice, l.nome, l.cognome_c
FROM utente AS u
     INNER JOIN r3 ON u.username = r3.utente
     INNER JOIN r2 ON r3.brano = r2.brano
     INNER JOIN brano AS b ON r2.brano = b.codice
     INNER JOIN artista AS g ON b.artista = g.codice
     INNER JOIN artista AS l ON g.leader = l.codice
WHERE u.anno_nascita > 2000 AND r2.genere = 'pop' AND g.tipo = 'gruppo';

artista compare due volte: g (il gruppo del brano) e l (il suo leader), collegati dall'autoriferimento g.leader = l.codice (è un self-join). Risultato: Anna Verdi (leader di Le Onde, ascoltata da $u_2$, $u_3$, $u_4$ tramite i brani 4 e 7) e Sara Gialli (leader di Alba, ascoltata da $u_3$ tramite il brano 5). Il brano 6 è pop ma di un cantante singolo (Elio Rossi): non è di un gruppo e non produce un leader.

Algebra corretta. Gli stessi passi con la rinomina del secondo uso di ARTISTA e una proiezione finale su codice, nome e cognome del leader (provata: stesso risultato).

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

• Query 1: serve la somma sulle righe di $R_3$ per brano. Un indice su $R_3(\text{brano})$ (o, poiché la chiave è $(\text{utente},\text{brano})$, un indice con brano in prima posizione) rende il raggruppamento una scansione ordinata. In alternativa un attributo derivato totale_ascolti in BRANO, da aggiornare a ogni ascolto con un trigger (letture immediate, ma aggiornamenti più cari e ridondanza).
• Query 2: serve contare gli utenti distinti per band: un attributo derivato n_ascoltatori in ARTISTA (aggiornato quando un utente ascolta per la prima volta un brano della band) evita tre join e un COUNT(DISTINCT); conviene se la query è frequente e gli ascolti, rispetto alle letture, sono meno frequenti. In alternativa la vista materializzata dei conteggi per band.
• Query 3: il percorso utente $\to$ brano $\to$ genere $\to$ artista $\to$ leader è lungo: si può spostare l'attributo leader (o una colonna genere_principale) dove serve, o indicizzare UTENTE(anno_nascita) e $R_2(\text{genere})$.

In ogni caso va confrontato il guadagno in lettura con il costo in scrittura e con il rischio di incoerenza (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 2: COUNT(*) conta coppie (utente, brano) e non utenti: dà Nebbia (6) invece di I Fulmini (3).
• Query 3: SQL senza DISTINCT (4 righe invece di 2 leader) e risultato con solo il codice del leader.
• Query 1: il brano mai ascoltato manca dal risultato (accettabile, ma va dichiarato); incoerenza codice/brano tra le tabelle.