Basi di dati modulo 2
This presentation is the property of its rightful owner.
Sponsored Links
1 / 67

Basi di dati (modulo 2) PowerPoint PPT Presentation


  • 74 Views
  • Uploaded on
  • Presentation posted in: General

Basi di dati (modulo 2). Prof. Giovanni Giuffrida Stanza: 362 tel: 095 738 3051 e-mail: [email protected] Programma. Progettazione di basi di dati Il modello entity-relationship (ER) Progettazione Logica, fisica e concettuale Oggetti SQL Vincoli, Viste, Procedure Trigger

Download Presentation

Basi di dati (modulo 2)

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Basi di dati modulo 2

Basi di dati(modulo 2)

Prof. Giovanni Giuffrida

Stanza: 362 tel: 095 738 3051

e-mail: [email protected]

Basi di dati II


Programma

Programma

  • Progettazione di basi di dati

    • Il modello entity-relationship (ER)

    • Progettazione

      • Logica, fisica e concettuale

  • Oggetti SQL

    • Vincoli, Viste, Procedure

    • Trigger

    • Esempi su DB commerciali: Oracle e DB2

    • Esercitazioni

Basi di dati II


Programma cont

Programma, cont.

  • Normalizzazione di schemi relazionali

  • Aspetti sistemistici dei DBMS

    • Cataloghi, schemi

    • Transazioni

    • Piani di esecuzione

  • SQL Avanzato

    • Sequenze

    • Viste materializzate

    • Query multidimensionali (OLAP)

    • Query ricorsive

    • Esercitazioni

Basi di dati II


Programma cont1

Programma, cont.

  • Linguaggi procedurali e interfacce

    • PHP, JDBC2, QBE

  • Amministrazione di basi di dati

    • Controllo accessi

    • Monitoring

    • Tuning

Basi di dati II


Vincoli d integrita

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’

Basi di dati II


A cosa servono i vincoli d integrita

A cosa servono i vincoli d’integrita’

  • Migliorare la qualita’ dei dati

  • Arricchire semanticamente la base di dati

  • La loro definizione e’ parte del processo di progettazione del data base

  • Usati internamente dal sistema per ottimizzare l’esecuzione

Basi di dati II


Esempio di db semanticamente errato

Esami

Studente

Voto

Lode

Corso

32

01

30

e lode

02

276545

787643

03

276545

739430

24

04

27

27

e lode

e lode

Studenti

Matricola

Cognome

Nome

276545

Rossi

Mario

Neri

Piero

787643

787643

787643

Bianchi

Luca

787643

Esempio di DB semanticamente errato

32

739430

Basi di dati II


Vincoli sui valori della tupla

Vincoli sui valori della tupla

  • NOT NULL

    • implicito se l’attributo fa parte di una chiave primaria

    • Esempio: campo matricola nella tabella Studente

  • DEFAULT (Costante|NULL)

    • assegna un valore di default per ogni inserimento se non specificato

    • Esempio: DEFAULT CURRENT DATE

  • CHECK Condizione

    • Dove “Condizione” e’ un’espressione booleana per il controllo di attributi, costanti ed espressioni

    • Dev’essere valutata True per la corretta esecuzione della transazione

    • Vincoli sul dominio:

      • Esempio: specifica i valori ammissibili nell’attributo Voto della tabella Esami:

        • Voto NOT NULL

        • (18  Voto AND Voto  30)

    • Vincoli basati su piu’ attributi

      • (Lode <> ‘Si’) OR (Voto = 30)

Basi di dati II


Definizione vincoli intrarelazionali

Definizione vincoli intrarelazionali

  • PRIMARY KEY [Nome Chiave] “(”Attributo{,Attributo} “)”

    • gli attributi devono essere dichiarati tutti NOT NULL

    • Esempio: Attributo Matricola nella relazione Studenti

  • UNIQUE “(”Attributo{,Attributo} “)”

    • definisce una chiave con uno o piu’ attributi

    • Esempio: (Nome,Cognome,DataDiNascita)

    • Nota:

      • Nome not null unique,Cognome not null unique

    • E’ diverso da:

      • Nome not null,Cognome not null,UNIQUE (Nome, Cognome)

Basi di dati II


Vincoli d integrita referenziali o interrelazionali

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

Basi di dati II


Basi di dati modulo 2

Infrazioni

Codice

Data

Vigile

Prov

Numero

34321

1/2/95

3987

3987

MI

39548K

3295

53524

4/3/95

3295

TO

E39548

3295

64521

5/4/96

3295

PR

839548

73321

5/2/98

9345

9345

PR

839548

Vigili

Matricola

Cognome

Nome

3987

Rossi

Luca

3987

3295

Neri

Piero

3295

9345

Neri

Mario

9345

7543

Mori

Gino

3987

3295

3295

9345

Basi di dati II


Basi di dati modulo 2

Infrazioni

Codice

Data

Vigile

Prov

Numero

34321

1/2/95

3987

MI

MI

39548K

39548K

53524

4/3/95

3295

TO

TO

E39548

E39548

64521

5/4/96

3295

PR

PR

839548

839548

73321

5/2/98

9345

PR

PR

839548

839548

Auto

Prov

Numero

Cognome

Nome

MI

39548K

Rossi

Mario

TO

E39548

Rossi

Mario

PR

839548

Neri

Luca

MI

39548K

TO

E39548

PR

839548

Basi di dati II


Vincolo di integrit referenziale

Vincolo di integrità referenziale

  • Un vincolo di integrità referenziale(“foreignkey”) 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

Basi di dati II


Basi di dati modulo 2

Infrazioni

Codice

Data

Vigile

Prov

Numero

34321

1/2/95

3987

MI

39548K

53524

4/3/95

3295

TO

E39548

TO

E39548

64521

5/4/96

3295

PR

839548

73321

5/2/98

9345

PR

839548

Auto

Prov

Numero

Cognome

Nome

MI

E39548

Rossi

Mario

TO

F34268

Rossi

Mario

E39548

PR

839548

Neri

Luca

TO

Violazione di vincolo di integrità referenziale

Basi di dati II


Integrit referenziale e valori nulli

Integrità referenziale e valori nulli

Impiegati

Matricola

Cognome

Progetto

34321

Rossi

IDEA

53524

Neri

XYZ

64521

Verdi

NULL

73032

Bianchi

IDEA

Progetti

Codice

Inizio

Durata

Costo

IDEA

01/2000

36

200

XYZ

07/2001

24

120

BOH

09/2001

24

150

Basi di dati II


Azioni compensative

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

Basi di dati II


Rifiuto della cancellazione

Impiegati

Matricola

Cognome

Progetto

53524

Neri

XYZ

34321

Rossi

IDEA

53524

Neri

XYZ

64521

Verdi

NULL

73032

Bianchi

IDEA

Progetti

Codice

Inizio

Durata

Costo

IDEA

01/2000

36

200

XYZ

07/2001

24

120

XYZ

07/2001

24

120

BOH

09/2001

24

150

XYZ

07/2001

24

120

XYZ

07/2001

24

120

Rifiuto della cancellazione

53524

Neri

XYZ

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

Basi di dati II


Eliminazione in cascata

Impiegati

Matricola

Cognome

Progetto

53524

Neri

XYZ

34321

Rossi

IDEA

53524

Neri

XYZ

64521

Verdi

NULL

73032

Bianchi

IDEA

Progetti

Codice

Inizio

Durata

Costo

IDEA

01/2000

36

200

XYZ

07/2001

24

120

XYZ

07/2001

24

120

BOH

09/2001

24

150

XYZ

07/2001

24

120

XYZ

07/2001

24

120

Eliminazione in cascata

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

Basi di dati II


Introduzione di valori nulli

Impiegati

Matricola

Cognome

Progetto

34321

Rossi

IDEA

53524

Neri

XYZ

64521

Verdi

NULL

73032

Bianchi

IDEA

Progetti

Codice

Inizio

Durata

Costo

IDEA

01/2000

36

200

XYZ

07/2001

24

120

XYZ

07/2001

24

120

BOH

09/2001

24

150

XYZ

07/2001

24

120

XYZ

07/2001

24

120

Introduzione di valori nulli

NULL

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

Basi di dati II


Vincoli multipli su pi attributi

Auto

Prov

Numero

Cognome

Nome

MI

39548K

Rossi

Mario

TO

E39548

Rossi

Mario

PR

839548

Neri

Luca

Vincoli multipli su più attributi

Incidenti

Codice

Data

ProvA

NumeroA

ProvB

NumeroB

34321

1/2/95

TO

E39548

MI

39548K

64521

5/4/96

PR

839548

TO

E39548

Basi di dati II


Vincoli interrelazionali sintassi

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

Basi di dati II


Esempio riassuntivo

Esempio Riassuntivo

  • CREATE TABLE Clienti (CodiceCliente CHAR(3) UNIQUE NOT NULL,Nome CHAR(30) NOT NULL,Citta’ CHAR(30) NOT NULL,Sconto INTEGER NOT NULLCHECK(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)

Basi di dati II


Esempio riassuntivo1

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 DELETENO ACTION FOREIGN KEY fk_AgenteOrdine (CodiceAgente) REFERENCES Agenti ON DELETE NO ACTION

Basi di dati II


Viste view

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.

Basi di dati II


Definizione view

Definizione VIEW

  • Sintassi creazione VIEW:

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

    AS Query-Select

Basi di dati II


Esempio definizione view

Esempio definizione VIEW

  • CREATE VIEW MediaVoti (Matricola,Media)AS SELECT Matricola, AVG(Voto) FROM Esami GROUP BY Matricola

  • Esecuzione:SELECT *FROM MediaVoti

Basi di dati II


Le view possono essere usate come tabelle

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

Basi di dati II


Le view possono essere usate come tabelle1

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.

Basi di dati II


Uso delle view per query complesse

Uso delle VIEW per query complesse

  • Semplificare query complesse

  • Esempio: non possiamo scrivereSELECT AVG(COUNT(*))FROM AGENTIGROUP BY ZONE

    • AVG deve agire sui valori di un attributo.

Basi di dati II


Uso delle view per query complesse1

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

Basi di dati II


Uso delle view per sicurezza

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

Basi di dati II


Mascherare l organizzazione logica dei dati tramite view

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 agente

    1) CREATE TABLE Zone (Zona CHAR(8), Supervisore CHAR(3))

    AS SELECT DISTINCT Zona,Supervisore

    FROM Agenti

    2) CREATE TABLE NuoviAgenti

    AS SELECT CodiceAgente,Nome,Zona,Commissione

    FROM Agenti

    3) DROP Agenti

    4) CREATE VIEW Agenti

    AS SELECT *

    FROM NuoviAgenti NATURAL JOIN Zone

Basi di dati II


Aggiornamento delle view

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…

Basi di dati II


Aggiornamento delle view cont

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 ASSELECT Nome, Cognome, Dipart, UfficioFROM Impiegato

  • INSERT INTO Impiegato2 VALUES (…)

    • Stipendio verra’ inizializzato a Null

    • Se Null non e’ permesso per Stipendio l’operazione fallisce

Basi di dati II


Aggiornamento view 2

Aggiornamento VIEW 2

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

  • La seguente operazione ha senso:

    • INSERT INTO ImpiegatoRossi (…’Rossi’,…)

Basi di dati II


Aggiornamento view 2 cont

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.

Basi di dati II


Aggiornamento view 3

Aggiornamento VIEW 3

  • Consideriamo il seguente caso:

    • Impiegato( Nome, Cognome, Dipart, Ufficio, Stipendio)

    • Dipartimenti( Dipart, Indirizzo)

    • CREATE VIEW IMP_IND ASSELECT 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

Basi di dati II


Aggiornamento view riepilogo

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 II


Aspetti procedurali dei dbms

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

Basi di dati II


Procedure

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

Basi di dati II


Linguaggio procedurale

Linguaggio procedurale

  • Complementano la natura dichiarativa di SQL

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

  • Scansione iterativa di tabelle

Basi di dati II


Esempio linguaggio procedurale db2

Esempio linguaggio procedurale DB2

  • Comando IF/THEN

BEGIN ATOMIC

DECLARE cur INT;

SET cur = MICROSECOND(CURRENT TIMESTAMP);

IF cur > 600000 THEN

UPDATE staff

SET name = CHAR(cur)

WHERE id = 10;

ELSEIF cur > 300000 THEN

UPDATE staff

SET name = CHAR(cur)

WHERE id = 20;

ELSE

UPDATE staff

SET name = CHAR(cur)

WHERE id = 30;

END IF;

END

Basi di dati II


Esempio linguaggio procedurale db21

Esempio linguaggio procedurale DB2

  • Comando FOR per scansione tabella

BEGIN ATOMIC

FOR V1 AS

SELECT dept AS dname, max(id) AS max_id

FROM staff

GROUP BY dept

HAVING COUNT(*) > 1

ORDER BY dept

DO

UPDATE staffSET id = id * -1

WHERE id = v1.max_id;

UPDATE staff SET dept = dept / 10

WHERE dept = v1.dname AND dept < 30;

END FOR;

END

Basi di dati II


Esempio linguaggio procedurale db22

Esempio linguaggio procedurale DB2

  • Comando WHILE per scansione tabella

BEGIN ATOMIC

DECLARE c1, C2 INT DEFAULT 1;

WHILE c1 < 10 DO

WHILE c2 < 20 DO

SET c2 = c2 + 1;

END WHILE;

SET c1 = c1 + 1;

END WHILE;

UPDATE staff

SET salary = c1 ,comm = c2

WHERE id = 10;

END

Basi di dati II


Vantaggi delle procedure

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.

Basi di dati II


Basi di dati modulo 2

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 II


Basi di dati attive

Basi di dati “Attive”

  • Trigger: Regole basate sul paradigma Event-Condition-Action (ECA) incorporate nella base di dati

    • Struttura tipica di una regola/trigger:whenEventif 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 II


Basi di dati attive cont

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.

Basi di dati II


Utilizzo trigger

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

Basi di dati II


Utilizzo trigger1

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

Basi di dati II


Paradigma event condition action

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

Basi di dati II


Esempi trigger

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 INSERTEDwhere stipendio>100)

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

Basi di dati II


Esempio esecuzione trigger

Esempio esecuzione trigger

  • La tabella impiegati inizialmente:

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

  • Il trigger ControlloStipendio parte:

Basi di dati II


Esempio esecuzione trigger cont

Esempio esecuzione trigger,cont

  • Il trigger ControlloStipendio parte di nuovo: Ricorsione

  • Inoltre, alla fine la tabella ImpiegatiRicchi conterra’:

Basi di dati II


Granularita

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

Basi di dati II


Tuple e tabelle di transizione

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

Basi di dati II


Modo di esecuzione del trigger

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

Basi di dati II


Esempio trigger before in db2

Esempio Trigger BEFORE in DB2

CREATE TRIGGER T1NO CASCADE BEFORE INSERT ON IMPIEGATOREFERENCING NEW AS NFOR EACH ROW MODE DB2SQLWHEN ( N.STIPENDIO > (SELECT MAX(STIPENDIO)FROM IMPIEGATO))SET N.STIPENDIO = (SELECT MAX(STIPENDIO)FROM IMPIEGATO)

Basi di dati II


Esempio granularita per after in db2

Esempio granularita’ per AFTER in DB2

create trigger storico_prog_canc

after delete on progetti

referencing old as o

for each row mode db2sql

begin atomic

insert into progetti_cancellati

values( o.codice, current date);

end

create trigger storico_prog_canc2

after delete on progetti

referencing old_table as o

for each statement mode db2sql

begin atomic

insert into progetti_cancellati

select codice, current date

from o;

end

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

)

Basi di dati II


Esecuzione del trigger

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

Basi di dati II


Esempio sintassi trigger oracle

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)

Basi di dati II


Esempio oracle pl sql

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

Basi di dati II


Creazione ed aggiornamento automatico di una tabella

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;

Basi di dati II


Cancellazione automatica

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”

Basi di dati II


Vantaggi sull uso dei trigger

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

Basi di dati II


Problemi di applicabilita dei trigger

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)

Basi di dati II


Altri problemi semantici

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

    • 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

Basi di dati II


  • Login