Come progettare il database fino al modello logico - Hotel Aurora
Da cliente a fattura: modelliamo clienti privati e aziende, prenotazioni, camere, servizi extra e fatture con generalizzazione totale ed esclusiva, attributi composti e multivalore, associazioni 1:N, N:M e 1:1. La traccia completa, dal concettuale al logico.
1. Lo scenario Hotel Aurora: cosa ci chiede la traccia
L’Hotel Aurora vuole realizzare un database per gestire clienti e prenotazioni.
Clienti. Privati o aziende (mai entrambe). Per ogni cliente: codice cliente, indirizzo (via, numero civico, CAP, città), email, uno o più telefoni. Privati: codice fiscale, nome, cognome, data di nascita. Aziende: partita IVA, ragione sociale, codice destinatario per la fatturazione elettronica.
Prenotazioni. Un cliente può effettuare molte prenotazioni, anche nessuna; ogni prenotazione è di un solo cliente. Codice, data di registrazione, data di arrivo, data di partenza.
Camere. Ogni prenotazione comprende almeno una camera; una camera può comparire in molte prenotazioni in periodi diversi, oppure in nessuna. Numero (unico nell'hotel), piano, tipologia (singola, doppia, suite), posti letto. Per ogni camera in prenotazione: prezzo per notte concordato e numero di ospiti.
Servizi extra. Codice, descrizione, prezzo unitario. Per ogni prenotazione, per ciascun servizio richiesto, la quantità complessiva. Un servizio può essere richiesto in molte prenotazioni; una prenotazione può non richiederne nessuno.
Fatturazione. Al massimo una fattura per prenotazione; ogni fattura si riferisce a una sola prenotazione. Identificata da anno + numero progressivo nell'anno; data di emissione e importo totale.
Questa è una delle tracce che uso sempre al compito in classe: sembra una semplice gestione alberghiera, ma in realtà contiene quasi tutto il programma. Generalizzazione, composto, multivalore, N:M con attributi, 1:1. Se sbagli uno di questi, perdi la parte logica.
Quando leggi "mai entrambe le cose" → sottolinea DUE volte. È il segnale di generalizzazione esclusiva. E quando leggi "ogni cliente è privato oppure azienda" → è anche totale. In verifica scrivo sempre: "Quanti tipi può essere un cliente?" Risposta giusta: esattamente uno. Mai zero, mai due.
2. Le 4 consegne: cosa devi consegnare
Ipotesi, entità, associazioni
Attributi, identificatori e molteplicità. Senza ipotesi dichiarate, il resto non si può valutare.
Disegno con (min,max)
Cardinalità e generalizzazione indicate. Lo schema si deve leggere ad alta voce.
Regole motivate, PK e FK
Non basta elencare le tabelle: devi dire quale regola hai usato e perché.
Quelli che il logico non esprime
È qui che si vede se hai capito davvero: il logico non dice tutto.
Non guardo solo il disegno. Guardo se le ipotesi sono dichiarate, se le molteplicità si leggono, se la derivazione è motivata e se i vincoli mancanti sono elencati. Un E/R senza ipotesi è un disegno, non una progettazione.
3. Ipotesi e analisi preliminare
Prima di disegnare, dichiaro le ipotesi. Sono le decisioni che prendo dove la traccia tace: se non le scrivi, chi corregge non può sapere se hai sbagliato o se hai semplicemente scelto un'altra interpretazione.
| # | Ipotesi | Perché / cosa cambia |
|---|---|---|
| 1 | L'hotel è uno solo: non serve un'entità HOTEL e il numero di camera è unico in tutto l'hotel. | Senza questa, CAMERA diventerebbe debole. Con più hotel, servirebbe HOTEL come padre. |
| 2 | Ogni cliente ha un codice interno (id_cliente) che lo identifica a prescindere dal tipo. Codice fiscale e partita IVA sono univoci nel rispettivo sottotipo, ma non esistono per tutti i clienti. | Per questo il codice fiscale non può identificare CLIENTE: le aziende non ce l'hanno. |
| 3 | La classificazione dei clienti è totale ed esclusiva: ogni cliente è esattamente un privato oppure un'azienda. | È la (T,E) della generalizzazione: mai zero, mai due. |
| 4 | Un cliente ha almeno un telefono e può averne più di uno: attributo multivalore (1,N). | Nel logico diventerà una tabella a parte, non una colonna. |
| 5 | L'indirizzo è un attributo composto (via, civico, CAP, città): le componenti si usano anche separatamente, per esempio per cercare per città. | Nel logico si scompone in quattro colonne. |
| 6 | La tipologia della camera è un attributo e non un'entità: nella traccia ha solo un nome e nessuna proprietà propria. | Diventa entità solo se avesse attributi suoi (es. prezzo di listino, descrizione). |
| 7 | I servizi extra si registrano con una quantità complessiva per prenotazione, senza data: la coppia (prenotazione, servizio) compare una sola volta. Se servisse la data di ogni erogazione, il servizio richiesto diventerebbe un'entità. | È l'ipotesi che giustifica RICHIEDE come associazione N:M e non come entità. |
| 8 | Il prezzo per notte sta nell'associazione COMPRENDE perché può differire dal prezzo di listino e cambiare nel tempo. | Stessa camera, prenotazioni diverse → prezzi diversi. |
| 9 | Non si registrano i dati anagrafici degli ospiti, solo il loro numero. | Altrimenti servirebbe un'entità OSPITE. |
| 10 | L'importo totale della fattura è memorizzato come valore storico, non ricalcolato ogni volta. | Se i prezzi cambiano dopo, la fattura resta quella emessa. |
| 11 | Una camera nuova può non avere ancora prenotazioni e un servizio appena inserito può non essere ancora stato richiesto: da qui le minime a 0. | Le minime a 0 non sono un dettaglio: decidono NULL e UNIQUE nel logico. |
In verifica, l'ipotesi 7 è quella che separa il 6 dal 9. Chi scrive "RICHIEDE è N:M perché la quantità è complessiva e senza data" ha capito. Chi fa RICHIEDE entità senza motivare, sta improvvisando. Scrivi sempre il "perché", non solo il "cosa".
4. Le entità e la generalizzazione
CLIENTE
id_cliente, indirizzo composto, email, telefono (1,N). Generalizzazione (T,E) verso PRIVATO e AZIENDA.
PRIVATO
codice_fiscale, nome, cognome, data_nascita. Eredita id_cliente dal padre.
AZIENDA
partita_iva, ragione_sociale, codice_destinatario. Eredita id_cliente dal padre.
PRENOTAZIONE
codice_prenotazione, data_registrazione, data_arrivo, data_partenza. Si collega a cliente, camere, servizi e fattura.
CAMERA
numero_camera, piano, tipologia, posti_letto. Identificatore: il numero, unico nell'hotel.
SERVIZIO
codice_servizio, descrizione, prezzo_unitario. Catalogo dei servizi extra.
FATTURA
anno + numero_fattura, data_emissione, importo_totale. Identificatore composto.
| Entità | Frase della traccia | Perché è un'entità |
|---|---|---|
| CLIENTE | «I clienti sono privati o aziende» | Ha identità propria e partecipa a più associazioni |
| PRIVATO | «Per i clienti privati interessano anche…» | Sottoentità con attributi propri |
| AZIENDA | «per le aziende partita IVA…» | Sottoentità con attributi propri |
| PRENOTAZIONE | «Di ogni prenotazione si registrano un codice…» | Ha un identificatore e si collega a clienti, camere, servizi e fattura |
| CAMERA | «Di ogni camera interessano il numero…» | Ha un identificatore (il numero) e proprietà proprie |
| SERVIZIO | «ciascuno con un codice, una descrizione e un prezzo» | Ha identificatore e proprietà proprie |
| FATTURA | «identificata dall'anno e dal numero progressivo» | Ha un identificatore composto |
Indirizzo, telefono e tipologia non sono entità: sono proprietà senza attributi propri. Indirizzo è composto, telefono è multivalore, tipologia è un semplice attributo con tre valori possibili. E le date non sono entità: sono attributi. Se trasformi tutto in entità, lo schema esplode e diventa illeggibile.
Disegnala con due frecce dai figli verso il padre e l'etichetta circolare al cui interno è scritto (T,E)(T,E). T = totale: ogni cliente è in almeno un figlio. E = esclusiva: in al massimo uno. L'etichetta è la sostanza, non la decorazione.
5. Le associazioni
Perché il diagramma si legge come frase: "CLIENTE EFFETTUA PRENOTAZIONE". Se metti "effettua" o "ha" poi nella relazione inversa non torna. All'infinito è neutro e professionale. Stessa regola che uso per tutte le tracce.
6. Attributi e identificatori
| Entità o associazione | Attributi | Note |
|---|---|---|
| CLIENTE | id_cliente, indirizzo, email, telefono | indirizzo è composto (via, civico, cap, città); telefono è multivalore (1,n) |
| PRIVATO | codice_fiscale, nome, cognome, data_nascita | |
| AZIENDA | partita_iva, ragione_sociale, codice_destinatario | |
| PRENOTAZIONE | codice_prenotazione, data_registrazione, data_arrivo, data_partenza | |
| CAMERA | numero_camera, piano, tipologia, posti_letto | |
| SERVIZIO | codice_servizio, descrizione, prezzo_unitario | |
| FATTURA | anno, numero_fattura, data_emissione, importo_totale | |
| COMPRENDERE | prezzo_notte, numero_ospiti | Dipendono dalla coppia prenotazione-camera, non da una sola delle due |
| RICHIEDERE | quantita | Dipende dalla coppia prenotazione-servizio |
| EFFETTUARE, GENERARE | nessuno |
Un attributo va nell'associazione quando il suo valore cambia al cambiare della coppia di istanze collegate. Il prezzo per notte non può stare in CAMERA, perché la stessa camera ha prezzi diversi in prenotazioni diverse. La quantità non può stare in SERVIZIO, perché lo stesso servizio ha quantità diverse in prenotazioni diverse. Se lo metti nell'entità sbagliata, perdi informazione.
Identificatori
| Entità | Identificatore | Tipo |
|---|---|---|
| CLIENTE | id_cliente | Semplice |
| PRIVATO | id_cliente (ereditato dal padre) | Alternativo: codice_fiscale |
| AZIENDA | id_cliente (ereditato dal padre) | Alternativo: partita_iva |
| PRENOTAZIONE | codice_prenotazione | Semplice |
| CAMERA | numero_camera | Semplice (unico nell'hotel) |
| SERVIZIO | codice_servizio | Semplice |
| FATTURA | anno + numero_fattura | Composto |
Un identificatore deve essere univoco, sempre presente e stabile nel tempo. Il codice fiscale non identifica CLIENTE: le aziende non ce l'hanno. È identificatore alternativo solo dentro PRIVATO, non del padre. Stesso discorso per la partita IVA.
7. Molteplicità, regole di lettura e cardinalità
Il primo valore dice se la partecipazione è opzionale (0) o obbligatoria (1); il secondo se l'istanza partecipa al più una volta (1) o più volte (N).
| Associazione | Entità | (min,max) | Perché |
|---|---|---|---|
| EFFETTUARE | CLIENTE | (0,n) | «molte prenotazioni, anche nessuna» |
| EFFETTUA | PRENOTAZIONE | (1,1) | «effettuata da un solo cliente»: sempre uno |
| COMPRENDERE | PRENOTAZIONE | (1,n) | «almeno una camera», anche due o tre |
| COMPRENDERE | CAMERA | (0,n) | «molte prenotazioni» oppure «in nessuna» |
| RICHIEDERE | PRENOTAZIONE | (0,n) | «può non richiederne nessuno» |
| RICHIEDERE | SERVIZIO | (0,n) | «in molte prenotazioni»; un servizio nuovo può non averne (ipotesi 11) |
| GENERARE | PRENOTAZIONE | (0,1) | «al massimo una fattura» |
| GENERARE | FATTURA | (1,1) | «si riferisce a una sola prenotazione» |
| Generalizzazione | CLIENTE | (T,E) | Totale: ogni cliente è privato o azienda. Esclusiva: mai entrambi |
Regole di lettura
Cardinalità
La cardinalità si ricava dai soli valori massimi. Due N danno N:M, due 1 danno 1:1, un 1 e un N danno 1:N. Nell'1:N l'entità con massimo 1 è quella che compare «dal lato N»: ogni sua istanza è collegata a una sola istanza dell'altra entità.
| Associazione | Massimo lato A | Massimo lato B | Cardinalità | Entità con massimo 1 |
|---|---|---|---|---|
| EFFETTUARE | CLIENTE: n | PRENOTAZIONE: 1 | 1:N (un cliente, molte prenotazioni) | PRENOTAZIONE |
| COMPRENDERE | PRENOTAZIONE: n | CAMERA: n | N:M | nessuna |
| RICHIEDERE | PRENOTAZIONE: n | SERVIZIO: n | N:M | nessuna |
| GENERARE | PRENOTAZIONE: 1 | FATTURA: 1 | 1:1 | entrambe |
I valori minimi non cambiano la cardinalità: servono più avanti per decidere dove mettere la chiave esterna e se può essere nulla. Chi deduce la cardinalità dai minimi sbaglia sempre. Cardinalità = solo massimi. Minimi = obbligo e NULL. Due cose diverse, due momenti diversi.
8. Schema E/R finale
Come si disegna: le entità sono rettangoli; le associazioni rombi con il nome; accanto a ogni entità si scrive (min,max); gli attributi sono collegati all'entità o all'associazione; gli identificatori hanno terminale pieno; l'attributo composto ha le componenti collegate; il multivalore si indica con (1,N) sull'attributo; la generalizzazione è una freccia dai figli verso il padre con l'etichetta (T,E).
TUTTE LE ENTITÀ rettangoli con nome all'interno, identificatori (cerchio pieno) + attributi (cerchio vuoto) collegati
TUTTE LE ASSOCIAZIONI rombi con nome, molteplicità (min,max) sul collegamento lato entità
GENERALIZZAZIONE (T,E) freccia dai figli verso il padre con etichetta (T,E)
ATTRIBUTI SPECIALI indirizzo composto con componenti collegate · telefono con (1,N) · prezzo_notte, numero_ospiti e quantita collegati alle associazioni
9. Modello logico: regole, derivazione passo passo, schema finale
9.1 Regole di derivazione
| Costrutto dello schema E/R | Regola | Dove si applica |
|---|---|---|
| Entità | Diventa una relazione; l'identificatore diventa chiave primaria (PK) | Tutte le entità |
| Attributo composto | Si sostituisce con un attributo per ogni componente | indirizzo |
| Attributo multivalore | Nuova relazione con chiave esterna verso l'entità e il valore; PK formata da entrambi | telefono |
| Generalizzazione | Tre opzioni: accorpare i figli nel padre, accorpare il padre nei figli, sostituire con associazioni | CLIENTE |
| Associazione 1:N | Chiave esterna (FK) nella relazione dell'entità con massimo 1; NOT NULL se il minimo è 1 | EFFETTUARE |
| Associazione N:M | Nuova relazione con PK formata dalle FK verso le due entità; gli attributi dell'associazione vi entrano | COMPRENDERE, RICHIEDERE |
| Associazione 1:1 | FK nell'entità con partecipazione obbligatoria (minimo 1), con vincolo UNIQUE | GENERARE |
| Identificatore composto o alternativo | PK composta; gli alternativi diventano UNIQUE | FATTURA; codice fiscale, partita IVA |
9.2 Derivazione passo passo
Passo A: entità e generalizzazione. Si creano le relazioni CAMERA, SERVIZIO, PRENOTAZIONE, FATTURA e CLIENTE. Per la generalizzazione (T,E) si confrontano le tre opzioni:
| Opzione | Come | Vantaggi | Svantaggi | Qui |
|---|---|---|---|---|
| Figli accorpati nel padre | Una sola relazione CLIENTE con un attributo tipo e tutti gli attributi dei figli | Una tabella, nessun join; l'esclusività si controlla con un CHECK sul tipo | Sette colonne specifiche, molte nulle | Accettabile se motivata |
| Padre accorpato nei figli | Due relazioni PRIVATO e AZIENDA con anche gli attributi di CLIENTE | Nessun join per leggere un cliente | EFFETTUARE andrebbe duplicata: PRENOTAZIONE non può avere una FK verso due tabelle | Scartata |
| Sostituzione con associazioni | CLIENTE, PRIVATO e AZIENDA; la PK dei figli è anche FK verso CLIENTE | Attributi comuni e associazioni in un solo posto, nessun NULL | Join per ricostruire un cliente; totalità ed esclusività non esprimibili con le FK | Scelta |
Si sceglie la terza opzione perché i due figli hanno attributi molto diversi e l'associazione EFFETTUARE riguarda il padre. Se avessi accorpato il padre nei figli, PRENOTAZIONE dovrebbe puntare a due tabelle diverse: impossibile con una sola FK. Questo ragionamento vale più di tante tabelle giuste ma non motivate.
Passo B: attributi composti e multivalore. L'indirizzo si scompone in via, civico, cap, citta dentro CLIENTE. Il telefono, multivalore, diventa la relazione TELEFONO_CLIENTE con PK (id_cliente, numero) e FK id_cliente verso CLIENTE.
Passo C: associazioni.
- EFFETTUARE (1:N): PRENOTAZIONE ha massimo 1, quindi riceve la FK id_cliente. Il minimo è 1, quindi la FK è NOT NULL.
- COMPRENDERE (N:M): nuova relazione COMPRENDERE con PK (codice_prenotazione, numero_camera), entrambe FK, più prezzo_notte e numero_ospiti.
- RICHIEDERE (N:M): nuova relazione RICHIEDERE con PK (codice_prenotazione, codice_servizio), entrambe FK, più quantita.
- GENERARE (1:1): FATTURA ha partecipazione obbligatoria (1,1), quindi riceve la FK codice_prenotazione, NOT NULL e UNIQUE. Mettere la FK in PRENOTAZIONE darebbe valori nulli per le prenotazioni non fatturate e una FK composta (anno, numero).
Se metti la FK da entrambe le parti, o senza UNIQUE, hai sbagliato l' 1:1. Una sola FK, dal lato obbligatorio, con UNIQUE. E se la metti in PRENOTAZIONE, dovresti usare come FK la PK composta di FATTURA (anno, numero): due colonne, quasi sempre nulle. Brutto e sbagliato.
Passo D: ordine di creazione. Prima CLIENTE, CAMERA, SERVIZIO; poi PRIVATO, AZIENDA, TELEFONO_CLIENTE e PRENOTAZIONE; infine COMPRENDERE, RICHIEDERE e FATTURA. Ogni tabella si crea dopo quelle a cui fa riferimento.
9.3 Schema logico finale
Grassetto e sottolineato = chiave primaria; corsivo = chiave esterna; grassetto corsivo sottolineato = chiave primaria che è anche chiave esterna.
Nota tipografica: nel codice sopra, il grassetto indica la PK e il corsivo la FK. In TELEFONO_CLIENTE, PRIVATO e AZIENDA la PK è formata anche da FK: è il caso "chiave primaria che è anche chiave esterna". Nella versione stampata / a mano, sottolinea la PK e usa la freccia o il corsivo per la FK, come facciamo in classe.
Vincoli di integrità referenziale
| Chiave esterna | Riferisce | Note |
|---|---|---|
| TELEFONO_CLIENTE.id_cliente | CLIENTE.id_cliente | |
| PRIVATO.id_cliente | CLIENTE.id_cliente | |
| AZIENDA.id_cliente | CLIENTE.id_cliente | |
| PRENOTAZIONE.id_cliente | CLIENTE.id_cliente | NOT NULL |
| COMPRENDERE.codice_prenotazione | PRENOTAZIONE.codice_prenotazione | |
| COMPRENDERE.numero_camera | CAMERA.numero_camera | |
| RICHIEDERE.codice_prenotazione | PRENOTAZIONE.codice_prenotazione | |
| RICHIEDERE.codice_servizio | SERVIZIO.codice_servizio | |
| FATTURA.codice_prenotazione | PRENOTAZIONE.codice_prenotazione | NOT NULL, UNIQUE |
UNIQUE su PRIVATO.codice_fiscale e AZIENDA.partita_iva; NOT NULL sugli attributi obbligatori; CHECK data_partenza > data_arrivo in PRENOTAZIONE; CHECK sulla tipologia (singola, doppia, suite); quantita e prezzo_notte maggiori di zero.
10. Vincoli che lo schema logico non riesce a esprimere
Questa è la consegna 4, e spesso è quella che fa la differenza tra un compito sufficiente e uno ottimo. Il logico con PK, FK, UNIQUE, NOT NULL e CHECK esprime molto, ma non tutto.
| Vincolo | Perché il logico non lo esprime | Come si risolve |
|---|---|---|
| Totalità ed esclusività della generalizzazione | Ogni cliente deve comparire in esattamente una tra PRIVATO e AZIENDA. Le FK non lo garantiscono. | Trigger o controllo applicativo (oppure, scegliendo l'accorpamento nel padre, un attributo tipo con CHECK) |
| Minime a 1 sul lato dell'entità forte | Ogni prenotazione deve avere almeno una camera in COMPRENDERE e ogni cliente almeno un telefono. Le FK non impongono righe nelle tabelle collegate. | Trigger / controllo in fase di inserimento (transazione) |
| Numero di ospiti | numero_ospiti in COMPRENDE non deve superare posti_letto in CAMERA (vincolo tra due tabelle). | Trigger o controllo applicativo con lettura di CAMERA |
| Camera doppiamente prenotata | La stessa camera non può comparire in due prenotazioni con periodi sovrapposti. | Trigger / controllo applicativo su date di arrivo e partenza |
| Coerenza della fattura | importo_totale dovrebbe corrispondere a notti, prezzi e servizi della prenotazione. | Calcolo applicativo al momento dell'emissione; il valore resta storico |
Questa traccia vale 10 se: 1) dichiari le ipotesi, in particolare la 7 su RICHIEDERE, 2) metti la generalizzazione (T,E) con le tre opzioni confrontate, 3) derivi COMPRENDERE e RICHIEDERE come tabelle ponte con PK sulle due FK, 4) metti la FK di GENERARE in FATTURA con UNIQUE, 5) elenchi i vincoli che il logico non esprime, soprattutto totalità/esclusività e camera doppiamente prenotata. Se manca anche solo uno, non è da 10. Allenatevi a consegnare tutto, non solo il disegnino.
11. Errori comuni, checklist e varianti
1. Mettere prezzo_notte in CAMERA o quantita in SERVIZIO invece che nell'associazione.
2. Scambiare minimo e massimo, o dedurre la cardinalità dai minimi.
3. Mettere la chiave esterna da entrambe le parti in un'associazione 1:1, o senza UNIQUE.
4. Aggiungere una chiave surrogata alle relazioni N:M invece di usare le FK come PK.
5. Dimenticare che le sottoentità hanno PK uguale alla FK verso il padre.
6. Trasformare la tipologia della camera in un'entità senza motivo.
7. Usare il codice fiscale come identificatore di CLIENTE.
| Check | Domanda | Se NO, cosa fare |
|---|---|---|
| Generalizzazione | CLIENTE ha (T,E) e PRIVATO / AZIENDA hanno PK = FK verso CLIENTE? | Aggiungi etichetta (T,E) e correggi le PK dei figli |
| N:M | COMPRENDERE e RICHIEDERE hanno PK sulle due FK, senza surrogata? | Togli la surrogata, PK = (FK1, FK2) |
| 1:1 | FATTURA ha FK codice_prenotazione NOT NULL + UNIQUE? | Sposta la FK in FATTURA e aggiungi UNIQUE |
| Multivalore | TELEFONO_CLIENTE esiste come tabella a parte? | Crea la tabella, non una colonna telefono in CLIENTE |
| Lettura | Riesco a leggere ad alta voce? | Se inciampi, rifai associazioni |
| Vincoli | Ho elencato i vincoli non esprimibili? | Aggiungi totalità, minime a 1, ospiti, sovrapposizione, fattura |
Varianti per differenziare
- Più strutture: aggiungere l'entità HOTEL, e CAMERA diventa entità debole identificata da (hotel, numero).
- Servizi con data di erogazione: RICHIEDERE diventa un'entità con identificatore proprio.
- Associazione ternaria: ogni camera di una prenotazione è assegnata a un addetto alla reception, con dipendente come terza entità.
Copri lo schema logico e prova a ridisegnarlo solo dalla traccia. Poi confronta: hai messo TELEFONO_CLIENTE? Hai scelto la terza opzione per la generalizzazione e l'hai motivata? Hai messo UNIQUE su FATTURA.codice_prenotazione? Se sì a tutte e tre, sei a posto.
12. Riepilogo e concetti chiave
