1
I. CONCEPTE ALE BAZELOR DE DATE RELAIONALE
1.1 Definiii
1.2 Niveluri de abstractizare a datelor
1.3 Componente ale bazelor de date relaionale
1.4 Proiectarea bazelor de date relaionale. Etape. Normalizarea
bazelor de date
1.1 Definiii
Baze de date
O baz de date este o colecie de informaii interrelaionate gestionate ca o singur unitate.
Aceast definiie este intenionat foarte larg, deoarece exist mari diferene ntre concepiile diferiilor
productori care pun la dispoziie sisteme de baze de date. De exemplu, Oracle Corporation definete o
baz de date ca fiind o colecie de fiiere fizice gestionate de o singur instan (copie) a produsului
software pentru baze de date, n timp ce Microsoft definete o baz de date SQL Server ca fiind o
colecie de date i alte obiecte. Un obiect al bazei de date este o structur de date denumit stocat n
baza de date, cum ar fi un tabel, o vizualizare sau un index.
Sisteme de gestiune a bazelor de date
Un sistem de gestionare a bazei de date (DBMS - dotabase management system) este un produs
software furnizat de productorul bazei de date. Produse software precum Microsoft Access, Microsoft
SQL Server, Oracle Database, Sybase, DB2, INGRES, MySQL i PostgreSQL fac parte din categoria
DBMS sau, mai corect, DBMS relaionale (RDBMS).
Sistemul DBMS pune la dispoziie toate serviciile de baz necesare pentru organizarea i
ntreinerea bazei de date, inclusiv urmtoarele:
Transferarea datelor n i din fiierele fizice de date, n funcie de cerine.
Gestionarea accesului concurenial la date al mai multor utilizatori, inclusiv prevenirea
conflictelor care ar putea fi cauzate de actualizrile simultane.
Gestionarea tranzaciilor, astfel nct toate modificrile fcute asupra bazei de date printr-o
tranzacie s fie executate ca o singur unitate. Cu alte cuvinte, dac tranzacia reuete, toate
modificrile efectuate de tranzacie sunt nregistrate n baza de date; dac tranzacia eueaz, nici
una dintre modificri nu este nregistrat n baza de date. Totui, unele sisteme RDBMS nu
asigur suportul pentru tranzacii.
Accept un limbaj de interogare, care reprezint sistemul de comenzi folosit de utilizator pentru
a obine date din baza de date. SQL este principalul limbaj folosit pentru sistemele DBMS
relaionale.
Funcii pentru salvarea bazei de date i pentru refacerea bazei de date n urma erorilor.
Mecanisme de securitate pentru mpiedicarea accesului neautorizat la date i modificarea
acestora.
Baze de date relaionale
O baz de date relaional este o baz de date care respect modelul relaional, dezvoltat de Dr. E.
2
F. Codd. Modelul relaional prezint datele sub forma familiarelor tabele bidimensionale, similar cu o
foaie de calcul tabelar Excel. Spre deosebire de o foaie de calcul tabelar, nu este obligatoriu ca datele
s fie stocate ntr-o form tabelar, iar modelul permite i combinarea tabelelor (crearea uniunilor
(joining), n terminologia relaional) pentru formarea vizualizrilor, care sunt prezentate tot ca tabele
bidimensionale. Flexibilitatea extraordinar a bazelor de date relaionale este dat de posibilitatea de a
folosi tabelele independent sau n combinaii, fr nici o ierarhie sau secven predefinit n care
trebuie s se fac accesul la date.
1.2 Niveluri de abstractizare a datelor
ntr-un sistem informatic ce utilizeaz baze de date, organizarea datelor poate fi analizat din
mai multe puncte de vedere i pe diferite niveluri. De obicei, abordarea se face pe trei niveluri: intern,
conceptual i extern (fig. 1.1).
- Nivelul fizic (intern)
Structura datelor este descris foarte detaliat, fiind accesibil numai specialitilor (ingineri de
sistem, programatori n limbaje de asamblare sau alte limbaje apropiate de main"). Cele dou pri
principale ale bazei la acest nivel sunt:
1. un set de programe care interacioneaz cu sistemul de operare pentru mbuntirea
managementului bazei de date;
2. fiierele stocate n memoria extern a calculatorului.
Fiierele ce conin datele propriu-zise sunt alctuite din articole sau nregistrri cu format comun.
La acest nivel, structura bazei de date se concretizeaz n schema intern.
- Nivelul conceptual (global)
Este nivelul imediat superior celui fizic, datele fiind privite prin prisma semanticii lor;
intereseaz coninutul lor efectiv, ca i relaiile care le leag de alte date. Reprezint primul nivel de
abstractizare a lumii reale observate. Obiectivul acestui nivel l constituie modelarea realitii
considerate, asigurndu-se independena bazei fa de orice restricie tehnologic sau echipament
anume. ntreaga baz de date este descris prin intermediul unui numr restrns de structuri. Toi
utilizatorii i exprim nevoile de date la nivel conceptual, prezentndu-le administratorului bazei de
date, acesta fiind cel care are o viziune global necesar satisfacerii tuturor cerinelor informaionale.
La acest nivel, structura bazei de date se concretizeaz n schema conceptual.
N i v e l
e x te r n G r u p 1 . G r u p n
N i v e l
c o ncep t u a l S c hem a c o ncep t u a l a
N i v e l S c h e m a in te r n a
in te r n
M e d i u l d e s t o ca r e
Fig. 1.1 Niveluri de abstractizare a datelor
3
- Nivelul extern
Este ultimul nivel de abstractizare la care poate fi descris o baz de date. Structurile de la
nivelul conceptual sunt relativ simple, ns volumul lor poate fi deconcertant. Dac la nivelul
conceptual baza de date este abordat n ansamblul ei, n practic un utilizator sau un grup de
utilizatori lucreaz numai cu o poriune specific a bazei, n funcie de departamentul n care i
desfoar activitatea i ce atribuii au. Simplificarea interaciunii utilizatori baz de date, precum i
creterea securitii bazei de date sunt deziderate ale unui nivel superior de abstractizare, care este
nivelul extern. Astfel, structura bazei de date se prezint sub diferite machete, referite uneori i ca sub-
scheme, scheme externe sau imagini (view-uri), n funcie de nevoile fiecrui utilizator sau grup de
utilizatori.
Observaii:
Este important aceast organizare pe trei niveluri pentru c explic conceptul de independen a
datelor, prin posibilitatea de modificare a sistemului bazei de date la orice nivel fr a avea influen la
nivelele superioare. Independena datelor se poate defini n dou moduri, ce sunt aferente nivelelor
conceptual i intern.
Prin independena logic se nelege capacitatea schimbrii schemei conceptuale, fr a atrage
dup sine schimbri in schema extern sau n programele de aplicaii. Este posibil schimbarea
schemei conceptuale prin expandarea bazei de date ca urmare a adugrii de noi tipuri de nregistrri
sau a datelor nsi, sau prin reducerea bazei de date ca urmare a reducerii nregistrrilor.
Independena fizic este reprezentat prin capacitatea de schimbare a schemei interne fr
schimbarea schemei conceptuale sau externe. Schimbarea schemei conceptuale poate surveni ca
urmare a reorganizrii fizice a unor fiiere, prin crearea de noi structuri de acces menite s asigure
accesul eficient la date.
Accesul utilizatorului la informaiile din baza de date este posibil numai prin intermediul
sistemului de gestiune a bazei de date (SGBD).
1.3 Componente ale bazelor de date relaionale
1. Tabele
Unitatea primar de stocare a datelor ntr-o baz de date relaional este tabelul, care este o
structur bidimensional compus din rnduri i coloane. Fiecare tabel reprezint o entitate, ceea ce
nseamn o persoan, un loc, un lucru sau un eveniment care trebuie s fie reprezentat n baza de date,
cum ar fi un client, un cont bancar sau o tranzacie bancar. Fiecare rnd al tabelului reprezint o
apariie a entitii.
2. Relaii
Relaiile reprezint asocierile dintre tabelele bazelor de date relaionale. Dei fiecare tabel
relaional poate exista independent, esena bazelor de date este tocmai stocarea informaiilor ntre care
exist legturi. De exemplu, pe lng filmele propriu-zise, se pot stoca i informaii despre categoriile
folosite de magazin pentru organizarea inventarelor de filme. n acelai timp, putei stoca i informaii
despre copiile fiecrui film, inclusiv data la care a fost primit copia i formatul acesteia, cum ar DVD
sau VHS. Prin folosirea relaiilor, se pot asocia tabelele nrudite, ntr-un mod formal, uor de folosit
astfel nct s combinm date din tabele multiple n aceeai interogare a bazei de date, dar pstrnd
flexibilitatea de a include numai informaiile care l intereseaz pe utilizator. Posibilitatea de a selecta
4
din baza de date numai informaiile care ne intereseaz ne permite s ajustm informaiile din baza de
date n funcie de cerinele specifice ale persoanelor sau aplicaiilor care au acces la baza de date.
Figura 1.2 prezint patru tabele din baza de date a magazinului de produse video i relaiile
dintre acestea, ntr-un format cunoscut sub numele de diagram de relaii a entitilor (ERD - Entity
Relationship Diagram). Diagramele ERD ne pun la dispoziie o modalitate de prezentare a proiectului
general al unei baze de date relaionale, ntr-un format uor de neles pentru utilizatorii bazei de date,
indiferent dac au sau nu cunotine tehnice. Fiecare dreptunghi din diagram reprezint un tabel
relaional, cu numele tabelului scris deasupra liniei orizontale i coloanele tabelului enumerate pe
vertical, n poriunea principal a dreptunghiului.
MPAA_RATING
MPAA_RATING_CODE (pk)
MPAA_RATING_DESCRIPTION
MOVIE
MOVIE_ID (pk)
MOVIE_GENRE_CODE (fk1)
MPAA_RATING_CODE (fk2)
MOVIE_TITLE
RETAIL_PRICE_VHS
RETAIL_PRICE_DVD
YEAR PRODUCED
MOVIE_GENRE
MOVIE_GENRE_CODE (pk)
MOVIE_GENRE_DESCRIPTI
ON
MOVIE_COPY
MOVIE_ID (pk, fk)
COPY_NUMBER (pk)
DATE_ACQUIRED
DATE_SOLD
MEDIA_FORMAT
Fig. 1.2 Diagrama ERD a bazei de date pentru magazinul de produse video (prezentare parial)
n funcie de numrul de elemente, ntre care se stabilesc relaii, aparinnd celor dou colecii,
aceste relaii pot fi de tip unu la unu, unu la muli i muli la muli.
Relaiile de tipul 11 (unu la unu), care presupun c unui membru din colecia A i
corespunde un singur membru din colecia B.
Relaie de tip 1 la 1
Relaiile de tipul 1m sau m1 (unu la muli sau muli la unu), care presupun c unui
membru din prima entitate A i corespund mai muli membri din a doua entitate B; astfel de relaii se
mai numesc i relaii ierarhice.
5
a) b)
Relaie de tip 1 la m (a) i m la 1 (b)
Relaiile de tipul mm (muli la muli), n care unui membru din entitatea A i corespund
mai multe date din colecia B i mai multor date din colecia A i corespunde o singur dat din
colecia B.
Relaie de tip m la m
Relaii de tip muli la muli se mai numesc i relaii de tip reea. O relaie muli la muli se va
descompune ntotdeauna n dou relaii, o relaie tip unu la muli i respectiv o a doua relaie de tip
muli la unu prin intermediul unei entiti de legtur.
Fiecare relaie este prezentat n diagrama ERD ca o linie ce conecteaz dou tabele. Cele dou
capete ale liniei arat cardinalitatea maxim a relaiei, respectiv numrul maxim de rnduri dintr-un
tabel care pot fi asociate cu un rnd dat din tabelul aflat la cellalt capt al relaiei.
Cardinalitatea maxim poate fi:
unu (caz n care linia nu are nici un simbol special la capt) sau
mai multe (caz n care linia se termin cu un simbol numit picior de cioar (crow'sfoot) - linia se
mparte la capt n trei segmente).
n apropiere de captul liniei se afl un alt simbol, care arat cardinalitatea minim, adic
numrul minim de rnduri dintr-un tabel care poate fi asociat cu tabelul de la cellalt capt al relaiei.
Cardinalitatea minim poate fi zero, indicat printr-un cerc desenat pe linie, sau unu, indicat
printr-o liniu care taie linia relaiei.
De exemplu, relaia dintre tabelele MPAA_RATING i MOVIE din figura 1-2 este o relaie de
tip unu-la-mai-muli, ceea ce nseamn c fiecare rnd din tabelul MPAA_RATING (tabelul din partea
unu, numit i tabel printe") poate fi asociat cu mai multe rnduri din tabelul MOVIE (tabelul din
partea mai muli" a relaiei, numit i tabel copil'"), dar fiecare rnd din tabelul MOVIE poate fi
asociat cu un singur rnd din tabelul MPAA_RATING.
Relaia este logic, deoarece orice film lansat n SUA poate avea o singur categorie MPAA, dar
o categorie poate fi asociat mai multor filme diferite. Este adevrat ca filmele sunt uneori cenzurate"
pentru a putea fi ncadrate n diferite categorii, dar aceast problem este rezolvat uor, prin tratarea
diferitelor versiuni ale aceluiai film ca i cum ar fi filme diferite, la fel cum facem atunci cnd un film
este reluat cu ali actori. Este foarte important s inei seama de aceste lucruri, deoarece bazele de date
relaionale accept numai relaiile de tip unu-la-mai-muli sau unu-la-unu.
Toate relaiile din fig. 1.2 sunt obligatorii n partea unu" i opionale n partea mai muli", aceasta
fiind cea mai frecvent folosit form de relaie. Dac ne uitm din nou la relaia dintre tabelele
MPAA_RATING i MOVIE, aceasta nseamn c fiecare rnd din tabelul MOVIE trebuie s aib un
rnd corespondent n tabelul MPAA_RATING, dar nu este obligatoriu ca fiecare rnd din tabelul
MPAA_RATING s aib asociat un rnd din tabelul MOVIE. Dac vrei s permitei ca inventarul de
filme al magazinului s conin titluri care nu au asociat o categorie MPAA, liniua de la captul
dinspre tabelul MPAA_RATING al liniei care reprezint relaia cu tabelul MOVIE va fi nlocuit de
un cerc. Dei sunt relativ frecvent ntlnite cazurile n care partea unu" a unei relaii nu este
obligatorie, este foarte neobinuit s avei o relaie n care s fie obligatorie partea mai muli" a
relaiei, ceea ce ar nsemna c tabelul printe trebuie s aib n orice moment cel puin un copil" n
baza de date.
Relaiile sunt implementate folosind coloane corespondente din cele dou tabele participante. n
diagrama ERD, coloana sau coloanele subliniate din fiecare tabel, avnd n dreapta notaia pk",
reprezint cheia primar (primary key), adic o coloan sau un set de coloane care identific n mod
unic fiecare rnd dintr-un tabel.
Un tabel poate avea o singur cheie primar. Totui, o cheie primar poate fi compus din mai
multe coloane, dac aceasta este calea de formare a unei chei unice. Dac o cheie primar este folosit
ntr-un alt tabel pentru stabilirea unei relaii, poart numele de cheie extern.
Cheile primare i cheile externe sunt blocuri de construcie fundamentale ale modelului
relaional, deoarece stabilesc relaii i permit crearea legturilor ntre date, atunci cnd este necesar.
Trebuie s nelegei acest concept pentru a putea nelege cum funcioneaz bazele de date relaionale.
3. Restricii
O restricie este o regul specificat pentru un obiect al bazei de date (de obicei, un tabel sau o
coloan), avnd rolul de a limita ntr-un mod oarecare domeniul de valori permise pentru obiectul
respectiv al bazei de date
Dup ce sunt specificate, restriciile sunt impuse automat de sistemul DBMS i nu pot fi ocolite
dect dac o persoan autorizat le dezactiveaz sau le terge (le elimin). Fiecare restricie primete
un nume unic, astfel nct s poat fi referit n mesajele de eroare i n comenzile folosite ulterior n
baza de date. Este recomandabil ca proiectanii bazei de date s denumeasc restriciile, deoarece
numele generate automat de baza de date nu sunt foarte descriptive.
Exist mai multe tipuri de restricii pentru baze de date:
Restricia NOT NULL. Poate fi plasat pe o coloan pentru a mpiedica folosirea valorilor
nule. O valoare nul (null) este o modalitate special prin care sistemul RDBMS trateaz
valoarea unei coloane pentru a indica faptul c valoarea coloanei respective nu este
cunoscut. O valoare nul nu este acelai lucru cu un spaiu liber, un ir vid sau valoarea
zero este o valoare special care nu este egal cu nimic altceva.
Restricia cheie primar (primary key). Definit pe coloana (coloanele) cheie primar ale
unui tabel pentru a garanta c valorile cheie primar sunt ntotdeauna unice n ntreg tabelul.
Atunci cnd cheia primar este definit pe mai multe coloane, combinaia valorilor acelor
coloane trebuie s fie unic n tabel - o coloan care reprezint doar o parte a cheii primare
poate conine valori duplicate n tabel. Restriciile cheie primar sunt aproape ntotdeauna
implementate de RDBMS prin folosirea unui index. Indexul este un tip special de obiect al
bazei de date care permite efectuarea cutrilor rapide n valorile coloanei. Atunci cnd n
7
asigura c pk a noului rnd nu este deja folosit n tabel i, dac se ntmpl acest lucru,
respinge cererea de inserare. Cutarea n indexuri se face mult mai repede dect cutarea n
tabel; ca urmare, indexarea cheii primare este esenial pentru orice tabel, indiferent de
dimensiunea acestuia, astfel nct cutarea cheilor duplicate la fiecare inserare s nu duc la
o reducere semnificativ a performanelor. O caracteristic suplimentar a cheilor primare
este faptul c nu pot fi definite dect pe coloane pentru care a fost definit i restricia NOT
NULL.
Restricia de unicitate (unique). Definit pe o coloan sau un set de coloane care trebuie s
conin valori unice n cadrul tabelului. Ca i n cazul cheilor primare, sistemul RDBMS
folosete aproape ntotdeauna un index ca modalitate de impunere eficient a restriciei,
Totui, spre deosebire de cheile primare, un tabel poate avea definite mai multe restricii de
unicitate, iar coloanele care particip la o restricie de unicitate pot conine (n cele mai
multe sisteme RDBMS) i valori nule.
Restricia referenial (numita uneori restricie de integritate referenial).
O restricie care impune o relaie ntre dou tabele dintr-o baz de date relaional. Prin
impunere se nelege c sistemul RDBMS se asigur ntotdeauna, n mod automat, c
fiecrei valori a cheii externe i corespunde o valoare a cheii primare n tabelul printe. Pe
scurt, restricia referenial garanteaz c relaia dintre cele dou tabele i valorile
corespondente ale cheii primare i cheii externe i pstreaz logica n orice moment.
Restricia CHECK. Folosete o instruciune logic simpl (scris n SQL) pentru a valida
valoarea unei coloane. Rezultatul instruciunii trebuie s fie o valoare logic de adevrat
(true) sau fals (false), astfel nct un rezultat adevrat s permit inserarea n tabel a valorii
coloanei, iar un rezultat fals s duc la rejectarea valorii coloanei, cu mesajul de eroare
corespunztor.
4.Vizualizri
O vizualizare (view) este o interogare stocat n baza de date care pune la dispoziia
utilizatorului un subset personalizat al datelor din unul sau mai multe tabele ale bazei de date. Cu alte
cuvinte, o vizualizare este un tabel virtual, deoarece arat ca un tabel i, n cele mai multe privine, se
comport ca un tabel, dar nu stocheaz date (nu este stocat dect interogarea SQL care definete
vizualizarea). Vizualizrile au mai multe funcii utile:
Mascheaz coloanele pe care utilizatorul nu este nevoie s le vad (sau nu-i este permis s le
vad).
Mascheaz rndurile pe care utilizatorul nu este nevoie s le vad (sau nu-i este permis s le
vad).
Mascheaz operaiile complexe efectuate n baza de date, cum ar fi uniunile de tabele (respectiv
combinarea coloanelor din tabele multiple ntr-o singur interogare a bazei de date).
mbuntesc performanele interogrilor (n unele sisteme RDBMS precum Microsoft SQL
Server).
8
1.4 Proiectarea bazelor de date relaionale. Etape. Normalizarea bazelor
de date
Proiectarea unei baze de date este o activitate laborioas i necesit parcurgerea urmtoarelor
etape:
formularea problemei;
analiza cerinelor informaionale i definirea datelor de ieire i a datelor de intrare;
definirea tabelelor, a structurii acestora i a relaiilor dintre tabele;
optimizarea structurii bazei de date.
Odat ce acest proces a fost finalizat se continu cu:
proiectarea procedurilor tehnologice, pentru prelucrarea bazei de date;
elaborarea programelor;
testarea programelor;
definitivarea documentaiei.
Toate aceste activiti necesit, pentru proiectele reale complexe, o munc n echip pe baza unei
metodologii riguroase, cunoscut ca metodologia de analiz i proiectare a sistemelor informatice. n
cadrul unui sistem informatic baza de date reprezint elementul central n jurul cruia se concentreaz
celelalte componente ale sistemului.
Formularea problemei presupune stabilirea obiectivelor aplicaiei informatice care asigur
actualizarea i exploatarea bazei de date n concordan cu cerinele managementului activitii
economice pentru care este proiectat baza de date. Obiectivele unei aplicaii informatice sunt legate
de asigurarea informaional a desfurrii proceselor decizionale specifice actului de conducere. Deci,
noi trebuie s ne gndim c, prin existena unei baze de date, s asigurm fondul de informaii, ntr-o
structur i de o calitate corespunztoare cu cerinele managementului firmei. Baza de date trebuie s
permit att obinerea unor informaii de detaliu, elementare, ct i calculul i prezentarea unor
indicatori sintetici, agregai. Dac am lua doar dou obiective: reducerea costurilor i creterea
productivitii muncii ntr-o firm, atunci o baz de date trebuie s furnizeze informaii despre
consumul factorilor de producie, costurile medii i globale, despre personalul muncitor i producia
realizat, despre cheltuielile salariale etc. Aceste informaii vor servi conducerii la identificarea cilor
de reducere a costurilor i adoptarea celor mai adecvate msuri pentru reducerea acestor costuri. Dup
aplicarea msurilor n practic, informaiile stocate n baza de date trebuie s permit de data aceasta i
o analiz comparat a costurilor noi cu cele vechi, de exemplu, o analiz a dinamicii costurilor pe baza
indicilor statistici. Am ales acest mic exemplu didactic pentru a accentua nc o dat complexitatea
procesului de proiectare a bazei de date.
Analiza cerinelor informaionale, pornind de la obiectivele formulate anterior, se concentreaz
asupra a dou probleme:
indicatorii, rapoartele, listele i datele de ieire care trebuie obinute;
datele de intrare necesare pentru obinerea datelor de ieire.
Acestea sunt cerinele informaionale care nglobeaz att cerinele pentru datele de intrare pe
baza crora se creeaz i se actualizeaz baza de date, ct i cerinele pentru datele de ieire folosite
pentru urmrirea, controlul i dirijarea activitii economice. Datele de intrare se culeg, de regul, din
documentele primare care circul n cadrul fluxului informaional al firmei. Datele finale se vor integra
n ansamblul de rapoarte, liste, situaii cu rezultate pe care le furnizeaz sistemul informaional
9
compartimentelor de conducere. Pentru indicatorii inclui n rapoartele finale, n general
pentru oricare din indicatorii de ieire, trebuie s fie foarte clar modul n care sunt obinui
prin prelucrarea datelor de intrare. n consecin, acolo unde este cazul, se precizeaz
algoritmii de calcul, regulile de totalizare, sau alte reguli de obinere a fiecrei coloane, sau
totaluri din rapoartele finale.
Aceasta are ca punct de plecare inventarierea cmpurilor prezente n situaiile finale
i apoi gruparea lor n tabele. Gruparea cmpurilor pe tabele se realizeaz prin diverse
metode. Dintre aceste metode, dou sunt cele mai utilizate:
analiza concordanei IEIRI INTRRI;
analiza semnificaiei semantice a datelor.
Analiza concordanei IEIRI INTRRI este o tehnic specific proiectrii
sistemelor informatice care identific documentele primare din care se preiau datele de
intrare folosite n calculul datelor de ieire. Aceste documente vor constitui sursele de
creare i actualizare a tabelelor bazei de date.
Tehnica este util n cazul n care proiectantul bazei de date este familiarizat cu
sistemul informaional existent.
Un indicator prezent ntr-o situaie final se poate obine astfel:
prin preluarea direct din documentul primar, respectiv din tabelul bazei de date;
prin aplicarea unui algoritm de calcul.
Tabelele se compun prin gruparea cmpurilor pe principiul apartenenei acestora la
anumite documente primare care circul n cadrul sistemului informaional.
Analiza semantic se practic atunci cnd proiectantul bazei de date nu are la
dispoziie un set de documente primare i apare n cazul sistemelor informaionale care se
proiecteaz pentru firmele noi.
Definirea tabelelor i relaiilor dintre tabele este etapa urmtoare n proiectarea
bazei de date. Analiza cerinelor informaionale i a proceselor de prelucrare va conduce
la identificarea datelor ce vor trebui stocate i care vor alctui tabelele bazei de date. O
tabel va pstra datele fie despre toate caracteristicile unei colecii de date, fie numai pentru
o parte dintre aceste caracteristici. Aici intervine spiritul analitic al proiectantului.
Structura unei tabele este reprezentat de lista cmpurilor asociate tabelei mpreun cu
descrierea atributelor fiecrui cmp (natur, lungime, numr de zecimale etc.). n structura
unui tabel se regsesc urmtoarele categorii de cmpuri:
cmpuri de identificare (chei primare i chei condiionate);
cmpuri tip dat calendaristic;
cmpuri cantitativ-valorice;
cmpuri de legtur cu alte tabele;
cmpuri de stare care pstreaz informaii privind ultimele operaii de
prelucrare care au fost efectuate pe nregistrrile din tabel.
Relaiile dintre tabele se caracterizeaz prin plasarea unor cmpuri comune n
structura fiecruia dintre tabelele aflate n relaie direct. Pe baza acestor cmpuri, chei,
fiecare sistem de gestiune a bazelor de date i construiete un mecanism propriu de
accesare a nregistrrilor de date. Aceste mecanisme sunt transparente pentru utilizatorul
obinuit. Totui este bine de reinut c nu orice cmp poate fi folosit la stabilirea unei
10
relaii, a unei legturi ntre dou tabele. Numai cmpurile de tip cheie candidat, care au
proprietatea de a identifica n mod unic o nregistrare dintr-o tabel, pot fi folosite n acest
scop. Cheile candidate se mai numesc i indeci. Operaia prin care se construiete sistemul
de legturi pentru ordonarea n vederea regsirii nregistrrilor ntr-o tabel se numete
indexare. Prin indexare, fiecrei tabele principale de date i se va asocia o tabel index
corespunztoare cheilor de
11
indexare.
Optimizarea structurii bazei de date este un proces prin care se urmrete:
reducerea redundanei datelor;
eliminarea anomaliilor de actualizare.
Reducerea redundanei datelor pn la un nivel minim i controlat urmrete eliminarea
duplicrii inutile a unor cmpuri n mai multe tabele sau eliminarea cmpurilor obinute prin calcul pe
baza cmpurilor atomice. Un anumit nivel de redundan, ns, trebuie admis pentru a nu denatura
realitatea reflectat de date. De exemplu, cmpul VALOAREA_CONTRACTULUI, se calculeaz dup
relaia: VALOAREA_CONTRACTULUI=CANTITATE*PRET
Nu este recomandat eliminarea acestui cmp pe considerentul c el se obine automat prin
calcul. De exemplu, n cazul n care dup un anumit interval de timp, preurile suport o majorare
global, cum se practic foarte des, atunci automat se vor modifica i valorile contractelor ncheiate
anterior datei de majorare a preurilor ori acest lucru nu este corect, contractul odat perfectat nu-i
poate modifica preul convenit prin negociere.
Anomaliile de actualizare se refer la anomaliile de tergere, respectiv de modificare.
De exemplu, se consider o firm care deruleaz lunar mii de contracte de aprovizionare, pentru un
nomenclator foarte mare de produse agroalimentare, dar care opereaz numai cu civa furnizori. Dac n
tabela CONTRACTE sunt incluse alturi de codul furnizorului i denumirea furnizorului, contul su
bancar i denumirea bncii, atunci va aprea urmtoarea anomalie de actualizare, n cazul schimbrii
bncii i a contului bancar de ctre furnizor. Acest lucru necesit parcurgerea tuturor nregistrrilor n
care exist aceste valori i actualizarea acestora (figura 1.4).
Fig. 1.4 Eliminarea anomaliilor de actualizare
Soluia este de a crea o tabel separat, care are n structur: codul furnizorului, denumirea,
contul i banca acestuia, n tabela CONTRACTE pstrndu-se doar codul furnizorului ca element de
legtur, ceea ce previne pierderea de informaii prin spargerea unui tabel n dou. n acest fel
modificarea se realizeaz numai asupra unei singure nregistrri.
n 1972, Dr. E. F. Codd, printele bazelor de date relaionale, i-a dat seama c tabelele
relaionale care ndeplinesc anumite criterii pun mai puine probleme la inserarea, actualizarea
sau tergerea datelor. Ca urmare, a pus la punct un set de reguli care trebuie respectate (organizate
n trei
forme normale") i un proces numit normalizare, care este o tehnic pentru producerea unui set
de relaii (termenul folosit de Dr. Codd pentru tabele) cu proprietile dorite.
12
Necesitatea normalizrii
Figura 1.5 prezint tabelul MOVIE fr normalizare, aa cum ar arta dac toate informaiile
despre filme ar fi colectate ntr-un singur tabel. Acest exemplu va fi folosit pentru ilustrarea procesului
de normalizare.
n general, numele coloanelor din tabelele relaionale folosesc liniue de subliniere pentru
separarea cuvintelor. n discuia despre normalizare am eliminat aceste liniue din figuri, pentru a face
textul mai uor de citit.
Exist trei probleme care pot aprea n tabelele fr normalizare din bazele de date relaionale.
Scopul procesului de normalizare este de a elimina aceste probleme (anomalii) din proiectul bazei de
date. Acestea sunt:
- Anomalia de inserare
- Anomalia de tergere
- Anomalia de actualizare
Anomalia de inserare
Anomalia de inserare se refer la o situaie n care nu putei insera date n baza de date din cauza
unei dependene artificiale dintre coloanele unui tabel.
Anomalia de tergere
Anomalia de tergere este inversul anomaliei de inserare. Se refer la situaia n care tergerea
unor date duce la pierderea neintenionat a altor date.
Anomalia de actualizare
Anomalia de actualizare se refer la o situaie n care actualizarea unei singure valori necesit
actualizarea mai multor rnduri. Un alt pericol legat de aceast anomalie este faptul c stocarea unor
date redundante poate duce la posibilitatea de a actualiza numai o parte a copiilor respectivelor date,
ceea ce ar avea ca rezultat apariia inconsecvenelor n baza de date.
Aplicarea procesului de normalizare
De obicei, normalizarea ncepe de la mijloacele de redare a datelor care sunt (sau vor fi)
prezentate utilizatorilor, cum ar fi pagini web, ecrane ale aplicaiilor, rapoarte i aa mai departe.
Colectiv, acestea sunt numite vizualizri de utilizator (user views). Poate prea ciudat la prima vedere,
dar este ceva obinuit ca proiectarea unui sistem de prelucrare a datelor s nceap de la rezultatele pe
care le va vedea utilizatorul, parcurgnd apoi drumul napoi ctre mijloacele folosite pentru obinerea
rezultatelor dorite.
n timpul proiectrii bazei de date, procesul de normalizare este aplicat fiecrei vizualizri, iar
rezultatul este un set de relaii normalizate care pot fi apoi direct implementate ca tabele ale bazei de
date relaionale. Procesul n sine este destul de simplu, iar regulile nu sunt foarte dificile. Totui,
stpnirea procesului de normalizare cere timp i exerciiu, n special deoarece impune proiectantului
s se gndeasc ntr-un mod conceptual la datele i relaiile pe care intenioneaz sa le foloseasc.
n timpul normalizrii, considerai c fiecare vizualizare este o relaie. Cu alte cuvinte,
conceptualizai fiecare vizualizare ca i cum ar fi deja un tabel bidimensional - un mod de lucru pentru
care avei nevoie de experien.
Reinei c scopul procesului de normalizare este eliminarea anomaliilor de inserare, actualizare
i tergere. Procesul determin crearea unui numr mai mare de relaii dect ai avea ntr-un model fr
13
normalizare. Relaiile suplimentare sunt necesare pentru eliminarea anomaliilor, dar mprirea datelor
n mai multe relaii face ca extragerea datelor stocate s fie puin mai dificil. De fapt, sacrificai o
parte din performanele de extragere a datelor i din uurina utilizrii pentru ca operaiile de inserare,
actualizare i tergere s fie mai simple.
Alegerea unul identificator unic
Primul pas al procesului de normalizare const n alegerea unui identificator unic (unique
identifier), care este un atribut (o coloan) sau un set de atribute care identific n mod unic fiecare
rnd de date dintr-o relaie.
Identificatorul unic va deveni ulterior cheia primar a tabelului creat din relaia normalizat.
Pentru normalizare, este obligatoriu ca fiecare relaie s aib un identificator unic, n multe cazuri,
putei gsi un atribut care identific n mod unic datele din fiecare rnd al relaiei pe care vrei s o
normalizai. Atunci cnd nu putei gsi un singur atribut care s poat fi folosit ca identificator unic,
este posibil s gsii mai multe atribute care pot fi concatenate (combinate) pentru a forma un
identificator unic. Atunci cnd identificatoarele unice sunt formate din atribute multiple, fiecare atribut
rmne pe propria lui coloan - nu facei dect s definii un identificator unic format din mai multe
coloane.
n foarte puine cazuri, ntr-o relaie nu exist un set rezonabil de atribute care s poat fi folosit
ca identificator unic. Atunci cnd se ntmpl acest lucru, trebuie s inventai un identificator unic,
deseori cu valori atribuite secvenial sau aleatoriu pe msur ce noile rnduri de date sunt adugate n
tabelul bazei de date. Aceast tehnic este sursa unor identificatoare unice, precum numrul de
asigurri sociale folosit n Statele Unite, numerele de identificare ale angajailor, numerele de
nmatriculare ale mainilor sau CNP.
Prima form normal: eliminarea datelor repetate
O relaie este n prima form normal atunci cnd nu conine atribute cu valori multiple (atribute
multivaloare), adic atribute care au mai multe valori pentru acelai rnd de date. ntr-o relaie, orice
intersecie a unui rnd cu o coloan trebuie s conin cel mult o valoare pentru ca relaia s fie n
prima form normal.
Pentru transformarea relaiilor ne-normalizate n prima form normal, trebuie mutate atributele
multivaloare i grupurile repetitive n noi relaii.
Procedura de mutare a unui atribut multivaloare sau a unui grup repetitiv ntr-o nou relaie
const n urmtoarele etape:
1. Creai o nou relaie, cu un nume sugestiv. Deseori, este bine s includei numele relaiei
originale, parial sau n ntregime, n numele noii relaii.
2. Copiai identificatorul unic din prima relaie n noua relaie. Datele depind de acest
identificator n relaia original, aa c trebuie s depind de aceeai cheie i n noua relaie.
Identificatorul copiat va deveni cheie extern n noua relaie.
3. Mutai grupul repetitiv sau atributul multivaloare n noua relaie.
4. Formai un identificator unic n noua relaie, adugnd atribute la identificatorul unic copiat
din relaia original. Ca ntotdeauna, asigurai-v ca identificatorul unic nou format conine numai
numrul minim de atribute necesar pentru a-1 face unic. Dac mutai un atribut multivaloare, care, n
esen, este un grup repetitiv cu un singur atribut, este adugat atributul respectiv pentru formarea
identificatorului unic. Poate prea ciudat la prima vedere, dar identificatorul unic copiat din relaia
original nu este doar o cheie extern, ci, de obicei, i o parte a identificatorului unic (cheia primar) a
14
noii relaii. Acest lucru este absolut normal. De asemenea, este perfect acceptabil s avem o
relaie n care toate atributele fac parte din identificatorul unic (adic nu exist atribute care s
nu fac parte din cheie).
5. Opional, putei s nlocuii cheia primar cu un singur atribut surogat pentru
cheie. Dac facei acest lucru, trebuie s pstrai i atributele care compun cheia primar
natural, format la paii 2 i 4.
A doua form normal: eliminarea dependenelor pariale
nainte de a explora a doua form normal, trebuie s nelegem conceptul de dependen funcional.
Pentru aceast definiie, vom folosi dou atribute arbitrare, inteligent denumite A" i
B". Atributul B este dependent funcional de atributul A dac n nici un moment nu exist
mai mult de o valoare a atributului B asociat cu o valoare dat a atributului A.
Se spune c o relaie este n a doua form normala dac ndeplinete urmtoarele criterii:
Relaia este n prima form normal.
Toate atributele non-cheie sunt dependente funcional de identificatorul unic
(cheia primar), luat ca ntreg.
n esen, amestecm atribute care descriu n aceeai relaie dou lucruri (entiti) diferite
(dei nrudite) din lumea real. Nici nu e de mirare c am obinut un asemenea haos. A doua
form normal ne va ajuta s rezolvm problemele.
A doua form normal se aplic numai relaiilor care au identificatoare unice concatenate
(adic formate din atribute multiple). ntr-o relaie care are un singur atribut ca identificator
unic, este imposibil ca un alt atribut s depind de o parte a identificatorului unic, deoarece
acesta, fiind format dintr-un singur atribut, nu are pri componente. Ca urmare, orice relaie
n prima form normal care are cheia primar format dintr-un singur atribut este
automat n a doua form normal.
A treia form normal: eliminarea dependenelor tranzitive
Pentru a nelege a treia form normal, trebuie s nelegem mai nti conceptul de dependen tranzitiv.
Despre un atribut care depinde de un atribut care nu este identificator unic (cheie
primar) a relaiei se spune c este dependent tranzitiv.
Se spune c o relaie este n a treia form normal dac ndeplinete urmtoarele dou criterii:
Relaia este n a doua form normal.
Nu exist dependene tranzitive (cu alte cuvinte, toate atributele non-cheie depind
numai de identificatorul unic).
Pentru a aduce la a treia form normal o relaie aflat n a doua form normal,
mutm atributele dependente tranzitiv n relaii n care depind numai de cheia primar Avem
grij s lsm atributul de care depind acestea n relaia original, cu rolul de cheie extern. Va
trebui apoi s reconstruim vizualizarea original printr-o uniune. Ca efect secundar, toate
15
atributele uor de calculat sunt eliminate ca nclcri ale criteriilor celei de-a treia forme
normale.De exemplu, ntr-o baz de date pentru vnzri, Suma Total este obinut nmulind
Cantitatea Cumprat cu Preul Unitar; aa cum se observ cu uurin, Suma Total
este dependent de Cantitatea Cumprat i de Preul Unitar. Presupunnd c toate cele trei
atribute sunt dependente de identificatorul unic al relaiei care le conine, este uor de vzut
c Suma Total (rezultatul calculat)este, de fapt, dependent tranzitiv de celelalte dou
atribute.
16
LLiimmbbaajjuull SSQQLL(( SSttrruuccttuurreedd QQuueerryy LLaanngguuaaggee ))
CCaarraacctteerriissttiiccii ggeenneerraallee
Limbajul SQL este un limbaj declarativ; utilizatorul descrie informaiile pe care vrea s le obin n urma
interogrii, fr a preciza algoritmii necesari pentru obinerea rezultatelor dorite. n 1986 SQL a devenit
standard ANSI (SQL-86). n ultimii ani, comitetele ANSI i ISO pentru standardizarea limbajului SQL au
adugat noi faciliti pentru gestiunea bazelor de date orientate pe obiecte i obiect-relaional. S-a ajuns astfel
la o versiune SQL3, pe cale de a deveni un standard n domeniu. Aceste faciliti se pot structura:
faciliti orientate obiect ce propun definirea la nivel de utilizator a tipurilor de date abstracte;
structuri de control specifice: IF, FOR, WHILE ce permit transformarea SQL-ului ntr-un limbaj de sine
stttor;
faciliti de comunicare n reea;
faciliti de prelucrare distribuit;
faciliti multi-media, nglobate n modulul Multi - Media SQL.
Tipuri de instruciuni
n funcie de rolul lor n manipularea datelor i tranzaciilor, instruciunile SQL, pot fi grupate astfel:
a) instruciuni de definire a datelor care permit descrierea structurii bazei de date;
b) instruciuni de manipulare a datelor n sensul adugrii, modificrii i tergerii nregistrrilor;
c) instruciuni de selecie a datelor care permit consultarea bazei de date;
d) instruciuni de procesare a tranzaciilor care privesc unitile logice de prelucrare i constituie n fapt,
operaii multiple de manipulare a datelor;
e) instruciuni de control al cursorului;
f) instruciuni privind controlul accesului la date.
Instruciuni pentru selectarea datelor- formatul frazei SELECT
SSEELLEECCTT [[AALLLL//DDIISSTTIINNCCTT//DDIISSTTIINNCCTTRROOWW]] lliisstt aattrriibbuuttee
FFRROOMM lliisstt--ttaabbeellee [[WWHHEERREE ccrriitteerriiuu--ddee--ccuuttaarree]]
[[GGRROOUUPP BBYY ccrriitteerriiuu--ddee--ggrruuppaarree]] [[HHAAVVIINNGG
ccrriitteerriiuu--ddee--ggrruuppaarree]] [[OORRDDEERR BBYY ccrriitteerriiuu--ddee--oorrddoonnaarree [[AASSCC||DDEESSCC]]]];;
Unde:
lista-atribute specific atributele ale cror valori vor fi returnate; FROM list-tabele specific tabelele din care se vor extrage datele;
WHERE permite prin precizarea unei expresii exprimarea
criteriului de selecie; ORDER BY precizeaz atributul dup care se va face ordonarea; GROUP BY folosit pentru a partiiona o tabel n grupuri acordnd
acestora valori pe un atribut sau list de atribute; HAVING specific criterii de selecie pe grupuri de tupluri.
Toate rindurile tabelei Produse:
SELECT DISTINCTROW * FROM PRODUSE;
Mumele i preul produselor avnd preul mai mare de 10000:
SELECT DISTINCTROW [DEN PROD], PRE FROM PRODUSE WHERE PRE>10000;
17
n cazul n care dorii s cunoatei denumirea produselor cu preuri ntre 19000 i 110000 lei putei fie s
introducei o expresie compus n clauza WHERE, fie s utilizai operatorul between:
SELECT [DEN PROD] FROM PRODUSE WHERE PRE >=19000 AND PRE= (SELECT PRE FROM PRODUSE WHERE [DEN PROD]=MERE);
Utilizarea predicatelor ALL, ANY, EXIST
Domeniul de obinere a rezultatelor unei subinterogri poate fi influenat prin precizarea unuia din cuvintele
cheie: ALL, ANY i respectiv EXIST.
ALL
Se preiau rezultatele subinterogrii i, dac acestea ndeplinesc condiia cerut, se returneaz valoarea logic
True.
Exemplu :
Dac dorim s cunoatem numele produselor de calitatea nti cu preul mai mic dect al tuturor produselor
de calitatea a 2-a cererea va avea urmtoarea form:
SELECT [DEN PROD] FROM PRODUSE
18
WHERE CALITATEA=1AND PRE< ALL (SELECT PRE FROM PRODUSE WHERE CALITATEA=2);
ANY
Are n vedere compararea valorii de ieire a subinterogrii cu fiecare nregistrare din interogarea extern.
Dac pentru fiecare nregistrare din interogare exist un rezultat al subinterogrii, se va returna valoarea
logic True. Spre deosebire de clauza IN poate fi folosit cu diveri operatori relaionali. Cuvntul cheie
SOME are acelai rol i caracteristici ca ANY.
Exemplu:
S se returneze lista crilor din bibliotec care aparin unuia din domeniile abordate de editura ABC n anul
2000:
SELECT TITLUL_CARTII, DOMENIUL, ANUL_APARITIEI, PRET
FROM CRI WHERE DOMENIUL= ANY (SELECT DOMENIUL FROM CRI WHERE EDITURA=ABC AND [ANUL APARIIEI]=2000);
nlocuirea lui ANY cu SOME produce un rezultat identic:
SELECT TITLUL_CARTII, DOMENIUL, ANUL_APARITIEI, PRE FROM CARTI
WHERE DOMENIUL= SOME (SELECT DOMENIUL FROM CARTI WHERE DOMENIUL
="INFORMATICA");
EXISTS
Folosete subinterogarea ca pe o condiie, analiznd setul de rezultate al acesteia i returnnd valoarea False
dac nu exist nici o ieire. Se poate astfel verifica existena anumitor nregistrri i controla ansamblul
rspunsurilor date de interogare.
Exemplu:
S se verifice dac n cadrul bibliotecii exist i cri aprute nainte de anul 2001:
SELECT TITLUL_CRII, ANUL_APARIIEI FROM CRI WHERE EXISTS (SELECT * FROM CRI WHERE ANUL_APARIIEI < 2001);
Fiecare dintre cuvintele cheie ANY, ALL i EXISTS poate s fie utilizat pentru restricionarea interogrilor i n
forma cu negaie adic prin folosirea operatorului logic NOT.
Interogri folosind operatorul UNION
Cnd utilizatorul dorete s vad rezultatele mai multor interogri SELECT n acelai timp, prin combinarea
ieirilor lor, poate utiliza UNION:
SSEELLEECCTT lliissttaa__ccaammppuurrii FFRROOMM ttaabbeellaa11
UUNNIIOONN SSEELLEECCTT lliisstt__ccaammppuurrii FFRROOMM ttaabbeellaa22
[[GGRROOUUPP BBYY ccaammpp__ddee__ggrruuppaarree]] [[HHAAVVIINNGG
ccrriitteerriiuull__ddee__aaggrreeggaarree]]
[[UUNNIIOONN SSEELLEECCTT lliisstt__ccaammppuurrii FFRROOMM ttaabbeellaa33
[[GGRROOUUPP BBYY ccaammpp__ddee__ggrruuppaarree ]] [[HHAAVVIINNGG
ccrriitteerriiuull__ddee__ggrruuppaarree]]]]
[[UUNNIIOONN......]]
[[OORRDDEERR BBYY ccaammpp__ccrriitteerriiuu__ddee__ssoorrttaarree]];;
Exist mai multe restricii pentru instruciunile care genereaz interogri UNION i anume: numrul de
cmpuri din lista de cmpuri asociat fiecrei instruciuni SELECT i UNION SELECT trebuie s fie
acelai; este permis doar o dat utilizarea clauzei ORDER BY, dup ultima instruciune UNION SELECT;
secvena de nume din fiecare list de cmpuri trebuie s corespund unor intrri identice. Cnd se folosete
UNION, automat se vor elimina duplicatele ce apar n urma combinrii. In cazul folosirii domeniului ALL se
vor lua n considerare i valorile duplicate.
Exemple:
a) Care a fost media salariilor colaboratorilor studeni i ingineri ?
SELECT CATEGORIE, AVG(SALARIU) AS MEDIE FROM COLABORATORI
19
GROUP BY CATEGORIE
HAVING CATEGORIE = "STUDENT"
UNION SELECT CATEGORIE, AVG(SALARIU) AS MEDIE FROM COLABORATORI
GROUP BY CATEGORIE
HAVING CATEGORIE = "ING";
b) Fie tabelele Clieni (cod, nume, localitate) i Furnizori (cod, nume, localitate). Prin utilizarea operatorului
UNION putem obine lista comun a clienilor i furnizorilor:
SELECT * FROM CLIENI UNION SELECT * FROM FURNIZORI
ORDER BY NUME;
Instruciuni pentru actualizarea bazei de date
Pentru inserarea unui tuplu ntr-o tabel se utilizeaz comanda INSERT:
IINNSSEERRTT IINNTTOO nnuummee--ttaabbeellaa [[((nnuummee--aattrriibbuutt,,))]] {{VVAALLUUEESS((vvaallooaarree,,))||cceerreerree}}
Lista de atribute se specific n cazul n care realizrile din clauza VALUES nu corespund ordinii tuplurilor
din tabel. Dac tuplul/tuplurile inserate sunt preluate dintr-o alt tabel se va specifica comanda Select.
Exemple: Se introduce un nou tuplu n tabela Produse (avnd n structura sa atributele: cod produs,
denumire, unit. msur, pre, calitate) :
INSERT INTO PRODUSE VALUES (345,PERE,KG,12000,1);
sau:
Se introduc tupluri n tabela Produse sursa de date constituind-o tabela Nouti (care prezint aceeai
structur cu tabela Produse):
INSERT INTO PRODUSE
SELECT * FROM NOUTTI;
Actualizarea datelor dintr-o tabel se realizeaz prin comanda UPDATE care prezint urmtoarea sintax:
UPDATE nume-tabela
SET {nume-atribut=expresie,| (nume-atribut=subcerere),} [WHERE condiie];
Noua realizare a atributului actualizat se precizeaz prin expresie sau este returnat de subcererea precizat.
Clauza WHERE permite selectarea tuplurilor afectate de actualizare.
Exemplu:
Se majoreaz cu 1000 preul produselor de categoria a doua :
UPDATE PRODUSE
SET PRE=PRE+500 WHERE CALITATE=2;
GGeessttiiuunneeaa vviieeww--uurriilloorr
Un view este o tabel virtual, care nu conine date, dar i confer utilizatorului posibilitatea de vizualizare a
datelor din baza de date dintr-o anumit perspectiv. Pentru definirea unui view se folosete o cerere SQL.
View-urile pot fi folosite n urmtoarele scopuri:
limiteaz accesul unor categorii de utilizatori la anumite date, mai mult sau mai puin confideniale;
permite simplificarea viziunii utilizatorilor asupra bazei de date;
asigur securitatea i confidenialitatea datelor. Fiecrui utilizator al bazei de date i sunt asignate anumite
drepturi (GRANT/REVOKE) ceea ce determin o limitare a accesului la baza de date, la obiectele bazei
de date i la efectuarea unor operaii asupra obiectelor bazei de date.
Asupra unui view se pot efectua ulterior aceleai operaii ca i asupra unei tabele, dar cu deosebirea c view-
ul nu conine date, ci este o reflectare dinamic a unei tabele. Astfel, o modificare a datelor dintr-o tabel este
automat reflectat n view-urile definite pe acea tabel. Un view se recreeaz de fiecare dat cnd este apelat,
pe baza definiiei sale stocate n dicionarul bazei de date.
20
Operaiile aplicate asupra view-urilor sunt:
crearea unui view cu comanda CREATE VIEW;
interogarea view-urilor - prin folosirea unei fraze SELECT;
actualizarea datelor dintr-un view folosind comanda UPDATE;
adugarea de noi date folosind comanda INSERT; tergerea datelor dintr-un view cu comanda DELETE.
Crearea unui view - permite definirea unei "ferestre" prin care se pot consulta datele stocate n tabele.
CCRREEAATTEE VVIIEEWW nnuummee--vviieeww [[]] AASS SSEELLEECCTT
sseeccvveennaa--sseelleecctt
[[WWIITTHH CCHHEECCKK OOPPTTIIOONN]]
Se poate observa n sintaxa comenzii c utilizatorului i se permite:
specificarea denumirii view-ului i opional a denumirii atributelor din view, n cazul n care se dorete
redenumirea atributelor specificate n instruciunea SELECT;
specificarea interogrii (fraza SELECT);
specificarea opional a unei condiii suplimentare impuse view-ului, astfel nct s poat fi realizat
actualizarea sau inserarea datelor n view (prin utilizarea clauzei WITH CHECK OPTION).
Definirea unui view este stocat n dicionarul de date al sistemului. Odat definit view-ul, drepturile de
acces ale utilizatorilor decurg pe de o parte, din privilegiile acordate utilizatorilor asupra relaiilor pentru care
este definit view-ul i, pe de alt parte, din posibilitatea de actualizare a view-ului.
Crearea unui view simplu, care va permite selectarea unei mulimi de tupluri dintr-o tabel, se realizeaz prin
comanda:
CREATE VIEW FZ1 AS
SELECT CODFZ, DENFZ, ADRESA FROM FURNIZOR WHERE ADRESA="BUCURESTI";
VViieeww ccrreeaatteedd..
Sursa de date o reprezint tabela Furnizor iar rezultatul returnat const n afiarea codului, denumirii i
adresei furnizorilor bucureteni. Cnd un view este creat, e afiat un mesaj "View Created". Ulterior, view-ul
poate fi vizualizat ca orice tabel:
SELECT * FROM FZ1;
unde FZ1 reprezint view-ul creat prin comanda anterioar.
Crearea unui view complex presupune utilizarea unor funcii agregat n fraza Select:
CREATE VIEW STOC_3
(DEPOZIT, STOC_MEDIU, STOC_MAX, STOC_MIN) AS
SELECT COD_DEP, AVG(CANTITATE), MAX(CANTITATE), MIN(CANTITATE) FROM STOCURI
GROUP BY COD_DEP;
n acest caz coloanele view-ului au primit nume distincte de numele atributelor existente n tabela Stocuri,
atribuirea valorilor fcndu-se n ordinea definirii atributelor n view.
n urmtorul exemplu se exemplific folosirea mai multor fraze SELECT imbricate, rezultatul returnat
reprezentnd clienii care au solicitat comenzi depozitelor alimentare:
CREATE VIEW CLIENT_A
AS SELECT * FROM CLIENT
WHERE CODCLI IN (SELECT CODCLI FROM COMANDA
WHERE CODDEP IN (SELECT CODDEP FROM DEPOZIT
WHERE DENDEP LIKE
"*ALIM*"));
Utilizarea opiunii WITH CHECK OPTION
Aceast opiune asigur faptul c n cazul unei operaii INSERT sau UPDATE - nu va fi adugat sau
actualizat nici un tuplu care nu se ncadreaz n condiiile specificate la definirea view-ului.
Exemplu:
CREATE VIEW SAL1
AS SELECT MARCA, NUME, SAL FROM SALARIAT
WHERE SAL BETWEEN 1500000 AND 3000000
WITH CHECK OPTION;
21
Asupra view-urilor pot fi efectuate aceleai operaii ca asupra tabelelor pe baza crora au fost definite, cu
unele restricii:
Interogarea view-urilor
Restricii pentru interogarea view-rilor:
a) Clauza WHERE nu poate conine un atribut rezultat din aplicarea unei funcii agregat.
CREATE VIEW DEP1(DEPOZIT, TOTAL)
AS SELECT COD_DEP, SUM(CANTITATE) FROM STOCURI GROUP BY COD_DEP;
Nu se poate specifica atributul TOTAL al vizualizrii DEP1 n clauza WHERE a unei interogri de tipul:
SELECT * FROM DEP1 WHERE TOTAL>300;
Atributul TOTAL poate fi apelat numai n clauza ORDER BY.
SELECT DEPOZIT, TOTAL FROM DEP1 ORDER BY TOTAL;
b) Un atribut al view-ului rezultat n urma aplicrii unei funcii agregat, nu poate fi utilizat ulterior ca
argument al unei alte funcii agregat. Nu va putea fi executat o cerere de forma :
SELECT AVG(TOTAL) FROM DEP1;
RReeaalliizzaarreeaa ooppeerraattoorriilloorr rreellaaiioonnaallii ffoolloossiinndd lliimmbbaajjuull SSQQLL
REUNIUNEA a dou tabele R i S (RUS) prezentnd aceeai structur, unde R este format din n tupluri i
S este format din m tupluri, are ca rezultat o a treia tabel, T avnd aceeai structur cu a tabelelor surs i
coninnd m+n tupluri.
Fie cele dou tabele Clienti i ClientiNoi (tabela ClientiNoi cuprinde noii clieni creai n cursul zilei curente)
definite cu aceeai structur. Operatorul reuniune se realizeaz n Access prin dou cereri de aciune: o
cerere de tip make-table prin care se creeaz noua tabel (numit T n cadrul definiiei reuniunii) TotiClientii,
pe baza tabelei Clienti, i o cerere de aciune de tip append-query pe baza tabelei ClientiNoi.
Comenzile SQL corespunztoare celor dou cereri de aciune sunt:
SELECT CLIENTI.* INTO TOTICLIENTII FROM CLIENTI;
INSERT INTO TOTICLIENTII
SELECT CLIENTINOI.* FROM CLIENTINOI;
O a doua soluie ar consta n utilizarea operatorului union care va returna toate tuplurile din cele dou tabele
surs, Clieni i ClientiNoi, i apoi generarea tabelei TotiClientii care s stocheze toate tuplurile returnate de
operatorul union. Frazele SELECT corespunztoare sunt:
SELECT * FROM CLIENTI
UNION
SELECT * FROM CLIENTINOI;
SELECT UNION.* INTO TOTICLIENTII FROM [UNION];
INTERSECIA a dou tabele R i S (RS) prezentnd aceeai structur, genereaz o a treia tabel, T (cu
aceeai structur), coninnd tuplurile identice aparinnd att lui R ct i lui S.
Pentru exemplificare vom utiliza tabelele Clienti i ClientiDebitori care prezint aceeai structur.
Implementarea operatorului intersecie se face printr-o fraz SELECT n care se realizeaz un inner join
ntre cele dou tabele surs, Clienti i ClientiDebitori:
SELECT Client.*
FROM Clienti INNER JOIN ClientiDebitori ON Clienti.CodClient=ClientiDebitori.CodClient;
Pentru crearea tabelei T se poate executa apoi o cerere de aciune de tip make table avnd drept surs de date
cererea construit prin fraza select prezentat mai sus (cerere salvat cu numele Query1):
SELECT Query1.* INTO T FROM Query1;
DIFERENA dintre dou tabele R i S avnd aceeai structur (R-S), este reprezentat de tabela T
cuprinznd mulimea tuplurilor aparinnd lui R dar neaparinnd lui S.
Implementarea operatorului diferen se realizeaz construind o cerere de cutare a tuplurilor din tabela
22
Clienti fr corespondent n tabela ClientiDebitori. Fraza SQL corespunztoare este:
SELECT DISTINCTROW [CLIENTI].[CodClient], [CLIENTI].[NumeClient], [CLIENTI].[Localitate],
[CLIENTI].[Adresa], [CLIENTI].[Telefon] FROM CLIENTI LEFT JOIN ClientiDebitori ON [CLIENTI].[CodClient] = [ClientiDebitori].[CodClient]
WHERE [ClientiDebitori].[CodClient] Is Null;
Pentru crearea tabelei T se poate construi apoi o cerere de aciune de tip make table avnd ca surs de date
cererea prezentat mai sus.
PRODUSUL CARTEZIAN a dou tabele R i S (RxS) este reprezentat de tabela T stocnd mulimea
perechilor obinute prin concatenarea nregistrrilor aparinnd lui R cu cele aparinnd lui S.
Pentru exemplificare vom folosi tabela Clienti i tabela Produse. Vom construi o cerere de selecie avnd
drept surs de date cele dou tabele (nelegate ntre ele) i vom cobor n grila de proiectare n rndul FIELD
atributele NumeClient din tabela Client i Denumire din tabela Produse. Fraza SELECT corespunztoare
acestei cereri de selecie este:
SELECT CLIENTI.NUMECLIENT, PRODUSE.DENUMIRE FROM CLIENTI, PRODUSE;
Tabela T se poate crea apoi printr-o cerere de aciune de tip make table.
SELECIA aplicat asupra unei tabele R stocnd n tupluri genereaz o nou tabel R1, prezentnd aceeai
structur, dar stocnd m tupluri (m
23
asociat se execut o comand insert, update sau delete. Declanatoarele sunt medii prin care SQL ofer
programatorilor de aplicaii i proiectantilor de BD s asigure integritatea BD.
Declanatoarele sunt utile deoarece impun reguli far a fi cuprinse n aplicaiile utilizatorilor.
Proiectantul declanatorului specific i momentul cnd trebuie executat n raport cu instruciunile LMD.SQL
Server ia n seama regulile i valorile implicite nainte ca informaiile s fie scrise n BD.
Acestea reprezint pre-filtre care pot s impiedice manipulrile de date, prin controlarea activitilor din cadrul
BD. Un declanator poate fi i un post-filtru care se execut dup ce modificarea datelor a trecut de reguli.
Declanatorii permit s executm o procedur rezident ori de cte ori este executat o instruciune
INSERT, UPDATE sau DELETE asupra unui tabel predefinit. Declanatorul este o procedur special stocat
care:
- genereaz automat anumite valori ce deriv din valorile coloanelor;
- determin respectarea restriciilor i privilegiilor ;
- face posibil jurnalizarea transparent a evenimentelor;
- strnge informaii statistice n legtur cu accesarea tabelelor.
Un declanator este o procedur stocat special care este executat de SQL Server la efectuarea unei
operaii de inserare, modificare sau stergere. Pentru c declanatorii sunt executai dup efectuarea operaiei de
inserare, modificare sau tergere ei reprezint un fel de ultim cuvnt la acestea. Dac un declanator respinge
cererea, modificarea informaiilor este refuzat, iar aplicaia care a iniiat operaia deine un mesaj de eroare. Cea
mai simpl utilizare este determinat de impunerea regulilor de integritate n BD.
Observaie: Deoarece declanatoarele sunt executate dup regulile de integritate dac acestea nu sunt
trecute atunci declanatorul nu este executat. Pentru ca un declanator s fie executat trebuie ca operaia n
cauz s nu fi euat.
Componentele unui declanator
Un declanator are trei componente:
- o instruciune de declarare aceasta specific instruciunile SQL care activeaz
declanatorul;
- o restricie de declanare specific condiia care trebui s fie adevarat pentru ca declanatorul s fie
activat;
- aciunea declanatorului care specific blocul de instruciuni care trebuie executate.
Pentru a crea un declanator trebuie s fii posesorul BD. Atunci cnd se adaug un declanator pentru o
coloana, linie sau tabel se schimb uneori i modul de accesare i modul cum interacioneaz alte obiecte cu
acesta. La folosirea obiectelor declanator se presupune respectarea condiiilor:
- Declanatoarele nu pot fi create pentru tabele temporare,
- Declanatoarele trebuie s fie create numai pentru tabele din baza de date curent,
- La eliminarea unui tabel, toate obicetele declanator asociate cu acest tabel sunt,
eliminate automat mpreun cu tabelul.
Crearea unui declanator se realizeaz cu instruciunea:
24
CREATE TRIGGER nume_declanator
ON { Nume_ tabel | Nume_ view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ] AS
[ { IF UPDATE ( coloana )
[ { AND | OR } UPDATE ( coloana ) ] [ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ operator de comparare } coloana_bitmask [ ...n ]
} ]
Instruciuni_SQL [ ...n ]
}
}
Sau urmtoarea form mai simpl
CREATE TRIGGER [posesor.]nume_declanator
ON [posesor.]nume_tabela
FOR {INSERT, UPDATE, DELETE } [WITH
ENCRYPTION]
AS
Instruciuni SQL
Nume_declanator este numele declanatorului (trigger-ului). Un nume de declanator trebuie s
respecte regulile pentru identificatori i trebuie s fie unic ncadrul unei baze de date.
Nume_ tabel | Nume_ view este numele tabelului sau vederii n care declanatorul este
executat.
INSERT, UPDATE, DELETE acestea definesc scopul declanatorului i specific
operaiile care iniiaz declanatorul.
WITH ENCRYPTION aceast opiune este utilizat pentru a mpiedica ca utilizatorii s citesc definiia
declanatorului dup ncrcarea lui pe server. SQL Server stocheaz definiia unui declanator n fiierul
Syscomment. Cripteaz intrrile n tabela syscomments care conine textul comenzii CREATE TRIGGER.
AFTER specific faptul c declanatorul este executat doar cnd toate operaiile din enunul SQL al
declanatorului au fost execuate cu succes. Toate aciunile refereniale n cascad i verificrile de constrngere
25
deasemenea trebuie s reuseasc nainte de executarea acestui trigger. Folosirea clauzei AFTER este identic
cu folosirea clauzei
26
FOR utilizat mai mult n ultimele versiuni de SQL Server. Declanatorii AFTER nu pot fi definii pentru
vederi.
INSTEAD OF Specific faptul c declanatorul este executat n locul enunului SQL
declanat, astfel suprapunnd operaiile din enunul declanatorului.
Acest tip de declanator poate fi definit pentru un tabel sau pentru o vedere cnd se realizeaz;
INSERT, UPDATE sau DELETE.
Este posibil s definim vederi unde fiecare vedere s aib propriul declanator
INSTEAD OF.
{ [DELETE] [,] [INSERT] [,] [UPDATE] } Sunt cuvinte cheie care specific pentru ce operaie are loc
declanatorul. Este obligatorie apariia cel puin a unei opiuni.
Pentru declanatori INSTEAD OF, opiunea DELETE nu este permis pentru tabelele care au
legturi refereniale ce specific o aciune ncascad pentru opiunea ON DELETE. Asemntor,
opiunea UPDATE nu este permis pe tabele care au legturi refereniale ce specific o aciune
ncascad pentru opiunea ON UPDATE. Instruciuni_sql Reprezint condiia (condiiile) i aciunea
(aciunile) declanatorului. Aciunile declanatorului sunt scrise n Transact-SQL i pot cuprinde oricte
instrucini sau comenzi Transact-SQL.
Cteva tabele speciale, de care am mai amintit, sunt folosite n instruciunea CREATE TRIGGER i
anume: inserted i deleted.
IF UPDATE (coloana)Testeaz aciunea unei comenzi INSERT sau UPDATE pentru coloana specificat
i nu este folosit pentru DELETE. Mai multe coloane pot fi specificate. Deoarece numele tabelului este
specificat dup clauza ON, nu este nevoie s includem numele tabelului nainte de numele coloanei n
clauza IF UPDATE. Pentru a testa o adugare sau o modificare pentru mai multe coloane, specificm
separat clauza UPDATE(coloana) dup ce am scris-o pe prima. IF UPDATE va returna valoarea TRUE n
aciunea INSERT deoarece coloanele vor fi explicit sau implicit (NULL) inserate.
Nota Clauza IF UPDATE (coloana) functioneaz identic cu IF, IF...ELSE sau WHILE
poate folosi i blocul BEGIN...END.
UPDATE(coloana) poate fi folosit oriunde n corpul declanatorului.
Coloana este numele unei coloane pentru testarea aciunii INSERT sau UPDATE. Aceast coloan
poate avea orice tip de date SQL Server.Oricum coloanele calculate nu pot fi utilizate n acest context.
IF (COLOANAS_UPDATED()) Testeaz, dac ntr-un declanator INSERT sau UPDATE,
coloanele menionate au fost inserate respectiv modificate.
COLOANAS_UPDATED returneaz un tip varbinary (tip binar variabil) ce indic ce coloane din tabel
au fost inserate sau modificate.
Funcia COLOANAS_UPDATED returneaz biii n ordine de la stanga la dreapta, cu cel mai
puin semnificativ bit n partea cea mai din stnga. Cel mai din stnga bit reprezentnd prima coloana
din tabel, urmtorul bit reprezentnd cea de-a dou coloan etc. COLOANAS_UPDATED returneaz
mai muli octei pentru declanatorul care a fost creat coninnd mai mult de 8 coloane, cu cel mai
27
puin semnificativ byte n partea din stnga.
COLOANAS_UPDATED va returna TRUE pentru toate coloanele din aciunea INSERT deoarece
coloanele au fost inserate cu valori explicite sau cu valori implicite (NULL). COLOANAS_UPDATED
poate fi folosit oriunde n corpul declanatorului.
bitwise_operator Operaor pe bit folosit n comparri.
Updated_bitmask Este o masc de tip ntreg pentru acele coloanele care tocmai au fost modificate sau
inserate. De exemplu, tabela t1 conine coloanele C1, C2, C3, C4, i C5. Pentru a verifica dac coloanele
C2, C3, i C4 sunt toate modificate (tabela t1 avnd un declanator UPDATE), specific valoarea 14
(01110). Pentru a verifica dac doar coloana C2 este modificat, specific valoarea 2(00010).
Operaor de comparare.Este unul din operatorii de comparare. Folosii semnul egal (=) pentru a verifica
dac toate coloanele specificate n updated_bitmask sunt realmente modificate.Folosii semnul mai mare
(>) pentru a verifica dac oricare sau cteva din coloanele specificate n updated_bitmask sunt modificate.
Coloana_bitmask Este o masc de tip ntreg pentru acele coloane pentru care verificm dac ele au fost
modificate sau inserate.
SQL Server permite declanatori multiplii ce pot fi creai pentru fiecare eveniment de modificare
de date (DELETE, INSERT, or UPDATE). De exemplu, dac este executat CREATE TRIGGER FOR
UPDATE pentru un tabel care deja are un declanator UPDATE, atunci este creat un declanator adiional
de tip UPDATE.
Exemplul 1 Declanator ce afiseaz pe ecran un mesaj cnd se adaug sau se modific date din tabela
Catalog
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'declan_mesaj ' AND type = 'TR') DROP
TRIGGER declan_mesaj
GO
CREATE TRIGGER declan_mesaj
ON Catalog
FOR INSERT, UPDATE
AS RAISERROR ('S-a executat un INSERT sau un UPDATE', 16, 1)
GO
INSERT INTO Catalog VALUES ('101','5',7,'11-17-2006')
GO
UPDATE Catalog
SET Nota=5
WHERE NrLeg='101' and Cod_disciplina='5'
28
Exemplul 2 Acest declanator afiseaz pe ecran anumite mesaje pentru oricine ncearc s adauge
sau s modifice date din tabelul Catalog
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = ' declan1' AND type = 'TR') DROP
TRIGGER declan1
GO
CREATE TRIGGER declan1
ON Catalog
FOR INSERT, UPDATE
AS
DECLARE
@@nota integer,
@@Nr_leg varchar(5)
SELECT @@Nr_leg = i.NrLeg, @@nota = i.Nota
FROM Catalog C, inserted i
WHERE C.NrLeg = i.NrLeg
IF (@@nota
29
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'dec1' AND type = 'TR') DROP
TRIGGER dec1
GO
CREATE TRIGGER dec1
ON Catalog
FOR INSERT, UPDATE
AS
DECLARE
@@nota integer,
@@Nr_leg varchar(5)
SELECT @@Nr_leg = i.NrLeg, @@nota = i.Nota
FROM Catalog C, inserted i
WHERE C.NrLeg = i.NrLeg
IF (@@nota
30
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = ' dec2' AND type = 'TR') DROP
TRIGGER dec2
GO
CREATE TRIGGER dec2
ON Catalog
FOR INSERT, UPDATE
AS
DECLARE @nota integer
SELECT @nota=Catalog.nota from Catalog, inserted WHERE
Catalog.NrLeg=inserted.NrLeg
IF(@nota10) BEGIN
31
RAISERROR ('Nota invalida', 16, 1) ROLLBACK TRANSACTION
END
go
INSERT INTO Catalog VALUES ('102','4',-2,'12-01-2006')
go
SELECT * FROM Catalog WHERE NrLeg='102'
Exemplul 5 Declanator ce adaug 1 punct la orice not din tabelul Catalog
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = ' dec3' AND type = 'TR') DROP TRIGGER dec3
GO
CREATE TRIGGER dec3
ON Catalog
AFTER INSERT AS
UPDATE Catalog
SET Catalog.nota = Catalog.nota + 1
FROM inserted
WHERE Catalog.NrLeg = inserted.NrLeg go
INSERT INTO Catalog VALUES ('103','4',4,'12-01-2006')
go
SELECT * FROM Catalog WHERE NrLeg='105'
Exemplul 6 Declanator inlocuieste o operaie INSERT cu operaia UPDATE din enunul SQL al declanatorului astfel
dac se doreste inserarea unei Catalog pentru un student la o anumit materie se va face o modificarea adic se adaug 1
punct la nota introdus anterior la acea materie pentru studentul dat n tabelul Catalog
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'dec3' AND type = 'TR') DROP TRIGGER dec3
GO
32
CREATE TRIGGER dec3
ON Catalog
INSTEAD OF INSERT
AS
UPDATE Catalog
SET Catalog.nota = Catalog.nota+1
FROM inserted
WHERE Catalog.NrLeg = inserted.NrLeg go
INSERT INTO Catalog VALUES ('102','4',4,'12-01-2006')
go
SELECT * FROM Catalog WHERE NrLeg='102'
Exemplul7 Crearea unui declanator ce afiseaz un mesaj dac s-a modificat Nota i un alt mesaj
ncaz contrar
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'dec4' ND type = 'TR') DROP
TRIGGER dec4
GO
CREATE TRIGGER dec4
ON Catalog
FOR UPDATE
AS
IF UPDATE(Nota)
RAISERROR ('S-a modificat campul Nota',16,1) ELSE
RAISERROR ('S-a modificat alt camp al tabelei CATALOG',16,1)
go
UPDATE Catalog SET Nota=10 WHERE NrLeg='105' go
SELECT * FROM Catalog WHERE NrLeg='105' go
UPDATE Catalog SET Cod_materie='3' WHERE NrLeg='105' go
UPDATE Catalog SET Cod_materie='3', Nota=8 WHERE NrLeg='105'
33
Exemplul 8 Crearea unui declanator care afiseaz un mesaj dac s-au modificat Nota i
Cod_disciplina i ***** ncaz contrar
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'dec5' AND type = 'TR') DROP
TRIGGER dec5
GO
CREATE TRIGGER dec5
ON Catalog
FOR UPDATE
AS
IF UPDATE(Nota) AND UPDATE(Cod_diciplina)
RAISERROR('S-au modificat atributele Nota i
Cod_disciplina',16,1) ELSE
RAISERROR ('*****',16,1)
go
UPDATE Catalog SET Nota=10 WHERE NrLeg='105' go
SELECT * FROM Catalog WHERE NrLeg='105'
go
UPDATE Catalog SET Cod_diciplina ='3', Nota=8 WHERE NrLeg='105'
Exemplul 9 Crearea unui declanator care afiseaz un mesaj dac s-au modificat atributele Nota sau
Cod_disciplina i *.*.* n caz contrar.
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'dec6' AND type = 'TR') DROP
TRIGGER dec6
GO
CREATE TRIGGER dec6
ON Catalog
FOR UPDATE
34
AS
IF (COLOANAS_UPDATED()&6)>0
RAISERROR ('S-au modificat atrib. Nota sau Cod_materie',16,1) ELSE
RAISERROR ('*.*.*',16,1)
go
UPDATE Catalog SET Nota=10 WHERE NrLeg='105' go
SELECT * FROM Catalog WHERE NrLeg='105' go
UPDATE Catalog SET Cod_materie='3', Nota=8 WHERE NrLeg='105' go
UPDATE Catalog SET Data='3-3-2002' WHERE NrLeg='105'
Exemplul 9 Crearea unui declanator care verifica dac au fost modificate printr-o comanda
UPDATE coloanele 3, 6 i 9 din tabelul Student
IF EXISTS (SELECT name FROM sysobjects WHERE
name = 'dec7' AND type = 'TR') DROP TRIGGER dec7
GO
CREATE TRIGGER dec7
ON Student
FOR UPDATE AS
IF ( (SUBSTRING(COLOANAS_UPDATED(),1,1)=power(2,(3-1))--coloana
3+ power(2,(6-1))) --coloana 6
AND (SUBSTRING(COLOANAS_UPDATED(),2,1)=power(2,(1-1)))--coloana 9 (coloana 1 din
cel de-al II-lea octet )
PRINT 'Coloanele 3, 6 i 9 au fost modificate'
GO
UPDATE Student
SET Initiala='O', Data_nastere='12-12-1980', NrLeg_sot='105' WHERE NrLeg='105'
GO
SELECT * FROM Student WHERE NrLeg='105'
Exemplul 10 Crearea unui declanator pentru DELETE
35
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sterge_stud' ND type = 'TR') DROP
TRIGGER sterge_stud
GO
CREATE TRIGGER sterge_stud
ON Student
FOR DELETE
AS
DECLARE @NL varchar(5)
SELECT @NL=deleted.NrLeg
FROM deleted
IF (cast(@NL as integer)>0) PRINT 'S-a
Sters'
ELSE
BEGIN
PRINT 'Acest NrLeg nu exista'
ROLLBACK
TRANSACTION END
GO
DELETE FROM Student WHERE NrLeg='17'
Modificarea unui declanator se face cu comanda ALTER TRIGGER care are sintaxa:
ALTER TRIGGER nume_declan
ON ( table | view )
[ WITH ENCRYPTION ]
{
{ ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
[ NOT FOR REPLICATION ]
AS
sql_statement [ ...n ]
}
|
{ ( FOR | AFTER | INSTEAD OF ) { [ INSERT ] [ , ] [ UPDATE ] }
36
[ NOT FOR REPLICATION ]
AS
{ IF UPDATE ( coloana )
[ { AND | OR } UPDATE ( coloana ) ]
[ ...n ]
| IF ( COLOANAS_UPDATED ( ) { bitwise_operaor } updated_bitmask )
{ comparison_operaor } coloana_bitmask [ ...n ]
}
sql_statement [ ...n ]
}
}
tergerea unui declanator se face cu comanda:
DROP TRIGGER { nume_declan } [ ,...n ]
Un declanator poate s conin oricte instruciuni cu condiia s fie ncadrate ntr-un bloc
BeginEND. La executarea unui declanator SQL serverul acceseaz o tabel special cu datele ce au
determinat rularea declanatorului.Aceasta tabel este INSERTED pentru operaiile INSERT i UPDATE
i respectiv DELETED pentru operaiile DELETE i UPDATE. Deoarece un declanator este executat
ntotdeauna dup operaie, liniile din INSERTED reprezint ntotdeauna o dublur a uneia sau a mai multor
nregistrri din tabela de baz.
Restricii de utilizare a declanatorilor
SQL-Serverul impune anumite restricii asupra tipurilor de instruciuni care pot fi executate n
cadrul unui declanator. Majoritatea restriciilor sunt determinate de imposibilitatea de anulare a
consecinelor unei operaii insert, update sau delete.
n corpul unui declanator nu poate apare nici una din instruciunile:
- CREATE DATABASE, CREATE TABLE INDEX, PROCEDURE, DEFAULT, RULE,
TRIGGER i VIEW.
37
- toate instruciunile DROP;
- instruciunile de modificare a obiectelor din baz ALTER TABLE, ALTER DATABASE i
TRUNCATE TABLE;
- UPDATE STATISTICS;
- Operaii de ncrcare a BD LOAD DATABASE i LOAD TRANZACTION;
- Toate instruciunile care realizeaz modificri fizice pe disc:DISK.
- Crearea de fiiere temporare fie implicita fie prin SELECT;
- Nu se poate crea un declanator pentru o vedere ci numai pentru tabel de baz care definete
vederea.
Manipularea unor coloane care conin bloc nu determin execuia unui declanator.
Tipuri de declanatoare
Atunci cnd se creeaz un declanator se specific de cte ori se execut. Exist 2 tipuri de
declanatoare:
-Declanator linie care se execut ori de cte ori tabelul este afectat de instruciunea
declanatoare. Dac o instruciune UPDATE actualizeaz 20 de linii, declanatorul se execut de 20 de ori.
Dac instruciunea nu afectez nici o linie declanatorul nu este executat.
-Declanatorul instruciune este executat o singur dat pentru instruciunea declanatoare. De
exemplu dac este executat o instruciune UPDATE care actualizeaz
10 linii este executat o singur dat
Declanatorele INSERT i UPDATE sunt utile pentru ca impun restriciile refereniale i asigur
validarea datelor nainte de a le scrie n tabele. De obicei se folosete pentru a verific dac datele urmrite
satisfac anumite criterii. La declanatoare se apeleaz numai cnd criteriile de integritate sunt foarte
complete. Declanatorul de mai jos este activat ori de cte ori se nsereaz sau se modific o
nregistrare n tabelul vnzri.
38
CREATE TRIGGER ins_vanzari
ON vanzari
FOR INSERT,UPDATE AS
DECLARE @zidinluna tinyint
SELECT @zidinluna=DataPart(Day, iData_comenzi)
FROM vanzari v, Inserted i
WHERE v.star_id=i.star_id AND
v.nr_com=i.nr_com AND
v.denumire=i.denumire
IF @zidinluna>15
Begin
ROLLBACK
END
GO
CREATE TRIGGER elimart
ON articole FOR
DELETE AS
FOR EACH row
WHERE (pretart)
39
END
Se poate crea declanatorul care s reacioneze dac se modific doar o coloan. Instruciunea IF
update poate fi utilizat ntr-un declanator pentru a decide dac se coninu execuia acestuia.
IF UPDATE (pre )AND (@@row COUNT=1) BEGIN
-
- END
In acest caz blocul se execut numai dac s-a modificat coloana pret .Modificarea unei coloane nu
nseamn neaprat schimbarea valorii ei.
Declanatoare pentru stergere
Se folosesc pentru a preveni tergerea acolo unde acestea ar afecta integritatea datelor (Este
exemplul cheilor strine pentru alte tabele. Al doilea este tergerea unui articol n cascad care s elimine
nregistrrile copie ale unei nregistrri particulare )
CREATE TRIGGER sterg
ON vanzri FOR
DELETE AS
IF @@row COUNT>1 /*verifica nr de linii afectate impiedicnd
stergerea a mai mult de o linie*/
BEGIN
ROLLBACK
RAISERROR(puteti sterge o singur instruciune la un moment dat,16, 10)
END
DECLARE @stare_id char(4)
SELECT @stare_id=s.stare-id
40
FROM vanzri v deleted s
IF EXIST (SELECT * FROM vanzri
WHERE [email protected]_id)
BEGIN ROLLBACK
RAISERROR(nu poate fi sters,16,10)
END GO
41
GESTIUNEA TRANZACIILOR
Conceptul de gestiune a tranzaciilor se refer la problematica meninerii ntr-o stare consistent a bazei de date n condiiile n care accesul la date se face ntr-un regim concurent i este posibil apariia unor defecte. n consecin se disting dou domenii de sine stttoare n cadrul problematicii generale a gestiunii tranzaciilor :
Controlul concurenei
Se ocup cu mecanismele de sincronizare a acceselor astfel nct s fie meninut integritatea bazei de date. Atunci cnd controlul concurenei este realizat prin mecanismele de blocare (care la ora actual sunt cele mai rspndite) mai apare o problem, colateral, i anume aceea a interblocrilor. Datorit importanei sale problema gestiuni interblocrilor este de multe ori tratat ca o problematic de sine stttoare a gestiunii tranzaciilor.
Rezistena la defecte
Se refer la tehnicile prin care se asigur att tolerana sistemului fa de apariia unor defecte, ct i capacitatea de recuperare a acestuia, adic posibilitatea de revenire la o stare consistent n urma apariiei unui defect care a cauzat intrarea lui ntr-o stare inconsistent.
Definiia conceptului de tranzacie
Prin controlul concurenei i rezistena la defecte se urmrete asigurarea consistenei i siguranei bazei de date. O baz de date este ntr-o stare consistent dac respect toate constrngerile de integritate a datelor definite asupra sa. n timpul operaiilor de adugare, tergere i modificare, baza de date trece dintr-o stare n alta. Evident, starea rezultat dup orice prelucrare asupra bazei de date trebuie s fie o stare consistent, chiar dac n timpul prelucrrii baza de date s-a aflat temporar ntr-o stare inconsistent.
Sigurana bazei de date se refer la tolerana acesteia fa de defecte i la capacitatea de recuperare dup apariia unui defect.
O tranzacie este o unitate logic de prelucrare care asigur consistena i sigurana bazei de date. n principiu, orice execuie a unui program se poate considera o tranzacie dac baza de date este ntr-o stare consistent att nainte ct i dup execuia sa. Consistena bazei de date este garant independent de faptul c :
1. tranzacia a fost executat n mod concurent cu alte tranzacii ; 2. au aprut defecte n timpul execuiei tranzaciei.
n general, o tranzacie const dintr-o secven de operaii de citire i scriere a bazei de date, la care se adaug o serie de operaii de calcul. Baza de date poate fi ntr-o stare temporar inconsistent n timpul executrii tranzaciei dar trebuie s fie n stri consistente att nainte ct i dup execuia acesteia.
Tranzaciile ar trebui s conin doar acele comenzi DML care realizeaz o singur modificare asupra datelor. De exemplu un transfer de fonduri (s spunem 1000$) ntre dou conturi ar trebui s implice un debit al unui cont de 1000$ i un credit al altui cont de 1000$. Ambele aciuni ar trebui s se ncheie cu succes sau s dea eroare mpreun. Creditul nu ar trebui executat fr debit.
Condiii de terminare a tranzaciilor
O tranzacie nu se termin ntotdeauna cu succes totui orice tranzacie trebuie s se termine, indiferent de situaia existent (chiar i n cazul unor defecte). Dac tranzacia reuete s execute cu succes toate operaiile prevzute, atunci aceasta se va termina printr-o operaie de validare (commit). n schimb, dac dintr-un motiv sau altul tranzacia nu reuete s-i execute complet operaiile prevzute, atunci se va termina printr-o operaie de abortare (abort sau rollback). Motivele pentru care o tranzacie se aborteaz sunt numeroase, ele pot fi interne tranzaciei sau externe acesteia (ex. : detectarea de ctre SGBD a unei situaii de interblocare). n cazul abortrii, execuia tranzaciei este oprit iar efectele tuturor operaiilor pe care le-a
42
executat pn n acel moment sunt anulate astfel nct baza de date revine la starea de dinaintea lansrii tranzaciei.
Comanda de validare a unei tranzacii are dublu rol :
-ului momentul de la care efectele tranzaciei pot fi reflectate n baza de date i devin vizibile altor tranzacii ;
ia nu se mai poate
aborta) i modificrile efectuate n baza de dte devin permanente.
Operaia de validare este vital n cazul sistemelor concurente, deci acolo unde este posibil executarea n acelai timp a mai multor tranzacii care acceseaz aceeai baz de date. Prin validare se pot preveni o serie de fenomene nedorite cum este abortarea n cascad a tranzaciilor.
S presupunem c o tranzacie T este abortat dup ce a efectuat una sau mai multe operaii de actualizare a bazei de date. n acest caz datele alterate de ctre tranzacia T vor fi readuse la valorile pe care le-au avut nainte de a fi modificate de aceasta. Este ns posibil ca unele dintre tranzaciile executate n mod concurent cu tranzacia T s fi accesat aceste date nainte de abortarea lui T. Aceste tranzacii vor trebui s fie, la rndul lor, abortate deoarece au avut acces la date inconsistente din baza de date iar rezultatele produse de ele pot fi compromise. Acest efect se poate propaga n continuare i asupra altor tranzacii, pe un numr nedefinit de nivele, conducnd la abortarea n cascad a tranzaciilor. Fenomenul este cunoscut n literatura de specialitate sub numele de efect domino.
Dac se folosete un mecanism de validare care respect cele dou reguli de mai sus, atunci apariia fenomenului de abortare n cascad devine imposibil. ntr-adevr, conform primei reguli, nici o tranzacie nu va putea accesa datele modificate de tranzacia T dect dup validarea acesteia. Pe de alt parte, conform regulii a doua, dup validarea sa, tranzacia T nu mai poate fi abortat, deci nu poate declana un lan de abortri n cascad.
Validarea unei tranzacii marcheaz, din punct de vedere logic, terminarea acesteia. Validarea nu se poate face nainte ca operaiile specificate prin codul tranzaciei s fie executate integral i nainte ca tranzacia s ajung ntr-o stare ncepnd de la care exist certitudinea c nu mai poate fi abortat.
Pn n momentul validrii, actualizrile efectuate de tranzacie sunt invizibile alror tranzacii, au caracter tentativ i pot fi oricnd revocate (odat cu abortarea tranzaciei). Dup validare actualizrile se nscriu cu caracter permanent n baza de date i devin irevocabile. Dup validare nu maie ste posibil abortatrea tranzaciilor.
Proprietile tranzaciilor
Prin definie, tranzaciile sunt uniti de execuie care garanteaz consistena i sigurana bazei de date. Pentru aceasta orice tranzacie trebuie s satisfac un set de patru condiii sintetizate n literatura de specialitate prin acronimul ACID atomicitate, consisten, izolare, durabilitate.
Atomicitatea
Se refer la faptul c o tranzacie este considerat o unitate elementar de prelucrare. Aceasta nseamn c execuia unei tranzacii se face dup regula totul sau nimic , adic ori sunt executate toate operaiile din tranzacie ori nu se execut nimic. Dac o tranzacie este ntrerupt datorit unor cauze oarecare, atunci i revine SGBD-ului sarcina de a asigura, ntr-un fel sau altul, terminarea tranzaciei. Dup eliminarea cauzei care a dus la ntreruperea tranzaciei, n funcie de stadiul de execuie n care s-a aflat aceasta n momentul apariiei ntreruperii, SGBD-ul poate proceda ntr-unul dintre mod
Top Related