Esercizio - Progettazione ER e schema relazionale dello stabilimento balneare (tema d'esame 18 giugno 2012)

Testo (compito d'esame del 18/06/2012, svolto a lezione nel corso di Basi di Dati, Ing. Informatica UniPD). Uno stabilimento balneare vuole informatizzare la prenotazione dei posti ombrellone, a partire dall'anno corrente, per le stagioni estive. Requisiti in sintesi:

• ogni ombrellone è identificato da un numero e può avere zero, uno o due lettini, anch'essi con un numero che li identifica;
• lo stabilimento ha un certo numero di posti ombrellone che può variare da stagione a stagione (spazi adiacenti);
• una persona che occupa un ombrellone lascia gli estremi di un documento; basta un solo documento anche se le persone sono due per lo stesso ombrellone;
• il prezzo dell'ombrellone è di 5 euro all'ora; i teli da mare si prendono in prestito a 3 euro a telo (prezzi che non cambiano dal prossimo anno); i teli sono pochi, certamente meno dei lettini;
• per ogni persona che prende un ombrellone si registra sia l'orario di entrata sia quello di uscita, per poter prenotare lo stesso ombrellone più volte nella stessa giornata.

Query (svolte nella parte sulle interrogazioni): quanti lettini sono stati usati il 18 giugno 2012; controllare che a una certa ora il numero di teli prestati non superi il massimo disponibile; età media delle persone che hanno prenotato un ombrellone.

Teoria: Modello entità-relazione - entità, relazioni e attributiIl modello entità-relazione (ER, Chen 1976) descrive il mini-mondo con entità (classi di oggetti autonomi), relazioni (associazioni tra due o più entità, di grado 2 o più) e attributi (proprietà locali di entità o relazioni su un dominio; NULL per l'informazione mancante, con tre significati; attributi composti). Ogni costrutto ha una semantica estensionale: un'entità è un insieme di oggetti (senza ripetizioni né ordine), una relazione è un sottoinsieme del prodotto cartesiano delle entità coinvolte (quindi la stessa coppia non può comparire due volte: per gli eventi ripetuti si introduce una nuova entità, come Sessione o Fattura), un attributo è una funzione totale dalle istanze a un dominio. Si sceglie un'entità invece di un attributo quando il concetto ha istanze con proprietà proprie o collegamenti; mai trasformare un'entità in relazione. Lo schema ER è documentato dal dizionario dei dati (entità, relazioni, attributi, vincoli esterni).Modello entità-relazione - entità, relazioni e attributi →, Generalizzazioni e identificatori nel modello ERUna relazione IS-A tra superclasse e sottoclasse significa che ogni istanza della sottoclasse è anche istanza della superclasse (inclusione tra estensioni: riflessiva e transitiva, non simmetrica); vale il principio di ereditarietà: attributi, relazioni e vincoli della superclasse sono propri anche delle sottoclassi e non si ridisegnano. Una generalizzazione è una superclasse con più sottoclassi secondo un solo criterio, con due vincoli: completezza (ogni istanza della superclasse è in almeno una sottoclasse) e disgiunzione (in al più una); si hanno quattro combinazioni, e partizione se completa e disgiunta. Su una stessa entità possono esserci più generalizzazioni indipendenti con criteri diversi. L'identificatore di un'entità è un insieme di proprietà che identifica ogni istanza: interno se formato da soli attributi (1,1) dell'entità, esterno se contiene ruoli di relazioni (1,1) con altre entità (con eventuali attributi), come per l'entità debole; ogni entità ne ha almeno uno.Generalizzazioni e identificatori nel modello ER →, Ristrutturazione dello schema ER e traduzione nel modello relazionaleLa progettazione logica ha due passi. 1) Ristrutturazione dello schema ER, per togliere i costrutti che il modello relazionale non esprime: analisi delle ridondanze (attributi derivati e cicli di relazioni, decisa con volume dei dati e carico, tabelle di frequenza e accessi), eliminazione degli attributi multivalore (nuova entità), composti (appiattiti o nuova entità), delle IS-A (relazione con partecipazione (1,1) dalla sottoclasse) e delle generalizzazioni (opzione 1 relazioni, 2 accorpamento nel padre con attributo discriminante, 3 accorpamento nei figli, solo se completa e disgiunta), scelta degli identificatori principali (niente cicli di identificazione esterna), riscrittura dei vincoli esterni, partizionamento e accorpamento di entità. 2) Traduzione quasi automatica nel modello relazionale: ogni entità diventa una relazione con la chiave primaria data dall'identificatore principale (anche esterno); una relazione molti-a-molti diventa una relazione con le chiavi delle entità; una uno-a-molti con partecipazione obbligatoria si fonde con l'entità dal lato max 1 con una chiave esterna; con partecipazione opzionale si fonde (NULL) o si crea una relazione a parte; le uno-a-uno si fondono sul lato con partecipazione obbligatoria; le ternarie seguono la stessa logica con le chiavi di tutte le entità.Ristrutturazione dello schema ER e traduzione nel modello relazionale →. Query: Esercizio - Interrogazioni sullo stabilimento balneare (tema d'esame 18 giugno 2012).

1. Analisi dei requisiti

Decisioni:

• Stagione è un'entità: ha attributi propri (anno, numero di teli, prezzi) che cambiano da stagione a stagione e fissa i posti.
• Prenotazione è un'entità, non una relazione Cliente-Ombrellone, perché lo stesso cliente può prenotare lo stesso ombrellone più volte nella stessa giornata: sono eventi ripetuti e una relazione ne permetterebbe una sola (Modello entità-relazione - entità, relazioni e attributiIl modello entità-relazione (ER, Chen 1976) descrive il mini-mondo con entità (classi di oggetti autonomi), relazioni (associazioni tra due o più entità, di grado 2 o più) e attributi (proprietà locali di entità o relazioni su un dominio; NULL per l'informazione mancante, con tre significati; attributi composti). Ogni costrutto ha una semantica estensionale: un'entità è un insieme di oggetti (senza ripetizioni né ordine), una relazione è un sottoinsieme del prodotto cartesiano delle entità coinvolte (quindi la stessa coppia non può comparire due volte: per gli eventi ripetuti si introduce una nuova entità, come Sessione o Fattura), un attributo è una funzione totale dalle istanze a un dominio. Si sceglie un'entità invece di un attributo quando il concetto ha istanze con proprietà proprie o collegamenti; mai trasformare un'entità in relazione. Lo schema ER è documentato dal dizionario dei dati (entità, relazioni, attributi, vincoli esterni).Modello entità-relazione - entità, relazioni e attributi →). L'orario di uscita è opzionale (0,1): finché il cliente è sulla spiaggia è vuoto.
• Ombrellone identificato da numero e stagione: lo stesso numero in stagioni diverse può essere un posto diverso (spazi adiacenti, numero di posti variabile). L'identificatore è esterno: numero più il ruolo verso Stagione.
• Cliente: il testo parla di documento, lo si usa come identificatore (ID del documento) con la data di nascita (per l'età media).

2. Schema ER

Cardinalità: Posto: ogni ombrellone è in una sola stagione (1,1), ogni stagione ha almeno un ombrellone (1,N); Richiede: ogni prenotazione ha un cliente e un ombrellone (1,1), un cliente ha almeno una prenotazione (1,N), un ombrellone può non essere mai stato prenotato (0,N); Collocato: zero, uno o due lettini per prenotazione (0,2), un lettino può essere in molte prenotazioni (0,N) in momenti diversi.

Vincoli esterni.

1. Il numero di posti è fisso a inizio stagione: non cambia durante la stagione.
2. Prenotato e gli orari vanno in tandem: quando il cliente entra (DataOraIn assegnata) Prenotato diventa vero; quando esce (DataOraOut assegnata) torna falso. Prenotato è quindi un attributo ridondante, tenuto per vedere subito se l'ombrellone è libero.
3. Un lettino viene associato a una prenotazione solo se non c'è conflitto temporale: non può stare in due prenotazioni con intervalli sovrapposti.
4. A ogni ora il numero di teli in prestito non supera NTeli della stagione.
5. L'ombrellone di una prenotazione appartiene alla stagione dell'anno di DataOraIn.

3. Alternativa corretta e alternativa sbagliata per ombrelloni e stagioni

Il corso mostra una soluzione generale valida: separare le entità Ombrellone e Posto (la posizione fisica) con una relazione Occupa(Ombrellone (1,N), Posto (1,N), Stagione (1,N)) con un attributo che dice la posizione (casella): così gli ombrelloni sono persistenti e il posto in cui stanno cambia di stagione. Servono regole di vincolo per fissare la posizione degli ombrelloni per la stagione, necessarie per gestire correttamente le prenotazioni.

Una soluzione sbagliata: Postazione (numero) – Occupa (0,1) – Ombrellone (0,1) con attributi: non si può sapere quale ombrellone sia associato a quale postazione attraverso le varie stagioni, perché la relazione è un insieme di coppie, una sola per postazione.

4. Ristrutturazione

Nessun multivalore, composto o generalizzazione. Identificatori: Stagione (Anno), Prenotazione (Numero), Lettino, Cliente (Documento) interni; Ombrellone esterno (Numero + Stagione). Un solo identificatore esterno e nessun ciclo (Stagione non dipende da altro). Il campo Prenotato è ridondante ma tenuto per le prestazioni; non si aggiunge un attributo Età (derivato e sempre in cambiamento): si calcola dalla data di nascita.

5. Schema relazionale

Stagione(*anno, n_teli, prezzo_teli, prezzo_ombrellone)
Ombrellone(*numero, *stagione, prenotato)            FK stagione -> Stagione(anno)
Cliente(*documento, data_nascita)
Lettino(*numero)
Prenotazione(*numero, n_teli, ingresso, uscita?, ombrellone, stagione, cliente)
        FK (ombrellone, stagione) -> Ombrellone(numero, stagione); FK cliente -> Cliente(documento)
Collocato(*prenotazione, *lettino)                   FK prenotazione -> Prenotazione(numero); FK lettino -> Lettino(numero)

• Ombrellone: identificatore esterno → chiave composta (numero, stagione) con la FK; la relazione Posto (1,1) è fusa.
• Richiede (ternaria con Prenotazione (1,1)): fusa in Prenotazione, con chiave esterna composta verso Ombrellone (perché l'ombrellone ha chiave composta) e FK verso Cliente; uscita può essere NULL.
• Collocato: molti-a-molti → relazione con le due chiavi; la cardinalità massima 2 non entra nella chiave: vincolo esterno.

Versione SQL (provata con SQLite; dati: una prenotazione con due lettini rifiutando il terzo con un trigger):

CREATE TABLE Stagione (anno INTEGER PRIMARY KEY, n_teli INTEGER NOT NULL,
  prezzo_teli REAL NOT NULL, prezzo_ombrellone REAL NOT NULL);
CREATE TABLE Ombrellone (numero INTEGER, stagione INTEGER REFERENCES Stagione(anno),
  prenotato BOOLEAN NOT NULL DEFAULT FALSE, PRIMARY KEY (numero, stagione));
CREATE TABLE Cliente (documento TEXT PRIMARY KEY, data_nascita DATE NOT NULL);
CREATE TABLE Lettino (numero INTEGER PRIMARY KEY);
CREATE TABLE Prenotazione (numero INTEGER PRIMARY KEY, n_teli INTEGER NOT NULL DEFAULT 0,
  ingresso TIMESTAMP NOT NULL, uscita TIMESTAMP, ombrellone INTEGER NOT NULL, stagione INTEGER NOT NULL,
  cliente TEXT NOT NULL REFERENCES Cliente(documento),
  FOREIGN KEY (ombrellone, stagione) REFERENCES Ombrellone(numero, stagione),
  CHECK (uscita IS NULL OR uscita > ingresso));
CREATE TABLE Collocato (prenotazione INTEGER REFERENCES Prenotazione(numero),
  lettino INTEGER REFERENCES Lettino(numero), PRIMARY KEY (prenotazione, lettino));
CREATE TRIGGER max_due_lettini BEFORE INSERT ON Collocato
WHEN (SELECT COUNT(*) FROM Collocato WHERE prenotazione = NEW.prenotazione) >= 2
BEGIN SELECT RAISE(ABORT, 'al massimo due lettini'); END;

Il CHECK su uscita impedisce un'uscita prima dell'ingresso; il trigger realizza la cardinalità massima 2 di Collocato. Il vincolo di non sovrapposizione temporale dei lettini richiede un trigger più complesso (confronto degli intervalli) o il controllo applicativo.

6. Osservazioni

• Lo stesso cliente che prenota due volte lo stesso ombrellone nella stessa giornata genera due tuple di Prenotazione con numero diverso: è ciò che permette la tupla ripetuta.
• La soluzione del corso tiene l'attributo Prenotato, ridondante e allineato dal vincolo "in tandem"; è accettabile perché indica subito lo stato dell'ombrellone (come lo Stato del tavolo nella pizzeria, Esercizio - Progettazione ER e schema relazionale della pizzeria (tema d'esame 30 giugno 2025)).
• Per l'età media il corso nota: niente attributo Età nello schema, meglio la data di nascita e una funzione di calcolo dell'età nella query; "meglio ragionare vicino all'applicativo".

Domande d'esame

Perché Prenotazione non è una relazione tra Cliente e Ombrellone? Traccia. Una relazione è un insieme di coppie: lo stesso cliente non potrebbe prenotare lo stesso ombrellone più volte nel giorno. Con un'entità ogni prenotazione ha il suo identificatore e i suoi orari.

Come si identifica un ombrellone e come si traduce? Traccia. Con un identificatore esterno (numero e stagione); la chiave primaria è composta e le chiavi esterne che puntano a Ombrellone (Prenotazione) sono composte.