Sperimentazioni con le basi di dati - apogeonline.com · prima fase, e supponiamo che il...

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 re-sidenti nella memoria di massa. Affinché ciò avvenga, occorre però che il progettista esamini con attenzione il problema a lui sottoposto e lo espri-ma 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 mo-strato 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 ri-guardano non si sono verificati per “colpa del computer” e spesso nean-che per colpa di chi lo utilizza: possono derivare invece da una program-mazione superficiale di strumenti sofisticati sì, ma, proprio per questo, da usare con attenzione.

2 Capitolo B

B.1 DDL in AccessUn 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 pos-sa 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 tabelleScegliamo 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 supe-riore è 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 indica-

Figura B.1

Relazioni

Sperimentazioni con le basi di dati 3

to la matricola del responsabile immediato della persona presa in consi-derazione.

Il triangolino nero, che nella figura in discussione è posto sulla secon-da riga, evidenzia che le “Proprietà campo” della parte inferiore riguar-dano 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 indicato-re). Non abbiamo definito l’attributo MATRICOLA di tipo numerico, in quanto si tratta solo di un codice su cui non avrà senso fare operazioni al-gebriche. Poiché abbiamo dichiarato tale campo “chiave primaria” il si-stema ci impedirà di ripetere un valore già immesso. Inoltre, come tutti gli attributi che fanno parte di una chiave, MATRICOLA non può assu-mere il valore NULL. Si tratta di un segnale di valore mancante (non cono-sciuto) o inesistente (senza significato per la particolare ennupla). In con-creto, 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 conse-guenza sulla logica che, invece di essere booleana, come siamo abituati

Figura B.2 Interfaccia per la definizione delle tabelle.

ProprietàIndiciChiave primaria

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 va-lore NULL, che potrebbe derivare da un errore (il campo non viene riem-pito) durante l’immissione dei dati. Abbiamo scelto il valore “No” per la proprietà Consenti lunghezza zero, che altrimenti permetterebbe l’inseri-mento di “” (o di una sequenza di spazi), visualizzato dal sistema come nel caso del valore NULL.

Degli attributi restanti mettiamo in evidenza il campo RESPONSABI-LE, in quanto dobbiamo permettere il valore NULL. Occorre infatti preve-dere 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 visualiz-zazione Struttura. Tra le finestre generate adesso dal sistema occorre chiu-dere quella dal nome Mostra tabella per poi scegliere Visualizzazione SQLdal 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));

Figura B.3 Menu per la creazione delle query.

EseguiSQL


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 in-fatti una dal nome “nometabella”.

B.1.2 Definizione degli indiciPer 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. Sa-ranno 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 Indicievidenziato 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 di-chiariamo indici, proprio come noi facciamo con l’indice analitico: costru-isce 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 vincoliLimitiamoci in questo paragrafo a una sola tabella e vediamo come si de-finiscono in Access i vincoli:■ di chiave;■ sui singoli attributi;■ su più attributi della stessa tabella.

Vincoli di chiaveLa 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 Win-dows 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 essen-do 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 impo-stando a “Sí” la proprietà Univoco dell’indice. Nel nostro esempio di Figu-ra 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 convenien-te dichiararla candidata e sostituirla con una chiave ad attributo singolo sfruttando eventualmente il contatore offerto dal sistema.

Vincoli sui singoli attributiOsserviamo che è necessario stabilire il tipo di valore previsto per un campo che viene predefinito testuale dal sistema. Possiamo essere più re-strittivi usando il campo Valido se nel pannello inferiore della Figura B.2.

Esempio B.1Uno 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:


Vincoli su più attributi della stessa tabella Si possono esplicitare altri tipi di legami tra più attributi di una stessa ta-bella, 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 oppor-tuna.

Esempio B.2La 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 effettua-re 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 immis-sione dati. Ma ci sono casi alquanto particolari. Ipotizziamo di aggiunge-re un campo ANZIANITA per esprimere l’età lavorativa. Dobbiamo allo-ra 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

Figura B.6 Interfaccia per la definizione delle proprietà della tabella.

8 Capitolo B

vincoli su valori dinamici devono essere trattati diversamente: considere-remo in un momento successivo gli attributi calcolati.

B.1.4 Osservazioni sui vincoliFermiamoci 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 sufficien-ti, 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. Possia-mo scrivere nel campo MANSIONE

“Capo Stazione”

oppure

“Capostazione”

che per il sistema sono dati diversi.Dobbiamo allora cercare di esprimere vincoli che permettano al siste-

ma 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 ca-so, oltre ad essere univoca, può essere dedotta e dunque, il sistema può controllare che il valore calcolato sia uguale a quello immesso prima di

Figura B.7 Un uso scorretto di vincoli.


accettare che l’ennupla entri a far parte della tabella. Questo è un caso particolarmente favorevole: è compito del progettista cercare soluzioni ottimali.

B.2 Le interrogazioniTorniamo alla struttura della tabella personale, rappresentata in Figura B.2, per introdurre il linguaggio di interrogazione tramite il quale possia-mo 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’ico-

na “Query”. Il sistema presenta una serie di scelte la più generale delle quali è “Crea una query in visualizzazione Struttura”. L’utilizzo di que-sta 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’ico-na Esegui, rappresentata (si veda la Figura B.3) da un punto esclamativo. Si ottengono tutte le ennuple della tabella. Dal menu Visualizza della stes-sa figura scegliamo di vedere l’interrogazione espressa in SQL: troveremo

Figura B.8 Semplice interrogazione.

Campo

10 Capitolo B

SELECT personale.*FROM personale;

Di solito l’utente è portato ad usare l’interfaccia grafica, data la sua im-mediatezza. 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 ot-tenere una scrittura compatta. Il lettore è invitato a sperimentare sull’in-terfaccia grafica di Access le interrogazioni presentate.

B.2.1 Le interrogazioni su una tabella con la clausola WHERECominciamo ad illustrare il linguaggio di interrogazione partendo dalla struttura semplificata

SELECT lista attributiFROM tabellaWHERE condizione

La condizione della clausola WHERE può usare gli operatori logici ANDOR NOT, di relazione =, <, >, <= ecc, l’operatore LIKE con i caratteri wild-card per combinare i paragoni e altri operatori.

Esempio B.3Vogliamo visualizzare il cognome degli impiegati che lavorano nella sta-zione di Pisa.

SELECT personale.COGNOMEFROM personaleWHERE 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 siste-ma usa la notazione puntata nometabella.nomecampo per individuare uni-vocamente 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 conse-guenza, aggiorna le tabelle primarie da cui essi provengono. Se cambia-mo la stazione di appartenenza, nel risultato della query precedente tro-viamo la modifica nella tabella personale!


Esempio B.4Vogliamo visualizzare gli impiegati che lavorano nelle stazioni di Pisa e di Firenze.

SELECT COGNOME, STAZIONE_APPFROM personaleWHERE (STAZIONE_APP = "Pisa C.le") OR (STAZIONE_ APP = "Firenze");

La condizione WHERE si può anche esprimere con notazione insiemisti-ca

WHERE STAZIONE_APP IN (“Pisa C.le” ,”Firenze”)

Esempio B.5Vogliamo 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 personaleORDER BY STIPENDIO + PREMIO_PRODUZIONEDESC

Esempio B.6Vogliamo visualizzare le qualifiche del personale in servizio presso la stazione di Pisa.

SELECT MANSIONEFROM personaleWHERE STAZIONE_APP="Pisa C.le";

Questa interrogazione produce duplicati che vengono soppressi grazie alla parola chiave DISTINCT che compare nell’interrogazione seguente.

SELECT DISTINCT MANSIONEFROM personaleWHERE STAZIONE_APP="Pisa C.le";

Evidenziamo che negli esempi seguenti useremo le funzioni di aggre-gazione 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.7Si chiede il costo di tutto il personale:

SELECT SUM(STIPENDIO) AS TOTALE_COSTIFROM personale;

In questo esempio, tramite la funzione SUM abbiamo ottenuto la som-ma 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.8Si chiede il numero totale dei dipendenti.

SELECT COUNT(*) AS TOTALE_DIPENDENTIFROM personale;

B.2.2 Le interrogazioni su una tabella con la clausola GROUP BYPresentiamo ora il tipo d’interrogazione:

SELECT attributo, aggregazione(attributo)FROM tabellaGROUP BY attributoHAVING 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.9Si chiedono le mansioni e il numero dei dipendenti che le svolgono quan-do questi siano più di due.

SELECT MANSIONE, COUNT(MANSIONE) AS QUANTITAFROM personaleGROUP BY MANSIONEHAVING COUNT(MANSIONE)>2;


B.2.3 Le interrogazioni annidatePresentiamo adesso un’interrogazione in cui la clausola WHERE contiene un’altra interrogazione che ha una sola ennupla in risposta.

Esempio B.10Chiediamo quali sono gli impiegati che hanno le stesse mansioni di Luca (matricola M34587).

SELECT NOME, MANSIONEFROM personaleWHERE MANSIONE=(

SELECT MANSIONEFROM personaleWHERE 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 calcolatiMostriamo adesso come le interrogazioni ci vengono incontro nel risolve-re il problema degli attributi calcolati, esposto alla fine del Paragrafo B.1.3. Abbiamo già notato che, se si vuole aggiungere nella tabella perso-nale un nuovo campo che rappresenti l’anzianità nell’azienda dei suoi impiegati, occorre calcolarne il valore in funzione della data attuale. Poi-ché nella SELECT possiamo aggiungere nuovi nomi di campi creiamo l’in-terrogazione seguente

SELECT *, DateDiff(“aaaa”;DATA_ASSUNZIONE; Date()) AS ANZIANITAFROM 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

B.2.5 Un’interrogazione su due tabelleIl 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 con-seguito, 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 trat-te. 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.

Figura B.10 Una istanza della tabella modelli_e_tratte.


Si inizia costruendo un nuovo schema con tutti gli attributi di entram-bi 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 tabel-le; 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 coin-cidono. 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 no-mi 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_TRATTAFROM modelli_e_tratte AS mlt,

macchinisti_e_modelli AS mmWHERE 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 na-vigare tra le tabelle per arrivare a rispondere alle interrogazioni che ci in-teressano.

È 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 com-pagnia...

Per definizione, nel caso estremo in cui le tabelle non hanno attributi in comune, il natural join risulta costituito da tutte le possibili combina-zioni 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 ve-dremo 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 schemiPrendono il nome di forme normali alcune regole di costruzione delle ta-belle relazionali che garantiscono certe coerenze sui dati. Infatti una ta-bella, per sua natura, può essere composta da attributi scoordinati tra lo-ro o viceversa troppo dipendenti tra loro. Le forme normali cercano di guidare il progettista nel superamento di realizzazioni scorrette.


B.3.1 Prima forma normaleNella 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.

Supponiamo di volere l’elenco di tutti i cognomi dei piloti.A causa dello schema assegnato, non possiamo formulare una tale in-

terrogazione 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’applica-zione dovrà trattare con una parte semanticamente significativa di esso. Nel nostro caso specifico, se pensiamo che potremmo aver necessità di co-noscere 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 normaliProviamo 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à sin-tetizzi in modo più immediato il legame tra i macchinisti e le tratte ma

Figura B.12

18 Capitolo B

facciamo le seguenti osservazioni. La chiave primaria deve essere costitu-ita da tutti gli attributi, per permettere ad un macchinista di avere più abilitazioni, ad un TIPO di locomotiva di essere guidato da più macchini-sti 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’inesi-stente macchinista abilitato a guidarla) sarebbe NULL. Analogamente, sa-rebbe impossibile inserire ulteriori abilitazioni dei macchinisti per loco-motive non possedute attualmente dalla compagnia, in quanto TIPO e NUM_TRATTA avrebbero valore NULL. Il cambiamento di un tipo di loco-motiva per una tratta implicherebbe quindi un complesso aggiornamento della tabella stessa, perchè riguarderebbe tutti i macchinisti che erano as-segnati 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.

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 so-lo 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 in-tendersi 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-

Figura B.13


stituire una tabella con altre più piccole) non è però una panacea e diven-ta 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’occupazio-ne 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 tabelleNel Capitolo 6 abbiamo parlato della definizione dello schema logico ri-ferendoci 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 nPer dare l’esempio di una tale relazione, supponiamo che la compagnia memorizzi alcune caratteristiche delle stazioni: numero di binari, di bi-glietterie, 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’asso-ciazione tra classi, che nel diagramma di Chen si rappresenta come in Fi-gura 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

Relazione n a mSupponiamo 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.

Figura B.14 Esempio di relazione 1 a n.

personale

stazioni

Figura B.15

macchinisti

modelli locomotive


Sottoclasse Questo caso può essere esemplificato ritenendo che per la classe personaleoccorra, quando ci si riferisce ai macchinisti, tenere in considerazione il numero di ore di esperienza e i tipi di abilitazioni di guida possedute (in-formazioni 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 è ca-ratterizzata 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.

Relazione riflessivaAnalizziamo ora un caso particolare, quello in cui si considera il respon-sabile come elemento della classe personale. Questo, nel diagramma di Chen si esprime creando un arco come in Figura B.16.

Figura B.16 Esempio di sottoclasse

macchinisti

personale

ESPERIENZA

Figura B.17 Esempio di associazione riflessiva.

personale

22 Capitolo B

B.4.2 Modello logicoChiediamoci adesso come possiamo rappresentare tutte queste situazioni nel modello relazionale. Qui ci viene incontro l’operazione di join che ab-biamo già presentato.

Relazione 1 a nCominciamo con il primo caso. Alla tabella personale aggiungiamo un nuovo attributo che è la chiave della tabella stazioni. Poiché ogni dipen-dente 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 mol-ti.

In generale, per rappresentare un’associazione 1 a n (×) si procede ana-logamente 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 grafica-mente, come in Figura B.17, o in SQL, con la parola chiave REFERENCES.

La dichiarazione di una chiave esterna consente al sistema di control-lare 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


SIONE macchinista. In questo modo MATRICOLA è contemporaneamen-te 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 oppor-tunatamente 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 mModelliamo 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 rispettiva-mente 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 riflessivaPer modellare l’associazione riflessiva “uno a molti” tra personale e personaleche riferisce al dirigente immediato, usiamo l’attributo RESPONSABILE come chiave esterna per la stessa tabella. Per creare graficamente la chia-ve esterna, come si vede in Figura B.18, dobbiamo usare anche l’alias del-la tabella in questione.

24 Capitolo B

B.4.3 La chiave esterna in AccessPer 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 possia-mo scegliere quelle che ci interessano. Poi chiusa la finestra Visualizza ta-belle, effettuiamo la dichiarazione di chiave esterna graficamente, “trasci-nando 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 stabili-re i vincoli di cui parleremo nel paragrafo seguente.

B.5 Vincoli esprimibili su più tabelleDistinguiamo i vincoli in: vincoli di integrità referenziale, di aggiorna-mento e di cancellazione.

B.5.1 Vincolo di integrità referenzialeQuando definiamo la chiave esterna, il sistema controlla errori di immis-sione verificando che l’inserimento effettuato sia coerente con la tabella di riferimento.

I controlli possono essere ancora più stringenti, permettendo esclusi-vamente 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:

Figura B.19 Associazione riflessiva in Access.


Visualizza controllo Casella combinata Tipo origine riga Tabella/query Solo in elenco Sì

Nel campo Origine riga occorre inserire la query opportuna. Come ri-sultato, il sistema in fase di immissione dati permette solo di prelevarli da un menu a tendina.

Esempio B.11Nella tabella macchinisti_e_modelli, sia i nomi delle locomotive da asse-gnare 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 so-cietà ferroviaria può disporre, la seconda si ottiene dalla tabella personale.

La query per estrarre gli identificativi dei macchinisti è la seguente:

SELECT personale.MATRICOLAFROM personaleWHERE 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 tem-po. Il risultato è visibile in Figura B.19. Si nota che il dato da inserire è prelevabile da un menu a tendina.

B.5.2 Vincoli di aggiornamento e vincoli di cancellazioneQuando si definisce un vincolo di integrità referenziale, si possono defi-nire anche le azioni che il sistema deve compiere in caso di aggiornamen-to e di cancellazione su ennuple correlate.

La parola CASCADE serve a segnalare la propagazione della modifica.

Figura B.20

26 Capitolo B

Ad esempio, una piena espressione del vincolo per cui tutti i macchi-nisti devono far parte del personale, impone che la cancellazione di un macchinista dalla tabella personale abbia come conseguenza la cancella-zione di tutte le sue mansioni di pilota. In concreto, dovranno essere can-cellate, nella tabella macchinisti_e_modelli, tutte le ennuple che contengo-no 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 pos-siamo 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 NOACTION, di cui possiamo già intuire il significato, ma rimandiamo ai ma-nuali specializzati.

B.5.3 Il triggerPossiamo notare che i vincoli referenziali eseguono un’azione (RESTRICT, CASCADE ecc.) quando si verifica un evento di aggiornamento o cancella-zione. I trigger sono una estensione di questo paradigma in cui, al verifi-carsi dell’evento di base, l’azione successiva del sistema non è indifferen-ziata, ma invece regolata da qualche condizione. Sono infatti desiderabili funzioni che ci permettano di controllare situazioni senza scrivere com-plessi 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 defi-nizione e di ordine nell’esecuzione delle regole dei trigger.

B.6 Interrogazioni su una base di datiPrendiamo in esame lo schema visualizzato in Figura B.20 ed effettuiamo le seguenti interrogazioni.

Esempio B.12Chiediamo le ore di esperienza di tutti i macchinisti. A questa interroga-zione possiamo rispondere usando il join naturale che esprimiamo nel modo seguente:

SELECT personale.COGNOME,macchinisti.ORE_DI_ESPERIENZAFROM personale, macchinisti WHERE personale.MATRICOLA=macchinisti.MATRICOLA;


In alternativa, possiamo usare l’interfaccia grafica. Dalla finestra Scegli tabelle scegliamo le tabelle macchinisti e personale. Il sistema segnala auto-maticamente il legame di join. Poi operiamo nel pannello inferiore le se-guenti scelte:

Se visualizziamo la soluzione SQL offerta da Access, troviamo:

SELECT personale.COGNOME,macchinisti.ORE_DI_ESPERIENZAFROM personale INNER JOIN macchinisti ON personale.MATRICOLA = macchinisti.MATRICOLA;

Campo COGNOME ORE_DI_ESPERIENZA MATRICOLA

Tabella personale macchinisti personale

Visualizza ✓ ✓

Condizione =macchinisti.MATRI-COLA

Figura B.21 Lo schema su cui effettuiamo le interrogazioni.

28 Capitolo B

Come si vede, Access ha usato automaticamente l’operatore INNERJOIN 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 stan-dard SQL quando lo ritengono opportuno.

Esempio B.13Cerchiamo 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:

Esempio B.14Cerchiamo il cognome dell'immediato responsabile di Giuseppe Rossi.

Operiamo come nei casi precedenti, usando due volte la tabella perso-nale. Il sistema crea l’alias personale_1, ma ignora il legame precedente-mente costruito tra la chiave RESPONSABILE della tabella personale_1 e l’attributo MATRICOLA della tabella personale. Dobbiamo dunque ricre-arlo e poi effettuare le seguenti scelte:

Esempio B.15Cerchiamo i nomi dei responsabili dei macchinisti che possono guidare l’ETR460.

Campo COGNOME ELETTRICA

Tabella personale modelli

Visualizza ✓ ✓

Condizione TRUE

Campo COGNOME NOME COGNOME

Tabella personale personale personale_1

Visualizza ✓

Condizione "Rossi" "Giuseppe"


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:

Esempio B.16Cerchiamo il cognome del personale che ha il responsabile non apparte-nente alla propria stazione. L’interrogazione in SQL risulta essere:

Campo COGNOME COGNOME TIPO

Tabella personale personale_1 macchinisti_e_modelli

Visualizza ✓ ✓

Condizione “ETR460”

Campo STAZIONE_APP COGNOME [CAPO]:COGNOME

Tabella personale personale personale_1

Visualizza ✓ ✓

Condizione <>personale_1.STAZIONE_APP “ETR460”

Sperimentazioni con le basi di dati - apogeonline.com · prima fase, e supponiamo che il...

Documents

Transcript of Sperimentazioni con le basi di dati - apogeonline.com · prima fase, e supponiamo che il...