Capitolo 3
Il modello relazionale
I database sono il fulcro dei sistemi informativi, sono il cuore delle applicazioni. La struttura
di un database, denominata modello di dati (schema o anche progettazione di database),
specifica un database. Uno dei modelli più importanti utilizzati per i moderni database è il
modello relazionale. Benché non sia l'unico modello di dati, è probabilmente il più importante.
Il modello relazionale viene usato principalmente per i database transazionali, dove i dati di
un'azienda vengono prima archiviati, in contrapposizione ai database warehouse che servono
come archivio dei dati storici. Rispetto agli altri modelli di dati di oggi, il modello relazionale è
particolarmente utile per i dati transazionali perché l'integrità dei dati può essere dichiarata e
applicata dal modello. L'integrità dei dati è la conformità dei dati alle regole aziendali. Se i dati
sono sbagliati la prima volta che vengono inseriti in azienda, l'impatto sull'intera azienda sarà
negativo. Ad esempio, i sistemi analitici non aiuterebbero a migliorare le operazioni a causa
del comune concetto garbage in - garbage out. Un altro vantaggio del modello relazionale è
che viene definito matematicamente. Quindi, durante la modellazione non sei guidato solo
dalle procedure consigliate, puoi valutare anche la tua struttura e decidere fermamente se la
tal cosa va bene o meno.
I sistemi di gestione dei database relazionali (RDBMS), incluso Microsoft SQL Server, archiviano
i dati in formato relazionale. Benché l'implementazione fisica sia diversa da rivenditore a
rivenditore, il modello relazionale fornisce una percezione dei dati utente coerente per tutti
i RDBMS. In questo capitolo saranno introdotti i concetti principali del modello relazionale.
Questa conoscenza ti aiuterà a capire i successivi capitoli in cui saranno illustrate query
avanzate.
Introduzione al modello relazionale
Il modello relazionale è stato creato negli anni '60 da Edgar F. Codd, che lavorava all'IBM. È
una concettualizzazione semplice ma definita in modo rigoroso del modo in cui gli utenti
ricevono e utilizzano i dati. Tratta i tre aspetti principali dell'elaborazione dei dati nel seguente
modo, secondo quanto scritto in An Introduction to Database Systems, 8th edition di C. J. Date
(Addison-Wesley, 2003):
■
Strutturale
I dati vengono ricevuti dall'utente solo sotto forma di tabelle.
■
Manipolativo Gli utenti manipolano i dati con un insieme infinito di operatori
relazionali. Gli operatori rappresentano l'algebra relazionale.
■
Integrità Le tabelle devono soddisfare vincoli di integrità definiti.
L'aspetto strutturale viene espresso anche dall'Information Principle, che afferma che tutte le
informazioni di un database relazionale vengono espresse in un (e solo un) modo come valori
espliciti in colonne all'interno delle righe di una tabella.
Nel modello relazionale una tabella viene definita relazione e una riga viene definita tupla.
Nella prossima sezione verranno illustrate più nel dettaglio sia le relazioni sia le tuple.
83
84
Microsoft Transact SQL Query
Relazioni, tuple e tipi
Una relazione è un oggetto matematico che rappresenta ciò che gli utilizzatori dei database
chiamano una tabella. Gli elementi di una determinata relazione, come le righe di una
tabella, rappresentano le istanze di qualche entità reale, come persone, luoghi, cose o eventi.
La relazione rappresenta l'insieme di questi elementi, che sono tuple dal punto di vista
matematico. Una tupla è un insieme dei relativi attributi, ognuno dei quali viene rappresentato
da tre cose: il nome dell'attributo, il tipo di attributo e il valore dell'attributo.
Nota Il modello relazionale utilizza nozioni più generali di relazione e tupla rispetto a quelli
introdotti nel capitolo 2. Nel capitolo 2 hai imparato a conoscere le tuple ordinate, che avevano
parti posizionali bene definite: prima, seconda e così via. Anche qui le tuple hanno parti ben
definite, ma sono non ordinate, inoltre sono definite dai nomi di attributo invece che dalle posizioni
ordinali. Nel capitolo 2 una relazione era un insieme di coppie ordinate di un prodotto cartesiano.
Qui una relazione è un insieme di tuple non ordinate che hanno la stessa intestazione. Le nozioni
usate nel modello relazionale sono più astratte, e renderle precise dal punto di vista matematico
non è molto intuitivo.
L'insieme dei nomi e dei tipi di attributo di una tupla, presi insieme, vengono chiamati
intestazione. Puoi pensare all'intestazione di una tupla come a un modulo che può essere
riempito; il modulo ha nomi di attributi con spazi vuoti per i valori che vanno inseriti. Una tupla
è la copia riempita di un modulo di intestazione. Le proprietà della tupla includono:
■
Ogni attributo di una tupla contiene esattamente un valore del tipo appropriato per
ognuno dei relativi nomi di attributo. Pensando di nuovo a una tupla come a una
copia riempita di un modulo di intestazione, c'è esattamente un valore in ogni spazio
vuoto (e del tipo appropriato per quel particolare attributo).
■
Gli attributi non hanno un ordine (come gli elementi di un set). Di conseguenza ogni
attributo deve avere un nome diverso, perché non puoi fare riferimento a un attributo
usando la relativa posizione in una tupla. In termini di moduli, il modo in cui i nomi di
attributo vengono disposti nel modulo di intestazione è irrilevante; hanno importanza
solo i nomi degli attributi e, di conseguenza, tali nomi devono essere diversi.
■
Un subset di una tupla è una tupla (con meno attributi). Usando di nuovo l'analogia
del modulo, una sezione di un modulo, vista da sola, è comunque un modulo, ma può
avere meno voci.
Benché sia possibile definire gli operatori dall'algebra relazionale sulle tuple, non puoi
manipolare singole tuple in un database relazionale. Le operazioni vengono eseguite solo
sui set di tuple, ossia sulle relazioni. Le tuple non solo costituiscono le relazioni, ma aiutano
anche a definirle. Una relazione è composta da un set di tuple con la stessa intestazione;
l'intestazione di tali tuple può essere chiamata intestazione della relazione e viceversa. Allo
stesso modo, è possibile pensare che le relazioni abbiano degli attributi. Le relazioni con
intestazioni diverse sono tipi di relazioni diversi. Nel modello relazionale i tipi di dati degli
attributi, al contrario dei tipi di intestazioni delle relazioni, a volte vengono definiti domini per
evitare confusione con la parola tipo.
Capitolo 3
Il modello relazionale
85
Come i contenuti di una tabella di database possono cambiare, una relazione dovrebbe
essere in grado di contenere diversi set di tuple in momenti di versi. Le relazioni del modello
relazionale di fatto sono variabili, a volte chiamate variabili relazionali, e il valore di una
variabile relazionale di un qualunque tipo è un set di tuple di tale tipo. Non verrà sempre fatta
una distinzione tra relazioni e variabili relazionali dello stesso tipo, seguendo la pratica comune
di altre aree della matematica. Ad esempio, spesso verrà scritto "n è un numero intero", mentre
bisognerebbe scrivere correttamente "n è una variabile intera". Il fatto che una relazione sia un
set di tuple comporta le seguenti importanti conseguenze:
■
Come per le tuple, gli attributi di una relazione non hanno un ordine.
■
Ogni attributo di una relazione ha un tipo di dati fortemente definito. Ogni tupla di
una relazione contiene esattamente un valore di questo tipo per ogni attributo.
■
La proiezione di una relazione è una relazione, in cui la proiezione è un'operazione che
seleziona un subset specifico di attributi da una relazione (e da tutte le relative tuple).
La proiezione è uno degli operatori più importanti nell'algebra relazionale.
■
Una relazione non ha tuple duplicate. È una conseguenza del fatto che una relazione è
un set e i set contengono elementi distinti. Dal momento che le tuple di una relazione
sono distinte, possono essere distinte da alcuni o da tutti i relativi valori di attributo.
Un subset minimo di attributi sufficiente a distinguere le tuple per ogni valore della
variabile relazionale viene definito chiave.
■
L'ordine delle tuple non è importante. Ancora una volta, questo deriva dalla teoria dei
set, in cui gli elementi di un set non sono ordinati. Ciò significa che in una relazione,
termini come prima, successiva, precedente, ultima tupla non vengono definiti.
Il termine tipo è stato usato più volte, presumendo tacitamente che si capisca di quale tipo
si tratti. Di seguito viene riportata una definizione formale di tipo: un tipo, chiamato anche
tipo di dati o dominio, è un set finito di valori, ad esempio un set finito di valori interi. Benché
in matematica i set universali (ad esempio i valori interi) possano essere infiniti, in un sistema
informatico incontri sempre un limite. Quindi, un set di valori possibili di un tipo è finito.
Ogni valore ha esattamente un tipo più specifico. Con "più specifico" si intende la possibilità
di ereditarietà del tipo (benché l'ereditarietà del tipo non sia ancora implementata in SQL
Server). Ad esempio, il valore 3 può essere considerato un numero reale, un intero o un
numero naturale; un numero naturale è il tipo più specifico per tale numero. In breve, puoi
tranquillamente dire che ogni valore in un database relazionale ha un solo tipo.
Un tipo è composto da:
■
Un nome
■
Uno o più rappresentazioni possibili denominate:
❏
Una è archiviata fisicamente.
❏
Almeno una viene dichiarata agli utenti.
■
Un insieme di operatori possibili sui valori del tipo
■
Vincoli del tipo
86
Microsoft Transact SQL Query
Ogni variabile e ogni attributo ha un tipo esplicito, ogni operatore restituisce un risultato di
qualche tipo esplicito, ogni parametro di ogni operatore ha un tipo esplicito e ogni espressione
è implicitamente di qualche tipo. L'archiviazione fisica non viene esposta agli utenti; dipende
dal sistema. Un tipo vincola i valori possibili in modi diversi: con vincoli espliciti e con operatori
definiti. Ad esempio, per il tipo integer puoi definire gli operatori di addizione, sottrazione e
moltiplicazione. L'operatore di divisione non viene definito come integer per tutte le coppie
di integer perché il risultato può non rientrare nel dominio integer. I numeri naturali sono
gli integer con un vincolo; il numero deve essere positivo (o, secondo alcuni autori, non
negativo). Gli operatori e i vincoli sono caratterizzati da interfoliazione. Nota che l'operatore di
sottrazione non viene definito all'interno dei numeri naturali, anche se lo era per gli integer.
Perché un tipo sia utile, deve implementare almeno due operatori: un operatore mutatore,
che consente l'aggiornamento di variabili e attributi del tipo, e un operatore di selezione, che
consente di recuperare i valori del tipo. Gli altri operatori possono essere definiti dal creatore
del tipo nel modo appropriato all'uso che si intende farne. Nota che il tipo può avere più
presentazioni e quindi può avere più operatori di selezione. Ad esempio, un punto in un piano
può essere rappresentato nei sistemi cartesiani o con coordinate polari.
Un concetto importante è se un tipo è scalare o non scalare. Un tipo non scalare ha un insieme
di componenti visibili all'utente e direttamente accessibili; un tipo scalare no. I tipi scalari
vengono definiti anche atomici o incapsulati. Questa descrizione è in qualche modo vaga. È
chiaro quando un punto nel piano delle coordinate è scalare? Sia le presentazioni cartesiane
che polari hanno componenti visibili all'utente. Tuttavia, se operi solo sui punti interi e mai
sulle singole coordinate, un singolo punto non è divisibile e quindi è scalare. E il tipo car?
Sicuramente ha componenti visibili all'utente, ma normalmente lo consideri come indivisibile
e quindi scalare. Si proverà ora a dare una definizione precisa. Un valore è scalare se lo utilizzi
solo con operatori definiti per il relativo tipo. Gli operatori possono recuperare o aggiornare
una singola coordinata di un punto, ma quando questi operatori vengono definiti sui punti (al
contrario dei numeri), un punto resta comunque scalare.
Un insieme di punti archiviati in una stringa è non scalare se devi operare con i punti recuperati
dalla stringa. Se usi questo insieme come stringa e operi su di esso solo con gli operatori di
stringa, allora questo valore è scalare. Ma quando un insieme di punti definisce un poligono?
Se definito in modo esplicito un tipo poligono, si tratta di un tipo scalare. Se operi con i punti
che definiscono gli angoli del poligono tramite operazioni definite nel tipo poligono, i valori
di questo tipo sono scalari. Nota che questo si riflette sul mondo reale. A volte tratti un valore
come scalare di un qualche tipo e a volte come insieme di componenti in cui ogni componente
ha il proprio tipo. Ad esempio, guidi una macchina come se fosse un valore scalare. Quando
porti la macchina da un meccanico, tuttavia, il meccanico potrebbe trattarla come insieme non
scalare di componenti.
Nelle relazioni sono consentiti solo gli attributi scalari (o atomici). Ciò non significa che i punti
su un piano non possono essere valori attributo di una relazione; tuttavia, i valori dell'attributo
devono essere archiviati usando il tipo più specifico per i punti, in altre parole, il tipo di punto
e non una stringa di coordinate. Qual è il tipo più specifico di un valore? Dipende dall'uso che
si intende farne. Se stai sviluppando un'applicazione per le risorse umane, l'immagine di un
dipendente può probabilmente essere trattata come valore scalare di qualche tipo binario, e
Capitolo 3
Il modello relazionale
87
modelleresti una relazione Subjects usando un singolo attributo Picture. Se stai sviluppando
un'applicazione di riconoscimento facciale e devi analizzare l'immagine usando alcuni grafici
di vettore, modellerai una relazione Subjects usando una relazione Pictures associata che
abbia i propri e più dettagliati attributi (o, in alternativa, una relazione Subjects con attributi
dettagliati di un'immagine invece che un singolo attributo Picture).
Ad ogni modo, il modello relazionale non è limitato all'utilizzo di pochi tipi specifici; supporta
tutti i tipi possibili. Alcuni dei tipi più comuni vengono forniti da un RDBMS e vengono definiti
sistema. Inoltre, un RDBMS dovrebbe consentire di estendere il set di tipi sistema con tipi
definiti dall’utente. SQL Server consente di creare tipi definiti dall’utente nella versione 2005 e
successive.
Il significato di relazioni
Come già detto, ogni relazione rappresenta delle entità del mondo reale, ad esempio persone,
luoghi, cose o eventi. Un'entità è una cosa che può essere identificata in modo distinto ed è
di interesse aziendale. Il termine classe di entità può essere usato al posto di entità per un tipo
di cosa (ad esempio "persona") in contrapposizione a un esempio o una rappresentazione
specifica (ad esempio "Steve Kass", che rappresenta una persona specifica). Ogni
rappresentazione di un'entità può essere identificata in modo univoco, fatto che consente di
usare una relazione per rappresentare un'entità.
Ogni rappresentazione di un'entità svolge un ruolo importante nell'applicazione o nel
sistema in cui è rappresentata. Questo è il concetto di astrazione: in un database sono
disponibili solo classi di entità (e attributi di tali entità) che hanno un motivo per essere lì. Ogni
rappresentazione di un'entità può essere descritta tramite uno o più attributi. Le relazioni
sono associazioni tra entità. Una relazione è un subset dei prodotti incrociati dei set dell'entità
coinvolti nella relazione. Gli attributi forniscono delle informazioni sulle entità che sono di
interesse per l'applicazione.
Il paragrafo precedente definisce il significato di relazioni in termini di entità e dei rapporti tra
loro, come definito da Peter Chen nel suo scritto “The Entity-Relationship Model—Toward a
Unified View of Data", a cui fanno riferimento la maggior parte dei libri sulla modellazione dei
dati. L'approccio entità-relazione è quello più usato anche nella modellazione del database
relazionale (ricerca di entità, relazioni e relativi attributi). Tuttavia esiste un altro approccio
per capire cosa significa relazioni. In questo secondo approccio, considerato più naturale, le
relazioni vengono descritte in termini di proposizioni e predicati.
Nel capitolo 2 hai appreso la definizione di proposizioni e predicati. Cosa ha a che fare
questa definizione con una relazione? Nel linguaggio normale vengono fatte affermazioni
sulle entità di interesse tramite dichiarazioni del fatto, o, in logica, tramite le proposizioni. Di
seguito viene illustrato un esempio di proposizione: Il dipendente con l'ID numero 17 si chiama
Fernando, lavora nel reparto D1 ed è stato assunto il 19 luglio 2003. Le forme generalizzate
delle proposizioni sono i predicati. Di seguito viene illustrato un esempio di predicato: Il
dipendente con l'ID numero (Dip#) si chiama (Nome), lavora nel reparto (Rep#) ed è stato
assunto il (DataAssunzione). I quattro termini nelle parentesi sono segnaposto o parametri che
corrispondono ai quattro valori della proposizione precedente.
88
Microsoft Transact SQL Query
Quando sostituisci i parametri con valori specifici, il predicato si riduce a una singola
proposizione. Di seguito sono riportati i valori per i parametri che riducono il predicato
sopracitato nella proposizione precedente:
(17; Fernando; D1; 19 luglio 2003)
Puoi notare che i parametri formano una tupla. Nota che le tuple in una relazione
rappresentano di fatto le proposizioni. Come le tuple rappresentano le proposizioni, le
intestazioni della relazione rappresentano i predicati per tali proposizioni. Questo approccio
è molto vicino al linguaggio normale. Descrivi semplicemente un problema aziendale, trova i
predicati e scrivili, in questo modo ottieni il modello di dati. Naturalmente hai bisogno di uno
strumento che converta i predicati in relazioni. Questo approccio alla modellazione simile al
linguaggio normale viene definito modellazione ruolo-oggetto, ed è descritto in Information
Modeling and Relational Databases, 2nd edition di Terry Halpin e Tony Morgan (Morgan
Kaufmann, 2008) (informazioni in lingua inglese).
Ma questo non è un libro sulla modellazione. Quello che devi capire è il significato delle
relazioni. Puoi considerarle dei contenitori di entità del mondo reale o dei predicati e
proposizioni del linguaggio normale. Nota che per i predicati citati fino ad ora non esistono
vincoli sui valori di tupla che li rendono proposizioni, tranne il fatto che devono essere valori
dei tipi attributo. I vincoli verranno illustrati a breve; per ora verrà fornita una definizione
generica e informale del tipo di regola che viene applicata con i vincoli: una proposizione che
restituisce False per il predicato della relazione (intestazione) non può mai fare parte della
relazione.
Viste (e altre relazioni virtuali)
Le viste sono una parte importante del database relazionale. Un'altra parte importante delle
query in un'applicazione sono le relazioni temporanee (o set di righe nella terminologia di SQL
Server). Una vista è una relazione virtuale; di fatto è una query archiviata che viene valutata in
fase di esecuzione quando necessario. Un utente di database, uno sviluppatore di applicazioni
o un'applicazione non dovrebbe essere in grado di distinguere una vista da una tabella.
Questo principio è importante (principio di interscambiabilità) e afferma che non dovrebbe
esserci distinzione tra relazioni effettive (a volte chiamate di base) e relazioni virtuali. Questo
principio fornisce indipendenza logica dei dati in un database relazionale. Tale indipendenza
può aiutarti molto con due problemi: crescita e ristrutturazione. Se la tabella di un database
diventa troppo grande e causa scarse prestazioni, puoi suddividerla manualmente in più
tabelle nuove per poi riunirle in una vista il cui nome è il nome originale della tabella. Le nuove
tabelle possono anche trovarsi in database separati o in server separati. Se devi ristrutturare
una tabella e non puoi modificare un'applicazione che la utilizza, puoi creare una vista che
restituisce la struttura originale all'applicazione. Un'applicazione utilizza la vista senza sapere
che si tratta di una relazione virtuale. Tuttavia, le viste non possono fornire un'indipendenza
dei dati totale. Se non puoi nascondere a un'applicazione tutte le modifiche alla struttura di
una tabella con una vista, devi modificare anche l'applicazione. Ad esempio, potresti dover
aggiungere un attributo che deve essere inserito manualmente dagli utenti finali.
Capitolo 3
Il modello relazionale
89
Questo concetto di interscambiabilità può essere ulteriormente esteso alle espressioni di
tabella, query che restituiscono relazioni all'interno di query esterne. Probabilmente conosci
già le tabelle derivate e le espressioni di tabella comuni; apprenderai come utilizzarle in modo
efficace nel capitolo 6.
Convenzioni dei nomi
Le convenzioni dei nomi aiutano a creare strutture più intuitive e a scrivere codice più chiaro.
La scelta della convenzione non è importante quanto la scelta di una convenzione e il relativo
utilizzo in modo coerente. La scelta della convenzione è solo una questione di storia, gusto,
limiti del sistema e così via. I progettisti dei database tendono ad appassionarsi molto alle
convenzioni dei nomi.
Per il significato delle relazioni si consiglia l'approccio predicato-proposizioni. Ad esempio,
viene ripetuta la proposizione già citata: "Il dipendente con l'ID numero 17 si chiama
Fernando, lavora nel reparto D1 ed è stato assunto il 19 luglio 2003". Si consiglia di poter
sempre ricreare i predicati e le proposizioni. Una tupla che rappresenta questa proposizione
viene scritta in una relazione con solo valori, ad esempio (17, Fernando, D1, 19-07-2003). È
semplice ricreare questa proposizione se il relativo predicato, ossia la struttura di tabella, ha
dei nomi significativi per la tabella stessa e per le colonne, ad esempio Employees(EmployeeId,
EmployeeName, DepartmentId, HireDate). Tuttavia, se la tabella e le colonne avessero i
nomi Tabella1(colonna1, colonna2, colonna3, colonna4) dovresti essere in grado di leggere il
database. In questo modo è più semplice stabilire se il database risolve in modo appropriato
il problema aziendale e se i dati sono conformi alle regole aziendali. Inoltre è più semplice
per un nuovo programmatore familiarizzare con la struttura del database, e l'attività di
interscambio dei dati con altri sistemi viene resa molto semplice.
L'unica convenzione dei nomi che non viene consigliata per un database relazionale è quella
denominata notazione ungherese, in cui si usano i prefissi per denotare i tipi di oggetto.
La notazione ungherese usa nomi come tblEmployees per una tabella di dipendenti e
vwCustomerOrders per una vista di ordini dei clienti; tali nomi contraddicono il principio di
interscambiabilità che rappresenta il principio più importante del modello relazionale.
Il modello relazionale: riepilogo rapido
Il modello relazionale è indipendente dallo sfondo, ossia non dipende da un presupposto
specifico. Questo fatto verrà sottolineato più volte. Per prima cosa, il modello relazionale non
dipende dal tipo. Non esistono tipi "relazionali" prescritti o tipi "oltre il relazionale". Il modello
relazionale consente tutti i tipi. Infatti, è completamente valido per definire una relazione
con un singolo attributo di un tipo abbastanza complesso; si tratterebbe di una relazione
fortemente tipizzata. Tuttavia, i tipi forniti dal sistema sono in genere più semplici da utilizzare
perché gli sviluppatori del database conoscono già come usarli e, naturalmente, non devono
svilupparli da zero.
90
Microsoft Transact SQL Query
Per riepilogare, il modello relazionale è composto da:
■
Una raccolta infinita di tipi scalari
■
Un modo per definire i tipi; in altre parole un generatore di tipi
■
Un modo per definire i tipi di relazioni; in altre parole un generatore di tipi di relazioni
■
Un modo per generare variabili relazionali e assegnargli valori (set)
■
Algebra relazionale: una raccolta infinita di operatori relazionali
Le tabelle rappresentano le relazioni, e tutte le informazioni di un database relazionale
vengono archiviate nelle tabelle. Una relazione rappresenta un'entità del mondo reale. Inoltre,
le tuple di una relazione rappresentano le proposizioni, mentre un'intestazione di relazione
rappresenta un predicato.
Il modello relazionale non dipende dalle convezioni dei nomi, perché come già detto non
dipende dello sfondo. Ciò significa che dipende da te usare una convenzione dei nomi
abbastanza descrittiva da rendere possibile ricreare i predicati e le proposizioni del database.
Algebra relazionale e calcoli relazionali
Per manipolare le relazioni (le variabili relazionali) hai bisogno di alcuni operatori. Le relazioni
e gli operatori sulle relazioni formano ciò che viene chiamato algebra relazionale. La raccolta
di operatori relazionali è infinita, ma alcuni operatori sono considerati di base. Benché gli
operatori di base siano in qualche modo intuitivi, verranno illustrati per completezza.
Operatori di base
Come per i tipi semplici, sono necessari almeno due operatori di tipo relazionale: uno per
archiviare un set di tuple in una variabile relazionale e uno per recuperare il valore della
variabile. Ciò corrisponde alle nozioni familiari di assegnazione e valutazione. L'operatore
relazionale di selezione (corrispondente alla valutazione) restituisce una tabella da una
variabile relazionale, mentre l'operatore relazionale di assegnazione assegna un valore di
tabella a una variabile relazionale.
Ovviamente è necessario anche un insieme di operatori booleani di base per relazioni e tuple:
■
= (uguale)
■
≠ (non uguale)
■
⊆ (subset di)
■
⊇ (superset di)
■
∈ (elemento di)
■
=∅ (è vuoto)
Capitolo 3
Il modello relazionale
91
I primi quattro operatori elencati accettano due relazioni come parametri. Il quinto verifica
se una tupla è un membro della relazione, in altre parole accetta una tupla come parametro
di sinistra e una relazione come parametro di destra. Infine, l'ultimo operatore dell'elenco
accetta un'unica relazione come parametro e verifica se è vuota. Se lo desideri puoi definire
gli operatori di addizione per convenienza, ad esempio subset esatto di (ossia subset di e non
uguale a) e superset esatto di (superset di e non uguale a). Si desidera citare un altro operatore
specifico che aiuta molto con la presentazione tabulare di una relazione, l'operatore Order By
<attributo_1,attributo_2,. . .,attributo_n>.
L'operatore Order By non restituisce un risultato non ordinato; quindi non restituisce un set o
una relazione, che sono non ordinati. Puoi considerare il valore restituito dell'operatore Order
By come una tabella ordinata. L'ordinamento tuttavia non è predefinito per relazioni e tuple;
quindi il supporto dell'operatore Order By per una particolare relazione richiede che almeno
un attributo della relazione supporti l'ordinamento e i seguenti operatori:
■
> (maggiore di)
■
≥ (maggiore o uguale a)
■
≤ (minore o uguale a)
■
< (minore di)
La tabella restituita dall'operatore Order By viene ordinata in base ai valori di uno o più
attributi, che devono essere tutti del tipo di dati che supporta gli operatori elencati.
Algebra relazionale
L'algebra relazionale è una raccolta di operatori che accettano relazioni come parametri di
input e restituiscono relazioni. Il fatto che il risultato di ogni operazione relazionale sia una
relazione viene definito come proprietà di chiusura relazionale dell'algebra relazionale. Codd
originariamente ha definito otto operatori relazionali, quattro basati sugli operatori sui set
tradizionali e quattro su speciali operatori relazionali. Questi otto sono Restrict, Project,
Product, Union, Intersect, Minus, Join e Divide.
L'algebra relazionale non è chiusa; puoi definire ulteriori operatori, purché rispettino la
proprietà di chiusura relazionale. Saranno introdotti una serie di operatori utili oltre agli otto
originali di Codd. Naturalmente, dal momento che la raccolta di operatori relazionali è infinita,
l'elenco non sarà completo. Sono stati deliberatamente scelti gli operatori considerati più utili
e che vengono usati nel linguaggio Transact-SQL più avanti nel libro.
Gli otto operatori originali di Codd
L'operatore Restrict filtra le tuple di una relazione. Il risultato di questo operatore è una
relazione con un numero inferiore (o uguale) di tuple rispetto alla relazione originale. Il tipo
principale di relazione restituito è lo stesso del tipo principale della relazione originale. La
restrizione si basa su un'espressione booleana (denominata espressione di restrizione) che
confronta i valori degli attributi con valori letterali, variabili, altri attributi o espressioni.
92
Microsoft Transact SQL Query
La relazione di output dell'operatore Restrict contiene esattamente le tuple della relazione
originale per cui l'espressione di restrizione ha restituito True.
L'operatore Restrict filtra una relazione orizzontalmente, a differenza dell'operatore Project
che filtra una relazione verticalmente. L'operatore Project è molto più semplice: oltre a una
relazione, questo operatore accetta come input un elenco di attributi necessari per la relazione
risultante. Nota che la proiezione corretta dovrebbe includere solo tuple univoche, altrimenti il
risultato non sarebbe una relazione.
Tuttavia, RDMBS non applica questa regola perché è più pratico consentire un multiset (o un
contenitore) come risultato, per inviarlo direttamente a un'applicazione client o per archiviarlo
temporaneamente.
Nella figura 3-1 vengono illustrati graficamente gli operatori Restrict e Project. Immagina che
il rettangolo di destra che mostra l'operatore Project rappresenti la relazione Employees, con
gli attributi ID, Name, HireDate, DepartmentID e BirthDate. L'operatore Project restituisce una
relazione con ID, HireDate e DepartmentID come relativi attributi, che vengono indicati nella
figura dal colore più scuro.
Restrict
Project
FIGURA 3-1 Operatori Restrict e Project
L'operatore Product si basa sul prodotto cartesiano della matematica. Hai già appreso dal
capitolo 2 che il prodotto cartesiano di due set è un set di coppie ordinate (x,y), dove x deriva
dal primo set e y dal secondo. Tuttavia, nel modello relazionale, le tuple non sono ordinate e
l'operatore Product deve rispettare la proprietà di chiusura relazionale e restituire un set di
tuple non ordinate, non un set di coppie ordinate.
Quindi, nell'algebra relazionale, l'operatore Product viene generalizzato. Invece di restituire
coppie ordinate (x,y) di tuple (dove x è una tupla del primo input di Product e y una tupla
del secondo), l'operatore Product restituisce tuple che sono l'unione delle due tuple originali.
Unione in questo caso viene usato nel senso della teoria dei set; significa che la tupla finale ha
come relativi attributi l'unione degli attributi delle due tuple originali.
Unione significa naturalmente unione distinta, e quindi se un attributo compare in entrambe
le relazioni di input, viene mantenuta solo un'occorrenza nell'output dell'operatore Product.
Capitolo 3
Il modello relazionale
93
Cosa succede se le due relazioni originali includono un attributo con lo stesso nome e desideri
mantenere entrambi? Chiaramente, l'operatore Product non è completo; è necessario un
operatore che consenta la ridenominazione di un attributo. Tale operatore non fa parte
dell'algebra originale di Codd, quindi verrà introdotto dopo questa sezione che riguarda
invece gli otto operatori originali.
L'operatore relazionale Union si basa sull'operatore di set Union. Tuttavia, l'operatore
relazionale Union differisce nuovamente dalla controparte matematica per la proprietà di
chiusura relazionale dell'algebra relazionale.
Dal momento che il risultato deve essere una relazione e una relazione può avere tuple solo
di un tipo di intestazione, l'unione relazionale deve essere limitata a relazioni di input dello
stesso tipo o proiettare implicitamente ogni relazione di input negli attributi che sono comuni
a entrambe le relazioni di input.
Nella figura 3-2 vengono illustrati gli operatori Product e Union. Per l'operatore Union, una
proiezione di ognuna delle due relazioni viene usata per limitare l'unione solo agli attributi che
le relazioni hanno in comune. Il risultato di Union ha lo stesso tipo di intestazione per entrambi
gli input (o le relative proiezioni negli attributi comuni) e contiene tuple distinte.
Product
Union
a
x
a x
b
y
a y
c
b x
b y
c x
c y
FIGURA 3-2 Operatori Product e Union
L'operatore relazionale Intersect si basa, analogamente all'operatore relazionale Union,
sull'operatore sui set Intersect, e come Union ha la restrizione che gli operandi (relazioni) siano
dello stesso tipo o che venga preapplicata una proiezione implicita agli operandi. Il risultato
è un set di tuple distinte che compaiono in entrambe le relazioni di input (o nelle relative
proiezioni sugli attributi comuni).
Un altro operatore relazionale, Minus (o Difference), si basa sull'operatore equivalente della
teoria dei set, di nuovo con una proiezione nota per fare in modo che gli operandi abbiano lo
stesso tipo. Il risultato dell'operatore relazionale Minus è una relazione che include solo tuple
94
Microsoft Transact SQL Query
dell'operando di sinistra che non compaiono nell'operando di destra. Nella figura 3-3 vengono
illustrati gli operatori Intersect e Minus.
Intersect
Minus
FIGURA 3-3 Operatori Intersect e Minus
Esistono molte varietà dell'operatore relazionale Join, tuttavia la più importante è l'operatore
Natural Join, illustrato nella figura 3-4. Natural Join ha bisogno di due relazioni con almeno
un attributo in comune; il risultato è una relazione con tuple per cui gli attributi in comune
hanno valori uguali. Questi attributi in comune provengono solo da una delle relazioni unite in
join e dall'unione degli altri attributi di entrambe le relazioni. Union viene usato nuovamente
nel senso della teoria dei set, quindi un'unione di attributi distinti di relazioni originali. Come
l'operatore Product, l'operatore Join sarebbe molto più utile con un operatore che consentisse
la ridenominazione di un attributo. Come affermato, nella figura 3-4 viene illustrato l'operatore
Natural Join. Immagina che la relazione di input di sinistra sia la relazione Employees con
gli attributi numero ID dipendente e numero Department ID e che la relazione di input di
destra sia la relazione Departments con gli attributi numero Department ID e Department
Name. L'operatore Natural Join utilizza l'attributo comune numero Department ID per
trovare corrispondenza tra i dipendenti e i relativi reparti in base all'uguaglianza del numero
Department ID. Nota che nella relazione risultante il numero Department ID compare solo
una volta. Inoltre, il risultato contiene solo tuple che derivano da una corrispondenza basata
sui numeri Department ID di entrambe le relazioni di input. Infine, nota anche che un singolo
reparto (y2 nella figura 3-4) ha corrispondenza con più di un dipendente.
Non tutti i join sono naturali, e non tutti i join sono basati sull'operatore di uguaglianza.
I join generali (join che non usano necessariamente l'operatore di uguaglianza come
condizione di corrispondenza per le tuple) vengono definiti Θ (theta) join. Se l'operatore per
la corrispondenza delle tuple è l'operatore di uguaglianza, allora il join viene definito equi-join.
Un join naturale è semplicemente un caso speciale di equi-join.
Capitolo 3
Il modello relazionale
95
Natural Join
x1
y1
y1
z1
x1
y1
z1
x2
y2
y2
z2
x2
y2
z2
x3
y3
y3
z3
x3
y3
z3
y4
z4
FIGURA 3-4 Operatore Natural Join
Probabilmente l'operatore relazionale meno compreso è Divide. Una relazione divisore viene
utilizzata per dividere una relazione dividendo e generare una relazione quoziente. La relazione
quoziente è composta dai valori di una colonna della tabella dividendo per cui la seconda
colonna contiene tutti i valori nel divisore.
Benché questo sia un capitolo teorico, verrà utilizzato del codice per spiegare l'operatore
Divide e un problema che potresti incontrare se esegui la divisione con un set vuoto, quindi
un problema di divisione per zero. Verrà utilizzato un esempio che hai visto nel capitolo 2 e
che vedrai ancora nel capitolo 6. Il problema, che fa riferimento al database InsideTSQç2008,
chiede di restituire tutti i clienti per cui ogni dipendente degli USA ha gestito almeno un
ordine. In questo caso, dividi il set di tutti gli ordini per il set di tutti i dipendenti USA, per cui
ti aspetti di ottenere il set di clienti corrispondenti. T-SQL non ha un operatore Divide. Per
illustrarlo, il problema verrà riscritto come compare nel capitolo 6:
Restituire i clienti
per cui non puoi trovare
alcun dipendente
degli USA
per cui non puoi trovare
alcun ordine
inserito per il cliente
e dal dipendente
La query per questo problema è piuttosto intuitiva:
USE InsideTSQL2008;
SELECT custid FROM Sales.Customers AS C
WHERE NOT EXISTS
(SELECT * FROM HR.Employees AS E
WHERE country = N'USA'
AND NOT EXISTS
(SELECT * FROM Sales.Orders AS O
WHERE O.custid = C.custid
AND O.empid = E.empid));
Questa query restituisce 23 righe, quindi significa che ci sono 23 clienti per cui ogni
dipendente USA ha gestito almeno un ordine. La domanda verrà ora posta con un paese
diverso: quanti clienti ci sono per cui ogni dipendente di Israele abbia gestito almeno un
ordine?
96
Microsoft Transact SQL Query
Di seguito viene riportata la stessa query con un parametro modificato:
SELECT custid FROM Sales.Customers AS C
WHERE NOT EXISTS
(SELECT * FROM HR.Employees AS E
WHERE country = N'IL'
AND NOT EXISTS
(SELECT * FROM Sales.Orders AS O
WHERE O.custid = C.custid
AND O.empid = E.empid));
Questa query restituisce 91 righe, che rappresentano tutti i clienti. Questo non può
essere il risultato atteso, dal momento che non esistono dipendenti di Israele nella tabella
HR.Employees. Questo è il modo in cui l'operatore Divide è stato definito originariamente.
Dal momento che la tabella HR.Employees non ha dipendenti di Israele, la condizione che un
cliente sia stato servito da tutti i dipendenti di Israele è vera per ogni cliente. In altre parole,
ogni cliente è stato servito da ogni dipendente di Israele. Tuttavia, è vero anche qualcos'altro:
ogni cliente non è stato servito dai dipendenti di Israele. Nota che non c'è una verità migliore
in questo caso; quella che scegli dipende dal problema che stai risolvendo. In questo caso
si verifica qualcosa di simile al paradosso di Russell (citato nel capitolo 2)? Non proprio. Il
problema è che non è stata considerata la possibilità di non avere dipendenti di Israele. Se
la domanda originale "clienti . . . per cui . . . almeno un ordine" fosse intesa a significare che di
fatto ci fossero degli ordini, è possibile dare una risposta semplicemente aggiungendo una
condizione al predicato che richiede di restituire i clienti serviti da tutti i dipendenti di Israele,
se esiste almeno un dipendente di Israele:
Restituire clienti
per cui non puoi trovare
alcun dipendente
di Israele
per cui non puoi trovare
alcun ordine
inserito dal cliente
e dal dipendente
se esiste almeno un dipendente di Israele
La query ora è uguale alla seguente:
SELECT custid FROM Sales.Customers AS C
WHERE
NOT EXISTS
(SELECT * FROM HR.Employees AS E
WHERE country = N'IL'
AND NOT EXISTS
(SELECT * FROM Sales.Orders AS O
WHERE O.custid = C.custid
AND O.empid = E.empid))
AND EXISTS
(SELECT * FROM HR.Employees AS E
WHERE country = N'IL');
Capitolo 3
Il modello relazionale
97
Questa query restituisce zero righe, come ti saresti aspettato quando hai originariamente fatto
la domanda. La formula per l'operatore Divide include tre relazioni:
a Divide By b Per c,
dove a è il dividendo, b il divisore e c è la relazione mediatore. La relazione a ha gli attributi
A e la relazione b gli attributi B. L'operatore Divide restituisce una relazione che include tutte
le tuple del divisore così che una tupla {A, B} compaia nella relazione mediatore per tutte le
tuple della relazione divisore. Negli esempi mostrati, il dividendo è la relazione Customers, il
divisore è la relazione che include dipendenti di un paese specifico (USA o Israele negli esempi)
e il mediatore è la relazione Orders. Tuttavia, per evitare il problema della divisione per zero
è stata usata una quarta relazione temporanea (SELECT * FROM HR.Employees AS E WHERE
country = N’IL’). Puoi esprimere il predicato che richiede di restituire i clienti serviti da tutti
i dipendenti degli USA se esiste almeno un dipendente degli USA anche in un altro modo,
ossia cercando clienti diversi (rappresentati con custid) dagli ordini serviti dai dipendenti USA
avendo il numero di diversi dipendenti USA che hanno servito un cliente uguale al numero
totale di dipendenti degli USA (troverai questo esempio anche nel capitolo 6):
SELECT custid
FROM Sales.Orders
WHERE empid IN
(SELECT empid FROM HR.Employees
WHERE country = N'USA')
GROUP BY custid
HAVING COUNT(DISTINCT empid) =
(SELECT COUNT(*) FROM HR.Employees
WHERE country = N'USA');
Questa query restituisce il risultato per la seconda versione della divisione sia per i dipendenti
USA sia di Israele e inoltre è molto più corta. Per concludere con gli otto operatori originali di
algebra relazionale, nella figura 3-5 viene illustrato graficamente l'operatore Divide esteso (con
la relazione mediatore).
Divide
a
a x
x
b
a y
z
c
a z
a
b x
b z
FIGURA 3-5 Operatore Divide esteso
98
Microsoft Transact SQL Query
Altri operatori di algebra relazionale
Come già detto, l'algebra relazionale ha una serie infinita di operatori, ma qui ci si focalizzerà
su quelli più utili.
È già stato detto quanto sia utile l'operatore Rename. Senza, tutti gli operatori che accettano
più di una relazione come parametri sarebbero molto limitati. L'operatore Rename assegna un
alias a un attributo o a una relazione in una query. Nota che è molto pratico avere alias per le
relazioni oltre che per gli attributi, perché una singola query può fare riferimento alla stessa
relazione più di una volta.
Un linguaggio che supporta l'algebra relazionale viene detto completo dal punto di vista
relazionale; tuttavia, ciò non significa che sia anche completo dal punto di vista del calcolo. Non
è stato ancora introdotto un operatore che restituisce un attributo calcolato nella relazione
risultante. L'operatore Extend è l'operatore che aggiunge un'espressione denominata (che
restituisce un valore scalare) alla relazione risultante. Nota che questa espressione non è
limitata ai calcoli tra attributi solo di una singola tupla; l'espressione può lavorare anche su più
tuple se aggrega più valori di input a un singolo valore di output. Nella figura 3-6 vengono
illustrati gli operatori Rename ed Extend, con attributi con alias e aggiunti di colore più scuro.
Rename
Extend
A
B
A
C
x1
y1
x1
y1
x1
y2
x1
y2
x2
y1
x2
y1
FIGURA 3-6 Operatori Rename ed Extend
L'operatore Extend esegue calcoli orizzontali, o nel senso della tupla. È necessario un operatore
per i calcoli verticali, o nel senso dell'attributo. L'operatore che esegue i calcoli verticali è
Summarize (illustrato nella figura 3-7), che combina una proiezione sugli attributi su cui viene
fatto un calcolo verticale con un'estensione della relazione risultante per includere i calcoli di
aggregazione.
I semi join sono join che restituiscono tuple di una relazione in base all'esistenza di tuple
correlate in un'altra relazione. L'operatore di sinistra Semijoin (illustrato nella figura 3-8)
restituisce tuple della relazione di sinistra, mentre l'operatore Semijoin di destra restituisce le
tuple della relazione di destra.
Capitolo 3
Il modello relazionale
99
Summarize
x1
y1
z1
x1
f (z1,z2)
x1
y2
z2
x2
f (z3,z4)
x2
y1
z3
x2
y2
z4
FIGURA 3-7 Operatore Summarize
Semijoin
x1
y1
y1
z1
x1
y1
x2
y2
y2
z2
x2
y2
x3
y3
y3
z3
x3
y3
x4
y4
FIGURA 3-8 Operatore Semijoin (di sinistra)
La teoria dei grafi è una delle teorie più utili in matematica. È stata sviluppata da Leonhard
Euler mentre studiava un famoso problema storico di matematica denominato i sette ponti di
Königsberg. Di seguito viene riportata una breve descrizione del problema tratta da Wikipedia:
La città di Königsberg, (ora Kaliningrad) è percorsa dal fiume Pregel e da suoi
affluenti e presenta due estese isole che sono connesse tra di loro e con le due aree
principali della città da sette ponti.
La questione è se sia possibile con una passeggiata seguire un percorso che
attraversa ogni ponte una e una volta sola.
Nella teoria dei grafi un grafo è un insieme di elementi (denominati nodi o vertici) e
connessioni (denominate spigoli) tra coppie di elementi. I nodi sono elementi statici astratti,
mentre gli spigoli possono rappresentare associazioni o relazioni tra i nodi. Un sistema
stradale, ad esempio, può essere rappresentato con un grafo: le città sono i nodi, mentre le
strade sono gli spigoli. Gli alberi e le gerarchie sono casi speciali si grafi. In una relazione in
genere si modella un grafo con il modello elenco adiacenza. In questo modello si considerano
gli spigoli del grafo come spigoli diretti da un vertice all'altro, e tali spigoli vengono
rappresentati come tuple. I nodi connessi tramite uno spigolo (che può essere visto come
adiacente in virtù dello spigolo che li connette) sono rappresentati dagli attributi della tupla
spigolo. Vengono rappresentati solo i nodi con una connessione. Il problema con il modello
elenco adiacenza si presenta quando devi eseguire una query. Ad esempio, se devi trovare
tutti i percorsi possibili dalla città A alla città B nel sistema stradale, la query deve includere
qualche tipo di ciclo (il ciclo può essere nascosto in un'espressione di tabella ricorsiva comune,
ma resta sempre un ciclo). Per rendere queste query più veloci e semplici, si può usare un
nuovo operatore relazionale, TClose. Questo operatore unario restituisce la chiusura transitiva
100
Microsoft Transact SQL Query
della relazione originale. Il risultato è una relazione con lo stesso tipo principale della relazione
originale, ma include tuple di tutte le coppie di nodi con percorsi non interrotti tra loro.
L'esecuzione di query su una relazione risultante di questo tipo è molto più semplice. Nel
capitolo 12 apprenderai altre informazioni sui grafi, gli alberi, le gerarchie e anche su come
calcolare la chiusura transitiva di un grafo. Per ora osserva semplicemente la rappresentazione
grafica dell'operatore nella figura 3-9.
TClose
a
b
c
d
a c
a c
b c
b c
b d
b d
c e
c e
c f
c f
e f
e f
e
f
a e
b e
a f
b f
FIGURA 3-9 Operatore TClose (di sinistra)
Per completezza saranno illustrati altri due operatori ben conosciuti che hanno a che fare
con le relazioni con i dati temporali: Unpack e Pack. Anche se questo libro non tratta di
problemi temporali, molti libri lo fanno, ad esempio Inside Microsoft SQL Server 2008: T-SQL
Programming di Itzik Ben-gan e altri. (Microsoft Press, 2009).
Immagina che ogni tupla in una relazione abbia un attributo che rappresenta l'intervallo di
tempo per cui è valida una tupla. Pensa di avere un tipo intervallo di tempo nella raccolta dei
tipi, definito dal sistema o definito dall'utente. Una tupla con un simile intervallo di validità può
essere simile al seguente:
{A, d4:d6}
Senza definire esplicitamente l'intestazione di questa tupla, presumi che la proposizione qui
dica che il fornitore A sia sotto contratto (un fornitore valido) per il periodo che va dal punto
nel tempo d4 al punto nel tempo d6 e che i punti nel tempo siano distinti: d1, d2, d3 e così via,
come i giorni del calendario, ad esempio. Puoi avere anche altre tuple per lo stesso fornitore,
ad esempio:
{A, d5:d7}
{A, d8:d8}
Capitolo 3
Il modello relazionale
101
Qui le tre tuple per il fornitore A hanno intervalli di validità che si sovrappongono e sono
adiacenti. Come puoi trovare il numero di punti nel tempo distinti in cui il fornitore A era
sotto contratto? Come puoi combinare le tuple con intervalli adiacenti e sovrapposti in una
singola tupla che rappresenta che il fornitore A era sotto contratto in modo continuato per un
intervallo più lungo senza interruzioni?
Bisogna definire un operatore Unpack come operatore relazionale unario che restituisce una
relazione con tutti i punti nel tempo distinti e validi proiettati su un set di operatori di input,
nel modo in cui l'operatore Summarize proietta sugli attributi di input. Tuttavia, Unpack
fa l'opposto di Summarize in termini di tuple restituite; la relazione restituita viene estesa
per includere tuple per tutti i punti nel tempo validi e distinti. Nel caso delle proposizioni
dell'esempio, il solo attributo di input per cui i punti nel tempo possono essere decompressi è
il fornitore. L'operatore Pack esegue l'opposto: restituisce una relazione con attributi di input
per cui gli intervalli sono compressi e gli intervalli sono un'unione di tutti gli intervalli delle
tuple di origine per gli stessi attributi di input che si sovrappongono o sono adiacenti. Nota
che unione in questo caso non è l'operatore relazionale Union; è un'unione di intervalli, definita
solo per gli intervalli che si sovrappongono o sono adiacenti. Nella figura 3-10 vengono
illustrati graficamente gli operatori Unpack e Pack.
Unpack
Pack
a
d4 : d6
a
d4 : d4
a
d4 : d6
a
d4 : d8
a
d5 : d7
a
d5 : d5
a
d5 : d7
b
d2 : d4
a
d8 : d8
a
d6 : d6
a
d8 : d8
b
d7 : d7
b
d2 : d4
a
d7 : d7
b
d2 : d4
b
d7 : d7
a
d8 : d8
b
d7 : d7
b
d2 : d2
b
d3 : d3
b
d4 : d4
b
d7 : d7
FIGURA 3-10 Operatori Unpack e Pack
Operatori di algebra relazionale primitivi
Forse avrai già notato che molti degli operatori relazionali definiti fino ad ora possono essere
espressi con altri operatori relazionali. Infatti, la maggior parte degli operatori citati fino ad ora
sono solo collegamenti che rendono le espressioni relazionali più semplici e brevi.
102
Microsoft Transact SQL Query
Infatti, persino gli otto operatori originali di Codd non sono tutti primitivi; alcuni possono
essere espressi con altri. Il componente Query Optimizer RDBMS può utilizzare questo aspetto
nell'ottimizzazione di una query; può riscrivere una query al relativo equivalente logico usando
operatori diversi, che possono essere implementati con operatori fisici più veloci rispetto agli
operatori relazionali in un RDBMS specifico. Ad esempio, potresti notare che a volte SQL Server
utilizza l'operatore fisico Merge Join quando usi l'operatore logico (relazionale) Union.
Nota inoltre che gli operatori relazionali che si basano su operatori sui set sono diversi dagli
operatori sui set originali.
Calcolo relazionale
L'algebra relazionale fornisce una raccolta infinita di operatori relazionali che vengono utilizzati
per creare la relazione desiderata che risulta da una query; servono per stabilire un sistema per
ottenere la relazione risultante. L'algebra relazionale è prescrittiva. Il calcolo relazionale è un
modo alternativo per ottenere la relazione risultante desiderata da un sistema. Con il calcolo
relazionale descrivi la relazione risultante. Quindi il calcolo relazionale è descrittivo.
Come puoi descrivere la relazione risultante di cui hai bisogno? Ancora una volta usando i
predicati. Descrivi la relazione risultante con un predicato soggetto a vincoli. Ad esempio,
quando è stato descritto l'operatore relazionale Divide, è stato usato il calcolo relazionale per
introdurre il problema: restituire tutti i clienti per cui ogni dipendente degli USA abbia gestito
almeno un ordine. La descrizione più dettagliata è la seguente:
Restituire i clienti
per cui non puoi trovare
alcun dipendente
degli USA
per cui non puoi trovare
alcun ordine
inserito per il cliente
e dal dipendente
Esistono due tipi di calcolo relazionale: calcolo di tuple e calcolo di dominio. Nel calcolo di tuple
specifichi il risultato di una query descrivendo le condizioni di appartenenza alla tupla per la
relazione risultante. Nel calcolo di dominio specifichi la relazione risultante limitando i domini
di attributi. Benché esista una netta differenza matematica tra calcolo di tuple e calcolo di
dominio, per lo scopo di questo libro tale differenza può essere considerata una sfumatura. La
differenza era importante in passato perché diversi linguaggi, seri competitori di SQL, si sono
evoluti basandosi sul calcolo di tuple e dominio. Per il calcolo di tuple è stato sviluppato QUEL
(Query Language), mentre il calcolo di dominio era supportato da QBE (Query by Example).
Per spiegare la differenza tra l'algebra relazionale e il calcolo relazionale, è opportuno usare
un esempio. Immagina due relazioni: Customers con attributi CustomerId, CustomerName e
City, e Orders con attributi OrderId, CustomerId e OrderDate. La query che stai risolvendo è
"Ottieni i valori di attributo CustomerId e CustomerName dei diversi clienti di Parigi che hanno
inserito almeno un ordine". Una formula algebrica e prescrittiva della query potrebbe essere la
seguente:
Capitolo 3
Il modello relazionale
103
1. Unire in join Customers e Orders tramite CustomerId.
2. Limitare il risultato delle tuple alla città di Parigi.
3. Riepilogare il risultato tramite CustomerId e CustomerName per ottenere i clienti
distinti.
4. Proiettare il risultato tramite CustomerId e CustomerName.
Una formula di calcolo descrittiva della query potrebbe essere la seguente:
Restituire CustomerId e CustomerName per i clienti di Parigi per cui esiste qualche ordine.
La descrizione del risultato della query è molto simile nel calcolo di tuple e di dominio. In
entrambi i casi è inclusa una descrizione dell'intestazione risultante (denominata anche prototupla) e una descrizione dei vincoli in termini di predicato che usa un'espressione quantificata.
Nell'esempio, CustomerId e CustomerName definiscono la proto-tupla, l'intestazione della
relazione risultante. Il predicato nell'esempio usa l'istruzione quantificata dal punto di
vista dell'esistenza "clienti di Parigi per cui esiste qualche ordine". La parola esiste indica la
quantificazione. Crei il predicato combinando espressioni logiche usando gli operatori logici
standard ¬ (Not), ∧ (And) e ∨ (Or). Inoltre, le espressioni quantificate sono necessarie per il
calcolo relazionale. Quindi il quantificatore di esistenza ∃ (Exists) e il quantificatore universale
∀ (For all) sono una parte indispensabile del calcolo relazionale.
SQL consente di esprimere il risultato desiderato di una query quasi in un linguaggio umano.
Supporta sia gli operatori logici che i quantificatori. Itzik ha affermato più volte in questo libro
che alcuni problemi sono più semplici da risolvere quando vengono rifrasati con un predicato
diverso o vengono analizzati usando la logica inversa. Ora puoi notare che questo spesso
significa che di fatto usi il calcolo relazionale.
Il calcolo relazionale e l'algebra relazionale sono equivalenti; hanno entrambi la stessa
espressività. Quindi dipende da te scegliere il modo migliore per esprimere la relazione
risultante desiderata; esprimere una query (usando l'algebra relazionale o il calcolo relazionale)
e capire il significato di una relazione (entità o predicato e proposizioni) sono simili.
Supporto di T-SQL
È stato detto che SQL non è l'unico linguaggio usato per la manipolazione delle relazioni.
In effetti il modello relazionale non dipende dal linguaggio; questo è un altro aspetto di
indipendenza di base del modello relazionale. SQL è solo uno dei linguaggi possibili. Tuttavia,
c'è uno standard ANSI esistente per SQL. E anche se non è perfetto, SQL è il linguaggio più
usato comunemente per la manipolazione delle relazioni. Transact-SQL (T-SQL) è il dialetto
dello standard SQL di SQL Server.
T-SQL supporta la maggior parte degli operatori dell'algebra relazionale. Puoi manipolare le
relazioni con istruzioni DML (Data Manipulation Language), quindi SELECT, INSERT, UPDATE,
DELETE e MERGE. L'operatore Product viene espresso con CROSS JOIN. L'operatore Restrict
viene supportato nelle clausole WHERE e HAVING e implicitamente nella clausola ON di
104
Microsoft Transact SQL Query
un'operazione JOIN se il join non è un CROSS JOIN, in quanto gli altri join filtrano il risultato
di un CROSS JOIN. L'operatore Project viene supportato nella parte SELECT di una query, in
cui elenchi gli attributi in modo esplicito. Gli operatori relazionali Union, Intersect e Minus
hanno le relative controparti negli operatori T-SQL UNION, INTERSECT ed EXCEPT. Tutti i tipi
di operatori Join, theta join, equi-join, semi-join e natural join, sono supportati dall'operatore
JOIN. L'operatore Rename viene espresso in T-SQL con la clausola AS, che può comparire
nell'elenco SELECT di una query per la ridenominazione degli attributi e nella parte FROM di
una query per la ridenominazione delle relazioni. L'operatore Extend viene espresso nell'elenco
SELECT, che può includere espressioni di calcolo denominate oltre agli attributi originali.
L'operatore Summarize diventa in T-SQL la clausola GROUP BY. Gli operatori relazionali Divide,
TClose, Unpack e Pack non hanno operatori T-SQL direttamente equivalenti.
Il calcolo relazionale viene supportato tramite la parte SELECT di una query, in cui descrivi la
proto-tupla, e nelle clausole WHERE e HAVING, in cui vincoli la relazione risultante con un
predicato. Naturalmente, T-SQL supporta tutti gli operatori logici standard: ¬ (Not), ∧ (And)
e ∨ (Or) e sia il quantificatore di esistenza ∃ (Exists) sia il quantificatore universale ∀ (For all)
nelle espressioni che vincolano la relazione risultante.
Dopo tutte queste informazioni si può dire che T-SQL è completo dal punto di vista
relazionale.
Integrità dei dati
È già stato detto che l'integrità dei dati è fondamentale per un database relazionale. Di fatto,
le regole di integrità dei dati sono una parte importante di un database relazionale. Un RDBMS
deve applicare le regole. Rendendo le regole parte del database, informi il sistema di quali
regole si tratta. Con i vincoli dichiarativi, il modo in cui vengono applicate dipende dal sistema;
con il codice procedurale devi definire il modo in cui implementarle. In entrambi i casi i vincoli
vengono espressi in termini di predicati.
Le intestazioni delle relazioni, tabella fisica e definizioni di vista che includono definizioni del
tipo di attributo, insieme ai vincoli dichiarativi e procedurali, costituiscono lo schema di un
database. Ora è possibile riepilogare esattamente cos'è uno schema di database. Uno schema
di database rappresenta predicati soggetti a vincoli che descrivono uno scenario aziendale.
Puoi ottenere i predicati soggetti a vincoli dalle intestazioni delle relazioni e dai vincoli definiti
nel database. Un predicato di database può essere definito come un'aggregazione di tutti i
predicati di relazione e di vincolo. Le regole di integrità dei dati possono essere espresse con
una singola regola: nel database non deve mai esserci un valore che viola il relativo predicato
soggetto a vincoli.
I vincoli possono essere classificati in vincoli base che definiscono l'integrità di entità, di
dominio e referenziale e le regole aziendali. Le regole di integrità di base si possono esprimere
con vincoli dichiarativi. La maggior parte delle regole aziendali hanno bisogno di codice di
programmazione in SQL Server. Le regole aziendali possono essere di qualunque tipo, ad
esempio regole di cardinalità o di frequenza (quante tuple possono esistere in una relazione in
un qualunque momento), regole di derivazione dei dati (come calcolare lo stato degli eventi),
Capitolo 3
Il modello relazionale
105
regole di subset (una relazione può avere un subset di tuple solo di un'altra relazione), regole
di inclusione (un periodo in cui un fornitore ha fornito un prodotto deve essere incluso in un
periodo in cui il fornitore aveva un contratto valido), regole di processo (quale evento deve
verificarsi prima) e altre ancora. Dipende dal creatore del database e dell'applicazione decidere
dove implementare le regole. Si consiglia di avere almeno i vincoli dichiarativi nel database
relazionale. Dopo tutto, se non li usi, perché usare un RDBMS?
I vincoli possono essere classificati anche in altri modi. Ad esempio, possono essere classificati
in base al tipo di oggetto che vincolano: vincoli di tipo, attributo, relazione e database.
Possono inoltre essere classificati come immediati o posticipati, in base a quando vengono
applicati: immediatamente o alla fine della transazione corrente. Nota che in base alla regola
che "in un database non deve mai esserci un valore che viola il relativo predicato soggetto a
vincoli", all'interno di un database relazionale dovrebbero funzionare solo i vincoli immediati.
Ciò significa che i vincoli devono essere applicati al limite di un'unica istruzione DML, non
alla fine di una transazione o anche dopo. In un RDBMS una singola istruzione DML viene
trattata come un'operazione atomica anche se modifica più righe; quindi, durante l'esecuzione
dell'istruzione ottieni righe che violano alcuni vincoli, ma mai dopo che è terminata l'istruzione.
Nota che i vincoli immediati non possono garantire che un database rifletta sempre uno stato
valido degli affari nell'ambiente reale. Ad esempio, anche se il trasferimento di denaro da un
account a un altro viene inteso come operazione atomica, include due aggiornamenti in un
database. Entrambi gli aggiornamenti devono terminare correttamente, altrimenti non ne
viene eseguito nessuno. Quindi, è chiaramente necessario qualche altro mezzo per rendere
i database sempre coerenti con il mondo reale. Ciò può essere ottenuto con le transazioni.
Una transazione è un'unità logica di lavoro che estende una nozione di atomicità a livello di
istruzione. Benché le transazioni svolgano un ruolo importante in un RDBMS, non verranno
illustrate nel dettaglio; per ulteriori informazioni in merito, leggi Inside Microsoft SQL Server
2008: T-SQL Programming.
SQL standard di ANSI consente i vincoli posticipati. SQL Server non li implementa. Tuttavia,
possono essere implementati nel codice procedurale per verifiche e ricerche avanzate di dati
errati. La correttezza è un termine più rigido di coerenza; un RDBMS può applicare la coerenza
dei dati ma non la correttezza. La coerenza significa che i dati rispettano le regole aziendali
dichiarate e note al sistema; la correttezza viene definita al di fuori del sistema dagli utenti del
sistema stesso.
Vincoli dichiarativi
Dal momento che i vincoli dichiarativi rappresentano il modo più importante per
implementare le regole aziendali in un database relazionale, verranno illustrati più nel
dettaglio rispetto agli altri vincoli.
Integrità dell'entità
Le tabelle di un database sono rappresentazioni fisiche delle relazioni, mentre le righe di una
tabella rappresentano le tuple; le relazioni sono composte da tuple univoche.
Questo è l'integrità dell'entità, identificazione univoca delle righe di una tabella.
106
Microsoft Transact SQL Query
Devi avere una combinazione di colonne (che rappresentano fisicamente gli attributi) che
identificano in modo univoco una riga. Il set di colonne minimo che identifica in modo univoco
ogni riga viene chiamato chiave. Ogni tabella può avere più combinazioni di colonne univoche,
in altre parole più chiavi candidate. Sei tu che devi selezionarne una come riferimento principale
per ogni riga e chiamarla chiave primaria. SQL Server ha due vincoli per l'integrità dell'entità: il
vincolo Unique per le chiavi candidate e il vincolo Primary Key per le chiavi primarie. Puoi avere
più vincoli Unique e un vincolo Primary Key per tabella.
Come sai ogni tabella deve avere una chiave. Sai inoltre che SQL Server non applica questa
regola: puoi creare una tabella senza un vincolo Primary Key o Unique. Il motivo è puramente
pratico. Immagina di dover importare i dati da un file di testo. Se avessi una chiave definita,
dovresti pulire i dati nel file di testo prima dell'importazione. Pulire i file di testo è meno
pratico che pulire i dati in una tabella SQL Server. Nonostante ciò, nell'ambiente di produzione,
tutte le tabelle devono avere una chiave definita per ogni tabella.
Ogni chiave ha due proprietà richieste e due desiderate (D. Sarka, 2008). Univocità e
applicabilità sono richieste; stabilità e minimalità sono desiderate. Univocità significa che la
chiave identifica ogni tupla in modo univoco. Applicabilità significa che la chiave deve essere
applicabile per tutte le tuple della relazione, deve essere nota e non deve essere composta
da attributi che non sono significativi per alcune tuple (consulta la sezione "Generalizzazione
e specializzazione" più avanti in questo capitolo). Stabilità significa che la chiave non deve
cambiare, se possibile. Minimalità significa che la chiave deve essere composta dal minor
numero possibile di colonne e dal minor numero possibile di byte. Nonostante ciò, a causa
dei problemi fisici, dovresti cercare chiavi con tutte e quattro le proprietà. Per verificare i
cambiamenti di un entità nel tempo, ad esempio negli scenari di data warehouse, la stabilità
diventa una proprietà necessaria. Mentre le chiavi minime forniscono prestazioni migliori.
Esiste un antico dibattito sulle chiavi e su quale siano le migliori: naturali o surrogate. Una
chiave naturale è un subset degli attributi che definiscono un'entità. Una chiave surrogata è
una chiave creata dal progettista e aggiunta agli attributi di un'entità; in genere è un numero
sequenziale. Non è possibile fare una netta distinzione tra chiavi naturali e surrogate. Un
Social Security ID (SSID) è una chiave naturale o surrogata? Qualcuno potrebbe aggiungerla
agli attributi dell'entità Person. Per dare una definizione di chiave naturale si potrebbe dire
che una chiave è naturale se l'attributo che rappresenta viene usato per l'identificazione
indipendentemente dal database. Se hai qualcosa di univoco, applicabile, stabile e breve nella
tabella, usalo. In caso contrario, aggiungi un numero sequenziale per il riferimento principale,
così da avere tutte le proprietà richieste e desiderate per la chiave primaria..
Se una chiave è applicabile, i relativi valori devono essere noti. SQL Server impone questa
regola proibendo alle colonne che consentono valori NULL la partecipazione nei vincoli
Primary Key; tuttavia, consente le colonne con valori NULL nei vincoli Unique. Più avanti in
questo capitolo verranno nuovamente ripresti i valori NUll, che rappresentano il segnaposto
per qualcosa di sconosciuto. Per ora si consiglia semplicemente di non usare colonne che
ammettono valori NULL per le chiavi.
Capitolo 3
Il modello relazionale
107
Integrità referenziale
Una chiave esterna è un set di colonne i cui valori corrispondono a qualche chiave di un'altra
tabella, in altre parole, una copia di una chiave di un'altra relazione. Le chiavi esterne denotano
le associazioni tra le relazioni, rappresentano il collante che tiene insieme le relazioni in un
database. L'applicazione della regole delle chiavi esterne può essere espressa brevemente:
In un database non devono mai esserci chiavi esterne senza corrispondenza. Le chiavi esterne
mantengono i riferimenti tra le relazioni, in altre parole applicano l'integrità referenziale.
La regola delle chiavi esterne può essere mantenuta durante le operazioni di aggiornamento
ed eliminazione in diversi modi. In SQL Server esistono quattro possibilità per applicare la
regole delle chiavi esterne, e ogni possibilità è composta da due coppie di regole. Una coppia
di regole riguarda la tabella principale (padre), l'altra coppia riguarda la tabella secondaria
(figlio). La coppia di regole per la tabella figlio è immutabile; le regole sono sempre le stesse
per tutte e quattro le possibilità di implementazione di una chiave esterna:
■
Non puoi inserire una riga nella tabella figlio se non ha una riga correlata nella tabella
padre.
■
Non puoi aggiornare le colonne della chiave esterna nella tabella figlio in un modo
che le lascerebbe senza una riga correlata nella tabella padre.
Le due regole per la tabella padre sono diverse per ognuna delle quattro possibili
implementazioni. Le quattro possibilità standard e l'implementazione delle due regole per la
tabella padre sono:
■
■
■
■
Implementazione di No action
❏
Non puoi eliminare una riga nella tabella padre se ha righe correlate nella
tabella figlio.
❏
Non puoi aggiornare le colonne chiave nella tabella padre se hanno righe
correlate nella tabella figlio che diventerebbero orfane.
Implementazione di Cascade
❏
Se elimini una riga nella tabella padre, devi eliminare tutte le righe correlate
nella tabella figlio.
❏
Se aggiorni la chiave primaria nella tabella padre, devi aggiornare le chiave
esterna in tutte le tabelle figlio correlate con lo stesso nuovo valore.
Implementazione di Set Null
❏
Se elimini una riga nella tabella padre, devi impostare su sconosciute (NULL)
tutte le chiavi esterne delle righe correlate nella tabella figlio.
❏
Se aggiorni una chiave primaria nella tabella padre, devi impostare su
sconosciute (NULL) tutte le chiavi esterne delle righe correlate nella tabella figlio.
Implementazione di Set Default
❏
Se elimini una riga nella tabella padre, devi impostare a un valore predefinito
tutte le chiavi esterne delle righe correlate nella tabella figlio.
108
Microsoft Transact SQL Query
❏
Se aggiorni una chiave primaria nella tabella padre, devi impostare a un valore
predefinito tutte le chiavi esterne delle righe correlate nella tabella figlio.
In breve, qualunque cosa tu faccia, non lasciare mai orfane le righe nella tabella figlio.
Normalmente usi l'implementazione No Action. L'implementazione Cascade la useresti per le
eliminazioni solo nel caso in cui volessi implementare una forte relazione tra la tabella padre e
la tabella figlio. In una relazione simile le righe della tabella figlio non avrebbero senso senza le
righe padre. Un esempio classico sono gli elementi ordini e riga ordine: gli elementi riga ordine
non possono esistere senza un ordine. Se elimini un ordine, devi eliminare anche tutti i relativi
elementi riga. Non si consiglia l'uso di Cascade per gli aggiornamenti. Cascade updates indica
che la chiave nella tabella padre non è stabile, e la stabilità è una delle proprietà desiderate per
una chiave. Le regole Set Null e Set Default sono utili per mantenere la cronologia della tabella
figlio; ad esempio, un ordine con un cliente sconosciuto fornisce informazioni su qualcosa che
è stato ordinato e quando è stato ordinato, ma non da chi. Nonostante ciò, oggi la cronologia
viene normalmente mantenuta in un data warehouse, quindi solitamente non sono necessarie
queste regole.
Un vincolo di chiave esterna deve fare riferimento a una chiave in una tabella padre. La tabella
padre può essere uguale alla tabella figlio; una chiave esterna può fare riferimento alla tabella
stessa. In questo modo puoi rappresentare grafi, alberi e gerarchie usando il modello elenco di
adiacenza.
Integrità del dominio
L'integrità del dominio limita il dominio dei possibili valori di un attributo. Naturalmente il tipo
di attributo già vincola i possibili valori di un attributo. Un altro modo standard per limitare il
dominio di un attributo in un database relazionale è il vincolo check.
Un vincolo check è un'espressione logica che restituisce true, false o unknown, è un altro
predicato. Un RDBMS lo applica ogni volta che una tupla viene inserita o aggiornata. I
valori di attributo della tupla sostituiscono i parametri del predicato, rendendo il predicato
una proposizione. Una tupla viene rifiutata se la proposizione restituisce false. La sintassi di
un'espressione vincolo check è simile alla sintassi delle espressioni in una clausola WHERE.
I vincoli check possono essere semplici come il controllo di un intervallo di valori. Tuttavia,
cosa puoi fare quando non conosci in anticipo l'intervallo consentito, quando devi mantenere i
valori nell'intervallo consentito in modo dinamico? Cosa fai quando l'elenco dei valori possibili
è molto lungo o addirittura infinito? Un'espressione vincolo check sarebbe composta da un
elenco enorme di valori collegati con operatori logici OR, inoltre dovresti modificare il vincolo
ogni volta che l'elenco dei valori possibili cambia. In tal caso, è più semplice utilizzare le tabelle
di ricerca. Colleghi l'attributo o gli attributi che stai vincolando a una tabella di ricerca con una
chiave esterna. Quindi, i vincoli di chiave esterna possono servire anche come meccanismi di
integrità del dominio.
Tutti i vincoli citati, chiavi, chiavi esterne e vincoli check, svolgono un ruolo importante
nell'ottimizzazione delle query. Forniscono informazioni a un RDBMS, e questo aiuta a
trovare un piano di esecuzione ottimale. Le chiavi forniscono informazioni che stai cercando
per un singolo valore; tale valore è univoco. Quindi la ricerca è molto ristretta, e il sistema
Capitolo 3
Il modello relazionale
109
può usare una ricerca indicizzata. Le chiavi esterne forniscono informazioni sull'esistenza di
una riga padre, aiutando a trovare l'algoritmo di join più efficace. I vincoli check forniscono
informazioni sull'intervallo, quindi (ad esempio) la ricerca di un valore che si trova all'esterno
dell'intervallo restituisce zero righe, e il sistema non deve neanche leggere i dati per restituire
il set di risultati corretto. Apprenderai ulteriori informazioni sull'ottimizzazione delle query nel
capitolo 4.
Altri strumenti per l'applicazione dell'integrità
Come è già stato detto, i vincoli espliciti non sono i soli strumenti per applicare l'integrità
dei dati. Anche i tipi di dati sono dei vincoli, che usano vincoli definiti dal tipo e set di
operazioni consentite. Un attributo è un vincolo con il relativo tipo di dati. Puoi inoltre
definire se la colonna di una tabella consente valori NULL. Infine, anche le definizioni delle
tabelle rappresentano dei vincoli: se non hai un punto in cui inserire un valore, non puoi
inserirlo. Questo aspetto verrà illustrato meglio nella sezione di questo capitolo relativa alla
normalizzazione.
Non puoi implementare tutte le regole aziendali usando strumenti dichiarativi. Alcuni vincoli
sono troppo complessi, mentre altri superano il limite del database. Una chiave esterna, ad
esempio, è limitata all'associazione delle tabelle solo nello stesso database. Alcuni vincoli
devono essere implementati in modo programmatico. Puoi inserire codice vincolante in
un'applicazione client, in un livello intermedio, nel livello di accesso ai dati, nelle stored
procedure di un database o in un qualunque altro punto in cui esiste codice. Tuttavia, se
desideri che il RDBMS applichi automaticamente vincoli complessi, devi usare i trigger.
I trigger sono speciali stored procedure eseguite, o avviate, automaticamente da un RDBMS.
Puoi usare trigger DML (Data Modification Language) per applicare le regole di modifica
dei dati e trigger DDL (Data Definition Language) per applicare le regole di modifica dello
schema. I trigger possono essere attivati primo o dopo l'istruzione che sta modificando lo
stato del database. SQL Server 2008 supporta due tipi di trigger DML, INSTEAD OF e AFTER,
e solo un tipo di trigger DDL, AFTER. I trigger INSTEAD OF di fatto non sono trigger BEFORE
dello standard ANSI, vengono attivati prima dell'istruzione, ma intercettano anche l'istruzione,
quindi puoi poi fare quello che desideri nel corpo del trigger. Se desideri che l'istruzione venga
eseguita, devi scriverlo esplicitamente nel corpo del trigger.
In teoria, devi sempre essere in grado di usare una vista invece di una relazione di base.
Tuttavia, non tutte le viste sono aggiornabili. Ad esempio, una vista può riepilogare alcuni
attributi di una tabella base; un RDBMS non sa come distribuire un valore da una singola riga
di una vista a più righe base. I trigger INSTEAD OF sono pensati specificatamente per rendere
aggiornabili le viste.
SQL Server 2008 offre anche un tipo di sistema XML incorporato. Il tipo XML applica alcune
regole di integrità: consente solo XML corretti. Inoltre puoi convalidare valori XML con uno
schema predefinito di una raccolta di schemi creata all'interno del database SQL Server.
Descrizioni dettagliate sui trigger e le convalide XML vanno oltre lo scopo del libro; per
ulteriori informazioni leggi Inside Microsoft SQL Server 2008: T-SQL Programming.
110
Microsoft Transact SQL Query
Puoi inoltre usare alcuni elementi di un database che non applicano di fatto l'integrità dei dati,
ma aiutano gli utenti a inserire valori corretti. Le impostazioni predefinite possono aiutare a
inserire un valore quando non è elencato esplicitamente nell'istruzione SELECT. SQL Server
2008 ha inoltre un tipo Timestamp; SQL Server inserisce e aggiorna i valori di questo tipo
automaticamente, inoltre garantisce che i valori nelle colonne di questo tipo siano univoche
nel database. La proprietà IDENTITY di una colonna può aiutare a inserire numeri sequenziali.
Una cosa importante da sapere è l'ordine in cui il sistema applica i vincoli. Probabilmente avrai
notato che si è passati da una discussione relativa all'implementazione generale (e teorica)
all'implementazione specifica di SQL Server 2008. I dettagli dei vincoli sono specifici del
sistema, quindi sembra più appropriato parlare del sistema illustrato in questo libro, Microsoft
SQL Server 2008. Quindi, l'ordine di esecuzione in SQL Server è il seguente:
1. Viene verificato lo schema (se un aggiornamento è valido per lo schema di tabella).
2. Vengono controllati i tipi di dati.
3. Vengono attivati i trigger INSTEAD OF invece dell'istruzione effettiva.
4. Vengono applicati i vincoli Default.
5. Viene verificato il supporto dei valori NULL.
6. Vengono verificati i vincoli Primary Key e Unique.
7. Vengono applicati i vincoli Foreign Key e Check.
8. Viene eseguita l'istruzione.
9. Vengono attivati i trigger AFTER.
Questo ordine ti dice che i vincoli dichiarativi vengono applicati prima dell'istruzione effettiva,
inoltre impediscono aggiornamenti errati, mentre i trigger AFTER vengono attivati dopo
l'istruzione, e devi eseguire il rollback di una modifica errata scoperta dal trigger AFTER
dell'istruzione. Ciò significa che l'uso di vincoli dichiarativi è più efficace dell'uso dei trigger
AFTER, quindi devi optare per l'uso di tali vincoli ogni volta che è possibile. Non dimenticare
un altro vantaggio nell'uso dei vincoli dichiarativi: possono essere utili nell'ottimizzazione delle
query.
Il Buono, il Cattivo e. . . lo sconosciuto!
L'ultima questione che verrà trattata in merito all'integrità dei dati è se sia necessario
consentire i valori NULL nel database. In un mondo ideale il database dovrebbe solo
rappresentare proposizioni vere; se qualcosa è NULL e non sai che significa NULL, non puoi
dire che sia vera. Quindi, da un certo punto di vista, non dovresti consentire i valori NULL.
Tuttavia, nel mondo reale manca sempre qualche informazione, almeno temporaneamente.
Inoltre, puoi sperimentare il paradosso di Russell, illustrato nel capitolo 2. Oltre alla descrizione
teorica, si desidera fornire un esempio trovato in Fermat’s Last Theorem di Simon Singh
(HarperPerennial, 2005) (informazioni in lingua inglese), che mostra il paradosso di Russell
nella vita reale. Si tratta del problema del libraio meticoloso.
Capitolo 3
Il modello relazionale
111
Questo libraio ha due tipi di cataloghi (di qualunque argomento); alcuni elencano se stessi
nei riferimenti, altri no. Il libraio vuole creare due nuovi cataloghi: uno che elenca tutti i
cataloghi che elencano se stessi e uno che elenca tutti i cataloghi che non elencano se stessi. Il
problema si presenta con il secondo catalogo: dovrebbe elencarsi? Se si elenca, per definizione
non dovrebbe essere elencato. Se non si elenca, per definizione dovrebbe essere elencato.
Immagina di dover inserire questi due cataloghi in un database, e in una tabella che descrive
i cataloghi hai un attributo rappresentato da una bandiera che mostra se il catalogo elenca se
stesso. Cosa inseriresti in questo attributo per il catalogo che elenca tutti i cataloghi che non
elencano se stessi? NULL potrebbe andare bene, in quando mostra che non puoi avere nulla di
significativo in questo caso.
Naturalmente, nella vita reale incontrerai informazioni mancanti per le mille ragioni diverse
dal paradosso di Russell. Nonostante ciò, devi trovare un modo per risolvere il problema delle
informazioni mancanti. Lo standard ANSI prescrive e SQL Server implementa i valori NULL per
denotare i valori mancanti. Nota che NULL non è un valore, è solo un indicatore. NULL non ha
il privilegio di essere uguale a se stesso. Alcuni autori (Date, Pascal) proibiscono severamente
i valori NULL, altri li consentono esplicitamente (Codd), mentre altri (Halpin) non li discutono,
mostrano solo come modellarli e usarli. Cosa fare?
Se i valori NULL non sono consentiti, devi implementare dei valori speciali per contrassegnare
le informazioni mancanti. Il vantaggio di questo approccio è che puoi usare gli operatori
booleani standard nelle query, quindi non saranno necessari operatori speciali che gestiscono i
valori NULL. Lo svantaggio è che non esiste un valore singolo, standard e speciale, accettato in
tutto il mondo. Inoltre un singolo valore speciale non sarebbe sufficiente; di fatto ne sarebbe
necessario uno per ogni tipo di dati. Usare valori NULL significa usare uno standard che sia già
accettato; tuttavia significa anche introdurre la logica a tre valori, dove not true non è uguale a
false. La logica a tre valori rende le query più complesse.
Dopo aver considerato i pro e i contro, la conclusione è che i valori NULL esistono per un
motivo, inoltre vengono implementati da tutti i maggiori RDBMS, quindi si preferisce utilizzarli
invece di inventare valori speciali. Nel corso del libro verranno fornite ulteriori informazioni
sulla scrittura di query efficienti con logica a tre valori. Nonostante ciò, è possibile evitare
alcuni valori NULL, ad esempio quelli che esistono perché un attributo non è applicabile per
una particolare tupla di una relazione. In questo caso è un problema di progettazione. Un
buono schema vincola, o esclude, i valori NULL che rappresentano "non applicabile". È quindi
giunto il momento di definire uno schema buono.
Normalizzazione e altri argomenti di progettazione
Per prima cosa bisogna chiarire che questo non è un libro sulla modellazione; si tratta di
una guida pratica con qualche capitolo introduttivo che spiega la teoria che sta dietro la
pratica. La teoria aiuta a capire perché in SQL Server vengono implementate alcune cose in
un determinato modo. Questo libro aiuta a capire meglio cosa fai quando crei e mantieni un
database relazionale, oltre ad aiutarti a trovare modi diversi per esprimere le query, trovare
query più ottimizzate e così via. Quindi non verrà spiegato come eseguire la modellazione;
verrà illustrato cosa devi ottenere con i modelli.
112
Microsoft Transact SQL Query
Sul mercato si trovano molti libri sulla modellazione, che non hanno bisogno di pubblicità. Ne
verranno citati alcuni solo per rendere più completo il capitolo. Uno è Information Modeling
and Relational Databases, 2nd edition di Terry Halpin e Tony Morgan (Morgan Kauffman, 2008)
(informazioni in lingua inglese), considerato la bibbia della modellazione ORM (object-role
modelling). Per quanto riguarda l'approccio più popolare, l'approccio ER, vale la pena citare
Data Modeling Essentials, 3rd edition di Graeme Simsion e Graham Witt (Morgan Kauffman,
2004) (informazioni in lingua inglese), in cui si possono trovare molte informazioni sul processo
di modellazione e informazioni sulle regole aziendali. Infine, se sei uno sviluppatore e usi già la
metodologia UML (Unified Modeling Language), una buona risorsa potrebbe essere Database
Design for Smarties: Using UML for Data Modeling di Robert J. Muller (Morgan Kauffman,
1999) (informazioni in lingua inglese).
Quello che si deve ottenere per creare un buon modello relazionale vien descritto
matematicamente con normalizzazione e specializzazione. Dal momento che la
normalizzazione è più complessa, verrà illustrata più nel dettaglio, anche se entrambi gli
aspetti sono importanti per una buona progettazione. Prima di iniziare con la normalizzazione
però, è opportuno ripetere un concetto molto semplice ma molto importante sulla buona
progettazione: un database relazionale è ben progettato se puoi ricostruire i predicati (e le
proposizioni) usati per descrivere il problema aziendale.
Formati normali per dipendenze funzionali
Le tabelle sono normalizzate quando rappresentano proposizioni relative a entità di un
tipo, in altre parole quando rappresentano un singolo set. Ciò significa che le entità non si
sovrappongono alle tabelle e che le tabelle sono ortogonali o normali in termini matematici.
Quando una tabella soddisfa un determinato insieme di condizioni prescritte, si dice che sia
in una forma normale. Un database è normalizzato quando tutte le tabelle sono normalizzate.
Puoi creare modelli di database completamente normalizzati con l'approccio ORM o ER.
La normalizzazione è un processo di riprogettazione per frazionare le entità. Il processo
include la decomposizione, ma non la decomposizione che porta alla perdita di informazioni.
Dopo il processo di normalizzazione, tutte le informazioni originali devono poter essere
ottenute con query che includono operatori relazionali come Join e altri. La normalizzazione
si ottiene applicando una sequenza di regole per creare ciò che viene chiamato forme
normali. L'obiettivo è eliminare la ridondanza e l'incompletezza. Nota che la seconda viene
spesso tralasciata, tuttavia la normalizzazione elimina l'incompletezza oltre alla ridondanza.
Vengono definite molte forme normali. Le più importanti sono la prima, seconda terza,
Boyce-Codd, quarta e quinta. Se un database ha la quinta forma normale, si dice che sia
completamente normalizzato. Se un database non è completamente normalizzato, possono
verificarsi anomalie nella manipolazione dei dati. Verranno illustrate le prime quattro forme
normali, che riguardano le dipendenze funzionali. Una variabile dipendente è dipendente
dal punto di vista funzionale da una indipendente quando esiste esattamente un valore della
variabile dipendente per ogni valore della variabile indipendente. Ciò significa che se conosci
il valore della variabile indipendente, conosci anche il valore della variabile dipendente. In una
relazione, gli attributi non chiave sono dipendenti dal punto di vista funzionale dalle chiavi;
se conosci il valore chiave, puoi trovare il valore dell'attributo non chiave. Questo è ciò che
significa dipendenza funzionale in una relazione.
Capitolo 3
Il modello relazionale
113
Prima forma normale
Immagina uno scenario reale con clienti che ordinano prodotti. Clienti, ordini e prodotti sono
entità scoperte quando hai avuto la descrizione dello scenario aziendale. Inizialmente esegui la
modellazione di tutto in un'unica tabella denominata Orders. Nella tabella 3-1 viene illustrata
la tabella immaginaria Orders. Le colonne che fanno parte della chiave sono ombreggiate (in
questo esempio solo OrderId).
Una tabella prima di 1NF
TABELLA 3-1
OrderId
CustomerId
CustomerName
OrderDate
Items
1
1
Company ABC
2008-10-22
Ap Apples q=5, Ch Cherries q=10
2
1
Company ABC
2008-10-24
Ba Bananas q=12
3
2
Company ABC
2008-09-15
Ap Apples q=3, Ba Bananas q=3
Questo progetto, ovviamente, è problematico. Alcune possibili anomalie nella manipolazione
dei dati sono:
■
Inserimento
❏
■
Aggiornamento
❏
■
Se l'elemento Ba viene rinominato, come esegui l'aggiornamento? Puoi perdere
facilmente qualche riga che dovresti aggiornare. E ciò a causa della ridondanza.
Eliminazione
❏
■
Come inserisci un cliente senza un ordine? (ad ogni modo, puoi notare
l'incompletezza del problema?)
Se l'ordine 3 viene eliminato, i dati del cliente 2 saranno persi. Questo è un
problema di incompletezza.
Selezione
❏
Come calcoli la quantità totale di banane? È il problema della colonna non
scalare. La colonna Items è una raccolta.
La prima forma normale (1NF) dice che una tabella rientra nella prima forma normale se tutte
le colonne sono atomiche. Non sono consentite colonne multivalore. Nota che la definizione di
1NF afferma semplicemente che una tabella deve rappresentare una relazione.
La decomposizione deve iniziare con la colonna Items. Ha bisogno di una singola riga per
elemento in un ordine, e ogni parte atomica dei dati di un singolo elemento (ProductId,
ProductName, Quantity) deve ottenere la propria colonna relativa. Tuttavia, dopo la
decomposizione, ottieni più righe per un singolo ordine. OrderId da solo non può più essere
la chiave. La nuova chiave è composta dalle colonne OrderId e ProductId. Se consenti più
prodotti su un singolo ordine, ad esempio ogni volta con uno sconto diverso, non potrai usare
ProductId come parte della chiave. Probabilmente aggiungerai l'attributo ItemId e lo userai
come parte della nuova chiave. Una tabella decomposta in 1NF viene illustrata nella tabella
3-2.
114
Microsoft Transact SQL Query
TABELLA 3-2
Una tabella in 1NF
OrderId CustomerId CustomerName OrderDate
Product
ItemID ProductId Quantity Name
1
1
Company ABC
2008-10-22
1
Ap
5
Apples
1
1
Company ABC
2008-10-22
2
Ch
10
Cherries
2
1
Company ABC
2008-10-24
1
Ba
12
Bananas
3
2
XYZ
2008-09-15
1
Ap
3
Apples
3
2
XYZ
2008-09-15
2
Ba
3
Bananas
Prima di iniziare con 2NF, è opportuno sottolineare un equivoco comune con 1NF. Spesso si
legge di ripetere il gruppo di colonne. Considera, ad esempio, la tabella Employees illustrata
nella figura 3-11.
Employees
PK
EmployeeId
EmployeeName
Child1Name
Child2Name
Child3Name
FIGURA 3-11 Tabella Employees
Probabilmente noterai qualcosa di strano. Presenta un gruppo di colonne che si ripetono con
un nome simile, ChildXName. Child1Name è il nome del figlio maggiore, Child2Name è il nome
del secondo più vecchio e Child3Name è il nome del terzo più vecchio (non considerando
i gemelli). Naturalmente la domanda è cosa succede se un dipendente ha più di tre figli.
Probabilmente creeresti una nuova tabella. Potresti pensare che stai normalizzando la tabella
Employees.
Sai che il modello relazionale non dipende dai nomi. Rinomina quindi la tabella e tutte le
colonne per ottenere la tabella illustrata nella figura 3-12.
Orders
PK
OrdersId
CustomerID
OrderDate
DueDate
ShipDate
FIGURA 3-12 Tabella Orders (la tabella Employees rinominata)
Probabilmente ti sentirai più a tuo agio con questo progetto e questa tabella ti sembrerà
perfettamente normalizzata. Anche la tabella Employees rientrava in 1NF, ma il problema è che
viene creato un vincolo in entrambe le tabelle. Il primo vincolo afferma che ci sono dipendenti
con tre figli (o al massimo tre se le colonne consentono valori NULL); il secondi vincolo afferma
Capitolo 3
Il modello relazionale
115
che un ordine ha tre date. Naturalmente il primo vincolo non ha senso nel mondo reale, quindi
il primo progetto non andava bene in ogni caso. Tuttavia era normalizzato. Ricorda che puoi
vincolare il modello di dati stesso con il progetto di tabella. Spesso un gruppo di colonne che
si ripete con nomi simili rappresenta davvero una raccolta nascosta; tuttavia, non decomporre
tali gruppi automaticamente. Verifica prima le regole aziendali, i predicati soggetti a vincoli.
Seconda forma normale
Dopo aver ottenuto 1NF, come visto nella tabella 3-2, esistono ancora molte anomalie di
aggiornamento:
■
Inserimento
Come inserisci un cliente senza un ordine? (Incompletezza)
❏
■
Aggiornamento
Se un cliente cambia la data di ordine, come puoi eseguire l'aggiornamento?
(Ridondanza)
❏
■
Eliminazione
Se l'ordine 3 viene eliminato, i dati del cliente 2 saranno persi. (Incompletezza)
❏
Per ottenere la seconda forma normale (2NF), una tabella deve essere nella 1NF (noterai la
progressione lineare) e ogni colonna non chiave deve essere dipendente dal punto di vista
funzionale dall'intera chiave. Ciò significa che nessuna colonna non chiave può dipendere
solo da una parte della chiave. Nella tabella 3-2 hai bisogno solo di OrderId per ottenere
CustomerId e OrderDate; non hai bisogno di ItemId, che fa ugualmente parte della chiave. Per
le forme normali diverse da 1NF, la decomposizione significa la creazione di nuove tabelle,
non solo nuove righe come in 1NF. Per ottenere 2NF devi decomporre la tabella in due tabelle,
come illustrato nelle tabelle 3-3 e 3-4.
Tabella Orders in 2NF
TABELLA 3-3
OrderId
CustomerId
CustomerName
OrderDate
1
1
Company ABC
2008-10-22
2
1
Company ABC
2008-10-24
3
2
XYZ
2008-09-15
TABELLA 3-4
Tabella OrderDetails in 2NF
OrderId
ItemId
ProductId
Quantity
ProductName
1
1
Ap
5
Apples
1
2
Ch
10
Cherries
2
1
Ba
12
Bananas
3
1
Ap
3
Apples
3
2
Ba
3
Bananas
116
Microsoft Transact SQL Query
Esegui la divisione in modo da lasciare solo gli attributi che dipendono da OrderId nella tabella
Orders, inoltre introduci una nuova tabella, OrderDetails, con altri attributi. 2NF riguarda la
relazione tra le colonne che fanno parte di una chiave e altre colonne che non ne fanno parte.
Per ottenere la decomposizione senza perdite, devi poter riunire in join le due tabelle per
generare la tabella originale. Quindi hai bisogno di qualche valore comune in entrambe le
tabelle. Naturalmente questa è la colonna OrderId della tabella Orders, che come già sai è la
colonna chiave esterna nella tabella OrderDetails.
Terza forma normale
Con 2NF è stata risolta l'anomalia dell'aggiornamento della data di ordine dovuta alla
ridondanza. Tuttavia restano ancora molti problemi:
■
Inserimento
Come inserisci un cliente senza un ordine? (Incompletezza)
❏
■
Aggiornamento
Se un cliente o un prodotto viene rinominato, come esegui l'aggiornamento?
(Ridondanza)
❏
■
Eliminazione
Se l'ordine 3 viene eliminato, i dati del cliente 2 saranno persi. (Incompletezza)
❏
Per ottenere la terza forma normale (3NF), una tabella deve essere nella 2NF e ogni colonna
non chiave non deve essere dipendente dal punto di vista transitivo da ogni chiave. In altre
parole, le colonne non chiave devono essere indipendenti l'una dall'altra. Ad esempio, nella
tabella 3-3 da OrderId puoi trovare CustomerId e da CustomerId puoi trovare transitivamente
il valore CustomerName. Prova a trovare un problema simile nella tabella 3-4 (naturalmente
ProductId e ProductName non sono dipendenti l'una dall'altra).
Per ottenere 3NF devi creare nuove tabelle per le dipendenze tra le colonne non chiave, come
illustrato dalla tabella 3-5 alla tabella 3-8.
Tabella Customers in 3NF
TABELLA 3-5
CustomerId
CustomerName
1
Company ABC
2
XYZ
TABELLA 3-6
Tabella Orders in 3NF
OrderId
CustomerId
OrderDate
1
1
2008-10-22
2
1
2008-10-24
3
2
2008-09-15
Capitolo 3
Il modello relazionale
117
Tabella OrderDetails in 3NF
TABELLA 3-7
OrderId
ItemId
ProductId
Quantity
1
1
Ap
5
1
2
Ch
10
2
1
Ba
12
3
1
Ap
3
3
2
Ba
3
TABELLA 3-8
Tabella Products in 3NF
ProductId
ProductName
Ap
Apples
Ch
Cherries
Ba
Bananas
Quando ottieni 3NF in genere elimini tutte le anomalie di manipolazione dei dati. In genere
quando normalizzi fino a 3NF, il risultato soddisfa anche BCNF, 4NF e 5NF. Le forme normali
più elevate di violazioni sono rare. Per rendere questa panoramica completa, tuttavia,
verranno illustrate le forme normali più elevate e verranno forniti suggerimenti pratici su come
riconoscere la possibilità di violarle.
Forma normale di Boyce-Codd
La prima domanda che potresti farti è perché la NF successiva non viene chiamata 4NF. Il fatto
è che Codd di fatto a voluto sostituire la 3FN con quella che viene ora conosciuta come forma
normale di Boyce-Code (BCFN). Dal momento che è più rigida di 3NF, 3NF non è stata tolta, di
conseguenza esiste una certa incoerenza nella numerazione.
Verrà ora illustrato come violare BCNF. Immagina per un momento di avere la tabella Orders
senza la colonna OrderId e con un unico ordine per cliente al giorno consentito. Inoltre, ogni
ordine ha un tempo di spedizione standard, quindi OrderDate fornisce l'atteso DueDate. Nella
tabella 3-9 viene illustrato questo esempio. Per chiarire la dipendenza, DueDate è sempre un
giorno dopo OrderDate.
TABELLA 3-9
Tabella immaginaria Orders
CustomerId
OrderDate
DueDate
OtherOrderColumns
1
2008-10-22
2008-10-23
...
1
2008-10-24
2008-10-25
...
2
2008-09-15
2008-09-16
...
Questa tabella ha due chiavi candidate composte: {CustomerId, OrderDate} e {CustomerId,
DueDate}. Le chiavi candidate si sovrappongono sulla colonna CustomerId (ombreggiata con
un colore più scuro per dimostrare che viene usata due volte). È in 3NF perché tutte le colonne
118
Microsoft Transact SQL Query
non chiave dipendono intransitivamente da ogni chiave. Tuttavia, è possibile una anomalia di
manipolazione dei dati specifica:
■
Aggiornamento
❏
Se un cliente cambia OrderDate, non devi dimenticare di aggiornare anche
DueDate. (Ridondanza)
Puoi violare BCNF solo nel raro caso in cui una tabella ha più di una chiave candidata
composta e le chiavi candidate si sovrappongono. Sarebbe possibile decomporre la tabella
3-9 in due nuove tabelle basate sulle due chiavi candidate, per brevità nella notazione breve,
mostrando solo le intestazioni di tabella:
OrdersOrderDate {CustomerId, OrderDate, OtherOrderColumns}
OrdersDueDate {CustomerId, DueDate, OtherOrderColumns}
Tuttavia, il buon senso ti dice che questa decomposizione non è quello che desideri nel
tuo modello. Inoltre, esiste una ridondanza nascosta tra le due nuove tabelle; altre colonne
non chiave si ripetono. Non è possibile risolvere questo problema solo con le regole di
normalizzazione (sai già che il buon senso può aiutarti). Questo problema verrà ripreso più
avanti con una soluzione formale, quando verrà descritto il principio della progettazione
ortogonale.
La BCNF non è ancora stata definita. BCNF afferma che ogni determinante deve essere una
chiave. La parte indipendente di una dipendenza funzionale viene chiamata determinante.
Un attributo chiave deve essere un determinante, ossia non deve essere determinato. Nella
tabella 3-9 OrderDate determinava DueDate e viceversa, inoltre entrambi sono attributi
chiave (precisamente parte di una chiave). In altre parole, per ottenere BCNF non devi avere
dipendenze funzionali tra gli attributi chiave.
Puoi ottenere BCNF senza decomposizione usando il buon senso. Nelle tabelle 3-10 e 3-11
vengono illustrate le due possibilità per ottenere BCNF nella tabella 3-9.
TABELLA 3-10
Tabella Orders in BCNF: Prima soluzione
CustomerId
OrderDate
StandardShippingTimeDays
OtherOrderColumns
1
2008-10-22
1
...
1
2008-10-24
1
...
2
2008-09-15
1
...
TABELLA 3-11
Tabella Orders in BCNF: Seconda soluzione
OrderId
CustomerId
OrderDate
DueDate
OtherOrderColumns
1
1
2008-10-22
2008-10-23
...
2
1
2008-10-24
2008-10-25
...
3
2
2008-09-15
2008-09-16
...
Nota che la soluzione illustrata nella tabella 3-11 non definisce più le coppie (CustomerId,
OrderDate) e (CustomerId, Duedate) come chiavi. Quindi non è realmente una soluzione se le
Capitolo 3
Il modello relazionale
119
due coppie continuano a determinare gli ordini. Tuttavia è stata introdotta perché è più simile
agli scenari reali; un cliente può inserire più di un ordine al giorno.
Forme normali più elevate
Le forme normali più elevate, la quarta e la quinta, non riguardano le dipendenze funzionali,
bensì le dipendenze multivalori e di join. Verranno ora illustrate.
Quarta forma normale
Come già detto, le violazioni della quarta e quinta forma normale sono molto rare, e in genere
possono essere evitate con il buon senso. Per iniziare, le violazioni possono verificarsi solo in
una tabella composta da colonne che insieme compongono una chiave, senza colonna non
chiave e con almeno tre colonne chiave. Gli esempi seguenti di violazioni della 4NF e 5NF, così
come le soluzioni, si basano su esempi di Practical Issues in Database Management di Fabian
Pascal (Addison-Wesley, 2000) (informazioni in lingua inglese).
Si inizierà con il descrivere un esempio di problema aziendale. Un'azienda fittizia lavora su
dei progetti. I dipendenti vengono assegnati a tali progetti. Ogni dipendente ha una serie di
competenze. Se un dipendente viene assegnato a un progetto, tale dipendente esegue tutte
le attività che può eseguire. Nella tabella 3-12 viene illustrato questo esempio. Benché non
venga mostrato qui, immagina che ci siano tabelle Employees, Projects e Activities separate nel
database.
TABELLA 3-12
Tabella Employee-Projects-Activities
Employee
Project
Activity
1
Proj 111
ABC
1
Proj 111
DEF
1
Proj 222
ABC
1
Proj 222
DEF
2
Proj 111
ABC
2
Proj 111
XYZ
Noterai la ridondanza. Sono possibili le seguenti anomalie nella manipolazione dei dati:
■
Inserimento
❏
■
Aggiornamento
❏
■
Come assegni un dipendente a un progetto se il dipendente non ha ancora
competenze? (Incompletezza)
Se un dipendente viene riassegnato da un progetto a un altro, come gestisci
l'aggiornamento di tutte le righe necessarie? (Ridondanza)
Eliminazione
❏
Se elimini tutte le assegnazioni di progetto di un dipendente, le informazioni
relative alle relative competenze andranno perse. (Incompletezza)
120
Microsoft Transact SQL Query
Le informazioni su progetti e attività si ripetono per ogni dipendente. È possibile evitare
questo problema se si consentono colonne multivalori, come illustrato nella tabella 3-13.
TABELLA 3-13
Tabella Employee-Projects-Activities con colonne multivalori
Employee
Project
Activity
1
Proj 111
ABC
Proj 222
DEF
Proj 111
ABC
2
XYZ
Questa situazione indica che esiste qualcosa denominato dipendenza multivalore tra
dipendenti, progetti e attività. Le dipendenze multivalori sono una generalizzazione delle
dipendenze funzionali. La quarta forma normale (4NF) afferma che non devono esserci
dipendenze multivalori non triviali che non sono dipendenze funzionali. Per ottenere tutto ciò,
devi decomporre la tabella 3-12, come illustrato nelle tabelle 3-14 e 3-15.
TABELLA 3-14
Tabella Employee-Projects
Employee
Project
1
Proj 111
1
Proj 222
2
Proj 111
TABELLA 3-15
Tabella Employee-Activities
Employee
Activity
1
ABC
1
DEF
2
ABC
2
XYZ
Quinta forma normale
Ora verrà leggermente modificata la descrizione del problema aziendale. Se un dipendente
viene assegnato a un progetto, tale dipendente non deve eseguire tutte le attività per cui ha le
competenze da eseguire in questo progetto; infatti, in un progetto alcune delle attività per cui
i dipendenti hanno le competenze potrebbero non essere necessarie. Tuttavia se un progetto
include un'attività, un dipendente viene assegnato al progetto e tale dipendente assegnato
esegue la sopraccitata attività, il dipendente deve eseguire tale attività sul progetto. Nella
tabella 3-16 viene illustrato un esempio.
Capitolo 3
TABELLA 3-16
Il modello relazionale
121
Tabella Employee-Projects-Activities
Employee
Project
Activity
1
Proj 111
ABC
1
Proj 111
DEF
1
Proj 222
ABC
2
Proj 111
ABC
2
Proj 111
XYZ
Senza decomposizione, le possibili anomalie nella manipolazione dei dati sono simili alle
anomalie citate nella sezione della 4NF. Dopo la decomposizione in due tabelle, come hai visto
nelle tabelle 3-14 e 3-15, provi a unire in join le tabelle decomposte per riottenere la tabella
originale 3-16. Quello che accade è che ottieni un'ulteriore tupla non corretta:
{1, Proj 222, DEF}
Con la decomposizione della tabella 3-16 in due tabelle che sono di fatto proiezioni della
tabella originale, hai ottenuto una riga non corretta se hai unito in join le due nuove tabelle.
Il problema sta nel fatto che la tabella originale violava il cosiddetto vincolo di dipendenza
join. Una relazione soddisfa la relazione join se ogni valore lecito della relazione è uguale al
join delle relative proiezioni. Le dipendenze join sono una generalizzazione delle dipendenze
multivalori. Per risolvere il problema devi eseguire una decomposizione in tre tabelle. Oltre
alle tabelle Employess-Projects ed Employees-Activities, devi avere anche una tabella ProjectsActivities, come illustrato nella tabella 3-17.
TABELLA 3-17
Tabella Projects-Activities
Project
Activity
Proj 111
ABC
Proj 111
DEF
Proj 222
ABC
Proj 111
XYZ
Se non esiste una violazione della dipendenza join, una tabella è in 5NF. Una definizione di
forma normale afferma che ogni dipendenza join non triviale nella tabella è implicata dalle
chiavi della tabella.
Infine, è opportuno ritornare sul buon senso citato precedentemente. Cosa succede se un
progetto include un'attività, un dipendente viene assegnato a un progetto e tale dipendente
assegnato esegue la sopraccitata attività, ma il dipendente non deve eseguire tale attività sul
progetto? A questo punto sono necessarie quattro tabelle, che è il progetto probabilmente
creato inizialmente. È necessaria una tabella Employees-Projects che mostra quali dipendenti
sono assegnati a quale progetto; la tabella Employees-Activities che mostra quali attività
possono essere eseguite dai dipendenti, la tabella Projects-Activities che mostra quali attività
sono necessarie in quale progetto, e infine la tabella Employees-Projects-Activities che mostra
quale attività viene eseguita da quale dipendente su quale progetto.
122
Microsoft Transact SQL Query
Altre forme normali
Prima di introdurre le forme normali quarta e quinta è opportuno citare brevemente la
forma normale chiave dominio (DKNF). In DKNF tutti i vincoli derivano dai domini (tipi) e
dalle chiavi (chiavi candidate e chiavi esterne). Una tabella in DKNF è libera di violare regole
di entità, referenziali e di dominio, come descritto precedentemente. È nella quinta forma
normale e quindi completamente normalizzata. Tuttavia, DKNF è una forma normale più
teorica che pratica. Per ottenerla, devi creare moltissimi tipi diversi. Questa è una missione
quasi impossibile, specialmente se i tipi devono essere accettati ampiamente e i vincoli di tipo
devono essere concordati. Inoltre, gli utenti dei tipi (il database e gli altri sviluppatori) devono
imparare molte cose solo per iniziare a usare i tipi.
C. J. Date ha proposto una sesta forma normale, una forma che risolve possibili anomalie
di dati temporali. Tuttavia per risolvere problemi di dati temporali, è necessario introdurre
il tipo di dati Interval, implementato negli operatori Pack e Unpack, e risolvere anche altri
problemi. Leggi Inside Microsoft SQL Server 2008: T-SQL Programming per una discussione più
approfondita sui dati temporali e le soluzioni proposte per i problemi temporali.
Denormalizzazione
Dovresti sempre provare a ottenere almeno 3NF quando progetti un database. Tuttavia,
a volte devi rigirare il processo e, dopo aver normalizzato un database, devi iniziare a
denormalizzarlo. I due motivi principali per la denormalizzazione sono prestazioni e
cronologia, come illustrato in Designing Database Solutions by Using Microsoft SQL Server
2005 di Dejan Sarka, Andy Leonard, Javier Loria e Adolfo Wiernik (Microsoft Press, 2007)
(informazioni in lingua inglese).
Una domanda aziendale classica è qual è la quantità attuale di prodotto in magazzino. Puoi
calcolare le quantità in magazzino sommando le forniture e sottraendo le consegne. Gli stati
e i livelli possono essere sempre calcolati dagli eventi. Tuttavia, questa domanda può essere
molto frequente. Quindi ha senso aggregare gli eventi ai livelli e agli stati e mantenere tali
aggregati con ogni nuovo evento. Inoltre, puoi velocizzare i join replicando una chiave esterna
dalla prima tabella figlio alla seconda. Le query di questo tipo possono includere meno
tabelle da unire in join. In entrambi i casi hai eseguito la denormalizzazione per migliorare le
prestazioni.
Immagina un altro esempio. Un'applicazione di fatturazione usa un progetto di database
completamente normalizzato. L'indirizzo di un cliente viene archiviato solo nella tabella
Customers. Se un cliente si sposta, aggiorni tale indirizzo cliente con quello nuovo. Supponi
che dopo l'aggiornamento, il cliente chiede di ristampare una vecchia fattura. Ora hai un
problema perché non hai salvato il vecchio indirizzo. Puoi risolvere il problema mantenendo
una copia dell'indirizzo del cliente con la data della fattura nella tabella Invoices (bisogna dire
che questo potrebbe non essere trattato come denormalizzazione; probabilmente hai appena
perso quell'attributo InvoiceAddress mentre analizzavi il problema aziendale). Nella figura 3-13
viene illustrato il database Invoices completamente normalizzato.
Capitolo 3
Employees
PK
Customers
EmployeeId
PK
EmployeeName
CustomerName
Address
CityId
FK1 EmployeeId
CustomerId
ProductName
Price
Warehouses
PK
ProductId
PK
Invoiceld
FK1 Customerld
InvoiceDate
InvoiceDetails
PK,FK1
PK
InvoiceId
ItemId
FK2
ProductId
Quantity
Discount
WarehouseId
WarehouseId
FK3
WarehouseName
123
Invoices
Products
PK
Il modello relazionale
FIGURA 3-13 Versione normalizzata del database Invoices
Puoi eseguire la denormalizzazione in più punti. Ad esempio, puoi trasferire la colonna
EmployeeId alla tabella Invoices per evitare un join con la tabella Customers quando analizzi le
fatture solo tramite i dipendenti. Puoi includere le colonne CustomerName e CustomerAddress
nella tabella Invoices per mantenere la cronologia. Puoi mantenere gli aggregati, ad esempio
livello di magazzino per warehouse (in una tabella separata), livello totale di magazzino per
prodotto, vendite anno per data per cliente e altro ancora. La figura 3-14 illustra una versione
denormalizzata del database Invoices.
Employees
PK
EmployeeId
PK
EmployeeName
CustomerName
CustomerAddress
CityId
FK1 EmployeeId
YTDSales
ProductsInWarehouses
PK,FK1
PK,FK2
Customers
CustomerId
WarehouseId
ProductId
Invoices
PK
Invoiceld
FK1 Customerld
InvoiceDate
EmployeeId
CustomerName
CustomerAddress
Products
QualityInStock
PK
Warehouses
PK
ProductId
ProductName
Price
TotalInStock
InvoiceDetails
PK,FK1
PK
InvoiceId
ItemId
FK2
ProductId
Quantity
Discount
WarehouseId
WarehouseId
WarehouseName
FK3
FIGURA 3-14 Versione denormalizzata del database Invoices
Ricorda di eseguire la denormalizzazione in modo molto deliberato. Dopo la
denormalizzazione, introduci possibili anomalie di aggiornamento nel database. Devi
mantenere i dati ridondanti nelle transazioni definite dall'utente. Se inserisci un nuovo
124
Microsoft Transact SQL Query
evento, ad esempio, fai attenzione ad aggiornare il livello o lo stato derivato dagli eventi nella
stessa transazione. I trigger sono particolarmente utili per mantenere i dati denormalizzati.
Con i trigger, che fanno automaticamente parte di una transazione, trasferisci il carico della
manutenzione dei dati denormalizzati sul tuo RDBMS.
Generalizzazione e specializzazione
Torna al problema dei valori NULL. Ricorda che puoi avere valori NULL quando un attributo
non è applicabile per alcune tuple. Puoi eliminare la necessità di usare i valori NULL in questo
modo tramite la specializzazione introducendo i sottotipi. Il problema può essere anche
aggirato; ricorda la decomposizione per risolvere la violazione BCNF precedentemente in
questo capitolo:
OrdersOrderDate {CustomerId, OrderDate, OtherOrderColumns}
OrdersDueDate {CustomerId, DueDate, OtherOrderColumns}
Queste due relazioni hanno molti attributi in comune e questo è un tipo di ridondanza. Puoi
risolvere tale ridondanza tramite la generalizzazione introducendo i sopratipi.
Due entità sono di tipi distinti, o primitivi, se non hanno attributi in comune. Alcune relazioni
possono avere sia attributi in comune sia attributi distinti. Se hanno un identificatore in
comune (ossia uno schema di identificazione primario in comune o una chiave primaria in
comune) si può parlare di relazione sopratipo/sottotipo speciale. I sopratipi e i sottotipi sono
utili per rappresentare diversi livelli di generalizzazione o specializzazione. Nella descrizione
di un problema aziendale, il verbo è (o esplicitamente è un tipo di) porta a una relazione
sopratipo/sottotipo. Ad esempio, un cliente è un partner, così come un fornitore. Ovviamente, i
clienti e i fornitori hanno qualcosa in comune.
Nell'esempio precedente, i partner sono un sopratipo dei clienti e dei fornitori. Se inizi con
i sottotipi e trovi un sopratipo, stai usando un approccio dal basso verso l'alto. L'approccio
dall'alto verso il basso è il contrario. Quando esegui la generalizzazione o la specializzazione,
si verifica lo stesso problema: dove fermarsi? È possibile rispondere facilmente alla domanda
con l'approccio dall'alto verso il basso. Ferma la specializzazione (in altre parole, ferma
l'introduzione) dei sottotipi quando non ci sono altri attributi interessanti per un altro livello
di sottotipi. La tecnica opposta è più problematica; dopo tutto puoi finire con solo poche
entità, ad esempio soggetti, oggetti ed eventi. Una possibile condizione di arresto è quando
raggiungi gli oggetti astratti, o gli oggetti che non esistono nel mondo reale. Gli oggetti
astratti non fanno parte di un database relazionale. Tuttavia, a volte è pratico introdurre
un sopratipo solo per condividere uno schema di identificazione comune anche tra entità
disgiunte. Per esperienza si consiglia un approccio pratico che funzioni bene per se stessi:
fermati quando hai un problema nel denominare il sopratipo (quando raggiungi nomi
come cosa). Se non puoi denominarlo immediatamente, probabilmente stai provando a
generalizzare entità disgiunte.
Di seguito vengono illustrati altri consigli pratici per la generalizzazione e la specializzazione.
Se hai una tabella con alcuni valori noti e molti valori NULL in alcune colonne, probabilmente
è opportuno eseguire la specializzazione. Controlla se i valori NULL rappresentano valori
Capitolo 3
Il modello relazionale
125
sconosciuti o attributi che non sono applicabili alle righe in cui compaiono. Puoi eliminare i
valori NULL per gli attributi che non sono applicabili se introduci i sottotipi. Per l'approccio
dal basso verso l'alto, le tabelle che hanno molte colonne con nomi simili o addirittura uguali
probabilmente necessitano di una tabella sopratipo. Nota che sei nuovamente dipendente da
una buona convenzione di denominazione.
Nella figura 3-15 vengono illustrate le entità che devono essere generalizzate.
CustomersOriginal
PK
CustomerId
CompanyName
Address
DiscountCode
SuppliersOriginal
PK
SupplierId
CompanyName
Address
URL
FIGURA 3-15 Prima della generalizzazione
È necessario citare un problema notevole con la generalizzazione. Cosa succede se il sistema
con la struttura della figura 3-15 è già in ambiente di produzione con una notevole quantità di
dati già inseriti? In tal caso la generalizzazione non è così semplice. Non solo devi introdurre
un modello generalizzato come quello illustrato nella figura 3-16, ma anche fare attenzione ai
dati. Devi unire e togliere i duplicati di clienti e fornitori nel caso in cui un cliente fosse anche
un fornitore.
Partners
Customers
PK PartnerId
PK,FK1 PartnerId
PK,FK1 PartnerId
DiscountCode
Suppliers
CompanyName
Address
URL
FIGURA 3-16 Dopo la generalizzazione
Hai ricevuto molti consigli pratici su come trovare sopratipi e sottotipi. È stata inoltre citata
una regola formale relativa al momento in cui arrestare la specializzazione: quando non hai
più attributi da aggiungere a un sottotipo. Tuttavia, per rendere coerente questo argomento
con il resto delle teorie espresse nel capitolo, è necessaria una definizione formale di quando
arrestare la generalizzazione.
Principio della progettazione ortogonale
Trovi i sopratipi più generali quando nessuna delle due relazioni viene definita in modo tale
che possano rappresentare gli stessi fatti. Una definizione più formale afferma che il database
deve essere conforme al principio della progettazione ortogonale, come affermato in An
Introduction to Database Systems, 8th edition di C. J. Date (Addison-Wesley, 2003) (informazioni
in lingua inglese):
A e B devono essere variabili relazionali distinte. Non devono esistere
decomposizioni senza perdite di A e B in A1, A2, . . ., Am e B1, B2, . . ., Bn
(rispettivamente) così che alcune proiezioni di Ai nel set A1, A2, . . ., Am e alcune
proiezioni di Bj nel set B1, B2, . . ., Bn abbiano significati sovrapposti.
126
Microsoft Transact SQL Query
Ora è opportuno aggiungere qualche parola di spiegazione. Il termine ortogonale significa
che le relazioni devono avere significati reciprocamente indipendenti, e questo è esattamente
quello che si desidera per i tipi primitivi. Potresti notare che il principio è solo buon senso
formalizzato. Mentre la normalizzazione riduce la ridondanza all'interno delle relazioni, la
generalizzazione (o la progettazione ortogonale) riduce la ridondanza tra le relazioni. Infine,
la specializzazione riduce la necessità di usare il valore NULL per un attributo che non è
applicabile. Nota inoltre che il principio della progettazione ortogonale evita anche inutili
decomposizioni orizzontali in base alle restrizioni di non sovrapposizione della relazione
originale, in quanto otterresti di nuovo delle proiezioni delle nuove relazioni decomposte
con significati sovrapposti. L'implicazione della progettazione ortogonale è che anche se le
relazioni A e B hanno lo stesso tipo di intestazione, deve essere mantenuto quanto segue:
A Union B
: è un'unione disgiunta
A Intersect B
: è vuoto
A Minus B
: è uguale ad A
Puoi usare queste equazioni per verificare se hai relazioni senza significati sovrapposti.
Conclusioni
Questo capitolo ha introdotto il modello relazionale. Sono stati spiegati termini base come
tipo, tupla, relazione e attributo. Il significato di una relazione dovrebbe ora essere chiaro, e
dovresti riconoscere che puoi considerare una relazione come un'entità aziendale o capirla
come un predicato con proposizioni. Puoi inoltre usare questo doppio approccio quando
esegui la manipolazione delle relazioni; puoi essere prescrittivo, usando l'algebra relazionale,
o descrittivo, usando il calcolo relazionale. Sono stati sottolineati l'importanza dell'integrità
dei dati e il significato di mantenerla. Sono inoltre stati illustrati in modo esaustivo i vincoli.
È stato poi affrontato il problema dei valori NULL. Il capitolo si è concluso con un insieme
di regole formali e principi per ottenere una buona progettazione, incluse normalizzazione
e progettazione ortogonale. È stato sottolineato più volte come una buona convenzione di
denominazione sia fondamentale per una buona progettazione. Tutte le nozioni teoriche
illustrate in questo capitolo e nel capitolo 2 dovrebbero aiutarti a capire le query avanzate che
incontrerai nei prossimi capitoli.
