Introduzione
Il procedimento di normalizzazione serve per eliminare gli errori di progettazione da uno schema relazionale. La normalizzazione trasforma uno schema relazionale non normalizzato in uno schema normalizzato, senza alterarne il contenuto informativo.
Normalizzare uno schema significa ridurre le ridondanze che possono provocare incongruenze, dette anche anomalie.
Le principali anomalie che si possono presentare sono:
- anomalie di inserimento;
- anomalie di aggiornamento;
- anomalie di cancellazione.
Immaginiamo di inserire manualmente dei dati in una tabella di Excel. Supponiamo che la tabella contenga le informazioni di un prodotto, con i campi: (codiceProdotto, descrizione, prezzo). Se nella stessa tabella vengono aggiunte anche informazioni relative al fornitore, ad esempio: (codice fornitore, nome, indirizzo) la struttura dei dati può andare incontro ad anomalie.
Infatti, se devo inserire un nuovo prodotto, sono costretto a ripetere anche tutte le informazioni del relativo fornitore: questa è un'anomalia di inserimento. Se cambia l'indirizzo di un fornitore, devo modificarlo in tutte le righe dei prodotti associati a quel fornitore: questa è un'anomalia di aggiornamento. Infine, se elimino l'ultimo prodotto associato a un determinato fornitore, rischio di perdere anche tutte le informazioni relative al fornitore stesso: questa è un'anomalia di cancellazione.
Applicando il processo di normalizzazione a uno schema relazionale si ottengono relazioni che soddisfano diversi livelli di qualità, chiamati forme normali.
Uno schema che soddisfa la prima, la seconda e la terza forma normale (1NF, 2NF e 3NF) è generalmente ben progettato e riduce il rischio di ridondanze e anomalie durante l'inserimento, l'aggiornamento e la cancellazione dei dati.
1. Prima forma normale
Una relazione R è in prima forma normale (1NF) se ogni attributo di ogni tupla contiene un solo valore atomico (o elementare).
In altre parole, ogni colonna della tabella deve contenere valori atomici, cioè non ulteriormente suddivisibili dal punto di vista dell'applicazione.
Una relazione è in prima forma normale se rispetta i seguenti requisiti:
- ogni attributo contiene valori dello stesso tipo di dato;
- non esistono tuple duplicate (la chiave primaria garantisce l'univocità delle righe);
- ogni attributo contiene un solo valore per ogni tupla (valori atomici).
Ad esempio, nella tabella Giocatore, l'attributo IdGiocatore contiene esclusivamente numeri interi. Una volta definito il tipo dell'attributo, tutti i valori dovranno appartenere a quel dominio.
Inoltre, non possono esistere due righe con la stessa chiave primaria. Possono esistere due persone che si chiamano Mario Rossi, ma dovranno avere identificatori differenti.
Infine, ogni attributo deve rappresentare un solo valore. Ad esempio, un attributo Telefono non dovrebbe contenere una stringa del tipo 055123456 - 3339876543 perché contiene due valori distinti.
Analogamente, se un indirizzo deve essere interrogato separatamente per via, CAP e città, conviene memorizzare tali informazioni in attributi distinti: Via e NumeroCivico, CAP, Città, Provincia. In questo modo ogni attributo rappresenta una singola informazione.
Esempio: le relazioni Clienti ed Imprese in 1NF
Clienti(
CodiceCliente,
CodiceFiscale,
PartitaIVA,
Cognome,
Nome,
Via,
CAP,
Città,
Provincia,
Telefono,
Cellulare,
Email
)
Imprese(
CodiceImpresa,
CodiceFiscale,
PartitaIVA,
RagioneSociale,
Via,
CAP,
Città,
Provincia,
Email,
URL,
Contatto,
Telefono,
Cellulare
)
Esempio: un cliente con più numeri di telefono
Nel modello Entità-Relazione è possibile rappresentare un attributo multivalore. Nel modello relazionale, invece, ogni attributo deve contenere un solo valore.
Supponiamo che un cliente abbia un telefono di casa e uno dell'ufficio. Una soluzione consiste nell'introdurre due attributi distinti: TelefonoCasa e TelefonoUfficio. Sarebbe invece scorretto memorizzare entrambi i numeri nello stesso campo Telefono, separandoli con una virgola o con un altro carattere, perché il campo conterrebbe più valori.
Esempio: Impresa ha attributo Sede multiplo
Consideriamo la relazione Imprese.
Un'impresa può avere più sedi distribuite sul territorio.
Una possibile soluzione consiste nel replicare gli attributi, scrivendo:
Imprese(
...
Via1
CAP1
Città1
Provincia1
Via2
CAP2
Città2
Provincia2
Via3
CAP3
Città3
Provincia3
...)
Questa soluzione è semplice, ma presenta alcuni inconvenienti:
- limita il numero massimo di sedi (ad esempio 3 sedi)
- introduce molti campi inutilizzati (la maggior parte delle imprese ha solo una sede)
- rende più difficile la evoluzione/manutenzione del database.
Una soluzione migliore consiste nel creare una nuova relazione:
Imprese(
CodiceImpresa,
CodiceFiscale,
PartitaIVA,
RagioneSociale,
Email,
Url,
Contatto,
Telefono,
Cellulare)
SediImprese(
IdSede,
Via,
Cap,
Citta,
Prov,
CodiceImpresa (FK))
In questo modo ogni sede viene rappresentata da una riga della tabella SediImpresa, collegata all'impresa tramite una chiave esterna. Se un'impresa possiede quattro sedi, nella tabella Imprese sarà presente una sola riga, mentre nella tabella SediImpresa saranno presenti quattro righe. Questa soluzione elimina il limite massimo di sedi e rende il database più flessibile.
Esempio: Libro ha attributo Autore multiplo
Lo stesso ragionamento vale per i libri. Un libro può essere scritto da più autori e un autore può scrivere più libri. La soluzione consiste nel creare una relazione di collegamento:
Libri(ISBN, Titolo, Editore, NumeroEdizione, ...) Autori(IdAutore, Nome, Cognome, DataNascita) Libri_Autori(ISBN, IdAutore)
dove LibriAutori rappresenta l'associazione molti-a-molti tra libri e autori.
Nota: un errore comune consiste nel pensare che se l'applicazione non deve fare ricerche per autore, allora posso avere Autore1, Autore2, Autore3 oppure una stringa con tutti gli autori. Comunque, un campo che contiene una lista di autori separati da virgole non rispetta la prima forma normale, indipendentemente dal fatto che si effettuino o meno ricerche. Per quanto riguarda i campi Autore1, Autore2, ecc., la situazione è diversa: formalmente ogni colonna contiene un solo valore, quindi la 1NF non è necessariamente violata. Tuttavia è una progettazione poco flessibile e generalmente sconsigliata, perché impone un limite massimo al numero di autori e introduce ridondanza nello schema.
Conclusione
Per rispettare la prima forma normale, ogni attributo deve contenere un solo valore elementare per ogni riga della tabella. Gli attributi multivalore vengono generalmente trasformati in una nuova relazione collegata tramite una relazione uno-a-molti oppure, quando rappresentano un'associazione molti-a-molti tra entità, mediante una relazione di collegamento.
2. Dipendenze funzionali
Per comprendere la seconda forma normale è necessario introdurre il concetto di dipendenza funzionale.
Sia R una relazione e siano X e Y due insiemi di attributi di R.
Si dice che Y dipende funzionalmente da X, e si scrive X → Y, se, per ogni valore di X, esiste uno e un solo valore corrispondente di Y. In altre parole, conoscendo il valore di X, è possibile determinare univocamente il valore di Y.
Ad esempio, nella relazione Clienti:
CodiceCliente → CodiceFiscale
CodiceCliente → Cognome
CodiceCliente → Nome
Poiché ogni cliente è identificato da un solo CodiceCliente, conoscendo tale codice è possibile determinare tutti gli altri attributi del cliente.
Un altro esempio è costituito dalla relazione Penalità:
IdPenalità → Data
IdPenalità → Importo
IdPenalità → IdGiocatore
Le dipendenze funzionali rappresentano proprietà dello schema logico e devono essere valide per tutte le possibili istanze della relazione, non soltanto per i dati presenti in un determinato momento.
3. Seconda forma normale
Una relazione R è in seconda forma normale (2NF) se soddisfa entrambe le condizioni seguenti:
- è già in prima forma normale (1NF);
- ogni attributo non appartenente a una chiave candidata dipende funzionalmente dall'intera chiave e non soltanto da una sua parte.
La seconda forma normale ha significato solo quando esistono chiavi composte, cioè formate da due o più attributi.
Lo scopo della seconda forma normale è eliminare le dipendenze parziali.
Nota: le dipendenze funzionali non dipendono dai dati attualmente presenti nella tabella, ma dal significato degli attributi.
Esempio
Supponiamo di avere le relazioni
Studenti(
Matricola,
Nome,
Cognome,
DataNascita,
LuogoNascita, ...)
Esami(
Matricola,
Corso,
NomeCorso,
Voto,
Lode,
DataEsame)
Supponiamo inoltre che la chiave primaria della relazione Esami sia: (Matricola, Corso). L'attributo NomeCorso dipende soltanto da Corso. Esiste quindi una dipendenza parziale, per cui la relazione non soddisfa la seconda forma normale.
La soluzione consiste nello spostare le informazioni relative ai corsi in una nuova relazione.
Studenti(
Matricola,
Nome,
Cognome,
DataNascita,
LuogoNascita, ...)
Corsi(
IDCorso,
NomeCorso)
Esami(
Matricola,
IDCorso,
Voto,
Lode,
DataEsame)
Altra implementazione della relazione Esami.
AnagrafeStudenti(
Matricola,
Nome,
Cognome,
DataNascita,
LuogoNascita, ...)
Corsi(
IDCorso,
NomeCorso)
Esami(
IDEsame,
Matricola (FK),
Corso (FK),
Voto,
Lode,
DataEsame)
Nota: in molti progetti si preferisce utilizzare una chiave artificiale (surrogate key), ad esempio un identificatore numerico progressivo, per semplificare alcuni aspetti implementativi. Tuttavia questa è una scelta progettuale e non è richiesta dalla teoria della normalizzazione.
Osservazione
La 2NF non dipende dal numero di colonne della chiave primaria. Per cui, e' un errore pensare che "Usare chiavi primarie che siano un singolo attributo permetterebbe di avere automaticamente il database già in seconda forma normale." Infatti, se nella relazioni Esami introduco la chiave IdEsame ma lascio Esami( nella stessa tabella, continua ad esistere la dipendenza IdEsame, Matricola, Corso, NomeCorso)Corso → NomeCorso.
L'introduzione di una chiave artificiale:
- facilita alcune operazioni;
- semplifica i riferimenti tramite chiavi esterne;
- può migliorare la leggibilità del modello.
Non serve però a "mettere automaticamente il database in seconda forma normale".
La 2NF si ottiene perché NomeCorso è stato spostato nella tabella Corsi, non perché è stato aggiunto IDEsame.
4. Terza forma normale
Una relazione R è in terza forma normale (3NF) se soddisfa i seguenti requisiti:
- è già in seconda forma normale (2NF);
- ogni attributo non chiave dipende direttamente da una chiave candidata e non da un altro attributo non chiave.
In altre parole, nella relazione non devono esistere dipendenze transitive tra gli attributi.
Esempio: relazione Uffici non in 3NF
Uffici(
IDUfficio,
NomeUfficio,
IDCapo(FK),
NomeCapo)
Supponiamo che ogni capo sia identificato univocamente dal proprio identificatore.
Si ha quindi la dipendenza funzionale IDCapo → NomeCapo
Poiché IDCapo non è la chiave della relazione Uffici, l'attributo NomeCapo non dipende direttamente dalla chiave primaria IDUfficio,
ma da un altro attributo non chiave. (Se il nome del capo cambia, lo devo modificare in tutte le tuple della relazione Uffici, Si avrebbe quindi una anomalia di aggiornamento dovuta alla ridondanza).
La relazione, quindi, non soddisfa la terza forma normale.
La soluzione consiste nello spostare le informazioni relative ai capi in una nuova relazione.
Anagrafica(IDCapo, NomeCapo) Uffici(IDUfficio, NomeUfficio, IDCapo(FK))
Ora ogni attributo descrittivo dipende direttamente dalla chiave candidata della propria relazione.
La normalizzazione in 3NF si ottiene quindi scomponendo la relazione di partenza in due nuove relazioni, nelle quali tutti gli attributi dipendono direttamente dalla chiave candidata.
5. L’integrità referenziale
Per garantire la correttezza dei dati memorizzati in una base di dati vengono definiti opportuni vincoli di integrità.
I principali sono:
- vincoli di dominio;
- vincoli di chiave;
- vincoli di integrità referenziale.
5.1 Vincoli di dominio
Stabiliscono quali valori sono ammessi per un determinato attributo.
Ad esempio:
- una data deve essere valida;
- un voto deve appartenere all'intervallo previsto;
- un prezzo non può essere negativo.
5.2 Vincolo di chiave primaria
Ogni valore della chiave primaria deve essere:
- univoco;
- diverso da NULL.
5.3 Integrità referenziale
L'integrità referenziale garantisce la coerenza tra due relazioni collegate tramite una chiave esterna.
Una chiave esterna può contenere soltanto:
- un valore presente nella tabella di riferimento;
- oppure, se consentito, il valore NULL.
Regole generali
Quando è definito un vincolo di integrità referenziale:
- non è possibile inserire una chiave esterna che non esista nella tabella padre;
- non è possibile eliminare una riga della tabella padre se esistono righe figlie che la referenziano, salvo che siano state definite opportune regole di cancellazione (
ON DELETE CASCADE,SET NULL, ecc.); - non è possibile modificare il valore della chiave primaria della tabella padre se esistono riferimenti nella tabella figlia, salvo specifiche regole di aggiornamento (
ON UPDATE CASCADE, ecc.).
Esempio di base di dati con anomalie
La tabella Studenti contiene righe con anomalie (evidenziate con un asterisco): una foreign key CodScuola mancante oppure che non ha corrispondenza, una data di nascita troppo vecchia, un primary key matricola con valore duplicato. La tabella Scuole contiene una riga con primary key assente.
Studenti
| MATRICOLA | NOME | COGNOME | DATANASCITA | CODSCUOLA |
|---|---|---|---|---|
| 2345 | Mario | Rossi | NULL | S001 |
| 2346 | Luigi | Verdi | 03/10/88 | NULL * |
| 2347 | Guido | Galli | 20/09/29 * | S001 |
| 2345 * | Enrico | Bruni | 17/3/91 | S003 |
| 2348 | Paolo | Belli | 28/5/97 | S055 * |
Scuole
| CODICE | NOME |
|---|---|
| S001 | ITC Manzoni |
| S002 | Liceo Dante |
| S003 | Liceo Fermi |
| NULL * | ITIS Galvani |
Questi errori vengono prevenuti definendo opportuni vincoli nello schema della base di dati (DDL).
Il vincolo di integrita' referenziale esistente tra le tabelle Studenti e Scuole, ci permette l'inserimento di nuove righe nella tabella Scuole, ma non permette l'inserimento di nuove righe nella tabella Studenti, a patto che queste non siano associate ad una scuola tramite un valore della chiave FK esistente sulla tabella Scuole. Di solito, questo lo fa l'applicativo: se si sviluppa una form per l'inserimento di un nuovo studente, il campo che specifica la scuola non e' una casella di testo, ma una lista che permette di selezionare un valore all'interno di un insieme di nomi di scuole. L'integrita' referenziale in inserimento e' garantita.
Esempio di base di dati con anomalia da cancellazione
Giocatori
| IDGIOCATORE | NOME | COGNOME |
|---|---|---|
| G01 | Mario | Rossi |
| G03 | Paolo | Verdi |
Penalita'
| IDPENALITA | DATA | IMPORTO | IDGIOCATORE |
|---|---|---|---|
| 001 | 10/10/2015 | 100 | G01 |
| 002 | 11/10/2015 | 500 | G02 |
Consideriamo l'associazione Giocatori-Penalita. Supponiamo che sullo schema fisico non ci sia l'intergrita referenziale, per cui cancello il giocatore con id=2, senza aver prima cancellato le sue penalita, dando origine ad una anomalia da cancellazione. Per poter ripristinare l'intergrita referenziale, si deve recuperare la Penalita che ha FK che non si riferisce a nessun giocatore e poi eliminarla. Questo si fa con una query con subquery.
Ad esempio:
SELECT *
FROM Penalita p
LEFT JOIN Giocatori g
ON p.IdGiocatore = g.IdGiocatore
WHERE g.IdGiocatore IS NULL;
oppure, ancora meglio,
SELECT *
FROM Penalita p
WHERE NOT EXISTS
(
SELECT 1
FROM Giocatori g
WHERE g.IdGiocatore = p.IdGiocatore
);
Per cancellare la penalita' con IdPenalita 002, utilizzo la prima query come query di partenza di una seconda query di cancellamento.
6. Esercizio: sistema per la gestione delle gite di una agenzia viaggi
Si vuole progettare una base di dati per la gestione delle gite organizzate da un'agenzia di viaggi.
Per ogni gita si vogliono memorizzare:
- la descrizione;
- la data di partenza;
- la durata;
- il prezzo;
- il responsabile della gita;
- l'elenco dei partecipanti;
- l'itinerario.
Di ogni partecipante si vogliono memorizzare:
- nome;
- cognome;
- data di nascita.
Ogni gita è associata a un itinerario, costituito da una o più tappe.
Di ogni tappa si vogliono memorizzare:
- la località;
- la durata del soggiorno.
Analisi del problema
Si deve costruire uno schema relazionale che e' funzionale alle interrogazioni da fare sul database. Si suppone che l'agenzia di viaggio sia una sola e che non si voglia memorizzare informazioni relative alla agenzia di viaggi.
Le principali entità della base di dati sono:
- Gita
- Partecipante
- Itinerario
- Tappa
- Comune
Il responsabile e' un partecipante speciale alla gita, l'entita responsabile non puo' essere visto una entita' specializzazione di partecipante, perche' ha tutte e sole le proprieta' di partecipante.
L'associazione Gita-Viaggiatore e' una associazione di tipo molti a molti, perche' si suppone che una gita ha piu' viaggiatori e un viaggiatore puo' partecipare a piu' gite organizzate dalla stessa agenzia di viaggi. Molte associazioni Cliente-Servizio sono di tipo molti a molti (ad esempio Cliente-AbbonamentoPalestra, Cliente-AnalisiMedica, Cliente-CorsoDiBallo, ecc.) perche' si suppone che il cliente si registra la prima volta quando usa quel servizio e, quando vuole usare quel servizio una seconda volta, il cliente risulta gia' registrato nel database. Anche l'associazione studente-scuola e' una associazione molti a molti, perche' uno studente puo' fare un anno presso un liceo e poi passare ad un istituto tecnico; per cui, se si vuole tener traccia dello storico, si deve modellare l'associazione come N:N. Allo stesso modo, se si vuole sapere a quante gite ha partecipato un cliente, si deve usare una associazione N:N.
L'associazione gita-responsabile e' di tipo uno ad uno. In realta', il responsabile della gita e' un partecipante alla gita, in piu' e' anche il responsabile della gita, come nell'associazione ufficio-capoUfficio di tipo uno ad uno, il capoufficio e' un impiegato che lavora in quell'ufficio ed e' anche il capo dell'ufficio. Siccome il responsabile e' anche un partecipante, significa che esso e' caratterizzato dagli attributi della tabella partecipante: (id, cognome, nome, dataNascita). Il fatto che il responsabile della gita e' un attributo della gita si sarebbe potuto tradurre aggiungendo gli attributi cognome e nome del responsabile alla gita, ma non sarebbe stato in 3FN, perche' il nome del responsabile non dipende da idGita, ma solo da idPartecipante. Quindi, si aggiunge a gita l'attributo idPartecipante come FK, rispettando la 3FN.
Gli attributi di Gita sono: descrizione, durata in giorni, prezzo, data di partenza. L'attributo descrizione vuole indicare, brevemente, la meta delle gita: ad esempio "Le Cinque Terre", "Arezzo-Cortona", "Isola del Giglio". La gita ha un itinerario, cioe' tutte le tappe della gita, ad esempio per le cinque terre le tappe sono: Monterosso al Mare, Vernazza, Corniglia, Manarola e Riomaggiore. La associazione tra le entita Gita-Tappa e' di tipo 1:N, supponendo che una tappa e' ad esclusivo uso e consumo di una sola gita. Non ha senso inserire una tappa nel database se non la collego ad una gita. Gli attributi di tappa sono durata e localita'. Siccome ci sono delle localita' che sono frazioni, non un comune, posso fare riferimento ad un comune. Un comune puo' essere associato a piu' tappe, una tappa si trova in solo un comune.
Schema relazionale
Gite(
IDGita,
Descrizione,
Durata,
Prezzo,
Data,
IDResponsabile (FK))
Partecipanti(
IDPart,
Cognome,
Nome,
DataNascita)
Gite_Partecipanti(
IDGita (FK),
IDPart (FK))
Tappe(
IDTappa,
Durata,
Localita,
IDComune (FK),
IdGita(FK))
Comuni(
IDComune,
NomeComune)
Lo schema relazionale e' in 3FN normale, poiche' ogni attributo e' atomico, ogni attributo non chiave dipende funzionalmente dalla chiave, ogni attributo non chiave dipende direttamente dalla chiave. Per controllare se lo schema relazionale e' valido, si controlla che la base di dati soddisfa i requisiti, cioe' si controlla se si possono eseguire le interrogazioni che servono al committente. Ad esempio, per trovare nome e cognome di tutti i partecipanti alla gita "Cinque Terre", si eseguono due join, in cui l'ordine in cui si incrociano le tabelle non conta:
S = ( (Gita.IDGita JOIN Gite_Partecipanti.IDGita) JOIN Partecipanti.IDPart ) T = restrict S where Descrizione = "Gita alle 5 Terre" project T on Cognome, Nome
La relazione S, risultato delle due join, ha tutti gli attributi di Gita, tutti gli attributi di RelGP e tutti gli attributi di Partecipanti.
No comments:
Post a Comment