Sisteme de baze de date relationale

SISTEME DE BAZE DE DATE RELATIONALE

1 Modelul relational al BD

Modelul relational al BD a fost propus de E. Codd din 1971 si s-a bucurat de succes teoretic dar nu si practic [Jian97_1]. El formalizeaza matematic prin algebra relationala teoria BD pornind de la cateva principii:

O BD este formata din tabele ( corespunzatoare relatiilor din algebra relationala) care cuprind un numar de coloane ( campuri) si randuri (inregistrari).

Pentru fiecare tabela se defineste o cheie simbolica care identifica univoc un rand ( t-uplet) si nu poate fi nula ( integritatea entitatii).

Impartirea BD in tabele se face din considerente semantice si trebuie sa respecte cerintele de normalizare :

1NF - sa nu existe decat o valoare pentru fiecare camp dintr-o inregistrare;

2NF - sa nu existe campuri care depind numai partial de cheia primara ( PK);

3NF - sa nu existe campuri, care nu fac parte din cheia primara si care sa determine alt camp;

4NF - sa nu existe dependente multivaloare in BD.

Legaturile intre tabelele BD se fac prin chei externe ( FK) carora trebuie sa le corespunda in tabela referita o cheie primara ( integritatea referintei).

Asupra tabelelor ce compun BD sa se accepte operatorii :

relationali SELECT, PROJECT, JOIN, PRODUS CARTEZIAN;

din teoria multimilor UNION, INTERSECT, DIFFERANCE.

Sa nu se utilizeze pointeri pentru accesul la inregistrari sau legaturi vizibile pentru utilizator.

Se accepta indexarea tabelelor dupa anumite campuri, dar utilizatorul nu poate face referire explicita la index. El serveste la optimizarea accesului, facut automat de sistem.

Intrebarile asupra BD sa fie exprimate fara iteratii sau recursivitate.

Redundanta informatiei este minima daca BD este normalizata.

Modificarea si dezvoltarea BD este permisa ulterior dupa creare.

Sistemele relationale prin principiile de lucru sunt mai lente decat cele de tip retea. Din acest motiv, desi modelul relational a fost definit inaintea modelului retea Codasyl sau Socrate, primul SGBD relational comercial a aparut cu un deceniu in urma ( 1981 DB2 - IBM). Primul sistem relational experimental a fost System R/IBM, pentru calculatoare mari, care a introdus limbajul relational SQL ( Structured Query Language), preluat complet sau ca subset de toate sistemele relationale aparute ulterior. Mai tarziu, in 1981 prin perfectionarea sistemului R, IBM a lansat sistemul DB2, care si in prezent este cel mai complet si performant sistem relational, avand ca pret cca 100 000 $ pentru BD mari distribuite.

Deoarece in aceleasi conditii sistemele SQL sunt flexibile dar mai lente, pana la sfarsitul anilor ’80 sistemele retea Codasyl au dominat piata. Pentru aceeasi aplicatie in 1995 un program scris procedural in dBASE IV pe un IBM-PC/486 a lucrat de 4 ori mai repede decat un program DB2 lucrand pe un mainframe IBM. Sistemele relationale s-au dezvoltat pe doua directii:

Sisteme neprocedurale SQL : respecta complet cerintele algebrei relationale, sunt usor de utilizat si foarte flexibile, dar relativ lente si nu permit implementarea unor structuri de tip retea de mare complexitate. Posibilitatile de dialog sunt reduse. Din aceasta categorie fac parte : DB2 - IBM, Oracle, Ingres, RDBMS/DEC, Sybase.

Sisteme procedurale Xbase, care respecta partial cerintele algebrei relationale, accepta un subset SQL si permit dezvoltarea unor structuri mixte de BD relationale si cu posibilitati de tip retea complexe. Au posibilitati extinse de dialog si editare de rapoarte. Aceste sisteme sunt derivate din dBASE si din aceasta categorie fac parte : dBASE 5 si 7 - Borland, PARADOX - Borland, FOXPRO - Microsoft, CLIPPER, DELPHI, Borland C++ Builder.

2. Limbajul SQL

Limbajul SQL ( Structured Query Language) respecta cel mai bine cerintele algebrei relationale si este limbajul cel mai raspandit in SGBD - urile relationale :

Sistemul DB2 - IBM este cel mai complet si puternic, continand limbajul SQL de referinta. Are variante pe calculatoarele mari, pe statii UNIX si retele PC.

Sistemul ORACLE dezvoltat pe masinile UNIX si extins pe PC sub sistemul de operare Windows si Linux, este cel mai raspandit si performant si stabil sistem SQL pe piata BD. Necesita putere mare de calcul si multa memorie centrala pentru a obtine performante.

Sistemul RDBMS - DEC este unul din sistemele SQL cele mai puternice si mai raspandite.

Sistemul INGRES este o varianta putin modificata de SQL, foarte puternic dezvoltat in mediul academic.

MY SQL este un sistem relational dezvoltat in mediul Linux.

Microsoft SQL Server este cel mai raspandit sistem SQL dezvoltat la un pret mediu

SQL cuprinde un set redus si puternic de comenzi care realizeaza principalele functii de :

descriere a BD si a tabelelor componente;

stergere a BD, tabele componente sau vederi;

adaugare, stergere si modificare de inregistrari;

creare vederi in BD, care sunt vazute ca tabele virtuale;

interogarea BD prin intrebari;

comenzi de protectie, control al concurentei si asigurarea integritatii BD;

creare fisiere index.

Toate informatiile referitoare la o BD se tin in fisiere sistem : tabele componente, campuri definite, fisiere index existente, etc. Aceste fisiere sistem au regim de tabele, se genereaza vide la crearea BD si se actualizeaza pe masura completarii structurii.

Se remarca faptul ca operatiile SQL, conform algebrei relationale se fac asupra tabelelor si nu asupra unor inregistrari. Accesul la inregistrari individuale este interzis in SQL Standard. Selectia inregistrarilor se face punand conditii la parcurgerea unei tabele.

Se prezinta in continuare principalele comenzi SQL insotite de scurte comentarii :

CREATE DATABASE < nume_bd >;    -- creare BD

SHOW DATABASE ; -- afisare BD existente in sistem

START DATABASE < nume_bd > ; -- deschidere BD

STOP DATABASE ; -- inchidere BD curent deschisa

DROP DATABASE < nume_bd > -- stergere BD inchisa

CREATE TABLE < nume_tabela > (camp₁ tip₁, camp₂ tip₂,…camp_k tip_k); -- definire tabela

Tipurile utilizate pentru campuri sunt CHAR, DATE, LOGICAL, DECIMAL(x,y), SMALLINT, INTEGER, NUMERIC(x,y), FLOAT(x,y).

DROP TABLE < nume_tabela > ;         -- stergere tabela din BD curenta

INSERT INTO < nume_tabela > lista_coloane ) VALUE ( lista_valori ) ; -- introduce

-- inregistrari in tabela, de la tastatura

INSERT INTO < nume_tabela > ( SELECT FROM …) ; -- introduce articole in

tabela prin copiere din alte tabele

DELETE FROM < nume_tabela > WHERE < conditie > ; --

-- sterge inregistrarile care indeplinesc conditia dintr-o tabela

ALTER TABLE < nume_tabela > add (col₁ tip₁, col₂ tip₂,…col_k tip_k);

-- modifica structura unei tabele prin adaugare de campuri noi

UPDATE < nume_tabela > set col₁ = expr₁, col₂ = expr₂, … where < conditie >;

-- modifica valoarea unor campuri din inregistrarile selectate

SELECT from < nume_tabela > [ where < conditie > ]; --selecteaza

lista_col                         --inregistrari dintr-o tabela, care indeplinesc conditia si le afiseaza

CREATE VIEW < vedere > ( lista_coloane) AS SELECT ( lista_col)

FROM < tabela > WHERE < conditie > ; --

-- din inregistrarile selectate se iau coloanele indicate si se considera

-- o tabela virtuala asupra careia se pot efectua operatii

--

DROP VIEW < vedere > ; -- stergere vedere definita

CREATE [UNIQUE] INDEX < fis_index > ON tabela ( col₁, col₂ ASC/DSC) ;

-- creaza o tabela index pentru anumite coloane a unei tabele

DROP INDEX < fis_index >;    -- sterge un fisier index existent

DECLARE < nume_cursor > CURSOR FOR

SELECT * FROM < tabela > [ where < conditie > ], [ ORDER BY col ] ;

-- defineste o tabela temporara care poate fi exploatata secvential

-- la nivel de inregistrare prin comanda FETCH

OPEN < nume_cursor > ; -- deschide un cursor, creand tabela temporara

CLOSE < nume_cursor >          -- inchide cursorul si distruge tabela temporara

FETCH INTO < nume_cursor > INTO lista_var ;

-- transfera inregistrarea curenta din cursor in lista de variabile

GRANT ALL [ PRIVILEGE] ON TABLE lista_tabele TO PUBLIC

lista_privilegii                       lista_user

-- acorda utilizatorilor din lista privilegiile, referitoare la anumite tabele

REVOKE ALL ON TABLE lista_tabele FROM PUBLIC

lista_privilegii         lista_user

-- suprima anumite drepturi de la anumiti utilizatori. Lista de privilegii se refera

-- la comenzile pe care utilizatorul le poate efectua asupra tabelelor :

-- ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE [ lista_coloane]

SET TRANSACTION ; -- initiere tranzactie

ROLLBACK ;                   -- refacere informatie modificata in tranzactie

COMMIT ; -- terminare tranzactie normala

3. Regasirea informatiilor prin SQL

O baza de date relationala este formata din mai multe tabele (fisiere), obtinute in urma normalizarii structurii, pentru a realiza o redundanta minima a informatiilor. Regasirea informatiilor intr-o BD se face teoretic pe baza operatorilor relationali :

SELECT - care specifica o conditie pentru selectarea unor inregistrari ( t-upleti) dintr-o tabela;

PROJECT - care selecteaza numai anumite campuri din inregistrarile unei tabele sau din rezultatul operatorului SELECT;

JOIN - care combina doua tabele si selectaza inregistrarile ce indeplinesc o conditie in care se compara campuri din cele doua tabele.

Operatorii relationali se aplica unor tabele ( relatii) si rezultatul este o tabela, care poate fi afisata. Nu se admit operatii asupra unei singure inregistrari si nu exista inregistrare curent selectata ca in cazul BD ierarhice sau retea.

Comanda SELECT din SQL realizeaza toate cele trei operatii relationale specificate mai sus si este cea mai complexa si cea mai importanta comanda, realizand si alte functii. Aplicata unei tabele, comanda SELECT realizeaza proiectia relationala prin lista de campuri specificata si selectia relationala din tabela, prin conditia WHERE :

SELECT nume, adresa, data_n FROM student WHERE cods = ‘CA’

Pentru SELECT se parcurge secvential fisierul ( tabela) student, se verifica daca primele doua caractere sunt ‘CA’ (Calculatoare) si se afiseaza numele, adresa si data nasterii pentru studentii selectati.

Daca se selecteaza un singur student, se parcurge tot fisierul si din conditia particulara rezulta o singura inregistrare selectata, specificand de exemplu numele. Caracterul * indica afisarea tuturor campurilor :

SELECT * FROM student WHERE nume = ‘POPA DORIN’

Rezultatul unei selectii este in general un numar mai mare de inregistrari, care nu pot fi prelucrate individual. Pentru a putea prelucra fiecare inregistrare rezultat fara a parcurge de fiecare data fisierul, s-a introdus notiunea de cursor care este o tabela temporara in care se pot memora inregistrarile selectate si se pot parcurge apoi secvential prin comanda FETCH. Un cursor se declara prin nume si prin operatia de SELECT care genereaza tabela temporara in momentul deschiderii :

DECLARE cursor1 CURSOR FOR -- declarare cursor

SELECT * FROM student WHERE cods = ‘CA’

OPEN cursor1 -- deschidere cursor si generare tabela temporara

-- prin selctia studentilor de la Calculatoare

DO WHILE SQLCODE = 0 -- operatie normala de citire

FETCH cursor1 INTO Vnume, Vadresa, Vdatan -- citirea unei inregistrari,

-- memorare campuri in variabile si incrementare contor

- Prelucare inregistrare

ENDDO -- ciclu de citire din cursor

CLOSE cursor1 -- inchidere cursor

In acest caz s-a facut o singura operatie SELECT la deschiderea cursorului. CURSOR - ul trebuie privit ca un utilitar SQL, deoarece nu respecta principiile relationale.

Operatii cu mai multe tabele (JOIN)

In comanda SELECT se pot specifica mai multe tabele in care se cauta inregistrari care sunt referite prin cheie simbolica, chiar in lant. Consideram pentru exemplificare o BD normalizata pentru evidenta studenti ( fig. 1.) formata din tabelele :

STUDENT - care contine datele personale ale studentului cods, nume, adresa, data nasterii, locul nasterii, nr. buletin, telefon, tata, mama, etc. Codul studentului este cheie si identifica univoc un student. Primele caractere indica grupe de studenti : AC4527 reprezinta studentul din facultatea Automatica si Calculatoare (A), sectia Calculatoare (C), anul 4, grupa 5, nr. 27.

AC4 - indica toti studentii din anul 4 Calculatoare.

CURS - contine datele fiecarui curs CODC, titlu, numar ore de curs, lucrari, proiect, forma de examinare, cod profesor, semestrul.

PROF - contine date despre profesori CODP, nume, adresa, grad, salar, vechime, telefon, data_n, etc.

NOTE - este o tabela de legatura intre STUDENT si CURS, deoarece un student are mai multe note, la cursuri diferite. Memorarea notelor in tabela STUDENT incalca regula 1 de normalizare, care cere ca fiecare atribut sa aiba o singura valoare intr-o inregistrare. Identificarea notei se face prin cheia CODS + CODC si valoarea NOTA.

STUDENT CURS

NOTE PROF

Fig. 1. BD relationala normalizata

Afisarea notelor studentilor de la Calculatoare impreuna cu numele cursului si numele profesorilor se face printr-o comanda SELECT care contine o selectie ( cods = ‘AC’) si 3 operatii de JOIN, care se fac succesiv. Afisarea se poate face in ordine alfabetica, in ordinea notelor sau pe grupe ( cods) :

SELECT S.Nume, Titlu, Nota, P.Nume -- campuri afisate

FROM STUDENT S, NOTE N, CURS C, PROF P -- tabele folosite

WHERE cods = ’AC’ -- conditia de selectie studenti

AND S.cods = N.cods -- conditie JOIN identificare note student

AND N.codc = C.codc -- conditie JOIN identificare curs pentru nota

AND C.codp = P.codp -- conditie JOIN identificare prof pentru curs

order BY S.Nume -- ordonare rezultat dupa nume student

Scrierea este simpla dar numarul de citiri si comparatii este foarte mari fata de cazul BD ierarhice. Flexibilitatea prelucrarii este importanta si odata cu cresterea performantelor calculatoarelor s-au putut impune si BD relationale.

Pentru a se determina notele, se compara fiecare student selectat cu fiecare nota si rezulta o tabela care va avea campurile concatenate din STUDENT si NOTE, care indeplinesc conditia de JOIN. Operatia se continua printr-un JOIN intre tabela rezultata si tabela CURS, s.a.m.d. In final, tabela rezultat va avea toate campurile din cele 4 tabele din care se selecteaza Nume student, Titlu curs, Note obtinute, Nume profesor.

Asemanator se pot realiza si alte legaturi de tipul M : N indicate in fig.1, daca exista campuri de legatura intre tabele. Afisarea tuturor studentilor care participa la cursurile unui profesor se face prin :

SELECT S.Nume FROM PROF P, CURS C, NOTE N, STUDENT S

WHERE P.Nume = ‘POP’ AND -- conditie de selectie

P.Codp = C.Codp AND C.Codc = N.Codc -- conditie JOIN

AND N.Cods = S.Cods

Se remarca folosirea tuturor tabelelor si diferite campuri in comparatii, desi numai numele studentilor ne intereseaza.

In clauza WHERE pot apare alte comenzi SELECT, caz in care comparatia se face cu fiecare ( ANY) element rezultat din operatie. Intrebarea de mai sus se poate pune prin imbricare de SELECT-uri :

SELECT Nume FROM STUDENT WHERE

Cods = ANY ( select Cods FROM NOTE WHERE

Codc = ANY ( select Codc FROM curs WHERE

Codp = ANY ( select Codp FROM PROF WHERE Nume = ‘POP’ )))

Executand SELECT-ul mai interior, se selecteaza codul profesorului ‘POP’ pentru care in SELECT-ul urmator se determina codurile cursurilor. Apoi in fisierul NOTE se cauta codul studentilor ce au note la aceste cursuri, iar cu codurile de student se identifica studentii. In acest caz, in final avem acces numai la datele din tabela STUDENT, celelalte date despre note, cursuri,s-au gasit numai in tabele intermediare care s-au distrus.

Comanda SELECT permite si alte functii de grupare a inregistrarilor selectate si verificarea unor conditii de grup (HAVING) folosind clauze de calcul pe grup ( AVG, COUNT, MAX, MIN, SUM). Rezultatele unui SELECT pot fi combinate cu ale altuia, daca tabelele rezultate sunt union compatibile (au acelasi domeniu al campurilor).

Forma generala a comenzii SELECT este :

SELECT [ lista de coloane ] ALL

DISTINCT col

[ INTO lista_var] -- rezultatul memorat in variabile

FROM lista_tabele -- tabelele in care se face cautarea

[ WHERE conditie ] -- conditie de selectie sau JOIN

[ GROUP BY col1, col2 ] -- criteriu de grupare rezultate

[ HAVING conditie ] -- conditii pentru selectie grup

[ UNION / INTERSECT / MINUS ] -- combinare rezultate

SELECT … -- urmatorul SELECT

[ ORDER BY col1, col2 ASC/ DSC] -- criteriu de ordonare rezultat

Comanda SELECT este foarte puternica, dar BD trebuie bine proiectata ca si structura, iar intrebarile trebuie gandite cat mai optimizat, fiindca in SELECT practic nu se pot detecta erori semantice. Semantic orice intrebare sintactic corecta este corecta, chiar daca este absurda. Intrebarea

SELECT * FROM STUDENT, CURS;

este absurda, deoarece nu exista nici un element comun intre tabela STUDENT si CURS ( daca nu se ia tabela NOTE). Raspunsul va fi un produs cartezian intre cele doua tabele, asociindu-i-se fiecarui student toate cursurile din universitate. Daca exista 10 000 studenti si 5000 de cursuri vor rezulta 50 * 10⁶ inregistrari de lungime L = L_STUD + L _CURS . Pentru L = 200 se obtine un tabel rezultat de 10 GB, care evident va umple discul si va dura foarte mult timp.

Operatii de grup si functii agregat

Afisare media notelor studentilor de la calculatoare anul 4 (media pe an) si numarul notelor.Operatiile se aplica pe toate inregistrarile selectate. Se va afisa un rand cu datele cerute.

SELECT COUNT(*) Nr_note, AVG(Nota) Medie_an

FROM Note WHERE CODS LIKE ’AC4%’

Pentru a numara studentii din an se vor selecta numai codurile de student distincte din fisierul note, fiindca un student poate avea mai multe note.

SELECT COUNT(DISTINCT(Cods) Nr_studenti, AVG(Nota) Medie_an

FROM Note WHERE CODS LIKE ’AC4%’;

Pentru a afisa media fiecarui student din anul 4, numarul notelor, nota minima, maxima si numele lui se va folosi clauza GROUP BY pe CODS, care grupeaza toate notele fiecarui student dupa codul studentului. Daca se utilizeaza GROUP BY operatiile de tip agregat se aplica pe grup. Se va afisa cate o linie pentru fiecare student.

In lista de selectie pot apare numai functii agregat sau campuri din lista de GROUP BY. Din acest motiv s-a introdus in aceasta lista si campul Nume desi el este determinat de CODS.

SELECT ST.Nume, ST.Cods, COUNT(*) Nr_note, AVG(Nota) Medie,

MIN(Nota) Nota_min, Max(Nota) Nota_max

FROM Stud ST, Note NT

WHERE CODS LIKE ’AC4%’ -- conditie de selectie

AND ST.Cods = NT.Cods -- conditie de JOIN

GROUP BY ST.CODS, ST.Nume;                        -- campuri de grupare

Pentru a afisa numai mediile studentii cu medii peste 8 se introduce conditia de grup prin clauza HAVING.

SELECT ST.Nume, ST.Cods, COUNT(*) Nr_note, AVG(Nota) Medie,

MIN(Nota) Nota_min, Max(Nota) Nota_max

FROM Stud ST, Note NT

WHERE CODS LIKE ’AC4%’ -- conditie de selectie

AND ST.Cods = NT.Cods -- conditie de JOIN

GROUP BY ST.CODS, ST.Nume                          -- campuri de grupare

HAVING AVG(Nota) > 8; -- conditie de grup

La operatiile INSERT, UPDATE si DELETE se pot declara proceduri de verificare ( TRIGGER) a corectitudinii datelor, sau indeplinirea conditiilor de integritate a referintei :

- Nu se admite stergerea unui curs daca exista studenti inscrisi:

- Nu se admit note pentru studenti al caror Cods nu exista in tabele STUDENT:

- Nu se admit cursuri care nu au Codp in fisierul PROFESOR.

Optimizarea intrebarilor SQL

Respectand algebra relationala sistemele SQL au performante reduse din cauza modului de executie al operatiilor de selectie si de JOIN. Pentru a optimiza performantele, se folosesc diferite metode :

Declararea fisierelor index pentru anumite campuri utilizate frecvent in selectie si JOIN;

Optimizarea euristica si sistematica a intrebarilor in timpul compilarii.

SQL este un limbaj neprocedural in care programatorul spune ce vrea prin intrebari si nu poate interveni in modul in care se rezolva intrebarea. Succesiunea de operatii de citire, comparatii, ordinea in care se fac JOIN- urile se stabileste de catre compilator. Programatorul poate cere crearea unor fisiere index pentru anumite campuri si chiar modul lor de organizare ( multinivel, hashing, arbori B⁺), dar nu poate face referiri la ei in timpul selectiei ( cautarii inregistrarilor).

Pentru cheile primare din tabele se genereaza automat fisiere index. In timpul unei selectii daca unul din campurile utilizate in conditie este indexat, se va face prima selectie prin fisierul index si se vor verifica restul conditiilor pe tabela rezultat. Daca cautam toti studentii din anul 4 Calculatoare care sunt din Timisoara, profitam de Cods care este cheie primara si are index :

SELECT Nume, Adresa FROM STUDENT

WHERE Cods = ‘AC4’ -- cautare prin fisierul index

AND Adresa = ‘Timisoara’ ; -- evaluare conditie suplimentara

Fisierele index se pot genera temporar pentru o aplicatie si apoi se pot distruge.

Optimizarea euristica presupune la compilare construirea arborelui intrebarii, folosind operatorii relationali care vor fi in nodurile arborelui si tabelele vor fi frunze. Pentru a avea cat mai putine citiri si comparatii, se vor executa prima data operatiile care reduc numarul de inregistrari in prelucrare si numarul de campuri din inregistrare. Operatorii relationali sunt in mare parte comutativi si vom putea cobori in arbore cat mai mult selectiile si proiectiile, iar apoi operatiile de JOIN. La nivelele superioare ale arborelui vom ajunge sa avem un numar mic de inregistrari si putine campuri, [ELMA89] care participa la operatiile de JOIN.

Optimizarea sistematica a intrebarilor utilizeaza evaluarea costurilor executiei si compararea lor pentru diferite strategii de executie. Se alege in final cea cu cele mai mici costuri estimate. Metoda limiteaza numarul strategiilor de executie considerate, pentru a nu mari prea mult timpul de estimare si se aplica in faza de compilare cand rezulta un cod executabil. Daca se utilizeaza interpreter, optimizarea se face partial, deoarece consumul de timp pentru optimizare ar fi prea mare.

Componentele costului sunt :

Costul accesului la informatii pe disc pentru citirea si scrierea blocurilor de fisier si a celor temporare. Acesta depinde de modul de organizare a informatiei si de existenta fisierelor index.

Costul memorarii informatiilor in memoria centrala este in general neglijabil la BD obisnuite si se ia in considerare la sistemele expert in care majoritatea datelor sunt in memorie.

Costul de calcul necesar efectuarii operatiilor in memoria centrala asupra campurilor inregistrarilor din buffere ( comparatii, sortari, calcule asupra valorii campurilor). Nu este semnificativ la BD.

Costul comunicarii este costul transmiterii intrebarii si rezultatelor de la terminalul origine al intrebarii la BD. Este important la BD distribuite in retea.

Pentru BD mari este importanta minimizarea timpului de acces la fisierele disc. Functiile de cost ignora restul factorilor si compara strategiile de executie dupa numarul de blocuri transferate intre disc si memoria centrala.

Informatiile necesare functiilor de cost sunt continute in general in catalogul BD, de unde se iau de optimizor :

numarul de blocuri b din fisier si numarul de inregistrari r;

factorul de blocare bfr dat de numarul de inregistrari pe bloc;

modul de organizare al fisierului , ordonat sau nu, indexat dupa cheie;

numar x de nivele de indexare si numar de blocuri index;

numarul d de valori distincte ale atributului index care permite estimarea cardinalitatii selectiei s, care e un numar mediu de articole care satisfac conditia de selectie.

Pentru un atribut cheie s = 1, iar pentru un atribut oarecare s = r / d (numar de inregistrari / numar de valori distincte).

Functiile de cost pentru SELECT le vom evalua considerand doar timpul de transfer disc-memorie si neglijand timpul de calcul si memorare. Timpul de transfer este proportional cu numarul de blocuri transferate.

Cautarea lineara : Fisierul fiind nesortat se cauta in toate blocurile pentru a gasi inregistrarile care indeplinesc conditia :

Cs = b unde b - nr. de blocuri ale fisierului.

Pentru un atribut cheie se cauta o singura inregistrare :

Cs = b/2

Cautarea binara : Fisierul fiind sortat se va putea folosi cautarea prin metoda injumatatirii intervalului si se vor citi :

Cs = log₂ b + s / bfr - 1

unde termenul s/bfr se refera la cazul in care numarul de articole selectate s este mai mare decat un bloc ( bfr - numar de articole pe bloc).

Utilizand indexarea dupa cheia primara, numarul de blocuri citite va fi mai mare cu 1 decat numarul de nivele index x :

Cs = x + 1

Daca se utilizeaza hashing dupa cheie si se excepteaza coliziunile

Cs = 1

Daca se utilizeaza un fisier index ordonat multinivel si se cauta mai multe inregistrari cu conditii < , <= , > , >= pentru o cheie index :

Cs = x + n

unde x - numar de nivele fisier index ; n - numar de inregistrari selectate .

Inregistrarile ce indeplinesc conditia sunt consecutive in fisierul index ( in acelasi bloc index), dar fisierul fiind nesortat se gasesc in general in blocuri diferite. Daca fisierul este sortat, ele sunt consecutive si pot ocupa mai mult de un bloc ( n / bfr).

Cs = x + n / bfr

Utilizand fisier index multinivel pentru un atribut cu cardinalitate s (inregistrari cu aceeasi valoare a campului)

Cs = x + s / bfr - pentru cazul fisierului sortat

Cs = x + s - pentru fisierul nesortat

Pentru selectii dupa mai multe conditii :

- se selecteaza dupa atributul index si se elimina inregistrarile care nu indeplinesc conditia 2;

- daca ambele atribute din conditie au index, se selecteaza dupa conditia mai restrictiva (=) atributul cu cardinalitate minima;

- conditiile cu OR se transforma in doua selectii si se reunesc rezultatele;

- selectiile se executa inaintea JOIN- urilor.

Functiile de cost pentru JOIN. Pentru evaluare JOIN din doua fisiere R si S pe atributul A pe care nu exista index, se sorteaza S si R dupa A si se compara prin interclasare secventiala :

Cs = b_R + b_S + n / bfr

unde b_R si b_S sunt numar de blocuri din R si S, iar n - numar de inregistrari selectate.

Daca exista index in R pentru atributul A, fiecare inregistrare din S se citeste secvential si face acces la fisierul R prin index A verificand conditia :

Cs = b_S + r_S* ( x_R + 1) + n / bfr

Daca ambele fisiere R si S sunt indexate dupa atributul A, se parcurge secvential fisierul S, unde A era valori multiple si se cauta in R, unde A este cheie unica.

Pentru a mari posibilitatile limbajului SQL s-au adaugat utilitare sau subseturi de comenzi pentru introducerea datelor SQL*FORM si pentru formatarea rezultatelor si editarea de rapoarte SQL*PLUS.

g

5. Definirea vederilor in BD

Vederile ( View) intr-o BD servesc la manipularea si simplificarea accesului la anumite informatii. Ele nu ocupa spatiu de memorie, ci doar descriu o tabela virtuala, printr-o comanda SELECT, care selecteaza anumite coloane din tabele ale BD. Vederile se pastreaza intr-un fisier sistem unde se memoreaza expresia sursa a comenzii SELECT. Vederea poate fi compilata ca orice tabela si poate fi stearsa prin DROP VIEW. Din considerente de securitate a informatiei anumiti utilizatori au acces numai la anumite vederi din BD, cu un numar redus de coloane si inregistrari ( subset al BD).

Pentru a avea acces numai la studentii de la sectia Calculatoare si numai pentru Nume si Adresa, se poate defini o vedere :

CREATE VIEW calc_s AS -- definire vedere calc_s

SELECT nume, adresa FROM student WHERE cods = ‘AC’;

SELECT * FROM calc_s -- afiseaza studentii de la Calculatoare

Asupra unei vederi se pot executa si operatiile de INSERT, DELETE SI UPDATE. O vedere poate fi definita de administratorul aplicatiei pentru anumiti utilizatori.

Vederea poate fi mai complexa si sa se refere la mai multe tabele prin JOIN. Definirea unei vederi pentru a afisa notele studentilor de la AC si titlul cursurilor la care se refera, va fi echivalenta cu o tabela care contine cods, nume, nota, titlu curs :

CREATE VIEW note_s AS

SELECT S.cods, nume, nota, titlu FROM STUDENT S, NOTE N, CURS C

WHERE S.cods = N.cods AND N.codc = C.codc AND S.cods = ‘AC’

Afisarea notelor unui student se face simplu utilizand vederea :

SELECT nume, nota, titlu FROM note_s WHERE nume =’POP’

Utilizarea vederilor mareste putin timpul de acces, deoarece ele trebuie procesate inainte de executie. Vederile pot fi definite pe aplicatii pentru a simplifica intrebarile si a limita accesul unor grupe de utilizatori la o parte din informatiile din BD. Fiecare facultate are acces numai la studentii, notele, cursurile, profesorii care ii apartin, desi toate informatiile de acelasi tip din universitate se pastreaza in tabele unice.