Normalizzazione di un database: un esempio svolto

Normalizzare un database vuol dire progettare le relative tabelle in modo da minimizzare la duplicazione delle informazioni e rendere agevole la scrittura di query di ricerca e di raggruppamento. Sul web trovate tantissimi riferimenti alla formalizzazione della teoria della normalizzazione di un database. Lo scopo di questo articolo è di presentarvi un esempio concreto che vi spieghi i vari step da seguire e possa svolgere il ruolo di “esercizio svolto” per i vostri studi.

 

Creiamo un database non normalizzato

Lanciate il codice su MySql scaricabile da questo link. Abbiamo creato un database con due tabelle: DimClienti e DimPrestiti. Partiamo dall’analizzare la tabella DimClienti, lanciando l’usuale query

SELECT * FROM DimClienti

NumeroCliente	Residenza
1	Piemonte – 23212
2	Molise – 14212
3	Piemonte – 31231
4	Piemonte – 31222

Osserviamo che la colonna Residenza contiene due informazioni: la regione e il Cap. Questa situazione complica notevolmente tutte le query che richiedono un filtro utilizzando i dati presenti in queste colonne. Ad esempio, come fareste ad estrarre l’elenco dei clienti Piemontesi? Tecnicamente sarebbe ancora fattibile, ma a patto di complicare il codice e peggiorare le performance. Occorre dunque risolvere il problema a monte, normalizzando il database in modo tale che ogni colonna contenga una sola informazione.

 

Normalizzare un database: una sola informazione per ogni colonna

Rinominiamo dunque la seconda colonna in Regione

ALTER TABLE DimClienti
RENAME COLUMN Residenza TO Regione;

e aggiungiamo alla tabella una terza di nome Cap

ALTER TABLE DimClienti
ADD COLUMN Cap varchar(5) not null;

A questo punto, utilizzando il carattere separatore, aggiorniamo la terza colonna con la porzione di stringa che va dal carattere “-” fino alla fine, eliminando lo spazio iniziale con la funzione TRIM. Come spiegato in questo mio articolo sull’aggiornamento di un database, eseguiamo preliminarmente una select per controllare che l’estrazione sia corretta

SELECT regione, ltrim(substring(regione, INSTR(regione,’-‘) + 1,LENGTH(regione)))
FROM DimClienti;

e solo dopo trasformiamola in una query di aggiornamento

UPDATE DimClienti
SET cap = ltrim(substring(regione, INSTR(regione,’-‘) + 1, LENGTH(regione) ) );

In modo simile sovrascriviamo il contenuto della regione con la prima parte della stringa. Lanciamo prima la select per controllare il risultato

SELECT regione, RTRIM(substring(regione,1,INSTR(regione,’-‘)- 1))
FROM DimClienti;

e, quando siamo sicuri che sia tutto ok, la trasformiamo in un Update

UPDATE DimClienti
SET regione = RTRIM(substring(regione,1,INSTR(regione,’-‘)- 1));

Interroghiamo nuovamente la tabella e vedremo che la sua struttura ora segue la prima buona regola di progettazione: un’informazione per una colonna.

NumeroCliente	Regione	CAP
1	Piemonte	23212
2	Molise	14212
3	Piemonte	31231
4	Piemonte	31222

 

Gli attributi di una colonna devono essere relativi alla chiave primaria

Interroghiamo ora la tabella DimPrestiti.

Numero Prestito	Numero Cliente	Data Nascita Cliente	Data Apertura
1	1	19900601	20180101
1	2	19900101	20180101
2	2	19900101	20180601
3	3	19900101	20180901

Osserviamo subito che la colonna DataNascitaCliente è un’informazione relativa ai clienti, di conseguenza deve essere spostata nella tabella dei clienti. Una regola fondamentale sulla normalizzazione di un database è infatti che gli attributi di una tabella devono riferirsi alla sua chiave primaria, non ad un’altra colonna. Vediamo come procedere: per prima cosa aggiungiamo la colonna alla tabella di partenza.

ALTER TABLE DimClienti
ADD COLUMN DataNascita date null;

e aggiorniamola tramite un Update

UPDATE DimClienti
INNER JOIN DimPrestiti
   ON DimClienti.NumeroCliente = DimPrestiti.NumeroCliente
SET DimClienti.DataNascita = DimPrestiti.DataNascitaCliente;

A questo punto rimuoviamo la colonna dalla tabella dei prestiti

ALTER TABLE DimPrestiti
DROP COLUMN DataNascitaCliente;

 

Normalizzazione database: come gestire relazioni N a N

Analizziamo la tabella DimPrestiti dopo l’operazione precedente.

NumeroPrestito	NumeroCliente	DataApertura
1	1	01/01/2018
1	2	01/01/2018
2	2	01/06/2018
3	3	01/06/2018

Resta un ultimo problema: le informazioni sui prestiti sono ridondate per ogni riga del relativo cliente. Per capire come risolvere il problema analizziamo la relazione tra i Prestiti e i Clienti. Si tratta di una relazione N a N (detta anche relazione Molti a Molti), infatti:

• ad un prestito possono essere associati più clienti, ad esempio al prestito 1 sono associati i clienti 1 e 2;
• un cliente può avere più prestiti in corso, ad esempio il cliente 2 ha in corso il prestito 1 e 2;

Per gestire relazioni N a N senza ridondanze di dati occorre creare una terza tabella che riporti solamente le associazioni, senza nessun attributo specifico delle singole entità.

Creiamo dunque questa tabella

CREATE TABLE AssociazionePrestitoCliente
(NumeroPrestito INT NOT NULL, NumeroCliente INT NOT NULL); 

La chiave primaria della tabella che implementa la relazione N a N è data dalla composizione delle chiavi primarie delle due entità associate.

ALTER TABLE AssociazionePrestitoCliente
ADD PRIMARY KEY (NumeroPrestito, NumeroCliente);

Popoliamo la tabella utilizzando le associazioni già presenti nella tabella DimPrestiti

INSERT INTO AssociazionePrestitoCliente (NumeroPrestito, NumeroCliente)
SELECT NumeroPrestito, NumeroCliente
FROM DimPrestiti;

Ecco come si presenta ora la tabella di Associazione

NumeroPrestito	NumeroCliente
1	1
1	2
2	2
3	3

L’ultima operazione da fare è eliminare questa informazione ridondante nella DimPrestiti. Prima di eliminare la colonna NumeroCliente, dobbiamo rimuovere la chiave primaria.

ALTER TABLE DimPrestiti
DROP PRIMARY KEY;

A questo punto rimuoviamo la colonna NumeroCliente

ALTER TABLE DimPrestiti
DROP COLUMN NumeroCliente;

Osserviamo la tabella DimPrestiti:

NumeroPrestito	DataAperturaPrestito
1	01/01/2018
1	01/01/2018
2	01/06/2018
3	01/06/2018

Prima di creare la chiave primaria sulla colonna NumeroPrestito, occorre eliminare le righe duplicate. Su MySql quest’operazione può risultare un po’ più laboriosa rispetto ad altri DBMS.

Occorre creare una colonna contenente un id progressivo

ALTER TABLE DimPrestiti
ADD COLUMN RowNumber int AUTO_INCREMENT primary key;

Ora possiamo usare questa colonna per cancellare le righe duplicate

DELETE p1
FROM DimPrestiti p1
INNER JOIN DimPrestiti p2
ON p1.NumeroPrestito=p2.NumeroPrestito
AND p1.RowNumber > p2.RowNumber;

Infine possiamo cancellare la colonna appena creata e impostare la chiave primaria sulla colonna NumeroPrestito.

ALTER TABLE DimPrestiti
DROP PRIMARY KEY;

ALTER TABLE DimPrestiti
DROP COLUMN RowNumber;

ALTER TABLE DimPrestiti
ADD PRIMARY KEY NumeroPrestito;

 

Normalizzazione di un database: e adesso?

Se sei interessato ad un corso per imparare a gestire un database con l’SQL, visita la pagina del mio corso introduttivo sui database relazionali.