CAPITOLO 5: DATABASE INTRODUZIONE Una base di dati (o

CAPITOLO 5: DATABASE
INTRODUZIONE
Una base di dati (o database) è semplicemente una collezione di dati organizzati in qualche modo e
con qualche criterio. Il modello più diffuso è quello relazionale, ovvero basato sulle tabelle, mentre
ci 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'unica
tabella, per esempio di un foglio di calcolo, mettendo un titolo ad ogni campo ( = attributo) e
riempiendo 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 si
può fare intuitivamente è dividere questo tabellone in sottotabelle e collegarle mediante relazioni,
servendosi di un RDBMS (Relational Database Management System – mysql, oracle,…) o di un
programma come Access.
Questo processo di progettazione non è complicato, ma esula dallo scopo di questo capitolo, dove
facciamo un esempio semplice di realizzazione di un semplice database (non attraverseremo tutte le
fasi della progettazione).
SPECIFICA DEL PROBLEMA
Vogliamo creare un database per la gestione di un campionato di fantacalcio, che deve tener conto
delle squadre, dei giocatori e dei dati relativi ai fanta-allenatori e di cui ci interessa data di inizio
e di fine. Per gli allenatori ci interessa conoscere nome, cognome e recapito e-mail; dei giocatori
ci interessano nome, cognome, ruolo, costo e squadra di appartenenza; per ogni squadra deve
essere disponibile il numero dei giocatori e il budget disponibile.
Dalla specifica del problema possiamo individuare le entità e le relazioni tra le entità (modello
relazionale). Un’entità è un oggetto del mondo reale che ha determinate proprietà e che noi
possiamo 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 (indicate
come sostantivi e non verbi) tra di esse: questo schema prende il nome di diagramma E-R e
rappresenta 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 e
giocatore. Un giocatore può appartenere ad una sola squadra (cosa che dovrebbe essere specificata
nelle 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 per
esempio quella tra studenti ed esami, o relazioni con partecipazione opzionale; uno studente
potrebbe 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 ho
indicati semplicemente per chiarezza, ma naturalmente sappiamo quali possono essere dalle
specifiche. C’è comunque un attributo che è il più importante di tutti, quello che rende differente
una tupla da un’altra, ovvero la chiave. Una chiave è un attributo come il codice fiscale, il quale
identifica (o almeno dovrebbe…ho sentito di casi strani) una tupla specifica. Una chiave si definisce
come superchiave minimale, dove una superchiave è un insieme di attributi che individua
univocamente una tupla in una tabella. Facciamo un esempio
MATRICOLA
1000
1002
…
CODICE FISC.
AAA1234567890
BBB1234567890
…
NOME
Pippo
Pluto
…
COGNOME
Pippo
Pluto
…
VOTO
5
4
…
Una superchiave è per esempio quella costituita da tutti gli attributi della tupla, però anche i primi
quattro attributi bastano a distinguere tra due tuple, anche i primi tre, anche i primi due e anche il
primo o il secondo da solo. Una superchiave minimale è quella con meno attributi e dunque noi
abbiamo due possibili chiavi: infatti o la matricola o il codice fiscale possono per noi assumere il
ruolo di chiave. Il modo più semplice per avere una chiave è inserire un nuovo attributo come
identificatore posizionale(id), ovvero una specie di colonna come quella che indica la riga in un
foglio di calcolo.
Mettiamo di avere il seguente database (il titolo della tabella è quello esterno e all’interno delle
parentesi 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, lo
studente 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 ricordano
il nome delle tabelle. Abbiamo cioè tradotto in tabelle quello schema e le relazioni le abbiamo
inserite 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 esame
sarà collegato a corso mediante la sua chiave codice. Nella tabella esame dunque Studente e Corso
non possono essere vuoti (ovvero avere valore NULL), per cui saranno attributi “not null” (che non
possono 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 il
Voto 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 composta
da un solo attributo) e semplice da ricordare, ovvero abbiamo aggiunto (come detto prima) un
contatore del numero di tuple inserite, il quale andrà ad identificarle univocamente. Gli attributi
dovrebbero essere tutti contenuti nelle specifiche, ma possiamo anche dedurle da un’analisi
personale.
Ora dobbiamo tradurre le relazioni tra le tabelle, se anche loro hanno degli attributi, potrebbero
divenire anch’esse tabelle.
Partecipazione viene tradotta
Squadra(id,nome,numero_giocatori,Campionato);
Gestione può essere tradotta con
Allenatore(id,nome,cognome,email,Squadra);
oppure
Squadra(id,nome,numero_giocatori,Allenatore);
Appartenenza viene tradotta con
Giocatore(id,nome,cognome,ruolo,costo,Squadra);
Come si nota abbiamo tradotto le relazioni (1,1)<--->(1,N) includendo dalla parte (1,1) un
riferimento alla parte (1,N), lo stesso valeva per (0,1) e (0,N), ma l’attributo non avrebbe potuto far
parte (come prima nella tabella esame) della chiave della tabella. Se la relazione aveva qualche
attributo, esso viene trasferito dal lato (1,1).
L’unica relazioni (1,1)<--->(1,1) la possiamo tradurre creando un attributo di riferimento in
una o in un’altra tabella (come ci fa più comodo) all’altra tabella. Gli attributi eventuali di una
relazione vengono messi dove è stata messo il riferimento esterno.
Nel nostro caso non si creano nuove tabelle che realizzano una relazione, ma se avessimo avuto una
relazione (1,N)<--->(1,N) avremmo creato una nuova tabella a cui avremmo dato il nome
della 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 due
tuple (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 SQL
L’algebra relazionale è un linguaggio di tipo procedurale, basato su concetti algebrici; è basato su
un 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 solo
le 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 tabella
possa prendere parte, per esempio, ad un’unione.
Ridenominzione: ρEsame ← Corso
L’operatore di selezione, è un operatore che, appunto, seleziona le tuple che rispettano determinate
condizioni, le quali possono essere espresse anche mediante la logica delle preposizioni (algebra di
Boole) 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 solo
le colonne di alcuni attributi.
Proiezione: ПMatricola, Nome (σCorso = Geometria)
Qui facciamo prima una selezione, ovvero estraiamo delle tuple (tagliamo orizzontalmente), poi
facciamo una proiezione, ovvero prendiamo solo gli attributi che ci interessano (tagliamo
verticalmente). Dunque gli operatori possono esser usati in successione.
OPERATORE DI JOIN
Il join è la più importante operazione dell’algebra relazionale e consiste sostanzialmente nel fondere
due tabelle in una, correlando i dati delle due relazioni. Esistono sostanzialmente due tipi di join:
join naturale (teorico) e theta-join (pratico).
JOIN NATURALE
Vediamo 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 prima
tabella (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’attributo
corso in comune e correla i dati contenuti nelle due relazioni in modo logico, ovvero ad uno
studente di fisica, viene fatto corrispondere il docente del corso di fisica. Lo stesso sarebbe avvenuto
se 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 immaginare
di 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 esempio
solo 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 al
join, 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 al
join 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 che
seguono:
Nel caso abbiamo tuple dangling nell’una o nell’altra tabella o in entrambe, possiamo utilizzare un
particolare join, detto esterno, che va a sostituire con il valore NULL (se non ci sono vincoli che lo
impediscono) 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 due
tabelle.
Join destro: MEDICINALI
Join sinistro: MEDICINALI
Join completo: MEDICINALI
THETA-JOIN ED EQUI-JOIN
RIGHT
FORNITORE
LEFT
FORNITORE
FULL
FORNITORE
Il theta-join in sostanza serve a realizzare un join tra due tabelle senza attributi omonimi (ovvero
con lo stesso nome). Esso è un operatore derivato e si ottiene come prodotto cartesiano seguito da
un’operazione di selezione delle tuple che verificano una certa condizione tra i valori di due
attributi, 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 gli
attributi 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 di
più tabelle collegate). Si distingue tra viste materializzate, ovvero tuple memorizzate da qualche
parte 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 essere
aggiornata, ma ha il vantaggio di essere disponibile come una normale tabella. Una vista virtuale ha
il vantaggio di non occupare spazio in memoria, ma lo svantaggio di dover essere ricreata ad ogni
utilizzo rubando tempo processore. Si effettua una scelta a seconda della dimensione del database e
del tempo richiesto per l’accesso.
SQL
Per interrogare, con l’algebra vista prima, un DBMS, si utilizza il linguaggio SQL (Structured
Query 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 varie
versioni di SQL, ma la più utilizzata è la versione SQL-2 (92), ma attualmente c’è la SQL-3, meglio
conosciuta 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 sviluppare
con Access e uno con MySql.
TIPI DI DATO (DOMINI)
Char
Varchar
varchar2
Bit
Varbit
Numerical
Decimal
Integer
Smallint
Float
Real
Double Precision
Date
Time
Timestamp
Interval
Boolean
Blob
Clob
SQL-2
Variabile carattere
Variabile carattere
Variabile carattere
Variabile bit
Variabile bit
Variabile numerica
Variabile decimale
Variabile intera
Variabile intera short
Variabile reale a singola precisione
Variabile reale
Variabile reale a doppia precisione
Variabile che contiene una data
Variabile che contiene un ora
Variabile che contiene un ora
Variabile intervallo di tempo
SQL-99
Variabile booleana
Oggetti di grandi dimensioni contenenti valori binari (file)
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
not null
Inique
Primary key
VINCOLI INTRARELAZIONALI
L’attributo non può assumere valore NULL
L’attributo deve essere unico, ovvero nessun altro può assumere lo stesso
valore nella tabella
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 due
tabelle). Prendiamo, per esempio, un elenco dei dipendenti collegato alla relazione
nominativo_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 cui
collegarla, 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 della
tabella dipendenti potrebbe avere un riferimento a qualcosa che non esiste; la stessa potrebbe
accadere se aggiorniamo(update) una tupla. Quello che dobbiamo fare è definire l’azione da
intraprendere nel caso si verifichi una cosa del genere: la sintassi è la seguente
on [ delete | update ]
[cascade | set null | set default | no action]
cascade
set null
set default
no action
Elimina a cascata le tuple
Setta a null (secondo i vincoli intra-relazionali) l’attributo
Setta ad un valore di default l’attributo
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 SCHEMI
Mediante i comandi alter e drop possiamo effettuare una modifica o la cancellazione di qualcosa.
alter
alter
alter
alter
alter
table
table
table
table
table
nome_tabella
nome_tabella
nome_tabella
nome_tabella
nome_tabella
alter column . . .;
add constraint . . .;
drop constraint . . .;
add column nome_attributo tipo(N);
drop column nome_attributo;
drop table nome_tabella [restrict | cascade];
restrict Non rimuove la tabella se è collegata a qualcosa
cascade Rimuove tutto
INTERROGAZIONI (QUERY)
Un’interrogazione in sql, meglio conosciuta come query sql, permette di ottenere delle informazioni
dal DBMS su qualsiasi aspetto del database: elenco dati contenuti, join tra tabelle, contare elementi
di 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 di
selezione, 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 i
seguenti operatori
OPERATORE
=
<
>
<=
>=
<>
is null(. . .)
is not null( . . .)
like (. . .)
DESCRIZIONE
uguale
minore
maggiore
minore uguale
maggiore uguale
diverso
è NULL
non è NULL
assomiglia
Supponiamo di avere la tabella impiegato(matricola,nome,cognome,età) e di voler trovare gli
impiegati 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, possiamo
scrivere
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 che
cominci 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 di
caratteri qualsiasi, metre se vogliamo sostituire solo un carattere, per esempio per vedere gli
impiegati 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, dobbiamo
elencare 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 ACCESS
Prima di andare avanti e completare quanto fino adesso detto sulla select, dovrete esercitarvi in
qualche modo. Ora, dato che non tutti hanno la possibilità di scaricare da internet mysql oppure
oracle o qualsiasi altro DBMS, ci serviremo di Access. Access, presente nella maggior parte dei
computer, è un programma di database. Ci permette di creare tabelle e di definire relazioni tra di
esse, di effettuare query, creare maschere e tante altre belle cose. Quello che intendo realizzare è il
database fantaclacio proposto prima; a voi lascio il compito di effettuare qualche query in modo da
allenarvi per quello che andrò a spiegare dopo.
Dopo aver avviato Access ed aver creato un nuovo database chiamato “fantacalcio.mdb”, potremmo
creare 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 riassumere
il 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, il
suo 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 in
basso – non in figura) i vincoli di tupla, l’attributo che funge da chiave, etc… Poi vedremo come
creare 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 quel
punto comparirà una finestra dove andremo a scrivere la nostra query. Access non prevede (per
quanto so) il pieno supporto all’sql, e mentre di solito una stringa nei comuni DBMS è dichiarata
come varchar2, in Access dovremo utilizzare char, per gli interi int e così via…Per maggiori
informazioni consultate la guida in linea :-)
Una volta premuto in pulsante SQL scriviamo la nostra query e la salviamo come “crea tabella
campionato” (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 visualizzarla
selezionandola 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 prima
le tabelle che devono riferirsi ad altre, senza aver creato quelle a cui devono riferirsi (i computer
non prevedono il futuro…); inoltre, dopo aver creato una query bisogna eseguirla, altrimenti non
creeremo la tabella e quando Access cercherà di collegare due attributi di due tabelle diverse, non
saprà cosa fare e griderà allo scandalo.
Sappiamo che la tabella squadra è collegata alla tabella campionato, dunque dobbiamo creare prima
la 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 essa
sono 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 schema
fatto 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 le
tabelle e in seguito trascinare gli attributi su quelli collegati (ci sono molti tutorial in rete).
INSERIMENTO DEI DATI IN SQL
Per 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 e
possiamo inserire in questa tabella o una tupla alla volta mediante l’opzione values, oppure
inserire 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 valore
NULL.
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 piccolo
l’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 spazio
occupato dal database ed evitare errori di aggiornamento. Quel numero_giocatori, però potrebbe
essere utilizzato da un programma per velocizzare delle operazioni, senza dover richiedere per
forza un conteggio. Dobbiamo naturalmente analizzare cosa conviene e non conviene fare, poi
progettare in modo appropriato (secondo regole che non intendo trattare qui, altrimenti ci
vorrebbero troppe pagine).
insert
insert
insert
insert
into
into
into
into
Allenatore
Allenatore
Allenatore
Allenatore
values
values
values
values
(1,’Pippo’,’Esposito’,’[email protected]’,1);
(2,’Peppe’,’Menna’, [email protected]’,2);
(3,’Ciro’,’Napolitano’, [email protected]’,3);
(4’Peppe’,’Cece’,’ [email protected]’,4);
inseriamo ora alcune tuple d’esempio nella tabella giocatore (inseritele anche voi con
visualizzazione struttura, è più bello…)
NOTA: in questo database non è prevista la presenza di giocatori che non appartengono ad una
squadra, per cui avremmo potuto dichiarare (per correttezza logica) l’attributo Squadra come not
null, il che, appunto, non permetterebbe di inserire giocatori nel database se non appartenenti ad
una squadra. In questo caso l’attributo Squadra può assumere come valore NULL oppure un ‘id’
valido nella tabella Squadra.
CANCELLAZIONE DEI DATI IN SQL
Se 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 effettuare
questa operazione utilizziamo drop (visto prima).
MODIFICA DEI DATI IN SQL
update tabella
set attributo = [valore | query | default | NULL]
. . .
where condizione
per esempio, supponiamo di voler modificare un indirizzo e-mail scritto male
update Allenatore set email = ‘[email protected]’ where id = 1;
JOIN IN SQL
L’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.attrib
dove 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.attrib
Se 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 where
tab1.attrib *= tab2.attrib
tab1.attrib =* tab2.attrib
tab1.attrib *=* tab2.attrib
o ancora
tab1.attrib (+)= tab2.attrib
tab1.attrib =(+) tab2.attrib
tab1.attrib (+)=(+) tab2.attrib
ORDINAMENTO
Per 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 cui
eseguire l’ordinamento (prima sul primo, poi sul secondo,…) e il modo in cui ordinare crescente
(asc) o decrescente (desc).
OPERATORI AGGREGATI
In 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 tabella
che 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’attributo
specificato.
Vogliamo, per esempio, sapere il numero di giocatori che ci sono in una squadra del database
precedente
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 valore
massimo
min ( [distinct | all ] attributo)
l’operatore aggregato di minimo, prende la colonna risultante dalla select e ne restituisce il valore
minimo
avg ( [distinct | all ] attributo)
l’operatore aggregato di media, prende la colonna risultante dalla select e ne restituisce la media
aritmetica. Equivarrebbe a fare sum( ) diviso count ( ).
INTERROGAZIONI CON RAGGRUPPAMENTO
Supponiamo di avere questa tabella
Impiegato(matricola,nome,cognome,stipendio,settore);
se vogliamo selezionare lo stipendio massimo di un impiegato del settore tessile semplicemente
utilizziamo 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 realizzare
interrogazioni con raggruppamento, dobbiamo utilizzare nella nostra select la clausola
group by lista attributi having condizione
l’interrogazione avverrà raggruppando le tuple risultanti secondo gli attributi nella lista che
rispettano quelle condizioni.
Supponiamo di avere la seguente tabella e di voler calcolare la somma degli stipendi degli
impiegati, 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 utilizzare
l’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à con
una 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 di
più 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 caso
precedente 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 INSIEMISTICI
Se abbiamo due query e due tabelle che ne rappresentano il risultato, possiamo immaginare di
effettuare (con le dovute restrizioni riguardo i domini) le operazioni di unione, intersezione e
differenza, 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 di
attributi e questi attributi devono avere domini compatibili (devono essere dello stesso tipo in modo
ordinato).
Dopo il tipo di operazione possiamo mettere o omettere all; se lo omettiamo, di default viene
utilizzato distinct e dunque si ha l’eliminazione dei duplicati (che possono per esempio apparire
nell’unione), altrimenti, specificando all, si prendono in considerazione anche le tuple duplicate.
QUERY NIDIFICATE
A volte può servire confrontare un attributo con un insieme di attributi risultante da una seconda
query. A sua volta la seconda query può contenere una o più query nidificate, ottenendo una query
lunga e complicata, ma comunque efficiente… la struttura di base è riassunta nell’immagine che
segue:
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 del
risultato della seconda query. Se utilizzassimo all, la condizione sarebbe soddisfatta solo se
l’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 espressioni
controllano semplicemente se il valore dell’attributo sia contenuto o meno nel risultato della
sottoquery. Possiamo stabilire la corrispondenza
PASSAGGIO DI BINDING
Per ottenere il risultato di un insieme di query nidificate, si può pensare di partire da quelle al livello
più interno e man mano risalire i livelli: in questo modo ogni interrogazione nidificata viene
eseguita una sola volta. A volte però si verifica che c’è una variabile che collega l’interrogazione
nidificata 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 alla
condizione where, separatamente per ciascuna riga. Dunque quello che si fa è valutare per ogni riga
della query esterna, prima la query nidificata, poi calcolare il predicato (where) per ogni riga sulla
query esterna.
UTILIZZO DI VARIABILI
In una query possiamo rinominare sia attributi sia tabelle utilizzando un alias, il quale ha lo scopo
di 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 e
utilizzate mediante i loro alias. Utilizzando as si possono anche rinominare gli attributi, senza
cambiare il significato della query.
Facciamo un altro esempio: vogliamo estrarre da una tabella ‘persone’ tutte le persone con lo
stesso cognome.
select p1.cognome,p1.nome from persone p1, persone p2
where p1.cognome=p2.cognome;
Quello che ci interessa vedere è cosa succede nel caso di query nidificate. In questo caso riguardo la
visibilità 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 di
una query nidificata all’interno di essa. Se due query sono allo stesso livello non possono
condividere variabili”.
OPERATORE LOGICO EXISTS
Continuando con le query nidificate, introduciamo l’operatore logico exists. Questo operatore
accetta 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 il
risultato della query è vuoto.
VINCOLI DI INTEGRITÀ
Ritorniamo ora al DDL, ovvero la parte dichiarativa dell’SQL. Quando andiamo a scrivere una
tabella in SQL, possiamo voler definire delle restrizioni (oltre a not null, primary key,…) che
permettano 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ò avere
30 e lode, ma non 18 e lode: dunque mettere la lode a un 18 equivarrebbe ad un errore logico). Per
evitare tali situazioni si utilizzano i vincoli di integrità.
create table nome_tabella (
. . .
attributo tipo check (condizione),
. . .
)
ASSERZIONI
Con le asserzioni, si definiscono vincoli che appartengono allo schema e non sono associati a
nessun attributo o tabella; sono dei controlli che garantiscono la consistenza (una proprietà logica di
una 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 sia
immediato o differito. Controllo immediato significa che il controllo avviene immediatamente dopo
una modifica e dunque l’operazione di modifica può essere immediatamente annullata. Il controllo
differito invece avviene solo al termine dell’esecuzione di una transazione (= “insieme di
operazioni”), che in caso di errore viene annullata completamente.
Per dire al DBMS quando effettuare il controllo, si usano in modo alternativo le seguenti
espressioni:
set constraints nome_vincolo immediate;
set constraints nome_vincolo deferred;
che rispettivamente rappresentano, come si può capire intuitivamente, il controllo immediato e
quello differito.
VISTE
Accenniamo soltanto alle viste, le quali sono tabelle virtuali ricavate da informazioni contenute in
altre 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 queste
tabelle virtuali. Una relazione virtuale ha il vantaggio di essere sicuramente aggiornata e non
occupare spazio fisico, ma ha lo svantaggio di richiedere un tempo di elaborazione maggiore e
dunque un minore tempo di accesso ai dati.
Le viste cosiddette materializzate, una volta create, rimangono fisicamente, come tabelle, nel
DBMS e non devono essere ricalcolate ogni volta che sono richieste in un’interrogazione. Si
intuisce che questo tipo di viste ha il vantaggio di consentire un accesso ai dati più rapido, ma hanno
lo svantaggio di essere materializzate, ovvero di occupare spazio fisico e di dover essere di volta in
volta aggiornate.
Quando si progetta un database si pensa anche se e che tipo di viste creare, trovando un
compromesso 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.