Normalizzazione dei database: 1NF, 2NF, 3NF con esempi

Se sei in quarta in un istituto tecnico a indirizzo informatico o SIA, oppure alle scienze applicate, tra novembre e gennaio il programma di informatica passa dal modello E-R allo schema relazionale, poi alla normalizzazione e infine a SQL. La normalizzazione la spiegano di solito subito dopo l’E-R, quindi nelle prossime settimane. La verifica, scritta o in laboratorio, cade fra dicembre e gennaio: sei-dieci settimane da oggi. Conviene portarsi avanti di un passo rispetto al professore: la regola è corta, ma va applicata nell’ordine giusto e lì si perdono punti per distrazione.

Se arrivi dal blocco di HTML e CSS, cambia il tipo di ragionamento: non scrivi codice per vedere un effetto, decidi come organizzare i dati prima di scriverli. La versione da esame universitario, con BCNF e query, è in Basi di dati: dal modello ER allo schema relazionale e SQL. Qui resto al livello della scuola superiore.

Perché normalizzare: una tabella unica che fa danni

Una libreria online salva tutto in una sola tabella ordini(id_ordine, cliente, indirizzo_cliente, id_libro, titolo, prezzo, quantità). Funziona, finché i dati crescono:

Tre problemi, che all’esame hanno un nome preciso:

• Anomalia di modifica: Rossi cambia indirizzo. Devi correggerlo in tre righe; se ne dimentichi una, il database dice due indirizzi diversi per la stessa persona.
• Anomalia di inserimento: la libreria compra Siddharta e vuole metterlo a catalogo. Non può, perché una riga esiste solo dentro un ordine: id_ordine fa parte della chiave e non può essere vuoto.
• Anomalia di cancellazione: annulli l’ordine 103. Con la riga sparisce anche l’unica informazione su Dune e sul suo prezzo.

La causa è sempre la stessa: la stessa informazione ripetuta, o due informazioni diverse nella stessa riga. Normalizzare vuol dire spezzare la tabella finché ogni fatto è scritto in un posto solo.

Dipendenze funzionali, senza formalismi

Si dice che A determina B, e si scrive $A\to B$, quando il valore di A fissa quello di B. id_libro → titolo significa che un codice libro corrisponde a un solo titolo. Le dipendenze non si ricavano guardando le righe d’esempio: si ricavano dal testo, che dice come funziona il mondo (“un ordine è fatto da un solo cliente”). Due righe che coincidono per caso non fanno una dipendenza.

La chiave è l’insieme minimo di attributi che determina tutti gli altri. Nella tabella sopra una riga è identificata da (id_ordine, id_libro): un ordine ha più libri, un libro compare in più ordini, ma la coppia è unica.

1NF: valori atomici, niente gruppi ripetuti

La prima forma normale chiede che in ogni cella ci sia un solo valore e che non ci siano colonne ripetute per lo stesso dato. Sono fuori dalla 1NF:

Nel primo caso non cerchi “chi fa Nuoto” senza spezzare una stringa; nel secondo, chi fa quattro corsi non ci sta. La correzione è una tabella iscrizione(id_allievo, corso), una riga per coppia. Le tabelle degli esercizi sono già in 1NF.

2NF: tutto dipende dall’intera chiave

Una tabella è in 2NF se è in 1NF e ogni attributo non chiave dipende dall’intera chiave, non da una parte. Il problema esiste solo con le chiavi composte: se la chiave è un attributo solo, la 2NF è automatica.

Nella tabella ordini, titolo e prezzo dipendono solo da id_libro, cioè da metà della chiave. Rimedio: si porta fuori la parte di chiave con tutto ciò che determina, in una tabella propria.

3NF: niente dipendenze transitive

Una tabella è in 3NF se è in 2NF e nessun attributo non chiave dipende da un altro attributo non chiave. Una dipendenza transitiva è una catena $A\to B\to C$ in cui B non è chiave: C dipende da A, ma passando per B.

Per ricordare le tre forme insieme uso la formula classica: ogni attributo dipende dalla chiave (1NF), da tutta la chiave (2NF), e da nient’altro che la chiave (3NF).

Dal diagramma E-R alle tabelle (chiavi esterne, N:N)

Se il diagramma E-R è fatto bene, le tabelle che ne escono sono già quasi in 3NF. Le regole di traduzione sono tre:

1. Ogni entità diventa una tabella, con la sua chiave primaria.
2. Associazione 1:N: la chiave dell’entità con cardinalità 1 diventa chiave esterna nella tabella dal lato N. Un cliente fa molti ordini: ordine ha una colonna che punta a cliente.
3. Associazione N:N: serve una tabella ponte con le due chiavi esterne, che insieme formano la chiave primaria. Un ordine contiene molti libri e un libro sta in molti ordini: la tabella ponte è riga_ordine. Gli attributi dell’associazione, come la quantità, vivono lì, non in ordine né in libro.

Qui si sbaglia di più: la quantità non è del libro e non è dell’ordine, è di quel libro in quell’ordine. Per questo E-R e normalizzazione portano allo stesso schema: la normalizzazione è il controllo finale.

Esercizio 1: la tabella ordini portata in 3NF

Traccia. Porta in 3NF ordini(id_ordine, cliente, indirizzo_cliente, id_libro, titolo, prezzo, quantità). Il testo dice: un ordine appartiene a un cliente; il nome identifica il cliente; un libro ha un solo titolo e un solo prezzo.

1. Chiave. (id_ordine, id_libro).

2. Dipendenze, lette dal testo.

• id_ordine → cliente
• cliente → indirizzo_cliente
• id_libro → titolo, prezzo
• (id_ordine, id_libro) → quantità

3. 1NF. Ogni cella ha un solo valore: l’indirizzo lo tengo come testo unico, perché la traccia non chiede via e città separate. Siamo in 1NF.

4. 2NF. cliente e indirizzo_cliente dipendono solo da id_ordine; titolo e prezzo solo da id_libro. Sono dipendenze da una parte della chiave: le porto fuori.

ordine(id_ordine, cliente, indirizzo_cliente)
libro(id_libro, titolo, prezzo)
riga_ordine(id_ordine, id_libro, quantità)

5. 3NF. Nella tabella ordine c’è la catena id_ordine → cliente → indirizzo_cliente: indirizzo_cliente dipende da cliente, che non è chiave. Porto fuori il cliente.

Schema finale (chiavi primarie in PK, esterne in FK):

cliente(nome_cliente PK, indirizzo_cliente)
ordine(id_ordine PK, nome_cliente FK -> cliente)
libro(id_libro PK, titolo, prezzo)
riga_ordine(id_ordine FK -> ordine, id_libro FK -> libro, quantita; PK = (id_ordine, id_libro))

Con i dati d’esempio:

Negli CREATE TABLE si parte dalle tabelle “padre”: una chiave esterna può puntare solo a una tabella già creata.

CREATE TABLE cliente (
  nome_cliente      VARCHAR(50)  PRIMARY KEY,
  indirizzo_cliente VARCHAR(100) NOT NULL
);

CREATE TABLE libro (
  id_libro CHAR(4)      PRIMARY KEY,
  titolo   VARCHAR(100) NOT NULL,
  prezzo   DECIMAL(6,2) NOT NULL
);

CREATE TABLE ordine (
  id_ordine    INTEGER     PRIMARY KEY,
  nome_cliente VARCHAR(50) NOT NULL,
  FOREIGN KEY (nome_cliente) REFERENCES cliente (nome_cliente)
);

CREATE TABLE riga_ordine (
  id_ordine INTEGER NOT NULL,
  id_libro  CHAR(4) NOT NULL,
  quantita  INTEGER NOT NULL CHECK (quantita > 0),
  PRIMARY KEY (id_ordine, id_libro),
  FOREIGN KEY (id_ordine) REFERENCES ordine (id_ordine),
  FOREIGN KEY (id_libro)  REFERENCES libro (id_libro)
);

(Negli identificatori SQL tolgo l’accento: quantita.) Verifica: un JOIN delle quattro tabelle restituisce esattamente le quattro righe della tabella iniziale, quindi non abbiamo perso nulla; e le tre anomalie sono sparite. L’indirizzo di Rossi sta in una riga sola, Siddharta si inserisce in libro senza ordini, annullare l’ordine 103 non toglie Dune dal catalogo. La JOIN la trovi in SQL: SELECT, JOIN e GROUP BY con esempi svolti.

Una nota da scrivere in verifica: se vuoi conservare il prezzo a cui il libro è stato venduto, perché il listino cambia, aggiungi prezzo_unitario a riga_ordine. Lì dipende dall’intera chiave (quel libro in quell’ordine), quindi resta in 3NF.

Su informatica non c’è ancora una verifica dedicata: nella categoria Verifiche tecnici trovi le simulazioni per le altre materie dell’indirizzo, utili per allenare tempi e formato, ma non su questo argomento.

Esercizio 2: studenti, classi, coordinatore

Traccia. Una scuola tiene iscritti(matricola, nome_studente, classe, id_coordinatore, nome_coordinatore). Ogni studente sta in una sola classe; ogni classe ha un solo coordinatore; un docente può coordinare più classi. Porta in 3NF.

1. Chiave e 2NF. La chiave è matricola, un attributo solo: la 2NF è automatica, non c’è una “parte” di chiave. Il problema è tutto nella 3NF.

2. Dipendenze.

• matricola → nome_studente, classe
• classe → id_coordinatore
• id_coordinatore → nome_coordinatore

3. Catena transitiva. matricola → classe → id_coordinatore → nome_coordinatore. Ci sono due anelli intermedi non chiave: dividere in due tabelle non basta. Se tengo classe(classe, id_coordinatore, nome_coordinatore), il nome di Marini compare due volte (4A e 4C) e id_coordinatore → nome_coordinatore è ancora transitiva. Servono tre tabelle:

docente(id_docente PK, nome_docente)
classe(classe PK, id_coordinatore FK -> docente)
studente(matricola PK, nome_studente, classe FK -> classe)

CREATE TABLE docente (
  id_docente   CHAR(2)     PRIMARY KEY,
  nome_docente VARCHAR(50) NOT NULL
);

CREATE TABLE classe (
  classe          VARCHAR(3) PRIMARY KEY,
  id_coordinatore CHAR(2)    NOT NULL,
  FOREIGN KEY (id_coordinatore) REFERENCES docente (id_docente)
);

CREATE TABLE studente (
  matricola     INTEGER     PRIMARY KEY,
  nome_studente VARCHAR(50) NOT NULL,
  classe        VARCHAR(3)  NOT NULL,
  FOREIGN KEY (classe) REFERENCES classe (classe)
);

Verifica con i dati. docente ha 2 righe (D1 Marini, D2 Pellegrini), classe ne ha 3 (4A-D1, 4B-D2, 4C-D1), studente ne ha 5. Unendo le tre tabelle ricostruisco le cinque righe di partenza, quindi la scomposizione non perde informazioni. Il nome “Marini” ora è scritto una volta sola invece di tre, e una classe 4D senza studenti si può inserire in classe.

Errori tipici

1. Dire “è in 2NF” e fermarsi, con chiave semplice. Con una chiave di un attributo la 2NF è gratis: il controllo vero è la 3NF.
2. Spezzare senza una chiave in comune. Se dividi due tabelle e nessun attributo le collega, non puoi più ricostruire l’originale. Ogni spezzatura lascia una chiave esterna.
3. Mettere la quantità nel posto sbagliato (in libro o in ordine), invece che nella tabella ponte dell’associazione N:N.
4. Dipendenze inventate. Se la traccia non dice che il nome identifica il cliente, non lo assumere: scrivi la tua ipotesi o usa un codice cliente. Un codice (id_cliente) è quasi sempre più pulito del nome; qui uso il nome per fedeltà alla traccia.
5. Colonne ripetute (corso1, corso2, …): sembrano ordinate e violano la 1NF.
6. Chiave composta scritta male: se la chiave è (id_ordine, id_libro) serve PRIMARY KEY (id_ordine, id_libro), non due PRIMARY KEY separate.

Quando conviene denormalizzare

Una tabella normalizzata costringe a fare più JOIN per ogni lettura. Nei sistemi che leggono molto più di quanto scrivono, a volte si ripete un dato di proposito: un totale già calcolato, o il nome del cliente copiato in una tabella di report. Si accetta la ridondanza in cambio di velocità e ci si prende la responsabilità di tenere i dati allineati. In verifica, se dicono “porta in 3NF”, la risposta è la 3NF; se all’orale ti chiedono quando non normalizzeresti, la risposta è questa.

Come ripassare

Rifai i due esercizi su carta senza guardare la soluzione, nell’ordine: chiave, dipendenze, 1NF, 2NF, 3NF, CREATE TABLE. Poi inventa un terzo caso (prestiti di una biblioteca) e cerca le tre anomalie prima e dopo la scomposizione.

Su questo argomento non c’è ancora una simulazione dedicata di informatica: puoi vedere come sono fatte le altre prove nella categoria Verifiche tecnici. Il resto del programma lo trovi in /materie/informatica/. Se vuoi una mano a costruire lo schema di un progetto che non ti torna, la diagnosi gratuita è su /contatti/.