BAZE DE DATE

Implementarea constrngerilor View-uri @FII (2011-2012)prezentat de

Mihaela Elena Breabn

Tematic cursProiectarea bazelor de date relaionaleNormalizare i denormalizare Modelul entitate-asociere, diagrame UML Constrngeri i declanatoare View-uri Indeci

Procesarea interogrilor Managementul tranzaciilor OLAP, Baze de date distribuite, NoSQL, Data Mining

2

ObiectiveImplementarea constrngerilorConstrngeri de integritate Declanatoare

View-uriRol i definire Tratarea comenzilor de modificare

3

Constrngeri de integritate (statice) (1)Restricioneaz strile posibile ale bazei de datePentru a elimina posibilitatea introducerii eronate de valori la inserare Pentru a satisface corectitudinea la actualizare Foreaz consistena Transmit sistemului informaii utile stocrii, procesrii interogrilor

TipuriNon-null Chei Integritate referenial Bazate pe atribut i bazate pe uplu Aseriuni generale4

Constrngeri de integritate (2)DeclarareOdat cu schema Dup crearea schemei

RealizareVerificare dup fiecare modificare Verificare la final de tranzacie

5

Constrngeri de integritate peste 1 variabil ImplementareCREATE 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)) )6

Constrngeri de integritate peste n variabile ImplementareCREATE 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 multiatribut )7

Integritate referenial DefiniiiStudentsID sNume medie liceu

AplicaiesID uNume specializare decizia

UniversitateuNume ora nscrieri

Integritate referenial de la R.A la S.B:fiecare valoare n 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-atribut8

Integritate referenial RealizareComenzi ce pot genera violri: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

9

Integritate referenial Problema chicken-eggCREATE 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) INITIALLY DEFERRED DEFERRABLE; -- Oracle ALTER TABLE egg ADD CONSTRAINT eggREFchicken FOREIGN KEY (cID) REFERENCES chicken(cID) INITIALLY DEFERRED DEFERRABLE; -- Oracle INSERT INTO chicken VALUES(1, 2); Cum rezolvai problema inserrii dac INSERT INTO egg VALUES(2, 1); verificarea constrngerii se efectueaz imediat dup fiecare inserare? COMMIT;

Dar problema tergerii tabelelor?10

Aseriunicreate 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')));11

Constrngeri de integritate Abateri de la standardul SQLPostgres, SQLite, Oracle foreaz toate constrngerile anterioare, MySQL permite declararea constrngerilor de tip check dar nu le foreaz 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

12

DEMO(fiierul constrngeri.sql)

13

Declanatoare (dinamice)Monitorizeaz schimbrile n baza de date, verific anumite condiii i iniiaz aciuni Reguli eveniment-condiie-aciuneIntroduc 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

14

Declanatoare ImplementareCreate 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 proceduralevenimente:INSERT ON tabel DELETE ON tabel UPDATE [OF a1,a2,] ON tabel

variabile-refereniate (dup declarare pot fi utilizate n condiie i aciune): OLD ROW AS var pentru ev. DELETE, UPDATE doar pentru triggere de NEW ROW AS var pentru ev. INSERT, UPDATE tip row OLD TABLE AS var NEW TABLE AS var15

Declanatoare Exemplu (1)integritate referenial de la R.A la S.B cu tergere n cascadCreate 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)

16

Declanatoare Exemplu (2)Create Trigger IncreaseInserts After Insert On T Referencing New Row As NR, New Table As NT For Each Row When (Select Avg(V) From T) < (Select Avg(V) From NT) Update T set V=V+10 where K=NR.K

nu e posibil definirea unui declanator echivalent de tip statement are un comportament nedeterminist

17

Declanatoare Capcanemai multe declanatoare activate n acelai timp: care se execut primul? aciunea declanatorului activeaz alte declanatoare: nlnuire sau auto-declanare ce poate duce la ciclare

18

Declanatoare Abateri de la standardul SQLPostgrescel mai apropiat de standard implementeaz row+statement, old/new+row/table sintaxa sufer abateri de la standard

SQLitedoar tip row (fr old/new table) se execut imediat, dup modificarea fiecrei linii (abatere comportamental de la standard)

MySQLdoar 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

Oracleimplementeaz 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 laborator19

DEMO(fiierul declansatoare.sql)

20

View-uriView_1 View_2 View_3 View_n

Conceptual level

Internal level

physical storage

21

Motivaieascunderea 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

22

Definire i utilizareUn 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 23

Modificarea view-urilorView-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 bazde obicei este posibil uneori exist mai multe variante

ExempluR(A,B),V(A)=A(R), Insert into V values(3) R(N),V(A)=avg(N), update V set A=7

24

Modificarea view-urilor Abordricreatorul view-ului rescrie toate comenzile de modificare posibile cu ajutorul declanatorului de tip INSTEAD OFacoper toate cazurile nu garanteaz corectitudinea

standardul SQL prevede existena de view-uri inerent actualizabile (updatable views) dac:view-ul e creat cu comand 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

25

View-uri materializateCreate Materialized View V [a1,a2,] As are loc crearea unui nou tabel V cu schema dat de rezultatul interogrii uplele 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

26

Cum alegem ce materializmdimensiunea 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

27

DEMO(fiierul views.sql)

28

BibliografieHector 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#i1006732 http://www.oracle-base.com/articles/9i/MutatingTableExceptions.php

29