Structured Query Language: SQL
Maurizio Fermeglia
Università di Trieste
email: [email protected]
SQL
Structured Query Language
è un linguaggio con varie funzionalità:

contiene sia il DDL sia il DML;
esistono varie versioni dell’SQL;
vediamo gli aspetti essenziali non i dettagli
Linguaggio SQL
19 December, 2015 - slide 2
La storia di SQL
Dr. Codd + IBM = SYSTEM/R=SEQUEL (1970-74)
ORACLE (1979)
Berkeley + SQL = INGRES (1980)
IBM: SQL/DS e DB2 (1983)
dal 1983 ca., “standard di fatto”
ANSI SQL1 Standard (1986)
Sybase esce con RDBMS (1986)
SQL Access group pubblica le specifiche ODBC (1991)
ANSI SQL2 Standard (1992)
ODBC standard in 1993
1996 specifiche interrogazione OLAP
1999 standard SQL3 SQL 99(… scarso interesse industriale)
200n SQL 200n nuovo che introduce tante novità compreso SQL XML
SQLJ standard SQL all‘interno di programmi Java
Linguaggio SQL
19 December, 2015 - slide 3
Standard SQL
The following is a short, incomplete history of the SQL
Standards – ISO/IEC 9075
1987 – Initial ISO/IEC Standard
1989 – Referential Integrity
1992 – SQL2


1995 SQL/CLI (ODBC)
1996 SQL/PSM – Procedural Language extensions
1999 – User Defined Types
2003 – SQL/XML
2008 – Expansions and corrections
2011 (or 2012) System Versioned and Application Time
Period Tables
19 December 2015
Linguaggio SQL
Metadata Open Forum
4
19 December, 2015 - slide 4
SQL standard 1 2
Linguaggio SQL
SQL/1986
•
SQL/1989
•
SQL/1992
•
Basic features, that is
•
SQL/1986 plus
•
SQL/1989 plus
•
•
•
•
•
•
Tables
Columns
Views
Basic relational operations
Some integrity constraints
Language bindings to
COBOL, FORTRAN, C,
etc.
•
Partial
Referential
Integrity
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Entry Level SQL92
Enhanced constraints
Full Referential Integrity
Table constraints
Schema manipulation
Row & Table constraints
Beyond Entry Level SQL92
Assertions
Bit data type
CASE
Character Sets
Connection Management
DATETIME
Domains
Dynamic SQL
Get Diagnostics
Grouped operations
Information Schema
Multiple module support
National character sets
Natural joins (inner & outer)
Subqueries in check clauses
Temporary tables
Transaction Management
Union and intersect
19 December, 2015 - slide 5
SQL 99 standard: carattersitiche
•
SQL 1999 Feature
•
SQL Data Type: String (BLOB,
or Character (CLOB))
•
The ability to store either bit images or
large character documents
•
SQL Data Type: Boolean
•
The ability to specify boolean data
types, logic, and supporting rules
•
SQL Data Type: Ref Types
•
The ability to have a DBMS generated
or column value based pointer as a
reference between rows of different
tables.
•
SQL Data Type: Arrays
•
The ability to have an ordered list of
values within a column. Each value
may be a RefType. Each may also be a
ROW data structure
•
SQL Data Type: ROW Data
Structures
•
The ability to have groups of
“subcolumns” within a column. Each
may be an array or a RefType
•
SQL Data Type: User Defined
Types
•
The ability to completely define an nontraditional data type such as nautical
distance.
Triggers
•
•
Linguaggio SQL
Brief Description
The ability to specify the instigation of
an action as a consequence of a state
change in the database
•
Information Schema
•
A virtual database defined as virtual
tables and real SQL views on the virtual
tables that contain the complete set of
metadata in support of defined
databases.
•
Call Level Interface
•
The complete specification of a DBMS
vendor independent set of database
access routines similar to that
contained in the Microsoft ODBC
specification.
•
SQL Multi-Media: Full Text
•
The complete set of data structures,
special full-text operations and SQL
routines that support the loading,
accessing, and maintenance of full-text
type of data such as books,
manuscripts.
•
SQL Multi-Media: Spatial
•
The complete library or set of data
structures and routines that support
spatial data types and operations on
those data types
•
SQL Multi-Media: Still Image
•
The complete library or set of data
structures and routines that support still
image data types and operations on
those data types
•
SQL Programming Language
•
A complete SQL DBMS encapsulated
programming language that includes
traditional assignment, looping,
branching, If..Then...Else, and CASE
type constructs.
•
Savepoints
•
The ability to have cascading sets of
“soft” commits that can be rolled back
until there is a traditional hard-commit
•
Roles Security Enhancement
•
The ability to define additional layers
and kinds of security and the
assignment of persons fulfilling the
defined roles
•
Transaction, Connection,
Session, and Diagnostics
Management
•
The ability to specify sessions and the
management of those sessions in
support of centralized or distributed
type batch-type processing.
•
Recursion
•
The ability to fully model nested
relationships such as hierarchies for
organizations.
•
SQL/MED
•
The routines and facilities in support of
the management of types and classes
of data that exists outside the domain
of
19 December, 2015 - slide 6
SQL standard ‘in fieri’
SQL 2003: introduce l’uso di XML (poche
funzionalità), funzioni di finestre, valori autogenerati
(colonne identity)
SQL 2006: introduce Xquery (ISO/IEC 907514:2006 ) e completa l’uso di XML: import e
memorizzazioen di XML in DB, manipolazione di
XML.
SQL:2008 Legalizza uso di ORDER BY al di fuori
della definizione dei corsori. Aggiunge INSTEAD OF
nei triggers. Aggiunge TRUNCATE statement
Linguaggio SQL
19 December, 2015 - slide 7
SQL CONTROLLA TUTTE LE FUNZIONI DI
DBMS
Definizione dei dati (struttura e relazioni)
Estrazione dei dati
Manipolazione di dati
Controllo degli accessi (privilegi di accesso)
Condivisione dei dati
SQL Query
Integrità dei dati
DBMS
Data Base
Dati
Linguaggio SQL
19 December, 2015 - slide 8
Il ruolo di SQL (che cosa è)
Linguaggio
Linguaggio
Linguaggio
Linguaggio
Linguaggio
Linguaggio
Linguaggio SQL
di Query interattivo
di programmazione per data base
di amministrazione di data base
per il client/server
per la gestione di data base distribuiti
per la gestione di gateway
19 December, 2015 - slide 9
CARATTERISTICHE E BENEFICI....
Indipendenza dai venditori di HW e SW
Portabilità attraverso varie piattoforme HW
Coperto da standard internazioneli SQL1 ed SQL2 e SQL3
Strategico per IBM – Oracle – Micrsoft - …
Linguaggio per data base relazionali (unico)
Strutturo ad alto livello (English-like)
Linguaggio interattivo
Linguaggio programmatico (Statico - Dinamico - API)
In grado di fornire viste diverse del data base
Linguaggio completo (IF, triggers, ...) con T SQL e PL SQL
Definizione dinamica dei dati (anche quando in uso)
Client / server
Linguaggio SQL
19 December, 2015 - slide 10
SQL è strategico
per molti Hw e Sw
IL MITO DELLA PORTABILITA’
Ovvero ... non e’ vero che si puo fare tutto
Codici di errore non sono standard
I tipi di dati non sone sempre supportati
Le tabelle di sistema non sono uguali
Gli standard definiscono solo il SQL via programma, non
l’interattivo
Definisce solo lo Static Embedded Standard
Alcune differenze semantiche (funzioni, NULL)
Sorting secondo ASCII o EBCDIC (collating sequence e
codici)
Struttura delle tabelle
Linguaggio SQL
19 December, 2015 - slide 12
SQL LE BASI
Statements di manipolazione dei dati (DML)

SELECT - INSERT - DELETE - UPDATE
Statements di definizione dei dati (DDL)

CREATE/DROP/ALTER TABLE, VIEW, INDEX
Statements di accesso

GRANT e REVOKE
Statements di per processi transazionali

COMMIT - ROLLBACK
Statements programmatici

Linguaggio SQL
DECLARE - OPEN - FETCH - CLOSE
19 December, 2015 - slide 15
SQL: Le convenzioni
Sui NOMI
Sui TIPI DI DATI (interi - testo ,...)
Su COSTANTI
Su ESPRESSIONI
Su FUNZIONI
Linguaggio SQL
19 December, 2015 - slide 16
Definizione dei dati in SQL
Istruzione CREATE TABLE:


Linguaggio SQL
definisce uno schema di relazione e ne crea un’istanza vuota
specifica attributi, domini e vincoli
19 December, 2015 - slide 17
CREATE TABLE, esempio
CREATE TABLE Impiegato(
Matricola CHAR(6) PRIMARY KEY,
Nome CHAR(20) NOT NULL,
Cognome CHAR(20) NOT NULL,
Dipart CHAR(15),
Stipendio NUMERIC(9) DEFAULT 0,
FOREIGN KEY(Dipart) REFERENCES
Dipartimento(NomeDip),
UNIQUE (Cognome,Nome)
)
Linguaggio SQL
19 December, 2015 - slide 18
Domini
Domini elementari (predefiniti)
Domini definiti dall'utente (semplici, ma riutilizzabili)
Domini elementari
Carattere: singoli caratteri o stringhe, anche di
lunghezza variabile
Bit: singoli booleani o stringhe
Numerici, esatti e approssimati
Data, ora, intervalli di tempo
Introdotti in SQL:1999:


Linguaggio SQL
Boolean
BLOB, CLOB (binary/character large object): per grandi immagini e testi
19 December, 2015 - slide 19
Definizione di domini
Istruzione CREATE DOMAIN:

definisce un dominio (semplice), utilizzabile in definizioni di relazioni, anche
con vincoli e valori di default
CREATE DOMAIN, esempio
CREATE DOMAIN Voto
AS SMALLINT DEFAULT NULL
CHECK ( value >=18 AND value <= 30 )
Linguaggio SQL
19 December, 2015 - slide 20
Vincoli intrarelazionali
NOT NULL
UNIQUE definisce chiavi
PRIMARY KEY: chiave primaria (una sola, implica NOT
NULL)
CHECK, vedremo più avanti
UNIQUE e PRIMARY KEY
due forme:


Linguaggio SQL
nella definzione di un attributo, se forma da solo la chiave
come elemento separato
19 December, 2015 - slide 21
CREATE TABLE, esempio
CREATE TABLE Impiegato(
Matricola CHAR(6) PRIMARY KEY,
Nome CHAR(20) NOT NULL,
Cognome CHAR(20) NOT NULL,
Dipart CHAR(15),
Stipendio NUMERIC(9) DEFAULT 0,
FOREIGN KEY(Dipart) REFERENCES
Dipartimento(NomeDip),
UNIQUE (Cognome,Nome)
)
Linguaggio SQL
19 December, 2015 - slide 22
PRIMARY KEY, alternative
Matricola CHAR(6) PRIMARY KEY
Matricola CHAR(6),
…,
PRIMARY KEY (Matricola)
Linguaggio SQL
19 December, 2015 - slide 23
CREATE TABLE, esempio
CREATE TABLE Impiegato(
Matricola CHAR(6) PRIMARY KEY,
Nome CHAR(20) NOT NULL,
Cognome CHAR(20) NOT NULL,
Dipart CHAR(15),
Stipendio NUMERIC(9) DEFAULT 0,
FOREIGN KEY(Dipart) REFERENCES
Dipartimento(NomeDip),
UNIQUE (Cognome,Nome)
)
Linguaggio SQL
19 December, 2015 - slide 24
Chiavi su più attributi, attenzione
Nome
CHAR(20) NOT NULL,
Cognome CHAR(20) NOT NULL,
UNIQUE (Cognome,Nome),
Nome
CHAR(20) NOT NULL UNIQUE,
Cognome CHAR(20) NOT NULL UNIQUE,
Non è la stessa cosa!
Linguaggio SQL
19 December, 2015 - slide 25
Vincoli interrelazionali
CHECK, vedremo più avanti
REFERENCES e FOREIGN KEY permettono di definire vincoli di integrità
referenziale
di nuovo due sintassi


per singoli attributi
su più attributi
E' possibile definire politiche di reazione alla violazione
Linguaggio SQL
19 December, 2015 - slide 26
Infrazioni
Codice
Data
Vigile
Prov
Numero
34321
1/2/95
3987
MI
39548K
53524
4/3/95
3295
TO
E39548
64521
5/4/96
3295
PR
839548
73321
5/2/98
9345
PR
839548
Matricola
Cognome
Nome
3987
Rossi
Luca
3295
Neri
Piero
9345
Neri
Mario
7543
Mori
Gino
Vigili
Linguaggio SQL
19 December, 2015 - slide 27
Infrazioni
Codice
Data
Vigile
Prov
Numero
34321
1/2/95
3987
MI
39548K
53524
4/3/95
3295
TO
E39548
64521
5/4/96
3295
PR
839548
73321
5/2/98
9345
PR
839548
Auto
Linguaggio SQL
Prov
Numero
Cognome
Nome
MI
39548K
Rossi
Mario
TO
E39548
Rossi
Mario
PR
839548
Neri
Luca
19 December, 2015 - slide 28
CREATE TABLE, esempio
CREATE TABLE Infrazioni(
Codice CHAR(6) NOT NULL PRIMARY KEY,
Data DATE NOT NULL,
Vigile INTEGER NOT NULL
REFERENCES Vigili(Matricola),
Provincia CHAR(2),
Numero CHAR(6) ,
FOREIGN KEY(Provincia, Numero)
REFERENCES Auto(Provincia, Numero)
)
Linguaggio SQL
19 December, 2015 - slide 29
Modifiche degli schemi
ALTER DOMAIN
ALTER TABLE
DROP DOMAIN
DROP TABLE
...
Linguaggio SQL
19 December, 2015 - slide 30
Definzione degli indici
è rilevante dal punto di vista delle prestazioni
ma è a livello fisico e non logico
in passato era importante perché in alcuni sistemi
era l'unico mezzo per definire chiavi
CREATE INDEX
Vedremo dettagli nella progettazione fisica
Linguaggio SQL
19 December, 2015 - slide 31
DDL, in pratica
In molti sistemi si utilizzano strumenti diversi dal
codice SQL per definire lo schema della base di dati
Linguaggio SQL
19 December, 2015 - slide 32
SQL, operazioni sui dati
interrogazione:

SELECT
modifica:

Linguaggio SQL
INSERT, DELETE, UPDATE
19 December, 2015 - slide 33
Istruzione SELECT (versione base)
SELECT ListaAttributi
FROM ListaTabelle
[ WHERE Condizione ]
"target list"
clausola FROM
clausola WHERE
Linguaggio SQL
19 December, 2015 - slide 34
selezione
proiezione
Linguaggio SQL
19 December, 2015 - slide 35
Maternità
Paternità
Linguaggio SQL
Madre
Luisa
Luisa
Anna
Anna
Maria
Maria
Figlio
Maria
Luigi
Olga
Filippo
Andrea
Aldo
Padre
Figlio
Sergio Franco
Olga
Luigi
Luigi
Filippo
Franco Andrea
Franco
Aldo
Persone
Nome
Andrea
Aldo
Maria
Anna
Filippo
Luigi
Franco
Olga
Sergio
Luisa
Età Reddito
27
21
25
15
55
42
50
35
26
30
50
40
60
20
30
41
85
35
75
87
19 December, 2015 - slide 36
Selezione e proiezione
Nome e reddito delle persone con meno di trenta
anni
PROJNome, Reddito(SELEta<30(Persone))
select nome, reddito
from persone
where eta < 30
Linguaggio SQL
19 December, 2015 - slide 37
Persone
Andrea
Nome
Aldo
Filippo
Maria
Anna
Filippo
Luigi
Franco
Olga
Sergio
Luisa
Linguaggio SQL
Reddito
27 21Reddito
21
Età
25 15 15
55 30 30
26
42
50
35
26
30
50
40
60
20
30
41
85
35
75
87
19 December, 2015 - slide 38
SELECT, abbreviazioni
select nome, reddito
from persone
where eta < 30
select p.nome as nome,
p.reddito as reddito
from persone p
where p.eta < 30
Linguaggio SQL
19 December, 2015 - slide 39
Selezione, senza proiezione
Nome, età e reddito delle persone con meno di trenta anni
SELEta<30(Persone)
select *
from persone
where eta < 30
Linguaggio SQL
19 December, 2015 - slide 40
SELECT, abbreviazioni
select *
from persone
where eta < 30
select nome, età, reddito
from persone
where eta < 30
Linguaggio SQL
19 December, 2015 - slide 41
Proiezione, senza selezione
Nome e reddito di tutte le persone
PROJNome, Reddito(Persone)
select nome, reddito
from persone
Linguaggio SQL
19 December, 2015 - slide 42
SELECT, abbreviazioni
R(A,B)
select *
from R
equivale (intuitivamente) a
select X.A as A, X.B as B
from R X
where true
Linguaggio SQL
19 December, 2015 - slide 43
Espressioni nella target list
select Reddito/2 as redditoSemestrale
from Persone
where Nome = 'Luigi'
Linguaggio SQL
19 December, 2015 - slide 44
Condizione complessa
select *
from persone
where reddito > 25
and (eta < 30 or eta > 60)
Linguaggio SQL
19 December, 2015 - slide 45
Condizione “LIKE”
Le persone che hanno un nome che inizia per 'A' e ha
una 'd' come terza lettera
select *
from persone
where nome like 'A_d%'
Linguaggio SQL
19 December, 2015 - slide 46
Gestione dei valori nulli
Impiegati
Matricola
7309
5998
5998
9553
9553

Cognome
Rossi
Neri
Bruni
Neri
Bruni
Filiale
Milano
Roma
Milano
Milano
Età
32
45
NULL
45
NULL
Gli impiegati la cui età è o
potrebbe essere maggiore di 40
SEL Età > 40 OR Età IS NULL (Impiegati)
Linguaggio SQL
19 December, 2015 - slide 47
Gli impiegati la cui età è o
potrebbe essere maggiore di 40
SEL Età > 40 OR Età IS NULL (Impiegati)
select *
from impiegati
where eta > 40 or eta is null
Linguaggio SQL
19 December, 2015 - slide 48
Selezione, proiezione e join
Istruzioni SELECT con una sola relazione nella
clausola FROM permettono di realizzare:

selezioni, proiezioni, ridenominazioni
con più relazioni nella FROM si realizzano join (e
prodotti cartesiani)
Linguaggio SQL
19 December, 2015 - slide 49
SQL: esecuzione delle interrogazioni
Le espressioni SQL sono dichiarative e noi ne stiamo
vedendo la semantica
In pratica, i DBMS eseguono le operazioni in modo
efficiente, ad esempio:


Linguaggio SQL
eseguono le selezioni al più presto
se possibile, eseguono join e non prodotti cartesiani
19 December, 2015 - slide 54
SQL: speciifca delle interrogazioni
La capacità dei DBMS di "ottimizzare" le
interrogazioni, rende (di solito) non necessario
preoccuparsi dell'efficienza quando si specifica
un'interrogazione
È perciò più importante preoccuparsi della chiarezza
(anche perché così è più difficile sbagliare …)
Linguaggio SQL
19 December, 2015 - slide 55
Proiezione, attenzione
• cognome e filiale di tutti gli impiegati
Matricola
7309
5998
9553
5698
Cognome
Neri
Neri
Rossi
Rossi
Filiale
Napoli
Milano
Roma
Roma
Stipendio
55
64
44
64
PROJ Cognome, Filiale (Impiegati)
Linguaggio SQL
19 December, 2015 - slide 56
select distinct
cognome, filiale
from impiegati
select
cognome, filiale
from impiegati
Cognome
Neri
Neri
Rossi
Rossi
Linguaggio SQL
Filiale
Napoli
Milano
Roma
Roma
Cognome
Neri
Neri
Rossi
Filiale
Napoli
Milano
Roma
19 December, 2015 - slide 57
Maternità
Paternità
Linguaggio SQL
Madre
Luisa
Luisa
Anna
Anna
Maria
Maria
Figlio
Maria
Luigi
Olga
Filippo
Andrea
Aldo
Padre
Figlio
Sergio Franco
Olga
Luigi
Luigi
Filippo
Franco Andrea
Franco
Aldo
Persone
Nome
Andrea
Aldo
Maria
Anna
Filippo
Luigi
Franco
Olga
Sergio
Luisa
Età Reddito
27
21
25
15
55
42
50
35
26
30
50
40
60
20
30
41
85
35
75
87
19 December, 2015 - slide 58
Selezione, proiezione e join
I padri di persone che guadagnano più di venti milioni
PROJPadre(paternita
JOIN Figlio =Nome
SELReddito>20 (persone))
select distinct padre
from persone, paternita
where figlio = nome and reddito > 20
Linguaggio SQL
19 December, 2015 - slide 59
Join naturale
Padre e madre di ogni persona
paternita JOIN maternita
select paternita.figlio,padre, madre
from maternita, paternita
where paternita.figlio = maternita.figlio
Linguaggio SQL
19 December, 2015 - slide 60
Le persone che guadagnano più dei rispettivi padri; mostrare nome, reddito e
reddito del padre
PROJNome, Reddito, RP (SELReddito>RP
(RENNP,EP,RP  Nome,Eta,Reddito(persone)
JOINNP=Padre
(paternita JOIN Figlio =Nome persone)))
select f.nome, f.reddito, p.reddito
from persone p, paternita, persone f
where p.nome = padre and
figlio = f.nome and
f.reddito > p.reddito
Linguaggio SQL
19 December, 2015 - slide 61
SELECT, con ridenominazione del risultato
select figlio, f.reddito as reddito,
p.reddito as redditoPadre
from persone p, paternita, persone f
where p.nome = padre and figlio = f.nome
and .reddito > p.reddito
Linguaggio SQL
19 December, 2015 - slide 62
Join esplicito
Padre e madre di ogni persona
select paternita.figlio,padre, madre
from maternita, paternita
where paternita.figlio = maternita.figlio
select madre, paternita.figlio, padre
from maternita join paternita on
paternita.figlio = maternita.figlio
Linguaggio SQL
19 December, 2015 - slide 63
SELECT con join esplicito, sintassi
SELECT …
FROM Tabella { … JOIN Tabella ON CondDiJoin }, …
[ WHERE AltraCondizione ]
Linguaggio SQL
19 December, 2015 - slide 64

Le persone che guadagnano più dei rispettivi padri; mostrare nome, reddito
e reddito del padre
select f.nome, f.reddito, p.reddito
from persone p, paternita, persone f
where p.nome = padre and
figlio = f.nome and
f.reddito > p.reddito
select f.nome, f.reddito, p.reddito
from persone p join paternita on p.nome = padre
join persone f on figlio = f.nome
where f.reddito > p.reddito
Linguaggio SQL
19 December, 2015 - slide 65
Ulteriore estensione: join naturale
(meno diffuso)
PROJFiglio,Padre,Madre(
paternita JOIN Figlio = Nome REN Nome=Figlio(maternita))
paternita JOIN maternita
select madre, paternita.figlio, padre
from maternita join paternita on
paternita.figlio = maternita.figlio
select madre, paternita.figlio, padre
from maternita natural join paternita
Linguaggio SQL
19 December, 2015 - slide 66
Join esterno: "outer join"
Padre e, se nota, madre di ogni persona
select paternita.figlio, padre, madre
from paternita left join maternita
on paternita.figlio = maternita.figlio
select paternita.figlio, padre, madre
from paternita left outer join maternita
on paternita.figlio = maternita.figlio
outer e' opzionale
Linguaggio SQL
19 December, 2015 - slide 67
Outer join
select paternita.figlio, padre, madre
from maternita join paternita
on maternita.figlio = paternita.figlio
select paternita.figlio, padre, madre
from maternita left outer join paternita
on maternita.figlio = paternita.figlio
select paternita.figlio, padre, madre
from maternita full outer join paternita
on maternita.figlio = paternita.figlio
Linguaggio SQL
19 December, 2015 - slide 68
Ordinamento del risultato
Nome e reddito delle persone con meno di trenta anni
in ordine alfabetico
select nome, reddito
from persone
where eta < 30
order by nome
Linguaggio SQL
19 December, 2015 - slide 69
select nome, reddito
from persone
where eta < 30
select nome, reddito
from persone
where eta < 30
order by nome
Persone
Persone
Nome Reddito
Andrea
21
Aldo
15
Filippo
30
Nome Reddito
Aldo
15
Andrea
21
Filippo
30
Linguaggio SQL
19 December, 2015 - slide 70
Operatori aggregati
• Nelle espressioni della target list possiamo avere anche espressioni che
calcolano valori a partire da insiemi di ennuple:


Linguaggio SQL
conteggio, minimo, massimo, media, totale
sintassi base (semplificata):
Funzione ( [ DISTINCT ] * )
Funzione ( [ DISTINCT ] Attributo )
19 December, 2015 - slide 71
Operatori aggregati: COUNT
• Il numero di figli di Franco
select count(*) as NumFigliDiFranco
from Paternita
where Padre = 'Franco'
l’operatore aggregato (count) viene applicato al risultato dell’interrogazione:
select *
from Paternita
where Padre = 'Franco'
Linguaggio SQL
19 December, 2015 - slide 72
Paternità
Padre
Figlio
Sergio Franco
Olga
Luigi
Luigi
Filippo
Franco Andrea
Franco
Aldo
NumFigliDiFranco
2
Linguaggio SQL
19 December, 2015 - slide 73
COUNT e valori nulli
select count(*) from persone
select count(reddito) from persone
select count(distinct reddito) from persone
Persone
Linguaggio SQL
Nome
Andrea
Aldo
Maria
Anna
Età
27
25
55
50
Reddito
21
NULL
21
35
19 December, 2015 - slide 74
Altri operatori aggregati
SUM, AVG, MAX, MIN
Media dei redditi dei figli di Franco
select avg(reddito)
from persone join paternita on nome=figlio
where padre='Franco'
Linguaggio SQL
19 December, 2015 - slide 75
Operatori aggregati e valori nulli
select avg(reddito) as redditomedio
from persone
Persone
Linguaggio SQL
Nome
Andrea
Aldo
Maria
Anna
Età
27
25
55
50
Reddito
30
NULL
36
36
19 December, 2015 - slide 76
Operatori aggregati e target list
un’interrogazione scorretta:
select nome, max(reddito)
from persone
di chi sarebbe il nome? La target list deve essere omogenea
select min(eta), avg(reddito)
from persone
Linguaggio SQL
19 December, 2015 - slide 77
Operatori aggregati e raggruppamenti
Le funzioni possono essere applicate a partizioni delle relazioni
Clausola GROUP BY:
GROUP BY listaAttributi
Linguaggio SQL
19 December, 2015 - slide 78
Operatori aggregati e raggruppamenti
Il numero di figli di ciascun padre
select padre, count(*) AS NumFigli
from paternita
group by Padre
paternita
Linguaggio SQL
Padre
Sergio
Luigi
Luigi
Franco
Franco
Figlio
Franco
Olga
Filippo
Andrea
Aldo
Padre
Sergio
Luigi
Franco
NumFigli
1
2
2
19 December, 2015 - slide 79
Semantica di interrogazioni
con operatori aggregati e raggruppamenti
1. interrogazione senza group by e senza operatori aggregati
select *
from paternita
2. si raggruppa e si applica l’operatore aggregato a ciascun gruppo
Linguaggio SQL
19 December, 2015 - slide 80
Raggruppamenti e target list
scorretta
select padre, avg(f.reddito), p.reddito
from persone f join paternita on figlio = nome join
persone p on padre =p.nome
group by padre
corretta
select padre, avg(f.reddito), p.reddito
from persone f join paternita on figlio = nome join
persone p on padre =p.nome
group by padre, p.reddito
Linguaggio SQL
19 December, 2015 - slide 81
Condizioni sui gruppi
I padri i cui figli hanno un reddito medio maggiore di 25
select padre, avg(f.reddito)
from persone f join paternita on figlio = nome
group by padre
having avg(f.reddito) > 25
Linguaggio SQL
19 December, 2015 - slide 82
WHERE o HAVING?
I padri i cui figli sotto i 30 anni hanno un reddito medio maggiore di 20
select padre, avg(f.reddito)
from persone f join paternita on figlio = nome
where eta < 30
group by padre
having avg(f.reddito) > 25
Linguaggio SQL
19 December, 2015 - slide 83
Sintassi, riassumiamo
SelectSQL ::=
select ListaAttributiOEspressioni
from ListaTabelle
[ where CondizioniSemplici ]
[ group by ListaAttributiDiRaggruppamento ]
[ having CondizioniAggregate ]
[ order by ListaAttributiDiOrdinamento ]
Linguaggio SQL
19 December, 2015 - slide 84
Unione, intersezione e differenza
La select da sola non permette di fare unioni; serve un costrutto
esplicito:
select …
union [all]
select ...
i duplicati vengono eliminati (a meno che si usi all); anche dalle
proiezioni!
Linguaggio SQL
19 December, 2015 - slide 85
Notazione posizionale!
select padre
from paternita
union
select madre
from maternita
quali nomi per gli attributi del risultato?



Linguaggio SQL
nessuno
quelli del primo operando
…
19 December, 2015 - slide 86
Figlio
Sergio Franco
Olga
Luigi
Luigi
Filippo
Franco Andrea
Franco
Aldo
Luisa
Maria
Luigi
Luisa
Olga
Anna
Anna Filippo
Maria Andrea
Maria
Aldo
Linguaggio SQL
Padre
Figlio
Sergio Franco
Olga
Luigi
Luigi
Filippo
Franco Andrea
Franco
Aldo
Luisa
Maria
Luigi
Luisa
Olga
Anna
Anna Filippo
Maria Andrea
Maria
Aldo
19 December, 2015 - slide 87
Notazione posizionale, 2
select padre, figlio
from paternita
union
select figlio, madre
from maternita
Linguaggio SQL
select padre, figlio
from paternita
union
select madre, figlio
from maternita
19 December, 2015 - slide 88
Notazione posizionale, 3
Anche con le ridenominazioni non cambia niente:
select padre as genitore, figlio
from paternita
union
select figlio, madre as genitore
from maternita
Corretta:
select padre as genitore, figlio
from paternita
union
select madre as genitore, figlio
from maternita
Linguaggio SQL
19 December, 2015 - slide 89
Differenza
select Nome
from Impiegato
except
select Cognome as Nome
from Impiegato
vedremo che si può esprimere con select nidificate
Linguaggio SQL
19 December, 2015 - slide 90
Intersezione
select Nome
from Impiegato
intersect
select Cognome as Nome
from Impiegato

equivale a
select I.Nome
from Impiegato I, Impiegato J
where I.Nome = J.Cognome
Linguaggio SQL
19 December, 2015 - slide 91
Interrogazioni nidificate
le condizioni atomiche permettono anche


Linguaggio SQL
il confronto fra un attributo (o più, vedremo poi) e il risultato di una
sottointerrogazione
quantificazioni esistenziali
19 December, 2015 - slide 92
nome e reddito del padre di Franco
select Nome, Reddito
from Persone, Paternita
where Nome = Padre and Figlio = 'Franco'
select Nome, Reddito
from Persone
where Nome =
(
Linguaggio SQL
select Padre
from Paternita
where Figlio = 'Franco')
19 December, 2015 - slide 93
Interrogazioni nidificate, commenti
La forma nidificata è “meno dichiarativa”, ma
talvolta più leggibile (richiede meno variabili)
La forma piana e quella nidificata possono essere
combinate
Le sottointerrogazioni non possono contenere
operatori insiemistici (“l’unione si fa solo al livello
esterno”); la limitazione non è significativa
Linguaggio SQL
19 December, 2015 - slide 94
Nome e reddito dei padri di persone che guadagnano più di 20 milioni
select distinct P.Nome, P.Reddito
from Persone P, Paternita, Persone F
where P.Nome = Padre and Figlio = F.Nome
and F.Reddito > 20
select Nome, Reddito
from Persone
where Nome in (select Padre
from Paternita
where Figlio = any (select Nome
from Persone
where Reddito > 20))
Linguaggio SQL
19 December, 2015 - slide 95
Nome e reddito dei padri di persone che guadagnano più di 20 milioni
select distinct P.Nome, P.Reddito
from Persone P, Paternita, Persone F
where P.Nome = Padre and Figlio = F.Nome
and F.Reddito > 20
select Nome, Reddito
from Persone
where Nome in (select Padre
from Paternita, Persone
where Figlio = Nome
and Reddito > 20)
Linguaggio SQL
19 December, 2015 - slide 96
Interrogazioni nidificate, commenti, 2
La prima versione di SQL prevedeva solo la forma
nidificata (o strutturata), con una sola relazione in
ogni clausola FROM. Insoddisfacente:


Linguaggio SQL
la dichiaratività è limitata
non si possono includere nella target list attributi di relazioni nei blocchi
interni
19 December, 2015 - slide 97
Quantificazione esistenziale

Ulteriore tipo di condizione
 EXISTS ( Sottoespressione )
Linguaggio SQL
19 December, 2015 - slide 100
Le persone che hanno almeno un figlio
select *
from Persone
where exists (
select *
exists ( select *
Linguaggio SQL
from Paternita
where Padre = Nome) or
from Maternita
where Madre = Nome)
19 December, 2015 - slide 101
I padri i cui figli guadagnano tutti più di venti milioni
select distinct Padre
from Paternita Z
where not exists (
select *
from Paternita W, Persone
where W.Padre = Z.Padre
and W.Figlio = Nome
and Reddito <= 20)
Linguaggio SQL
19 December, 2015 - slide 102
Semantica delle espressioni “correlate”
L’interrogazione interna viene eseguita una volta per
ciascuna ennupla dell’interrogazione esterna
Linguaggio SQL
19 December, 2015 - slide 103
Operazioni di aggiornamento
operazioni di



inserimento: insert
eliminazione: delete
modifica: update
di una o più ennuple di una relazione
sulla base di una condizione che può coinvolgere
anche altre relazioni
Linguaggio SQL
19 December, 2015 - slide 108
Inserimento
INSERT INTO Tabella [ ( Attributi ) ]
VALUES( Valori )
oppure
INSERT INTO Tabella [ ( Attributi )]
SELECT ...
Linguaggio SQL
19 December, 2015 - slide 109
INSERT INTO Persone VALUES ('Mario',25,52)
INSERT INTO Persone(Nome, Eta, Reddito)
VALUES('Pino',25,52)
INSERT INTO Persone(Nome, Reddito)
VALUES('Lino',55)
INSERT INTO Persone ( Nome )
SELECT Padre
FROM Paternita
WHERE Padre NOT IN (SELECT Nome
FROM Persone)
Linguaggio SQL
19 December, 2015 - slide 110
Inserimento , commenti
l’ordinamento degli attributi (se presente) e dei valori è significativo
le due liste debbono avere lo stesso numero di elementi
se la lista di attributi è omessa, si fa riferimento a tutti gli attributi della
relazione, secondo l’ordine con cui sono stati definiti
se la lista di attributi non contiene tutti gli attributi della relazione, per gli altri
viene inserito un valore nullo (che deve essere permesso) o un valore di
default
Linguaggio SQL
19 December, 2015 - slide 111
Eliminazione di ennuple
DELETE FROM Tabella
[ WHERE Condizione ]
Linguaggio SQL
19 December, 2015 - slide 112
DELETE FROM Persone
WHERE Eta < 35
DELETE FROM Paternita
WHERE Figlio NOT in (
SELECT Nome
FROM Persone)
DELETE FROM Paternita
Linguaggio SQL
19 December, 2015 - slide 113
Eliminazione, commenti
elimina le ennuple che soddisfano la condizione
può causare (se i vincoli di integrità referenziale sono definiti con
politiche di reazione cascade) eliminiazioni da altre relazioni
ricordare: se la where viene omessa, si intende where true
Linguaggio SQL
19 December, 2015 - slide 114
Modifica di ennuple
UPDATE NomeTabella
SET Attributo = < Espressione |
SELECT … |
NULL |
DEFAULT >
[ WHERE Condizione ]
Linguaggio SQL
19 December, 2015 - slide 115
UPDATE Persone SET Reddito = 45
WHERE Nome = 'Piero'
UPDATE Persone
SET Reddito = Reddito * 1.1
WHERE Eta < 30
Linguaggio SQL
19 December, 2015 - slide 116
Vincoli di integrità generici: check
Specifica di vincoli di ennupla (e anche vincoli più complessi)
check ( Condizione )
Linguaggio SQL
19 December, 2015 - slide 117
Check, esempio
create table Impiegato
(
Matricola character(6),
Cognome character(20),
Nome character(20),
Sesso character not null check (sesso in (‘M’,‘F’))
Stipendio integer,
Superiore character(6),
check (Stipendio <= (select Stipendio
from Impiegato J
where Superiore = J.Matricola)
)
Linguaggio SQL
19 December, 2015 - slide 118
Vincoli di integrità generici: asserzioni
Specifica vincoli a livello di schema
create assertion NomeAss check ( Condizione )
create assertion AlmenoUnImpiegato
check (1 <= (
select count(*)
from Impiegato ))
Linguaggio SQL
19 December, 2015 - slide 119
Viste
create view NomeVista [ ( ListaAttributi ) ] as SelectSQL
[ with [ local | cascaded ] check option ]
create view ImpiegatiAmmin
(Matricola, Nome, Cognome, Stipendio) as
select Matricola, Nome, Cognome, Stipendio
from Impiegato
where Dipart = 'Amministrazione' and
Stipendio > 10
Linguaggio SQL
19 December, 2015 - slide 120
Interrogazioni sulle viste
Possono fare riferimento alle viste come se fossero
relazioni di base
select * from ImpiegatiAmmin
equivale a (e viene eseguita come)
select Nome, Cognome, Stipendio
from Impiegato
where Dipart = 'Amministrazione' and
Stipendio > 10
Linguaggio SQL
19 December, 2015 - slide 121
Aggiornamenti sulle viste
Ammessi (di solito) solo su viste definite su una sola
relazione
Alcune verifiche possono essere imposte
Linguaggio SQL
19 December, 2015 - slide 122
create view ImpiegatiAmminPoveri as
select *
from ImpiegatiAmmin
where Stipendio < 50
with check option
check option permette modifiche, ma solo a condizione che la ennupla continui
ad appartenere alla vista (non posso modificare lo stipendio portandolo a 60)
Linguaggio SQL
19 December, 2015 - slide 123
Un’interrogazione non standard
La nidificazione nella having non è ammessa
select Dipart
from Impiegato
group by Dipart
having sum(Stipendio) >= all
(select sum(Stipendio)
from Impiegato
group by Dipart)
Linguaggio SQL
19 December, 2015 - slide 124
Soluzione con le viste
create view BudgetStipendi(Dip,TotaleStipendi) as
select Dipart, sum(Stipendio)
from Impiegato
group by Dipart
select Dip
from BudgetStipendi
where TotaleStipendi =(select max(TotaleStipendi)
from BudgetStipendi)
Linguaggio SQL
19 December, 2015 - slide 125
Ancora sulle viste
Interrogazione scorretta
select avg(count(distinct Ufficio))
from Impiegato
group by Dipart
Con una vista
create view DipartUffici(NomeDip,NroUffici) as
select Dipart, count(distinct Ufficio)
from Impiegato
group by Dipart;
select avg(NroUffici)
from DipartUffici
Linguaggio SQL
19 December, 2015 - slide 126
Funzioni scalari
Funzioni a livello di ennupla che restituiscono singoli valori
Temporali
 current_date, extract(year from …)
Manipolazione stringhe
 char_length, lower
Conversione
 cast
Condizionali
 …
Linguaggio SQL
19 December, 2015 - slide 129
Funzioni condizionali
Case, coalesce, nullif
select Nome, Cognome, coalesce(Dipart,'Ignoto')
from Impiegato
select Targa,
case Tipo
when 'Auto' then 2.58 * KWatt
when 'Moto' then (22.00 + 1.00 * KWatt)
else null
end as Tassa
from Veicolo
where Anno > 1975
Linguaggio SQL
19 December, 2015 - slide 130
Controllo dell'accesso
In SQL è possibile specificare chi (utente) e come (lettura, scrittura, …) può
utilizzare la base di dati (o parte di essa)
Oggetto dei privilegi (diritti di accesso) sono di solito le tabelle, ma anche altri
tipi di risorse, quali singoli attributi, viste o domini
Un utente predefinito _system (amministratore della base di dati) ha tutti i
privilegi
Il creatore di una risorsa ha tutti i privilegi su di essa
Linguaggio SQL
19 December, 2015 - slide 131
Privilegi
Un privilegio è caratterizzato da:





Linguaggio SQL
la risorsa cui si riferisce
l'utente che concede il privilegio
l'utente che riceve il privilegio
l'azione che viene permessa
la trasmissibilità del privilegio
19 December, 2015 - slide 132
Tipi di privilegi offerti da SQL
insert: permette di inserire nuovi oggetti (ennuple)
update: permette di modificare il contenuto
delete: permette di eliminare oggetti
select: permette di leggere la risorsa
references: permette la definizione di vincoli di integrità referenziale verso la
risorsa (può limitare la possibilità di modificare la risorsa)
usage: permette l'utilizzo in una definizione (per esempio, di un dominio)
Linguaggio SQL
19 December, 2015 - slide 133
grant e revoke
Concessione di privilegi:
grant < Privileges | all privileges > on Resource
to Users [ with grant option ]

grant option specifica se il privilegio può essere trasmesso ad altri utenti
grant select on Department to Stefano
Revoca di privilegi
revoke Privileges on Resource from Users
[ restrict | cascade ]
Linguaggio SQL
19 December, 2015 - slide 134
Autorizzazioni, commenti
Come autorizzare un utente a vedere solo alcune
ennuple di una relazione?

Attraverso una vista:
 Definiamo la vista con una condizione di selezione
 Attribuiamo le autorizzazioni sulla vista, anziché sulla relazione di base
Linguaggio SQL
19 December, 2015 - slide 136
Transazione
Insieme di operazioni da considerare indivisibile
("atomico"), corretto anche in presenza di concorrenza
e con effetti definitivi
Proprietà ("acide"):




Linguaggio SQL
Atomicità
Consistenza
Isolamento
Durabilità (persistenza)
19 December, 2015 - slide 138
Le transazioni sono … atomiche
La sequenza di operazioni sulla base di dati viene
eseguita per intero o per niente:

Linguaggio SQL
trasferimento di fondi da un conto A ad un conto B: o si fanno il
prelevamento da A e il versamento su B o nessuno dei due
19 December, 2015 - slide 139
Le transazioni sono … consistenti
Al termine dell'esecuzione di una transazione, i
vincoli di integrità debbono essere soddisfatti
"Durante" l'esecuzione ci possono essere violazioni,
ma se restano alla fine allora la transazione deve
essere annullata per intero ("abortita")
Linguaggio SQL
19 December, 2015 - slide 140
Le transazioni sono … isolate
L'effetto di transazioni concorrenti deve essere
coerente (ad esempio "equivalente" all'esecuzione
separata)

Linguaggio SQL
se due assegni emessi sullo stesso conto corrente vengono incassati
contemporaneamente si deve evitare di trascurarne uno
19 December, 2015 - slide 141
I risultati delle transazioni sono durevoli
La conclusione positiva di una transazione
corrisponde ad un impegno (in inglese commit) a
mantenere traccia del risultato in modo definitivo,
anche in presenza di guasti e di esecuzione
concorrente
Linguaggio SQL
19 December, 2015 - slide 142
Transazioni in SQL
Istruzioni fondamentali



Linguaggio SQL
begin transaction: specifica l'inizio della transazione (le operazioni non vengono
eseguite sulla base di dati)
commit work: le operazioni specificate a partire dal begin transaction vengono
eseguite
rollback work: si rinuncia all'esecuzione delle operazioni specificate dopo l'ultimo begin
transaction
19 December, 2015 - slide 143
Una transazione in SQL
begin transaction;
update ContoCorrente
set Saldo = Saldo – 10
where NumeroConto = 12345 ;
update ContoCorrente
set Saldo = Saldo + 10
where NumeroConto = 55555 ;
commit work;
Linguaggio SQL
19 December, 2015 - slide 144
Tecnologia DBMS >> Concetti Avanzati >> Transazioni
Transazioni
Fine della transazione

istruzioni COMMIT e ROLLBACK (ABORT)
(standard SQL-92)
Esito della transazione




Linguaggio SQL
esecuzione come unità indivisibile
COMMIT: rende permanenti le operazioni
errore: operazioni annullate dal sistema
ROLLBACK: annulla esplicitamente le operazioni
19 December, 2015 - slide 145
Tecnologia DBMS >> Concetti Avanzati >> Transazioni
Transazioni: La Base di Dati dei Video
CREATE TABLE Videoc (
cod integer PRIMARY KEY,
CREATE TABLE Tessere (
titolo varchar(50) NOT NULL,
cod char(4) PRIMARY KEY,
regista varchar(20),
nomeCliente varchar(50),
quantita integer DEFAULT 1,
indirizzo varchar(50),
prezzo numeric(4,2)
totalenoleggi integer DEFAULT 0
);
);
CREATE TABLE Noleggi (
video integer NOT NULL
REFERENCES Videoc(cod),
tessera char(4) NOT NULL
REFERENCES Tessere(cod),
Esempio: noleggio di una videocassetta
BEGIN TRANSACTION;
INSERT INTO Noleggi VALUES
(110, ‘pp02’, ‘2002-04-15’);
UPDATE Videoc SET quant.=quant.-1
data date NOT NULL,
PRIMARY KEY
WHERE cod=110;
UPDATE Tessere SET totn.=totn.+1
(video, tessera, data)
);
Linguaggio SQL
WHERE cod=‘pp02’;
COMMIT;
19 December, 2015 - slide 146
Tecnologia DBMS >> Concetti Avanzati >> Transazioni
Gestione delle Transazioni in un DBMS
Due moduli fondamentali
Gestore della concorrenza


garantisce isolamento e consistenza
implementa tecniche più sofisticate di sincr.
Gestore dell’affidabilità


Linguaggio SQL
garantisce atomicità e durevolezza
utilizza un file di registrazioni (“log”) per consentire il recupero in
caso di guasti
19 December, 2015 - slide 147
Tecnologia DBMS >> Concetti Avanzati >> Gestione della Concorrenza
Gestione della Concorrenza
Assume atomicità e durevolezza

fornite dal gestore dell’affidabilità
>>
Due obiettivi fondamentali


Linguaggio SQL
garantire la consistenza della base di dati
(partendo da uno stato consistente, la transazione genera uno stato
consistente)
garantire l’isolamento delle transazioni
(le transazioni devono essere eseguite come se fossero isolate)
19 December, 2015 - slide 148
Tecnologia DBMS >> Concetti Avanzati >> Gestione della Concorrenza
Consistenza
Impone i vincoli di integrità

definiti nel DDL (più eventuali “trigger”)
Vincoli di riferimento

la verifica può essere immediata o differita
Istruzione SET CONSTRAINTS

SET CONSTRAINTS ALL DEFERRED;
Vincoli differibili (“deferrable”)

Linguaggio SQL
i vincoli vengono verificati solo al COMMIT
19 December, 2015 - slide 149
Tecnologia DBMS >> Concetti Avanzati >> Gestione della Concorrenza
Consistenza
Esempio: in Noleggi

video integer NOT NULL
REFERENCES Videoc(cod) DEFERRABLE;
BEGIN TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO Noleggi VALUES
(200, ‘pp02’, ‘2002-04-15’);
INSERT INTO Videoc VALUES
(200, ‘Clerks’, ...);
UPDATE Videoc SET
quantita=quantita-1
WHERE cod=200;
Attenzione: parte
dell’integrità è a
carico del
programmatore
(in questo caso non
viene aggiornato
il totalenoleggi della
tessera)
COMMIT;
Linguaggio SQL
19 December, 2015 - slide 150
Tecnologia DBMS >> Concetti Avanzati >> Gestione dell’Affidabilità
Gestione dell’Affidabilità
Due obiettivi fondamentali


garantire l’atomicità delle transazioni
garantire la durevolezza degli effetti, anche in caso di guasti
(recupero della base di dati)
Idee di base



Linguaggio SQL
registrare tutte le azioni eseguite in un file di registro (“log”)
mantenere copie dei dati e del log (“mirror”)
strettamente legato alla gestione del buffer
19 December, 2015 - slide 152
Tecnologia DBMS >> Concetti Avanzati >> Gestione dell’Affidabilità
Gestione dell’Affidabilità
File di registro (“log”)




si registrano tutte le istruzioni di aggiornamento
tutte le istruzioni di start transaction
tutte le istruzioni commit
tutte le istruzioni rollback
Formato dei record del log


Linguaggio SQL
ciascun record del log registra la modifica di un record
della base di dati da parte di una transazione
<id trans, id record, vecchio val, nuovo val.>
19 December, 2015 - slide 153
Tecnologia DBMS >> Concetti Avanzati >> Gestione dell’Affidabilità
Gestione dell’Affidabilità
Protocollo di scrittura anticipata

“Write Ahead Logging” (WAL)
Idea



Linguaggio SQL
le informazioni vengono scritte secondo un ordine che
garantisce la ripristinabilità in caso di guasti
i record del log sono scritti prima dei record della base di
dati (garantisce l’atomicità)
i record del log di una transazione sono scritti tutti prima
di effettuare il commit (garantisce la durevolezza)
19 December, 2015 - slide 154
Tecnologia DBMS >> Concetti Avanzati >> Gestione dell’Affidabilità
Gestione dell’Affidabilità
Attenzione


in ogni istante parte delle pagine del disco sono nel buffer
in memoria centrale
se sono state modificate, in caso di guasto si perdono le
modifiche
Punto di controllo (“checkpoint”)



Linguaggio SQL
“fotografia” stabile della situazione della base di dati in un
certo istante
informazioni sulle transazioni attive in quel momento
scrittura su disco delle pagine relative del buffer
19 December, 2015 - slide 155
Tecnologia DBMS >> Concetti Avanzati >> Architettura di un DBMS
Architettura di un DBMS
Gestione dei Metodi di acc.
Gestione del buffer
Affidabilità
Ottimizzazione
Algebra (Operatori)
Concorrenza
Autorizzazioni
Sicurezza e
(TCP/IP)
Connessioni
Esecuzione interrogazioni
Gestione del disco
DB
Linguaggio SQL
19 December, 2015 - slide 160
Il Transaction Log
Il transaction log registra il
dettaglio di tutte le
transazioni



Linguaggio SQL
Qualsiasi cambiamento fatto
sul DB
Come fare gli undo dei
cambiamenti
Quando e come la transazione
è completata
Il log è memorizzato su
disco, non in memoria

Se il sistema va in crash, è
preservato
Write ahead log rule

La scrittura sul log viene fatta
PRIMA del COMMIT
19 December, 2015 - slide 161
System Failures
Un system failure significa
che tutte le transazioni
sono coinvolte



Software crashes
Power failures
I dischi fisici NON sono
coinvolti e danneggiati
Linguaggio SQL
In diversi momenti il DBMS
fa dei checkpoint

Tutte le transazioni
committed sono scritte sul
disco
Vengono registrate su disco
tutte le transazioni in
esecuzione
19 December, 2015 - slide 162
Tipi di Transazioni
T1
T2
T3
T4
T5
Last Checkpoint
Linguaggio SQL
System Failure
19 December, 2015 - slide 163
System Recovery
Tutte le transazioni che
erano in esecuzione al
momento del failure
devono essere cancellate e
rifatte
Tutte le transazioni
committed dopo l’ultimo
checkpoint devono essere
ripetute
Linguaggio SQL
Transazione del tipo T1 non
necessita recovery
Transazione del tipo T3 o T5
necessita di undo e restart
Transazione del tipo T2 o T4
necessita di redo
19 December, 2015 - slide 164
Transaction Recovery
UNDO e REDO: lista delle transazioni
UNDO = tutte le transazioni running all’ ultimo checkpoint
REDO = empty
Per ciascuna entry nel file di log, a fare inizio dall’ultimo checkpoint
If a BEGIN TRANSACTION entry is found for T
Add T to UNDO
If a COMMIT entry is found for T
Move T from UNDO to REDO
Linguaggio SQL
19 December, 2015 - slide 165
Transaction Recovery
T1
T2
T3
T4
T5
Checkpoint
UNDO: T2, T3
Failure
Last Checkpoint
REDO:
Active transactions: T2, T3
Linguaggio SQL
19 December, 2015 - slide 166
Transaction Recovery
T1
T2
T3
T4
T5
Checkpoint
UNDO: T2, T3, T4
Failure
T4 Begins
REDO:
Add T4 to UNDO
Linguaggio SQL
19 December, 2015 - slide 167
Transaction Recovery
T1
T2
T3
T4
T5
Checkpoint
UNDO: T2, T3, T4, T5
Failure
T5 begins
REDO:
Add T5 to UNDO
Linguaggio SQL
19 December, 2015 - slide 168
Transaction Recovery
T1
T2
T3
T4
T5
Checkpoint
UNDO: T3, T4, T5
Failure
T2 Commits
REDO: T2
Move T2 to REDO
Linguaggio SQL
19 December, 2015 - slide 169
Transaction Recovery
T1
T2
T3
T4
T5
Checkpoint
Failure
UNDO: T3, T5
REDO: T2, T4
T4 Commits
Move T4 to REDO
Linguaggio SQL
19 December, 2015 - slide 170
Media Failures
System failures non sono
preoccupanti


Linguaggio SQL
Informazioni dopo l’ultimo
checkpoint sono interessate
Si recuperano dal transaction
log
Media failures (disk crashes
etc) sono più serie


Dati su disco sono
danneggiati
Il transaction log stesso può
essere danneggiato
19 December, 2015 - slide 171
Backups
Backups sono necessari !!
Per proteggersi da media
failure


Linguaggio SQL
Il transaction log e l’intero
contenuto del database è
scritto su uno storage
secondario (tape)
Time consuming, e spesso
richiede down time
Frequenza di backups



Alta per non perdere
informazioni
Non troppo frequente per non
causare rallentamenti
operativi
Ogni giorno (notte) di solito
Backup storage
19 December, 2015 - slide 172
Recovery from Media Failure
Restore del database
dall’ultimo backup
Usare il transaction log per
fare il redo dei cambiamenti
dopo ultimo back up
Se il transaction log è
danneggiato non si può
fare lo step 2


Linguaggio SQL
Memorizzare il log su un
device separato del database
Rischio di perderli entrambi è
ridotto
19 December, 2015 - slide 173
Concorrenza
Grossi databases sono usati
da tante persone



Linguaggio SQL
Molte transazioni aperte sul
database
Devono girare tutte insieme in
modo indipendente
Si deve garantire isolamento
Se non ci fosse
concorrenza, le transazioni
sarebbero sequenziali


Un coda di transazioni
Lunghe transazioni
rallenterebbero gli altri
19 December, 2015 - slide 174
Problemi di concorrenza
Per gestire transazioni
concorrenti si intercalano le
operazioni
Ciascuna transazione va in
time sharing
Questo comporta diversi
problemi



Lost updates
Uncommitted updates
Incorrect analysis
Tutte dovute alla rottura
dell’isolamento
Transactions and Recovery
Linguaggio SQL
19 December, 2015 - slide 175
Lost Update
T1
T2
Read(X)
T1 eT2 leggono X, lo
modificano entrambe,
entrambe lo scrivono

X = X - 5
Read(X)
X = X + 5
Write(X)

L’effetto finale di T1 e T2
dovrebbe essere nessun
cambiamento di X
Solo i cambiamenti di T2
sono visibili, quindi il valore
finale di X è aumentato di 5
Write(X)
COMMIT
COMMIT
Linguaggio SQL
19 December, 2015 - slide 176
Uncommitted Update
T1
T2
Read(X)
T2 vede i cambiamenti di X
fatti da T1, ma T1 è rolled
back

X = X - 5
Write(X)
Read(X)

Il cambio fatto da T1 è un
undone dal rollback
Ma nel finale non è così: il
cambio di T2 viene anche
eliminato
X = X + 5
Write(X)
ROLLBACK
COMMIT
Linguaggio SQL
19 December, 2015 - slide 177
Inconsistent analysis
T1
T2
Read(X)
X = X - 5
T1 non cambia la somma di
X ed Y, ma T2 vede il
cambiamento

Write(X)
Read(X)

T1 consiste di due parti –
prendi 5 da X e poi aggiungi
5 ad Y
T2 vede l’effetto del primo,
ma non del secondo
Read(Y)
Sum = X+Y
Read(Y)
Y = Y + 5
Write(Y)
Linguaggio SQL
19 December, 2015 - slide 178
SQL è STANDARD
Strategico per IBM, Microsoft, Oracle
Ogni DBMS vendor ha il suo SQL based
software
Domina in ogni mercato, dai PC ai OLTP
OLAP
Emerge come standard dell’office e PC LAN