Esercizio - Progettazione ER e schema relazionale della pizzeria (tema d'esame 30 giugno 2025)

Testo (esercizio "gestione ordini pizzeria" del corso di Basi di Dati, Ing. Informatica UniPD: il materiale riporta il compito del 1/3/2013 e il corso lo svolge a lezione come tema d'esame, ripreso il 30/06/2025). Una nuova pizzeria vuole automatizzare comande e pietanze. Requisiti:

1. Tavoli: identificati da un numero, possono essere liberi, occupati o in attesa del conto. A ogni tavolo c'è un solo cameriere che prende la comanda.
2. Comanda: ha un numero, una data, il tavolo, il cameriere e la lista delle pietanze ordinate. Le pietanze sono piatti e bevande, con una categoria (antipasti, pizze, dolci, birre, vini...), un numero identificativo, un nome, una descrizione e un prezzo. Ogni elemento della comanda ha una quantità.
3. Ogni piatto è composto da alcuni ingredienti; solo per le pizze si possono fare aggiunte o eliminazioni di ingredienti (una capricciosa senza funghi con doppia mozzarella). Ogni ingrediente ha un prezzo, da contare nell'aggiunta.
4. Conto: a fine pasto si può chiedere scontrino o fattura. Lo scontrino contiene le pietanze scelte con il prezzo e la data di emissione. La fattura ha un numero identificativo, la lista delle pietanze e un codice fiscale o una partita IVA. Nessun sovrapprezzo per il coperto.

Interrogazioni (svolte nell'esercizio sulle query): somma di birre e pizze ordinate il 1/3/2013; piatti e prezzo (con aggiunte/eliminazioni) della fattura 10; media dei conti del tavolo 5.

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 →. Le query sono in Esercizio - Interrogazioni sugli ordini di una pizzeria (tema d'esame 1 marzo 2013).

1. Analisi dei requisiti

Attori e concetti. Gli imprenditori e i neolaureati non fanno parte della base di dati: sono chi la commissiona, non chi interagisce con i suoi dati (come i ricercatori nel tema delle formule). Concetti da modellare:

Il cameriere come attributo? Sbagliato: il testo non dà altri dati, ma il cameriere è una persona reale che può servire più tavoli e comande, quindi è un'entità (con ID).

2. Tavolo, cameriere, comanda

Relazione Serve ternaria: Tavolo (0,N), Cameriere (1,N), Comanda (1,1): una comanda esiste per un tavolo e un cameriere ed è unica; un cameriere ha almeno una comanda, un tavolo può non averne (0,N). Il modello permette a un tavolo di avere comande di camerieri diversi, quindi serve un vincolo esterno: un tavolo è seguito da un solo cameriere. (Alternativa formalmente corretta ma meno efficace: due binarie Appartiene(Tavolo (0,N), Comanda (1,1)) e Serve(Cameriere (0,N), Comanda (1,1)); la comanda è sempre associata a un tavolo e a un cameriere e la ternaria lo dice in una relazione sola.)

Errori comuni (indicati dal corso):

1. Modificare le pietanze con una relazione Modifica(Ingrediente, Pietanza) con un attributo Aggiunge/Toglie: come si dice che la pietanza della seconda comanda non ha modifiche, se nella prima c'era? Il legame è tra ingrediente e pietanza del menu, non tra ingrediente e pietanza ordinata.
2. Collegare Tavolo e Cameriere con una relazione (0,1) e poi Cameriere con la Comanda: non si capisce a quale tavolo si riferisce la comanda (la relazione tra due entità e la comanda va tenuta insieme dalla ternaria).
3. Cardinalità sbagliate: "ogni comanda deve avere almeno una modifica" (se 0, una comanda senza pietanze): le modifiche sono facoltative, le pietanze obbligatorie.

3. Pietanze e piatti

Pietanza (Numero, Nome, Descrizione, Prezzo) è una generalizzazione di Piatto e Bevanda (completa e disgiunta). Ogni comanda ha almeno una pietanza (1,N). La Categoria (antipasto, pizza, dolce; analcolico, vino, birra) si può vedere come generalizzazione di secondo livello, che nella ristrutturazione si sostituisce con l'opzione 2 (attributo discriminante).

Il problema delle modifiche. Qui sta la parte difficile. Un piatto è composto da ingredienti (Compone: Piatto (1,N), Ingrediente (0,N)), ma le modifiche valgono per un piatto ordinato in una comanda, solo se è una pizza.

Soluzione. Una nuova entità PietanzaOrdinata, che rappresenta una riga della comanda: una pietanza con una quantità e le sue modifiche. Le istanze di PietanzaOrdinata sono identificate da un ID insieme alla pietanza scelta (identificatore esterno, perché l'ID distingue le righe con la stessa pietanza).

Eventi da poter rappresentare:

• S1 comanda con pietanze tutte diverse e nessuna modifica;
• S2 pietanze tutte diverse, alcune modificate;
• S3 la stessa pietanza ordinata più volte (due pizze margherita) con modifiche diverse;
• S4 la stessa pietanza modificata con lo stesso ingrediente (una margherita con doppia mozzarella e una senza): due righe, una con modifica e una senza. Funziona perché ogni riga ha le sue modifiche.

Il fatto che solo le pizze siano modificabili è un vincolo esterno.

4. Il conto

Conto è una generalizzazione di Scontrino e Fattura (ognuno con attributi propri: Scontrino la data di emissione; Fattura il numero e il codice fiscale o la partita IVA). Paga: Conto (1,1), Comanda (0,1): un conto si riferisce a una sola comanda, e una comanda può non essere ancora pagata.

Il totale del conto è un attributo derivato: si ricava dalle pietanze ordinate, dai prezzi e dalle modifiche attraverso lo schema (sommando tutto). Il corso lo tiene, documentato, perché è comodo per la terza query (media dei conti di un tavolo): senza, servirebbe un join su comanda, pietanze, modifiche e ingredienti. Il totale si calcola alla chiusura del conto e non cambia più (vincolo esterno: coincide con la somma).

Nella ristrutturazione la generalizzazione Conto ⊃ {Scontrino, Fattura} (completa e disgiunta, sottoclassi con pochi attributi, operazioni sul conto intero) diventa un'entità unica con attributo Tipo: opzione 2. I campi di una sola sottoclasse diventano opzionali:

• Tipo (1,1); NumeroFattura (0,1), CF (0,1), PIVA (0,1), Data (0,1);
• vincolo esterno: se Tipo = fattura sono obbligatori NumeroFattura e almeno uno tra CF e PIVA; se Tipo = scontrino è obbligatoria Data e gli altri sono NULL.

5. Schema ER finale

Vincoli esterni: (1) un tavolo è servito da un solo cameriere; (2) solo le pizze hanno Modifica; (3) le modifiche "toglie" riguardano ingredienti che compongono la pizza; (4) Totale = somma dei prezzi delle pietanze ordinate (quantità comprese) più le aggiunte e meno le eliminazioni; (5) i vincoli sul tipo di conto.

6. Schema relazionale

Cameriere(*id)
Tavolo(*numero, stato)
Comanda(*numero, data, tavolo, cameriere)         FK tavolo -> Tavolo(numero); FK cameriere -> Cameriere(id)
Pietanza(*numero, nome, descrizione, prezzo, tipo, categoria)
Ingrediente(*nome, prezzo)
Compone(*ingrediente, *pietanza)                  FK ingrediente -> Ingrediente(nome); FK pietanza -> Pietanza(numero)
PietanzaOrdinata(*id, *pietanza, comanda, qta)    FK pietanza -> Pietanza(numero); FK comanda -> Comanda(numero)
Modifica(*id, *pietanza, *ingrediente, tipo)      FK (id, pietanza) -> PietanzaOrdinata(id, pietanza); FK ingrediente -> Ingrediente(nome)
Conto(*id, comanda, totale, tipo, numero_fattura?, piva?, cf?, data?)   FK comanda -> Comanda(numero)

• Serve (ternaria con Comanda (1,1)) → fusa in Comanda (due chiavi esterne NOT NULL).
• Ordina (1,1) lato PietanzaOrdinata → fusa (colonna comanda); Sceglie (1,1) → fusa; la FK pietanza fa parte della chiave (identificatore esterno).
• Modifica: molti-a-molti con attributo → relazione con le chiavi (che includono la chiave composta di PietanzaOrdinata) e il tipo (aggiunge o toglie).
• Compone: molti-a-molti → relazione con le due chiavi.
• Paga Conto (1,1) → fusa in Conto (comanda, UNIQUE perché una comanda ha al più un conto).
• Generalizzazione Pietanza ⊃ {Piatto, Bevanda} e Categoria: opzione 2, attributi tipo e categoria; Conto: opzione 2, attributo tipo.
• Compone vale per i piatti; si impedisce Modifica sulle pietanze non pizza con un vincolo esterno (trigger).

Versione SQL (provata in SQLite con dati di esempio, tra cui una comanda con due margherite modificate in modo diverso):

CREATE TABLE Cameriere (id INTEGER PRIMARY KEY);
CREATE TABLE Tavolo (numero INTEGER PRIMARY KEY,
  stato TEXT NOT NULL CHECK (stato IN ('libero','occupato','in attesa del conto')));
CREATE TABLE Comanda (numero INTEGER PRIMARY KEY, data DATE NOT NULL,
  tavolo INTEGER NOT NULL REFERENCES Tavolo(numero),
  cameriere INTEGER NOT NULL REFERENCES Cameriere(id));
CREATE TABLE Pietanza (numero INTEGER PRIMARY KEY, nome TEXT NOT NULL, descrizione TEXT,
  prezzo REAL NOT NULL, tipo TEXT NOT NULL CHECK (tipo IN ('piatto','bevanda')), categoria TEXT NOT NULL);
CREATE TABLE Ingrediente (nome TEXT PRIMARY KEY, prezzo REAL NOT NULL);
CREATE TABLE Compone (ingrediente TEXT REFERENCES Ingrediente(nome),
  pietanza INTEGER REFERENCES Pietanza(numero), PRIMARY KEY (ingrediente, pietanza));
CREATE TABLE PietanzaOrdinata (id INTEGER, pietanza INTEGER REFERENCES Pietanza(numero),
  comanda INTEGER NOT NULL REFERENCES Comanda(numero), qta INTEGER NOT NULL CHECK (qta > 0),
  PRIMARY KEY (id, pietanza));
CREATE TABLE Modifica (id INTEGER, pietanza INTEGER, ingrediente TEXT REFERENCES Ingrediente(nome),
  tipo TEXT NOT NULL CHECK (tipo IN ('aggiunge','toglie')),
  PRIMARY KEY (id, pietanza, ingrediente),
  FOREIGN KEY (id, pietanza) REFERENCES PietanzaOrdinata(id, pietanza));
CREATE TABLE Conto (id INTEGER PRIMARY KEY, comanda INTEGER NOT NULL UNIQUE REFERENCES Comanda(numero),
  totale REAL NOT NULL, tipo TEXT NOT NULL CHECK (tipo IN ('scontrino','fattura')),
  numero_fattura INTEGER UNIQUE, piva TEXT, cf TEXT, data DATE,
  CHECK ((tipo = 'scontrino' AND data IS NOT NULL AND numero_fattura IS NULL AND piva IS NULL AND cf IS NULL)
      OR (tipo = 'fattura' AND numero_fattura IS NOT NULL AND (piva IS NOT NULL OR cf IS NOT NULL))));

Il CHECK su Conto esprime il vincolo 5. Esempio S4: la comanda 1 ha due righe di margherita (ID 1 e 2, pietanza uguale): alla prima si aggiunge mozzarella, alla seconda niente; nessun conflitto di chiave perché la chiave di PietanzaOrdinata contiene l'ID.

7. Osservazioni sul materiale

• Il corso mostra anche un'alternativa con il solo attributo Quantità sulla relazione Ordina: non basta per le modifiche (punto 3): per questo si introduce PietanzaOrdinata.
• Nello schema scritto a mano del corso la relazione PIETANZA ha un attributo tipo e la categoria come generalizzazione di secondo livello sostituita con l'opzione 2: la categoria resta un attributo.
• Nel testo la clausola "in nessun caso viene aggiunto un sovrapprezzo per il coperto" serve solo a dire che Totale non ha termini extra.

Domande d'esame

Perché non basta una relazione Modifica(Ingrediente, Pietanza) con attributo Aggiunge/Toglie? Traccia. La coppia (ingrediente, pietanza) è unica, quindi non si possono modificare due pizze uguali in modo diverso nella stessa comanda, né distinguere una comanda dall'altra. Serve un'entità PietanzaOrdinata (una riga della comanda) con le sue modifiche.

Come si traduce Conto ⊃ {Scontrino, Fattura}? Traccia. Opzione 2: una sola relazione con attributo Tipo e campi opzionali, più vincolo esterno (fattura: numero e almeno uno tra CF e PIVA; scontrino: data). I campi opzionali sono NULL a seconda del tipo, ma la scelta è semplice e conserva tutte le combinazioni.