Mihaela Elena Breabn

FII 2014-2015

Implementarea constrngerilor

Declanatoare

Views

BAZE DE DATE

Obiective

Constrngeri de integritate

Declanatoare

Views

2

Constrngeri de integritate (statice)

(1)

Restricioneaz strile posibile ale bazei de date

Pentru a elimina posibilitatea introducerii eronate de valori la inserare

Pentru a satisface corectitudinea la actualizare

Foreaz consistena

Transmit sistemului informaii utile stocrii, procesrii interogrilor

Tipuri

Non-null

Chei

Integritate referenial

Bazate pe atribut i bazate pe tuplu

Aseriuni generale

3

Constrngeri de integritate

(2)

Declarare

Odat cu schema

Dup crearea schemei

Realizare

Verificare dup fiecare modificare

Verificare la final de tranzacie

4

Constrngeri de integritate peste 1 variabil Implementare

CREATE TABLE tabel (

a1 tip not null, -- accept doar valori nenule

a2 tip unique, --cheie candidat format dintr-un singur atribut

a3 tip primary key, -- cheie primar format dintr-un singur

atribut, implicit {not null, unique}

a4 tip references tabel2 (b1), --cheie strin format dintr-un

singur atribut

a5 tip check (condiie) -- condiia e o expresie booleana

formulat peste a5: (a54), (a5 between 5 and 10),

(a5 in (5,6,7,8,9,10))

)

5

Constrngeri de integritate peste n variabile

Implementare

CREATE TABLE tabel (

a1 tip,

a2 tip,

a3 tip,

a4 tip,

primary key (a1,a2), --cheie primar format din 2 (sau mai multe) atribute

unique(a2,a3), -- cheie candidat format din 2 (sau mai multe) atribute

check (condiie), -- expresie boolean peste variabile declarate anterior: ((a1+a3)/2>=5)

foreign key (a3,a4) references tabel2(b1,b2) -- cheie strin multi-atribut

)

6

Integritate referenial Definiii

7

uNume ora nscrieri sID sNume medie liceu sID uNume specializare decizia

Universitate Student Aplica

Integritate referenial de la R.A la S.B:

fiecare valoare din coloana A a tabelului R trebuie s apar n

coloana B a tabelului S

A se numete cheie strin

B trebuie s fie cheie primar pentru S sau mcar declarat unic

sunt permise chei strine multi-atribut

Integritate referenial Realizare

8

Comenzi ce pot genera nclcarea restriciilor:

inserri n R

tergeri n S

actualizri pe R.A sau S.B

Aciuni speciale:

la teregere din S:

ON DELETE RESTRICT (implicit) | SET NULL | CASCADE

la actualizri pe S.B:

ON UPDATE RESTRICT (implicit) | SET NULL | CASCADE

Integritate referenial oul sau gina?

9

CREATE TABLE chicken (cID INT PRIMARY KEY,

eID INT REFERENCES egg(eID));

CREATE TABLE egg(eID INT PRIMARY KEY,

cID INT REFERENCES chicken(cID));

CREATE TABLE chicken(cID INT PRIMARY KEY, eID INT);

CREATE TABLE egg(eID INT PRIMARY KEY, cID INT);

ALTER TABLE chicken ADD CONSTRAINT chickenREFegg

FOREIGN KEY (eID) REFERENCES egg(eID)

DEFERRABLE INITIALLY DEFERRED; -- Oracle

ALTER TABLE egg ADD CONSTRAINT eggREFchicken

FOREIGN KEY (cID) REFERENCES chicken(cID)

DEFERRABLE INITIALLY DEFERRED; -- Oracle

INSERT INTO chicken VALUES(1, 2);

INSERT INTO egg VALUES(2, 1);

COMMIT;

Cum rezolvai problema inserrii dac

verificarea constrngerii se efectueaz

imediat dup fiecare inserare?

Dar problema tergerii tabelelor?

Aseriuni

create assertion Key

check ((select count(distinct A) from T) =

(select count(*) from T)));

create assertion ReferentialIntegrity

check (not exists (select * from Aplica

where sID not in (select sID from Student)));

create assertion AvgAccept

check (3.0 < (select avg(medie) from Student

where sID in

(select SID from Aplica where decizie = DA')));

10

Constrngeri de integritate

Abateri de la standardul SQL

Postgres, SQLite, Oracle implementeaz i valideaz toate constrngerile anterioare, MySQL permite declararea

constrngerilor de tip check dar nu le valideaz

Standardul SQL permite utilizarea de interogri n clauza check

ns nici un SGBD nu le suport

Nici un SGBD nu a implementat aseriunile din standardul SQL,

funcionalitatea lor fiind furnizat de declanatoare

11

12

DEMO

(fiierul constrngeri.sql)

Declanatoare (constrangeri dinamice)

Monitorizeaz schimbrile n baza de date, verific

anumite condiii i iniiaz aciuni

Reguli eveniment-condiie-aciune

Introduc elemente din logica aplicaiei n SGBD

Foreaz constrngeri care nu pot fi exprimate altfel

Sunt expresive

Pot ntreprinde aciuni de reparare

implementarea variaz n funcie de SGBD, exemplele de aici

urmresc standardul SQL

13

Declanatoare Implementare

14

Create Trigger nume Before|After|Instead Of evenimente [ variabile-refereniate ] [ For Each Row ] -- actiune se execut pt fiecare linie modificat (tip row vs. statement) [ When ( conditie ) ] -- ca o condiie WHERE din SQL actiune -- n standardul SQL e o comand SQL, n SGBD-uri poate fi bloc procedural

evenimente: INSERT ON tabel

DELETE ON tabel

UPDATE [OF a1,a2,] ON tabel

variabile-refereniate (dup declarare pot fi utilizate n condiie i aciune): OLD TABLE AS var

NEW TABLE AS var

OLD ROW AS var pentru ev. DELETE, UPDATE

NEW ROW AS var pentru ev. INSERT, UPDATE

doar pentru triggere de

tip row

Declanatoare Exemplu (1)

15

integritate referenial de la R.A la S.B cu tergere n

cascad

Create Trigger Cascade After Delete On S Referencing Old Row As O

For Each Row

[ fr condiii ] Delete From R Where A = O.B

Create Trigger Cascade After Delete On S Referencing Old Table As OT

[ For Each Row ]

[ fr condiii ] Delete From R Where

A in (select B from OT)

Declanatoare Capcane

16

mai multe declanatoare activate n acelai timp: care se

execut primul?

aciunea declanatorului activeaz alte declanatoare:

nlnuire sau auto-declanare ce poate duce la ciclare

Declanatoare Abateri de la standardul SQL

17

Postgres

cel mai apropiat de standard

implementeaz row+statement, old/new+row/table

sintaxa sufer abateri de la standard

SQLite

doar tip row (fr old/new table)

se execut imediat, dup modificarea fiecrei linii (abatere comportamental de la standard)

MySQL

doar tip row (fr old/new table)

se execut imediat, dup modificarea fiecrei linii (abatere comportamental de la standard)

permite definirea unui singur declanator / eveniment asociat unui tabel

Oracle

implementeaz standardul: row+statement cu modificri uoare de sintax

tipul instead-of e permis numai pt. view-uri

permite inserarea de blocuri procedurale

introduce restricii pentru a evita ciclarea

aprofundate la laborator

18

DEMO

(fiierul declansatoare.sql)

View-uri

19

physical storage

Internal level

Conceptual level

View_1 View_2 View_3 View_n

Motivaie

20

ascunderea unor date fa de unii utilizatori

uurarea formulrii unor interogri

acces modular la baza de date

aplicaiile reale tind s utilizeze foarte multe view-uri

Definire i utilizare

21

Un view este n esen o interogare stocat formulat peste

tabele sau alte view-uri

Schema view-ului este cea a rezultatului interogrii

Conceptual, un view este interogat la fel ca orice tabel

n realitate, interogarea unui view este rescris prin inserarea

interogrii ce definete view-ul urmat de un proces de

optimizare specific fiecrui SGBD

Sintaxa

Create View numeView [a1,a2,] As

Modificarea view-urilor

22

View-urile sunt n general utilizate doar n interogri ns

pentru utilizatorii externi ele sunt tabele: trebuie s poat

suporta comenzi de manipulare/modificare a datelor

Soluia: modificri asupra view-ului trebuie s fie rescrise n

comenzi de modificare a datelor n tabelele de baz

de obicei este posibil

uneori exist mai multe variante

Exemplu

R(A,B), V(A)=R[A], Insert into V values(3)

R(N), V(A)=avg(N), update V set A=7

Modificarea view-urilor

Abordri

23

creatorul view-ului rescrie toate comenzile de modificare

posibile cu ajutorul declanatorului de tip INSTEAD OF

acoper toate cazurile

garanteaz corectitudinea?

standardul SQL prevede existena de view-uri inerent

actualizabile (updatable views) dac:

view-ul e creat cu comanda select fr clauza DISTINCT pe o singur

tabel T

atributele din T care nu fac parte din definiia view-ului pot fi NULL sau

iau valoare default

subinterogrile nu fac referire la T

nu exist clauza GROUP BY sau alt form de agregare

View-uri materializate

24

Create Materialized View V [a1,a2,] As

are loc crearea unui nou tabel V cu schema dat de rezultatul interogrii

tuplele rezultat al interogrii sunt inserate n V

interogrile asupra lui V se execut ca pe orice alt tabel

Avantaje:

specifice view-urilor virtuale + crete viteza interogrilor

Dezavantaje: V poate avea dimeniuni foarte mari

orice modificare asupra tabelelor de baz necesit refacerea lui V

problema modificrii tabelelor de baz la modificarea view-ului rmne

Cum alegem ce materializm

25

dimensiunea datelor

complexitatea interogrii

numrul de interogri asupra view-ului

numrul de modificri asupra tabelelor de baz ce afecteaz

view-ul i posibilitatea actualizrii incrementale a view-ului

punem n balan timpul necesar execuiei interogrilor i

timpul necesar actualizrii view-ului

26

DEMO

(fiierul views.sql)

Bibliografie

27

Hector Garcia-Molina, Jeff Ullman, Jennifer Widom:

Database Systems: The Complete Book (2nd edition), Prentice

Hall; (June 15, 2008)

Oracle:

http://docs.oracle.com/cd/B28359_01/server.111/b28310/general0

05.htm

http://www.oracle-base.com/articles/9i/MutatingTableExceptions.php

http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm