Microsoft Word - SIE_MAE_2009.doc

I. CONCEPTE ALE BAZELOR DE DATE RELAIONALE

1.1 Definiii1.2 Niveluri de abstractizare a datelor1.3 Componente ale bazelor de date relaionale1.4 Proiectarea bazelor de date relaionale. Etape. Normalizarea bazelor de date

1.1 Definiii

Baze de dateO 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 dateUn 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-otranzacie 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 pentrua obine date din baza de date. SQL este principalul limbaj folosit pentru sistemele DBMSrelaionale. 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 relaionaleO baz de date relaional este o baz de date care respect modelul relaional, dezvoltat de Dr. E.

1F. 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 le x te r nG r u p 1 .G r u p n

N i v e lc o ncep t u alS c hem a c o ncep t u al a

N i v e lS 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 car e

Fig. 1.1 Niveluri de abstractizare a datelor

-Nivelul externEste 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. TabeleUnitatea 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. RelaiiRelaiile 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

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 pevertical, 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_DVDYEAR 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.

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, adicnumrul 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, indicatprintr-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 parteaunu, 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.

5Toate 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. RestriciiO 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 dateDup 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

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. Prinimpunere 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.VizualizriO 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).

111.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 concentreazasupra 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

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

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*PRETNu 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 treiforme 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.

Necesitatea normalizriiFigura 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 actualizareAnomalia de inserareAnomalia 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 tergereAnomalia 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 actualizareAnomalia 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 normalizareDe 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, actualizarei tergere. Procesul determin crearea unui numr mai mare de relaii dect ai avea ntr-un model fr

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 unicPrimul 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 repetateO 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

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 parialenainte 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.Toateatributelenon-cheiesuntdependentefuncionaldeidentificatorulunic(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 tranzitivePentru 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 depindnumai 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 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.

Limbajul SQL( Structured Query Language )

Caracteristici generale

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 instruciunin 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 SELECTSELECT [ALL/DISTINCT/DISTINCTROW] list atributeFROM list-tabele[WHERE criteriu-de-cutare][GROUP BY criteriu-de-grupare] [HAVING criteriu-de-grupare][ORDER BY criteriu-de-ordonare [ASC|DESC]];Unde:lista-atributespecific atributele ale cror valori vor fi returnate; FROM list-tabelespecific tabelele din care se vor extrage datele;WHEREpermite prin precizarea unei expresii exprimarea criteriului de selecie;ORDER BYprecizeaz atributul dup care se va face ordonarea;GROUP BYfolosit pentru a partiiona o tabel n grupuri acordnd acestora valori pe un atribut sau list de atribute;HAVINGspecific 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;

n cazul n care dorii s cunoatei denumirea produselor cu preuri ntre 19000 i 110000 lei putei fie sintroducei 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, EXISTDomeniul de obinere a rezultatelor unei subinterogri poate fi influenat prin precizarea unuia din cuvintele cheie: ALL, ANY i respectiv EXIST. ALLSe preiau rezultatele subinterogrii i, dac acestea ndeplinesc condiia cerut, se returneaz valoarea logicTrue.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

WHERE CALITATEA=1AND PRE< ALL (SELECT PRE FROM PRODUSE WHERE CALITATEA=2);

ANYAre 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 anul2000:SELECT TITLUL_CARTII, DOMENIUL, ANUL_APARITIEI, PRETFROM CRIWHERE 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, PREFROM CARTIWHERE DOMENIUL= SOME (SELECT DOMENIUL FROM CARTI WHERE DOMENIUL ="INFORMATICA"); EXISTSFolosete 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_APARIIEIFROM CRIWHERE 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 UNIONCnd utilizatorul dorete s vad rezultatele mai multor interogri SELECT n acelai timp, prin combinarea ieirilor lor, poate utiliza UNION:SELECT lista_campuri FROM tabela1UNION SELECT list_campuri FROM tabela2[GROUP BY camp_de_grupare] [HAVING criteriul_de_agregare][UNION SELECT list_campuri FROM tabela3[GROUP BY camp_de_grupare ] [HAVING criteriul_de_grupare]][UNION...][ORDER BY camp_criteriu_de_sortare];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 COLABORATORIGROUP BY CATEGORIEHAVING CATEGORIE = "STUDENT"UNION SELECT CATEGORIE, AVG(SALARIU) AS MEDIE FROM COLABORATORIGROUP BY CATEGORIEHAVING CATEGORIE = "ING";

b) Fie tabelele Clieni (cod, nume, localitate) i Furnizori (cod, nume, localitate). Prin utilizarea operatoruluiUNION putem obine lista comun a clienilor i furnizorilor:SELECT * FROM CLIENIUNIONSELECT * FROM FURNIZORIORDER BY NUME;

Instruciuni pentru actualizarea bazei de datePentru inserarea unui tuplu ntr-o tabel se utilizeaz comanda INSERT:INSERT INTO nume-tabela [(nume-atribut,)] {VALUES(valoare,)|cerere} 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 PRODUSESELECT * FROM NOUTTI;Actualizarea datelor dintr-o tabel se realizeaz prin comanda UPDATE care prezint urmtoarea sintax:UPDATE nume-tabelaSET {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 PRODUSESET PRE=PRE+500WHERE CALITATE=2;

Gestiunea view-urilor

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.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.CREATE VIEW nume-view [] AS SELECT secvena-select[WITH CHECK OPTION]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 realizatactualizarea 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 ASSELECT CODFZ, DENFZ, ADRESA FROM FURNIZOR WHERE ADRESA="BUCURESTI";View created.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) ASSELECT COD_DEP, AVG(CANTITATE), MAX(CANTITATE), MIN(CANTITATE) FROM STOCURIGROUP 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_AAS SELECT * FROM CLIENTWHERE CODCLI IN (SELECT CODCLI FROM COMANDAWHERE CODDEP IN (SELECT CODDEP FROM DEPOZITWHERE DENDEP LIKE "*ALIM*"));

Utilizarea opiunii WITH CHECK OPTIONAceast 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 SAL1AS SELECT MARCA, NUME, SAL FROM SALARIATWHERE SAL BETWEEN 1500000 AND 3000000WITH CHECK OPTION;Asupra view-urilor pot fi efectuate aceleai operaii ca asupra tabelelor pe baza crora au fost definite, cu unele restricii:

Interogarea view-urilorRestricii 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;

Realizarea operatorilor relaionali folosind limbajul SQL

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 TOTICLIENTIISELECT 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 CLIENTIUNIONSELECT * 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 Tcuprinznd mulimea tuplurilor aparinnd lui R dar neaparinnd lui S.Implementarea operatorului diferen se realizeaz construind o cerere de cutare a tuplurilor din tabelaClienti 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 (m0

RAISERROR ('S-au modificat atrib. Nota sau Cod_materie',16,1) ELSERAISERROR ('*.*.*',16,1)

go

UPDATE Catalog SET Nota=10 WHERE NrLeg='105' goSELECT * FROM Catalog WHERE NrLeg='105' goUPDATE Catalog SET Cod_materie='3', Nota=8 WHERE NrLeg='105' goUPDATE 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 dec7GO

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

IF EXISTS (SELECT name FROM sysobjectsWHERE name = 'sterge_stud' ND type = 'TR') DROP TRIGGER sterge_stud

GO

CREATE TRIGGER sterge_stud

ON Student FOR DELETE ASDECLARE @NL varchar(5) SELECT @NL=deleted.NrLegFROM deleted

IF (cast(@NL as integer)>0) PRINT 'S-a Sters'ELSE BEGINPRINT 'Acest NrLeg nu exista'

ROLLBACK TRANSACTION ENDGO

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 ] }

[ 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.

-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 actualizeaz10 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.

CREATE TRIGGER ins_vanzari

ON vanzari

FOR INSERT,UPDATE ASDECLARE @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.denumireIF @zidinluna>15

Begin ROLLBACK ENDGO

CREATE TRIGGER elimart

ON articole FOR DELETE ASFOR EACH row

WHERE (pretart)1/*verificanrdeliniiafectateimpiedicnd

stergerea a mai mult de o linie*/

BEGIN ROLLBACKRAISERROR(puteti sterge o singur instruciune la un moment dat,16, 10)

END

DECLARE @stare_id char(4)

SELECT@stare_id=s.stare-id

FROM vanzri v deleted s

IF EXIST (SELECT * FROM vanzriWHERE stare_id=@s.stare_id)

BEGIN ROLLBACKRAISERROR(nu poate fi sters,16,10)

END GO

GESTIUNEA TRANZACIILORConceptul 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 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 : indic SGBD-ului momentul de la care efectele tranzaciei pot fi reflectate n baza de date i devin vizibile altor tranzacii ; marcheaz momentul ncepnd de la care efectele tranzaciei nu mai pot fi anulate (tranzacia 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 modurile urmtoare : fie completeaz operaiile rmase neexecutate din cadrul tranzaciei, terminnd tranzacia cu succes fie anuleaz toate efectele operaiilor executate pn n momentul ntreruperii, terminnd tranzacia prin abortare. Consistena Consistena unei tranzacii const pur i simplu n corectitudinea ei. Orice tranzacie, dac este executat independent, trebuie s menin consistena bazei de date. Altfel spus, o tranzacie este un program corect care transform baza de date dintr-o stare consistent ntr-o alt stare consistent a sa. Prin consistena bazei de date nelegem satisfacerea tuturor constrngerilor de integritate, explicite sau implicite, cum ar fi : unicitatea cheilor primare ; integritatea referenial ; orice predicat exprimat n sens de constrngere de integritate asupra bazei de date. Bineneles c este de neconceput verificarea tuturor acestor condiii dup executarea fiecrei tranzacii. De aceea unicul criteriu pentru stabilirea proprietii de consisten a unei tranzacii rmne corectitudinea sa din punct de vedere logic. Spre deosebire de celelalte proprieti din complexul ACID, care sunt asigurate de ctre sistem, proprietatea de consisten a tranzaciei cade n sarcina programatorului de aplicaii. De remarcat faprul c strile intermediare prin care trece baza de date n timpul execuiei unei tranzacii nu sunt neaprat consistente. Izolarea Se refer la proprietatea oricrei tranzacii de a avea acces doar la strile consistente ale bazei de date. Aceasta nseamn c modificrile efectuate de ctre o tranzacie sunt inaccesible altor tranzacii concurente pn n momentul validrii acesteia. Prin proprietatea de izolare se creeaz iluzia c fiecare tranzacie este executat singur n sistem. Utilizatorul care a lansat o tranzacie nu va percepe n nicu un fel (cel puin n ceea ce privete rezultatele) faptul c alte tranzacii sunt executate n acelai timp. Izolarea tranzaciilor este asigurat prin algoritmi de control al concurenei. Proprietatea de izolare este important deoarece elimin fenomenul de abortare n cascad a tranzaciilor (efect domino). ntr-adevr dac rezultatele incomplete ale unei tranzacii ar fi vizibile altor tranzacii nainte de validarea acesteia i dac se ntmpl ca aceast tranzacie s aborteze, atunci toate tranzaciile care au accesat rezultatele incomplete vor trebui abortate. Aceasta poate conduce la abortarea altor tranzacii .a.m.d. rezultnd efectul domino. Durabilitatea Durabilitatea unei tranzacii este proprietatea prin care se garanteaz faprul c, odat tranzacia validat, rezultatele sale devin permanente i sunt nscrise n baza de date. Chiar dac dup momentul validrii apare un defect care mpiedic nscrierea normal a rezultatelor tranzaciei n baza de date, acestea vor fi trecute n baza de date dup reluarea activitii. Rezultatele tranzaciilor validate vor supravieui oricrei cderi de sistem. Mecanismul prin care re realizeaz proprietatea de durabilitate are la baz conceptul de jurnal. Jurnalul este un fiier secvenial n care sunt nregistrate toate operaiile efectuate de tranzaciile din sistem. Jurnalul conine istoria evoluiei ntegului sistem. El este folosit la reluarea activitii de ctre procedurile de recuperare care vor completa eventualele operaii neterminate ale tranzaciilor care au fost validate nainte de apariia defectului. Controlul tranzaciilor cu instruciuni SQL Exist dou clase de tranzacii. Tranzacii DML care conin un numr oarecare de blocuri DML i pe care SGBD-ul (ex ORACLE) le trateaz ca o singur entitate sau o singur unitate logic de lucru, i tranzactii DDL care conin un singur bloc DDL. O tranzacie nou este lansat fie imediat dup conectarea la serverul de baze de date (de exemplu, printr-o sesiune SQL*Plus) fie dup o comand care a ncheiat tranzacia precedent (execuia unui COMMIT sau ROLLBACK), cnd este ntlnit primul bloc executabil DML sau DDL. O tranzacie se termin n una din urmtoarele situaii : * ntlnirea comenzior COMMIT/ROLLBACK * Sfritul comenzii DDL * Apariia anumitor erori (DEADLOCK) * ntlnirea comenzii EXIT iesire din SQL*Plus * Apariia unei erori de sistem Un bloc DDL este executat automat i de aceea implicit ncheie o tranzacie. Dup ncheierea unei tranzacii, urmtorul bloc executabil SQL va lansa automat urmtoarea tranzacie. Erorile de Sistem Cnd o tranzacie este ntrerupt de o eroare serioas, de exemplu o eroare de sistem, ntreaga tranzacie este anulat. Aceasta previne erorile datorate modificrilor nedorite asupra datelor, i realizeaz ntoarcerea tabelelor la strile de dup ultimul COMMIT. n acest fel SQL protejeaz integritatea tabelelor. Anularea automata este cauzat cel mai des de ctre o eroare de sistem, ca de exemplu o resetare a sistemului sau o cdere de tensiune. Erorile de tastare a comenzilor, ca de exemplu tastarea gresit a unor nume de coloane sau ncercrile de a realiza operaii neautorizate asupra tabelelor altor utilizatori, nu ntrerup tranzacia i nu realizeaz anularea automat. Aceasta se datoreaz faptului c aceste erori sunt detectate n cursul compilrii (cnd un bloc SQL este scanat i verificat), i nu n timpul execuiei. Urmtoarele instruciuni SQL sunt utilizate cnd apar finalizri (commit) sau refaceri (rollback) : * COMMIT[WORK] * SAVEPOINT nume_savepoint * ROLLBACK[WORK] to [SAVEPOINT] nume_savepoint; De notat ca att COMMIT ct i ROLLBACK sunt instruciuni SQL. Cele trei instruciuni SQL utilizate pentru controlul tranzaciilor sunt explicate mai jos: COMMIT[WORK] Sintaxa : COMMIT[WORK]; * Permanentizeaz schimbrile din tranzacia curent * terge toate punctele de salvare (savepoint) din tranzacie * Termin tranzacia * Elibereaz toate blocrile (Lock) tranzaciei * Cuvantul cheie WORK este opional Utilizatorul trebuie s expliciteze sfritul tranzaciei n programul aplicaie utiliznd COMMIT (sau ROLLBACK). Dac nu se finalizeaz explicit tranzacia i programul se termin anormal, ultima tranzacie executat va fi anulat. Finalizri implicite (commit) apar n urmtoarele situaii : + nainte de o comand DDL + dup o comand DDL + la nchiderea normal a unei baze de date Dac introducei un bloc DDL dup cteva blocuri DML, blocul DDL cauzeaz apariia unui commit naintea propriei execuii, incheind tranzacia curenta. Apoi, dac blocul DDL este executat pn la capt, este i nregistrat (commit). SAVEPOINT Sintaxa : SAVEPOINT nume_savepoint Exemplu : SAVEPOINT terminare_actualizari * Poate fi utilizat pentru a mpri o tranzacie n buci mai mici * Punctele de salvare (savepoints) permit utilizatorului s rein toat munca sa la orice moment din timp, cu opiunea de a nregistra mai trziu totul, a anula totul sau o parte din ea. Astfel, pentru o tranzacie lung, se pot salva pri din ea, pe msura execuiei, la sfrit nregistrndu-se sau refcndu-se coninutul iniial. La apariia unei erori nu trebuie executat din nou fiecare bloc. * La crearea unui nou punct de salvare cu acelai nume ca al unuia dinainte, primul punct este ters. * Numrul maxim de puncte de salvare pentru un proces utilizator este implicit 5. Aceast limit poate fi schimbat. ROLLBACK[WORK] to [SAVEPOINT] nume_punct_salvare * Instructiunea ROLLBACK este utilizata pentru a reface starea bazei de date. * Cuvantul cheie "work" este opional. ntoarcerea la un punct de salvare este de asemenea opionala. * Dac se utilizeaz ROLLBACK fr clauza TO SAVEPOINT, atunci : + se termin tranzacia + se anuleaz modificrile din tranzacia curent + se terg toate punctele de salvare din tranzacie + se elibereaz blocrile tranzaciei ntoarcerea la Nivel de Bloc O parte a unei tranzacii poate fi anulat. Dac un singur bloc DML d eroare, ORACLE va ntoarce napoi doar acel bloc. Aceast facilitate este cunoscut ca STATEMENT LEVEL ROLLBACK. ntoarcerea la nivel de bloc const n faptul c dac un singur segment DML d eroare la execuia unei tranzacii, efectul lui este anulat, dar schimbrile realizate de precedentul bloc DML n tranzactie nu vor fi anulate i pot fi nscrise (COMMIT) sau ntoarse (ROLLBACK) explicit de ctre utilizator. Dac blocul este unul de tip DDL, nscrierea (commit) care precede imediat acest bloc nu este anulat (schimbrile au fost fcute deja permanente). ORACLE realizeaz ntoarcerea la nivel de bloc prin crearea unui punct de salvare implicit nainte de executarea fiecarei comenzi DML. Utilizatorul nu poate referi acest punct de salvare n mod direct. Astfel, dac v ntoarcei la un punct de salvare, atunci: * se anuleaz o parte din tranzacie ; * se reine punctul de salvare pentru ntoarcere, dar se pierd toate celelalte puncte create dup punctul de salvare respectiv ; * se elibereaz toate blocrile de tabele i linii. ntoarceri implicite ntoarcerile implicite apar cnd se ntlnesc terminri anormale ale execuiei (de exemplu cnd se ntrerupe un proces utilizator). ntoarcerile implicite la nivel de bloc apar la eroarea de execuie a unui bloc. Este recomandat ca tranzaciile s se termine explicit utiliznd COMMIT[WORK] ori ROLLBACK[WORK]. Urmtorul exemplu ilustreaz utilizarea unui punct de salvare i a instruciunilor ROLLBACK i COMMIT. INSERT INTO OFFICES VALUES ( 23, LAS VEGAS,WESTERN,null, null, 0 ); SAVEPOINT insert_done; UPDATE SALESREPS SET REP_OFFICE = 23, MANAGER=106; ROLLBACK TO insert_done ( modificrile sunt abandonate ); UPDATE SALESREPS SET REP_OFFICE = 23, MANAGER=106 WHERE EMPL_NUM=102 ; ( revizuim comanda UPDATE ) UPDATE OFFICES SET MGR=102 WHERE OFFICE=23 ; COMMIT; AUTOCOMMIT COMMIT sau ROLLBACK pot fi date manual sau automat, prin utilizarea opiunii AUTOCOMMIT a comenzii SET. Opiunea AUTOCOMMIT controleaz cnd schimbrile dintr-o baz de date sunt fcute permanente. Exist dou setri : COMANDA + DESCRIEREA SET AUTO[COMMIT] ON COMMIT este utilizat automat la fiecare INSERT, UPDATE sau DELETE SET AUTO[COMMIT] OFF COMMIT poate fi utilizat de utilizator explicit. De asemenea, COMMIT se execut dup execuia comenzilor DROP, ALTER, CREATE sau la ieirea din SQL*Plus. ROLLBACK poate fi executat explicit de ctre utilizator pentru refacerea bazei de date. De reinut c SET este o comand SQL*Plus. Controlul concurenei La sistemele n care o baz de date este accesat simultan de ctre mai muli utilizatori apar situaii de coflict datorate accesului concurent la datele care constituie o resurs comun. Consistena la citire Utilizatorii bazelor de date fac dou tipuri de accesri asupra bazelor de date: Operaii de citire ( SELECT ) Operaii de scriere ( INSERT, UPDATE, DELETE ) Cititorului i scriitorului unei baze de date trebuie s i se garanteze o vedere consistent asupra bazei de date. Cititorii nu trebuie s vizualizeze o dat care este n curs de modificare iar scriitorii trebuie s fie siguri c schimbrile dintr-o baz de date sunt fcute ntr-un mod consistent : schimbrile fcute de un scriitor s nu distrug sau s intre n conflict cu schimbrile pe care le face un alt scriitor. Scopul consistenei la citire este acela de a asigura faptul c fiecare utilizator vede data ca fiind cea de la ultimul COMMIT, inainte ca o operatie DML s nceap. Consistena la citire este implementat prin inerea unor copii pariale ale bazei de date n segmente de ntoarcere (ROLLBACK). Cnd de execut operaii de scriere ntr-o baz de date, ORACLE va face o copie a datelor nainte de schimbare i o va scrie ntr-un segment de ntoarcere. Toi cititorii, exceptndu-i pe cei care au facut schimbrile, nc mai vd baza de date care exista nainte ca schimbrile s fie fcute ei vd segmentul de ntoarcere de fapt. Oricum, nainte ca schimbrile s fie fcute permanente n baza de date, doar utilizatorul care modific datele poate s vad baza de date cu alteraiile ncorporate. Toi ceilali vd baza de date nemodificat (fereastra din segmentul de ntoarcere ). Aceasta garanteaz citirea unor date consistente care nu fac subiectul unor modificari n curs. Cnd execuia unui bloc DML se ncheie (commit), schimbrile fcute n baza de date devin vizibile oricarui utilizator care execut SELECT. Modificrile sunt fcute universale i acum toi utilizatorii vd baza de date cu modificarile ncorporate. Spaiul ocupat de ctre vechile date n segmentul de ntoarcere este eliberat pentru a fi reutilizat. Dac tranzacia este anulat (ROLLBACK), atunci toate schimbrile sunt anulate : Versiunea veche (original) a bazei de date aflat n segmentul de ntoarcere este scris napoi (recuperat) n baza de date. Toi utilizatorii vd baza de date existent nainte de nceperea tranzaciei. Tranzacii de citire Implicit, modelul consistent al ORACLE DBMS garanteaz c rezultatul execuiei unui bloc este consistent. n anumite situaii se poate dori efectuarea unor interogri multiple, asupra datelor din mai multe tabele i se dorete asigurarea consistenei datelor. Adic, rezultatele din orice tabel sunt consistente n timp n raport cu rezultatele din orice alt tabel. Comanda SQL : SET TRANSACTION READ ONLY este utilizat pentru a ncepe o tranzactie de citire exclusiv. Consistena la citire pe care READ ONLY o furnizeaz este implementat n acelai fel cu consistena la nivel de bloc utiliznd segmente de ntoarcere. Fiecare bloc vede implicit o fereastr consistent a datelor la momentul nceperii blocului. Aceast facilitate este foarte folositoare pentru rapoarte care efectueaz mai multe interogri asupra mai multor tabele, n timp ce ali utilizatori actualizeaz aceleai tabele. Observaii : SET TRANSACTION READ ONLY este utilizat pentru a ncepe o tranzacie doar de citire. Sunt permise doar cereri ( blocuri SELECT ). Comenzile DML nu sunt permise. SELECT FOR UPDATE va genera o eroare. O instruciune COMMIT, ROLLBACK, sau un bloc DDL va termina tranzacia de citire ( de reinut c blocurile DDL genereaz implicit suprascrieri COMMIT ). n cazul blocurilor DDL, nu este dat nici o indicaie referitoare la faptul c tranzacia se termin implicit. n timpul tranzaciei de citire, toate cererile se refer la aceeai copie a bazei de date ( schimbrile sunt efectuate nainte ca tranzacia de citire s nceapa). Ali utilizatori pot continua s citeasc sau s modifice datele. Urmatoarele instruciuni pot fi rulate pentru a extrage datele din tabelele SALESREPS i OFFICES. COMMIT; SET TRANSACTION READ ONLY; SELECT EMPL_NUM, NAME, REP_OFFICE, TITLE FROM SALESREPS; SELECT OFFICE, CITY, REGION FROM OFFICES; COMMIT; Ultimul COMMIT este necesar pentru a termina explicit tranzacia de citire. Niveluri de izolare a tranzaciilor SerializareaSerializarea presupune derularea tranzaciilor fr nici o posibilitate de intercalare a operaiilor lor, fiind regula care impune programarea acestora n uniti atomice. Acest lucru nseamn c, o dat ce o tranzacie a devenit activ, prin execuia primei operaii de citire sau scriere, tranzaciile iniiate de ali utilizatori nu pot fi derulate (sunt blocate n stare de ateptare) pn cnd cea dinti se ncheie. Totui, nici un sistem de baze de date nu impune o planificare att de strict a operaiilor tranzacionale, din urmtorul motiv: intercalarea operaiilor tranzacionale ofer oportuniti deosebite de mbuntire a performanelor n folosirea concurenial a resurselor, lucru care se poate determina prin numrul de tranzacii ncheiate ntr-o anumit perioad de timp. O astfel de abordare trebuie totui s respecte principiul seriabilizrii, n caz contrar fiind ameninat integritatea datelor. Cu alte cuvinte, chiar n condiii concureniale, modul de derulare a tranzaciilor trebuie s fie echivalent cu derularea serial ca uniti atomice a acestora, adic situaia n care fiecare tranzacie s-ar fi derulat n ordine, dup ce precedenta s-a ncheiat complet. Pentru a obine un grad de concuren mai ridicat s-a recurs la slbirea principiului seriabilizrii pentru a scadea numrul de tranzacii blocate n starea de ateptare. Consecina mai puin dorit, dar acceptabil n anumite condiii, va fi scderea gradului de izolare a tranzaciilor, crescnd astfel posibilitatea executrii operaiilor tranzacionale ntr-o ordine neechivalent serializabil. GESTIUNEA TRANZACIILORConceptul 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 : 3. tranzacia a fost executat n mod concurent cu alte tranzacii ; 4. 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 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 : indic SGBD-ului momentul de la care efectele tranzaciei pot fi reflectate n baza de date i devin vizibile altor tranzacii ; marcheaz momentul ncepnd de la care efectele tranzaciei nu mai pot fi anulate (tranzacia 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 modurile urmtoare : fie completeaz operaiile rmase neexecutate din cadrul tranzaciei, terminnd tranzacia cu succes fie anuleaz toate efectele operaiilor executate pn n momentul ntreruperii, terminnd tranzacia prin abortare. Consistena Consistena unei tranzacii const pur i simplu n corectitudinea ei. Orice tranzacie, dac este executat independent, trebuie s menin consistena bazei de date. Altfel spus, o tranzacie este un program corect care transform baza de date dintr-o stare consistent ntr-o alt stare consistent a sa. Prin consistena bazei de date nelegem satisfacerea tuturor constrngerilor de integritate, explicite sau implicite, cum ar fi : unicitatea cheilor primare ; integritatea referenial ; orice predicat exprimat n sens