Verso l'Esame di Stato: una traccia svolta

La seconda prova scritta dell'Esame di Stato di Informatica ha di solito questa struttura: una prima parte obbligatoria, con un caso reale da analizzare (progetto del database, query SQL, pagine web che lo usano), e una seconda parte con alcuni quesiti, tra cui scegliere, che approfondiscono o estendono il tema. In questa lezione svolgiamo una traccia completa, nello stile di quelle ministeriali, usando tutto quello che hai imparato nel corso.

Prova a svolgere la traccia da solo prima di leggere la soluzione: il database con i dati di prova, per verificare le query, te lo fornisce il docente.

La traccia

Bike sharing cittadino

Il Comune di una grande città vuole realizzare un servizio di bike sharing. In vari punti della città sono installate stazioni, ciascuna con un nome, un indirizzo e un numero di stalli (posti per le biciclette). Il servizio dispone di biciclette muscolari ed elettriche, identificate da un codice alfanumerico di 6 caratteri; una bicicletta può essere temporaneamente fuori servizio per manutenzione.

Per usare il servizio un cittadino si registra con nome, cognome, email e password, e ricarica un credito. Per noleggiare una bicicletta l'utente la sblocca da una stazione con l'app; al termine la riconsegna in una qualsiasi stazione con stalli liberi. Per ogni noleggio si registrano la stazione e l'ora di partenza, la stazione e l'ora di arrivo e il costo, calcolato alla riconsegna in base alla durata e al tipo di bicicletta.

Il candidato, fatte le opportune ipotesi aggiuntive:

1. analizzi la realtà di riferimento e ne individui una possibile soluzione;
2. progetti lo schema concettuale e il corrispondente schema logico del database;
3. definisca in SQL almeno due tabelle, con i relativi vincoli;
4. scriva le seguenti interrogazioni:
   • a. le biciclette disponibili in una stazione, dato il suo nome;
   • b. lo storico dei noleggi di un utente in un mese, con stazione di partenza e di arrivo, durata in minuti e costo;
   • c. per ogni stazione, il numero di noleggi partiti da essa in un periodo dato, in ordine decrescente;
   • d. per tipo di bicicletta, il numero di noleggi conclusi, la durata media e l'incasso totale;
   • e. gli utenti che non hanno mai noleggiato una bicicletta elettrica;
5. progetti e codifichi in un linguaggio a scelta una pagina web che mostri, per ogni stazione, le biciclette disponibili per tipo e gli stalli liberi, e una pagina riservata in cui l'utente consulta i propri noleggi di un mese a scelta.

Seconda parte (due quesiti a scelta):

• I. Al momento dello sblocco occorre registrare il noleggio e aggiornare la posizione della bicicletta. Si descriva come garantire la coerenza dei dati e si scriva il codice relativo.
• II. Il Comune vuole poter cambiare le tariffe nel tempo, mantenendo corretti i costi dei noleggi passati. Si proponga una modifica allo schema.
• III. Si illustrino le principali vulnerabilità di un'applicazione web che accede a un database e le relative contromisure.

Il metodo

Prima di scrivere, leggi la traccia due volte e sottolinea:

• i sostantivi → candidati a essere entità o attributi (stazione, bicicletta, utente, noleggio, credito…);
• i verbi → candidati a essere associazioni (sblocca, riconsegna, noleggia);
• le quantità e i vincoli → cardinalità e regole (numero di stalli, codice di 6 caratteri, "una qualsiasi stazione").

Poi gestisci il tempo: la prova dura 6 ore. Una ripartizione ragionevole: analisi e progetto 1 ora e mezza, DDL e query 1 ora e mezza, pagine web 2 ore, seconda parte 1 ora. Rileggi sempre la traccia alla fine: un punto dimenticato costa più di un punto fatto male.

1. Analisi e ipotesi aggiuntive

Il sistema gestisce la flotta di biciclette, la rete delle stazioni, gli utenti registrati e i noleggi. Gli utenti sono di due tipi: i cittadini, che usano l'app per noleggiare e consultare i propri noleggi, e gli operatori del Comune, che gestiscono stazioni e biciclette (fuori dalle richieste della traccia).

Ipotesi aggiuntive:

1. Una bicicletta, in ogni momento, si trova in una stazione oppure è in uso: la posizione attuale si memorizza nella bicicletta (stazione_id, vuota durante un noleggio).
2. Una bicicletta in manutenzione resta registrata nella sua stazione ma non si può noleggiare (in_servizio = falso).
3. Un utente può avere al massimo un noleggio aperto alla volta (il controllo si fa nell'applicazione).
4. Un noleggio aperto ha stazione di arrivo, ora di fine e costo non ancora noti (NULL).
5. Il costo è calcolato dall'applicazione alla riconsegna e memorizzato nel noleggio: in questo modo un cambio di tariffa non modifica i noleggi passati (vedi quesito II).
6. Per il pagamento, il credito è un importo in euro salvato nell'utente; lo storico delle ricariche non è richiesto.
7. Le password sono memorizzate solo come impronta (password_hash).

2. Schema concettuale

Entità e attributi (identificatore sottolineato):

• STAZIONE (id, nome, indirizzo, posti)
• BICICLETTA (id, codice, tipo, in_servizio)
• UTENTE (id, nome, cognome, email, password_hash, credito)
• NOLEGGIO (id, inizio, fine, costo)

Associazioni:

Il noleggio: entità o associazione? Si potrebbe vedere il noleggio come un'associazione N:M tra UTENTE e BICICLETTA, con gli attributi inizio, fine e costo. Ma il noleggio è collegato anche a due stazioni: ha senso trattarlo come entità a sé (si dice anche entità associativa), con quattro associazioni 1:N. Al passaggio allo schema logico il risultato è lo stesso: una tabella con le chiavi esterne. All'esame entrambe le scelte sono corrette, se motivate.

Notare le due associazioni tra STAZIONE e NOLEGGIO, con ruoli diversi (partenza e arrivo): è un caso frequente nelle tracce (mittente e destinatario, aeroporto di partenza e di arrivo…).

3. Schema logico

stazioni   (id, nome, indirizzo, posti)
biciclette (id, codice, tipo, in_servizio, stazione_id*)
utenti     (id, nome, cognome, email, password_hash, credito)
noleggi    (id, utente_id*, bicicletta_id*, partenza_id*, arrivo_id*, inizio, fine, costo)

biciclette.stazione_id → stazioni(id)    (NULL = in uso)
noleggi.utente_id      → utenti(id)
noleggi.bicicletta_id  → biciclette(id)
noleggi.partenza_id    → stazioni(id)
noleggi.arrivo_id      → stazioni(id)    (NULL = noleggio aperto)

Ogni associazione 1:N è diventata una chiave esterna dalla parte "molti" (lezione 3). Le tabelle sono in 3NF: ogni attributo non chiave dipende solo dalla chiave della sua tabella. Ad esempio il nome della stazione di partenza non è ripetuto nei noleggi: si ottiene con una JOIN.

4. Il DDL

CREATE TABLE stazioni (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(60) NOT NULL UNIQUE,
  indirizzo VARCHAR(120) NOT NULL,
  posti TINYINT UNSIGNED NOT NULL,
  CHECK (posti > 0)
) ENGINE=InnoDB;

CREATE TABLE biciclette (
  id INT AUTO_INCREMENT PRIMARY KEY,
  codice CHAR(6) NOT NULL UNIQUE,
  tipo ENUM('muscolare', 'elettrica') NOT NULL,
  in_servizio BOOLEAN NOT NULL DEFAULT TRUE,
  stazione_id INT NULL,               -- NULL = in uso in questo momento
  FOREIGN KEY (stazione_id) REFERENCES stazioni(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE noleggi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  utente_id INT NOT NULL,
  bicicletta_id INT NOT NULL,
  partenza_id INT NOT NULL,
  arrivo_id INT NULL,
  inizio DATETIME NOT NULL,
  fine DATETIME NULL,
  costo DECIMAL(5,2) NULL,
  CHECK (fine IS NULL OR fine > inizio),
  FOREIGN KEY (utente_id) REFERENCES utenti(id) ON DELETE RESTRICT,
  FOREIGN KEY (bicicletta_id) REFERENCES biciclette(id) ON DELETE RESTRICT,
  FOREIGN KEY (partenza_id) REFERENCES stazioni(id) ON DELETE RESTRICT,
  FOREIGN KEY (arrivo_id) REFERENCES stazioni(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

(La tabella utenti è come quella della biblioteca, con in più credito DECIMAL(6,2) NOT NULL DEFAULT 0; la traccia ne chiede almeno due, ma scriverle tutte non guasta.)

Scelte da motivare nell'elaborato:

• il codice è CHAR(6) (lunghezza fissa) e UNIQUE;
• il tipo è un ENUM: i valori possibili sono solo due e stabili. In alternativa, una tabella tipi con una chiave esterna, utile se i tipi aumenteranno o se ogni tipo avrà una tariffa (quesito II);
• i soldi sono DECIMAL, mai FLOAT;
• ON DELETE RESTRICT sui noleggi conserva lo storico; SET NULL sulla posizione della bicicletta: se una stazione viene smantellata, le biciclette restano, "senza stazione".

5. Le interrogazioni

a. Le biciclette disponibili in una stazione (per esempio Porta Nuova).

SELECT b.codice, b.tipo
FROM biciclette b
JOIN stazioni s ON s.id = b.stazione_id
WHERE s.nome = 'Porta Nuova'
  AND b.in_servizio = TRUE
ORDER BY b.tipo, b.codice;

Risultato con i dati di prova: TO0007 (muscolare) e TO0003 (elettrica). La TO0004 è a Porta Nuova ma in manutenzione, quindi è esclusa. (Un ENUM si ordina secondo l'ordine dei valori nella definizione: prima muscolare, poi elettrica.)

b. Lo storico di un utente in un mese (Anna Ferrero, settembre 2026).

SELECT n.inizio,
       b.codice,
       sp.nome AS partenza,
       sa.nome AS arrivo,
       TIMESTAMPDIFF(MINUTE, n.inizio, n.fine) AS minuti,
       n.costo
FROM noleggi n
JOIN utenti u      ON u.id = n.utente_id
JOIN biciclette b  ON b.id = n.bicicletta_id
JOIN stazioni sp   ON sp.id = n.partenza_id
LEFT JOIN stazioni sa ON sa.id = n.arrivo_id
WHERE u.email = 'anna.ferrero@mail.it'
  AND n.inizio >= '2026-09-01' AND n.inizio < '2026-10-01'
ORDER BY n.inizio;

+---------------------+--------+---------------------+-------------+--------+-------+
| inizio              | codice | partenza            | arrivo      | minuti | costo |
+---------------------+--------+---------------------+-------------+--------+-------+
| 2026-09-03 08:05:00 | TO0001 | Porta Nuova         | Politecnico |     20 |  1.00 |
| 2026-09-03 17:40:00 | TO0003 | Politecnico         | Porta Nuova |     20 |  2.00 |
| 2026-09-20 16:00:00 | TO0007 | Parco del Valentino | Porta Nuova |     35 |  1.50 |
+---------------------+--------+---------------------+-------------+--------+-------+

È la query più istruttiva della traccia:

• la tabella stazioni compare due volte, con due alias diversi (sp per la partenza, sa per l'arrivo): ogni alias è una "copia" della tabella collegata a una chiave esterna diversa;
• l'arrivo è in LEFT JOIN, così un eventuale noleggio ancora aperto compare comunque, con arrivo e minuti NULL;
• il mese si seleziona con >= primo giorno e < primo giorno del mese dopo: funziona con i DATETIME (il 30 settembre alle 23:59 è compreso) e permette a MySQL di usare gli indici. MONTH(n.inizio) = 9 AND YEAR(n.inizio) = 2026 è corretto ma meno efficiente;
• TIMESTAMPDIFF(MINUTE, a, b) calcola i minuti tra due DATETIME (DATEDIFF conta solo i giorni).

c. I noleggi partiti da ogni stazione in un periodo (settembre 2026).

SELECT s.nome, COUNT(n.id) AS partenze
FROM stazioni s
LEFT JOIN noleggi n
       ON n.partenza_id = s.id
      AND n.inizio >= '2026-09-01' AND n.inizio < '2026-10-01'
GROUP BY s.id, s.nome
ORDER BY partenze DESC, s.nome;

Politecnico e Porta Nuova 2, Lingotto e Parco del Valentino 1. La LEFT JOIN con il periodo nell'ON (come la disponibilità dei libri nella lezione 6) mostra anche le stazioni con zero partenze; con il periodo nel WHERE sparirebbero.

d. Statistiche per tipo di bicicletta.

SELECT b.tipo,
       COUNT(*) AS noleggi,
       ROUND(AVG(TIMESTAMPDIFF(MINUTE, n.inizio, n.fine)), 1) AS durata_media,
       SUM(n.costo) AS incasso
FROM noleggi n
JOIN biciclette b ON b.id = n.bicicletta_id
WHERE n.fine IS NOT NULL
GROUP BY b.tipo;

Muscolari: 5 noleggi, 39,4 minuti di media, 9,50 €; elettriche: 2 noleggi, 25 minuti, 5,00 €. Il WHERE esclude i noleggi aperti, che non hanno ancora durata né costo.

e. Gli utenti che non hanno mai noleggiato una bicicletta elettrica.

SELECT nome, cognome
FROM utenti
WHERE id NOT IN (
    SELECT n.utente_id
    FROM noleggi n
    JOIN biciclette b ON b.id = n.bicicletta_id
    WHERE b.tipo = 'elettrica'
)
ORDER BY cognome;

Paolo Gallo e Omar Haddad (che non ha mai noleggiato nulla, quindi a maggior ragione nessuna elettrica). Attenzione: la richiesta non si risolve con WHERE b.tipo <> 'elettrica', che troverebbe chi ha noleggiato almeno una muscolare (anche Anna, che ha usato anche un'elettrica). Le domande con "mai" o "nessun" si risolvono con NOT IN, NOT EXISTS o LEFT JOIN … IS NULL.

6. Le pagine web

Usiamo PHP con mysqli e la stessa struttura della biblioteca: config.php, db.php e funzioni.php (con h(), la sessione e richiedi_login()). Il login è identico a quello della lezione 13, cercando l'utente per email.

Le stazioni (pagina pubblica)

<?php
require 'funzioni.php';

$risultato = mysqli_query($conn,
    "SELECT s.nome, s.indirizzo, s.posti,
            SUM(b.tipo = 'muscolare' AND b.in_servizio) AS muscolari,
            SUM(b.tipo = 'elettrica' AND b.in_servizio) AS elettriche,
            s.posti - COUNT(b.id) AS stalli_liberi
     FROM stazioni s
     LEFT JOIN biciclette b ON b.stazione_id = s.id
     GROUP BY s.id, s.nome, s.indirizzo, s.posti
     ORDER BY s.nome"
);
$stazioni = mysqli_fetch_all($risultato, MYSQLI_ASSOC);

$titolo = 'Stazioni';
require 'header.php';
?>
<h1>Stazioni</h1>
<table>
    <tr><th>Stazione</th><th>Indirizzo</th><th>🚲 Muscolari</th><th>⚡ Elettriche</th><th>Stalli liberi</th></tr>
    <?php foreach ($stazioni as $s): ?>
    <tr>
        <td><?= h($s['nome']) ?></td>
        <td><?= h($s['indirizzo']) ?></td>
        <td><?= (int) $s['muscolari'] ?></td>
        <td><?= (int) $s['elettriche'] ?></td>
        <td><?= $s['stalli_liberi'] ?> su <?= $s['posti'] ?></td>
    </tr>
    <?php endforeach; ?>
</table>
<?php require 'footer.php'; ?>

Il conteggio condizionato è un trucco utile: in MySQL una condizione vale 1 se vera e 0 se falsa, quindi SUM(condizione) conta le righe che la rispettano. Con una stazione vuota la LEFT JOIN produce una riga di NULL e la SUM dà NULL: per questo nella pagina c'è (int), che lo trasforma in 0. Gli stalli liberi contano tutte le biciclette presenti, anche quelle in manutenzione, perché occupano comunque uno stallo.

La query non contiene dati dell'utente, quindi mysqli_query() è corretto.

I miei noleggi (pagina riservata)

<?php
require 'funzioni.php';
richiedi_login();

// mese scelto dall'utente, nel formato del campo <input type="month">: 2026-09
$mese = $_GET['mese'] ?? date('Y-m');
if (!preg_match('/^\d{4}-(0[1-9]|1[0-2])$/', $mese)) {
    $mese = date('Y-m');
}
$dal = "$mese-01";
$al = date('Y-m-d', strtotime("$dal +1 month"));

$utente_id = utente()['id'];
$stmt = mysqli_prepare($conn,
    'SELECT n.inizio, b.codice, b.tipo, sp.nome AS partenza, sa.nome AS arrivo,
            TIMESTAMPDIFF(MINUTE, n.inizio, n.fine) AS minuti, n.costo
     FROM noleggi n
     JOIN biciclette b    ON b.id = n.bicicletta_id
     JOIN stazioni sp     ON sp.id = n.partenza_id
     LEFT JOIN stazioni sa ON sa.id = n.arrivo_id
     WHERE n.utente_id = ? AND n.inizio >= ? AND n.inizio < ?
     ORDER BY n.inizio'
);
mysqli_stmt_bind_param($stmt, 'iss', $utente_id, $dal, $al);
mysqli_stmt_execute($stmt);
$noleggi = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
$totale = array_sum(array_column($noleggi, 'costo'));

$titolo = 'I miei noleggi';
require 'header.php';
?>
<h1>I miei noleggi</h1>
<form method="get">
    <label>Mese <input type="month" name="mese" value="<?= h($mese) ?>"></label>
    <button>Mostra</button>
</form>

<?php if ($noleggi): ?>
<table>
    <tr><th>Data e ora</th><th>Bici</th><th>Da</th><th>A</th><th>Minuti</th><th>Costo</th></tr>
    <?php foreach ($noleggi as $n): ?>
    <tr>
        <td><?= date('d/m/Y H:i', strtotime($n['inizio'])) ?></td>
        <td><?= h($n['codice']) ?> (<?= h($n['tipo']) ?>)</td>
        <td><?= h($n['partenza']) ?></td>
        <td><?= $n['arrivo'] === null ? '<em>in corso</em>' : h($n['arrivo']) ?></td>
        <td><?= $n['minuti'] ?? '—' ?></td>
        <td><?= $n['costo'] === null ? '—' : number_format((float) $n['costo'], 2, ',', '.') . ' €' ?></td>
    </tr>
    <?php endforeach; ?>
</table>
<p>Totale del mese: <strong><?= number_format($totale, 2, ',', '.') ?> €</strong></p>
<?php else: ?>
    <p>Nessun noleggio in questo mese.</p>
<?php endif; ?>
<?php require 'footer.php'; ?>

Gli elementi che un correttore cerca, e che conviene commentare nell'elaborato:

• l'utente viene dalla sessione, non dall'indirizzo: se l'id fosse un parametro ?utente=3, chiunque potrebbe vedere i noleggi degli altri cambiando il numero. È un errore frequentissimo (si chiama IDOR, Insecure Direct Object Reference);
• il mese viene validato con un'espressione regolare (e se è sbagliato si usa quello corrente), poi passato con una query preparata;
• il periodo si calcola in PHP (primo giorno del mese e primo giorno del mese dopo) ed è lo stesso schema della query b;
• tutti i testi passano da h(); i noleggi aperti sono gestiti (arrivo, minuti e costo NULL).

7. Seconda parte

Quesito I – Lo sblocco e le transazioni

Lo sblocco richiede due modifiche: inserire il noleggio e togliere la bicicletta dalla stazione (stazione_id = NULL). Se il programma si interrompe tra le due (errore, server che si spegne), il database resta incoerente: un noleggio aperto con una bicicletta che risulta ancora in stazione, che un altro utente potrebbe sbloccare.

La soluzione è una transazione: un gruppo di operazioni che il DBMS esegue "tutte o nessuna". Le transazioni hanno le proprietà ACID:

• Atomicità: o tutte le operazioni vanno a buon fine, o nessuna;
• Coerenza: il database passa da uno stato valido a un altro stato valido (vincoli rispettati);
• Isolamento: le transazioni contemporanee non vedono i risultati intermedi l'una dell'altra;
• Durabilità: una volta confermate (commit), le modifiche non si perdono, nemmeno per un guasto.

In SQL si scrive START TRANSACTION; ... COMMIT; (oppure ROLLBACK; per annullare). Con mysqli ci sono tre funzioni: mysqli_begin_transaction, mysqli_commit e mysqli_rollback.

<?php
require 'funzioni.php';
richiedi_login();
if ($_SERVER['REQUEST_METHOD'] !== 'POST') vai('stazioni.php');
csrf_controlla();

$codice = strtoupper(trim($_POST['codice'] ?? ''));
$utente_id = utente()['id'];

try {
    mysqli_begin_transaction($conn);

    // 1. la bici esiste, è in servizio ed è in una stazione? FOR UPDATE la "blocca" fino al commit
    $stmt = mysqli_prepare($conn, 'SELECT id, stazione_id FROM biciclette
                                   WHERE codice = ? AND in_servizio = TRUE AND stazione_id IS NOT NULL
                                   FOR UPDATE');
    mysqli_stmt_bind_param($stmt, 's', $codice);
    mysqli_stmt_execute($stmt);
    $bici = mysqli_fetch_assoc(mysqli_stmt_get_result($stmt));
    if (!$bici) {
        throw new RuntimeException('Bicicletta non disponibile.');
    }

    // 2. l'utente non ha già un noleggio aperto (ipotesi 3)
    $stmt = mysqli_prepare($conn, 'SELECT COUNT(*) FROM noleggi WHERE utente_id = ? AND fine IS NULL');
    mysqli_stmt_bind_param($stmt, 'i', $utente_id);
    mysqli_stmt_execute($stmt);
    if (mysqli_fetch_row(mysqli_stmt_get_result($stmt))[0] > 0) {
        throw new RuntimeException('Hai già un noleggio in corso.');
    }

    // 3. le due modifiche
    $stmt = mysqli_prepare($conn, 'INSERT INTO noleggi (utente_id, bicicletta_id, partenza_id, inizio) VALUES (?, ?, ?, NOW())');
    mysqli_stmt_bind_param($stmt, 'iii', $utente_id, $bici['id'], $bici['stazione_id']);
    mysqli_stmt_execute($stmt);

    $stmt = mysqli_prepare($conn, 'UPDATE biciclette SET stazione_id = NULL WHERE id = ?');
    mysqli_stmt_bind_param($stmt, 'i', $bici['id']);
    mysqli_stmt_execute($stmt);

    mysqli_commit($conn);
    messaggio("Bicicletta $codice sbloccata. Buona pedalata!");
} catch (Throwable $e) {
    mysqli_rollback($conn);
    messaggio($e instanceof RuntimeException ? $e->getMessage() : 'Errore: riprova.', 'errore');
}
vai('noleggi.php');

• Tra mysqli_begin_transaction() e mysqli_commit() le modifiche non sono definitive; in caso di qualsiasi errore, mysqli_rollback() le annulla tutte.
• SELECT ... FOR UPDATE blocca la riga della bicicletta fino al commit: se due utenti provano a sbloccare la stessa bici nello stesso istante, il secondo aspetta che il primo finisca e poi non la trova più disponibile. Senza, entrambi potrebbero leggere "disponibile" e creare due noleggi.
• Le transazioni funzionano con il motore InnoDB (per questo ENGINE=InnoDB nel DDL).

Quesito II – Le tariffe nel tempo

Si aggiunge una tabella delle tariffe con un periodo di validità:

tariffe (id, tipo, euro_minuto, minuti_gratis, valida_dal, valida_al)
noleggi (id, ..., costo, tariffa_id*)     tariffa_id → tariffe(id)

• Una nuova tariffa non modifica la vecchia: si chiude quella in vigore (valida_al) e se ne inserisce un'altra. La tariffa in vigore per un tipo è quella con valida_dal <= oggi e valida_al nullo o futuro.
• Alla riconsegna l'applicazione legge la tariffa in vigore, calcola il costo e salva nel noleggio sia il costo sia la tariffa usata. In questo modo lo storico resta corretto e verificabile: si può sempre ricostruire come è stato calcolato un vecchio importo.
• Salvare il costo nel noleggio è una ridondanza voluta (lezione 3): ricalcolarlo ogni volta darebbe risultati sbagliati per i noleggi passati, dopo un cambio di tariffa.

Quesito III – Le vulnerabilità

È il riassunto delle lezioni 9, 11 e 13. Uno schema per rispondere in modo ordinato:

A queste si aggiungono le misure sul server: utente del database con i soli privilegi necessari (GRANT SELECT, INSERT, UPDATE ON bike_sharing.* TO 'app'@'localhost', mai root), backup regolari, aggiornamenti di PHP e MySQL.

Errori da non fare all'esame

• Saltare le ipotesi aggiuntive: tutto ciò che la traccia non dice va deciso e dichiarato.
• Cardinalità senza spiegazione: per ogni associazione, una riga che dica "un X ha … Y, un Y ha … X".
• Query che funzionano solo con i dati di esempio: per esempio filtrare per id = 3 quando la traccia dice "dato il nome della stazione".
• Dimenticare i NULL: noleggi aperti, stazioni vuote. Chiediti sempre "e se non c'è?".
• Pagine PHP senza sicurezza: query concatenate, nessun htmlspecialchars, id dell'utente nell'indirizzo. Costano punti anche se il codice "funziona".
• Codice non commentato: il correttore deve capire le tue scelte. Un commento di una riga sopra ogni blocco basta.

Esercizi

-- 1. Sofia Russo, TO0008, da Porta Nuova
SELECT u.nome, u.cognome, b.codice, s.nome AS partenza, n.inizio
FROM noleggi n
JOIN utenti u     ON u.id = n.utente_id
JOIN biciclette b ON b.id = n.bicicletta_id
JOIN stazioni s   ON s.id = n.partenza_id
WHERE n.fine IS NULL;

-- 2. Porta Nuova, 4 partenze
SELECT s.nome, COUNT(*) AS partenze
FROM noleggi n
JOIN stazioni s ON s.id = n.partenza_id
GROUP BY s.id, s.nome
HAVING COUNT(*) = (SELECT MAX(c) FROM (SELECT COUNT(*) AS c FROM noleggi GROUP BY partenza_id) AS t);

-- 3. TO0004 (in manutenzione)
SELECT b.codice, b.tipo
FROM biciclette b
LEFT JOIN noleggi n ON n.bicicletta_id = b.id
WHERE n.id IS NULL;

<?php
$utente_id = utente()['id'];
$stazione = (int) ($_POST['stazione_id'] ?? 0);
try {
    mysqli_begin_transaction($conn);

    $stmt = mysqli_prepare($conn, 'SELECT n.id, n.bicicletta_id, b.tipo, TIMESTAMPDIFF(MINUTE, n.inizio, NOW()) AS minuti
                                   FROM noleggi n JOIN biciclette b ON b.id = n.bicicletta_id
                                   WHERE n.utente_id = ? AND n.fine IS NULL
                                   FOR UPDATE');
    mysqli_stmt_bind_param($stmt, 'i', $utente_id);
    mysqli_stmt_execute($stmt);
    $nol = mysqli_fetch_assoc(mysqli_stmt_get_result($stmt));
    if (!$nol) throw new RuntimeException('Non hai noleggi in corso.');

    // la stazione esiste e ha uno stallo libero?
    $stmt = mysqli_prepare($conn, 'SELECT s.posti - (SELECT COUNT(*) FROM biciclette WHERE stazione_id = s.id)
                                   FROM stazioni s WHERE s.id = ?');
    mysqli_stmt_bind_param($stmt, 'i', $stazione);
    mysqli_stmt_execute($stmt);
    $riga = mysqli_fetch_row(mysqli_stmt_get_result($stmt));
    if (!$riga) throw new RuntimeException('Stazione inesistente.');
    if ($riga[0] < 1) throw new RuntimeException('La stazione è piena: scegline un\'altra.');

    $tariffa = $nol['tipo'] === 'elettrica' ? 0.10 : 0.05;
    $costo = max(1.00, round(max(1, $nol['minuti']) * $tariffa, 2));

    $stmt = mysqli_prepare($conn, 'UPDATE noleggi SET arrivo_id = ?, fine = NOW(), costo = ? WHERE id = ?');
    mysqli_stmt_bind_param($stmt, 'idi', $stazione, $costo, $nol['id']);       // d = decimale
    mysqli_stmt_execute($stmt);

    $stmt = mysqli_prepare($conn, 'UPDATE biciclette SET stazione_id = ? WHERE id = ?');
    mysqli_stmt_bind_param($stmt, 'ii', $stazione, $nol['bicicletta_id']);
    mysqli_stmt_execute($stmt);

    $stmt = mysqli_prepare($conn, 'UPDATE utenti SET credito = credito - ? WHERE id = ?');
    mysqli_stmt_bind_param($stmt, 'di', $costo, $utente_id);
    mysqli_stmt_execute($stmt);

    mysqli_commit($conn);
    messaggio('Noleggio concluso: ' . number_format($costo, 2, ',', '.') . ' €.');
} catch (Throwable $e) {
    mysqli_rollback($conn);
    messaggio($e instanceof RuntimeException ? $e->getMessage() : 'Errore: riprova.', 'errore');
}