CAPITOLUL 1Sisteme de baze de date Bazele de date se folosesc in aproape toate domeniile de activitate actuale:Activitati bancare si comerciale (depozite bancare, vanzari produse)Productie (gestiunea stocurilor, gestiunea financiar-contabila, salarizare etc.)Evidenta populatiei, taxe si impoziteServicii (servicii medicale, rezervari bilete de calatorie etc.)Definitie (in sens larg): O baza de date(database) este o colecie de date corelate din punct de vedere logic, care reflectaun anumit aspect al lumii reale i este destinataunui anumit grup de utilizatori. In acest sens pot fi considerate ca fiind baze de date:Fise de evidenta (mentinute manual)Fisiere de documente sau foi de calcul tabelar (Microsoft Word, Microsoft Excel)Baze de date mentinute computerizatDefinitie (in sens restrans): O baz de date este o colecie de date creat i meninut computerizat, care permite operaii de:Introducere(insert)Stergere(delete)Actualizare (update)Interogare (query)

Componentele unui Sistem de Baze de Date Un sistem de baze de date(Database System)este un sistem computerizat de meninere a evidenei unei anumite activiti, folosind baze de dateComponentele unui sistem de baze de date sunt: hardware, software, utilizatori si date persistenteHardware: Sistemele de baze de date sunt instalate pe calculatoare de uz generalBazele de date sunt memorate fizic ca fisiere pe discuri magnetice (hard-discuri)Software:Sisteme de operare, biblioteci, instrumente de dezvoltare, interfeteSistemul de Gestiune a Bazelor de Date(SGBD) (Database Management System DBMS) -recepioneaz cererile utilizatorilor de acces la baza de date, le interpreteaz, execut operaiile corespunztoare i returneaz rezultatulAplicatii de baze de date: (Database Applications)sunt programe care ofer anumiteutilizarialeunei baze de date

Utilizatori:Programatori de aplicatii Utilizatori finaliAdministratorul bazei de dateAnalisti si proiectanti ai bazelor de date

Datele persistente sunt memorate in fisiere pe hard-diskLimbaje conceptuale pentru lucrul cu bazele de date:Limbaje pentru Definirea Datelor(LDD) (Data Definition Languages DDL)Limbaje pentru Manipularea Datelor (LMD) (Data Manipulation LanguagesDML)

Arhitectura interna a unui Sistem de BD Arhitectura pe 3 niveluri relativ independente: nivelul intern, nivelul conceptual i nivelul extern(Standard ANSI/X3/SPARC -1975) Schema descrierea datelor pe un anumit nivel: schema interna, conceptuala si scheme externe (vedere utilizator)Corespondente intre niveluri (mappings)

Avantaje oferite de Sistemele de BD Compactitate ridicata datelorReprezentarea unor asocieri complexe intre dateTimp de dezvoltare a bazelor de date redusViteza mare de actualizare si regasire a datelorRedundanta controlata a datelor (si cat mai scazuta)Flexibilitate, mentinerea datelor actualizate la ziIndependenta datelor fata de suportul hardware utilizatSecuritatea datelor: autentificarea utilizatorilor si autorizarea accesuluiImpunerea de restrictii (constrangeri) de integritate la introducerea si actualizarea datelorMentinerea integritatii datelor in caz de defecte: salvare si refacerePosibilitatea de partajare a datelor intre mai multe categorii de utilizatoriPosibilitatea de introducere a standardelor

Clasificarea Sistemelor de Baze de Date (1) Clasificare dupa modelul de date: Modelul ierarhic de dateModelul de date reteaModelul relationalModelul obiect-orientatModelul obiect-relationalClasificare dupa numarul de utilizatoriSisteme mono-utilizatorSisteme multi-utilizatorClasificare dupa numarul de statii pe care este memorata baza dedate:Baze de date centralizateBaze de date distribuiteArhitectura client-server:Server (back-end): SGBD-ul si baza de dateClient (front-end): program (programe) de aplicatie

Sisteme de baze de date centralizate: a-mono-utilizator; b-multi-utilizator

Sistem de baze de date distribuit

Modelarea datelor Un modeleste o abstractizare a unui sistem:capteaz cele mai importante trsturi caracteristice ale sistemului (concepte)conceptele trebuie sa fie relevante din punct de vedere al scopului pentru care se definete modelul respectiv Tehnica de identificare a trsturilor caracteristice eseniale ale unui sistem se numete abstractizareUn model de date stabilete regulile de organizare i interpretare a unei colecii de date. n proiectarea bazelor de date se folosesc2 categorii de modele: Modele conceptualede nivel nalt(modelul Entitate-Asociere, modelul Entitate-Asociere Extins) descriu concis colectiile de date care modeleaz activitatea dorit fr s detalieze modul de reprezentare sau de prelucrare a datelor -schem conceptual de nivel naltModele specifice(modelul ierarhic, modelul reea, modelul relaional, etc.) -descriu reprezentarea mulimilor de entiti i a asocierilor dintre acestea prin structuri de date specifice implement[rii -schem conceptual (logic)Trecerea de la modelul conceptual de nivel nalt la un model de date specific proiectare logic a bazei de date.

Modelul Entitate Entitate-Asociere

Modelul Entitate-Asociere(Entity-Relationship Model) defineste multimile de entiti i asocierile dintre ele, dar nu impune nici un mod specific de structurare i prelucrare (gestiune) a datelor; Introdus n 1976 de P.S. ChenO entitate(entity)este orice exista in realitatea obiectiva si poate fi identificat n mod distinctivExemple: o persoana,o planta, o activitate, un concept etc.Un atribut(attribute) este o proprietate care descrie un anumit aspect al unei entitiExemple: persoanele au nume, prenume, adresa etc.Tip de entitate (entity type): se refera la entittile similare, care pot fi descrise prin aceleasi atributeExemple: tipul persoana, tipul plantaMultime de entitati(entities set): colecia tuturor entitilor de acelai tip dintr-o baz de date constituie o mulime de entitiExemple: multimea tuturor persoanelor, multimea tuturor plantelorO entitate este o instanta a unui tip de entitate si un element al multimii de entitati de acel tipIn exprimarea curenta, adeseori nu se face diferentierea dintre entitate, tip de entitate si multime de entitati, dar diferenta este evidentaAsemanare cu modelul obiect: tip de entitate -clasa; entitate -obiect

Asocieri

O asociere(relationship) este o legtur (coresponden)ntre entiti din dou sau mai multe mulimi de entiti; asocierile pot avea atributeTipul asocierii(relationship type) se refera la asocierile similare, care pot fi definite intre entitati din 2 sau mai multe multimi de entitati Multime de asocieri(relationship set): multimea asocierilor de acelasi tipO asociere este o instanta a unui tip de asociere si un element al multimii de asocieri de acel tipIn exprimarea curenta, adeseori nu se face diferentierea dintre asociere, tip de asociere si multime de asocieri, dar diferenta este evidenta Gradulunui (tip de) asociere (degree): numrul de (mulimi de) entiti asociate; dupa grad, asocierile pot fi:binare(de gradul 2, ntre 2 mulimi de entiti) majoritatea asocierilormultiple(ntre k mulimi de entiti, k > 2)Categorii (tipuri) de asocieri binare - dup numrul elementelor din fiecare dintre cele dou mulimi puse n coresponden:unul-la-unul(one-to-one) 1:1; exemplu: sot-sotieunul-la-multe(one-to-many) 1:N; exemplu: parinte-fiimulte-la-unul(many-to-one) N:1; exemplu: fii-parintemulte-la-multe(many-to-many) M:N; exemplu: profesori-studenti

Categorii de asocieri binare Asocieri binare intre multimile de entitati A si B

Asocieri binare intre multimile de entitati A si B

Cardinalitatea asocierilor

Cardinalitatea (multiplicitatea) unei asocieri fa de o mulime de entiti(cardinality, multiplicity) este numrul maxim de elemente din acea mulime care pot fi asociate cu un element din alt mulime a asocieriiExemplu: asocierea unul-la-multedintre mulimile A i B prezint multiplicitatea 1 fa de mulimea A i multiplicitatea N (se nelege o valoare oarecare N > 1) fa de mulimea BRaport de cardinalitate(cardinality ratio): raportul dintre valorile cardinalitilor unei asocieri fa de dou din mulimile de entiti asociateExemple pentru asocieri binare: 1:1, 1:N, N:1, M:NAsocierile multiple (k-are, k > 2) prezint cte un raport de cardinalitate pentru fiecare pereche de mulimi de entiti pe care le asociaz.

Diagrama Entitate Entitate-Asociere

Diagrama Entitate-Asociere(Entity-Relationship Diagram) reprezint grafic modelul Entitate-Asociere prin mulimile de entiti i asocierile dintre acesteaMultimi (tipuri) de entitati:Puternice (de sine statatoare)Slabe (depind de alte multimi de entitati)Notatii:

Exemplu de diagrama Entitate Entitate-Asociere

Multimi de entitati puternice:SECTII (Numar, Nume, Buget)ANGAJATI (Nume, Prenume, DataNasterii, Adresa, Functie, Salariu)PROIECTE (Denumire, DataInceperii, Termen, Buget)Multimi de entitati slabe: DEPENDENTI (Nume, Prenume, DataNasterii,GradRud)

Asocieri: Asocierea SECTII -ANGAJATI -1:N Asocierea ANGAJATI -PROIECTE -M:N Asocierea ANGAJATI -DEPENDENTI -1:NRaportul de cardinalitate al unei asocieri este stabilit de proiectantastfel nct s reflecte ct mai corect modul de organizare a activitii modelateModul de stabilire a tipurilor de entiti i a asocierilor nu este unic: aceeai funcionalitate se poate obine printr-o varietate de diagrame E-A O mulime de entiti se denumeste printr-un substantiv, iar o asociere se denumeste (de regul) printr-un verb, deoarece o asociere reprezint o interaciune ntre entiti Modelul E-A nu precizeaz modul cum sunt realizate asocierile ntre mulimile de entiti: acest aspect depinde de modelul de date specializat utilizat pentru definirea bazei de dateExemple: n modelul ierarhic asocierile sunt realizate explicit, prin pointeri de la o entitate la entitile asociate; n modelul relaional asocierile se realizeaz prin egalitatea valorilor unor atribute comune ale multimilor de entiti (chei)

Modelul Entitate Entitate-Asociere Extins

Modelul Entitate-Asociere Extins (Enhanced Entity-Relationship Model) permite definirea de subtipuri ale unui tip de entiti, care motenesc atribute de la tipul de entitate respectivCrearea ierarhiilor: specializaresi generalizareTipurile i a subtipurile formeaza ierarhii de tipuri de entiti complexe, organizate pe mai multe niveluriDiagrama Entitate-Asociere Extinsa

Modelul de date ierarhic

Modelul ierarhic(Hierarchical Model): baza de date se reprezinta printr-o structur ierarhic de nregistrri (records) conectate prin legturi (links). A fost primul model folosit pentru dezvoltarea bazelor de date Cel mai cunoscut SGBD ierarhic: sistemul IMS (Information Management System) dezvoltat de IBM n programul de cercetri Apollo, n perioada anilor 1960 O nregistrare de date n modelul ierarhic este o instan a unui tip de nregistrare (record type) i const dintr-o colecie de cmpuri (fields), fiecare cmp coninnd valoarea unui atribut. Un tip de legtur n modelul ierarhic: tip de asociere cu raportul de cardinalitate 1:N (printe-fiu) ntre dou tipuri de nregistrriSchema conceptual a unei baze de date n modelul ierarhic se reprezint printr-un numr oarecare de scheme ierarhice O schem ierarhic este un arbore direcionat, reprezentat pe mai multe niveluri, n care nodurile sunt tipuri de nregistrri, iar arcele sunt tipuri de legturi

Baze de date ierarhiceBaze ierarhice

Numai legturi de tipul printe-fiu, care corespund asocierilor 1:1 i 1:N din modelul E-AAsocierile M:N se pot reprezenta prin multiplicarea nregistrrilor de tip fiu, atunci cnd sunt referite de mai multe nregistrri de tip printemare redundan a datelor Avantaje: simplitatea i eficiena de calcul Deficiente:nu exista separare intre descrierea logica si fizica a datelorinterogarile trebuie s fie prevzute explicit in structura datelorUtilizari actuale- aplicatii specializate, baze de date XML

Modelul de date retea

Modelul reea(Network Model) folosete o structur de graf pentru definirea schemei conceptuale a bazei de date Modelele ierarhic si retea modele pre-relationale standardizat n 1971, de o comisie DBTG (Database Task Group). Sisteme de gestiune comerciale in modelul retea: IDS II (Honeywell), UNISYS (Burroughs), IDMS (Computer Associates) Nodurile grafului sunt tipuri de entiti (nregistrri -records), iar muchiile reprezint asocierile (legturile-links) dintre tipurile de entiti Asocierile M:N se reprezint fr duplicarea nregistrrilor, fiecare nregistrare putnd fi referit de mai multe nregistrri, ceea ce elimin (micoreaz) redundanaDezavantaje: aceleasi ca si la modelul ierarhic, la care se adauga complexitatea mare in reprezentarea datelorActualmente modelul retea: este rar utilizat pentru baze de date de uz general se utilizeaza pentru aplicaii specializate de ex, pentru baze de date grafice (scene virtuale)

Modelul de date relational

Modelul relaional(Relational Model) se bazeaz pe noiunea de relaie(relation) din matematic, care corespunde unei mulimi de entiti Fundamentat de E.F. Codd (IBM), prin lucrarea "Un Model Relaional de Date pentru Bnci Mari de Date Partajate" (1970)Dezvoltare extraordinara a sistemelor de gestiune a bazelor de date relationale, datorit simplitii i a fundamentrii matematice a modeluluiAlte lucrri ale cercetatorilor C.J. Date, P. Chen, R. Boyce, J.D. Ullman, R. Fagin, W. Armstrong, M. Stonebraker etc. au perfecionat modelul relaionalPrimul Sistem de Gestiune a Bazelor de Date Relaionale (SGBDR) a fost prototipul System R (IBM, 1970) Dup aceasta numeroase companii au realizat sisteme de gestiune relaionale: Oracle, Microsoft, Ingres, Sybase, IBM, Informix SGBDR folosesc limbajul SQL (Structured Query Language), pentru care au fost emise mai multe standarde ANSI (American National Standardization Institute) si ISO (International StandardizationOffice)Majoritatea SGBD-urilor relaionale actuale implementeaz versiunea SQL2 (SQL92) sau versiuni ulterioare (SQL-1999, SQL-2003, SQL-2006)

Modelul obiect obiect-orientat

Modelul obiect(Object Model) este un concept unificator-Necesar in domenii n care se manipuleazdate de tipuri complexe:-proiectarea sistemelor de calcul: programare, hardware, interfete-proiectarea asistat de calculator (CAD-CAM)-sisteme de informaii geografice-fizic, biologie, medicin i alteleStrategii pentru dezvoltarea sistemelor de gestiune a bazelor dedate obiect-orientate (SGBDOO):-Extinderea unui limbaj de programare obiect-orientat cu capaciti de administrare a obiectelor persistente: sistemul GemStone (extinde Java si C++)-Extinderea unui limbaj de programare relaional cu capaciti de orientare spre obiecte. Exemplu: limbajul OQL (Object Query Language) (sau Object SQL), Exist mai multe astfel de sisteme, cum sunt: Ontos, Versant, O2.-Dezvoltarea unui limbaj obiect-orientat pentru baze de date complet nou: SIM (Semantic Information Manager). Dificultati:-Complexitate in dezvoltare a bazei de date i a aplicaiilor-Interogarile trebuie s fie prevzute explicit in structura datelor-Utilizare SGBDOO: cam 5% din sistemele de gestiune actuale

Modelul obiect obiect-relational

Modelul obiect-relaional(Object-Relational Model) reprezint extinderea modelului relaional cu caracteristici ale modelului obiectModelul obiect-relaional pstreaz structurarea datelor n relaii, si, in plus:permite definirea unor noi tipuri de date, ca domenii ale atributelorpermite extinderea tipurilor de date prin motenireSistemele de gestiune a bazelor de date obiect-relaionale (SGBDOR) se realizeaz prin extinderea sistemelor relaionale, de regula n mod gradat, adugndu-se de la o versiune la alta ct mai multe caracteristici posibile ale modelului obiectAceasta abordare asigur rularea n continuare a aplicaiilor relaionale existente n noile versiuni de sisteme SGBDOR, ceea ce permite productorilor s-i pstreze clienii i domeniile de utilizareLimbajele de programare pentru SGBDOR sunt implementri de standarde mai recente ale limbajului SQL: SQL3 (SQL-1999), SQL-2003, SQL-2006

Capitolul 2: Baze de date rela relaionale

Relaii, atribute, domenii; schema relaieiReprezentarea relaiilor prin tabeleLimbajul SQL:Convenii lexicaleExpresii, operatori, functiiInstructiuni de definire a datelor: CREATE, ALTER, DROPInstructiuni de manipulare a datelor: SELECT, INSERT, UPDATE, DELETEConstrngerile de integritate ale relaiilorConstrngeri de domeniuConstrngeri de tuplu: cheia primarchei secundareConstrngeri de integritate referenial chei strineIndexarea relaiilorIndexul primarIndexuri secundare

Relaii Atribute DomeniiModelul relaional: E.F.Codd, 1970 IBMO baz de date relaional este compus dintr-o mulime finit de relaiifiecare relaie reprezinta o mulime (tip) de entitati sau o mulime (tip) de asocierifiecare relaie este unica intr-o baza de dateo relaie se defineste prin intermediul atributelor saleAtributeleunei relaii corespund atributelor tipului de entitate sau de asociere pe care l reprezint relaia respectivfiecare atribut are un nume (Ai) i un domeniu de definiie D(Ai)pentru o entitate data, un atribut poate lua o singur valoare (scalar) Atributele pot fi: simple (un element) sau compuse (o submulime de atribute)Domeniu: o mulime de valori D = {di| i = 1,, n }, definit printr-o specificare de tip, unde:D este numele domeniuluidi este un element al domeniului care satisface anumite constrngeri Elementele domeniilor sunt atomice (indivizibile)O valoare speciala, null, poate apartine oricarui domeniu (inseamna lipsa de informatie sau valoare necunoscuta)

Schema rela relaieiSchema relaiei: descriere a unei relaii (tipul, intensiunea relaiei)Schema relaiei: R(A1,A2,...Ai,...An), unde:Reste numele schemei relaieilista ordonat a atributelor sale A1,A2,...Ai,..Anfiecare atribut Ai definit pe domeniul su de definiie, D(Ai)Gradul relaiei: numrul de atribute ale schemei acelei relaii (n)Exemplu: STUDENTI (Nume, Prenume, DataNasterii, Adresa, Facultatea)O relaie r definita prin schema R(A1,A2,...Ai,...An) este:o mulime finita de n-tupluri ttuplul t este o list ordonat de n valori: t = , unde 1 i nvieste o valoare a atributului Ai, vi D(Ai)Relaia r(R): r este variabila, instanta a schemei (tipului) RValoarea variabilei: starea sau extensiunea relaieiNumarul de tupluri ale unei relaii: cardinalitatea relaieiFiecare tuplu este unic intr-o relaie (nu exista tupluri duplicat)Corespondenta: relaiemulime de entitati (sau de asocieri); tuplu entitaten mod curent: se foloseste R atat pentru schema cat i pentru relaia insasi

Reprezentarea relaiilor prin tabeleUn tabel(table) = reprezentarea grafica unei relaii; compusdin:Numele tabelului -identic cu numele relaieiColoanele corespund atributelor relaieiCapul tabelului-contine numele atributelor (coloanelor) schema relaieiO mulime de linii, fiecare linie corespunznd unui tuplustarea relaieiValori ale atributelor fiecarui tupluExemplu: Tabelul care reprezinta relaia (starea relaiei) STUDENTI

Numele tabelului STUDENTI Coloane atribute Valori absolute NumePrenumeData nasteriiAdresaFacultatea

AnghelescuOctavian1999BucurestiETTI

BeldimanCristina1998BucurestiETTI

BoeruMarius1999nullETTI

Tabelul sugereaz ordonarea atributelor (coloanelor) i a tuplurilor (liniilor) ceea ce nu corespunde modelului matematic (relaie = mulime de tupluri)

Afiarea tabelelorSGBD-urile oferinstrumnente de proiectare i afisare a tabelelorDe exemplu, afiarea tabelului Employees din baza de date Northwind folosind toolset-ul SQL Query Analyser din Microsoft SQL Server

Ordonarea valorilor atributelor n tupluriDin punct de vedere logic, ordinea valorilor atributelor ntr-un tuplu nu conteaza; aceast structurare poate fi exprimat prin urmtoarele definiii:Schema relaiei: R = {A1,A2, ...Ai,...An} (o mulime de atribute)Relaia r(R): o mulime de n-tupluri t, unde:fiecare tuplu t este o mulime de n perechi ordonate ,unde 1 i n,t = {,,..., ...}vi este valoarea atributului Ai, vi D(Ai)Observaii asupra celor doudefiniii:A doua definiie a relaiei este mult mai general decat prima definitiePrima definiie simplific notaiile i corespunde reprezentrii prin tabel a relaiei i de aceea va fi folosit n continuare destul de frecventn implementrile reale, exist o anumit ordine a valorilor atributelor memorate n fiiere, dar aceasta nu este relevant din punct de vedere logic

Limbajul SQL

Fiecare SGBDR implementeaz un dialect al limbajului SQL, ceea ce micoreaz gradul de portabilitate a aplicaiilor n diferitele implementri ale limbajului SQL pot s lipseasc unele comenzi prevzute n standard, dar pot exista extensii specificeSGBD-ului respectiv

Caracteristicile generale ale limbajului SQL

Limbajul SQL foloseste reprezentarea prin tabele a relaiilor, reprezentare care este mai simpl i mai intuitiv (foloseste termenii tabel, linie, coloan) Limbajul SQL cuprinde:Componenta de descriere a datelor (Limbaj de Descriere a DatelorLDD) Componenta de manipulare a datelor (Limbaj de Manipulare a Datelor LMD)Alte componente: controlul tranzactiilor, controlul securitatii,protectia datelor etc. Limbajul SQL2 este un limbaj neprocedural: o instruciune SQL2 specific ce informaii trebuie s fie setate sau obinute, nu modul (procedura) n care se opereaz limbajul SQL2 nu conine instruciuni de control al fluxului execuiei (instruciuni ca for, while, if, etc) Standardul SQL3 prevede instructiuni de control i crearea de tipuri definite de utilizator, fiind implementat n SGBD-urile obiect-relaionalePentru aplicaiile de baze de date, s-au dezvoltat extensii procedurale ale limbajului SQL, biblioteci i interfee de programare care integreaz instruciunile SQL

Structura lexicala a limbajului SQL

O instruciune SQL (statement) este o secven de elemente - de regula terminat cu semnul punct i virgul (;)Fiecare instruciune SQL conine o comand SQL(command), care specific ce aciune se efectueaz, urmat de alte elemente, care specific operaii, clauze, parametri etc. Exemplu: SELECT * FROM ANGAJATI;Elementele (tokens) instruciunilor SQLcuvnte cheie (key words): CREATE, INSERT, SELECT, WHERE, FROMetc.identificatori (identifiers): simpli -numai caractere alfa-numerice i underscore(_): ANGAJATI, Nume, Prenume etc.delimitati (quoted) -pot contine orice caracter, foloseste ghilimele : Nume, Prenumeetc. constante (literali): 1000, 100.5, Ionescu, NULLcaractere speciale: *, ., ;Spaiile albe (whitespaces) separa elementele: spaiu, linie nou, tabO instructiune se poate scrie pe una sau mai multe linii, iar ntr-o linie se pot introduce una sau mai multe instructiuniLimbajul SQL este case-insensitive(nu deosebeste literele mici de cele mari) cu exceptia identificatorilor delimitati (quoted) care sunt case-sensitive

Expresii i operatori n limbajul SQL O expresie SQL const dintr-unul sau mai muli operanzi, operatori i parantezeParantezele se pot folosi pentru a preciza o anumit ordine a operaiilor, dac aceasta este diferit de ordinea implicit data de precedenta operatorilor.Un operand poate fi:numele unei coloane n acest caz se foloseste valoarea memorata n acea colona intr-una sau mai multe linii ale tabeluluio constant (literal)valoarea returnat de o functieUn operator SQL este exprimat prin:unul sau mai mai multe caractere speciale; exemple: +, -, *, /, %, 1000; SELECT * FROM city WHERE (Population > 200000) AND (CountryCode=ROM);

Clauze secundare funcii agregatClauzele secundare sunt: ORDER BY, GROUP BY, HAVINGClauza ORDER BYspecific numele atributului dup care se face ordonarea liniilor tabelului rezultatSELECT * FROM city order by CountryCode;Ordonarea n ordine cresctoare: parametrul ASC (implicit); n ordine descrescatoare: DESC. Exemplu:SELECT * FROM city order by CountryCode DESC;Clauzele GROUP BY i HAVING se folosesc mpreun cu funciile agregatFunciile agregat definite n limbajul SQL2 sunt urmtoarele:FunctiaValoarea returnata

COUNTNumarul de linii al tabelului rezultat

SUMSuma valorilor din coloana data ca argument

MAXValoarea maxima din coloana data ca argument

MINValoarea minima din coloana data ca argument

AVGValoarea medie din coloana data ca argument

Funcii agregat Exemple de funcii agregat fr clauze secundare: SELECT COUNT(*) FROM city; --returneaza numarul de linii din tableSELECT COUNT(col) FROM city; --return nr de valori dif de null din acea col.SELECT MAX(Population) FROM city;SELECT MIN(Population) FROM city;SELECT AVG(Population) FROM city; Clauza GROUP BY se folosete pentru gruparea rezultatelor funciilor agregat dupa valoarea uneia sau mai multor coloane. Exemplu:SELECT CountryCode, AVG(Population) FROM city GROUP BY CountryCode; Clauza HAVING nlocuiete clauza WHERE atunci cnd n condiia care trebuie sfie ndeplinitse folosesc funcii agregat. Exemplu: SELECT CountryCode, AVG(Population) FROM city GROUP BY CountryCode HAVING AVG(Population) >800000;

Instruciunea INSERTInstruciunea INSERT se folosete pentru introducerea datelor n tabele i are urmtoarea sintax:INSERT INTO nume_tabel(col1,col2,...coln) VALUES(val1,val2,...valn);ntre valori i numele de coloane trebuie s existe o coresponden pozitional. De exemplu:INSERT INTO SECTII (Numar,Nume,Buget) VALUES (1,Productie,40000);Lista de coloane poate s lipseasc dac se introduc valori n toate coloanele tabelului i n aceast situatie:ordinea valorilor introduse trebuie s respecte ordinea coloanelor tabeluluiordinea coloanelor provine din ordinea de definire a atributelorprin instruciunea CREATE TABLE, precum i din operaiile ulterioare de alterare a tabeluluiordinea coloanelor se poate afla prin instruciunea DESCRIBE nume_tabel.De exemplu, introducerea unei linii cu toate valorile n tabelulANGAJATI (IdAngajat,Nume,Prenume,DataNasterii,Adresa,Functia,Salariu) se poate face cu instruciunea:INSERT INTO ANGAJATIVALUES(100,Mihailescu, Mihai,1950-04-05,Craiova,Inginer, 3000);

Instruciunile UPDATE si Instruciunea UPDATEpermite actualizarea valorilor coloanelor (atributelor) din una sau mai multe linii ale unui tabel i are sintaxa:UPDATE nume_tabel SET col1 = expr1 [, . . . n] [WHERE conditie];Clauza WHERE: actualizarea valorilor se efectueaza numai asupra acelor linii care ndeplinesc condiia dat. Exemplu:UPDATE ANGAJATI SET Adresa = BucurestiWHERE Nume = Popescu;Dac este omis clauza WHERE, vor fi modificate valorile coloanelor din toate liniile tabelului.Instruciunea DELETEpermite tergerea uneia sau mai multor linii dintr-un tabel i are sintaxa:DELETE FROM nume_tabel [WHERE conditie];Din tabel se terg acele linii care ndeplinesc condiia dat n clauza WHERE. Dac este omis clauza WHERE, vor fi sterse toate liniile din tabel.Exemplu:DELETE FROM ANGAJATI WHERE Nume =Ionescu;

Constrngeri de integritate Constrngerile de integritate (integrity constraints) sunt reguli care se impun pentru ca datele memorate s corespund ct mai bine celor din realitate: se definesc la proiectarea bazei de date trebuie s fie respectate de orice stare a acesteia Clasificare duplocul unde se definesc: constrngeri de coloana i constrngeri de tabel Clasificare dupnumrul de relaii implicate: constrngeri intra-relaie i constrngeri inter-relaii. Constrngerile intra-relaie -reguli care se impun n cadrul unei singure relaii; sunt de trei categorii: Constrngeri de domeniu -condiii ce se impun valorilor domeniilor atributelor Constrngeri de tuplu -condiii ce se impun tuplurilor unei relaii prin chei (primare sau secundare) Constrngeri impuse prin dependene de date (dependene funcionale, multivalorice sau de jonciune); acestea sunt constrngeri intre valorile atributelor dintr-o relaie Constrngerile inter-relaii -reguli care se impun ntre dou sau mai multe relaii; asigura integritarea referenial prin intermediul cheilor strine

Clasificare din punct de vedere al modului de definire i de verificare a respectrii constrngerilor: inerente, implicite i explicite. Constrngerile inerente sunt cele ale modelului de date nsui, care nu trebuie s fie definite deoarece sunt incluse n sistemul de gestiuneDe exemplu: n modelul relaional constrngerea ca valoarea fiecrui atribut s fie atomic (indivizibil) este o constrngere inerent Constrngerile implicite sunt reguli specifice fiecrui sistem de gestiune; acestea se definesc de ctre proiectant, iar sistemul de gestiune le verific ileimpune automatExemple: connstrngerile de domeniu, constrngerile de tuplu i constrngerile de integritate referenial sunt constrngeri implicite. Constrngerile explicite sunt constrngeri suplimentare, specifice bazei de date respective; proiectantul definete constrngerile explicite precum i procedurile de verificare ale accestora (funcii, proceduri stocate, triggere)Exemple:dependenele de date care nu sunt determinate de cheile relaiilor

Constrngeri de domeniu Constrngerile de domeniu: constrngerea NOT NULL, constrngerea de valoare implicit (DEFAULT), constrngerea de verificare (CHECK) Constrngerea NOT NULLnsemna c atributul respectiv nu poate lua valoarea NULL n nici un tuplu al relaiei. Valoarea NULL a unui atribut ntr-un tuplu semnific faptul c valoarea acelui atribut nu este cunoscut pentru acel tuplu. Exemple:nu se cunoaste deloc data de nastere a unei personalitati istorice; nu se cunoate valoarea unui atribut n momentul inserarii tuplului, dar aceasta va fi cunoscuta i completat ulterior La crearea unui tabel opiunea NULL este implicit (nu se specific nimic), sau se poate introduce explicit; optiunea NOT NULL se introduce explicit.Optiunile NULL i NOT NULL se introduc ca i constrngeri de coloana n instructiunea SQL CREATE TABLE. Exemplu:CREATE TABLE ANGAJATI (Nume varchar(20) NOT NULL,Prenume varchar(20) NOT NULL,DataNasterii date NULL,Functie varchar(20),Salariu numeric);

Constrangerea de valoare implicit a unui atribut (DEFAULT): dac la inserarea unui tuplu nu se specific valoarea unui atribut, atunci: atributul primete valoarea implicit (DEFAULT), dac a fost definitatributul primete valoarea NULL, dac nu a fost definit valoare implicit, dar sunt admise valori NULL se genereaz o eroare, dac nu a fost definit o valoare implicit i nici nu sunt admise valori NULL. Exemplu:

CREATE TABLE STUDENTI (Nume varchar (20) NOT NULL,Prenume varchar (20) NOT NULL,Tara varchar (20) DEFAULT Romania) ;Constrngerea de verificare (CHECK) pentru verificarea valorilor atributelor printr-o conditie care trebuie sa ia valoarea TRUE. Se introduce ca o constrangere de tabel n instructiunea CREATE TABLE:

[CONSTRAINT nume_constrangere] CHECK (conditie); Exemplu:CREATE TABLE ANGAJATI (Nume varchar(20) NOT NULL,Prenume varchar(20) NOT NULL,Salariu numeric,CONSTRAINT Verificare_Salariu CHECK (Salariu >= 1500 ));MySql 5.0 nu face verificarea CHECK, chiar daca admite acest cuvnt cheie

Constrngeri de tuplu O relaie = mulime de tupluri tuplurile unei relaii trebuie s fie distincte (nu pot exista dou sau mai multe tupluri identice) Pentru ca tuplurile unei relaii s fie distincte se folosete cte o cheie primar (primary key) n fiecare relaieO cheie primarPK a unei relaii este un atribut (simplu sau compus) al acelei relaii care are proprietatea de unicitate, adic fiecare valoarea cheii primare este unicn acea relaie. Aceasta nseamn c:Nu exist dou tupluri distincte (diferite) care s aib aceeai valoare a cheii primare (sau combinaie de valori) pentru orice stare a relaiei, adic:

ti[PK] tj[PK] dac i j, unde ti i tj sunt 2 tupuri diferite ale relaieiProprietatea de unicitate a cheii primare este o constrngere deintegritate a tuplurilor: fiecare tuplu poate fi identificat n mod precis i se pstreaz integritatea acestuia, dacse cunoate valoarea cheii sale primare Cheia primar este o constrngere implicit: se definete de proiectant la crearea tabelului ieste verificatde SGBD (s nu existe duplicate, etc)Cheia primar mai are urmtoarele restricii:Este ireductibil: nu exist o submulime proprie nevid a cheii PK care s aib proprietatea de unicitate Nici o valoare a atributelor cheii primare nu poate fi modificat prin operaii de actualizare(UPDATE)Nu se admit valori de NULL pentru nici unul dintre atributele cheii primare

Chei primar primare naturale i artificiale Se pot defini chei primare naturalesau chei primare artificile, cu condiia ca acestea s ndeplineasc condiia de unicitateO cheie primar naturaleste un atribut (simplu sau compus) al relaiei:reprezint o proprietate a tipului de entitate (sau asociere) reprezntat de acea relaieare n mod natural valori unice: nu exist dou tupluri cu aceeai valoare a cheii primare, deoarece nu exist dou entiti cu acceai valoare a proprietii respectiveO cheie primar artificialeste un atribut (de obicei simplu) care nu reprezinto proprietate a tipului de entitate sau asociere reprezentat de relaie, ci se adaug n schema relaiei special pentru identificarea unic a tuplurilorExemplu:ANGAJATI(IdAngajat,CNP,Nume,Prenume,DataNasterii,Adresa,Functia, Salariu):IdAngajat este o cheie primarartificialAr putea fi definite i chei primarenaturaleprin atributesimple sau compuse care auproprietatea de unicitate n anumite condiii:atributul simplu {CNP}valabil numai pentru persoanele din Romaniaatributul compus {Nume, Prenume, DataNasterii, Adresa}Din motive de eficien a operaiilor de identificare a tuplurilor, se prefer chei primare cu un numr ct mai mic de atribute (atribut simplu)

Modul de asigurare a unicitii valorii cheii primare artificiale depinde de sistemul SGBD folosit. De exemplu: n Microsoft SQL Server se pot obine valori unice ale cheii primare folosind parametrul IDENTITY, care asigur incrementarea valorii atributului cheii la introducerea fiecrei linii noi n sistemele Oracle se pot genera chei artificiale folosind obiecte SEQUENCE; un obiect SEQUENCE geneaza un numar unic la fiecare apel al metodeiNEXTVAL n MySQL, se foloseste parametrul AUTO_INCREMENT pentru generareanumerelorunicepentru cheile primare.SGBD-urile interzic introducerea liniilor (tuplurilor) care au valoriidentice ale cheilor primare

Definirea cheii primare Cheia primar se defineste prin instructiunea CREATE TABLE ca o constrngere de tabel sau ca o constrngere de coloanDefinirea cheii primare ca o constrngere de tabel:[CONSTRAINT nume_constr] PRIMARY KEY (lista_atribute)Exemplu:CREATE TABLE SECTII ( IdSectieint, Nume varchar(50) NOT NULL, Buget numeric, CONSTRAINT PK PRIMARY KEY (IdSectie));Dac cheia primar este simpl (format dintr-un singur atribut), ea se poate specifica i ca o constrngere de coloan; exemplu:CREATE TABLE ANGAJATI ( IdAngajatint PRIMARY KEY AUTO_INCREMENT, Nume varchar(20)NOT NULL, Prenume varchar(20) NOT NULL, DataNasterii Date, Adresa varchar(50), Salariu numeric);

Superchei, chei candidat O supercheie(superkey) este o submulime SK de atribute ale unei relaii care prezint proprietatea de unicitate (orice combinaie de valori ale atributelor supercheii este unic pentru orice stare a relaiei)Dac se cunoate valoarea (combinaia de valori ale atributelor) supercheii, atunci acel tuplu poate fi identificat n mod unicOrice relaie are cel puin o supercheie, care este mulimea tuturor atributelor saleO cheie candidat(candidate key) este o supercheie ireductibil:Unicitate: nu exist dou tupluri diferite ale relaiei care s conin aceeai combinaie de valori ale atributelor cheii CK; Ireductibilitate: nu exist nici o submulime proprie, nevid a cheii CK care s aib proprietatea de unicitate O cheie candidat poate fi simpl (un atribut), sau compus (mai multe atribute)Exemplu: ANGAJATI (IdAngajat, CNP, Nume, Prenume, DataNasterii, Adresa, Functia, Salariu)

SK1 = {IdAngajat, CNP, Nume, Prenume, DataNasterii, Adresa,Functia, Salariu}SK2 = {CNP, Nume, Prenume, DataNasterii, Adresa}; SK3 = {IdAngajat, CNP}CK1={Nume, Prenume, DataNasterii, Adresa}; CK2 = {CNP}; CK3 ={IdAngajat}

Chei candidate, primare i i secundare Atunci cnd exist mai multe chei candidate (cu proprietile de unicitate i ireductibilitate), una dintre ele se alege ca i cheie primar, iar celelalte chei se pot defini ca ichei secundareCheia primar este o cheie candidat aleas (desemnat) de proiectant la definirea tabeluluiO cheie secundar (alternativ, unic) (secondary, alternate, unique key) este o cheie candidat definit de proiectantCheile secundare se definesc n instruciunea CREATE TABLE folosind specificatorul UNIQUE [KEY] n loc dePRIMARY KEY Alegerea cheii primare dintre mai multe chei candidate este arbitrar, dar, din motive de eficien, se alege cheia cu cel mai mic numr de atributeCheile secundare se deosebesc de cele primare prin:Pot fi modificate prin instruciuni UPDATE, dac se respect proprietatea de unicitate Cheile secundare compuse admit valori NULL pentru unele din atributele lor

Constrngeri inter-relaiiAsocierile(relaionships) 1:N ntre dou mulimi de entiti (din modelul Entitate-Asociere) se realizeaz n modelul relaional prin chei strineExemplu: Pentru a realiza asocierea 1:N dintre relaiile SECTII i ANGAJATI, se adaug n relaia ANGAJATI cheia strin IdSectie, care reprezint identificatorul (numrul) seciei n care lucreaz angajatul respectiv:ANGAJATI (IdAngajat, Nume, Prenume, DataNasterii, Adresa, Salariu, IdSectie) SECTIIIdSectieNumeBuget

1Productie400000

2Proiectare300000

3Cercetare200000

4Documentare100000

ANGAJATIIdAngajatNumePrenumeDataNasteriiAdresaFunctiaSalariulIdSectie

1IonescuIon1960.01.05BucurestiInginer40001

2PopaPetre1965.02.07BucurestiTechnician32001

3CarolAna1961.03.06BucurestiSecretara20002

4MarinRadu1970.03.18Bucurestiinginer40003

Cheia strinFie dou relaii R1 i R2, ntre care exista o asociere cu raportul 1:N.O cheie strin(foreign key) este o submulime FK de atribute ale relaiei R2 care refer cheia CK din relaia R1 i satisface urmtoarelecondiii:atributele cheii strine FK sunt definite pe domenii compatibilecu cele ale atributelor cheii candidate CK a relaiei R1valorile atributelor FK ntr-un tuplu din relaia R2, fie sunt identice cu valorile atributelor CK ale unui tuplu oarecare din starea curent a relaiei R1, fie sunt NULLDou domenii sunt compatibile dac ele sunt compatibiledin punct de vedere al tipului de date i compatibile semantic (are sens sfie comparate)n limbajul SQL verificarea domeniilor se rezum la verificarea tipurilor de date, iar compatibiltatea semantic trebuie s fie asigurat de proiectantCheia strin reprezint o constrngere referenial ntre cele 2 relaiiRelaia referit (R1) relaie printe, relaia care refer (R2) relaie fiuCheia strin se specific n comanda CREATE TABLE sau ALTER TABLE: [CONSTRAINT nume_constr] FOREIGN KEY (cheie_strina) REFERENCES relaia_referita (cheie_candidata)Exemplu: CREATE TABLE ANGAJATI ( IdAngajatint PRIMARY KEY, Numevarchar(20) NOT NULL, Prenumevarchar(20) NOT NULL, IdSecieint, CONSTRAINT FK FOREIGN KEY (IdSectie) REFERENCES SECTII(IdSectie));

Mentinerea integritii refereniale a relaiilor Integritatea referenial(referential integrity) este proprietatea bazei de date prin care orice cheie strin: fie are o valoare care se regsete printre valorile cheii candidate referitefie are valoarea NULLPentru meninerea integritii refereniale trebuie sfie inpuse restrictii operaiilor de modificare a strii relaiilor (INSERT, DELETE, UPDATE)Restriciile care se impun operaiilor de modificare a relaiilor depind de rolul relaiei (relaie care refer, relaie referit, sau poate avea ambele roluri)Operaia INSERT:ntr-o relaie care nu refer alt relaie, inserarea se poate face fr restriciintr-o relaie care refer (care conine o cheie strin): SGBD-ul permite introducerea unui tuplu nou numai dac: (a) valoarea cheii strine a tuplului nou este NULLsau (b) exist o valoare acheii referite egalcu valoarea cheii strine a tuplului nouOperatia DELETE:ntr-o relaie care nu este referittergerea se poate face frrestriciintr-o relaie referitse admite: tergere restricionat, tergere n cascad, anularea (SET NULL) a cheilorstrine care refereau tuplul terstergerea restricionat interzice tergerea unui tuplu din relaia referit dac acesta este referit de un tuplu din relaia care o refer

tergerea n cascad permite tergerea unui tuplu din relaia referit; dac tuplul ters era referit de unul sau mai multe tupluri, atunci se terg i acestea din relaia care o refer; dac tuplurile terse din relaia care refer sunt, la rndul lor referite de alte tupluridin alte relaii, atunci trebuie s fie terse i acestea, .a.m.d.; se execut deci o tegere n cascad Operaia UPDATE esteo tergere urmat de o introducere, deci restriciile de actualizare reprezint combinaia restriciilor de introducere i de tergeren limbajul SQL se specificopiunile ON DELETE i ON UPDATE constrngerii de cheie strin; valorile posibile ale acestor opiuni sunt:RESTRICTtergerea restricionat (este valoare implicit)CASCADEtergerea n cascad; SET NULL anularea cheilor strine care refereau tuplul tersNO ACTION se admit valori care nu respect integritatea relaionalExemplu:CREATE TABLE ANGAJATI (IdAngajatint PRIMARY KEY,Nume varchar (20) NOT NULL, . . . . . . . . Sectieint,CONSTRAINT FK FOREIGN KEY (Sectie) REFERENCES SECTII(IdSectii) ,ON DELETE CASCADE ON UPDATE RESTRICT);

Indexarea relatiilorTimpul de execuie a operatiilor asupra datelor din relaii depinde de modul de reprezentare a mulimii de elemente (tupluri) ale relaiilorOperaia de cutare a unui element ntr-o mulime se execut mai rapid dac elementele mulimii sunt reprezentate printr-o colecie ordonat, cum sunt liste, arbori, tabele de dispersie (hash table). De exemplu:Timpul de cutare a unui element ntr-o mulime neordonat de N elementeeste proportional cu N: TC= k1* N = O(N)Timpul de cutare al unui element memorat ntr-o structur arbore binar de cutare ordonat dup valoarea etichetei (cheii) de ordonare este TC= k2* log N = O(log N)Un arbore binar ordonat complet cu d niveluri: pe nivelul 0 are 20= 1 nodpe nivelul 1 are 21= 2 noduripe nivelul j are 2jnoduriNr.total noduri N = 20+ 21+ 2j + 2d-1= 2d1 d = log (N + 1)Pentru cutare se parcurg max d pai, deci timpul de cutare TC= k2* log N = O(log N)

Rezult c, dei o relaie nu presupune ordonarea tuplurilor sale, pentru accelerarea operaiei de cutare(SELECT) a unui tuplu se folosesc colecii ordonatei celelate operaii (INSERT, UPDATE, DELETE) se executmai rapid n colecii ordonatePentru inserarea unui tuplu se verific mai nti s nu existe deja un tuplu cu aceeai valoare a cheii Pentru modificarea unui tuplu se caut mai nti tuplul dorit, apoi se fac modificrilePentru tergere se caut mai nti tuplul dorit i apoi se tergeUn index al unei relaii este o structur auxiliar, memorat n baza de date,care permite accesul rapid la tuplurile relaiei prin ordonarea acestora Structuri folosite n indexare: arbori binari de cutare, arbori BTREE, arbori RTREE, tabele de dispersie (HASH) etc.Exista dou categorii de indexuriale unei relaii: un index primar, care determin localizarea tuplurilor n fiierele bazei de date zero, unul sau mai multe indexuri secundare, care nu modific localizarea tuplurilor, dar sunt folosii pentru regsirea rapid a tuplurilor dup valorile unor attribute

Indexul primar Indexul primar(primary index) se definete pe cheia primar a relaiei Fiecare element al indexului primar corespunde unui tuplu al relaiei i elementele sunt ordonate dup valoarea cheii primar PKDe exemplu, pentru o structur arbore binar ordonat a indexului primar al unei relaiicu ckeia primar PK i atributele (A, B, C, ...), un element (nod) Ni este memorat la adresa Lipe hard-disk i conine:Valoarea cheii primare a tuplului (pki), care este i eticheta de ordonare a arborelui Valorile celorlalte atribute ale tuplului (ai, bi, ci, ...)Adresele fiilor (Lj, Lk) (locaiilede memorare pe hard-disk a nodurilor fii)

Structura indexului primar este memorat mpreun cu tuplurile relaiei Operaiile de interogare care se fac dup indexul primar (cheia primar) se execut eficient, fiind o cutare ntr-o mulime ordonat dup acea valoare Exemplu: Care sunt funcia i salariul angajatului cu cheia primara 3?Se caut nodul arborelui care are valoarea etichetei de ordonare(care este i cheia primar a relaiei) egal cu valoarea dat (3)Dup gsirea nodului se extrag valorile atributelor tuplului memorat n acel nodSunt necesari maximum d (log N) pai de cutare (N este nr total de tupluri ale relaiei)Operatiile de interogare care se fac dupvaloarea altor atribute (dect indexul primar) se executmult mai ineficient, fiind o cutare ntr-o mulime neordonat dup acea valoareExemplu: Care sunt funcia i salariulangajatului cu numele Dobre?Pentru cutare se vor parcurge pe rnd toate tuplurile relaiei (memorate n nodurile arborelui - exist astfel de algoritmi de parcurgere) pentru a gsi tuplul (sau tuplurile) cu valoarea atributului Nume egal cu DobreSunt necesari maximum N pai (N este nr total de tupluri ale relaiei)Pentru rezolvarea mai eficient a unor astfel de interogri se defines indexuri secundare pe acele atribute care intervin n clauza WHERE din interogri

Indexuri secundare Un index secundar pe un atribut al unei relaii (secondary index) este o structur ordonat dup valoarea acelui atribut; un element al unui index secundar conine:valoarea atributului indexat (care este etichet de ordonare)adresa (sau adresele) tuplurilor care conin acea valoare a atributului respectivSunt dou categorii de indexuri secundare:unice (UNIQUE) i normaleUn index secundar UNIQUE este definit pe un atribut A (simplu sau compus) al relaiei care ia valori unice(cum este o cheie unic -secundar sau alternativ)Un element (nod) al indexului este compus din valoarea ai atributului indexat A i adresa (Li) a unui singur tuplu care are acea valoare a atributului ADac relaia are N tupluri, indexul va avea M = N elementeIndex secundar normal(care nu este unic -nu are o denumire specific) este definit pe un atribut A care nu ia valori unice (nu este cheie unic)Un element (nod) al indexului este compus dinvaloarea aia atributului indexat A i lista(Li1, Li2, ) a adreselor(pe hard-disk) a tuplurilorti1, ti2, care au valoarea aia atributului ADac relaia are N tupluri, indexul va avea M N elementePentru o structur arbore binara indexului, fiecare nod mai conine i adresele nodurilor fii (stnga, dreapta) (nereprezentate n figura urmtoare)

Exemplu: indexul secundar (cu structur arbore binar) definit pe atributul Nume al relaiei ANGAJATI, al crei index primar este cel dat n figura precedentLa interogarea Care sunt functia i salariul angajailor cu numele Dobre?se parcurge indexul secundar definit pe atributul Nume ise afl adresa unui singur tuplu (L7)La interogarea Care sunt functia i salariul angajatilor cu numele Ionescu?se parcurge indexul secundar definit pe atributul Nume ise afl adresele tuplurilor (L1i L6) care au valoarea atributului Nume egal cu IonescuDac indexul are o structur arbore binar ordonat, se vor executa max (log N) paiUn index secundar nu modific adresa de memorare a unui tuplu (care se afl nindexulprimar), dar conine informaii pentrugsirea rapid a unui tuplu dup valoarea acestui index

Un index secundar se poate crea cu comanda CREATE TABLE (ca o costrngere de tabel), cu ALTER TABLE sau cuCREATE INDEX; ex.:CREATE [optiuni] INDEX nume_index ON nume_tabel(lista_atribute_index);Una din opiunile care se pot introduce n CREATE INDEX este UNIQUEn general,sistemele SGBD adaug:Un index secundar UNIQUE pentru fiecare cheie candidat (definit prin constrngerea UNIQUE KEY) Un index secundar normal pentru fiecare cheie strin; un astfel de index secundar ajut la gsirearapid a tuturor tuplurilor asociate cu o valoare a cheii strine (Care sunt angajaii care lucreaz n secia cu numrul (identificatorul IdSectie) 1?n sistemele SGBD avansate (obiect-relaionale), pot exista i indexuri secundare speciale, cum sunt Indexurispaiale (indexarea obiectelor reprezentate n spaiul bi sau tridimensional)Indexuri de context (indexarea textelor)Indexuri XML (indexarea documentelor XML)Indexurile secundare au avantaje i dezavantaje:Avantaje: accelereazoperaiile de interogare care se fac dupvaloarea indexuluiDezavantaje: ocupspaiu de memorie i consumtimp la actualizarea relaiilor n general, se recomand utilizarea unui numr ct mai mic de indexuri secundare, definite pe atributele care intervin cel mai frecvent n interogri

Capitolul 3: Interogarea bazelor de date

-Limbaje de interogare-Algebra relationala si calculul relational-Operatiile pe multimi ale algebrei relationale-Reuniunea-Intersecia-Diferenta-Produsul Cartesian-Operatiile speciale ale algebrei relationale-Selectia-Proiectia-Jonctiunea-Diviziunea-Interogarea bazelor de date-Interogarea intr-o singura relatie-Interogarea in doua sau mai multe relatii

Limbaje de interogare

Interogarea(query):operaia prin care se obin informatiile dorite dintr-o baz de date, selectate conform unui anumit criteriu (condiie); Limbaje de interogare: abstracte si concrete(reale -implementari in diferite SGBD-uri) Limbaje de interogare abstracte: algebra relationala si calcululrelational Algebra relationala (relational algebra)-const dintr-o mulime de operaii care au ca operanzi relaii, iar rezultatul este tot o relaie Calculul relaional(relational calculus) -bazat pe calculul predicatelor- exprim o interogare definindrezultatul dorit ca expresie de calcul relaional Calculul relational al tuplurilorfoloseste variabile de tuplu(variabile definite pe mulimea tuplurilor unei relaii) Calculul relational al domeniilorfoloseste variabile de domeniu(variabile definite pe domenii de definiie ale atributelor)Cele trei limbaje formale sunt echivalente din punct de vedere al interogarilor Limbajele de interogare reale sunt definite pe baza unuia sau altuia din limbajele de interogare abstracte, sau pe o combinaie a acestora. De exemplu, limbajul SQL2 este n cea mai mare parte bazat pe algebra relaional, dar mai conine i construcii derivate din calculul relaional.

Algebra relaional Algebra relaional (relational algebra)- interogrile sunt expresii compuse din operatii care au ca operanzi relatii si rezultatul este o relatieOperatiile algebrei relationale: operatii pe multimi si operatiispeciale, la care se adauga operatia de redenumire (rename) a atributelor (E.Codd)Operatiile relationale pe multimi acioneaz asupra relaiilor vzute ca mulimi (de tupluri), fr a lua n consideraie compoziia fiecrui tuplu; acestea sunt: Reuniunea Intersecia Diferena Produsul cartezian Operaiilerelaionale specialeiau n consideraie compoziia tuplurilor, formate din valori ale atributelor relaiilor; acestea sunt: Restricia Proiecia Jonciunea DiviziuneaProprietatea de nchidere: operanzii (unul sau doi operanzi) sunt relaii, rezultatul este o relaie; aceast proprietate permite operaii imbricate: proiecia unei jonciuni etc.

Operaia de reuniune Reuniunea(union) a dou relaii compatibile r(R) i s(S):q = r s= { t | t r or t s}Pentru ca r si s sa fie compatibile,trebuie ca: r si s saiba acelasi grad (acelasi numar de atribute) Atributele corespondente (n ordine pozitional) s fie compatibile Tuplurile care aparin ambelor relaii se introduc n relaia rezultat o singur dat (nu se duplic) Relatia rezultat are un numar de tupluri (cardinalitatea) mai mic sau egal cu suma numerelor de tupluri ale celor doi operanziExemplu:ABABABa1 a2a1a23a21 r 1 s 3 rsOperaiile de intersecie i diferen Intersectia (set-intersection) a dou relaii compatibile r(R) i s(S):q = r s = { t | t r and t s}ExempluABABABa1a2a2a23 r s 1 r s

Diferena (set-difference) a dou relaii compatibile r(R) i s(S):q = r -s = { t | t r and t s}ABABABa1a2a1a2311 s r-s r Reuniunea si intersectia sunt comutative si asociative (r s = s r ; r (s t) = (r s) t); diferenta nu este nici nici comutativa, nici asociativa

Operatia de produs Cartesian Produsul Cartesian(Cartesian-Product) a dou relaii r(R) i s(S):q = r x s= { tp | t r and p s}, Q = R SSe presupune c multimile R si S sunt disjuncte, adica R S = Daca atributele din R si S nu sunt disjuncte, atunci (unele):-se pot redenumi (RENAME nume_atribut AS noul_nume_atribut) sau-se pot califica cu numele relatiei careia ii apartin (folosind operatorul punct)Tuplurile relaiei rezultat se obtin prin concatenarea valorilor atributelor fiecrui tuplu din prima relaie cu valorile atributelor tuturor tuplurilor din a doua relaieRelaia rezultat are numrul de tupluri (cardinalitatea) egal cu produsul numarului de tupluri ale relatiilor operandExemplu: ABCDEABCDE 1a10aa1a10a210aa110a r20ba120b10ba110b s 2a10a210a220b210br x sExprimarea operatiilor pe multimi in SQL Reuniunea:SELECT lista_coloane1 FROM tabel1 [WHERE condiie1]UNIONSELECT lista_coloane2 FROM tabel2 [WHERE condiie2];Exemplu (MySQL, Intreprindere): SELECT Nume, Prenume, Adresa FROM FURNIZORIUNIONSELECT Nume, Prenume, Adresa FROM CLIENTI;Afiseaza numele tuturor furnizorilor si al clientilor, precum si adresa acestora Intersectia:SELECT lista_coloane1 FROM tabel1 [WHERE condiie1]INTERSECTSELECT lista_coloane2 FROM tabel2 [WHERE condiie2]; Diferenta:SELECT lista_coloane1 FROM tabel1 [WHERE condiie1]MINUSSELECT lista_coloane2 FROM tabel2 [WHERE condiie2]; Produsul Cartesian: SELECT*from R,S;SELECT lista_col_R, lista_col_S from R, S;Exemplu (MySQL, Intreprindere): SELECT * FROM ANGAJATI, SECTII;SELECT IdAngajat, Nume, Prenume, DataNasterii, Adresa, Functia,Salariu, ANGAJATI.IdSectie, SECTII.IdSectie, Denumire, BugetFROM ANGAJATI< SECTII;Produsul Cartesian este implementat in toate SGBD-urile (instructiunea SQL SELECT)In sistemul Oracle sunt implementate toate operatiile pe multimiIn alte SGBD-uri nu sunt implementate toate operaiile pe mulimi; in SQL Server 2000 si in MySQL 5.0 nu sunt implementate operaiile INTERSECT i MINUS

Operaia de selecie Selecia (sau restricia select, restriction) ntr-o relatie r(R) -definita astfel:p(r) = {t | t rand p(t)}unde p, predicatul selectiei, este o expresie logic compus din termeni conectati prin operatorii and, ornot (i, eventual, paranteze) Fiecare termen este o valoare logic obinut ca rezultat al unei operaii de comparaie de forma: op sau op , undeOp este un operator de comparatie aritmetic (=, , >, . 10000000;

Operatia de proiecie Proiectia (project) pe atributele A1, A2, .. Ak intr-o relatie r(R) se noteaz:PA1, A2, Ak(r), unde A1 R, A2 R, Ak RRezultatul este o relatie cu k atribute, cele din lista dataDaca {A1, A2, Ak} nu contine o supercheie, pot sa apara tupluri duplicat; teoretic, tuplurile duplicat se elimina, dat fiind ca rezultatul este o multimeExemplu A BCACAB 10111r 2011=130112 4022 A.C (r)

-In limbajul SQL proiectia se exprima astfel:SELECT DISTINCT A1, A2, Ak FROM RDaca nu se introduce parametrul DISTINCT, nu se elimina tuplurile duplicatExemplu (MySQL, WORLD): SELECT DISTINCT CountryCode FROM City ;

Operaia de jonciune naturalJonciunea natural (natural join) combin tuplurile din doua relatii Fie multimile de atribute: A = {A1,A2,...Am} , B= {B1,B2,...Bn}, C={C1,C2,Ck} si doua relatii r(R) si s(S), unde:R={A, B}, S = {B, C} deci atributele R S = B = {B1,B2,...Bn} sunt comune celor dou relaii Jonciunea natural este o relatie q = r >< s = A,B,C (r.B1=s.B1 AND r.B2=s.B2 AND r.Bn = s.Bn)(r x s) Atributele comune R.B si S.B trebuie s fie compatibile n cele doua relatii; dacsunt compatibile, ele se consider identice chiar dac au denumiri diferite, si n reuniunea atributelor R S se introduc o singur data Cazul cel mai frecvent de jonctiune naturala: intre doua relatii associate (1:N), atributul comun fiind cheia straina cheia primara (candidate) referitaExemplu 1: r >< s = A,B,C (r.D=s.D) (r xs)ABCD DEABCDE1a a1a a ba4b c4b a da 2b e2b r s r>< s In SQL trebuie sa fie introduse explicit lista atributelor rezultatului si conditiile de egalitate ale atributelor comune:SELECT A,B,C,R.D,E FROM R, S WHERE R.D = S.D; Exemplul 2: ANGAJATI>= 2000(ANGAJATI)Instruciunea SQL:SELECT Nume, Prenume FROM ANGAJATI WHERE Salariu >= 2000;

Exemplul 2: (MySQL -WORLD): Care sunt numele si populatia oraselor din tara cu codul ROM?Expresia de algebr relaional: q = Name, Population CountryCode=ROM(city)Instructiunea SQL:SELECT Name, Population FROM city WHERE CountryCode=ROM';

Exemplul 3: Fie relaia ANGAJATI i interogarea: Care sunt numele, prenumele si adresa angajailor care lucreaza in sectia numarul 1?.Expresia de algebr relaional: q = Nume, Prenume, Adresa IdSectie =1(ANGAJATI)Instructiunea SQL:SELECT Nume, Prenume, Adresa FROM ANGAJATI WHERE IdSectie=1;

Interogri n dou sau mai multe relaii Daca atributele de proiecie i atributele din condiia de interogare nu aparin unei singure relaii, pentru rezolvarea interogrii trebuie s fie folosite toate acele relaiile care, mpreun, conin atributele i asocierile necesare Conceptual, o astfel de interogare se rezolv astfel:se construieste mai nti o relaie care s conin toate atributele implicate prin combinarea relaiilor necesare, folosind operaii de produs cartezian sau jonciuni;in relatia obtinuta se aplica o selectie (restrictie) (cu condiia de interogarep);apoi se face proiecia (pe atributele de proiecie). Expresia generala de algebra relationala a interogarii este: q= lista_atribute p(r xsx t...) Daca intre relatiile din produsul cartesian exista atribute comune care trebuie sa aiba valori egale (de regula, perechile cheie strin -cheie candidata) atunci se potface operaii de jonciune: q= lista_atribute p1 AND conditii-join(rxsxt...)= lista_atribute p1 (r >< s >< t...) interogare poate conine una sau mai multe subinterogri In limbajul SQL, o interogare se exprima prin instructiuni SELECT in care: Clauza WHERE combina atat conditiile impuse valorilor atributelor cat si conditiile de jonctiuni Jonctiunile se pot specifica i n clauza FROM (cu INNER JOIN, OUTER JOIN)

Exemplu: interogare n dou relaii Fie interogarea: Care sunt numele, prenumele, funcia, salariul i denumireaseciei n care lucreaz angajaii? Expresia de algebr relaional este:q = Nume, Prenume,Salariu, Denumire (ANGAJATI > script_name.sql), se importa scriptul (de ex. in Msql Workbench), care va desena diagrama logica a bazei de date

Proiectarea relatiilor normalizate in prima forma normala O relaie este normalizat n prima form normal (FN1) dac fiecare atribut ia numai valori atomice i scalare din domeniul su de definiie.O situaie frecvent ntlnit este aceea n care un atribut poate lua mai multe valori pentru fiecare entitateDe exemplu, n mulimea de entiti PERSOANE (Nume, Prenume, Adresa, NrTelefon) atributul NrTelefon poate lua mai multe valori (numrul telefonului de acas, al telefonului de la birou, al telefonului mobil, etc)Relaia n care un atribut poate avea valori multiple (un vector de valori) este o relaie nenormalizat, care nu este admis de SGBD relaionale Transformarea unei relaii nenormalizate n relaie normalizat n prima forma normal (FN1): se nlocuiete atributul care ar putea avea valori multiple cu cte un atribut pentru fiecare din posibilitile existente: Exemplu: PERSOANE (IdPersoana, Nume, Prenume, Adresa,TelefonAcasa, TelefonBirou, TelefonMobil)se nlocuiete atributul care ar putea avea valori multiple cu o nou relaie care refer relaia iniial printr-o cheie strin. Exemplu: PERSOANE(IdPersoana, Nume, Prenume, Adresa), TELEFOANE(NrTelefon, IdPersoana, Descriere)Exemple de date in tabele cu diferite tipuri de asocieri

Proiectarea fizica a bazelor de date Fiecare SGBD ofer o varietate de opiuni de organizare a fiierelor i a modului de acces la datele stocate: indexuritipuri de fisiere gruparea nregistrrilor corelate n aceleai blocuri pe disc (clustering) Proiectarea fizic a bazei de date este procesul de alegere a acestor structuri de memorare i de acces la fiierele bazei de date, pentru a obine performane ct mai bune pentru SGBD-ul ales, pentru ct mai multe din aplicaiile proiectate Parametrii generali de alegere a opiunilor proiectului fizic al bazei de date:Timpul de rspuns: intervalul de timp dintre lansarea n execuie a unei tranzacii i primirea rspunsului la acea tranzaciiUtilizarea spaiului de memorare: dimensiunea spaiului pe disc utilizat de fiierele bazei de date i de structurile de acces la dateCapacitatea tranzacional (transaction throughput): numrul mediu de tranzacii care pot fi prelucrate pe minut de ctre sistemul de baze de date

Implementarea bazelor de date n faza de implementare a bazelor de date relaionale:Se creeaz obiectele principale ale bazei de date (tabele, vederi, indexuri), pe baza proiectului logic i a proiectului fizic, folosind limbajul de descriere a datelor (LDD) oferit de sistemul SGBD ales, sau toolset-uri grafice (de ex. table editor) sau prin executia scriptului generat de un toolset de proiectare Se implementeaz procedurile care asigur verificarea i forarea tuturor constrngerilor explicite (aseriuni, dependene de date care nu sunt determinate de chei ale relaiilor), a cror previziune i documentare a fost realizat n faza de proiectare logic a bazei de date Se populeaza baza de date cu informatii obinute prin conversia unor date existente sub form de fiiere sau introduse direct n tabele Tot n aceast faz programatorii de aplicaii implementeaz, pe baza specificaiilor conceptuale ale tranzaciilor, programele de aplicaii, n diferite tehnologii de programare disponibile (limbaje procedurale de extensie a limbajului SQL, limbajul SQL integrat, interfee i biblioteci de programare)Dup crearea i popularea bazei de date i implementarea programelor de aplicaii se poate trece la etapa de testare si operare a sistemului debaze de date, n paralel cu monitorizarea i ntreinerea acestuia

Dezvoltarea aplicatiilor de baze de date Aplicaiile de baze asigura:interfaa (grafic) cu utilizatoriiimplementarea algoritmilor de calcul necesari interfaa cu sistemele de gestiune a bazelor de date

Limbajul SQL, folosit in SGBD-uri este un limbaj neprocedural, adica nu prevede instruciuni de control al ordinii de execuie a operaiilorDe aceea SGBD-urile mai folosesc si extensii procedurale a limbajului SQL:PL/SQL in sistemele Oracle, PL/PLGSQL in sistemele PostGreSQLTransact-SQL in sistemele Microsoft SQL ServerExtensie SQL in MySQL (asemanatoare cu Transact SQL) etc.Aplicatiile de baze de date folosesc:Limbajul SQL integrat intr-un limbaj de nivel inalt (Embeded SQL)Interfete de programare a aplicatiilor (API) (call level interface)

Limbaje procedurale de extensie a SQL Extensiile procedurale ale limbajului SQL definesc:-Variabile, instructiuni pentru controlul ordinii de execuie (bucle while, instruciuni condiionale if etc.), instruciuni SQL extinse-ofera suport de crearea cursoarelor, a procedurilor stocate, a funciilor definite de utilizator i a declanatorilor (triggere) Variabilele sunt folosite pentru stocarea in memorie a unor valori care potfi testate sau modificate i pot fi folosite pt transferul datelor ctre i de la tabele Variabilele locale au ca domeniu de definiie blocul, procedura, functia sau trigger-ul n care au fost declarate-Un bloc este un grup de instructiuni delimitat prin instructiunile: BEGIN ... END; un bloc este considerat o instructiune compusa-O variabil local se declar si se initializeaza diferit de la un SGBD la altul. ex: in Transact SQL: DECLARE @contor INT SELECT @contor = 0in PL/SQL (Oracle): DECLARE CONTOR := 1; in MySQL: DECLARE contorINT;SET contor = 0; Ordinea de execuie a instruciunilor este controlat prin instruciuni ca:BEGIN...ENDREPEAT...UNTILFORGOTO WHILE IF...ELSEBREAKRETURNCONTINUE

Instructiuni SQL extinse Extensiile procedurale definesc clauze suplimentare in instructiunile SQL, astfel nct acestea s poat fi folosite n combinaie cu variabilele localeDe exemplu -instruciunea SELECT prin care se incarca valori ale unor atribute selectate din baza de date in variabile locale:In Transact-SQL:SELECT @var1 = col1, @var2 = col2, ... @varn = coln FROM lista_tabele WHERE conditieIn PL/SQL (Oracle): SELECT lista_coloane INTO lista_variabileFROM lista_tabele [WHERE conditie] [optiuni] In MySQL: SELECT lista_coloane INTO lista_variabileFROM lista_tabele [WHERE conditie] [optiuni] Astfel de instruciuni sunt utile pentru interogrile care returneaz o singur linie, deoarece variabilele locale sunt setate cu valorile atributelor din prima linie a rezultatului, iar valorile din celelalte linii se pierd.De ex. (in MySQL):DECLARE id_angajat int; DECLAREs_nume, s_prenume varchar(20);SET id_angajat = 5;SELECT Nume, Prenume INTOs_nume, s_prenumeFROM ANGAJATI WHERE IdAngajat = id_angajat Atunci cnd o interogare returneaz o mulime de linii, se foloseste un cursor Variabilele locale mai pot fi folosite n clauza WHERE a instructiunii SELECT precum si in instructiunile INSERT sau UPDATE (ca valori introduse)

Proceduri stocate procedur stocat(stored procedure) este o procedur care implementeaz o parte din algoritmii de calcul ai aplicaiilor i care este memorat in baza de date, la fel ca i alte obiecte ale bazei de dateProcedurile stocate se definesc folosind extensiile procedurale ale SQL: In Transact-SQL: CREATE PROCEDURE nume_proc [parametri] AS instructiune In PL/SQL (Oracle): CREATE PROCEDURE nume_proc [parametri] AS instructiuneIn MySQL: CREATE PROCEDURE nume_proc [parametri] instructiune_compusa Parametrii pot fi de intrare (IN), de iesire (OUT) sau de intrare-iesire (INOUT); apelul unei proceduri stocate de ctre un client (aplicaie) produce execuia de catre SGBD a tuturor instruciunilor procedurii i returnarea rezultatrlor in parametrii OUT si INOUT Avantaje -imbunatatirea performantelor sistemului prin: Scaderea comunicaiei ntre aplicaie i serverul bazei de date Scaderea timpului de execuie a sarcinii respective, dat fiind c procedura stocat este deja compilat, optimizat si memorata, putand fi apelata oricand, de oricati clienti Dezavantaje: congestionarea serverului si scaderea performantelor acestuia, dac prea multe aplicaii executa operaiile de prelucrare pe server prin intermediul procedurilor stocate

Exemplu: Procedura stocata in MySQL Folosim tabelele: Studenti, Examene, Discipline:Procedura stocata: Calculul mediei notelor la o disciplina data:DELIMITER $$/* se redefineste delimitatorul deoarece ; este obligatoriu intre instr. din blocuri */DROP PROCEDURE IF EXISTS SP_Media $$CREATE PROCEDURE SP_Media(OUT mediafloat, IN disciplinavarchar(4))BEGIN SELECT AVG(nota) INTO mediaFROM DISCIPLINE, EXAMENEWHERE DISCIPLINE.idDiscipline = EXAMENE.idDisciplineAND Acronim = disciplina;END$$ DELIMITER ;Apelul procedurii:call SP_Media(@media, 'PBD'); /* o variabila precedata de @ esteimplicit locala */select @media;

Functii definite de utilizator O funcie definit de utilizator(user-defined function) este o funcie memorat in baza de date, la fel ca o procedur stocatO funcie are numai parametri de intrare, returneaz ntotdeauna o valoare i poate fi folosit direct n expresii (o procedur stocat poate s returneze zero, una sau mai multe valori prin parametrii de tip OUT sau INOUT) Functiile se definesc folosind extensiile procedurale ale limbajului SQL: In Transact-SQL: CREATE FUNCTIONnume_func [parametri] AS instructiune In PL/SQL (Oracle): CREATE FUNCTIONnume_func [parametri] AS instructiuneIn MySQL: CREATE FUNCTIONnume_func [parametri] instructiune_compusaExemplu de creare a unei functii in MySQL: DELIMITER $$ DROP FUNCTION IF EXISTS Func_Media$$ CREATE FUNCTION Func_Media(disc varchar(4)) RETURNS float BEGIN DECLARE nota_medie float; SELECT AVG(nota) INTO nota_medie FROM DISCIPLINE, EXAMENEWHERE DISCIPLINE.idDiscipline = EXAMENE.idDisciplineAnd Acronim = disc; RETURN nota_medie; END$$ DELIMITER ;Utilizarea valorii returnate de o functie: select Func_Media('PBD');

Cursoare Un cursor(cursor) este o structur (un buffer) care permite memorarea unei mulimi de linii returnate de o instruciune de interogare, urmata de extragerea si prelucrarea (eventual repetata) in programele de aplicatii a fiecarei linii Cursoarele se pot crea folosind limbajul SQL sau extensiile procedurale ale acestuia Instruciunile SQL de definire i de operare a cursoarelor: Definire cursor: DECLARE nume_cursor[OPTIUNI] CURSOR FORinstructiune_select; Deschidere cursor (popularea cu datele din tabele): OPENnume_cursor;Extragerea unei (sau mai multor) linii dintr-un cursor de la pozitia curenta:FETCH [FROM] nume_cursor INTOlista_variabile;Inchiderea cursor: CLOSE nume_cursor; Cursoarele pot fi memorate:-la server, iar clientul primete cte o linie (sau un grup de linii) de la server la fiecare instruciune de extragere -la client i liniile sunt folosite direct n programul respectiv In general, cursoarele la server sunt mai avantajoase dect cursoarele la client, deoarece cursoarele la client necesit ca toate liniile rezultat s fie transferate dintr-o dat de la server la clientExemplu: Cursor intr-o procedura stocata MySQL Procedura: Calculul mediei unui student dat (nume, prenume)DELIMITER $$DROP PROCEDURE IF EXISTS Medii_Studenti $$CREATE PROCEDURE Medii_Studenti(OUT media float, IN s_nume varchar(20), IN s_prenume varchar(20))BEGINDECLARE done INT DEFAULT 0;DECLARE student, id_student INT;/*Crearea cursorului*/DECLARE cursor_examene CURSOR FORSELECT idStudenti, avg(nota) FROM EXAMENE GROUP BY idStudenti;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;SELECT idStudenti INTO student FROM STUDENTI WHERE Nume = s_numeAND Prenume = s_prenume;OPEN cursor_examene;REPEATFETCH cursor_examene INTO id_student, media;UNTIL done = 1 OR id_student = studentEND REPEAT;CLOSE cursor_examene;END$$DELIMITER ;Exemplu: Cursor intr-o procedura stocata MySQL-Pentru parcurgerea liniilor cursorului se defineste un handler pentru conditia de terminare a parcurgerii liniilor cursorului (not found); un handler este un fel de rutina de tratare a exceptiilor Apelul procedurii:call Medii_Studenti(@media, 'Popescu', 'Marius');select @media; Se obtine rezultatul:@media6.5 Parcurgerea liniilor cursorului se poate face si cu instructiunea while:FETCH cursor_examene INTO id_student, media;WHILEdone = 0 AND id_student student DOFETCH cursor_examene INTO id_student, media;END WHILE; Declararea unei variabile locale (cu instructiunea DECLARE) se poate face numai intr-un bloc BEGIN ... END si numai la inceputul acestuia Declaratiile trebuie sa fie facute intr-o anumita ordine: cursoarele se declara inaintea declararii handler-elor, iar variabilele locale inaintea declararii cursoarelor si a handler-elor

Triggere Un trigger este o procedur stocat special, care este executat automat atunci cnd se efectueaz operaii de actualizare a relaiilor (INSERT, DELETE, UPDATE)Triggerele pot fi create folosind extensiile procedurale ale limbajului SQL; sintaxa difera de la un SGBD la altul (sunt neportabile):In Transact-SQL: CREATE TRIGGERnume_trigger ON nume_tabel {FOR|AFTER|INSTEAD OF} {[DELETE][,INSERT][,UPDATE]} AS instructiuniIn Pl/SQL (Oracle): CREATE TRIGGERnume_trigger {BEFORE|AFTER} [INSERT, DELETE, UPDATE] [FOR EACH ROW [WHEN conditie]] CALL proceduraIn MySQL: CREATE TRIGGERnume_trigger ON tabel FOR EACH ROW instructiune Utilizarea triggerelor: Extinderea capacitii SGBD-ului de meninere a integritii datelor relaionale -impunerea constrngerile explicite cum sunt dependenele de date (dependene funcionale sau multivalorice care nu sunt determinate de chei)Generarea automat a unor valori care rezult din valori ale altor atributeJurnalizarea transparent a evenimentelor sau culegerea de date statistice n legtur cu accesarea relaiilor.

Exemplu: trigger in MySQL Se defineste un trigger care genereaza coloana notain tabelul examene_2(idExamene, idDiscipline, notaLab, notaExam, nota):DELIMITER $$DROP TRIGGER IF EXISTS calcul_nota $$CREATE TRIGGER calcul_nota BEFORE UPDATE ON `examene_2` FOR EACH ROW BEGIN SET NEW.nota = NEW.notaLab + NEW.notaExam; END;$$ DELIMITER ; Instructiunile dupa FOR EACH ROW se executa de fiecare data candtriggerul este activat, ceea ce se intampla la fiecare linie afectata de instructiunea de declansare a triggerului (UPDATE in exemplul dat):update examene_2 set notaLab = 2 , notaExam = 5 where idStudenti=1 AND idDiscipline = 2; Cuvintele cheie OLD si NEW permit accesarea coloanelor din liniaafectata: pentru triggere INSERT se poate folosi numai NEW, pentru triggere DELETE numai OLD,pentru triggere UPDATE se poate folosi OLD (pentru valorile dinainte de UPDATE) sau NEW (pentru valorile actualizate).

Limbajul SQL integrat (Embeded SQL) n limbajul SQL integrat (Embeded SQL) instruciunile limbajului SQL sunt incluse direct n codul programului surs scris ntr-un limbaj gazd de nivel nalt (Ada, PL/1, Pascal, Fortran, Cobol, C) Controlul fluxului de operaii este realizat prin instruciunile limbajului gazd, iar operaiile cu baza de date sunt realizate prin instruciuni SQLInstruciunile SQL integrate n programul scris n limbajul gazd sunt prelucrate de un instrument software adecvat (numit preprocesor), fiind transformate n apeluri de funcii ale unei biblioteci speciale a SGBD-uluiRezultatul preprocesrii este un program surs n limbajul gazd, care poate fi compilat cu compilatorul limbajului gazd respectiv i apoi legat (link) cu bibliotecile de sistem i bibliotecile SGBD-uluiStandardul SQL2 specific suport integrat pentru limbajele PL/1, C, Pascal, Cobol, Fortran, Mumps. Pentru produsele Oracle, limbajul SQL a fost integrat n limbajul Java, sub numele de SQLJPentru sistemele Microsoft SQL Server se poate folosi limbajul ESQL/C (Embedded SQL for C), pentru MySQL exista biblioteca mysqld

Interfete si biblioteci de programare a aplicatiilor de baze de date In general se folosesc 2 categorii de interfete de programare a aplicatiilor de baze de date: interfete specifice unui anumit SGBD si interfete independente de SGBDInterfete specifice unui anumit SGBD sunt biblioteci care contin funcii i macrodefiniii ce permit aplicaiilor s interacioneze cu serverul bazei de date. De exemplu: Biblioteci dezvoltate pentru limbajul C ( biblioteca C pentru sistemul Microsoft SQL Server -DB-Library for C, biblioteca MySQL C API)Biblioteci pentru alte limbaje (C++, Perl, PHP, etc) Interfee independente de SGBD, cu un grad ridicat de generalitate, carepot fi folosite pentru mai multe tipuri de SGBD-uri; cele mai cunoscute sunt:Interfata ODBC (Open DataBase Connectivity) Interfata JDBC (Java DataBase Connectivity)

Interfata ODBC Tehnologia ODBC (Open Database Connectivity) -interfa de programare a aplicaiilor prin apel de funcii independente de sistemul SGBD folositIndependen se obine prin drivere specifice fiecrui SGBD Driverul transform apelurile de funcii ODBC n comenzi SQL (sau ntr-un limbaj procedural de extensie a limbajului SQL) si le transmite SGBD-ului Interfata ODBCAPLICATIE

Administrator de drivere

driverdriverDriver

Sursa de dateSursa de dateSursa de date

SGRD si baza de dateSGRD si BAza de dateSGRD si Baza de date

Interfaa JDBCJDBC este o interfa de programare a aplicaiilor de baze de date independent de platform i de sistemul SGBDInterfaa JDBC const din clase si obiecte Java i permite interaciunea cu baze de date relaionale, precum i cu alte surse de date n format tabelarArhitectura JDBC const din mai multe niveluri care asigur independena funciilor de acces apelate din programele de aplicaie de SGBDProgram de aplicatie Java

Interfata JDBC

Administratorul de driver (driver manager)

Driver Java purDriver combinatPunte JDBC-ODBCDriver JDBC de retea

Driver ODBC

Protocol JDBC-middleware Protocoale de acces specific SGBD-urilor

Capitolul 5: Gestiunea tranzactiilor

Tranzactii Anomalii de acces concurent la bazele de dateActualizare pierdutaCitire improprieCitire irepetabilaCitire fantoma Proprietatile tranzactiilor Operatiile efectuate de tranzactii Starile tranzactiilor Planificarea tranzactiilor Tehnici de control al concurenteiControlul concurentei prin blocareControlul concurentei prin marci de timp Tehnici de refacere a bazelor de date

Tranzactii n mod obinuit, un sistem SGBD deservete mai muli utilizatori, care acceseaz concurent datele din tabele Execuia concurent a mai multor procese poate avea loc:ntr-un sistem uniprocesor, prin partajarea (mprirea) timpului de execuie al procesorului ntre mai multe procese(multiprogramare)ntr-un sistem multiprocesor,n care mai multe procese pot fi executate n mod real simultan, pe mai multe procesoare ale sistemului (multiprocesare) tranzacie (transaction) este o unitate logic de prelucrare indivizibil (atomic) a datelor unei baze de date prin care se asigur consistena acesteia tranzacie trebuie s asigure consistena bazei de date in diferite situatii:tranzactia se execut individual sau concurent cu alte tranzaciiapardefecte ale sistemuluin cursul execuiei tranzaciei tranzacieeste o operaie indivizibil de acces la baza de datecare:fie se execut cu succes toate aciunile i se termin cu o validare a modificrilor efectuate asupra bazei de date (commit) fie nu poate efectua (din diferite motive) toate aciunile i este abandonat i anulat (abort, rollback)

Exemplu de tranzactie Exemplu: un sistem de rezervare a locurilor la curse aerienePASAGERI(IdPasager,Nume,Prenume,Adresa)CURSE(IdCursa,AeroportPlecare,AeroportSosire,DataCursa, NrLocuriLibere)FACTURI(IdFactura,IdPasager,IdCursa, DataFactura,Pret)- Pentru rezervarea unui loc se efectueaz mai multe operaii:1.Se insereaz o linie nou n tabelul PASAGERI, cu datele pasagerului2.Dac exist locuri libere la cursa dorit, atunci se face propriu-zis rezervarea, prin inserarea unei linii noi n tabelul FACTURI; altfel, nu se face rezervarea3.Se tipreste factura4.Se emite (tiprete) biletul - Probleme care pot sa apara:Dac sistemul se defecteaz dup ce s-a executat pasul 2, s-a fcut o rezervare, dar biletul nu a fost facturat i nici emis Dac defeciunea are loc dup pasul 3, clientului i se trimite factura, dar el nu a primit biletul Dac nu se defecteaz sistemul, dar doi ageni de vnzri atribuie acelai loc la doi pasageri diferii, atunci vor fi probleme la mbarcarea pasagerilor- Astfel de probleme ar disparea dac toate actiunile efectuate pentru o rezervare ar fi grupate ca o operaie indivizibil (atomic)

Anomalii de acces concurent la bazele de date Unitatea de transfer a datelor ntre discul magnetic i memoria principal a sistemului este un bloc, care corespunde unui sector de pe discsi in care se memoreaza mai multe inregistrari (tupluri) Un articol (data item) este un cmp care memoreaz valoarea unui atribut dintr-o nregistrare (tuplu), dar poate fi o nregistrare ntreag sau chiar o grupare de inregistrari memorate intr-un bloc Operaiile de acces la un articol X al bazei de date pot fi:read(X): citete articolul X din baza de date ntr-o variabil a programului; pentru simplificarea notaiilor se va considera c variabila n care se citete articolul X este notat, de asemenea, cu X.write(X): scrie variabila de program X n articolul X al bazei de date. Tranzaciile lansate de diferii utilizatori se pot executa concurent i este posibil s actualizeze aceleai articole ale bazei de dateDac execuia concurent a tranzaciilor este necontrolat, este posibil ca baza de date s ajung ntr-o stare inconsistent (incorect), chiar dac:fiecare tranzacie n partea fost executat corect nu au aprut defecte de funcionare ale sistemului

Actualizare pierduta(lost update): rezulta X=X-M (b) in loc de valoarea corecta X= X+N-M (a) Citire improprie(dirty read): T2 citeste X+N, desi X=X+N nu a fost validata (c) Timp T1T2T1T2T1T2

Read(X)Read(X)Read(X)

X=X+NX=X+NX=X+N

Write(X)Read(X)Write(X)

Read (X)X=X-MRead(X)

X=X-MWrite(X)X=X-M

Write(X)Write(X)Write(X)

abort

(a) (b) (c)

Citire irepetabil(nonrepetable read): o tranzacie citete un articol de dou ori, iar ntre cele dou citiri, o alt tranzacie a modificat chiar acel articol Citire fantom(phantom read): o tranzacie prelucreaz un set de linii rezultat al unei interogrisin timpul acestei prelucrri o alt tranzacie insereazasau stergeo linie

Proprietatile tranzactiilor (ACID) Atomicitatea(atomicity):proprietatea unei tranzacii de a reprezenta o unitate de execuie indivizibil, adic de a executa totul sau nimic Consistena(consistency): proprietatea unei tranzactii de a efectua modificri corecte ale bazei de dateo tranzacie transform baza de date dintr-o stare consistent n alt stare consistentstarea unei baze de date este consistent dac respect toate constrngerile de integritate implicite sau explicite Izolarea(isolation):proprietatea unei tranzacii de a face vizibile modificrile efectuate numai dup ce a fost validat(committed) Dac rezultatele pariale ale unei tranzacii sunt vizibile altor tranzacii nainte de validarea acesteia i dac se ntmpl ca aceast tranzacie s fie abandonat i anulat (rollback), atunci toate tranzaciile care au accesat rezultatele pariale ale acesteia vor trebui s fie anulateAceste operaii de anulare pot produce, la rndul lor alte anulri, .a.m.d. Durabilitarea(durability): proprietatea prin care, dup validarea unei tranzacii, modificrile efectuate de aceasta n baza de date nu vor mai fi pierdute datorit unor defectri ulterioare a sistemului

Operatiile efectuate de tranzactii Operaiile efectuate de o tranzacie i nregistrate de administratorul de refacere (recovery manager):begin: nceputul execuiei unei tranzaciireadsau write: operaii de citire sau scriere a articolelor dinbaza de dateend: marcheaz terminarea operaiilor de scriere sau citire din baza de date, ceea ce nseamn c tranzacia se poate termina; totui, este posibil s fie necesare unele operaii de verificare nainte de validarea (commit) tranzaciei.commit:terminarea cu succes a tranzaciei, validarea tuturor modificrilor efectuate n baza de date i vizibilitatea modificrilor efectuate pentru alte tranzacii; din acest moment, modificrile efectuate nu mai pot fi anulate, nici pierdute printr-o defectare ulterioar a sistemului rollback(sau abort): semnifica faptul c tranzacia a fost abandonat i c orice efect pe care tranzacia l-a avut asupra bazei de date trebuie s fie anulat (printr-o rulare napoia operaiilor).undo: operaie similar operaiei rollback, dar se aplic unei singure operaii, nu unei ntregi tranzacii.redo: specific faptul c unele operaii ale unei tranzacii trebuie s fie executate din nou pentru a se putea valida ntreaga tranzacie.Ultimele dou operaii sunt necesare numai n anumite tehnici de refacere

Starile tranzactiilor Diagrama de stare a unei tranzactii:

Pentru refacerea bazei de date, sistemul SGBD menine un fiier jurnal(log file), n care memoreaz operaiile efectuate de fiecare tranzacie, identificat printr-un identificatorunic(T) generat de system Fiierul jurnal este memorat pe disc i nu este afectat de erori de execuie, cu excepia unei defectri catastrofice a discului Fiierul jurnal este salvat periodic pe un suport auxiliar (band magnetic) Planificarea tranzactiilor planificare(schedule, sau istorie -history) S a n tranzacii T1,T2,..Ti,...Tn este o ordonare a operaiilor tranzaciilor astfel nct:Pentru orice tranzacie Ti care particip n S, operaiile lui Ti n S respect ordinea iniial din TiAlte operaii (ale altor tranzacii Tj, unde j i) pot fi ntreesute cu operaii ale tranzaciei Ti Dou operaii dintr-o planificare sunt conflictuale(conflicting operations) dac aparin unor tranzacii diferite, acceseaz acelai articol i cel puin una dintre operaii este operaie de scriere Planificri seriale(serial schedules):o planificare S se numete serial dac pentru orice tranzacie T participant n planificare, toate operaiile din T se execut consecutiv n S; altfel, planificarea se numete neserial Pentru n tranzactii pot exista n! planificari seriale Orice planificare seriala a unor tranzactii corecte este corecta, dar nu permite ntreeserea operaiilor i concurena tranzaciilor De aceea, n cazul sistemelor de baze de date cu utilizatori multipli se folosesc planificrile serializabile, care admit concurena, asigurnd n aceelai timp consistena bazei de date

Planificari seriale ale tranzactiilor Planificarile seriale posibile ale tranzactiilor T1 si T2sunt SA si SB:T1T2SAT1T2SB

Read(X)-r1(X)Read(X)-r2(X)

X=X-NX=X+M

Write(X)-w1(X)Write(X)-w2(X)

Read(Y)-r1(Y)Read(X)-r1(X)

Y=Y+NX=X-N

Write(Y)-w1(Y)Write(X)-w1(X)

Read(X)-r2(X)Read(Y)-r1(Y)

X=X+MY=Y+N

Write(X)-w2(X)Write(Y)-w1(Y)

Notam operaiile de begin, read, write, commit i abort cu b, r, w, c, a, cu indice numarul tranzaciei i ca parametru articolul pe care l-a citit sau scris:SA: b1; r1(X); w1(X); r1(Y); w1(Y); c1; b2; r2(X); w2(X); c2; SB: b2; r2(X); w2(X); c2; b1; r1(X); w1(X); r1(Y); w1(Y); c1; Perechile de operaii conflictualesunt: in SA: ((r1(X), w2(X)), w1(X), r2(X)),(w1(X), w2(X)); - - in SB: (r2(X), w1(X)), (w2(X), r1(X)), (w2(X), w1(X))

Planificari serializabile ale tranzactiilor O planificare a n tranzacii se numte serializabil dac este echivalent cu o planificare serial a celor n tranzacii Dou planificri sunt echivalente (din punct de vedere al conflictelor) dac operaiile din oricare pereche de operatii conflictuale se execut n aceeai ordine n cele dou planificri Planificarile SCsi SDsunt planificari echivalente cu SA, deci sunt serializabileT1T2SET1T2SF

Read(X)-r1(X)Read(X)-r1(X)

X=X-NX=X-N

Read(X)-r2(X)Read(X)-r2(X)

Write(X)-w1(X)X=X+M

X=X+MWrite(X)-w1(X)

Write(X)-w2(X)Read(Y)-r1(Y)

Read(Y)-r1(Y)Y=Y+N

Y=Y+NWrite(Y)-w1(Y)

Write(Y)-w1(Y)Write(X)-w2(X)

Planificarile SE si SF nu sunt sunt echivalente nici cu SA nici cu SB, deci sunt neserializabileT1T2SET1T2SF

Read(X)-r1(X)Read(X)-r1(X)

X=X-NX=X-N

Read(X)-r2(X)Read(X)-r2(X)

Write(X)-w1(X)X=X+M

X=X+MWrite(X)-w1(X)

Write(X)-w2(X)Read(Y)-r1(Y)

Read(Y)-r1(Y)Y=Y+N

Y=Y+NWrite(Y)-w1(Y)

Write(Y)-w1(Y)Write(X)-w2(X)

Testarea echivalentei unei planificri cu o planificare seriala prin testarea ordinii operatiilor din toate perechile de operatii conflictuale este foarte costisitoare DAR s-a demonstrat ca se poate asigura echivalenta planificarilor(deci serializabilitatea lor) prin tehnici de control al concurenei tranzaciilor, care interzicexecuia n ordine incorect a operaiilor din perechile conflictualeDe exemplu: n SE se interzice execuia r2(X) naintea de w1(X)

Tehnici de control al concurentei tranzactiilor -Pentru a asigura proprietile ACID ale tranzaciilor i, prin aceasta, consistena datelor,este necesar controlul execuiei concurente a tranzaciilor Cele mai utilizate tehnici de control al concurenei sunt: Tehnici bazate pe blocarea accesului la date prin zvoare(locks) Tehnici bazate pe mrci de timp (timestamps) Protocoalele de control al concurenei sunt implementate de SGBD-uri:programatorii de aplicaii nu opereaz explicit cu zvoare sau mrci de timp ei stabilesc opiunile prin care sistemul SGBD executaanumite operatii de control Un zvor (lock) este o variabil L(X) asociat cu un articol X al bazeide date care descrie starea acelui articol n raport cu operaiile care i se pot aplica Tipuri de zavoare utilizate in SGBD-uri: zvoare binare, zvoare cu stri multiple Un zvor binar(binary lock) L(X) poate avea dou stri: L(X) = 1 -liber (sau neblocat -free, unlocked) se poate accesa articolul XL(X) = 0 -ocupat (sau blocat -busy, locked) nu se poate accesa articolul XAsupra unui zvor binar L(X)se pot executa dou operaii: operaia de blocare, lock(X) trece zvorul n starea blocat (ocupat) operaia de eliberare, unlock(X) trece zvorul n starea neblocat (liber)

Zavoare binare Dac zvorul articolului X este liber (L(X)=1), atunci tranzactia: achiziioneaz zvorul (trecndu-l n starea ocupat prin operatia lock )execut operaiile necesare asupra articolului X elibereaz zvorul (trcndu-l in starea liberprin operaia unlock)Dac zvorul articolului X este ocupat (L(X)=0), atunci tranzacia:ateapt pn ce acesta este eliberat (de o alt tranzacie, care i-a terminat operaiile de acces la acel articol), dup care execut aceeai secven de operaii: blocarea zvorului, execuia operaiilor care acceseaz articolul respectiv i eliberarea zvoruluiOperaia de blocare se executat ca operaie indivizibil (folosind instruciuni speciale ale procesoarelor de tip TestAndSet)Regulile respectate defiecare tranzacie care folosete un zvor binar:1.O tranzacie trebuie s blocheze zvorul articolului X (prin operatia lock(X)), nainte de a efectua orice operaie de citire sau de scriere a articolului X 2.O tranzacie trebuie s elibereze zvorul unui articol X (prin operaia unlock(X)) dup ce a efectuat toate operaiile de citire sau de scriere a articolului X3.O tranzacie nu poate achizitiona un zvor pe care l deine deja4.O tranzacie nu poate elibera un zvor pe care nu l deine

Zvoare cu stri multipleTehnica zvoarelor binare este prea restrictiv i limiteaz n mod nejustificat concurena n execuia tranzaciilor De exemplu, mai multe tranzacii pot efectua operaii de citire n mod concurent asupra aceluiai articol, fr ca acest lucr