Date post: | 08-Mar-2016 |
Category: |
Documents |
Upload: | babydoll777 |
View: | 237 times |
Download: | 0 times |
of 27
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