Basi di dati I Prof.ssa Rosalba Giugno Prof. Alfredo Pulvirenti SQL: Amministrazione e Viste.

Basi di dati I

Prof.ssa Rosalba GiugnoProf. Alfredo Pulvirenti

SQL: Amministrazione e Viste

Giugno-Pulvirenti AA 2005-2006

SQL per definire ed amministrare

• Ad ogni utente tipicamente viene associata una base di dati, creata dall’amministratore del sistema.

• L’utente diventa l’amministratore potendo stabilire gli accessi di eventuali altri utenti alla sua base di dati.

•La creazione consiste nel definire incrementalmente uno schema con un certo nome, interattivamente o da programma, e tutti i suoi elementi vengono registrati in un catalogo.


CREATE SCHEMA

CREATE SCHEMA Nome AUTHORIZATION Utente

Definizioni

• Dove:– Si crea il database chiamato Nome – Utente e’ l’amministratore– Le Definizioni creano gli elementi dello schema

(Tabelle,Viste,Indici,etc…)


DROP SCHEMA

DROP SCHEMA Nome [RESTRICT | CASCADE]

• cancella la base di dati Nome • Restrict: drop non viene eseguito se

il database non è vuoto.• Cascade: Vengono rimossi

automaticamente tutti i dati presenti nel database


Definizione dei dati in SQL

• Istruzione CREATE TABLE: – definisce uno schema di relazione e ne

crea un’istanza vuota– specifica attributi, domini e vincoli


Create Table

CREATE TABLE Nome “(“Attributo Tipo[Vincolo

{,Vincolo}] {,Attributo Tipo[Vincolo

{,Vincolo}]}”)” [VincoloDiTabella {,

VincoloDiTabella}]


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) )


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• Sistemi diversi estendono il set di base con

domini non standard (vettori, periodi, ecc.)


Domini o tipi

• CHAR(n) stringhe di lunghezza n• VARCHAR(n) stringhe di lunghezza

variabile con al massimo n caratteri• INTEGER interi • REAL reali• NUMERIC (p,s) p cifre di cui s decimali• FLOAT(p) binari con p cifre significative• DATE ,TIME per date ed ore.


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 NULLCHECK ( value >=18 AND value <= 30 )


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:– nella definizione di un attributo, se

forma da solo la chiave– come elemento separato


Matricola CHAR(6) PRIMARY KEY

Matricola CHAR(6),…,PRIMARY KEY (Matricola)

PRIMARY KEY, alternative


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!


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


Matricola398732959345

Vigili CognomeRossiNeriNeri

NomeLucaPieroMario

Mori Gino7543

InfrazioniCodice

34321

73321

64521

53524

Data

1/2/95

4/3/95

5/4/96

5/2/98

Vigile

3987

3295

3295

9345

Prov Numero

MI

TO

PR

PR

39548K

E39548

839548

839548


InfrazioniCodice

34321

73321

64521

53524

Data

1/2/95

4/3/95

5/4/96

5/2/98

Vigile

3987

3295

3295

9345

Prov Numero

MI

TO

PR

PR

39548K

E39548

839548

839548

Auto Prov NumeroMITOPR

39548KE39548839548

CognomeRossiRossiNeri

NomeMarioMarioLuca



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)

)


Modifiche degli schemi

ALTER DOMAINALTER TABLEDROP DOMAINDROP TABLE...


Definizione 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


SQL, operazioni sui dati

• interrogazione: – SELECT

• modifica:– INSERT, DELETE, UPDATE


Insert

INSERT INTO Tabella [“(”Attributo {,Attributo} “)”] VALUES “(” Valore {,Valore} “)”

• Esempio– INSERT INTO Esami VALUES (‘DB1’,

123456, 27)


Delete

DELETE FROM Tabella WHERE Condizione

• Esempio:

– DELETE FROM Esami WHERE Matricola = 123456


Update

UPDATE Tabella SET Attributo = Espr {,Attributo = Espr} WHERE Condizione

• Esempio:– UPDATE Aule SET Aula = 126 WHERE

Aula = 3


Esempi

CREATE TABLE Studenti(Nome CHAR(30), Matricola INTEGER, Indirizzo CHAR(30),Telefono INTEGER)

CREATE TABLE FuoriCorso LIKE Studenti


Altri modi di definire tabelle

CREATE TABLE Nome AS EsprSelect

Esempio:CREATE TABLE EsamiBuoni LIKE Esami AS SELECT * FROM Esami WHERE Voto > 27

Basi di dati I


SQL: vincoli d’Integrità


Vincoli d’integrita’

• Riguardano i valori ammissibili degli attributi di una tupla– Vincoli Intrarelazionali: nell’ambito della stessa

relazione– Vincoli Referenziali (o Interrelazionali): tra diverse

relazioni

• Vengono controllati durante le tre possibili operazioni di modifica SQL– INSERT,DELETE e UPDATE – Devono essere sempre soddisfatti altrimenti la

transazione fallisce– Oppure, l’utente puo’ opzionalmente definire della azioni

(correttive) da intraprendere per ripristinare l’integrita’


A cosa servono i vincoli d’integrita’

• Migliorare la qualità dei dati• Arricchire semanticamente la base di

dati• La loro definizione è parte del

processo di progettazione del data base

• Usati internamente dal sistema per ottimizzare l’esecuzione


Esempio di DB semanticamente errato

Studente Voto Lode Corso32 01276545

276545 30 e lode 02787643 27 e lode 03739430 24 04

Esami

Matricola276545787643787643

CognomeRossiNeri

Bianchi

NomeMarioPieroLuca

Studenti

787643787643

32

27 e lode739430


Vincoli su una n-upla

• NOT NULL : – e’ implicito se l’attributo fa parte di una chiave

primaria

• CHECK Condizione : – specifica i valori ammissibili; esempio: Voto

INTEGER NOT NULL CHECK (18 Voto AND Voto 31)

• DEFAULT(Costante|NULL) – assegna quel valore di default per ogni

inserimento

• CHECK Condizione : – anche per attributi diversi della stessa n-upla


Vincoli Intrarelazionali

• UNIQUE: – l’attributo e’ una chiave

• PRIMARY KEY [Nome Chiave] “(”Attributo{,Attributo} “)” – dove gli attributi devono essere

dichiarati tutti NOT NULL

• UNIQUE “(”Attributo{,Attributo} “)” – definisce una chiave con piu’ attributi


Vincoli Interrelazionali

FOREIGN KEY [NomeChiaveEsterna]“(”Attributo{,Attributo} “)”REFERENCES TabellaRefON DELETE {NO ACTION,CASCADE,SET NULL}

• dove per la TabellaRef e’ stata definita una chiave primaria.

• Impedisce l’inserzione di n-uple con il valore della chiave esterna che non corrisponde ad un valore della chiave primaria della TabellaRef . Se un’operazione di cancellazione su TabellaRef viola il vincolo referenziale allora vengono applicate le tre azioni:


Azioni per Vincoli su Chiavi Esterne

• ON DELETE NO ACTION : – rifiuta l’operazione (la piu’ diffusa nei DBMS)

• ON DELETE CASCADE : – cancella tutte le n-uple con valori della chiave

esterna corrispondenti alla chiave primaria delle n-uple cancellate

• ON DELETE SET NULL – assegna il valore NULL agli attributi della

chiave esterna


Vincoli d’integrita’ referenziali (o interrelazionali)

• Tuple di relazioni diverse sono correlati per mezzo del valore di chiavi (primarie)

• Servono a garantire che i valori in una certa tabella facciano riferimento a valori reali di un’altra tabella– Esempio:

• Esami(...,Matricola), Studenti( Matricola,...)


Matricola

3987

3295

9345

Vigili Cognome

Rossi

Neri

Neri

Nome

Luca

Piero

Mario

Mori Gino7543

InfrazioniCodice

34321

73321

64521

53524

Data

1/2/95

4/3/95

5/4/96

5/2/98

Vigile

3987

3295

3295

9345

Prov Numero

MI

TO

PR

PR

39548K

E39548

839548

839548

3295

3295

3987

3987

9345

3987

9345

9345

3295

3295

3295


Auto Prov Numero

MI

TO

PR

39548K

E39548

839548

Cognome

Rossi

Rossi

Neri

Nome

Mario

Mario

Luca

InfrazioniCodice

34321

73321

64521

53524

Data

1/2/95

4/3/95

5/4/96

5/2/98

Vigile

3987

3295

3295

9345

Prov Numero

MI

TO

PR

PR

39548K

E39548

839548

839548

MI

TO

PR

PR

39548K

E39548

839548

839548

MI

TO

PR

39548K

E39548

839548


• Un vincolo di integrità referenziale (“foreign key”) fra gli attributi X di una relazione R1 e un’altra relazione R2 impone ai valori su X in R1 di comparire come valori della chiave primaria di R2

• Nell‘esempio precedente:– vincoli di integrità referenziale fra:

• l’attributo Vigile della relazione INFRAZIONI e la relazione VIGILI

• gli attributi Prov e Numero di INFRAZIONI e la relazione AUTO

• NULL per evitare il controllo del vincolo

Vincolo di integrità referenziale


InfrazioniCodice34321

733216452153524

Data1/2/954/3/955/4/965/2/98

Vigile3987329532959345

Prov NumeroMITOPRPR

39548KE39548839548839548

Auto Prov NumeroMITOPR

E39548F34268839548

CognomeRossiRossiNeri

NomeMarioMarioLuca

TO E39548

TOE39548

Violazione di vincolo di integrità referenziale


Integrità referenziale e valori nulli

Impiegati Matricola34321

6452153524

CognomeRossiNeriVerdi

ProgettoIDEAXYZNULL

Progetti CodiceIDEA

BOHXYZ

Inizio01/200007/200109/2001

Durata362424

Costo200120150

73032 Bianchi IDEA


Azioni compensative

• Esempio: Viene eliminata una tupla causando cosi' una violazione

• Tre possibili azioni– Rifiuto dell'operazione

• ON DELETE NO ACTION (la piu’ diffusa nei DBMS)– Eliminazione in cascata

• ON DELETE CASCADE: cancella tutte le tuple con valori della chiave esterna corrispondenti alla chiave primaria delle tuple cancellate

– Introduzione di valori nulli• ON DELETE SET NULL assegna il valore NULL agli attributi

della chiave esterna


Rifiuto della cancellazione


6452153524


ProgettoIDEAXYZNULL

73032 Bianchi IDEA

Progetti CodiceIDEA

BOHXYZ

Inizio01/200007/200109/2001

Durata362424

Costo200120150

XYZ 07/2001 24 120XYZ 07/2001 24 120XYZ 07/2001 24 120

53524 Neri XYZ53524 Neri XYZ

• La transazione fallisce e XYZ non puo’ essere cancellato dalla relazione Progetti


Eliminazione in cascata


6452153524


ProgettoIDEAXYZNULL

73032 Bianchi IDEA

Progetti CodiceIDEA

BOHXYZ

Inizio01/200007/200109/2001

Durata362424

Costo200120150

XYZ 07/2001 24 120XYZ 07/2001 24 120XYZ 07/2001 24 120

53524 Neri XYZ

• La transazione termina e XYZ viene cancellato anche dalla relazione Impiegati


Introduzione di valori nulli


6452153524


ProgettoIDEAXYZNULL

73032 Bianchi IDEA

Progetti CodiceIDEA

BOHXYZ

Inizio01/200007/200109/2001

Durata362424

Costo200120150

XYZ 07/2001 24 120XYZ 07/2001 24 120XYZ 07/2001 24 120

NULL

• La transazione termina e all’attributo Impiegati.Progetto viene assegnato NULL


Auto Prov Numero

MI

TO

PR

39548K

E39548

839548

Cognome

Rossi

Rossi

Neri

Nome

Mario

Mario

Luca

IncidentiCodice

34321

64521

Data

1/2/95

5/4/96

ProvB NumeroB

MI

TO

39548K

E39548

ProvA NumeroA

TO

PR

E39548

839548

Vincoli multipli su più attributi


Vincoli Interrelazionali, Sintassi

• FOREIGN KEY [NomeChiaveEsterna]“(”Attributo{,Attributo} “)”REFERENCES TabellaRefON DELETE {NO ACTION,CASCADE,SET NULL}– dove per la TabellaRef e’ stata definita una chiave

primaria

• Quindi: impedisce l’inserimento di tuple con il valore della chiave esterna che non corrisponde ad un valore della chiave primaria della TabellaRef


Esempio Riassuntivo

• CREATE TABLE Clienti (CodiceCliente CHAR(3) UNIQUE NOT NULL,Nome CHAR(30) NOT NULL,Citta’ CHAR(30) NOT NULL,Sconto INTEGER NOT NULL

CHECK(Sconto>0 AND Sconto<100), PRIMARY KEY pk_Clienti(CodiceCliente))

• CREATE TABLE Agenti (CodiceAgente CHAR(3) UNIQUE NOT NULL,Nome CHAR(30) NOT NULL,Zona CHAR(8) NOT NULL,Supervisore CHAR(3),Commissione INTEGER) PRIMARY KEY pk_Agenti(CodiceAgente),CHECK (Supervisore CodiceAgente OR Supervisore IS

NULL)


Esempio Riassuntivo

• CREATE TABLE Ordini(NumOrdine CHAR(3) NOT NULL,CodiceCliente CHAR(3) NOT NULL,CodiceAgente CHAR(3) NOT NULL,Data CHAR(8) NOT NULL,Prodotto CHAR(3) NOT NULL,Ammontare INTEGER NOT NULL CHECK

(Ammontare > 100)PRIMARY KEY pk-Ordini (NumOrdine)FOREIGN KEY fk_ClienteOrdine (CodiceCliente)

REFERENCES ClientiON DELETE NO ACTION

FOREIGN KEY fk_AgenteOrdine (CodiceAgente) REFERENCES Agenti ON DELETE NO ACTION

Basi di dati I


SQL:

Viste


Viste (View)

• Oltre alle tabelle di base che fanno parte dello schema si possono creare delle tabelle ausiliarie virtuali

• Sono “virtuali” in quanto sembrano tabelle a tutti gli effetti ma sono delle relazioni “create al volo”

• Utilizzate per vari scopi:– Semplificazione– Protezione dati– Scomposizione query complesse– Riorganizzazione dati secondo nuovi schemi– Etc.


Definizione VIEW

• Sintassi creazione VIEW:

CREATE VIEW NomeVista [“(” Attributo {,Attributo}

“)”]AS Query-Select


Esempio definizione VIEW

• CREATE VIEW MediaVoti (Matricola,Media)AS

SELECT Matricola, AVG(Voto) FROM Esami GROUP BY Matricola

• Esecuzione:SELECT *FROM MediaVoti


Le VIEW possono essere usate come tabelle

• SELECT Nome, MediaFROM Studenti, MediaVotiWHERE Studenti.Matricola = MediaVoti.Matricola

• Le VIEW possono essere distrutte alla pari di tabelle– DROP (TABLE | VIEW) Nome [RESTRICT|CASCADE]– Con RESTRICT non viene cancellata se e’ utilizzata in

altre viste– Con CASCADE verranno rimosse tutte le viste che usano

la View o la Tabella rimossa– Non tutti i sistemi permettono l’uso di RESTRICT e

CASCADE– La distruzione di una VIEW non altera le tabelle su cui la

VIEW si basa


Le VIEW possono essere usate come tabelle

• Una VIEW puo’ essere definita sulla base di un’altra VIEW

• Nelle prime versioni di SQL non era possibile modificare una VIEW tramite Insert, Delete, Update– Non piu’ vero nei nuovi DBMS (Vedremo dopo)

• Che succede se una tabella usata in una VIEW viene alterata o cancellata (senza specificare RESTRICT o CASCADE)?– Dipende dal DBMS:

• la VIEW viene marcata ‘inoperative’, oppure• La modifica/cancellazione viene negata• Etc.


Uso delle VIEW per query complesse

• Semplificare query complesse• Esempio: non possiamo scrivere

SELECT AVG(COUNT(*))FROM AGENTIGROUP BY ZONE

– AVG deve agire sui valori di un attributo.


Uso delle VIEW per query complesse

• CREATE VIEW AgPerZona (Zona,NumAg)ASSELECT Zona,COUNT(*)

FROM AGENTIGROUP BY Zona

• SELECT AVG(NumAg)FROM AgPerZona

• DROP AgPerZona


Uso delle VIEW per Sicurezza

• CREATE VIEW EsamiPublici AS SELECT Corso,Voto FROM Esami

• Data la tabella ClientiBanca(Nome,Indirizzo,Saldo)

• CREATE VIEW ClientiInd AS SELECT Nome,Indirizzo FROM ClientiBanca


Mascherare l’organizzazione logica dei dati tramite VIEW

• Immaginiamo la seguente tabella:– Agenti( CodiceAgente, Nome, Zona, Commissione, Supervisore)

• Per riorganizzazione aziendale si decide di assegnare un Supervisore ad una zona intera invece del singolo agente1) CREATE TABLE Zone (Zona CHAR(8), Supervisore CHAR(3)) AS SELECT DISTINCT Zona,Supervisore FROM Agenti2) CREATE TABLE NuoviAgenti AS SELECT CodiceAgente,Nome,Zona,Commissione FROM Agenti3) DROP Agenti4) CREATE VIEW Agenti AS SELECT * FROM NuoviAgenti NATURAL JOIN Zone


Aggiornamento delle VIEW

• Le operazioni INSERT/UPDATE/DELETE sulle VIEW non erano permesse nelle prime edizioni di SQL

• I nuovi DBMS permettono di farlo con certe limitazioni dovute alla definizione della VIEW stessa

• Che senso ha aggiornare una VIEW? Dopotutto si potrebbe aggiornare la tabella di base direttamente…


Aggiornamento delle VIEW, cont.

• … utile nel caso di accesso dati controllato• Esempio:

– Impiegato( Nome, Cognome, Dipart, Ufficio, Stipendio)• Il personale della segreteria non puo’ accedere ai dati sullo

stipendio ma puo’ modificare gli altri campi della tabella, aggiungere e/o cancellare tuple

• Si puo’ controllare l’accesso tramite la definizione della VIEW:– CREATE VIEW Impiegato2 AS

SELECT Nome, Cognome, Dipart, UfficioFROM Impiegato

• INSERT INTO Impiegato2 VALUES (…)– Stipendio verra’ inizializzato a Null– Se Null non e’ permesso per Stipendio l’operazione fallisce


Aggiornamento VIEW 2

• Immaginiamo la seguente VIEW:CREATE VIEW ImpiegatoRossiASSELECT * FROM Impiegato WHERE Cognome=‘Rossi’

• La seguente operazione ha senso:– INSERT INTO ImpiegatoRossi (…’Rossi’,

…)


Aggiornamento VIEW 2, cont.

• Ma che succede nel caso di:– INSERT INTO ImpiegatoRossi (…’Bianchi’,…)– In genere e’ permesso, finisce nella tabella base ma non

e’ visibile dalla VIEW– Si puo’ controllare tramite l’opzione “WITH CHECK

OPTION”:CREATE VIEW ImpiegatoRossiASSELECT * FROM Impiegato WHERE Cognome=‘Rossi’WITH CHECK OPTION

• Adesso l’insert con ‘Bianchi’ fallisce, quella con ‘Rossi’ viene invece eseguita.


Aggiornamento VIEW 3

• Consideriamo il seguente caso:– Impiegato( Nome, Cognome, Dipart, Ufficio, Stipendio)– Dipartimenti( Dipart, Indirizzo)– CREATE VIEW IMP_IND AS

SELECT Nome, Cognome, d.dipart, indirizzoFROM Impiegato i join Dipartimenti d ON i.Dipart=d.Dipart

• Un INSERT sulla VIEW IMP_IND dovrebbe inserire su entrambe le tabelle base

• In alcuni casi potrebbe inserire in una ma non nell’altra• In genere quest’operazione non e’ consentita• Alcuni DBMS consentirebbero l’INSERT se

“Impiegati.Dipart” fosse una foreign key su “Dipartimenti.Dipart” e quest’ultima fosse chiave primaria


Aggiornamento VIEW, riepilogo

• In genere una VIEW definita su una singola tabella e’ modificabile se gli attributi della VIEW contengono la chiave primaria (e altre chiavi)

• In genere VIEW definite su piu’ tabelle non sono aggiornabili– Alcuni DBMS, come discusso prima, lo permettono nel

caso certe condizioni, molto restrittive, siano rispettate• VIEW che usano funzioni di aggregazione non sono

aggiornabili• PRINCIPIO di base per l’aggiornamento delle VIEW:

– Ogni riga ed ogni colonna della VIEW deve corrispondere ad una ed una sola riga ed una ed una sola colonna della tabella base

Basi di dati I


SQL: Procedure e Trigger


Aspetti procedurali dei DBMS

• Procedure: Programmi memorizzati nel DBMS che vengono eseguiti su esplicita richiesta degli utenti.

• Trigger: Programmi memorizzati nel DBMS che vengono attivati automaticamente dopo le operazioni di modifica sulle tabelle


Procedure

• Possono essere costituite da un unico comando SQL parametrizzato

• I moderni DBMS offrono un linguaggio procedurale piu’ ricco– Oracle: PL/SQL– Sybase: Transact/SQL– IBM-DB2– MySQL (Ver.5)??

• Possono anche essere scritte in linguaggi standard:– C/C++, Java– Compilate come oggetti esterni integrati dal DBMS

• In alcuni casi possono eseguire azioni esterne:– Cancellare un file– Spedire un’email


Linguaggio procedurale

• Complementano la natura dichiarativa di SQL

• Costrutti tipo: FOR, WHILE, LOOP, IF, etc.

• Scansione iterativa di tabelle


Esempio linguaggio procedurale DB2

BEGIN ATOMICDECLARE cur INT;SET cur = MICROSECOND(CURRENT TIMESTAMP);IF cur > 600000 THEN

UPDATE staffSET name = CHAR(cur)WHERE id = 10;

ELSEIF cur > 300000 THENUPDATE staffSET name = CHAR(cur)WHERE id = 20;

ELSEUPDATE staffSET name = CHAR(cur)WHERE id = 30;

END IF;END

• Comando IF/THEN



BEGIN ATOMICFOR V1 AS

SELECT dept AS dname, max(id) AS max_idFROM staffGROUP BY deptHAVING COUNT(*) > 1ORDER BY dept

DOUPDATE staffSET id = id * -1WHERE id = v1.max_id;UPDATE staff SET dept = dept / 10WHERE dept = v1.dname AND dept < 30;

END FOR;END

• Comando FOR per scansione tabella



BEGIN ATOMICDECLARE c1, C2 INT DEFAULT 1;WHILE c1 < 10 DO

WHILE c2 < 20 DOSET c2 = c2 + 1;

END WHILE;SET c1 = c1 + 1;

END WHILE;UPDATE staff

SET salary = c1 ,comm = c2WHERE id = 10;

END

• Comando WHILE per scansione tabella


Vantaggi delle procedure

• Consentono di condividere fra gli utenti delle attivita’ comuni, in modo da centralizzare la manutenzione, la modifica etc..

• Unificano la semantica di certe operazioni sul DB per ogni applicazione

• Possono controllare in modo centralizzato certi vincoli d’integrita’ non esprimibili nelle tabelle.


Vantaggi delle procedure

• Riducono il traffico sulla rete dovuto ad applicazioni remote, infatti invece di agire interattivamente con il DBMS l’utente spedisce una volta per tutte una chiamata alla procedura ricevendone la risposta.

• Garantiscono la sicurezza dei dati consentendo a certi utenti di accedere ai dati attraverso certe procedure e non direttamente


Basi di dati “Attive”

• Trigger: Regole basate sul paradigma Event-Condition-Action (ECA) incorporate nella base di dati– Struttura tipica di una regola/trigger:

when Eventif Conditionthen Action

• I DBMS attivi hanno un comportamento “reattivo” in contrasto col passivo della basi di dati tradizionali– Eseguono sia transazioni utente che trigger

• I trigger sono simili alle procedure ma vengono invocati automaticamente in seguito alle operazioni di modifica della base di dati (INSERT/DELETE/UPDATE)

• Fanno parte della definizione della base di dati• Arricchiscono semanticamente lo schema relazionale


Basi di dati “Attive”, cont.

• La loro sintassi e’ stata standardizzata in SQL-1999

• Sistemi relazionali commerciali (e non) includono i trigger fin dagli anni 80

• Cio’ ha causato difformita’ di sintassi difficilmente riconciliabile non esiste ancora un’implementazione “Standard” da

un punto di vista sintattico• Estensione del “CHECK” in quanto permettono di

operare (modificare) su altre tabelle• Possono anche scatenare azioni esterne al DB

– Spedire email, cancellare file, etc.


Utilizzo trigger

• Business rules, parte della procedura “di business” applicativa (normalmente eseguite in modo asincrono dall’applicazione)– Automazione magazzino con riordino automatico– Spedizione solleciti– Controllo attivita’ conti bancari/carte di credito– Acquisto/Vendita automatica strumenti finanziari

• Auditing e Logging, memorizzazione eventi per controlli– Esempi:

• Storico delle modifiche effettuate su una tabella per recupero dati• Elenco dei login effettuati (da chi e quando)• Controllo delle attivita’ (chi ha fatto cosa e quando)• Etc.

• Version Management– Conservare varie versioni dello stato della base di dati nel

tempo


Utilizzo Trigger

• Duplicazione database– trasparente tramite l’uso dei trigger– Implementazione database distribuiti

• Vincoli d’integrita’ complessi non esprimibili con il comando “CHECK”

• Workflow management– Esempio: Assegnare sostituto per chiamate

d’urgenza


Trigger Passivi

• Sono quelli che segnalano un fallimento.• Sono di due tipi:• Quelli che definiscono vincoli non

esprimibili direttamente (ad esempio quelli dinamici)

• Quelli che controllano le operazioni degli utenti basandosi sui parametri della query.Ad esempio si possono inserire dati solo se (il codice del)la zona e’ quella dell’utente che esegue l’inserimento.


Paradigma Event-Condition-Action

• Semantica operativa:– Quando succede l’evento…– … se la condizione e’ soddisfatta…– … esegui l’azione specificata

• Event: Aggiornamento dati tramite INSERT, UPDATE o DELETE

• Condition: Predicato SQL– Opzionale

• Action: Sequenza di comandi SQL o SQL procedurale, ROLLBACK, etc.

• Ogni trigger e’ associato ad una sola tabella e viene attivato dalle operazioni dirette a quella tabella


Esempi trigger

• Espressi in sintassi non-standard:

CREATE RULE ControlloStipendio ON ImpiegatiWHEN Inserted, Deleteted, Updated(Stipendio)IF (select avg(stipendio) from impiegati) > 100THEN update impiegati set stipendio=stipendio * 0.9

CREATE RULE ControlloRicchi ON ImpiegatiWHEN InsertedIF EXISTS ( select * from INSERTED

where stipendio>100)THEN Insert into ImpiegatiRicchi ( select * from INSERTED

where stipendio>100)


Esempio esecuzione trigger

Aldo 90

Maria 90

Luca 110

Aldo 90

Maria 90

Luca 110

Piero 150

Mario 120

Inseriamo: (Piero, 150) e (Mario, 120)

Il trigger ControlloStipendio parte:

Aldo 81

Maria 81

Luca 99

Piero 135

Mario 108…

La tabella impiegati inizialmente:


Esempio esecuzione trigger,cont

Aldo 73

Maria 73

Luca 89

Piero 121

Mario 97

Il trigger ControlloStipendio parte di nuovo: Ricorsione

Inoltre, alla fine la tabella ImpiegatiRicchi conterra’:

Piero 121


Problemi con i Trigger

• I sistemi commerciali adottano diverse semantiche per l’attivazione dei trigger e diversi modi di interazione fra i meccanismi di attivazione. Ecco le principali differenze:

• Granularita’: Se la modifica riguarda un insieme di n-uple (come con INSERT,UPDATE e DELETE), allora in certi sistemi il trigger viene eseguito una sola volta (trigger di comando) in altri sistemi viene eseguito tante volte quante sono le n-uple modificate (trigger di riga). In ORACLE e’ possibile scegliere con FOR EACH ROW. Invece in Sybase e’ attivato una sola volta per comando . In questo caso si usano le tabelle inserted e deleted contenenti le righe inserite e cancellate (un’update e’ considerata la coppia cancellazione seguita da una corrispondente inserzione)


Granularita’

• Due tipi:– Row-level– Statement-level

• Row-level– Il trigger viene eseguito una volta per ogni tupla

coinvolta nell’operazione di modifica• Statement-level

– Il trigger viene eseguito una sola volta per tutte le tuple coinvolte

• Richiedono una sintassi diversa– Row-level: Riferimento ad una riga sola– Statement-level: Riferimento ad una tabella


Esempio

• CREATE TRIGGER cancellaAgente AFTER DELETE ON Agenti FOR EACH ROW BEGIN DELETE Totali

WHERE CodiceAgente = :old.CodiceAgente; END;

• Diventa in Sybase• CREATE TRIGGER cancellaAgente

AFTER DELETE ON Agenti BEGIN

DELETE Totali WHERE CodiceAgente IN

(SELECT deleted.CodiceAgente FROM deleted) END;


Tuple e Tabelle di transizione

• Tuple e tabelle temporanee visibili all’interno del trigger eseguito che contengono la porzione di dati manipolati dal comando SQL che ha attivato il trigger– Servono per accedere ai dati manipolati dal comando che ha scatenato

il trigger– Vengono usate in modo standard dall’SQL del trigger

• Possono essere usate sia nella condizione che nell’azione• Nella granularita’ statement-level il trigger usa delle tabelle di

transizione:– DELETE: Una tabella di transizione con tutte le tuple cancellate– INSERT: Una tabella di transizione con tutte le tuple inserite– UPDATE: Una tabella di transizione con tutte le tuple con i vecchi valori

ed una con tutte le tuple con i nuovi valori• Nella granularita’ row-level il trigger usa delle tuple di

transizione:– DELETE: Una per la tupla cancellata– INSERT: Una per la tupla inserita– UPDATE: Una per la tupla con i vecchi valori ed una con i nuovi valori


Modo di esecuzione del trigger

• Indica se far partire il trigger prima (BEFORE) o dopo (AFTER) l’esecuzione fisica del comando impartito

• BEFORE– viene normalmente usato per modificare i dati del

comando impartito prima dell’esecuzione (fisica) del comando stesso… (vedi esempio)

– In genere limitato nei comandi che puo’ eseguire

• AFTER– Molto piu’ comune– Pieno accesso all’SQL e SQL procedurale


Esempio Trigger BEFORE in DB2

CREATE TRIGGER T1NO CASCADE BEFORE INSERT ON IMPIEGATOREFERENCING NEW AS N //come chiamare le nuove tupleFOR EACH ROW WHEN ( N.STIPENDIO > (SELECT MAX(STIPENDIO)

FROM IMPIEGATO))SET N.STIPENDIO = (SELECT MAX(STIPENDIO)

FROM IMPIEGATO)


Esempio granularita’ per AFTER in DB2

create table progetti( codice char(10) not null primary key, Inizio date, Durata int, Costo int)

create table progetti_cancellati( codice char(10) not null, cancellato_il date with default current

date)

create trigger storico_prog_cancafter delete on progettireferencing old as ofor each rowbegin atomic insert into progetti_cancellati

values( o.codice, current date);end

create trigger storico_prog_canc2after delete on progettireferencing old_table as ofor each statementbegin atomic insert into progetti_cancellati select codice, current date from o;end


Esecuzione del trigger

• Immediata– Non appena il comando viene eseguito– Nel mezzo della transazione– Fa parte della stessa transazione

• Differita– Esecuzione rimandata alla fine della transazione– Fa parte della stessa transazione

• Disaccoppiata– Esecuzione del trigger fa parte di una transazione separata, viene

quindi eseguito anche se la transazione precedente fallisce– Non disponibile in tutti i sistemi

• Nota che questi modi di esecuzione possono essere in conflitto con la semantica del BEFORE/AFTER– Dipende dal DBMS


Esempio sintassi trigger Oracle

• Sintassi Oracle: CREATE TRIGGER NomeTrigger TipoTrigger (TipoOperazione{OR TipoOperazione}) [OF Attributo] ON NomeTabella [FOR EACH ROW] [WHEN “(”Condizione“)”] Procedura in PL/SQL

• TipoTrigger ::= (BEFORE|AFTER)• TipoOperazione ::= (DELETE|INSERT|UPDATE) • FOR EACH ROW specifica che l’azione deve essere

ripetuta su ogni n-upla (oppure una volta per tutte)


About PL/SQLAbout PL/SQL

– PL/SQL e’ un’estensione di SQL con delle caratteristiche di linguaggi programmazione .

– I comandi SQL sono inclusi in unita’ procedurali di codice.


Benefici di PL/SQLBenefici di PL/SQL

• Integrazione

• Integrazione

ApplicazioneApplicazione

Oracle ServerOracle ServerLibreria Libreria condivisacondivisa



ApplicazioneApplicazione Altri DBMSAltri DBMS

ApplicazioneApplicazioneOracle con

PL/SQLOracle con

PL/SQL

SQLSQL

SQLSQLSQLSQL

SQLSQL

SQLSQLIF...THENIF...THEN

SQLSQLELSEELSE

SQLSQLEND IF;END IF;SQLSQL

• Prestazioni Elevate



• Modularizzazione programmiDECLAREDECLARE

BEGINBEGIN

EXCEPTIONEXCEPTION

END;END;



– Portabile.– Possono essere dichiarati

identificatori.



– Si puo’ programmare con un linguaggio procedurale che possiede strutture di controllo.

– Consente la gestione degli errori.


PL/SQL Struttura BloccoPL/SQL Struttura Blocco

• DECLARE – Opzionale– Variabili, cursori, eccezioni definite

dall’utente

• BEGIN – Obbligatorio– Comandi SQL

– Comandi PL/SQL

• EXCEPTION – Opzionale– Azioni da effettuare in caso di errori

• END; – Obbligatorio

• DECLARE – Opzionale– Variabili, cursori, eccezioni definite

dall’utente

• BEGIN – Obbligatorio– Comandi SQL

– Comandi PL/SQL

• EXCEPTION – Opzionale– Azioni da effettuare in caso di errori

• END; – Obbligatorio

DECLAREDECLARE

BEGINBEGIN

EXCEPTIONEXCEPTION

END;END;


PL/SQL Struttura BloccoPL/SQL Struttura Blocco

DECLARE v_variable VARCHAR2(5);BEGIN SELECT column_name INTO v_variable FROM table_name;EXCEPTION WHEN exception_name THEN ...END;

DECLARE v_variable VARCHAR2(5);BEGIN SELECT column_name INTO v_variable FROM table_name;EXCEPTION WHEN exception_name THEN ...END;

DECLAREDECLARE

BEGINBEGIN

EXCEPTIONEXCEPTION

END;END;


Tipi di BlocchiTipi di Blocchi

Anonimi ProcedureFunzioni

[DECLARE][DECLARE]

BEGINBEGIN --statements--statements

[EXCEPTION][EXCEPTION]

END;END;

[DECLARE][DECLARE]



END;END;

PROCEDURE namePROCEDURE nameISIS



END;END;

PROCEDURE namePROCEDURE nameISIS



END;END;

FUNCTION nameFUNCTION nameRETURN datatypeRETURN datatypeISISBEGINBEGIN --statements--statements RETURN value;RETURN value;[EXCEPTION][EXCEPTION]

END;END;

FUNCTION nameFUNCTION nameRETURN datatypeRETURN datatypeISISBEGINBEGIN --statements--statements RETURN value;RETURN value;[EXCEPTION][EXCEPTION]

END;END;


Esempio Oracle PL/SQL

• Supponiamo che non si accettano ordini con uno scoperto >2.500

• CREATE TRIGGER ControlloFido BEFORE INSERT ON Ordini DECLARE DaPagare NUMBER; BEGIN SELECT SUM(Ammontare) INTO DaPagare FROM Ordini WHERE CodiceCliente = :new.CodiceCliente; IF DaPagare > 2.500 - :new.Ammontare THEN

RAISE_APPLICATION_ERROR (-2061, ‘fido superato’); END IF; END

• :new valore da inserire o modificato, :old e’ il valore precedente


Creazione ed aggiornamento automatico di una tabella

• CREATE TABLE Totali(CodiceAgente CHAR(3), TotaleOrdini INTEGER) • CREATE TRIGGER aggiornaTotali

AFTER INSERT ON Ordini FOR EACH ROW DECLARE esiste NUMBER; BEGIN SELECT COUNT(*) INTO esiste FROM Totali WHERE CodiceAgente = :new.CodiceAgente; IF esiste = 0 $agente non ancora presente$ THEN INSERT INTO Totali VALUES (:new.CodiceAgente, :new.Ammontare); ELSE UPDATE Totali SET TotaleOrdini = TotaleOrdini + :new.Ammontare

WHERE CodiceAgente = :new.CodiceAgente; END;


Cancellazione automatica

• Per cancellare la riga dei Totali relativa ad un agente che viene licenziato

• CREATE TRIGGER cancellaAgenteAFTER DELETE ON AgentiFOR EACH ROWBEGIN

DELETE FROM Totali WHERE CodiceAgente = :old.CodiceAgente; END;

• Nota che per il DELETE e’ stato usato “:old”


Vantaggi sull’uso dei Trigger

• Knowledge Independence, si semplificano le applicazioni che non devono fare i controlli dei trigger

• Permettono di centralizzare i controlli che quindi non possono essere evitati dagli utenti del DB

• Arricchimento semantico della base di dati• Il controllo diventa parte della transazione stessa,

se il controllo fallisce l’intera transazione fallisce– Puo’ anche essere eseguito in ‘differita’ su alcuni DBMS


Problemi di Applicabilita’ dei Trigger

• Complessita’: Bisogna conoscere tutti gli effetti diretti ed indiretti dell’azione del trigger (ancora peggio per quelli che attivano altri trigger in cascata)

• Rigidita’: Si potrebbe volere occasionalmente una eccezione al trigger, ma non si puo’ evitare l’attivazione del trigger

• Debugging: Difficile da eseguire, manca un sistema di controllo e debugging in genere– Stessi problemi dei linguaggi dichiarativi (CLIPS, OPS5)


Altri problemi semantici

• Risoluzione di conflitti. Piu’ trigger sono attivabili allo stesso momento. Varie politiche di gestione– L’ordine di esecuzione e’ quello di definizione (ORACLE) – L’utente specifica un ordine per ogni trigger (e.g., after

trig1)– Gestito arbitrariamente dal sistema

• Trigger in Cascata. Trigger si attivano a vicenda, ricorsivamente. Varie politiche di gestione– Non permesso– Limitazione del numero di attivazioni – Dinamico

(Sybase) ammette la ricorsione. In ORACLE la ricorsione e’ vietata.


Interazione con le transazioni

• Se il trigger interagisce con la transazione che l’attiva,in generale l’azione diventa parte della transazione cosi’ che se il trigger abortisce anche la transazione abortisce e viceversa. Se invece voglio registrare su una tabella di controllo tutto quello che succede allora questo deve essere fatto anche se la transazione abortisce.

Basi di dati I


Aspetti sistemistici dell’SQL


Schemi

• Sono collezioni di tabelle, viste, asserzioni, trigger, moduli PSM, ecc.

• Gli schemi sono l’unità di base dell’organizzazione gerarchica e sono vicini al concetto di database che ognuno di noi ha.


Schemi Esterni

•Schema Esterno e’ la definizione di uno schema per un DB per una certa classe di utenti e degli accessi ad essi consentiti


Modi di definire Schemi esterni

• Si definisce, con il comando CREATE SCHEMA, un unico schema S e, con il meccanismo dei Grant, si dichiara chi puo’ accedere alle tabelle o view di S ed in quali modi.

• Fatto questo si possono definire schemi Si che usano tabelle o view di S. Per ogni schema Si si possono autorizzare alcuni utenti ad usare gli elementi di Si sempre con il meccanismo dei Grant .


Grant

• Grant Autorizzazion //select, insert, //update, update(attributi), delete, all //previleges– On Tabella– TO (Public, utente,{, utente})– With grant option //gli utenti possono

//concedere le stesse autorizzaizoni ad //altri

• Revoke [grant option for] autorizzazioni– ON Tabella– FROM utente {, utente}


Cataloghi

• Sono collezioni di schemi. Ogni catalogo ha uno o più schemi. Ogni catalogo ha uno schema speciale chiamato INFORMATION_SCHEMA che contiene informazioni riguardo a tutti gli schemi memorizzati in esso.


Cataloghi in SQL

• L’INFORMATION_SCHEMA (cataloghi di sistema) contiente delle tabelle speciali che contengono i meta-dati del DB (tabelle, view, vincoli, trigger, utenti, autorizzazioni, indici etc..)

• Esempi:– PASSWORD(NomeUtente,ParolaChiave)

– SYSDB(NomeBaseDati, Proprietario, Cammino, Commenti)

– SYSTABLE(NomeTabella, Proprietario, BaseODerivata, NumeroColonne, NomeArchivioFisico,

Commenti)

– SYSCOLS(NomeColonna, Tabella, Numero, Tipo, Lunghezza, Default, Commenti)

– SYSINDEX(NomeIndice, Tabella, Proprietario, NumeroColonna, Commenti)


Altri Cataloghi

• Una decina di altre tabelle per view, vincoli, grant ecc..

• Altri riguardano aspetti quantitativi sui dati, le statistiche, utilizzate dall’ottimizzatore delle query.


Utilizzo dei cataloghi

• Normalmente consultabili ma non modificabili dagli utenti. Possono tuttavia essere consultate mediante SQL e per questa ragione sono autoreferenziate (ad esempio SYSTABLE conterrà una n-upla corrispondente a se stessa.


Cluster

• Sono collezioni di cataloghi. Ogni utente ha un cluster associato, relativo all’insieme di cataloghi che egli può accedere.


Gerarchia

SQL environmentCluster

Massimo scope per unaOperazione sul DB

Catalogo Catalogo

Schema

Schema

Catalogo


Locking

• Consiste nel bloccare un dato in lettura o scrittura rispettivamente prima di modificare o leggere quel dato.

• Quando una transazione T1 cerca di ottenere un blocco in scrittura su di un dato già bloccato da T2 allora T1 viene messa in attesa finché T2 termina rilasciando il lock.

• Si garantisce così la serializzabilità e l’isolamento in modo che ogni transazione non veda mai le modifiche di un’altra transazione non ancora terminata. Le richieste di blocco sono fatte dal sistema automaticamente.


Transazioni nei linguaggi che ospitano SQL

• I DBMS relazionali permettono di spezzare i programmi in più transazioni mediante i comandi COMMIT e ROLLBACK.

• Nel caso dell’SQL ospitato una transazione viene considerata iniziata dal sistema quando un programma esegue un’operazione su una tabella (SELECT, UPDATE, INSERT, DELETE, OPEN CURSOR)


Interruzione di transazioni

• La transazione prosegue finché:– viene eseguito EXEC SQL COMMIT WORK : la

transazione termina normalmente con il rilascio dei blocchi sui dati usati che diventano disponibili

– viene eseguito EXEC SQL ROLLBACK WORK (abort transaction) : comporta la terminazione prematura della transazione e quindi

• (a) il disfacimento di tutte le modifiche (atomicità) • (b) il rilascio dei blocchi usati.

– Il programma termina senza errori , normalmente.– Il programma termina con fallimento e provoca

l’aborto della transazione


Transazioni con livelli diversi di isolamento

• Con l’aumentare del numero di transazioni eseguite concorrentemente in modo serializzabile si può ridurre l’effettivo grado di concorrenza del sistema a causa del fatto che aumenta la probabilità di avere transazioni in attesa di dati bloccati da altre o interrotte per il verificarsi di situazioni di stallo

• Per questa ragione i sistemi commerciali prevedono la possibilità di programmare transazioni rinunciando alla proprietà di serializzabilità e quindi di isolamento delle transazioni.


SQL-92: SET TRANSACTION

SET TRANSACTION ISOLATION LEVEL

[READ UNCOMMITTED|

READ COMMITTED |

REPEATABLE READ |

SERIALIZABLE ]


READ UNCOMMITTED

• Il primo livello di isolamento read uncommitted (detto anche dirty read o degree of isolation 0) consente transazioni che fanno solo operazioni di lettura che vengono eseguite dal sistema senza bloccare in lettura i dati.

• Come conseguenza abbiamo che una transazione può leggere dati modificati da un’altra transazione non ancora completata.


READ COMMITTED

• Il livello di isolamento read committed (detto anche cursor stability o degree of isolation 1) stabilisce che i blocchi in lettura vengano rilasciati subito mentre quelli in scrittura vengono rilasciati alla terminazione della transazione.

• In questo modo una transazione T che modifica un dato, quel dato non può essere letto da altri fino a che T non abbia effettuato un commit o un rollback.

• Conseguenza: una transazione puo’ fare letture non ripetibili, letture degli stessi dati possono essere diverse perche’ nell’intervallo sono state modificati da altre transazioni.


REPEATABLE READ

• Repeatable read o anche degree of isolation 2 prevede che i blocchi in lettura e scrittura siano assegnati solo su n-uple di tabelle e vengano rilasciati alla terminazione della transazione.

• Presenta il fenomeno dei fantasmi (phantoms), abbiamo che dati inseriti nella tabella possono non essere visti.


SERIALIZABLE

• Degree of isolation 3. Consiste nel blocco temporaneo della tabella;

• In certe applicazioni troppo restrittivo.


Livello Letture sporche

Letture non ripetibili Dati fantasmi

READ UNCOMMITTED X X X

READ COMMITTED - X X

REPEATABLE READ - - X

SERIALIZABLE - - -


Altri strumenti per amministrare un DB

• Strumenti per progettare concettualmente il DB: Erwin,S-Designor,Er/1

• Strumenti per definire la memoria da assegnare alle varie strutture e sua riorganizzazione nel caso di eccessiva frammentazione: S-Designor, DB-General,TS-Reorg

• Strumenti per controllare l’esecuzione dei comandi SQL per migliorare le prestazioni nei punti critici: Plan Analyzer

• Strumenti per la pianificazione ed esecuzione delle procedure per il back-up di sicurezza del DB: Patrol,Platinum Fast Unload (per Oracle)

• Strumenti per il controllo del funzionamento del DBMS, generazioni di statistiche sull’utilizzazione della memoria e del buffer, operazioni di I/O, condizioni di stallo delle transazioni ecc..: SQL Enterprise Manager (Microsoft), Enterprise Manager (Oracle) ecc...

Basi di dati I Prof.ssa Rosalba Giugno Prof. Alfredo Pulvirenti SQL: Amministrazione e Viste.

Documents

Transcript of Basi di dati I Prof.ssa Rosalba Giugno Prof. Alfredo Pulvirenti SQL: Amministrazione e Viste.