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;
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 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.
NumeroCliente | Regione | CAP |
1 | Piemonte | 23212 |
2 | Molise | 14212 |
3 | Piemonte | 31231 |
4 | Piemonte | 31222 |
Infine possiamo valutare se imporre anche alla nuova colonna Cap il vincolo NOT NULL
ALTER TABLE DimClienti
MODIFY COLUMN Cap VARCHAR(5) NOT NULL;
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 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.
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:
- 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
NumeroPrestito | NumeroCliente |
1 | 1 |
1 | 2 |
2 | 2 |
3 | 3 |
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:
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 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!
Continua a imparare
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.
- Segui la lezione successiva su cross apply e outer apply
- Torna all’indice delle lezioni
- Visita la pagina del mio videocorso SQL