Esercizio - Interrogazioni sullo stabilimento balneare (tema d'esame 18 giugno 2012)

Query 1: quanti lettini sono stati utilizzati il 18 giugno 2012

Idea. Le prenotazioni di quel giorno, collegate ai lettini tramite COLLOCATO, e un conteggio.

Soluzione del corso (algebra).

$$\mathcal{F}_{\text{COUNT}(*)}\Big(\text{COLLOCATO}\bowtie_{\text{prenotazione}=\text{numero}}\sigma_{\text{ingresso}=2012/06/18}(\text{PRENOTAZIONE})\Big)$$

Due imprecisioni.

1. ingresso è un timestamp (data e ora): ingresso = '2012-06-18' confronta un'ora precisa con una data e non trova nulla (provato: 0 righe). Si usa la parte data: DATE(ingresso) = '2012-06-18' (oppure un intervallo $\ge$ giorno e $<$ giorno dopo, che permette di usare un indice).
2. COUNT(*) conta le coppie (prenotazione, lettino), non i lettini: il lettino 1, usato nella prenotazione 1 al mattino e nella 2 nel pomeriggio, è contato due volte. "Quanti lettini sono stati utilizzati" chiede i lettini distinti.

SQL.

SELECT COUNT(*)                      AS assegnazioni,
       COUNT(DISTINCT co.lettino)    AS lettini_usati
FROM collocato AS co INNER JOIN prenotazione AS p ON co.prenotazione = p.numero
WHERE DATE(p.ingresso) = '2012-06-18';

(DATE() funziona in SQLite e in PostgreSQL con una lieve variante, p.ingresso::date.) Risultato: 5 assegnazioni, 4 lettini distinti (1, 2, 3, 4). Il lettino 5 è della prenotazione del 17 giugno.

Algebra con i distinti. L'algebra non ha COUNT(DISTINCT ...): si proietta prima, e la proiezione elimina i duplicati.

$$\mathcal{F}_{\text{COUNT}(*)}\Big(\pi_{\text{lettino}}\big(\text{COLLOCATO}\bowtie_{\text{prenotazione}=\text{numero}}\sigma_{\text{DATE}(\text{ingresso})=2012/06/18}(\text{PRENOTAZIONE})\big)\Big)=4.$$

(Provato con un interprete di algebra: 5 senza la proiezione, 4 con.)

Query 2: controllare i teli da mare in uso a una certa ora

Idea. I teli in uso all'istante $t$ sono quelli delle prenotazioni già entrate ($\text{ingresso}\le t$) e non ancora uscite ($\text{uscita}$ NULL oppure $>t$). Si somma n_teli e si confronta con STAGIONE.n_teli.

Soluzione del corso (algebra).

$$\text{TELI}\leftarrow\pi_{\text{nteli}}\big(\sigma_{\text{ingresso}=2012/06/18}(\text{PRENOTAZIONE})\bowtie_{\text{stagione}=\text{anno}}\text{STAGIONE}\big)\qquad\text{TELIUSATI}\leftarrow\mathcal{F}_{\text{SUM}(\text{nteli})}\big(\sigma_{\text{uscita IS NULL}}(\text{PRENOTAZIONE})\big)$$ $$\text{TELI}-\text{TELIUSATI}$$

con la nota "ricordarsi di applicare $\rho_{\text{nteli}\leftarrow\text{SUM}(\text{nteli})}$".

Errori della soluzione del corso.

1. L'ultima operazione è una differenza insiemistica tra relazioni (TELI $-$ TELIUSATI), non una sottrazione tra numeri: toglie dalle tuple di TELI quelle uguali a TELIUSATI, e non calcola i teli disponibili. Per sottrarre si usa una proiezione generalizzata che calcola $\text{massimo}-\text{usati}$ su una sola tupla ottenuta con un join o prodotto.
2. PRENOTAZIONE e STAGIONE hanno entrambe un attributo n_teli: nel join vanno rinominati (il massimo $\ne$ il numero di teli di una prenotazione).
3. uscita IS NULL rappresenta solo "adesso", cioè le persone ancora presenti: non risponde a "ad una certa ora del giorno" per un istante passato (una prenotazione già uscita alle 13 conta se $t$ è le 12).

Algebra corretta. Sia $\varphi_t\equiv\text{ingresso}\le t\ \wedge\ (\text{uscita IS NULL}\ \vee\ \text{uscita}>t)$.

$$\text{USATI}\leftarrow\rho_{\text{usati}\leftarrow\text{SUM}(\text{n\_teli})}\Big({}_{\text{stagione}}\mathcal{F}_{\text{SUM}(\text{n\_teli})}\big(\sigma_{\varphi_t}(\text{PRENOTAZIONE})\big)\Big)$$ $$\text{DISP}\leftarrow\pi_{\text{anno},\ \text{massimo},\ \text{usati},\ \text{massimo}-\text{usati}}\Big(\rho_{\text{massimo}\leftarrow\text{n\_teli}}(\sigma_{\text{anno}=2012}(\text{STAGIONE}))\bowtie_{\text{anno}=\text{stagione}}\text{USATI}\Big)$$

(Se nessuna prenotazione è attiva, USATI è vuota e il join perde la stagione: con un left outer join si ha $\text{usati}=\text{NULL}$ da leggere come 0.) Il controllo chiede che l'ultima colonna sia $\ge0$.

SQL.

SELECT s.anno, s.n_teli AS massimo, COALESCE(SUM(p.n_teli), 0) AS usati,
       s.n_teli - COALESCE(SUM(p.n_teli), 0) AS disponibili
FROM stagione AS s
     LEFT JOIN prenotazione AS p
       ON p.stagione = s.anno AND p.ingresso <= '2012-06-18 12:00'
          AND (p.uscita IS NULL OR p.uscita > '2012-06-18 12:00')
WHERE s.anno = 2012
GROUP BY s.anno, s.n_teli;

Il LEFT JOIN con le condizioni nella ON (e non nella WHERE) tiene la stagione anche se nessuna prenotazione è attiva. Risultati sui dati: alle 12:00 sono attive le prenotazioni 1 (2 teli) e 3 (2 teli): 4 usati, 1 disponibile; alle 14:30 la 1 è uscita ma è entrata la 2: usano la 2 (1 telo) e la 3 (2 teli): 3 usati, 2 disponibili; alle 08:00 nessuna: 0 usati, 5 disponibili. Il valore della soluzione del corso (uscita IS NULL) darebbe 2 disponibili alle 12:00, sbagliato.

Come vincolo: un trigger. Più utile di un controllo a richiesta è impedire la violazione. In SQLite (e analogamente con BEFORE INSERT e RAISE EXCEPTION in PostgreSQL, vedi 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 →):

CREATE TRIGGER controlla_teli BEFORE INSERT ON prenotazione
FOR EACH ROW
BEGIN
  SELECT RAISE(ABORT, 'teli da mare insufficienti')
  WHERE COALESCE((SELECT SUM(p.n_teli) FROM prenotazione p
                  WHERE p.stagione = NEW.stagione AND p.ingresso <= NEW.ingresso
                    AND (p.uscita IS NULL OR p.uscita > NEW.ingresso)), 0) + NEW.n_teli
        > (SELECT n_teli FROM stagione WHERE anno = NEW.stagione);
END;

Provato: alle 15:00, con 3 teli già in uso (prenotazioni 2 e 3) e massimo 5, la richiesta di una nuova prenotazione con 3 teli è rifiutata, con 2 teli è accettata, e una successiva richiesta di 1 telo alle 15:10 è rifiutata (ormai 5 su 5). Il trigger controlla all'istante di ingresso; il conteggio andrebbe rifatto anche quando si modificano ingressi, uscite o teli (trigger anche su UPDATE).

Query 3: l'età media delle persone che hanno prenotato un ombrellone

Soluzione del corso. Si assume una funzione $\text{AGE}(\text{data\_nascita})\in\mathbb{N}$ che calcola l'età in anni:

$$\mathcal{F}_{\text{AVG}(\text{AGE}(\text{data\_nascita}))}(\text{CLIENTE})$$

senza aggiungere un attributo età (sarebbe un attributo derivato che invecchia ogni giorno: la data di nascita basta).

Due precisazioni. (1) Ogni cliente ha almeno una prenotazione (cardinalità $(1,N)$ nell'ER): la soluzione del corso mediava su tutta CLIENTE. Se esistono clienti senza prenotazioni (come E nei dati) vanno esclusi: $\text{CLIENTE}\bowtie\pi_{\text{cliente}}(\text{PRENOTAZIONE})$. (2) "Le persone" si può leggere come persone distinte (ciascuna conta una volta, anche se prenota più volte) oppure come prenotazioni.

SQL. Età alla data di riferimento 2012-06-18 (in PostgreSQL: EXTRACT(YEAR FROM AGE(DATE '2012-06-18', data_nascita)); in SQLite non c'è AGE, si ricostruisce):

-- persone distinte che hanno prenotato
SELECT AVG(eta) FROM (
  SELECT cl.documento,
         CAST(strftime('%Y','2012-06-18') AS INT) - CAST(strftime('%Y', cl.data_nascita) AS INT)
         - (strftime('%m-%d','2012-06-18') < strftime('%m-%d', cl.data_nascita)) AS eta
  FROM cliente AS cl
  WHERE cl.documento IN (SELECT cliente FROM prenotazione)) AS t;

Età: A 22, B 26, C 12 (E ne ha 42 ma non ha prenotato). Media sulle persone distinte che hanno prenotato: $(22+26+12)/3=\mathbf{20}$. Se invece si media per prenotazione (A ha due prenotazioni): $(22+26+12+22)/4=\mathbf{20{,}5}$. Includendo anche E, che non ha prenotato: $25{,}5$ (errore: media su un insieme diverso). Il compito di norma intende la prima lettura; basta dichiararla.

Errori nel materiale del corso (riassunto)

• Query 1: uguaglianza tra timestamp e data (non trova nulla) e COUNT(*) al posto dei lettini distinti.
• Query 2: differenza insiemistica al posto di una sottrazione numerica; attributo n_teli omonimo non rinominato; uscita IS NULL risponde solo a "adesso", non a "ad una certa ora".
• Query 3: media su tutti i clienti (va bene solo se tutti hanno prenotato).