Dal modello E/R alle tabelle

Il diagramma E/R descrive cosa vogliamo salvare. Ora lo trasformiamo in tabelle: è la progettazione logica. Le regole sono poche e sempre le stesse, e all'Esame di Stato questo passaggio vale molti punti.

Come si scrive uno schema logico

Ogni tabella si scrive su una riga: il nome, poi le colonne tra parentesi. La chiave primaria si sottolinea, le chiavi esterne si scrivono in corsivo con un asterisco, indicando la tabella a cui puntano:

autori (id, nome, cognome, nazionalita)
libri (id, isbn, titolo, anno, genere, copie, autore_id*)

• Chiave primaria (primary key, PK): identifica ogni riga. Non può essere vuota (NULL) e non si può ripetere.
• Chiave esterna (foreign key, FK): una colonna che contiene il valore della chiave primaria di un'altra tabella. È il "filo" che collega le tabelle.

Nella riga del libro 1984 c'è autore_id = 4: per sapere chi è l'autore si cerca la riga con id = 4 nella tabella autori, cioè George Orwell.

Le regole di traduzione

1. Ogni entità diventa una tabella

Gli attributi diventano colonne, l'identificatore diventa la chiave primaria. Il nome della tabella di solito è al plurale: LIBRO → libri.

Gli attributi composti si dividono (indirizzo → via, cap, citta). Gli attributi multivalore (più telefoni) non si possono mettere in una colonna: diventano una tabella a parte, telefoni(id, numero, studente_id*).

2. Associazione 1:N → chiave esterna dalla parte "N"

La chiave esterna si mette nella tabella che sta dalla parte dei molti. Un autore ha molti libri, ogni libro ha un solo autore: quindi autore_id va in libri.

Perché non al contrario? Se mettessimo libro_id in autori, ogni autore potrebbe avere un solo libro: per il secondo libro dovremmo ripetere la riga dell'autore. Una cella contiene un solo valore, quindi la chiave esterna va dove il collegamento è uno solo.

3. Associazione N:M → una nuova tabella

Un'associazione molti a molti non si può tradurre con una sola chiave esterna. Diventa una tabella di collegamento (detta anche tabella ponte), che contiene:

• le chiavi esterne verso le due tabelle collegate;
• gli attributi dell'associazione;
• una chiave primaria: un id progressivo oppure la coppia delle due chiavi esterne.

Così l'associazione STUDENTE – prestito – LIBRO diventa la tabella prestiti:

prestiti (id, libro_id*, studente_id*, data_prestito, data_restituzione)

Qui serve un id proprio: lo stesso studente può prendere lo stesso libro due volte in momenti diversi, quindi la coppia (libro_id, studente_id) non è unica. Per le iscrizioni a un corso, invece, la coppia (studente_id, corso_id) basterebbe come chiave primaria.

4. Associazione 1:1 → chiave esterna da una delle due parti

Si mette la chiave esterna in una delle due tabelle, di solito in quella con partecipazione obbligatoria, e la si dichiara UNIQUE così non si ripete. A volte conviene unire le due entità in un'unica tabella.

Lo schema logico della biblioteca

autori   (id, nome, cognome, nazionalita)
libri    (id, isbn, titolo, anno, genere, copie, autore_id*)    autore_id → autori(id)
studenti (id, nome, cognome, classe, email)
prestiti (id, libro_id*, studente_id*, data_prestito, data_restituzione)
                     libro_id → libri(id), studente_id → studenti(id)

C'è anche una quinta tabella, utenti, che non viene dal diagramma: contiene i bibliotecari che possono entrare nell'area riservata e la useremo nella lezione 13.

L'integrità referenziale

Le chiavi esterne non sono solo una convenzione: il DBMS le controlla. È l'integrità referenziale: una chiave esterna deve sempre puntare a una riga che esiste.

• Non si può inserire un libro con autore_id = 99 se l'autore 99 non c'è.
• Non si può cancellare un autore che ha ancora dei libri, altrimenti quei libri punterebbero al vuoto.

Cosa succede quando si prova a cancellare o modificare la riga "puntata" si decide quando si crea la tabella:

Nella biblioteca usiamo RESTRICT: perdere libri e prestiti per una cancellazione sbagliata sarebbe un disastro. CASCADE è adatto per dati che non hanno senso da soli, per esempio le righe di un ordine quando si cancella l'ordine.

La normalizzazione

La normalizzazione è un controllo di qualità sulle tabelle: serve a eliminare la ridondanza e le anomalie (di inserimento, modifica e cancellazione) che abbiamo visto nella lezione 1. Si verifica in tre passi, le forme normali. Se il diagramma E/R è fatto bene, le tabelle sono già normalizzate; la normalizzazione serve soprattutto a trovare gli errori.

Prima forma normale (1NF): un valore per cella

Una tabella è in 1NF se ogni cella contiene un solo valore, indivisibile, e non ci sono gruppi di colonne ripetute.

❌ Non è in 1NF:

Con questa tabella non si può cercare "chi ha il numero 347 2222222" in modo affidabile. Anche le colonne telefono1, telefono2, telefono3 sono un errore: e il quarto numero?

✅ Soluzione: una tabella telefoni (id, numero, studente_id*), con una riga per numero.

Seconda forma normale (2NF): tutto dipende da tutta la chiave

Il problema compare quando la chiave primaria è composta da più colonne. Una tabella è in 2NF se è in 1NF e ogni colonna non chiave dipende dall'intera chiave, non solo da una parte.

❌ Iscrizioni ai corsi con chiave (studente_id, corso_id):

data_iscrizione dipende da entrambi (quando quello studente si è iscritto a quel corso). Ma nome_corso e aula dipendono solo da corso_id: si ripetono a ogni iscritto.

✅ Soluzione: si spostano in una tabella corsi (id, nome, aula) e in iscrizioni resta solo data_iscrizione.

Terza forma normale (3NF): niente dipendenze "a catena"

Una tabella è in 3NF se è in 2NF e nessuna colonna non chiave dipende da un'altra colonna non chiave.

❌ Libri con i dati dell'autore:

autore_nazionalita non dipende dal libro ma dall'autore: id → autore_id → autore_nazionalita è una dipendenza a catena (transitiva). Se si corregge la nazionalità in una riga e non nell'altra, i dati diventano incoerenti.

✅ Soluzione: la nazionalità sta solo nella tabella autori. È proprio lo schema della biblioteca.

Da ricordare. Una frase che riassume le tre forme normali: ogni colonna deve dipendere dalla chiave (1NF), da tutta la chiave (2NF) e da nient'altro che la chiave (3NF).

Quando si "denormalizza"

Ci sono eccezioni consapevoli. Il totale di un ordine si potrebbe sempre ricalcolare dalle righe, ma a volte si salva per velocità. Il prezzo pagato si salva nella riga dell'ordine anche se c'è già nel catalogo: se domani il prezzo cambia, l'ordine di ieri deve restare com'era. In questi casi la ripetizione è voluta e va motivata.

Esercizi

docenti (id, nome, cognome, materia)
classi  (id, anno, sezione, numero_studenti)
gite    (id, meta, data_partenza, giorni, costo, docente_id*)     docente_id → docenti(id)
partecipazioni (classe_id*, gita_id*, studenti_partecipanti)
                classe_id → classi(id), gita_id → gite(id)

studenti (id, nome, cognome, classe)
materie  (id, nome, docente)
voti     (studente_id*, materia_id*, voto)