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;

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.

Gli attributi di una colonna devono essere relativi alla chiave primaria

Interroghiamo ora la tabella DimPrestiti.

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.

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

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:

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;

Video lezione sulla normalizzazione di un Database

Ripercorriamo gli step di normalizzazione di un Database, utilizzando questa volta SQL Server.

Continua a imparare

• un elenco esaustivo di esercizi SQL
• approfondisci la sintassi dell’update con Join
• il mio video-corso di SQL