CAPITOLO 5: DATABASE

INTRODUZIONEUna base di dati (o database) è semplicemente una collezione di dati organizzati in qualche modo econ qualche criterio. Il modello più diffuso è quello relazionale, ovvero basato sulle tabelle, mentreci sono database a oggetti, nati appunto con lo sviluppo della programmazione ad oggetti.Comunque quello che ci interesserà esaminare è il più diffuso database relazionale.Molte persone, ancora oggi, utilizzano per immagazzinare dei dati in modo ordinato un'unicatabella, per esempio di un foglio di calcolo, mettendo un titolo ad ogni campo ( = attributo) eriempiendo le righe sotto della tabella ( = tuple) con i dati di interesse, creando un enorme tabellone,dove i dati sono confusi, male ordinati e ridondanti (ovvero ci sono delle ripetizioni). Quello che sipuò fare intuitivamente è dividere questo tabellone in sottotabelle e collegarle mediante relazioni,servendosi di un RDBMS (Relational Database Management System – mysql, oracle,…) o di unprogramma come Access.Questo processo di progettazione non è complicato, ma esula dallo scopo di questo capitolo, dovefacciamo un esempio semplice di realizzazione di un semplice database (non attraverseremo tutte lefasi della progettazione).

SPECIFICA DEL PROBLEMAVogliamo creare un database per la gestione di un campionato di fantacalcio, che deve tener contodelle squadre, dei giocatori e dei dati relativi ai fanta-allenatori e di cui ci interessa data di inizioe di fine. Per gli allenatori ci interessa conoscere nome, cognome e recapito e-mail; dei giocatorici interessano nome, cognome, ruolo, costo e squadra di appartenenza; per ogni squadra deveessere disponibile il numero dei giocatori e il budget disponibile.

Dalla specifica del problema possiamo individuare le entità e le relazioni tra le entità (modellorelazionale). Un’entità è un oggetto del mondo reale che ha determinate proprietà e che noipossiamo associare concettualmente ad una tabella; le relazioni sono quelle che legano due o piùentità. Possiamo disegnare uno schema che ci mostri le entità, i loro attributi e le relazioni (indicatecome sostantivi e non verbi) tra di esse: questo schema prende il nome di diagramma E-R erappresenta le entità come dei rettangoli e le relazioni come dei rombi, collegati per mezzo di linee.Sulle linee è indicata la cardinalità. Prendiamo per esempio la relazione appartenenza tra squadra egiocatore. Un giocatore può appartenere ad una sola squadra (cosa che dovrebbe essere specificatanelle specifiche), da cui la relazione uno a uno; ad una squadra, invece, possono appartenere piùgiocatori, da cui la relazione uno a molti. Ci sono anche esempi di relazione molti a molti, come peresempio quella tra studenti ed esami, o relazioni con partecipazione opzionale; uno studentepotrebbe non aver svolto alcun esame e dunque è più corretta una relazione come la seguente:

Avevamo detto che in uno schema E-R sono indicati anche degli attributi: nel mio schema non li hoindicati semplicemente per chiarezza, ma naturalmente sappiamo quali possono essere dallespecifiche. C’è comunque un attributo che è il più importante di tutti, quello che rende differenteuna tupla da un’altra, ovvero la chiave. Una chiave è un attributo come il codice fiscale, il qualeidentifica (o almeno dovrebbe…ho sentito di casi strani) una tupla specifica. Una chiave si definiscecome superchiave minimale, dove una superchiave è un insieme di attributi che individuaunivocamente una tupla in una tabella. Facciamo un esempio

MATRICOLA CODICE FISC. NOME COGNOME VOTO1000 AAA1234567890 Pippo Pippo 51002 BBB1234567890 Pluto Pluto 4… … … … …

Una superchiave è per esempio quella costituita da tutti gli attributi della tupla, però anche i primiquattro attributi bastano a distinguere tra due tuple, anche i primi tre, anche i primi due e anche ilprimo o il secondo da solo. Una superchiave minimale è quella con meno attributi e dunque noiabbiamo due possibili chiavi: infatti o la matricola o il codice fiscale possono per noi assumere ilruolo di chiave. Il modo più semplice per avere una chiave è inserire un nuovo attributo comeidentificatore posizionale(id), ovvero una specie di colonna come quella che indica la riga in unfoglio di calcolo.

Mettiamo di avere il seguente database (il titolo della tabella è quello esterno e all’interno delleparentesi ci sono i vari attributi – colonne):

Studente(Matricola, Nome, Cognome);Esame(Studente, Voto, Lode, Corso);Corso(Codice, Titolo, Professore);

per individuare la chiave sottolineiamo gli attributi che la compongono:

Studente(Matricola, Nome, Cognome);Esame(Studente, Voto, Lode, Corso);Corso(Codice, Titolo, Professore);

Mentre la matricola e il codice da soli sono sufficienti ad essere chiavi delle rispettive tabelle, lostudente e il corso sono entrambi attributi necessari a definire una tupla: uno studente può fare piùesami, ma non farà più volte lo stesso.

Studente(Matricola, Nome, Cognome);Esame(Studente, Voto, Lode, Corso);Corso(Codice, Titolo, Professore);

Guardiamo la tabella esame essa è legata a Studente e a Corso, mediante due attributi che ricordanoil nome delle tabelle. Abbiamo cioè tradotto in tabelle quello schema e le relazioni le abbiamoinserite nella tabella esame. È come se avessimo un puntatore alle tabelle, o meglio alle loro chiavi.Vediamo il seguente schema che ci indica le relazioni tra le tabelle.

Quindi Esame sarà collegato allo studente mediante la sua chiave, che è la sua matricola e esamesarà collegato a corso mediante la sua chiave codice. Nella tabella esame dunque Studente e Corsonon possono essere vuoti (ovvero avere valore NULL), per cui saranno attributi “not null” (che nonpossono avere valore nullo). Questo è un vincolo sia di chiave (perché è la chiave della relazione)sia di riferimento (perché sono riferimenti ad altre tabelle). Altri vincoli possono essere che se ilVoto non è 30, non si può avere la lode (anche se sarebbe bello 18 e lode…) e si dicono di tupla.

Torniamo al nostro fantacalcio e trasformiamo le entità in tabelle:

Campionato(id,nome,data_inizio,data_fine)Squadra(id,nome,numero_giocatori)Giocatore(id,nome,cognome,ruolo,costo)Allenatore(id,nome,cognome,email)

Per semplificare le relazioni tra le tabelle abbiamo definito una chiave minimale (ovvero compostada un solo attributo) e semplice da ricordare, ovvero abbiamo aggiunto (come detto prima) uncontatore del numero di tuple inserite, il quale andrà ad identificarle univocamente. Gli attributidovrebbero essere tutti contenuti nelle specifiche, ma possiamo anche dedurle da un’analisipersonale.Ora dobbiamo tradurre le relazioni tra le tabelle, se anche loro hanno degli attributi, potrebberodivenire anch’esse tabelle.

Partecipazione viene tradottaSquadra(id,nome,numero_giocatori,Campionato);

Gestione può essere tradotta conAllenatore(id,nome,cognome,email,Squadra);

oppureSquadra(id,nome,numero_giocatori,Allenatore);

Appartenenza viene tradotta conGiocatore(id,nome,cognome,ruolo,costo,Squadra);

Come si nota abbiamo tradotto le relazioni (1,1)<--->(1,N) includendo dalla parte (1,1) unriferimento alla parte (1,N), lo stesso valeva per (0,1) e (0,N), ma l’attributo non avrebbe potuto farparte (come prima nella tabella esame) della chiave della tabella. Se la relazione aveva qualcheattributo, esso viene trasferito dal lato (1,1).L’unica relazioni (1,1)<--->(1,1) la possiamo tradurre creando un attributo di riferimento inuna o in un’altra tabella (come ci fa più comodo) all’altra tabella. Gli attributi eventuali di unarelazione vengono messi dove è stata messo il riferimento esterno.

Nel nostro caso non si creano nuove tabelle che realizzano una relazione, ma se avessimo avuto unarelazione (1,N)<--->(1,N) avremmo creato una nuova tabella a cui avremmo dato il nomedella relazione e avremmo scritto:

Relazione(Chiave1,Chiave2,Attributi_Relazione);

e collegato così le due tabelle.Nel nostro caso tutte le tabelle hanno come chiave l’attributo id (identificativo) e per collegare duetuple (riferimento esterno) basta inserire nella cella relativa alla relazione della prima entitàl’identificativo di quella della seconda: in questo modo tutto diviene più semplice.

Abbiamo dunque concluso che le nostre tabelle saranno le seguenti:

Campionato(id,nome,data_inizio,data_fine)Squadra(id,nome,numero_giocatori,Campionato);Allenatore(id,nome,cognome,email,Squadra);Giocatore(id,nome,cognome,ruolo,costo,Squadra);

ALGEBRA RELAZIONALE E SQLL’algebra relazionale è un linguaggio di tipo procedurale, basato su concetti algebrici; è basato suun insieme di operatori che hanno come dominio e codominio relazioni.

Alcuni operatori sono quelli insiemistici: unione, intersezione, differenza.L’unione di due relazioni aventi gli stessi attributi è una nuova relazione che le incorpora entrambe,ovvero è un operatore che prende le tuple comuni e non comuni una sola volta.

Consideriamo le due tabelle

Unione: (Elenco A) U (Elenco B)

L’intersezione di due relazioni aventi gli stessi attributi è una nuova relazione che comprende solole tuple comuni.

Intersezione: (Elenco A) ∩ (Elenco B)

La differenza elimina dalla tabella a sinistra le tuple che ha in comune con la tabella a destra,ovvero le sottrae.

Differenza: (Elenco B) – (Elenco A)

L’operatore di ridenominazione è quell’operatore che rinomina un attributo, così che la tabellapossa prendere parte, per esempio, ad un’unione.

Ridenominzione: ρEsame ← Corso

L’operatore di selezione, è un operatore che, appunto, seleziona le tuple che rispettano determinatecondizioni, le quali possono essere espresse anche mediante la logica delle preposizioni (algebra diBoole) e anche in termini di algebra normale (minore, maggiore, uguale,…).

Selezione: σCorso = Geometria

Consideriamo la tabella

otteniamo

L’operatore di proiezione, è un operatore che divide verticalmente la tabella, ovvero considera solole colonne di alcuni attributi.

Proiezione: ПMatricola, Nome (σCorso = Geometria)

Qui facciamo prima una selezione, ovvero estraiamo delle tuple (tagliamo orizzontalmente), poifacciamo una proiezione, ovvero prendiamo solo gli attributi che ci interessano (tagliamoverticalmente). Dunque gli operatori possono esser usati in successione.

OPERATORE DI JOIN

Il join è la più importante operazione dell’algebra relazionale e consiste sostanzialmente nel fonderedue tabelle in una, correlando i dati delle due relazioni. Esistono sostanzialmente due tipi di join:join naturale (teorico) e theta-join (pratico).

JOIN NATURALEVediamo con un esempio cos’è in sostanza il join naturale. Consideriamo queste due tabelle

Consideriamo ora il prodotto cartesiano di queste due tabelle, ovvero per ogni riga della primatabella (A) gli aggiungiamo le righe della tabella a sinistra (B).

Prodotto cartesiano: A × B

Consideriamone ora il join naturale: A B

Come vediamo il join naturale prende dal prodotto cartesiano le uniche righe che hanno l’attributocorso in comune e correla i dati contenuti nelle due relazioni in modo logico, ovvero ad unostudente di fisica, viene fatto corrispondere il docente del corso di fisica. Lo stesso sarebbe avvenutose per esempio avessimo avuto:

Studenti(Matricola,Studente,Corso);Corsi(id,Nome_Corso,Docente);

in questo caso nella relazione studenti Corso, corrispondeva ad ‘id’ e dunque possiamo immaginaredi far precedere una ridenominazione, ottenendo una cosa del genere:

Studenti (ρCorso ← id(Corsi))

ottenendo una tabella che contiene

Risultato(Matricola,Studente,Corso,Nome_Corso,Docente)

con una proiezione poi possiamo eliminare l’identificatore del corso e far rimanere per esempiosolo il nome:

ПMatricola,Studente,Nome_Corso,Docente (Risultato)ottenendo

Risultato(Matricola,Studente,Nome_Corso,Docente)

Riassumendo quello che abbiamo fatto è:

ПMatricola,Studente,Nome_Corso,Docente (Studenti (ρCorso ← id(Corsi)))

Nel nostro caso abbiamo visto l’esempio di un join completo, ovvero tutte le tuple prendono parte aljoin, ma consideriamo per esempio

Avremo lo stesso risultato del join precedente, ma in questo caso la tupla B(‘Geografia’,‘Peppe’)non prenderà parte al join, e dunque avremo un join incompleto. La tuple che non prendono parte aljoin sono dette tuple dangling. Nel caso non ci sia nessuna correlazione trai dati delle due tabelle,allora il join darà come risultato una tabella vuota e il join è un join vuoto, come per le relazioni cheseguono:

Nel caso abbiamo tuple dangling nell’una o nell’altra tabella o in entrambe, possiamo utilizzare unparticolare join, detto esterno, che va a sostituire con il valore NULL (se non ci sono vincoli che loimpediscono) le celle che non dovrebbero far parte del join. Esistono vari tipi di join esterno:sinistro, destro e completo.

Consideriamo le seguenti tabelle

Come possiamo vedere, quando andiamo a fare un join, ci saranno tuple dangling, che per comoditàho messo per ultime in entrambe le tabelle. Ora vediamo i risultati dei vari tipi di join tra queste duetabelle.

Join destro: MEDICINALI RIGHT FORNITORE

Join sinistro: MEDICINALI LEFT FORNITORE

Join completo: MEDICINALI FULL FORNITORE

THETA-JOIN ED EQUI-JOIN

Il theta-join in sostanza serve a realizzare un join tra due tabelle senza attributi omonimi (ovverocon lo stesso nome). Esso è un operatore derivato e si ottiene come prodotto cartesiano seguito daun’operazione di selezione delle tuple che verificano una certa condizione tra i valori di dueattributi, come detto prima, non omonimi. Se la condizione legata al theta-join è di uguaglianza,allora l’operatore prende il nome di equi-join.

Quindi il theta-join (o meglio l’equi-join) permette di fare il join tra le seguenti tabelle, dove gliattributi Esame e Corso, non sono omonimi, ma sono evidentemente collegati

ora l’equi-join: A Esame = Corso B, esso sarà il prodotto cartesiano tra le due tabelle

seguito da un’opportuna selezione, ovvero σEsame = Corso (e anche una proiezione che elimini corso):

VISTE (CENNI)Una vista è una relazione definita su relazioni di schema logico (una tabella che contiene tuple dipiù tabelle collegate). Si distingue tra viste materializzate, ovvero tuple memorizzate da qualcheparte in un database, e viste virtuali, memorizzate in un database mediante un’interrogazione(selezione, join, proiezione…) che dà come risultato quella tabella (quindi non c’è niente di fisico).Una vista materializzata ha lo svantaggio, in quanto tale, di occupare spazio e di dover essereaggiornata, ma ha il vantaggio di essere disponibile come una normale tabella. Una vista virtuale hail vantaggio di non occupare spazio in memoria, ma lo svantaggio di dover essere ricreata ad ogniutilizzo rubando tempo processore. Si effettua una scelta a seconda della dimensione del database edel tempo richiesto per l’accesso.

SQL

Per interrogare, con l’algebra vista prima, un DBMS, si utilizza il linguaggio SQL (StructuredQuery Language). Questo linguaggio si compone di comandi dedicati alla creazione di database(DDL) e di una parte delegata alla loro interrogazione o aggiornamento (DML). Ci sono varieversioni di SQL, ma la più utilizzata è la versione SQL-2 (92), ma attualmente c’è la SQL-3, meglioconosciuta come SQL-99, che introduce tante funzionalità rispetto alla precedente, e che ècompatibile con le precedenti.

Sarò molto schematico in questa introduzione al linguaggio, dopo faremo un esempio da svilupparecon Access e uno con MySql.

TIPI DI DATO (DOMINI)

SQL-2Char Variabile carattereVarchar Variabile caratterevarchar2 Variabile carattereBit Variabile bitVarbit Variabile bitNumerical Variabile numericaDecimal Variabile decimaleInteger Variabile interaSmallint Variabile intera shortFloat Variabile reale a singola precisioneReal Variabile realeDouble Precision Variabile reale a doppia precisioneDate Variabile che contiene una dataTime Variabile che contiene un oraTimestamp Variabile che contiene un oraInterval Variabile intervallo di tempo

SQL-99Boolean Variabile booleanaBlob Oggetti di grandi dimensioni contenenti valori binari (file)Clob Oggetti di grandi dimensioni contenenti valori carattere

CREARE UNA TABELLA

create table nome_tabella (nome_attributo tipo(n) vincoli,. . .

);

per esempio, per un’agenda telefonica andiamo a creare una tabella nominativo

create table nominativo_agenda(id int(5) not null,nome char(20),cognome char(20),telefono char(10),primary key(id)

);

DICHIARAZIONE DI VINCOLI

I vincoli intra-relazionali (ovvero pertinenti alla tabella – relazione – stessa) possono essere

VINCOLI INTRARELAZIONALInot null L’attributo non può assumere valore NULLInique L’attributo deve essere unico, ovvero nessun altro può assumere lo stesso

valore nella tabellaPrimary key L’attributo è la chiave primaria della tabella

Un altro modo di indicare che l’attributo appartiene alla chiave è il seguente (modo equivalente)

create table nominativo_agenda(id int(5) primary key,nome char(20),cognome char(20),telefono char(10),

);

I vincoli possono essere anche iter-relazionali (ovvero pertinenti ad attributi che collegano duetabelle). Prendiamo, per esempio, un elenco dei dipendenti collegato alla relazionenominativo_agenda creata precedentemente (naturalmente il riferimento a questa tabella verràcreato tramite il parametro ‘id’).

create table dipendenti (id int(5) not null,mansione char(20) not null,nominativo int(5) references nominativo_agenda(id),primary key(id)

);

dunque dobbiamo utilizzare la parola chiave references e far seguire nome_tabella(attributo) a cuicollegarla, in modo equivalente possiamo scrivere:

create table dipendenti (id int(5) not null,mansione char(20) not null,nominativo int(5) not null,primary key(id),foregin key (id) references nominativo_agenda(id)

);

Può accadere che se cancelliamo(delete) una tupla dalla tabella nominativo_agenda, una tupla dellatabella dipendenti potrebbe avere un riferimento a qualcosa che non esiste; la stessa potrebbeaccadere se aggiorniamo(update) una tupla. Quello che dobbiamo fare è definire l’azione daintraprendere nel caso si verifichi una cosa del genere: la sintassi è la seguente

on [ delete | update ] [cascade | set null | set default | no action]

cascade Elimina a cascata le tupleset null Setta a null (secondo i vincoli intra-relazionali) l’attributoset default Setta ad un valore di default l’attributono action Non fa niente

create table dipendenti (id int(5) not null,mansione char(20) not null,nominativo int(5) not null,primary key(id),foregin key (id) references nominativo_agenda(id)

on delete cascade on update no action

);

MODIFICA DEGLI SCHEMIMediante i comandi alter e drop possiamo effettuare una modifica o la cancellazione di qualcosa.

alter table nome_tabella alter column . . .;alter table nome_tabella add constraint . . .;alter table nome_tabella drop constraint . . .;alter table nome_tabella add column nome_attributo tipo(N);alter table nome_tabella drop column nome_attributo;

drop table nome_tabella [restrict | cascade];

restrict Non rimuove la tabella se è collegata a qualcosacascade Rimuove tutto

INTERROGAZIONI (QUERY)

Un’interrogazione in sql, meglio conosciuta come query sql, permette di ottenere delle informazionidal DBMS su qualsiasi aspetto del database: elenco dati contenuti, join tra tabelle, contare elementidi una certa colonna, etc… Una volta inviata una query ad un DBMS, viene ritradotta, ottimizzata,eseguita e ci viene mostrato un risultato sotto forma di tabella.

Il comando utilizzato per realizzare una query è select. Una query select (o meglio “una select”),prevede la seguente sintassi

select attributo1,attributo2,… from tabella; (corrisponde ad una proiezione)select * from tabella; (elenca tutte le tuple, con tutti gli attributi)

dopo la tabella si può inoltre inserire una condizione che permette di eseguire un’operazione diselezione, utilizzando il comando where.

select attributi from tabella where condizioni;Più condizioni possono essere legate dagli operatori booleani (and, or, not) e inoltre sono validi iseguenti operatori

OPERATORE DESCRIZIONE= uguale< minore> maggiore<= minore uguale>= maggiore uguale<> diverso

is null(. . .) è NULLis not null( . . .) non è NULL

like (. . .) assomiglia

Supponiamo di avere la tabella impiegato(matricola,nome,cognome,età) e di voler trovare gliimpiegati che devono andare in pensione (ovvero quelli che superano i 65 anni):

select matricola from impiegato where età > 65;Se invece vogliamo trovare la matricola di un impiegato, avendo nome e cognome, possiamoscrivere

select matricola from impiegato where nome = ‘nome’ and cognome = ‘cognome’;

se invece vogliamo le persone che hanno tutti il nome che comincia per “nic” e un cognome checominci per “a” e finisca per “a” basta effettuare la seguente select

select nome,cognome from impiegato where nome like ‘nic%’ and cognome like ‘a%a’

l’operatore like si utilizza quando si vuole fare una select del genere e % sta per una stringa dicaratteri qualsiasi, metre se vogliamo sostituire solo un carattere, per esempio per vedere gliimpiegati che si chiamano Mario o Maria, dobbiamo utilizzare il carattere di underscore ‘_’:

select nome,cognome from impiegato where nome like ‘Mari_’;Supponiamo di voler fare un sondaggio riguardo a quali sono i nomi degli impiegati, dobbiamoelencare tutti i nomi presi una sola volta. Dobbiamo utilizzare la parola chiave distinct(attributi), la quale elimina le tuple duplicate:

select distinct(nome) from impiegati;

CREAZIONE DI DATABASE E QUERY SQL CON ACCESSPrima di andare avanti e completare quanto fino adesso detto sulla select, dovrete esercitarvi inqualche modo. Ora, dato che non tutti hanno la possibilità di scaricare da internet mysql oppureoracle o qualsiasi altro DBMS, ci serviremo di Access. Access, presente nella maggior parte deicomputer, è un programma di database. Ci permette di creare tabelle e di definire relazioni tra diesse, di effettuare query, creare maschere e tante altre belle cose. Quello che intendo realizzare è ildatabase fantaclacio proposto prima; a voi lascio il compito di effettuare qualche query in modo daallenarvi per quello che andrò a spiegare dopo.Dopo aver avviato Access ed aver creato un nuovo database chiamato “fantacalcio.mdb”, potremmocreare le nostre tabelle semplicemente in visualizzazione struttura o comunque ci venga in mente,ma noi lo facciamo con il nostro amato SQL. Eravamo arrivati, dopo una lunga analisi a riassumereil nostro database nelle seguenti tabelle:

Campionato(id,nome,data_inizio,data_fine)Squadra(id,nome,numero_giocatori,Campionato);Allenatore(id,nome,cognome,email,Squadra);Giocatore(id,nome,cognome,ruolo,costo,Squadra);

Ora in visualizzazione struttura, basterebbe crearle una ad una inserendo il nome dell’attributo, ilsuo tipo e (se ci và, per chiarezza) la sua descrizione

abbiamo così un modo semplice e veloce di creare delle tabelle e definire (tramite il pannello inbasso – non in figura) i vincoli di tupla, l’attributo che funge da chiave, etc… Poi vedremo comecreare delle relazioni.Per creare una tabella in SQL dobbiamo creare una nuova query e quando si entra in modalitàcreazione mediante visualizzazione bisogna cercare nella barra degli strumenti il tasto sql. A quelpunto comparirà una finestra dove andremo a scrivere la nostra query. Access non prevede (perquanto so) il pieno supporto all’sql, e mentre di solito una stringa nei comuni DBMS è dichiaratacome varchar2, in Access dovremo utilizzare char, per gli interi int e così via…Per maggioriinformazioni consultate la guida in linea :-)

Una volta premuto in pulsante SQL scriviamo la nostra query e la salviamo come “crea tabellacampionato” (il tipo di query da selezione diventerà di definizione dei dati).

CREATE TABLE Campionato( id int primary key, nome char(20) not null, data_inizio char(10), data_fine char(10))

Possiamo ora eseguire la nostra query facendo semplicemente doppio click, oppure visualizzarlaselezionandola e facendo click sul pulsante struttura (o in modi equivalente col tasto destro…).Creiamo ora tutte le nostre tabelle mediante apposite query, facendo attenzione a non definire primale tabelle che devono riferirsi ad altre, senza aver creato quelle a cui devono riferirsi (i computernon prevedono il futuro…); inoltre, dopo aver creato una query bisogna eseguirla, altrimenti noncreeremo la tabella e quando Access cercherà di collegare due attributi di due tabelle diverse, nonsaprà cosa fare e griderà allo scandalo.Sappiamo che la tabella squadra è collegata alla tabella campionato, dunque dobbiamo creare primala tabella campionato. Poiché la tabella campionato non è legata a nient’altro, procediamo.

Sappiamo che le tabelle giocatore e allenatore sono collegate alla tabella squadra (e mediante di essasono anche collegati tra di loro), e dato che i riferimenti per la tabella squadra sono soddisfatti,procediamo alla sua creazione. Non ci saranno poi problemi per le altre due tabelle.

create table Campionato( id int primary key, nome char(20) not null, data_inizio char(10), data_fine char(10))

create table Squadra ( id int primary key, nome char(20) not null, numero_giocatori int, Campionato int references Campionato(id))

create table Giocatore ( id int primary key, nome char(20) not null, cognome char(20) not null, ruolo char(2) not null, costo float not null, Squadra int references Squadra(id))

create table Allenatore ( id int primary key, nome char(20) not null, cognome char(20) not null, email char(20) not null, Squadra int references Squadra(id))

Se apriamo la finestra delle relazioni, mediante l’apposito tasto o il menù e poi (col tasto destro)facciamo mostrare tutte le relazioni, otteniamo una cosa del genere, che assomiglia allo schemafatto all’inizio. Se abbiamo creato le tabelle mediante la creazione struttura questa finestra ci sarà

utile per definire le relazioni tra le tabelle. Per farlo (la prima volta) basterà aggiungere tutte letabelle e in seguito trascinare gli attributi su quelli collegati (ci sono molti tutorial in rete).

INSERIMENTO DEI DATI IN SQLPer inserire i dati in una tabella, si utilizza il seguente comando SQL

insert into tabella [(attributi)] values (. . .);insert into tabella (query);

dove possiamo mettere un elenco di attributi se vogliamo non inserirne qualcuno opzionale epossiamo inserire in questa tabella o una tupla alla volta mediante l’opzione values, oppureinserire nella tabella il risultato di una select.

Inseriamo un po’ di dati nelle tabelle del nostro database del fantacalcio.

insert into Campionato(id,nome) values(1,'coppa del nonno');possiamo farlo perché data_inizio e data_fine, sono opzionali, ovvero possono assumere valoreNULL.

insert into Squadra(id,nome,Campionato) values (1,'Napoli',1);insert into Squadra(id,nome,Campionato) values (2,'Caserta',1);insert into Squadra(id,nome,Campionato) values (3,Avellino',1);insert into Squadra(id,nome,Campionato) values (4,'Salerno',1);

NOTA: Perché ometto numero_giocatori? No, non per pigrizia. Essendo un database piccolol’attributo è superfluo, in quanto il numero giocatori può essere ricavato mediante un conteggio(operazione prevista dall’SQL) in modo molto semplice. Lo facciamo per ridurre lo spaziooccupato dal database ed evitare errori di aggiornamento. Quel numero_giocatori, però potrebbeessere utilizzato da un programma per velocizzare delle operazioni, senza dover richiedere perforza un conteggio. Dobbiamo naturalmente analizzare cosa conviene e non conviene fare, poiprogettare in modo appropriato (secondo regole che non intendo trattare qui, altrimenti civorrebbero troppe pagine).

insert into Allenatore values (1,’Pippo’,’Esposito’,’pesp@boh.it’,1);insert into Allenatore values (2,’Peppe’,’Menna’, peppmen@tino.it’,2);insert into Allenatore values (3,’Ciro’,’Napolitano’, naps@ciccio.it’,3);insert into Allenatore values (4’Peppe’,’Cece’,’ peppcece@libbero.it’,4);

inseriamo ora alcune tuple d’esempio nella tabella giocatore (inseritele anche voi convisualizzazione struttura, è più bello…)

NOTA: in questo database non è prevista la presenza di giocatori che non appartengono ad unasquadra, per cui avremmo potuto dichiarare (per correttezza logica) l’attributo Squadra come notnull, il che, appunto, non permetterebbe di inserire giocatori nel database se non appartenenti aduna squadra. In questo caso l’attributo Squadra può assumere come valore NULL oppure un ‘id’valido nella tabella Squadra.

CANCELLAZIONE DEI DATI IN SQLSe vogliamo cancellare una tupla mediante una query SQL utilizziamo il comando delete.

delete form tabella where condizione;cancella tutte le tuple che rispettano quella condizione

delete from tabella;cancella solo le tuple e non lo schema della tabella, che continuerà ancora ad esistere, per effettuarequesta operazione utilizziamo drop (visto prima).

MODIFICA DEI DATI IN SQL

update tabellaset attributo = [valore | query | default | NULL]. . .where condizione

per esempio, supponiamo di voler modificare un indirizzo e-mail scritto male

update Allenatore set email = ‘peppeesp@emmail.it’ where id = 1; JOIN IN SQLL’operazione di join in SQL si realizza mediante la select. Si possono realizzare tutti i tipi di join(completo, sinistro, destro) in modo molto semplice.

select attributi from tab1,tab2 where tab1.attrib = tab2.attribdove tab1 e tab2 sono le due tabelle su cui eseguire l’equi-join. Ma possiamo anche scrivere

select attributi from tab1 join tab2 on tab1.attrib = tab2.attribSe vogliamo un altro tipo di join, basta scriverlo esplicitamente

select attributi from tab1 [right | left | full] join tab2 on ...il join naturale non è supportato dai DBMS, avevamo detto prima che non si usa nella pratica.

I join destro, sinistro e completo possono inoltre essere realizzati in questo modo:

select attributi from tab1,tab2 wheretab1.attrib *= tab2.attribtab1.attrib =* tab2.attribtab1.attrib *=* tab2.attrib

o ancora

tab1.attrib (+)= tab2.attribtab1.attrib =(+) tab2.attribtab1.attrib (+)=(+) tab2.attrib

ORDINAMENTOPer ordinare la tabella restituita da una select, si utilizza order by:

select . . . order by attributo,. . . [asc | desc]quindi alla fine della select si aggiunge la parola chiave order by seguita dagli attributi su cuieseguire l’ordinamento (prima sul primo, poi sul secondo,…) e il modo in cui ordinare crescente(asc) o decrescente (desc).

OPERATORI AGGREGATIIn SQL, a differenza dell’algebra relazionale, sono definiti altri operatori, detti operatori aggregati,i quali consentono di effettuare dei raggruppamenti sui risultati. count ([* | [distinct | all] attributo)è l’operatore aggregato che esegue nel caso count(*) il conteggio di tutte le tuple della tabellache la select dà come risultato, nel caso di una count([distinct | all] attributo)esegue il conteggio di tutte le tuple della tabella che contengono un valore diverso dell’attributospecificato.Vogliamo, per esempio, sapere il numero di giocatori che ci sono in una squadra del databaseprecedente

select count (*) from giocatore where Squadra = 1;

sum ( [distinct | all] attributo)l’operatore aggregato di somma, prende la colonna risultante dalla select e ne restituisce la somma.

max ( [distinct | all ] attributo)l’operatore aggregato di massimo, prende la colonna risultante dalla select e ne restituisce il valoremassimo

min ( [distinct | all ] attributo)l’operatore aggregato di minimo, prende la colonna risultante dalla select e ne restituisce il valoreminimo

avg ( [distinct | all ] attributo)l’operatore aggregato di media, prende la colonna risultante dalla select e ne restituisce la mediaaritmetica. Equivarrebbe a fare sum( ) diviso count ( ).

INTERROGAZIONI CON RAGGRUPPAMENTOSupponiamo di avere questa tabella

Impiegato(matricola,nome,cognome,stipendio,settore);

se vogliamo selezionare lo stipendio massimo di un impiegato del settore tessile semplicementeutilizziamo l’operatore aggregato di massimo e scriviamo

select max(stipendio) from Impiegato where settore=‘tessile’;se vogliamo invece sapere qual è l’impiegato che percepisce lo stipendio più alto in quel settore, èsbagliato scrivere

select nome,cognome,max(stipendio) from Impiegato where settore=‘tessile’;

ma dobbiamo ricorrere alle interrogazioni con raggruppamento, ovvero un tipo di interrogazione,in cui venga utilizzata la clausola group by...having...

Le operazioni con gli operatori aggregati, infatti, normalmente valgono per tutte le tuple;interrogazioni di questo tipo permettono di applicarli ad un sottoinsieme di righe. Per realizzareinterrogazioni con raggruppamento, dobbiamo utilizzare nella nostra select la clausola

group by lista attributi having condizionel’interrogazione avverrà raggruppando le tuple risultanti secondo gli attributi nella lista cherispettano quelle condizioni.

Supponiamo di avere la seguente tabella e di voler calcolare la somma degli stipendi degliimpiegati, divisa per settore:

quello che dobbiamo fare è effettuare una selezione di questo genere

select settore,stipendio from Impiegato;il cui risultato sarà la seguente tabella

Dopo di che possiamo cominciare ad usare la calcolatrice... Però possiamo pensare di utilizzarel’operatore aggregato di somma su stipendio, raggruppando per settore; otteniamo:

select settore,sum(stipendio) from Impiegato group by settore;questa query farà una selezione come la precedente e poi direttamente il DBMS ci risponderà conuna tabella contenente le somme divise per settore, come la seguente:

Supponiamo ora di voler risolvere il problema da cui siamo partiti e di voler trovare chi guadagna dipiù in ogni settore:

select matricola,stipendio from Impiegato group by settore having max(stipendio);

se vogliamo saperlo solo per il settore tessile, basta utilizzare la clausola where.

Specificando una condizione in having, si può effettuare una selezione sul raggruppamento, nel casoprecedente abbiamo preso le tuple per cui il valore dello stipendio era massimo, per ogni settore.Possiamo però prendere il settore per cui la somma degli stipendi sia maggiore di 40:

... having sum(stipendio) > 40

ottenendo la seguente tabella

OPERATORI INSIEMISTICISe abbiamo due query e due tabelle che ne rappresentano il risultato, possiamo immaginare dieffettuare (con le dovute restrizioni riguardo i domini) le operazioni di unione, intersezione edifferenza, ovvero quelle che tipicamente si fanno sugli insiemi.

select ... <union | intersect | except> [all] select ...Per realizzare un’unione (union), un’intersezione (intersect) o una differenza (except) c’èuna condizione sine qua non: le tabelle che risultano dalle select devono avere un numero uguale diattributi e questi attributi devono avere domini compatibili (devono essere dello stesso tipo in modoordinato).

Dopo il tipo di operazione possiamo mettere o omettere all; se lo omettiamo, di default vieneutilizzato distinct e dunque si ha l’eliminazione dei duplicati (che possono per esempio apparirenell’unione), altrimenti, specificando all, si prendono in considerazione anche le tuple duplicate.

QUERY NIDIFICATEA volte può servire confrontare un attributo con un insieme di attributi risultante da una secondaquery. A sua volta la seconda query può contenere una o più query nidificate, ottenendo una querylunga e complicata, ma comunque efficiente… la struttura di base è riassunta nell’immagine chesegue:

Utilizzando per esempio questa clausola where

select ... where attributo < any (select... )otteniamo di scegliere tutti gli attributi il cui valore sia minore di almeno uno degli elementi delrisultato della seconda query. Se utilizzassimo all, la condizione sarebbe soddisfatta solo sel’attributo sarebbe più piccolo di tutti gli elementi del risultato della seconda query.

Se utilizziamo in e not in non ci serve applicare un operatore, poiché queste due espressionicontrollano semplicemente se il valore dell’attributo sia contenuto o meno nel risultato dellasottoquery. Possiamo stabilire la corrispondenza

PASSAGGIO DI BINDINGPer ottenere il risultato di un insieme di query nidificate, si può pensare di partire da quelle al livellopiù interno e man mano risalire i livelli: in questo modo ogni interrogazione nidificata vieneeseguita una sola volta. A volte però si verifica che c’è una variabile che collega l’interrogazionenidificata e quella che la contiene: tale situazione è nota come passaggio di binding.Per capire come viene risolto questo problema, bisogna tornare alla definizione standard di query:una query è un prodotto cartesiano fra tabelle, su cui viene effettuata una selezione in base allacondizione where, separatamente per ciascuna riga. Dunque quello che si fa è valutare per ogni rigadella query esterna, prima la query nidificata, poi calcolare il predicato (where) per ogni riga sullaquery esterna.

UTILIZZO DI VARIABILIIn una query possiamo rinominare sia attributi sia tabelle utilizzando un alias, il quale ha lo scopodi rendere la query più comprensibile; in questo caso il costrutto che ci viene in aiuto è as.Facciamo un esempio:

select * form persone as p,ruolo as r where p.id = r.id_perosna;come sappiamo, questa query realizza un equi-join tra le due tabelle, le quali sono state rinominate eutilizzate mediante i loro alias. Utilizzando as si possono anche rinominare gli attributi, senzacambiare il significato della query.

Facciamo un altro esempio: vogliamo estrarre da una tabella ‘persone’ tutte le persone con lostesso cognome.

select p1.cognome,p1.nome from persone p1, persone p2where p1.cognome=p2.cognome;

Quello che ci interessa vedere è cosa succede nel caso di query nidificate. In questo caso riguardo lavisibilità delle variabili (che abbiamo chiamato alias) vale la seguente regola:

“Le variabili SQL sono utilizzabili solo nell’ambito della query in cui sono definite o nell’ambito diuna query nidificata all’interno di essa. Se due query sono allo stesso livello non possonocondividere variabili”.

OPERATORE LOGICO EXISTSContinuando con le query nidificate, introduciamo l’operatore logico exists. Questo operatoreaccetta come argomento una query e restituisce vero solo se il risultato della query non è vuoto.

select . . . where exists (select . . .);

Parallelamente ad exists c’è anche l’operatore not exists, che restituisce vero solo se ilrisultato della query è vuoto.

VINCOLI DI INTEGRITÀRitorniamo ora al DDL, ovvero la parte dichiarativa dell’SQL. Quando andiamo a scrivere unatabella in SQL, possiamo voler definire delle restrizioni (oltre a not null, primary key,…) chepermettano di evitare errori di immissione, oppure verificare la correttezza di un attributo che èdipendente dal valore di un altro attributo della stessa tupla (per esempio ad un esame si può avere30 e lode, ma non 18 e lode: dunque mettere la lode a un 18 equivarrebbe ad un errore logico). Perevitare tali situazioni si utilizzano i vincoli di integrità.

create table nome_tabella (. . .attributo tipo check (condizione),. . .

)

ASSERZIONICon le asserzioni, si definiscono vincoli che appartengono allo schema e non sono associati anessun attributo o tabella; sono dei controlli che garantiscono la consistenza (una proprietà logica diuna base dati, espressa dai vincoli dello schema) della base dati.

create assertion nome check (condizione);Ogni vincolo di integrità è associato ad una politica di controllo che specifica se tale controllo siaimmediato o differito. Controllo immediato significa che il controllo avviene immediatamente dopouna modifica e dunque l’operazione di modifica può essere immediatamente annullata. Il controllodifferito invece avviene solo al termine dell’esecuzione di una transazione (= “insieme dioperazioni”), che in caso di errore viene annullata completamente.Per dire al DBMS quando effettuare il controllo, si usano in modo alternativo le seguentiespressioni:

set constraints nome_vincolo immediate;set constraints nome_vincolo deferred;che rispettivamente rappresentano, come si può capire intuitivamente, il controllo immediato equello differito.VISTE

Accenniamo soltanto alle viste, le quali sono tabelle virtuali ricavate da informazioni contenute inaltre tabelle. Esse possono essere relazioni virtuali, ovvero viste definite per mezzo di funzioni,che non esistono materialmente, ma vengono eseguite quando si fa un’interrogazione su questetabelle virtuali. Una relazione virtuale ha il vantaggio di essere sicuramente aggiornata e nonoccupare spazio fisico, ma ha lo svantaggio di richiedere un tempo di elaborazione maggiore edunque un minore tempo di accesso ai dati.Le viste cosiddette materializzate, una volta create, rimangono fisicamente, come tabelle, nelDBMS e non devono essere ricalcolate ogni volta che sono richieste in un’interrogazione. Siintuisce che questo tipo di viste ha il vantaggio di consentire un accesso ai dati più rapido, ma hannolo svantaggio di essere materializzate, ovvero di occupare spazio fisico e di dover essere di volta involta aggiornate.Quando si progetta un database si pensa anche se e che tipo di viste creare, trovando uncompromesso tra le varie soluzioni.

Per dichiarare una vista si usa il seguente costrutto:

dove la seconda parte (opzionale) della query è relativa all’aggiornamento delle viste.

NOTE:- la query deve restituire un numero di attributi pari a quelli contenuti nello schema;- l’ordine degli attributi nella query deve rispettare quello dello schema.