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

Il codice per creare su MySQL il Database utilizzato in questo articolo è disponibile in questa pagina web.

https://raw.githubusercontent.com/iantomasinicola/DatabaseYimp/master/CodiceDbNonNormalizzato.txt

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 a estrarre l’elenco dei clienti Piemontesi? Tecnicamente sarebbe ancora fattibile, ma a patto di complicare il codice e peggiorare le performance.

Nel lavoro reale molto spesso non possiamo permetterci di modificare la struttura di un Database quando esso è già in produzione. Infatti esisteranno tantissime procedure interne ed esterne al Database che sono state costruite utilizzando la sua struttura, anche se non efficiente. Andando a cambiare lo schema delle tabelle, le nostre modifiche provocherebbero dunque una serie lunghissima di malfunzionamenti. Questo ci fa capire quanto importante e delicata sia la parte iniziale di progettazione!

In questo articolo vedremo comunque, a scopo didattico, come risolvere il problema a monte, normalizzando il Database in modo da rendere la sua struttura più efficiente.

Normalizzare un database: una sola informazione per ogni colonna

Partiamo rinominando la seconda colonna della tabella DimClienti in Regione

ALTER TABLE DimClienti
RENAME COLUMN Residenza 
TO Regione;

e aggiungendo una terza colonna di nome Cap

ALTER TABLE DimClienti
ADD COLUMN  Cap VARCHAR(5) NULL;

A questo punto, utilizzando il carattere separatore “-” contenuto nel testo, aggiorniamo la terza colonna con la porzione di stringa che va dal carattere “-” fino alla fine, eliminando lo spazio iniziale con la funzione LTRIM. 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))) AS cap_new
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)) AS regione_new
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 segue ora la prima buona regola di progettazione: un’unica informazione per ogni colonna.

Infine possiamo valutare se imporre anche alla nuova colonna Cap il vincolo NOT NULL

ALTER TABLE DimClienti
MODIFY COLUMN Cap VARCHAR(5) NOT NULL;

Corso SQL Video Academy

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 relativa tabella. Una regola fondamentale sulla normalizzazione di un database è infatti che gli attributi di una tabella devono riferirsi alla sua chiave primaria, non a un’altra colonna.

Vediamo come procedere: per prima cosa aggiungiamo una nuova colonna alla tabella DimClienti.

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:

• a 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 PrestitiClienti(
  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 PrestitiClienti
ADD PRIMARY KEY (NumeroPrestito, NumeroCliente);

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

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

Ecco come si presenta ora la tabella di associazione

L’ultima operazione da fare è eliminare le informazioni ridondanti 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 COLUMN RowNumber;
ALTER TABLE DimPrestiti
ADD PRIMARY KEY (NumeroPrestito);

Concludiamo il lavoro aggiungendo le chiavi esterne alla tabella PrestitiClienti

ALTER TABLE PrestitiClienti
ADD FOREIGN KEY (NumeroPrestito)
REFERENCES DimPrestiti(NumeroPrestito);
ALTER TABLE PrestitiClienti
ADD FOREIGN KEY (NumeroCliente)
REFERENCES DimClienti(NumeroCliente);

Il nostro Database è ora normalizzato!

Il tuo percorso completo di Analisi dei Dati

Conclusioni sulla normalizzazione di un Database

In questo articolo abbiamo studiato come normalizzare un Database che presenta errori di progettazione. In particolare ci siamo soffermati sullo split di una colonna contenente più informazioni, sullo spostare i dati nelle tabelle più idonee e nella gestione delle relazioni molti a molti. Come detto nei paragrafi precedenti, in realtà è molto difficile avere la possibilità di svolgere queste attività nel momento in cui il Database è “in produzione”, in quanto modificarne la struttura potrebbe portare a tutta una serie di errori nelle procedure create sulla sua versione originale. Questo deve farci riflettere ancor di più su quanto sia importante rispettare sin da subito le regole di normalizzazione.

Ecco una lista di pagine che potrebbero interessarti:

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