B
Sperimentazioni
con le basi di dati
Illustreremo adesso con esempi i principi di utilizzazione di un DBMS.
Come abbiamo già visto, questi sistemi vengono incontro al problema di
gestire in modo efficace ed efficiente grandi quantità di dati strutturati residenti nella memoria di massa. Affinché ciò avvenga, occorre però che il
progettista esamini con attenzione il problema a lui sottoposto e lo esprima successivamente in modo corretto nel DBMS a disposizione. La prima
fase prende il nome di “analisi dei requisiti”; in essa viene prevista anche
la lista delle possibili domande da porre al sistema, come abbiamo mostrato nella metafora di mago Merlino. Non vogliamo soffermarci sulla
prima fase, e supponiamo che il programmatore di basi di dati conosca
completamente i requisiti del suo problema. Ci concentriamo piuttosto su
come questo possa essere trasferito su un sistema di gestione relazionale
e più precisamente sul programma Microsoft Access, di cui adotteremo
anche la terminologia della versione italiana.
Alla fine del capitolo potremo provare a costruirci una piccola base di
dati per la nostra videoteca, o per i nostri file MP3 che abbiamo scaricato
dalla Rete, o anche per l’armadietto dei nostri medicinali.
E saremo in grado di capire che eventuali errori in documenti che ci riguardano non si sono verificati per “colpa del computer” e spesso neanche per colpa di chi lo utilizza: possono derivare invece da una programmazione superficiale di strumenti sofisticati sì, ma, proprio per questo, da
usare con attenzione.
2
B.1
Capitolo B
DDL in Access
Un sistema relazionale come Access fornisce una notazione per definire i
dati (DDL), assegnare i valori, interrogare le tabelle (DML) e stabilire dei
privilegi d’uso (DCL).
Alla sua apertura propone all’utente la scelta del database su cui agire.
Se questo non esiste, deve essere definito e memorizzato prima che si possa usarlo. Supponiamo allora di assegnare il nome “Ferrovia” a un nuovo
database. Il sistema ci consente, con la sua schermata iniziale (Figura B.1),
di generare tabelle, query, maschere, report, pagine, ecc. Di tutte queste
parti, quelle veramente legate al modello relazionale sono le tabelle e le
query.
B.1.1 Definizione delle tabelle
Scegliamo l’area delle tabelle e attiviamo il pulsante “Crea una tabella in
visualizzazione Struttura”, evidenziato in Figura B.1.
Si presenta davanti a noi un’interfaccia a due pannelli: in quello superiore è possibile assegnare il nome e il tipo agli attributi, mentre in quello
inferiore ci sono gli strumenti per scegliere le loro proprietà. Riportiamo
in Figura B.2 la struttura che abbiamo dato alla tabella personale. I nomi
degli attributi sono autoesplicativi; con RESPONSABILE abbiamo indicaRelazioni
Figura B.1
Sperimentazioni con le basi di dati
Chiave primaria
Indici
3
Proprietà
Figura B.2 Interfaccia per la definizione delle tabelle.
to la matricola del responsabile immediato della persona presa in considerazione.
Il triangolino nero, che nella figura in discussione è posto sulla seconda riga, evidenzia che le “Proprietà campo” della parte inferiore riguardano esattamente quell’attributo (COGNOME).
A fianco del campo MATRICOLA notiamo il simbolo di chiave che è
stato immesso selezionando il campo e successivamente cliccando
sull’icona Chiave primaria (evidenziata nella Figura B.2 con un indicatore). Non abbiamo definito l’attributo MATRICOLA di tipo numerico, in
quanto si tratta solo di un codice su cui non avrà senso fare operazioni algebriche. Poiché abbiamo dichiarato tale campo “chiave primaria” il sistema ci impedirà di ripetere un valore già immesso. Inoltre, come tutti
gli attributi che fanno parte di una chiave, MATRICOLA non può assumere il valore NULL. Si tratta di un segnale di valore mancante (non conosciuto) o inesistente (senza significato per la particolare ennupla). In concreto, un campo ha valore NULL se non abbiamo immesso alcun valore.
Anche se a livello di visualizzazione i risultati sono identici, per il sistema
esiste invece una radicale differenza se abbiamo immesso degli spazi
bianchi con la barra spaziatrice. Il valore NULL ha un’interessante conseguenza sulla logica che, invece di essere booleana, come siamo abituati
4
Capitolo B
solitamente, è a tre valori: VERO , FALSO , SCONOSCIUTO (risultato di un
confronto tra un NULL ed un altro qualsiasi valore).
Nella definizione per il campo COGNOME abbiamo immesso il valore
“Sì” sulla proprietà Richiesto. Questa scelta impedisce per l’attributo il valore NULL, che potrebbe derivare da un errore (il campo non viene riempito) durante l’immissione dei dati. Abbiamo scelto il valore “No” per la
proprietà Consenti lunghezza zero, che altrimenti permetterebbe l’inserimento di “” (o di una sequenza di spazi), visualizzato dal sistema come
nel caso del valore NULL.
Degli attributi restanti mettiamo in evidenza il campo RESPONSABILE, in quanto dobbiamo permettere il valore NULL . Occorre infatti prevedere che la persona di cui stiamo immettendo i dati possa non avere un
superiore.
Non è questo l’unico modo per creare le tabelle. Ne esiste un altro ad
interfaccia testuale, molto più vicino allo standard SQL. Nella schermata
iniziale di Figura B.1, invece di scegliere l’area delle tabelle si sceglie
l’area delle query e successivamente il pulsante Crea una query in visualizzazione Struttura. Tra le finestre generate adesso dal sistema occorre chiudere quella dal nome Mostra tabella per poi scegliere Visualizzazione SQL
dal menu Visualizza come illustrato nella Figura B.3.
Possiamo digitare la seguente istruzione:
CREATE TABLE nometabella (
MATRICOLA CHAR (50),
COGNOME CHAR (50) NOT NULL,
NOME CHAR (50) NOT NULL,
DATA_NASCITA DATE NOT NULL,
MANSIONE CHAR (50) NOT NULL,
DATA_ASSUNZIONE DATE NOT NULL,
STIPENDIO INTEGER NOT NULL,
PREMIO_PRODUZIONE INTEGER NOT NULL,
STAZIONE_APP CHAR (50) NOT NULL,
RESPONSABILE CHAR (50),
PRIMARY KEY (MATRICOLA));
SQL
Figura B.3 Menu per la creazione delle query.
Esegui
Sperimentazioni con le basi di dati
5
La sua esecuzione, ottenuta premendo il pulsante Esegui evidenziato
in Figura B.3 e rappresentato dal punto esclamativo, genera una tabella
simile alla precedente. Ritornando nell’ambiente Tabelle, ne vediamo infatti una dal nome “nometabella”.
B.1.2 Definizione degli indici
Per gestire la chiave primaria, il sistema crea automaticamente un indice.
Si tratta, in generale, di una struttura che rallenta le fasi di inserimento,
ma rende più rapida la consultazione della base di dati. Il DDL consente
la definizione di ulteriori indici su singoli attributi e su insiemi di essi. Saranno quelli su cui pensiamo che saranno effettuate le ricerche.
Definiamo per la nostra tabella l’indice “Cognome_Nome” costituito
dai due campi NOME e COGNOME. A tale scopo si usa il pulsante Indici
evidenziato in Figura B.2, col quale si apre la finestra di creazione. Qui
possiamo inserire il nome dell’indice e i campi di cui esso è composto. La
nostra realizzazione è mostrata nella Figura B.4, dove abbiamo impostato
a “No” la proprietà Univoco dell’indice per permettere l’inserimento di
persone che hanno cognomi e nomi uguali.
Per avere un’idea funzionale di un indice, paragoniamolo a quello
analitico che conclude spesso un libro cartaceo (anche il presente). La sua
struttura è ordinata su parole prestabilite con l’indicazione precisa delle
pagine del testo che le contengono, in modo che il lettore possa trovarle
immediatamente. Il sistema di basi di dati organizza e usa quelli che dichiariamo indici, proprio come noi facciamo con l’indice analitico: costruisce l’elenco dei valori assunti dall’attributo indicizzato e lo completa con
i puntatori opportuni. Si tratta solo di un’analogia a grandi linee.
Figura B.4 Interfaccia per la definizione degli indici.
6
Capitolo B
B.1.3 Definizione dei vincoli
Limitiamoci in questo paragrafo a una sola tabella e vediamo come si definiscono in Access i vincoli:
■ di chiave;
■ sui singoli attributi;
■ su più attributi della stessa tabella.
Vincoli di chiave
La loro definizione consiste nel dichiarare la chiave primaria e le chiavi
candidate per evitare duplicazioni.
Per la chiave primaria si indicano i campi con la tecnica usuale di Windows e si sceglie il pulsante con il simbolo di chiave, come visto
nell’esempio precedente con il campo MATRICOLA.
Per una chiave candidata il procedimento è più complesso, non essendo previsto un meccanismo immediato. Si procede innanzitutto settando
le prorpietà dei suoi attributi in modo da impedire che assumano il valore
NULL . Poi si crea un indice, come mostrato precedentemente, ma impostando a “Sí” la proprietà Univoco dell’indice. Nel nostro esempio di Figura B.4 definiamo chiave candidata l’insieme degli attributi COGNOME,
NOME e DATA_NASCITA e le assegnano il nome “Chiave_candidata”.
Se la chiave primaria è costituita da molti attributi è spesso conveniente dichiararla candidata e sostituirla con una chiave ad attributo singolo
sfruttando eventualmente il contatore offerto dal sistema.
Vincoli sui singoli attributi
Osserviamo che è necessario stabilire il tipo di valore previsto per un
campo che viene predefinito testuale dal sistema. Possiamo essere più restrittivi usando il campo Valido se nel pannello inferiore della Figura B.2.
Esempio B.1
Uno stipendio deve essere compreso tra 900,01 Euro e 17999,99 Euro. Tale
vincolo si esprime nella struttura della tabella indicando sull’attributo
STIPENDIO la condizione:
Sperimentazioni con le basi di dati
7
Vincoli su più attributi della stessa tabella
Si possono esplicitare altri tipi di legami tra più attributi di una stessa tabella, oltre a quelli (univocità, richiesto) esprimibili tramite gli indici.
In Access il vincolo si esprime attivando l’icona Proprietà riferita alla
tabella evidenziata in Figura B.2. Sulla finestra attivata (vedi Figura B.5)
ci posizioniamo sulla proprietà Valido se e scriviamo la condizione opportuna.
Figura B.6 Interfaccia per la definizione delle proprietà della tabella.
Esempio B.2
La ditta assume solo persone di età superiore ai 18 anni e inferiore ai 35.
In questo caso, nella riga Valido se scriviamo:
(DateDiff("aaaa";DATA_DI_NASCITA;DATA_ASSUNZIONE)>18) and
(DateDiff("aaaa";DATA_DI_NASCITA; DATA_ASSUNZIONE)<35)
Qui si usa una funzione offerta dall’ambiente, che permette di effettuare una differenza tra le date in anni, espressa mediante il parametro
“aaaa”, e quelle in giorni e in settimane, espressa con altri parametri.
Questi vincoli vengono controllati dal sistema solo in caso di immissione dati. Ma ci sono casi alquanto particolari. Ipotizziamo di aggiungere un campo ANZIANITA per esprimere l’età lavorativa. Dobbiamo allora imporre il vincolo che il suo valore sia uguale alla differenza tra la data
attuale (del sistema) e la data di assunzione. Dovremmo scrivere, come
mostrato in Figura B.6, nel campo Valido se:
[ANZIANITA]=DateDiff(“aaaa”;[DATA_ASSUNZIONE];Date())
Il sistema controlla che gli inserimenti siano corretti, ma cosa accadrà
l’anno seguente? La colonna ANZIANITA conterrà valori errati. Questi
8
Capitolo B
Figura B.7 Un uso scorretto di vincoli.
vincoli su valori dinamici devono essere trattati diversamente: considereremo in un momento successivo gli attributi calcolati.
B.1.4 Osservazioni sui vincoli
Fermiamoci un momento a riflettere sulla differenza tra l’aspetto teorico
e quello pratico nella definizione dei vincoli. I vincoli che abbiamo
espresso sui domini degli attributi possono essere teoricamente sufficienti, ma in concreto non lo sono in quanto non difendono la base di dati da
banali refusi umani nell’immissione dati o da modi diversi di scrivere
una stessa informazione. Inoltre, possiamo attribuire una stessa matricola
a una persona diversa o addirittura immetterne una non esistente. Possiamo scrivere nel campo MANSIONE
“Capo Stazione”
oppure
“Capostazione”
che per il sistema sono dati diversi.
Dobbiamo allora cercare di esprimere vincoli che permettano al sistema di controllare il più possibile la situazione.
Le dipendenze tra gli attributi possono esserci d’aiuto per garantire la
correttezza delle immissioni. Nello schema
COD_FISC COGNOME NOME SESSO DATA_NASCITA
COMUNE_NASCITA
è perfetto dichiarare COD_FISC chiave primaria. La chiave, in questo caso, oltre ad essere univoca, può essere dedotta e dunque, il sistema può
controllare che il valore calcolato sia uguale a quello immesso prima di
Sperimentazioni con le basi di dati
9
accettare che l’ennupla entri a far parte della tabella. Questo è un caso
particolarmente favorevole: è compito del progettista cercare soluzioni
ottimali.
B.2 Le interrogazioni
Torniamo alla struttura della tabella personale, rappresentata in Figura
B.2, per introdurre il linguaggio di interrogazione tramite il quale possiamo estrarre l’informazione che riteniamo utile.
Ipotizziamo di usare la base di dati Ferrovie (presente sul sito).
Per effettuare un’interrogazione, dal menu principale attiviamo l’icona “Query”. Il sistema presenta una serie di scelte la più generale delle
quali è “Crea una query in visualizzazione Struttura”. L’utilizzo di questa funzionalità ci mostra una finestra con l’elenco delle nostre tabelle.
Scegliamo solo la tabella personale e operiamo in modo da ottenere
quanto rappresentato in Figura B.7.
Possiamo memorizzare la query dandole un nome e eseguirla in una
fase successiva oppure possiamo eseguirla immediatamente tramite l’icona Esegui, rappresentata (si veda la Figura B.3) da un punto esclamativo.
Si ottengono tutte le ennuple della tabella. Dal menu Visualizza della stessa figura scegliamo di vedere l’interrogazione espressa in SQL: troveremo
Campo
Figura B.8 Semplice interrogazione.
10
Capitolo B
SELECT personale.*
FROM personale;
Di solito l’utente è portato ad usare l’interfaccia grafica, data la sua immediatezza. Noi, invece, preferiamo presentare le interrogazioni SQL,
per dare al lettore uno strumento di più ampio raggio, e non limitato ad
un solo tipo di interfaccia grafica; il nostro obiettivo è mirato anche ad ottenere una scrittura compatta. Il lettore è invitato a sperimentare sull’interfaccia grafica di Access le interrogazioni presentate.
B.2.1 Le interrogazioni su una tabella
con la clausola WHERE
Cominciamo ad illustrare il linguaggio di interrogazione partendo dalla
struttura semplificata
SELECT lista attributi
FROM tabella
WHERE condizione
La condizione della clausola WHERE può usare gli operatori logici AND
di relazione =, <, >, <= ecc, l’operatore LIKE con i caratteri wildcard per combinare i paragoni e altri operatori.
OR NOT ,
Esempio B.3
Vogliamo visualizzare il cognome degli impiegati che lavorano nella stazione di Pisa.
SELECT personale.COGNOME
FROM personale
WHERE personale.STAZIONE_APP = "Pisa C.le"
La sintassi sopra riportata è quella che Access genera automaticamente
in seguito alle nostre azioni sull’interfaccia grafica. Come si vede, il sistema usa la notazione puntata nometabella.nomecampo per individuare univocamente l’attributo. Lo standard SQL accetta anche la notazione senza
il punto. Nel presente paragrafo useremo quest’ultima perchè i nostri
esempi si svolgono su una sola tabella.
La filosofia del sistema Access assimila le interrogazioni alle viste:
quindi consente di cambiare i risultati delle interrogazioni e, di conseguenza, aggiorna le tabelle primarie da cui essi provengono. Se cambiamo la stazione di appartenenza, nel risultato della query precedente troviamo la modifica nella tabella personale!
Sperimentazioni con le basi di dati
11
Esempio B.4
Vogliamo visualizzare gli impiegati che lavorano nelle stazioni di Pisa e
di Firenze.
SELECT COGNOME, STAZIONE_APP
FROM personale
WHERE (STAZIONE_APP = "Pisa C.le") OR (STAZIONE_ APP = "Firenze");
La condizione WHERE si può anche esprimere con notazione insiemistica
WHERE STAZIONE_APP IN (“Pisa C.le” ,”Firenze”)
Esempio B.5
Vogliamo visualizzare il personale ordinato secondo il “peso” della busta
paga.
Usiamo la clausola ORDER BY e l’opzione DESC (l’opzione ASC darebbe
l’ordinamento opposto):
SELECT *
FROM personale
ORDER BY STIPENDIO + PREMIO_PRODUZIONE
DESC
Esempio B.6
Vogliamo visualizzare le qualifiche del personale in servizio presso la
stazione di Pisa.
SELECT MANSIONE
FROM personale
WHERE STAZIONE_APP="Pisa C.le";
Questa interrogazione produce duplicati che vengono soppressi grazie
alla parola chiave DISTINCT che compare nell’interrogazione seguente.
SELECT DISTINCT MANSIONE
FROM personale
WHERE STAZIONE_APP="Pisa C.le";
Evidenziamo che negli esempi seguenti useremo le funzioni di aggregazione COUNT e SUM . Esistono altre funzioni di aggregazione: MIN , MAX,
AVG . Per il loro uso nella interfaccia grafica occorre attivare il pulsante
Totali evidenziato nella Figura B.3.
12
Capitolo B
Esempio B.7
Si chiede il costo di tutto il personale:
SELECT SUM(STIPENDIO) AS TOTALE_COSTI
FROM personale;
In questo esempio, tramite la funzione SUM abbiamo ottenuto la somma totale sulla colonna STIPENDIO. Poiché il risultato avrebbe avuto
un’intestazione poco significativa, abbiamo usato la parola chiave AS. In
questo
modo
abbiamo
dichiarato
un
nuovo
identificatore
TOTALE_COSTI, di cui il sistema si servirà nel presentare la risposta.
Esempio B.8
Si chiede il numero totale dei dipendenti.
SELECT COUNT(*) AS TOTALE_DIPENDENTI
FROM personale;
B.2.2 Le interrogazioni su una tabella
con la clausola GROUP BY
Presentiamo ora il tipo d’interrogazione:
SELECT attributo, aggregazione(attributo)
FROM tabella
GROUP BY attributo
HAVING condizione
Per ottenere il risultato si procede raggruppando le ennuple con lo
stesso valore dell’attributo indicato nella clausola GROUP BY . La clausola
HAVING qualifica gli attributi che devono appartenere al risultato.
Esempio B.9
Si chiedono le mansioni e il numero dei dipendenti che le svolgono quando questi siano più di due.
SELECT MANSIONE, COUNT(MANSIONE) AS QUANTITA
FROM personale
GROUP BY MANSIONE
HAVING COUNT(MANSIONE)>2;
Sperimentazioni con le basi di dati
13
B.2.3 Le interrogazioni annidate
Presentiamo adesso un’interrogazione in cui la clausola WHERE contiene
un’altra interrogazione che ha una sola ennupla in risposta.
Esempio B.10
Chiediamo quali sono gli impiegati che hanno le stesse mansioni di Luca
(matricola M34587).
SELECT NOME, MANSIONE
FROM personale
WHERE MANSIONE=(
SELECT MANSIONE
FROM personale
WHERE MATRICOLA=M34587)
Se la sotto-interrogazione contiene più valori occorre indicare se il
confronto attuale risulta vero con tutti ( ALL ) o con almeno uno ( ANY ) di
essi. Vedremo più avanti come evitare l’annidamento delle interrogazioni
che, nonostante l’approccio intuitivo, ha tuttavia un’influenza negativa
sulla performance del sistema.
B.2.4 Attributi calcolati
Mostriamo adesso come le interrogazioni ci vengono incontro nel risolvere il problema degli attributi calcolati, esposto alla fine del Paragrafo
B.1.3. Abbiamo già notato che, se si vuole aggiungere nella tabella personale un nuovo campo che rappresenti l’anzianità nell’azienda dei suoi
impiegati, occorre calcolarne il valore in funzione della data attuale. Poiché nella SELECT possiamo aggiungere nuovi nomi di campi creiamo l’interrogazione seguente
SELECT *, DateDiff(“aaaa”;DATA_ASSUNZIONE; Date()) AS ANZIANITA
FROM personale;
Nell’interfaccia grafica (vedi Figura B.7) la condizione espressa con
[ANZIANITA]=DateDiff(“aaaa”;[DATA_ASSUNZIONE];Date())
si scrive in corrispondenza dell’etichetta Campo.
14
Capitolo B
Figura B.10 Una istanza della tabella modelli_e_tratte.
B.2.5 Un’interrogazione su due tabelle
Il nostro lavoro si è svolto fino a questo momento con una sola tabella.
Consideriamo adesso le due tabelle rappresentate in Figura B.8 e Figura
B.9
La tabella macchinisti_e_modelli di Figura B.8 indica le matricole dei
macchinisti della compagnia ferroviaria con l’abilitazione che hanno conseguito, ovvero con l’elenco delle locomotive che possono guidare.
La tabella modelli_e_tratte di Figura B.9 indica quali tipi di locomotive
possono essere impiegati su una tratta.
Supponiamo adesso che la compagnia voglia conoscere le matricole
dei macchinisti cui può affidare la responsabilità di guida nelle varie tratte. Per fare ciò serve un’operazione che si chiama natural join (o giunzione
naturale), che ora passiamo a descrivere.
Figura B.9 Una istanza della tabella macchinisti_e_modelli.
Sperimentazioni con le basi di dati
15
Si inizia costruendo un nuovo schema con tutti gli attributi di entrambi gli schemi delle due tabelle in join. Nel nostro caso, poiché gli attributi
dei due schemi sono (MATRICOLA TIPO) e (TIPO NUM_TRATTA), la
loro unione è data da (MATRICOLA TIPO NUM_TRATTA). Le colonne
che governano il join sono le colonne con lo stesso nome nelle due tabelle; nel nostro esempio si tratta dell’attributo TIPO. Si costruiscono poi i
valori della nuova tabella “prolungando” ogni riga della prima tabella
con quelle righe della seconda per cui i valori sugli attributi comuni coincidono. Qualsiasi riga contenente sulle colonne di join valori non presenti
nell’altra tabella, resta esclusa dal join.
Si noti che, come per tutte le operazioni, la tabella generata dal join
non viene memorizzata in nessuna memoria permanente del computer e
quindi deve essere calcolata ogni qual volta la si vuole visualizzare.
Vediamo come si esprime in SQL il join naturale. Innanzitutto questo
è possibile perché la clausola FROM permette l’uso di più tabelle, i cui nomi sono separati dalla virgola. La notazione puntata è adesso essenziale
per consentirci di distinguere gli attributi delle due tabelle. La condizione
che si esprime nella clausola WHERE contiene la condizione di join. Nel
nostro esempio scriviamo:
SELECT mm.*, mlt.NUM_TRATTA
FROM modelli_e_tratte AS mlt,
macchinisti_e_modelli AS mm
WHERE mm.TIPO=mlt.TIPO;
In questa interrogazione stiamo usando i sinonimi AS per le tabelle.
Ciò permette di definire nuovi nomi per adattarli alle proprie esigenze
anche senza creare duplicati delle tabelle.
Il risultato della giunzione, è rappresentato nella Figura B.10:
Figura B.11 Join.
16
Capitolo B
A parte l’ovvia considerazione che non è bene avere locomotive che
non possono essere guidate dai macchinisti della propria compagnia (in
questo caso la ETR470), si osservi come questa operazione permette di navigare tra le tabelle per arrivare a rispondere alle interrogazioni che ci interessano.
È da notare che se la tabella modelli_e_tratte si presentasse così:
l’operazione di join restituirebbe una tabella vuota, ovvero senza righe,
che si presenta all’utente così:
Nel qual caso sarebbe legittimo nutrire molti dubbi sulla siffatta compagnia...
Per definizione, nel caso estremo in cui le tabelle non hanno attributi
in comune, il natural join risulta costituito da tutte le possibili combinazioni delle righe delle tabelle.
Si può notare come la struttura della SELECT consente di definire la
giunzione su attributi con nomi diversi, realizzando un’operazione più
ampia del natural join che abbiamo prima definito.
L’operazione di join è il punto di forza del modello relazionale. Essa
dà all’utente la possibilità di navigare a suo piacimento nella base, di dati
e non solo lungo strade già previste in fase di definizione, come accade in
altri modelli.
Ci siamo limitati a trattare in dettaglio il natural join, ma esistono altri
tipi di join, sui quali è stata prodotta una vasta letteratura. Nelle query
costruite con l’interfaccia grafica di default Access preferisce, come vedremo più avanti, generare INNER JOIN. Da un punto di vista formale,
questo differisce dal natural join, in quanto non esprime la condizione
nella clausola WHERE, bensì nella clausola FROM .
B.3 Normalizzazione degli schemi
Prendono il nome di forme normali alcune regole di costruzione delle tabelle relazionali che garantiscono certe coerenze sui dati. Infatti una tabella, per sua natura, può essere composta da attributi scoordinati tra loro o viceversa troppo dipendenti tra loro. Le forme normali cercano di
guidare il progettista nel superamento di realizzazioni scorrette.
Sperimentazioni con le basi di dati
17
B.3.1 Prima forma normale
Nella definizione degli schemi occorre anche avere ben chiaro quali sono
le informazioni che dobbiamo trarre dalla base di dati. Prendiamo infatti
in esame la tabella abilitazione di Figura B.11, dove sono riportati i piloti e
modelli di locomotive che possono guidare.
Figura B.12
Supponiamo di volere l’elenco di tutti i cognomi dei piloti.
A causa dello schema assegnato, non possiamo formulare una tale interrogazione al sistema. Si esprime questa situazione dicendo che, nel
modello relazionale, i valori che gli attributi assumono sono atomici, non
decomponibili. In altre parole, i valori non sono composti da insiemi di
valori. Certo, un valore che è atomico in un’applicazione può non esserlo
in un’altra. La regola generale è che un valore è non atomico se l’applicazione dovrà trattare con una parte semanticamente significativa di esso.
Nel nostro caso specifico, se pensiamo che potremmo aver necessità di conoscere i cognomi, dovremo pensare allo schema
abilitazione (COGNOME NOME TIPO )
e su questo potremo effettuare l’interrogazione desiderata.
Uno schema che rispetta la regola di atomicità si dice in prima forma
normale.
B.3.2 Altre forme normali
Proviamo a chiederci per quale motivo la compagnia decida di mantenere
le due tabelle macchinisti_e_modelli e modelli_e_tratte viste nel Paragrafo
B.2.5, e non la sola
assegnabilità (MATRICOLA TIPO NUM_TRATTA )
ottenuta effettuando il join tra di esse. Sembra infatti che assegnabilità sintetizzi in modo più immediato il legame tra i macchinisti e le tratte ma
18
Capitolo B
facciamo le seguenti osservazioni. La chiave primaria deve essere costituita da tutti gli attributi, per permettere ad un macchinista di avere più
abilitazioni, ad un TIPO di locomotiva di essere guidato da più macchinisti e ad ogni macchinista, abilitato alla guida di un tipo di locomotiva, di
percorrere più tratte. Con questo vincolo è impossibile inserire in una tale
tabella l'elettrotreno ETR470 di proprietà della compagnia poichè, nella
riga che la riguarderebbe, il valore dell’attributo MATRICOLA (dell’inesistente macchinista abilitato a guidarla) sarebbe NULL . Analogamente, sarebbe impossibile inserire ulteriori abilitazioni dei macchinisti per locomotive non possedute attualmente dalla compagnia, in quanto TIPO e
NUM_TRATTA avrebbero valore NULL. Il cambiamento di un tipo di locomotiva per una tratta implicherebbe quindi un complesso aggiornamento
della tabella stessa, perchè riguarderebbe tutti i macchinisti che erano assegnati a quella tratta sulla precedente locomotiva. Lo spreco di memoria
che si vede in questo limitato esempio (vedi Figura B.12) diventerebbe
massiccio in una situazione reale, con centinaia di macchinisti.
Figura B.13
Questi problemi sono stati ampiamente trattati dagli studiosi di teoria
relazionale ed hanno portato all’identificazione delle forme normali. Non
è il caso di esporle tutte in questo contesto. Piuttosto, torniamo con altre
parole ad osservare che, per loro natura, le tabelle non rappresentano solo concettualizzazioni. Con conseguenze di solito disastrose sulla base di
dati. Si raccomanda allora di farsi guidare dal modello concettuale e dalle
regole di traduzione tra i modelli, anche se queste ultime non sono da intendersi in senso totalmente algoritmico.
L’operazione di join consente, come possiamo intuire, di memorizzare
tabelle più piccole, più regolari da cui ottenere, al momento opportuno, la
tabella con tutti gli attributi che ci occorrono. Il frammentare (ovvero so-
Sperimentazioni con le basi di dati
19
stituire una tabella con altre più piccole) non è però una panacea e diventa eccessivo quando nessun join permette di ricostruire l’informazione
nella sua interezza. Comunque, come accade spesso in informatica, una
buona soluzione è un compromesso tra il tempo di calcolo e l’occupazione di memoria. Così, se l’interesse è maggiormente rivolto al primo, può
essere forse più conveniente, nonostante i problemi segnalati, mantenere
tabelle più grandi.
B.4 Le relazioni tra due tabelle
Nel Capitolo 6 abbiamo parlato della definizione dello schema logico riferendoci ad una sola classe del modello concettuale. Ma le classi di un
progetto sono solitamente molto più numerose e legate da associazioni
semantiche. Vediamo adesso la loro classificazione e successivamente la
loro rappresentazione nel modello logico.
B.4.1 Modello concettuale
Relazione 1 a n
Per dare l’esempio di una tale relazione, supponiamo che la compagnia
memorizzi alcune caratteristiche delle stazioni: numero di binari, di biglietterie, indicazioni di presenza officina e di servizio auto al seguito. In
questo caso, le stazioni devono essere una classe, e il semplice attributo
“stazione di appartenenza” della classe personale si trasforma in un’associazione tra classi, che nel diagramma di Chen si rappresenta come in Figura B.13.
La freccia a punta unica indica che ogni persona fa capo ad una sola
stazione, quella a doppia punta indica invece che un’arbitraria stazione
può avere più dipendenti.
20
Capitolo B
personale
stazioni
Figura B.14 Esempio di relazione 1 a n.
Relazione n a m
Supponiamo che l’azienda mantenga sia l’anagrafe dei macchinisti sia le
caratteristische delle locomotive che possiede. È importante che associ ad
ogni macchinista l’elenco delle locomotive che può guidare. Si tratta di
una relazione n a m che si indica come nella Figura B.14. Le doppie frecce
indicano che ogni macchinista può guidare più modelli di locomotive e
che un modello di locomotiva può essere guidato da più macchinisti.
macchinisti
modelli
locomotive
Figura B.15
Sperimentazioni con le basi di dati
21
Sottoclasse
Questo caso può essere esemplificato ritenendo che per la classe personale
occorra, quando ci si riferisce ai macchinisti, tenere in considerazione il
numero di ore di esperienza e i tipi di abilitazioni di guida possedute (informazioni totalmente inutili per l’altra parte del personale).
La situazione è rappresentata nel diagramma di Chen in Figura B.15, e
si dice che macchinisti è una sottoclasse di personale. Questa sottoclasse è caratterizzata da un attributo in più ( ESPERIENZA ) e da un’associazione in
più: i modelli di locomotiva che ciascuna entità macchinista è abilitata a
guidare. Si dice che l’associazione tra modelli locomotive e macchinisti è di tipo
n a m per indicare che ogni macchinista può guidare più modelli e ogni
modello può essere guidato da più macchinisti.
personale
ESPERIENZA
macchinisti
Figura B.16 Esempio di sottoclasse
Relazione riflessiva
Analizziamo ora un caso particolare, quello in cui si considera il responsabile come elemento della classe personale. Questo, nel diagramma di
Chen si esprime creando un arco come in Figura B.16.
personale
Figura B.17 Esempio di associazione riflessiva.
22
Capitolo B
B.4.2 Modello logico
Chiediamoci adesso come possiamo rappresentare tutte queste situazioni
nel modello relazionale. Qui ci viene incontro l’operazione di join che abbiamo già presentato.
Relazione 1 a n
Cominciamo con il primo caso. Alla tabella personale aggiungiamo un
nuovo attributo che è la chiave della tabella stazioni. Poiché ogni dipendente ha una sola sede, sarà ben definito il valore corretto da assegnare a
quel campo, valore che nella tabella stazioni fornisce il punto d’attracco
dell’operazione di join.
Il linguaggio grafico di Access, mostrato in Figura B.17, è molto
espressivo.
Il segmento che lega fra loro gli attributi collegati porta il segno 1 ad
un estremo, il segno × all’altro, indicando che l’associazione è uno a molti.
In generale, per rappresentare un’associazione 1 a n (×) si procede analogamente a quanto abbiamo appena fatto. Si opera in modo che la tabella
in associazione univoca contenga tutti gli attributi che fanno da chiave
primaria per l’altra. Tale insieme di attributi è denominato chiave esterna
(in inglese foreign key) e viene indicata la tabella di riferimento: o graficamente, come in Figura B.17, o in SQL, con la parola chiave REFERENCES.
La dichiarazione di una chiave esterna consente al sistema di controllare che ogni inserimento nella tabella personale di un valore per il campo
STAZIONE_APP sia già presente nel campo NOME della tabella stazioni.
Per modellare macchinisti come sottoclasse di personale possiamo pensare
ad una tabella macchinisti costituita dal campo ESPERIENZA e dal campo
MATRICOLA, i cui valori sono quelli del personale assunto con MAN-
Figura B.18
Sperimentazioni con le basi di dati
23
SIONE macchinista. In questo modo MATRICOLA è contemporaneamente chiave primaria e chiave esterna in riferimento alla tabella personale.
CREATE TABLE macchinisti (
MATRICOLA CHAR (15),
ESPERIENZA DECIMAL(8,2),
PRIMARY KEY(MATRICOLA)
FOREIGN KEY (MATRICOLA) REFERENCES personale)
Le modalità di inserimento dati nelle sottoclassi devono essere opportunatamente controllate: è necessario che nella tabella sottoclasse
dell’esempio sia possibile inserire solo macchinisti. Questa condizione la
esprimeremo tramite i vincoli di integrità referenziale.
Relazione n a m
Modelliamo adesso l’associazione tra la classe macchinisti e la classe modelli.
A tale scopo definiamo una nuova tabella, per la quale scegliamo il nome
macchinisti_e_modelli. Le chiavi primarie delle due tabelle macchinisti e
modelli sono gli unici suoi attributi e fanno da chiave esterna rispettivamente per la prima e per la seconda tabella. Inoltre, esse ne sono la chiave
primaria.
CREATE TABLE macchinisti_e_modelli (
MATRICOLA CHAR (15),
ID_LOCOMOTIVA CHAR (15),
PRIMARY KEY(MATRICOLA, ID_LOCOMOTIVA)
FOREIGN KEY (MATRICOLA) REFERENCES macchinisti,
FOREIGN KEY (ID_LOCOMOTIVA) REFERENCES modelli)
Relazione riflessiva
Per modellare l’associazione riflessiva “uno a molti” tra personale e personale
che riferisce al dirigente immediato, usiamo l’attributo RESPONSABILE
come chiave esterna per la stessa tabella. Per creare graficamente la chiave esterna, come si vede in Figura B.18, dobbiamo usare anche l’alias della tabella in questione.
24
Capitolo B
Figura B.19 Associazione riflessiva in Access.
B.4.3 La chiave esterna in Access
Per definire i legami tra tabelle, si usa il pulsante Relazioni, evidenziato in
Figura B.1. Il sistema ci presenta l’elenco delle tabelle tra le quali possiamo scegliere quelle che ci interessano. Poi chiusa la finestra Visualizza tabelle, effettuiamo la dichiarazione di chiave esterna graficamente, “trascinando gli attributi” di collegamento delle due tabelle e scegliendo nella
tabella di dialogo visualizzata dal sistema Applica integrità referenziale.
L’elenco di scelte che il sistema successivamente permette serve a stabilire i vincoli di cui parleremo nel paragrafo seguente.
B.5 Vincoli esprimibili su più tabelle
Distinguiamo i vincoli in: vincoli di integrità referenziale, di aggiornamento e di cancellazione.
B.5.1 Vincolo di integrità referenziale
Quando definiamo la chiave esterna, il sistema controlla errori di immissione verificando che l’inserimento effettuato sia coerente con la tabella
di riferimento.
I controlli possono essere ancora più stringenti, permettendo esclusivamente una scelta da un elenco di dati predefiniti.
In pratica, facciamo riferimento al pannello inferiore della Figura B.2.
Scegliamo l’etichetta Ricerca ed effettuiamo le seguenti scelte:
Sperimentazioni con le basi di dati
Visualizza controllo
Tipo origine riga
Solo in elenco
25
Casella combinata
Tabella/query
Sì
Nel campo Origine riga occorre inserire la query opportuna. Come risultato, il sistema in fase di immissione dati permette solo di prelevarli da
un menu a tendina.
Esempio B.11
Nella tabella macchinisti_e_modelli, sia i nomi delle locomotive da assegnare ad un pilota sia le matricole dei piloti devono essere presi da liste
predefinite. La prima è una tabella con i tipi delle locomotive di cui la società ferroviaria può disporre, la seconda si ottiene dalla tabella personale.
La query per estrarre gli identificativi dei macchinisti è la seguente:
SELECT personale.MATRICOLA
FROM personale
WHERE personale.MANSIONE="macchinista";
È questa l’interrogazione che scriviamo perché il sistema sia in grado
di prelevare direttamente i dati da una tabella che può cambiare nel tempo. Il risultato è visibile in Figura B.19. Si nota che il dato da inserire è
prelevabile da un menu a tendina.
Figura B.20
B.5.2 Vincoli di aggiornamento
e vincoli di cancellazione
Quando si definisce un vincolo di integrità referenziale, si possono definire anche le azioni che il sistema deve compiere in caso di aggiornamento e di cancellazione su ennuple correlate.
La parola CASCADE serve a segnalare la propagazione della modifica.
26
Capitolo B
Ad esempio, una piena espressione del vincolo per cui tutti i macchinisti devono far parte del personale, impone che la cancellazione di un
macchinista dalla tabella personale abbia come conseguenza la cancellazione di tutte le sue mansioni di pilota. In concreto, dovranno essere cancellate, nella tabella macchinisti_e_modelli, tutte le ennuple che contengono i suoi identificativi.
Attenzione però ad un superficiale uso di CASCADE: nella nostra base
di dati potremmo trovare cancellazioni indesiderate su tabelle distanti da
quella su cui abbiamo cominciato.
La specifica RESTRICT segnala la restrizione delle modifiche: non possiamo eliminare una ennupla se essa è riferita da un’altra tabella. Ad
esempio, possiamo imporre che sulla tabella mansioni non sia possibile
eliminare una mansione se quest’ultima è associata a qualche dipendente.
Ci sono altre specifiche interessanti come SET NULL, SET DEFAULT e NO
ACTION , di cui possiamo già intuire il significato, ma rimandiamo ai manuali specializzati.
B.5.3 Il trigger
Possiamo notare che i vincoli referenziali eseguono un’azione ( RESTRICT,
CASCADE ecc.) quando si verifica un evento di aggiornamento o cancellazione. I trigger sono una estensione di questo paradigma in cui, al verificarsi dell’evento di base, l’azione successiva del sistema non è indifferenziata, ma invece regolata da qualche condizione. Sono infatti desiderabili
funzioni che ci permettano di controllare situazioni senza scrivere complessi blocchi di codice. Ad esempio, si potrebbe definire un trigger per
avvisare l’azienda che troppi macchinisti stanno andando in pensione.
Comunque, non esistono in questo momento standard condivisi di definizione e di ordine nell’esecuzione delle regole dei trigger.
B.6 Interrogazioni su una base di dati
Prendiamo in esame lo schema visualizzato in Figura B.20 ed effettuiamo
le seguenti interrogazioni.
Esempio B.12
Chiediamo le ore di esperienza di tutti i macchinisti. A questa interrogazione possiamo rispondere usando il join naturale che esprimiamo nel
modo seguente:
SELECT personale.COGNOME,macchinisti.ORE_DI_ESPERIENZA
FROM personale, macchinisti
WHERE personale.MATRICOLA=macchinisti.MATRICOLA;
Sperimentazioni con le basi di dati
27
Figura B.21 Lo schema su cui effettuiamo le interrogazioni.
In alternativa, possiamo usare l’interfaccia grafica. Dalla finestra Scegli
tabelle scegliamo le tabelle macchinisti e personale. Il sistema segnala automaticamente il legame di join. Poi operiamo nel pannello inferiore le seguenti scelte:
Campo
COGNOME
ORE_DI_ESPERIENZA
MATRICOLA
Tabella
personale
macchinisti
personale
Visualizza
✓
✓
Condizione
=macchinisti.MATRICOLA
Se visualizziamo la soluzione SQL offerta da Access, troviamo:
SELECT personale.COGNOME,macchinisti.ORE_DI_ESPERIENZA
FROM personale INNER JOIN macchinisti
ON personale.MATRICOLA = macchinisti.MATRICOLA;
28
Capitolo B
Come si vede, Access ha usato automaticamente l’operatore INNER
e ci ha inoltre esonerato dallo scrivere il noioso elenco degli attributi
coinvolti. Quest’ultima è la caratteristica più interessante dell’interfaccia
grafica. Gli utenti sono fortemente invitati a usarla ed eventualmente a
completare e/o modificare l’interrogazione automatica usando lo standard SQL quando lo ritengono opportuno.
JOIN
Esempio B.13
Cerchiamo i cognomi dei macchinisti che possono guidare su locomotive
elettriche.
L’interrogazione coinvolge tutte le tabelle illustrate nella Figura B.20
tranne personale_1, ma non è complessa da rappresentare perché, al solito,
il sistema ci viene incontro scrivendo la lunga condizione di join.
Si scelgono tutte le tabelle e si effettuano le seguenti scelte:
Campo
COGNOME
ELETTRICA
Tabella
personale
modelli
Visualizza
✓
✓
Condizione
TRUE
Esempio B.14
Cerchiamo il cognome dell'immediato responsabile di Giuseppe Rossi.
Operiamo come nei casi precedenti, usando due volte la tabella personale. Il sistema crea l’alias personale_1, ma ignora il legame precedentemente costruito tra la chiave RESPONSABILE della tabella personale_1 e
l’attributo MATRICOLA della tabella personale. Dobbiamo dunque ricrearlo e poi effettuare le seguenti scelte:
Campo
COGNOME
NOME
COGNOME
Tabella
personale
personale
personale_1
✓
Visualizza
Condizione
"Rossi"
"Giuseppe"
Esempio B.15
Cerchiamo i nomi dei responsabili dei macchinisti che possono guidare
l’ETR460.
Sperimentazioni con le basi di dati
29
Per rispondere a questa interrogazione, occorre scegliere le tabelle,
macchinisti, macchinisti_modelli e due volte la tabella personale (come nel
caso precedente). Effettuiamo le seguenti scelte:
Campo
COGNOME
COGNOME
TIPO
Tabella
personale
personale_1
macchinisti_e_modelli
Visualizza
✓
✓
Condizione
“ETR460”
Esempio B.16
Cerchiamo il cognome del personale che ha il responsabile non appartenente alla propria stazione. L’interrogazione in SQL risulta essere:
Campo
STAZIONE_APP
COGNOME
[CAPO]:COGNOME
Tabella
personale
personale
personale_1
Visualizza
✓
✓
Condizione
<>personale_1.STAZIONE_APP
“ETR460”