Basi di dati

MATRICOLA NOME COGNOME STIPENDIO RUOLO

123 Mario Rossi 1000 Segretario

456 Fabio Verdi 1500 Segretario

789 Claudio Bianchi 3000 Direttore

CREATE VIEW Imp1500

AS ( SELECT *

FROM impiegato

WHERE stipendio >= 1500 );

MATRICOLA NOME COGNOME STIPENDIO RUOLO

456 Fabio Verdi 1500 Segretario

789 Claudio Bianchi 3000 Direttore

UPDATE VIEW Imp1500

SET stipendio = 100

WHERE matricola = ‘456’ ;

N.B: È possibile inserire, dopo la query, la clausola WITH LOCAL | CASCADED CHECK

OPTION.

LOCAL: verifica se il cambiamento porta alla perdita di informazioni alla vista

CASCADED:

Cancellare una vista

nomeVista

DELETE VIEW CASCADE | RESTRICT ;

TRIGGER: esegue delle azioni in seguito a degli eventi sul database.

Rule Engine:

I trigger seguono il modello ECA, il tutto gestito dal

• →

E Evento

• →

C Condizione

• →

A Azione update, insert e delete.

Gli eventi sono 43 5 Aprile 2019

Le regole possono essere:

• Interne, ovvero PK, FK e CHECK

• Esterne, ovvero BR

N.B: vedi pagina successiva per sintassi più completa.

nomeTrigger

CREATE TRIGGER

AFTER | BEFORE evento1, evento2 …

tabellaTarget

ON [default]

FOR EACH ROW | FOR EACH STATEMENT

REFERENCING NEW | OLD

condizione

WHEN ( )

BEGIN

…

istruzioni SQL DML | PL SQL

…

END ; AFTER | BEFORE

Modalità:

INSERT | UPDATE | DELETE

Evento: FOR EACH…

Granularità:

PL SQL: sono statement proprietarie del DBMS, simili a istruzioni dei linguaggi di

programmazione. NEW OLD

✓ ✓

UPDATE ✓ ×

INSERT ✓

×

DELETE 44 16 Aprile 2019

nomeTrigger

CREATE TRIGGER

modalità evento1, evento2 …

tabellaTarget

ON IMMEDIATE | DEFERRED

FOR EACH STATEMENT | FOR EACH ROW

variabili

REFERENCING

condizione

WHEN variabili

DECLARE

BEGIN

…

istruzioni SQL DML | PL SQL

…

END ;

Modalità:

AFTER: per eseguire dopo l’evento

• BEFORE: per eseguire prima dell’evento

•

Evento:

• INSERT

• UPDATE

• DELETE

IMMEDIATE: Il trigger si attiva dopo ogni statement.

DEFERRED: Il trigger si attiva alla fine del blocco delle transazioni.

FOR EACH STATEMENT: Opera indipendentemente dal cambiamento effettuato dalla

singola tupla.

FOR EACH ROW: Opera conoscendo anche gli altri attributi. Attiva anche le variabili NEW

e OLD e le clausole WHEN e REFERENCING.

REFERENCING: per rinominare i valori dalle variabili NEW e OLD.

DECLARE: definisce variabili di supporto da utilizzare nella fase esecutiva.

Priorità di esecuzione per i trigger

1. BEFORE | FOR EACH STATEMENT

2. BEFORE | FOR EACH ROW

3. AFTER | FOR EACH ROW

4. AFTER | FOR EACH STATEMENT 45 16 Aprile 2019

es.

Ridurre del 10% lo stipendio di tutti gli impiegati quando la media degli stipendi supera

€5000.

IMPIEGATO( matricola, nome, cognome, stipendio )

CREATE TRIGGER AggiornaStip

AFTER INSERT, UPDATE, DELETE

ON impiegato

FOR EACH STATEMENT

BEGIN

UPDATE Impiegato

SET stipendio = stipendio * 0.9

WHERE (SELECT AVG(stipendio)

FROM Impiegato) > 5000 ;

END ;

Secondo metodo di risoluzione utilizzando istruzioni PL SQL

BEGIN

SELECT AVG(stipendio) INTO MediaStip

FROM stipendio

IF (MediaStip > 5000) THEN

UPDATE Impiegato

SET stipendio = stipendio * 0.9 ;

END IF;

END ;

es.

Realizzare il trigger che realizzi ON DELETE SET NULL su Dipartimento

IMPIEGATO( matricola, nome, cognome, codDip )

DIPARTIMENTO( codice, nome ) 46 16 Aprile 2019

CREATE TRIGGER deleteDip

AFTER DELETE

ON Dipartimento

FOR EACH ROW

WHEN ( SELECT COUNT(*)

FROM Impiegato

WHERE codDio = :OLD.codice ) > 0

BEGIN

UPDATE Impiegato

SET codDip = NULL

WHERE codDip = :OLD.codice;

END;

Con istruzioni PL SQL

BEGIN

SELECT COUNT(*)

INTO numDip

FROM Impiegato

WHERE codDip = :OLD.codDip;

IF (numDip > 0) THEN

UPDATE Impiegato

SET codDip = NULL

WHERE codDip > :OLD.cod;

END IF;

END;

es.

Creare un trigger che dopo ogni ordine modifica il totale.

ORDINE( codProd, codFornitore, data, qt )

TOTALE( codProd, codFornitore, qtTot )

CREATE TRIGGER AggiornaTotale

AFTER INSERT

ON Ordine

FOR EACH ROW

BEGIN

UPDATE totale

SET qtTot = qtTot + :NEW.qt

WHERE codProd = :NEW.codProd AND

codFornitore = :NEW.codFornitore;

END; 47 16 Aprile 2019

es.

Creare un trigger che, al cambio del voto in peggio, aggiorni le informazione nella tabella

Valutazione.

BIBLIO( ISBN, titolo, categoria, voto )

VALUTAZIONE ( ISBN, timeStamp, titolo, categoria, nuovoVoto, vecchioVoto )

CREATE TRIGGER cambioVoto

AFTER UPDATE

ON Biblio

FOR EACH ROW

WHEN :NEW.voto < :OLD.voto

BEGIN

INSERT INTO Valutazione

VALUES(:NEW.ISBN, SYSDATE, :NEW.titolo, :NEW.categoria, :NEW.voto, :OLD.voto);

END;

TRIGGER - TRACCIA D’ESAME 4 Settembre 2017

INTERPRETA( codFilm, codAttore, compenso, ruolo )

MAX_ATTORE( codAttore, max_compenso )

CREATE TRIGGER AggiornaMaxAttore

AFTER INSERT

ON Interpreta

FOR EACH ROW

BEGIN

UPDATE MAX_ATTORE

SET max_compenso = :NEW.codAttore AND :NEW.compenso > max_compenso;

END; 48 16 Aprile 2019

Pagina 49 17 Aprile 2019

Analisi del DBMS

Il DBMS fa da “intermediario” tra l’utente e il database. DB

DBMS

transazioni

Le sono un’insieme di istruzioni SQL eseguite in blocco.

BOT Of Transaction) EOT (End Of

Ogni transazione inizia con un (Begin e finisce con un

Transaction).

Ogni transazione ha due comandi che comunicano lo stato della stessa:

• COMMIT: conferma che tutte le operazioni sono andate a buon fine ed è posto alla fine

della transazione;

• ABORT: comunica che a seguito di un errore la transazione non è andata a buon fine.

Può essere posto in qualsiasi punto della transazione e genera una serie di operazioni

di rollback, che riportano il database ad uno stato precedente all’esecuzione della

transazione (ovvero precedente al BOT).

N.B: La transazione deve necessariamente avere uno dei due comandi e non possono

essere contemporaneamente presenti. COMMIT

BOT ABORT EOT

Una transazione si dice BEN FORMATA se inizia con un BOT, contiene un COMMIT o un

ABORT (e successivamente non esegue altre operazioni).

Un sistema che gestisce le transazioni si dice TRANSAZIONALE e deve gestire le

proprietà ACID:

• A - Atomicità

Ogni transazione è un insieme atomico di istruzioni, ovvero tutte le operazioni sono

eseguite tutte con successo, oppure si ritorna ad una situazione precedente.

49 17 Aprile 2019

• C - Consistenza

Una transazione parta da una situazione consistente e porta dopo l’esecuzione, che sia

andata a buon fine o no, ad una nuova situazione consistente.

• I - Isolamento

Nel caso in cui ci siano più transazioni attive contemporaneamente, ogni transazione

deve essere eseguita come se fosse l’unica attiva.

• D - Durabilità

Se una transazione termina con successo l’esecuzione, i risultati devono essere

applicati in maniera stabile sul database.

A questo scopo vengono utilizzati dei file di log per registrare le operazioni.

N.B: Per garantire l’atomicità e la durabilità è utilizzato il modulo controllore di affidabilità,

per l’isolamento se ne occupa il controllore della concorrenza. Per quanto riguarda la

consistenza, è garantita grazie alle istruzioni SQL.

File di log

I file di log si occupano di registrare tutte le operazioni che avvengono sul database.

Possono essere scritti diverse tipologie di record:

• RECORD TRANSAZIONALI ID_Tr - Id della transazione.

→

- Bot, Commit, Abort ID_Tr + TS ID_Obj - Oggetto che ha subito l’operazione.

→

- Insert ID_Tr + ID_Obj + AS + TS AS - After State, stato dell’oggetto dopo l’operazione.

→

- Delete ID_Tr + ID_Obj + BS + TS BS - Before State, stato dell’oggetto prima

dell’operazione.

→

- Update ID-Tr + ID_Obj + AS + BS + TS TS - Time Stamp

• RECORD DI SISTEMA →

- CHECKPOINT( T , T … T ) elenco di tutte le transazioni attive al momento della

1 2 n

creazione del record di checkpoint. Alla creazione, il database si blocca per consentire

la registrazione del record.

→

- DUMP( posizione ) fa un backup completo del database nella posizione

specificata. Anche in questo caso il database si blocca per consentire la realizzazione

del backup.

Il database può essere soggetto a guasti:

• SOFT FAILURE

Guasti di sistema di facile risoluzione che necessitano una ripresa a caldo (es. assenza

temporanea di corrente, cali di tensione, riavvio del sistema necessario…).

• HARD FAILURE

Guasti più gravi che necessitano una ripresa a freddo (es. malfunzionamento di hard e

server…). 50 17 Aprile 2019

Prima di scrivere le operazioni sul database in maniera permanente, il DBMS si interfaccia

con la memoria centrale. DB

DBMS

MC

I risultati delle operazioni eseguite vengono prima salvati in memoria centrale e

successivamente salvati sul database.

La creazione di un record di checkpoint impone un passaggio forzato da memoria

centrale a database.

La ripresa dagli errori può essere generalizzata attraverso un ciclo: RUN

AVVIO

STAND ERROR

BY RECOVERY

Potrebbero verificarsi dei problemi nella fase di recovery dagli errori che porterebbero ad

un ciclo senza fine. Per questo motivo vengono definite due operazioni:

• UNDO(c) per annullare un’operazione. Vale UNDO(c) = UNDO(UNDO(c))

• REDO(c) per rieseguire un’operazione. Vale REDO(c) = REDO(REDO(c))

51 17 Aprile 2019

DUMP B U C CP D U FAILURE

1 1 2 1 1

Ripresa a caldo - SOFT FAILURE

D e U vengono perse perché in memoria centrale.

1 1

• Viene letto il file di log a partire dall’ultima operazione (U ) e si torna indietro fino

1

all’ultimo checkpoint;

• Recupera dai checkpoint gli ID memorizzati e crea due insiemi:

- UNDO-SET, ovvero l’insieme delle operazioni da disfare, quindi le transazioni attive

che dopo il checkpoint non hanno terminato l’esecuzione (U , D , U , B ) per

1 1 1 1

rispettare l’atomicità;

- REDO-SET, ovvero le transazioni attive che hanno terminato l’esecuzione dopo il

checkpoint.

Ripresa a freddo - HARD FAILURE

Vengono persi tutti i dati fino all’ultimo dump.

• Accede al file di log (se esist