15_Stage_2007_files/Corso2007Database - INFN-LNF

Manuale Studente
Stage Estivo 2007
Database
PROGETTAZIONE E UTILIZZO
Claudio Bisegni
[email protected]
http:// www.lnf.infn.it/~bisegni
1 - I Database
La gestione dei dati nell’informatica di oggi, è il punto centrale di molte applicazioni
pratiche di un software. Gestione Anagrafiche, Conti Bancari, Testi etc. La ricchezza
di un patrimonio di informazioni digitale, ha visto negli anni, il crescere e il
perfezionarsi di sistemi DBMS(DataBase Management System).
Il DBMS è un sistema organizzato di dati che mette a disposizione gli strumenti
necessari per accedere alle informazioni in modo semplice ed efficiente. La
complessità dei database aumenta negli anni in rapporto alle quantità, sempre più
grandi, dei dati da gestire. All’aumento di questi corrisponde una diminuzione
dell’efficienza di gestione. Sono state e si svilupperanno quindi tecniche di
ottimizzazione delle richieste e di gestione dei dati sempre più perforanti.
2 - Perché usare un database
Quando si ha la necessità di gestire molti dati accessibili da più utenze, è d’obbligo
l’uso di un database.
Nello schema sopra riportato viene mostrata una situazione tipo in cui viene utilizzato
un database i cui dati sono letti e scritti da due client e un web server tramite una
pagina web con script php.
L’utilizzo di un database in questa situazione semplifica molto il lavoro, in quanto a
lui verranno demandate la gestione delle situazioni più critiche che questa tipologia di
sistemi comporta come ad esempio:

accesso concorrente ai dati in scrittura/lettura.

Possibilità di lock dei dati in modo che possano essere letti ma non
modificati.

Tempi ridotti per il prelevamento dei dati a seconda della complessità
delle ricerche effettuate.
Un database fa tutto questo lavoro per noi, e con l’avanzare delle tecniche di sviluppo
software, lo fa ogni giorno che passa nel modo migliore. Per fare un esempio, ogni
sito web con contenuti dinamici usa un database, e anche se sottoposto a molte visite
riesce a dare un ragionevole tempo di risposta nella fruizione dei dati richiesti.
3 - Didattica del corso
Durante il corso verrà realizzato un semplice database di esempio per introdurre le
principali caratteristiche del linguaggio SQL. Verranno utilizzate le relazioni
(Foreign Key) tra tabelle in modo da creare dei vincoli logici tra i dati, per
“proteggerne” inserimento aggiornamento e cancellazione in modo che il database
risulti sempre integro nella logica con cui è stato creato. Come motore DBMS verrà
utilizzato Mysql, i comandi utilizzati sono simili ai rispettivi degli gli altri
database(es: Oracle). Nelle select riportate nel manuale il testo indicato tra parentesi
quadri es: [WHERE condizione].
4 - Ideare un database
Per prima cosa occorre pensare a tutti i dati che dobbiamo gestire, nel nostro caso
creeremo un database che gestirà i seguenti dati:

Alunni

Classi

Gruppi Culturali (Sport, Scienza, Musica, ecc…).
Per dare delle regole al database inseriremo le relazioni tra tabelle che costituiranno la
logica con cui i dati fisici sono legati. Il database dovrà rispettare le seguenti regole:

Un alunno potrà essere associato ad una sola classe.

Un alunno potrà essere associato a nessuno, uno o molti gruppi.
5 - Creazione del Database
Per iniziare dobbiamo creare un database di esempio, utilizzeremo il client di MySql
autenticandoci con l’utente root per la creazione del Database lnf_demo e dell’Utente lnf. Per
eseguire l’amministrazione del database e la creazione dello stesso utilizzeremo il client base
di MySql a riga di comando digitando il comando:
#> mysql –u root –p
ci verrà chiesta l’immissione della password di root e poi avremo accesso al database per
creare il DB del nostro progetto.
I comandi che utilizzeremo per creare utente e DB sono i seguenti:
1. CREATE DATABASE lnf_demo;
2. CREATE USER `lnf`@`localhost` IDENTIFIED BY 'lnf';
3. GRANT ALL PRIVILEGES ON lnf_demo . * TO lnf@localhost;
Il comando sql (1) crea il database lnf_demo, la seconda crea l’utente. Una
considerazione va fatta su come MySql gestisce gli utenti. Nella comando sql (2)
avviamo la creazione di un utente lnf che si possa collegare dal localhost (computer si
sta lavorando) e di assegnare la password “lnf” a tale utente. Per lo stesso utente si
può quindi determinare caratteristiche di accesso differenti a seconda da quale
indirizzo si collega. Mysql permette di utilizzare diversi engine per la creazione delle
tabelle, per il nostro database utilizzeremo l’engine innodb che permette l’utilizzo
delle relazioni e transazioni.
6 - Creazione delle tabelle
La tabelle da creare sono quelle di:
1. alunno.
2. classe.
3. gruppo.
4. alunno_gruppo.
Immagine 1
Prima di crearle introdurremo il concetto di relazione (Foreign Key – FK1) tra tabelle.
Abbiamo detto, nell’introduzione, che nel database i dati sono in relazione tra loro
tramite dei legami logici e non fisicamente nelle stesse tabelle. Per descrivere la
situazione in cui l’alunno appartiene ad una classe, avremmo potuto mettere
l’informazione direttamente nella tabella alunno, cosi facendo avremmo dovuto
duplicare le informazioni relative alla classe per ogni alunno. Con l’uso delle relazioni
possiamo avere una tabella che descriverà la classe(id, nome, descrizione) e
creeremo una relazione tra la tabella alunno e la tabella classe chiamata
alunno_classe_idclasse.
1
Relazione che collega un campo di una tabella con il campo di un’altra, in modo che
rispetti delle regole.
La tabella alunno_gruppo serve per realizzare una relazione del tipo molti a molti:
più alunni possono appartenere a più gruppi e le relazioni create sono:

alunno_gruppo_idalunno – Relazione con la tabella degli alunni.

alunno_gruppo_idgruppo – Relazione con la tabella dei gruppi.
Con l’utilizzo delle relazioni tra tabelle le operazioni di Insert, Update e Delete
saranno protette dalla possibilità di inserire dati non congruenti, mentre le select
supporteranno la le join tra tabelle2.
Esempio: il quarto campo della tabella alunno è l’id della classe a cui appartiene, e
dallo schema capiamo che uno studente può essere associato ad una sola classe alla
volta. La relazione tra i campi alunno.id_classe e classe.id serve ad informare il
database che il valore ammissibile per il campo alunno.id_classe deve essere
assolutamente un valore presente nel campo id della tabella classe. Se andiamo ad
effettuare una Insert nella tabella alunno nel campo id_classe, con un valore non
presente nella tabella classe, l’operazione verrà interrotta informandoci della
violazione di integrità.
Comandi per la creazione delle tabelle:
CREATE TABLE `classe` (
CREATE TABLE `alunno` (
`id` INT NOT NULL AUTO_INCREMENT ,
`id` INT NOT NULL AUTO_INCREMENT ,
`nome` VARCHAR( 32 ) NOT NULL ,
`nome` VARCHAR( 32 ) NOT NULL ,
`descrizione` VARCHAR( 128 ) NOT NULL ,
`cognome` VARCHAR( 32 ) NOT NULL ,
PRIMARY KEY ( `id` )
`id_classe` INT NOT NULL,
) TYPE = innodb;
PRIMARY KEY (`id`)
) TYPE = innodb;
CREATE TABLE `gruppo` (
CREATE TABLE `alunno_gruppo` (
`id` INT NOT NULL AUTO_INCREMENT ,
`id` int(11) NOT NULL AUTO_INCREMENT,
`nome` VARCHAR( 32 ) NOT NULL ,
`id_alunno` int(11) NOT NULL,
`descrizione` VARCHAR( 128 ) NOT NULL ,
`id_gruppo` int(11) NOT NULL,
PRIMARY KEY ( `id` )
PRIMARY KEY (`id`)
) TYPE = innodb;
2
) ENGINE=innoDB;
Tipo di select per visualizzare i dati da più tabelle.
Una nota va fatta per i campi id (PrimaryKey - PK3) di ognuna delle tabelle create: il
parziale del comando `id` INT NOT NULL AUTO_INCREMENT `id` INT NOT NULL
AUTO_INCREMENT
indica che il campo id deve essere un intero, non nullo ed e’ di
tipo auto incrementanto. L’autoincremento fa si che se il campo corrispondente non è
specificato nella Insert, verrà assegnato ad esso il valore successivo al valore più alto
già presente nella tabella.
Prima di creare le Foreign Key dobbiamo creare un indice per ogni campo di FK con
la seguente istruzione SQL
ALTER TABLE tabella ADD INDEX (campo)
A questo punto possiamo creare le relazioni (Foreign Key – FK) per crearle dobbiamo
utilizzare il comando:
ALTER TABLE tabella ADD CONSTRAINT nome_foreign_key FOREIGN KEY nome_campo
REFERENCES tabella_destinazione (nome_campo) ON DELETE tipo_fk ON UPDATE tipo_fk.
Alla ForeignKey per poter operare servono quindi oltre alla tabella e al capo di
partenza e di arrivo anche le informazioni su come comportarsi nel caso di un
comando UPDATE o DELETE. Questi due comandi saranno descritti più avanti ma
introduciamo ora i quattro tipi di constraint associabili alle clausole ON DELETE e
ON UPDATE:

NO ACTION, (Identica alla RESTRICT).

RESTRICT, annulla tutte le operazioni di Update e Delete della
tabella_destinazione della FK.

CASCADE, dopo un operazione di Delete o Update alla tabella_destinazione
applica la Delete o Update ai campi corrispondenti.

SET NULL, dopo un operazione di Delete o Update alla tabella_destinazione
applica il valore nullo ai campi corrispondenti
3
Chiave che identifica univocamente, nella tabella un record, può essere formata da
uno o più campi.
I comandi di creazione indice e di creazioni foreign key possono essere concatenati
dopo una alter table come nell’esempio successivo:
ALTER TABLE tabella ADD INDEX (campo), ADD CONSTRAINT nome_foreign_key
FOREIGN KEY nome_campo REFERENCES tabella_destinazione (nome_campo) ON
DELETE tipo_fk ON UPDATE tipo_fk.
A questo punto possiamo creare le Foreign Key per le tabelle alunno e
alunno_gruppo in modo che il database risulti quello in figura 1:
ALTER TABLE `alunno`
ADD INDEX (`id_classe`),
ADD CONSTRAINT `alunno_classe_idclasse` FOREIGN KEY (`id_classe`) REFERENCES `classe`
(`id`) ON DELETE RESTRICT;
ALTER TABLE `alunno_gruppo` ADD INDEX (`id_alunno`);
ALTER TABLE `alunno_gruppo` ADD INDEX (`id_gruppo`);
ALTER TABLE `alunno_gruppo`
ADD CONSTRAINT `alunno_gruppo_idgruppo` FOREIGN KEY (`id_gruppo`) REFERENCES
`gruppo` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `alunno_gruppo_idalunno` FOREIGN KEY (`id_alunno`) REFERENCES
`alunno` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
Il database così creato risulta essere quello in figura 1. A questo punto abbiamo un
utente(lnf) e un database(lnf_demo) il quale è pronto per ricevere, ricercare,
aggiornare e cancellare i dati che dovranno essere gestiti da una nostra ipotetica
applicazione.
7 - Inserimento di valori nella tabella
Ora passeremo a popolare il database con dei dati di prova. Per questa operazione
l’SQL mette a disposizione il seguente comando:
INSERT INTO nome_tabella (colonna1,colonna2…colonnaN)
VALUES (valore1, valore 2….valoreN)
nota: nei comandi di insert, sotto riportati, nei campi non viene riportata colonna
dell’id e non ne viene inserito il valore nella sezione dei valori della insert. Questo e’
possibile in quanto abbiamo creato i campi id delle tabelle come auto-increment. In
questo modo tali campi saranno riempiti automaticamente dal database al momento
della insert.
Inserimento dei valori per la tabella delle classi:
insert into classe (nome,descrizione) values ('Prima','Prima Classe');
insert into classe (nome,descrizione) values ('Seconda','Seconda Classe');
Inserimento valori per la tabella degli alunni:
insert into alunno (nome,cognome,id_classe) values ('Mario','Rossi',1);
insert into alunno (nome,cognome,id_classe) values ('Mariolino','Rossi',1);
insert into alunno (nome,cognome,id_classe) values ('Luca','Rossini',2);
Inserimento valori per la tabella dei gruppi:
insert into gruppo (nome,descrizione) values ('Sport','Gruppo Sportivo');
insert into gruppo (nome,descrizione) values ('Musica','Gruppo Musicale');
insert into gruppo (nome,descrizione) values ('Cultura','Gruppo Culturale');
Ora che abbiamo popolato le tre babele principali ci rimane solamente di inserire i
valori che associeranno un alunno ad un gruppo:
Inserimento valori per la tabella delle associazioni alunni - gruppi:
insert into alunno_gruppo (id_alunno, id_gruppo) values (1,1);
insert into alunno_gruppo (id_alunno, id_gruppo) values (1,2);
insert into alunno_gruppo (id_alunno, id_gruppo) values (2,3);
Al primo alunno abbiamo associato due gruppi “Sport” e “Musica” al secondo
alunno il solo gruppo “Cultura”. Al terzo alunno non e’ stato associato un gruppo il
nostro database non esige l’associazione di un alunno ad un gruppo ma esige
l’associazione di un alunno ad una classe. Si noti, che a tale scopo, i valori nella
tabella delle classi sono stati i primi ad essere stati inseriti. Se provassimo ad
effettuare per prime le insert sulla tabella alunno, riceveremmo un errore di constraint
in quanto le classi, associate agli alunni con la FK, non sono ancora state inserite.
Questo e’ lo scopo delle Foreign Key, permettono di porre dei vincoli alle operazione
di modifica del database, forzano l’integrità strutturale dei dati che il programmatore
ha scelto per il suo progetto.
8 - Ricerca dei dati in una tabella
Il comando SQL Select permette di visualizzare i valori di una tabella specificando
delle condizioni per filtrare il risultato, la sua sintassi è:
SELECT
[DISTINCT]
campo1[,campo2...]
FROM tabella AS alias
[WHERE condizione_di_ricerca]
[ORDER BY condizione_di_ordinamento [ASC | DESC]]
Nella select quindi possiamo specificare quali colonne delle tabella interessata
vogliamo visualizzare, le condizioni che determinano quali record visualizzare ed
infine la condizione tramite la quale il risultato verrà ordinato.
Visualizzazione di tutte le colonne di tutti i record della tabella alunni ordinati
per nome:
select * from alunno order by nome;
Visualizzazione del nome e cognome di tutti i record che nel campo cognome
contengono ‘Rossi’:
select nome, cognome from alunno where cognome = ‘Rossi’;
Il simbolo * indica che devono essere usate tutte le colonne della tabella per
visualizzare i record. Per le condizioni di ricerca possono essere usate tutte le colonne
della tabella. Nella sezione [where] del comando, come gia detto, si possono usare
una o tutte le colonne a cui associare dei valori, ma in questo caso come risultato
avremo tutti quei record che avranno esattamente lo stesso valore nella stessa colonna.
Il problema nasce se si vuole effettuare una ricerca di tutti gli alunni il cui cognome
inizia con ‘Ross’. Se nella sezione della where mettessimo where cognome =
‘Ross’ la select non restituirà nessun valore. A questo scopo, per i campi testo, si
deve usare la parola chiave like. Questo comando sostituisce il simbolo ‘=’ dopo il
nome della colonna e permette di utilizzare il carattere ‘%’ come carattere jolly.
Visualizzazione di tutte le colonne di tutti i record della tabella alunno il cui
campo ‘cognome’ inizia con i caratteri ‘Ross’:
select * from alunno where cognome like 'Ross%';
Nel comando like il carattere jolly ‘%’ viene interpretato come equivalente a n
caratteri qualsiasi. Un altro carattere jolly che può essere usato è ‘_’ che equivale a un
solo carattere qualsiasi.
Visualizzazione di tutte le colonne di tutti i record della tabella alunno il cui
campo ‘cognome’ ha qualsiasi stringa formata da cinque caratteri qualsiasi:
select * from alunno where cognome like '_____';
9 - Modifica dei dati in una tabella
Per effettuare una modifica dei dati l’sql mette a disposizione il comando update,
sotto riportato nella sua versione semplificata:
UPDATE nome_tabella
SET colonna1 = valore1 [,
[WHERE condizione]
colonna2 = valore2 ...]
la condizione serve a filtrare i record che devono essere aggiornati, omettendo la
condizione where, la modifica viene eseguita su tutti i record della tabella.
Nel paragrafo precedente, abbiamo associato all’alunno Mario Rossi alla prima
classe, quando si avrà necessita di registrare la promozione, la classe a lui associata
dovrà cambiare e dovrà essere assegnato alla seconda classe ID_CLASSE = 2 il
comando sotto riportato effettua questa modifica. Essendo Mario il primo alunno
inserito il suo ID(Primary Key – auto increment) sarà ID=1, e per sincerarci di questa
cosa faremo la seguente select:
SELECT id FROM alunno WHERE nome = ‘Mario’ AND cognome = ‘Rossi’
Se dovesse essere diverso dovrà essere usato l’id riportato dalla select
Modifica della classe associata all’alunno Mario Rossi:
update alunno set id_classe = 2
where id = 1;
10 - Ricerca dei dati tra più tabelle
I comandi che abbiamo visto fino ad ora lavorano su una tabella alla volta. Il
comando Select permette di fare ricerche da più tabelle contemporaneamente. Questo
può avvenire facendo uso delle foreign key:
Lista di tutti gli alunni con in più il la descrizione della classe di appartenenza:
select a.nome, a.cognome, c.descrizione
from alunno a, classe c
where a.id_classe = c.id;
L’esempio sopra riportato produrrà il seguente output:
+-----------+---------+----------------+
| nome
| cognome | descrizione
|
+-----------+---------+----------------+
| Mario
| Rossi
| Prima Classe
|
| Mariolino | Rossi
| Prima Classe
|
| Luca
| Rossini | Seconda Classe |
La differenza delle select viste fin ora sta nel fatto che in quella dell’esempio viene
inserita una seconda tabella, quella della classe. I caratteri ‘a’ e ‘c’ sono usati come
alias per la tabella senza i quali avremmo dovuto usare, nella sezione dei campi della
select, i nomi della relativa tabella per ognuno di essi. Tra le tabelle alunno e classe
esiste una foreign key ed e’ quella che collega l’alunno(id_classe) alla classe(id).
quindi nella sezione della ‘where’ della select specifichiamo la condizione in modo da
informare il database come collegare le due tabelle. Nel nostro caso a.id_classe =
c.id;
Vogliamo ora visualizzare tutti gli alunni appartenenti alla prima classe, la select
diventerà:
Lista di tutti gli alunni con in più il la descrizione della classe di appartenenza:
select a.nome, a.cognome, c.descrizione
from alunno a, classe c
where a.id_classe = c.id
and c.id = 1;
L’esempio ci mostra come oltre alle condizioni di join possono essere usate altre
condizioni che soddisfano i criteri di ricerca desiderati. Vogliamo ora visualizzare i
gruppi associati ad ogni alunno. In questo caso le tabelle da prendere in
considerazione sono tre: alunno, alunno_gruppo, gruppo.
Lista degli alunni con la descrizione del gruppo di appartenenza:
select a.nome, a.cognome, g.descrizione
from alunno a, alunno_gruppo ag, gruppo g
where a.id = ag.id_alunno and ag.id_gruppo = g.id;
+-----------+---------+------------------+
| nome
| cognome | descrizione
|
+-----------+---------+------------------+
| Mariolino | Rossi
| Gruppo Culturale |
| Mario
| Rossi
| Gruppo Musicale |
| Mario
| Rossi
| Gruppo Sportivo |
+-----------+---------+------------------+
Come si vede, nel risultato della select manca l’unico alunno ‘Luca Rossini’ il quale
non e’ associato a nessun gruppo. Può essere necessario, a volte, avere tutti i record
della tabella di partenza anche se non c’è una associazione tra i valori delle tabelle in
join si parla in questo caso di left join. La situazione sopra descritta, riferita al nostro
caso, coincide con l’esigenza di avere tutti i nomi degli alunni, e per quelli non
associati ad un gruppo. La select che realizza questa situazione è:
Lista di tutti gli alunni con in più il la descrizione della classe di appartenenza:
select a.nome, a.cognome, g.descrizione
from alunno a left join (alunno_gruppo ag, gruppo g)
on a.id = ag.id_alunno
and ag.id_gruppo = g.id;
L’output sarà:
+-----------+---------+------------------+
| nome
| cognome | descrizione
|
+-----------+---------+------------------+
| Mario
| Rossi
| Gruppo Musicale |
| Mario
| Rossi
| Gruppo Sportivo |
| Mariolino | Rossi
| Gruppo Culturale |
| Luca
| Rossini | NULL
|
+-----------+---------+------------------+
Come vediamo la left join forza la selezione di tutti i record contenuti nella tabella
alunno e per quelli in relazione col gruppo ne mostra il nome , mentre per l’alunno
associato a nessun gruppo mostra il valore NULL.
11 - Cancellazione dei dati nella tabella
Le operazioni viste fino ad ora permettono l’inserimento, la modifica e la ricerca dei
dati inseriti in un database. Rimane infine un ultima operazione, la cancellazione.
DELETE FROM nome_tabella
[WHERE condizione]
Il comando necessita due informazioni per poter essere eseguito, il nome della tabella
e la condizione con cui effettuare la cancellazione:
Cancellazione di Mariolino Rossi dalla tabella ‘alunno’;
delete from alunno where nome='Mariolino';
Con il comando nell’esempio viene cancellato l’alunno Mariolino dalla tabella
‘alunno’. La tabella alunno_gruppo ha una foreign key in cascade verso la tabella
alunno e la tabella gruppo. Quindi ci aspettiamo che la relazione con l’alunno
‘Mariolino’ sia sparita automaticamente dalla tabella ‘alunno_gruppo’.
Visualizzazione del contenuto della tabella alunno_gruppo.
select * from alunno_gruppo;
L’output sarà:
+----+-----------+-----------+
| id | id_alunno | id_gruppo |
+----+-----------+-----------+
| 1 |
1 |
1 |
| 2 |
1 |
2 |
+----+-----------+-----------+
si vede quindi che la relazione e’ stata rispettata e il DBMS ha cancellato il record
dove ’id_alunno’ coincideva con il record relazionato e cancellato nella tabella
‘alunno’.
12 – Transazioni
Fino ad ora Mysql e’ stato usato in modalità ‘Auto Commit’. Ogni volta che viene
eseguito un comando di inserimento modifica o cancellazione, il database applica
l’operazione rendendola disponibile a qualsiasi sessione attiva sul database. Ogni
connessione apre una sessione di lavoro, quindi ogni comando impartito produrrà
degli effetti che saranno visti anche da altre connessioni.
Possono verificarsi delle situazioni in cui, prima di rendere effettive le modifiche
apportate, vanno modificate altre tabelle per poter rendere i dati integri. Altro scenario
possibile e’ quello in cui più pagine web debbano inserire dati nella stessa tabella e
alla fine l’utente ha la possibilità di salvare o annullare l’inserimento.
In entrambe le situazioni descritte abbiamo bisogno delle Transazioni. Di default
mysql non abilita le transazioni quindi per poterle attivare, dobbiamo usare il
comando:
Disabilitazione AUTO COMMIT
set autocommit 0
Abilitazione AUTO COMMIT
set autocommit 1
Disattivando la funzione AUTO COMMIT ogni comando inizia una transazione che
verrà terminata dai comandi:

ROLLBACK – Annulla la transazione in corso

COMMIT – Fissa i valori inseriti modificati o cancellati, rendendoli
disponibili alle altre connessioni.
Quando una transazione viene cominciata in una sessione, le altre non vedono le
modiche effettuate fino a che il comando commit le fissa e le rende disponibili.
Quindi i comandi sopra descritti vengono utilizzati per annullare o salvare le
modifiche apportate a tutto il database.