1

BAZE DE DATE

I. Proiectarea bazelor de date

I.1. Preliminarii Bazele de date reprezint un instrument indispensabil pentru sistemele

informatice. Modelarea bazelor de date constitue un subiect vast care nu poate fi tratat complet ntr-un singur curs. Baza de date reprezint o modalitate de stocare pe un suport extern a unei mulimi de date care modeleaz un proces din lumea real, cu posibilitatea regsirii acesteia. Bazele de date sunt manipulate cu ajutorul sistemelor de gestiune a bazelor de date (SGBD).

Acestea, SGBD-urile, sunt responsabile cu crearea, manipularea i ntreinerea unei baze de date. Principala funcie a acestuia este de a permite utilizatorilor (prin intermediul programelor) s acceseze informaiile stocate n baza de date.

Cel mai rspndit model de baze de date este cel relaional, n care datele sunt memorate n tabele ntre care se stabilesc anumite legturi (asocieri). Pe lng tabele, o baz de date relaional mai poate conine: indeci, proceduri stocate, trigger-e, utilizatori i grupuri de utilizatori, tipuri de date, mecanisme de securitate i de gestiune a tranzaciilor etc.

Cursul propune trecerea n revist a principalelor probleme care apar n proiectarea i implementarea bazelor de date relaionale. Pentru exemplificarea conceptelor se utilizeaz sistemul de gestiune MySql.

I.1.1. Noiuni folosite n teoria bazelor de date

1. O baz de date reprezint un ansamblu structurat de fiiere care grupeaz datele prelucrate n aplicaii informatice ale unei persoane, grup de persoane, instituii etc. De asemenea, este definit ca o colecie de date aflate n interdependen, mpreun cu descrierea datelor i a relaiilor dintre ele.

2. Organizarea bazei de date se refer la structura bazei de date i reprezint un ansamblu de instrumente pentru descrierea datelor, relaiilor, restriciilor la care sunt supuse.

3. Sistemul de gestiune a bazei de date (SGBD) este un sistem complex de programe care asigur interferena ntre o baz de date i utilizatorii acesteia (exemple de programe: ACCESS, Fox Pro, PARADOX, ORACLE, MySQL) Funciile unui SGBD sunt:

1) idefinirea structurii bazei de date; 2) ncrcarea datelor n baza de date; 3) accesul la baza de date (interogare, actualizare); 4) ntreinerea bazei de date (refolosirea spaiilor goale, refacerea bazei de

date n cazul unor incidente); 5) reorganizarea bazei de date (restructurarea i modificarea strategiei de

acces);

2

6) securitatea datelor. 4. Data este un model de reprezentare a informaiei, accesibil unui anumit

procesor (om, program, calculator) i se definete prin: 1) Identificator; 2) Atribut; 3) Valoare.

5. Cele trei conceptele de baz utilizate n organizarea bazei de date sunt: 1) Entitatea 2) Atributul 3) valoarea

Prin entitate se nelege un obiect concret sau abstract reprezentat prin proprietile sale. O proprietate a unui obiect poate fi exprimat prin perechea (ATRIBUT, VALOARE). Exemplu: n exemplul Studentul x studiaz la facultatea de informatic., atributul este facultate, iar valoarea este reprezentat de cuvntul informatic. Alte exemple ar putea fi: (Sex, Feminin), (Nume, POP), (Profesie, Medic), (Salariu, 200). Observaie: Atributele pot caracteriza o clas de entiti, nu doar o entitate.

I.1.2. Funcionarea unei baze de date

Exploatarea unei baze de date aflate pe un suport specific (magnetic), de

ctre un utilizator, prin intermediul unui sistem de calcul, avnd la dispoziie un SGBD, parcurge uzual urmtoarele etape:

1. Utilizatorul, aflat la un terminal electronic, pune o "ntrebare" sau lanseaz o cerere referitoare la informaiile din baza de date. ntrebarea se poate pune ntr-un limbaj de cereri specific SGBD-ului cu care se lucreaz (dac utilizatorul este familiarizat cu acest limbaj - de exemplu, SQL, FoxPro, dBase, Oracle) sau utilizatorul poate fi asistat n adresarea cererii de date de ctre SGBD (produsul soft pe care l folosete) printr-un sistem de meniuri, butoane sau ferestre de dialog (obiecte de control).

2. ntrebarea este analizat de ctre calculator, de fapt de SGBD, iar dac este corect, i se d rspuns prin accesarea informaiilor din baza de date. Rspunsul va fi constituit din mulimea datelor cerute de utilizator, care verific criteriile specificate de acesta.

Acest proces de lansare a unei cereri de date care va fi satisfcut prin furnizarea datelor care ndeplinesc proprietile cerute se numete interogarea bazei de date.

3. Rspunsul la cererea de date se va afia pe ecran, se va tipri la imprimant sau se va memora ntr-un fiier.

n realizarea unei baze de date se urmrete: - micorarea timpului de rspuns la o interogare - asigurarea costurilor minime de prelucrare i ntreinere - adaptabilitatea la cerine noi (flexibilitate) - sincronizarea n exploatarea simultan a datelor de ctre mai muli utilizatori - asigurarea proteciei mpotriva accesului neautorizat (confidenialitate) - posibilitatea recuperrii datelor n cazul deteriorrilor accidentale (integritate)

etc. Exemplu: n figura I.1.1 este prezentat o baz de date foarte mic, ce conine un singur fiier, numit VINOTECA; la rndul su, aceasta cuprinde date despre

3

coninutul unei anumite vinoteci. n figura I.1.2 este prezentat un exemplu de operaie de consultare din baza de date, mpreun cu datele returnate prin aceast operaie.

Fig. I.1.1. Baza de date pentru VINOTECA (fiierul VINOTECA)

Fig. I.1.2. Exemplu de consultare

Observaie: n limbajul SQL, fiierul VINOTECA din figura I.1.1 este numit tabela sau relaie, rndurile unei astfel de tabele pot fi considerate ca nregistrri din fiier, iar coloanele pot fi considerate drept cmpuri sau atribute.

I.1.3. Realizarea unei baze de date

Realizarea unei baze de date presupune parcurgerea etapelor: 1. analiza domeniului din realitatea nconjurtoare pentru care se realizeaz

baza de date; 2. proiectarea structurii bazei de date; 3. implementarea structurii bazei de date; 4. ncrcarea datelor n baza de date; 5. exploatarea i ntreinerea bazei de date.

Consultare: SELECT vin, raft, producator FROM VINOTECA WHERE lansat = 2004;

Rezultat ce apare pe monitorul unui PC:

vin raft producator Cab. Sauvignon 2 Windsor

Pinot Noir 3 Fetzer Merlot 50 Clos du Bois

raft# vin producator an sticle lansat 2 Cab.

Sauvignon Windsor 1995 12 2004

3 Pinot Noir Fetzer 1997 3 2004 22 Pinot Noir Dehlinger 1999 2 2002 50 Merlot Clos du Bois 1998 9 2004

4

Fig. I.1.3. Realizarea unei baze de date

a) Analiza sistemului real Analiza sistemului real presupune stabilirea domeniului, analiza

componentelor sistemului i analiza legturilor (asocierilor) dintre aceste componente.

b) Proiectarea modelului conceptual Rezultatul final al etapei de analiz const n proiectarea modelului conceptual

asociat, n conformitate cu specificaiile unui anumit model: relaional, ierarhic, reea sau obiectual.

c) Implementarea structurii bazei de date Dac etapa de analiz a modelului conceptual se realizeaz independent de

un SGBD, prin aceast etap se ia n considerare SGBD-ul cu ajutorul cruia va fi implementat i exploatat baza de date.

Compilatorul limbajului de descriere a datelor permite aducerea schemei bazei de date la nivelul la care s poat fi memorat de ctre calculator.

Astfel, proiectarea presupune o detaliere, de exemplu, de tip pseudocod a modulelor necesare realizrii bazei de date: module pentru crearea fiierelor, pentru introducerea datelor, pentru prelucrarea i extragerea rezultatelor, pentru tratarea erorilor etc.

d) ncrcarea datelor n baza de date Este etapa n care se realizeaz popularea masiv cu date a bazei de date,

activitate care trebuie s se efectueze cu un minim de efort. e) Exploatarea i ntreinerea bazei de date

LMD

LMD LDD

LMD

Baza de date

1. Analiza

2. Proiectarea

3. Implementarea

4. ncrcarea datelor

5. ntreinerea LDD

5. Exploatarea

5

Exploatarea bazei de date de ctre diferii utilizatori finali este realizat n scopul satisfacerii cerinelor de informare ale acestora. SGBD sprijin utilizatorii finali n exploatarea bazei de date, oferind o serie de mecanisme i instrumente cum ar fi limbajele de manipulare a datelor (LMD).

ntreinerea bazei de date reprezint o activitate complex, realizat, n principal, de ctre administratorul bazei de date i care se refer la actualizarea datelor din cadrul bazei de date.

Cele cinci etape necesare realizrii unei baze de date vor fi tratate pe parcusul

ntregului curs urmrind un exemplu concret i anume o baz de date pentru o agenie imobiliar din ar, denumit AGENIE IMOBILIAR, care faciliteaz tranzacii de vnzare cumprare ntre vnztor i cumprtor, care gestioneaz documente legate de oferte imobiliare, de ntreinere a nomenclatoarelor specifice domeniului i care ofer o gam larg de rapoarte privind situaia vnzare-cumprare.

I.2. Construirea diagramei entitate-relaie

Prima etap pentru realizarea unei baze de date const n analiza sistemului real, urmat de proiectarea modelului conceptual Se cunosc mai multe metode de proiectare, dar cea mai des ntlnit este metoda entitate-relaie.

Prin metoda entiate-relaie (denumit i entitate-asociere) se construiete o diagram entiate-relaie (notat E-R) prin parcurgerea urmtorilor pai:

a) identificarea entitilor (componentelor) din sistemul proiectului; b) identificarea atributelor corespunztoare entitilor; c) stabilirea atributelor de identificare a entitilor. d) identificarea asocierilor (relaiilor) dintre entiti i calificarea lor;

a) Identificarea entitilor Prin entitate se nelege un obiect concret sau abstract reprezentat prin

proprietile sale. Prin convenie, entitile sunt substantive, se scriu cu litere mari i se reprezint prin dreptunghiuri. ntr-o diagram nu pot exista dou entiti cu acelai nume, sau o aceeai entitate cu nume diferite.

Pentru baza de date din domeniul imobiliar considerat anterior, se pot pune n eviden urmtoarele entiti:

- DATE_PERSOAN entitate care stocheaz date personale ale ofertantului (vnztorului) sau ale clientului (cumprtorului);

- CERERI_OFERTE conine ofertele sau cererile imobiliare propuse de vnztori, respectiv cumprtori;

- DESCRIERE_IMOBIL stocheaz informaiile referitoare la imobile; - JUDEE entitate nomenclator ce conine judeele din ar; - LOCALITI entitate ce conine localitile n care sunt amplasate

imobilele; - STRZI - entitate ce precizeaz strzile n care sunt amplasate imobilele; - FACTURI formularul necesar unei tranzacii de cumprare-vnzare.

Figura urmtoare prezint o prim form a diagramei entitate-asociere (E-R).

6

Fig. I.2.1. Diagrama E-R pentru domeniul imobiliar (prima form)

b) Identificarea atributelor entitilor i a asocierilor dintre entiti Atributele unei entiti reprezint proprietile acesteia. Atributele sunt substantive, iar pentru fiecare atribut se va preciza tipul fizic (integer, float, char, string etc.) Exemplu: Entitatea LOCALITI are urmtoarele atribute: codul localitii, notat cod_loc, simbolul de identificare al judeului simbol_jude i denumirea localitii nume_loc.

c) Stabilirea atributelor de identificare a entitilor Un atribut de identificare (numit cheie primar), reprezint un atribut care se caracterizeaz prin unicitatea valorii sale pentru fiecare instan a entitii. n cadrul diagramei entitate-asociere, un atribut de identificare se marcheaz prin subliniere sau cu simbolul # plasat la sfritul numelui acestuia.

Fig. I.2.2. Notaii uzuale pentru atributele de identificare

Exemplu: Ca atribut de identificare putem considera codul numeric personal cnp pentru entitatea DATE_PERSOAN.

Pentru ca un atribut s fie atribut de identificare, acesta trebuie s satisfac unele cerine:

- ofer o identificare unic n cadrul entitii; - este familiar utilizatorului; - este scurt (de cele mai multe ori, atributul de identificare apare i n alte

entiti, drept cheie extern). Pentru o entitate pot exista mai multe atribute de identificare, numite atribute

(chei) candidate. Dac exist mai multe, cheia candidat va selecta una, preferndu-se cea cu valori mai scurte i mai puin volatile.

d) Identificarea asocierilor dintre entiti i calificarea lor

ntre majoritatea componentelor (adic a entitilor) unui sistem real se stabilesc legturi (asocieri).

E a

(a)

E a#

(b)

DATE_ PERSOANA

CERERI_ OFERTE

DESCRIERE_IMOBIL STRAZI

JUDETE

LOCALITATI

FACTURI

7

Exemplu: Exist o asociere ntre entitile CERERI_OFERTE i FACTURI deoarece facturile reprezint finalizarea unei cereri/oferte. Aceast asociere se reprezint ca n figura de mai jos.

Fig. I.2.3. Prezentarea asocierii dintre entitile CERERI_OFERTE i FACTURI

Sunt necesare precizarea ctorva notaii i noiuni utilizate n exemplul de mai sus:

- legturile (asocierile) se reprezint prin arce neorientate ntre entiti; - fiecrei legturi i se acord un nume plasat la mijlocul arcului i simbolizat

printr-un romb (semnificaia legturii); - numerele simbolizate deasupra arcelor se numesc cardinaliti i reprezint

tipul legturii; - cardinalitatea asocierilor exprim numrul minim i maxim de realizri a unei

entiti n raport cu entitatea asociat. Exemplu: Cardinalitatea (1,1) ataat entitii CERERI_OFERTA nseamn c o factur poate fi rezultatul tranzacionrii a minim unei cereri/oferte i a unui numr maxim de tot o cerere/ofert. Cardinalitatea (0,1) ataat entitii FACTURI nseamn c o cerere se poate finaliza prin maxim o factur sau prin nici una (0 facturi) . Aceast cardinalitate reiese din analiz:

Fig. I.2.4. Determinarea cardinalitii asocierii dintre entitile CERERI_OFERTE i

FACTURI

Maximele unei cardinaliti sunt cunoscute i sub denumirea de grad de asociere, iar minimele unei cardinaliti, obligativitatea participrii entitilor la asociere.

Tipuri de asocieri (legturi) ntre entiti

Asocierile pot fi de mai multe feluri, iar odat cu asocierea, se impune stabilirea calificrii acesteia. Asocierea dintre entiti se face n funcie de

i) cardinalitatea asocierii; ii) numrul de entiti distincte care particip la asociere.

i. Dup cardinalitatea asocierii n funcie de maxima cardinalitii (gradul de asociere), se cunosc trei tipuri de asocieri, care, fiecare la rndul lor, sunt de dou tipuri, n funcie de minima cardinalitii (gradul de obligativitate al participrii la asociere):

asocieri de tip unu la unu; o asocieri pariale de tip unu la unu

CERERI_OFERTE

1 2 3

FACTURI F1 F2

CERERI_OFERTE sunt finalizate prin

FACTURI (1,1) (0,1)

8

o asocieri totale de tip unu la unu asocieri de tip unu la mai muli

o asocieri pariale de tip unu la muli o asocieri totale de tip unu la muli

asocieri de tip muli la muli o asocieri pariale de tip muli la muli o asocieri totale de tip muli la muli.

ii. Dup numrul de entiti distincte care particip la asociere: asocieri binare (ntre dou entiti distincte); asocieri recursive (asocieri ale entitilor cu ele nsele); asocieri complexe (ntre mai mult de dou entiti distincte).

n continuare se descriu asocierile grupate dup cardinalitatea ei. Asocieri n funcie de cardinalitatea legturii

1. Asocieri de tip unu la unu sunt asocieri n care maximele cardinalitii au valoarea 1.

Fig. I.2.5. Asociere de tip unu la unu

Exemplu: Asocierea din figura I.2.3. este asociere de tip 1 la 1.

2. Asocieri de tip unu la mai muli sunt asocieri n care maxima cardinalitii unei entiti este unu, iar a celeilalte entiti are valoarea muli.

Fig. I.2.6. Asociere de tipul unu la mai muli

Exemplu:

Fig. I.2.7. Asociere de unu la mai muli ntre entitile LOCALITI i CERERI_OFERTE

(0,n) (1,1) LOCALITATI CERERI_OFERTE

i corespunde

LOCALITATI L1 L2 L3

CERERI_OFERTE 1 2 3

A B

E1 E2 A (...,1) (...,n)

E1 E2 A (...,n) (...,1)

A (...,1) E1 E2 (...,1)

9

3. Asocieri de tipul muli la muli sunt asocieri n care maximele cardinalitii au valoarea muli.

Fig. I.2.8. Asociere de tipul muli la muli

Exemplu:

Fig. I.2.9. Asociere de tipul muli la muli ntre entitile DEPOZIT i PRODUS

Observaie: Uneori (n cazul utilizrii unor SGBD), asocierea de tip muli la muli se transform n dou asocieri de tipul unul la muli fiind, de regul, mai uor de implementat i de utilizat i anume:

Fig. I.2.10. Transformarea unei asocieri de tipul muli la muli (a) n asocieri de tipul

unu la muli (b) Exemplu: n cazul exemplului de mai sus (vezi figura I.2.9.), transformarea asocierii muli la muli n asocieri de tipul unu la muli se poate realiza prin construirea unei noi entiti DEPOZIT_PRODUS astfel:

Fig. I.2.11. Transformarea asocierii de tipul muli la muli n asocieri de tipul unu la muli

Din E1 E2 (...,n) (...,n)

n E1 E E2 (...,1) (...,n) (...,n) (...,1)

A A1 A2

a) b)

DEPOZIT

PRODUS nmagazi

neaz

(0,n) (0,n)

DEPOZIT D1 D2 D3 D4

PRODUS P1 P2 P3 P4

A (...,n) E1 E2 (...,n)

DEPOZIT D1 D2 D3 D4

PRODUS P1 P2 P3 P4

DEPOZIT_ PRODUS D1-P1 D1-P3 D2-P2 D3-P2

(1,1) asociaz asociaz

(0,n) (0,n) (1,1)

10

4. Asocieri pariale i totale

Printr-o asociere parial se nelege o asociere n care nu exist obligativitatea participrii la aceast asociere a tuturor entitilor vizate, ci numai a unora dintre ele sau a nici uneia. Asocierea parial se caracterizeaz prin faptul c minima cardinalitii ataat unei entiti este zero. Observaii (asupra minimii cardinalitii):

- minima cardinalitii este zero, are drept rezultat lipsa obligativitii participrii partenerului la aceast asociere;

- minima cardinalitii este mai mare dect zero, are drept rezultat obligativitatea participrii.

Fig. I.2.12. Asocieri pariale ntre entitile E1 i E2

Exemplu: Asocierea dintre entitile CERERI_OFERTE i FACTURI din figura I.2.3 reprezint o asociere parial, deoarece participarea entitii FACTURI nu este obligatorie, minima caracteristicii corespunztoare entitii CERERI_OFERTE fiind 0. O asociere este total dac toate entitile au obligativitatea s participe la asociere, adic minima cardinalitii este mai mare dect zero.

Fig. I.2.13. Asocieri totale ntre entitile E1 i E2

n continuare se dau cteva exemple de asocieri totale, respectiv pariale. Exemplu: Asocieri pariale de tip unu la unu

E1 E2 A

a)

(1,) (1,)

E1 E2 (1,) (n,)

E1 E2 (n,) (n,)

E1 E2 (n,) (1,)

b) c)

d)

A

A

A

E1 E2 E1 E2 A A

a) b)

(0,) (,) (,) (0,)

CERERI_OFERTE

1 2 3

FACTURI F1 F2

11

Exemplu: Asocieri totale de tip unu la unu

Exemplu: Asocieri pariale de tip unu la muli

Exemplu: Asocieri totale de tip unu la muli

Exemplu: Asocieri pariale de tip muli la muli

Exemplu: Asocieri totale de tip muli la muli

Fig. I.2.14. Asocieri dup gradul i obligativitatea lor n exemplul bazei de date AGENTIE_IMOBILIARA, tipurile de asocieri dintre entiti stabilite n funcie de modul n care se desfoar activitatea modelat sunt:

CURSURI

C1 C2 C3

STUDENTI

S1 S2 S3 S4

CERERI_OFERTE

1 2 3

DESCRIERE_IMOBIL

I1 I2 I3

LOCALITATI L1 L2 L3

CERERI_OFERTE

1 2 3

DEPOZIT

D1 D2 D3 D4

PRODUS

P1 P2 P3

CLASE C1 C2 C3

ELEVI E1 E2 E3 E4

12

- JUDETE-LOCALITATI 1:n deoarece unui jude i corespunde mai multe localiti;

- LOCALITATI-STRAZI 1:n - deoarece unei localiti i corespunde mai multe strzi;

- STRAZI-CERERI_OFERTE 1:n deoarece unei strzi i poate corespunde mai multe oferte/cereri;

- FACTURI-CERERI_OFERTE 1:1 deoarece fiecare factur conine doar cte o ofert/cerere;

- CERERI_OFERTE-DESCRIERE_IMOBIL 1:1 fiecrui imobil i se face o singur descriere;

- FACTURI-DATE_PERSOANA 1:n o factur este ncheiat de o singur persoan, iar o persoana poate incheia mai mlte facturi;

- DATE_PERSOANA-CERERI 1:n o persoan poate lansa mai multe cereri sau oferte de imobil.

Exemplu: n urma analizrii celor 4 etape necesare construirii diagramei entitate-asociere i anume: - identificarea entitilor domeniului sau a sistemului economic; - identificarea asocierilor dintre entiti; - identificarea atributelor aferente entitilor i asocierilor dintre acestea; - stabilirea atributelor de identificare a entitilor, se poate prezenta forma complet a diagramei asociate sistemului ales n exemplu. (vezi figura I.2.15)

Fig. I.2.15. Diagrama E-R pentru domeniul imobiliar (a doua form)

n cazul n care se dorete o diagram care s conin i atributele fiecrei entiti nsoite de precizarea atributelor de identificare, adic a cheilor primare, pentru a nu ncrca imaginea, diagrama proiectului se poate fragmenta pe mici domenii, dup cum este cazul entitii OFERTE, prezentat n figura I.2.16. (S-au considerat un numr relativ mic de atribute).

STRAZI LOCALITAT JUDETE

DATE_PERSOANA

FACTURI

CERERI_ OFERTE

DESCRIERE _IMOBIL

are asociat

finisate

conin

conin

(1,1) (1,1) (0,n) (0,n) are asociat

se regsete

(1,1)

(1,n)

(1,n) (1,1)

(1,1)

(1,1)

(0,1)

(1,1) incheie

(1,1)

(0,n)

13

Fig. I.2.16. Reprezentarea atributelor aferente entitii CERERI_OFERTE (detaliu

dintr-o diagram E-R) n reprezentarea atributelor aferente entitii CERERI_OFERTE semnificaia atributelor este urmtoarea: cheia primar a entitii id_co reprezint numrul de ordine al cererii sau ofertei de imobil lansat de o anumit pesoan, atributul tipul specific dac este vorba de o cerere sau de o ofert, prin cnp se precizeaz codul numeric personal al clientului, data_inreg reprezint data la care s-a nregistrat oferta/cererea, apoi uremaz cteva date legate de imobil: codul strzii id_strada, numrul imobilului nr_imobil, preul minim, respectiv preul maxim al imobilului pret_min, pret_max. Ultimul atribut, tip_solutionare precizeaz dac cererea/oferta respectiv a fost soluionat; pentru o cerere/oferta nou introdus, acest atribut se va completa cu explicaia de nesoluionat.

Astfel, diagrama bazei de date AGENIE IMOBILIAR conine 7 entiti a cror asociere a fost prezentat n figura I.2.15.

Fig. I.2.17. Baza de date AGENIE IMOBILIAR- entiti i atribute

DATE_PERSOANA cnp# numele adresa nr_telefon email

banca_client nr_cont_client

STRZI id_strada# cod_loc# nume_str

LOCALITATI cod_loc# simbol_judet nume_loc

JUDETE simbol_judet# nume_judet

CERERI-OFERTE id_co # tip cnp data_inreg tip_solutionare cod_loc id_strada nr_imobil pret_min pret_max

DESCRIERE_IMOB IL id_co# tip_imobil etaj nr_camere suprafata garaj centrala_termica termopane

FACTURI nr_factura# id_oferta data_factura cnp pret TVA total

CERERI_OFERTE

tipul

data_inreg

cod_loc

id_strada

nr_imobil

pret_max

tip_solutionare

cnp

pret_min

id_co#

14

I.3. Proiectarea modelului relaional

Proiectarea corect a bazelor de date este crucial pentru obinerea unei aplicaii de nalt performan.

Modelul relaional este cel mai utilizat dintre modelele de date existente (modele ierarhice, modele reea, modele orientate pe obiect). Avantajele modelului relaional sunt:

- propune structuri de date uor de utilizat; - asigur independena logic i fizic; - pune la dispoziia utilizatorilor limbaje neprocedurale; - optimizeaz accesul la date; - mbuntete confidenialitatea datelor.

Din punct de vedere istoric, trebuie menionat c modelul relaional s-a conturat n dou articole publicate de ctre F.E. Codd n 1969 i 1970, matematician la centrul de cercetri (California) I.B.M. Codd a propus o structur de date tabelar, independent de tipul de echipamente i de software-ul de sistem pe care este implementat. Dei puternic matematizat, modelul relaional este relativ uor de neles. Dac, teoretic, modelul s-a consacrat n anii 1970, produsele software care s gestioneze baze de date au devenit populare abia n anii 80. Cele mai utilizate sisteme de gestiune a bazelor de date relaionale (SGBDR) dedicate uzului individual sunt: ACCESS, PARADOX, Visual Fox Pro. Pentru aplicaiile complexe se folosesc SGBDR-urile de categorie grea: ORACLE, DB2 IBM, Informix IBM, SyBase (SyBase), SQL Server (MicroSoft). Sunt mult mai robuste, fiabile, dar i costisitoare. n ultimul timp i-au fcut apariia aa-zisele SGBD-uri (aproape) gratuite: PostgreSQL, MySQL, FireBird etc. (Acestea ruleaz de obicei pe sisteme de operare Linux). Modelul relaional are la baz teoria matematic a relaiilor i poate fi privit ca o mulime de tabele obinute prin metoda normalizrii, eliminndu-se astfel anomaliile de actualizri. Conceptele modelului relaional sunt:

1. structura relaional a datelor; 2. operatorii modelului relaional; 3. restriciile de integritate ale modelului relaional.

I.3.1. Structura relaional a datelor

O baz de date relaional (BDR) reprezint un ansamblu de relaii, prin care se reprezint datele i legturile dintre ele.

n cadrul unei baze de date relaionale, informaiile sunt organizate sub forma unor tablouri bidimensionale (tabele), numite relaii. Asocierile dintre relaii se reprezint prin atributele de legtur. n cazul legturilor de tip unu la muli, aceste atribute figureaz ntr-una dintre relaiile implicate n asociere. n cazul legturilor de tip muli la muli, atributele sunt situate ntr-o relaie distinct, construit special pentru explicarea legturilor ntre relaii.

Prezentarea structurii relaionale a datelor impune definirea noiunilor de: 1. domeniu; 2. relaie; 3. atribut;

15

4. schem a unei relaii. Conceptele utilizate pentru a descrie formal, uzual sau fizic elementele de

baz ale organizrii datelor sunt date n urmtorul tabel:

Fig. I.3.1. Concepte uzuale folosite n exprimarea formal, uzual i fizic Domeniul

Domeniul se reprezint printr-un tip de date i se noteaz prin litere mari D1,D2 etc. Exemplu: D1: {Da, Nu} reprezint un domeniu definit explicit. D2: {x/ x este de dat calendaristic} sau D3: {s/ s este numr decimal} reprezint domenii definite implicit, unde prin numr decimal se nelege un numr zecimal pentru care se precizeaz numrul de cifre componente. Printr-un tuplu se nelege o succesiune de valori de diferite tipuri. Un tuplu se noteaz enumernd valorile sale , unde V1 este o valoare din

domeniul D1, V2D2 etc. Exemplu: Considerm c tuplul referitor la persoana x din entitatea CERERI_OFERTE conine trei valori diferite ce desemneaz: - codul numeric personal (cnp): 1701205230023; - data nregistrrii ofertei (data_nreg): 2006-07-03; - tipul soluionrii (tip_soluionare): Nu. Se formeaz tuplul . Relaia

Relaia R este un subansamblu al produsului cartezian dintre mai multe domenii D1, D2, ..., Dn, reprezentat sub forma unei tabele de date (tabelul bidimensional) i deci, o mulime de tupluri. Exemplu: Considerm c: - D1 cuprinde valori referitoare la tipul soluionrii tranzaciei: Da, dac tranzacia a fost soluionat, Nu, n caz contrar; . D2 cuprinde valori ale datei calendaristice; - D3 conine valori care exprim cnp-ul persoanei. De asemenea considerm c se cunosc datele a doi ofertani i c fiecare pune n vnzare doar cte un imobil. Atunci definim relaia R prin tuplurile care descriu aceste informaii ale ofertelor celor dou persoane:

R: {, }, sau

R: R: D2 D3 D1 2661805270023 2006-05-27 Da 1701205230023 2006-07-03 Nu

Fig. I.3.2. Variante de prezentare a unei relaii R

Formal Uzual Fizic Relaie Tablou Fiier Tuplu Linie nregistrare Atribut Coloan Camp Domeniu Tip de dat Tip de dat

16

Observaii: 1. ntr-o relaie tuplurile trebuie s fie distincte, adic s difere prin cel puin o valoare. 2. Cardinalul relaiei este numrul tuplurilor dintr-o relaie.

Gradul relaiei este numrul valorilor dintr-un tuplu. Atributul

Un atribut reprezint o proprietate a unei entiti din cadrul relaiei. Acesta se materializeaz printr-o coloan a unei tabele. Exemplu: R:

cnp: D2 data_nreg:D3 tip_soluionare:D3 2661805270023 2006-05-27 Da 1701205230023 2006-07-03 Nu

Fig. I.3.3. Relaia R reprezentat cu ajutorul atributelor

Atributele sunt utile atunci cnd ntr-o relaie un domeniu apare de mai multe

ori. Prin numele dat fiecrui atribut, se difereniaz atributele care conin valori ale aceluiai domeniu, eliminnd dependena fa de ordine.

Schema unei relaii

Schema unei relaii este numele relaiei urmat de lista de atribute, pentru fiecare atribut precizndu-se domeniul asociat.

Astfel, pentru o relaie R cu atributele A1, A2, ... , An i domeniile D1, D2, ... ,Dm, cu m n, schema relaiei R poate fi prezentat astfel:

R(A1: D1, A2:D2, ... , An: Dm) sau R: A1:D1 ... An:Dm

Fig. I.3.4. Reprezentarea schemei relaiei R

Ca o concluzie, dintre caracteristicile modelului relaional menionm: - nu exist tupluri identice; - ordinea tuplurilor i atributelor nu are importan; - valorile unui atribut sunt omogene; - fiecare atribut definete un domeniu distinct i nu se poate repeta n cadrul aceleiai relaii.

17

I.4. Operatorii modelului relaional

I.4.1. Operatorii modelului relaional

Algebra relaional (relational algebra) exprim interogrile prin aplicarea unor operatori specializai (operatorii algebrei relaionale) asupra relaiilor.

Operatorii modelului relaional aparin algebrei relaionale. Algebra relaional este o colecie de operaii pe relaii, fiecare operaie avnd drept operanzi una sau mai multe relaii, rezultatul fiind tot o relaie. E.F. Codd a propus opt operaii ale algebrei relaionale, grupate n dou categorii:

1. Operaii pe mulimi: reuniunea (union), intersecia (intersection), diferena (difference) i produsul cartezian (Cartesian product). Aceste operatii reprezint adaptarea operaiilor corespunztoare din teoria mulimilor i acioneaz asupra relaiilor vzute ca mulimi de elemente (tupluri), fr a lua n consideraie compoziia fiecrui element.

2. Operaii relaionale speciale: restricia (restriction), proiecia (projection), jonciunea (join) i diviziunea (division). Aceste operaii iau n consideraie compoziia tuplurilor, formate din valori ale atributelor relaiilor. Reuniunea

Reuniunea reprezint o operaie definit pe dou relaii: R1 i R2, ambele cu aceeai schem, n urma creia se construiete o nou relaie R3, cu aceeai schem ca i R1 i R2 i avnd drept rezultat tuplurile din R1 i R2, luate mpreun fircare o singur dat.

Notaii: R1U R2 OR (R1, R2) APPEND (R1, R2) UNION (R1, R2) Reprezentarea grafic

Fig. I.4.1. Reprezentarea grafic a operaiei de reuniune a dou relaii

Exemplu: Deoarece aplicaia AGENTIE IMOBILIARA luat ca exemplu n acest curs nu conine dou relaii cu aceeai structur, pentru a putea exemplifica operaia de reuniune se vor construi dou relaii ARHIVA_OFERTE i ARHIVA_CERERI populate cu informaiile aferente ofertelor respectiv cererilor soluionate (s-au ales doar trei atribute: id-ul ofertei sau a cererii, cnp-ul clientului, tipul soluionrii). Pentru a afla care sunt toate ofertele i cererile soluionate, se realizeaz operaia de reuniune.

R1

R3

R2

18

Fig. I.4.2. Reuniunea relaiilor ARHIVA_OFERTE i ARHIVA_CERERI

Diferena

Diferena reprezint o operaie definit pe dou relaii R1 i R2, ambele cu o aceeai schem, n urma creia se construiete o nou relaie R3, cu schema identic cu R1 i R2, avnd drept rezultat acele tupluri ale relaiei R1 care nu se regsesc n relaia R2.

Notaii: R1 R2 REMOVE (R1, R2) MINUS (R1, R2)

Reprezentarea grafic:

Fig. I.4.3. Reprezentarea grafic a operaiei de diferen a dou relaii

Exemplu: Presupunnd c exist clieni care au nregistrri n ambele tabele (adic au oferit imobil spre vnzare, dar i au achiziionat un alt imobil n acelai timp), pentru a afla care au fost doar ofertanii de imobile, se aplic diferena dintre relaiile ARHIVA_OFERTE i ARHIVA_CERERI.

Fig. I.4.4. . Diferena relaiilor ARHIVA_OFERTE i ARHIVA_CERERI

-

id tipul cnp tip_solutionare 1210 oferta 1881106300897 Da 2066 oferta 2660805270023 Da

REZ:

ARHIVA_OFERTE: ARHIVA_CERERI:

id tipul cnp tip_solutionare 2066 oferta 2660805270023 Da

id tipul cnp tip_solutionare 221 cerere 2820506300898 Da 1210 cerere 1881106300897 Da 3161 cerere 2690125270032 Da

id tipul cnp tip_solutionare 1066 oferta 2660805270023 Da 1210 oferta 1881106300897 Da

REZ:

ARHIVA_OFERTE: ARHIVA_CERERI:

id tipul cnp tip_solutionare 1066 oferta 2660805270023 Da 1210 oferta 1881106300897 Da 210 cerere 2820506300898 Da 1316 cerere 1881106300897 Da

id tipul cnp tip_solutionare 210 cerere 2820506300898 Da 1316 cerere 1881106300897 Da

R1

R3

R2

-

19

Produsul cartezian

Produsul cartezian reprezint o operaie definit pe dou relaii R1 i R2, n urma creia se construiete o nou relaie R3, a crei schem se obine prin concatenarea schemelor relaiilor R1 i R2, avnd ca extensie toate combinaiile tuplurilor din R1 cu cele din R2.

Notaie: R1xR2 PRODUCT (R1, R2) TIMES (R1, R2)

Reprezentarea grafic:

Fig. I.4.5. Reprezentarea grafic a produsului cartezian

Exemplu: Operaia de produs cartezian va fi exemplificat pe un exemplu independent de aplicaia AGENIA IMOBILIAR considerat. Astfel:

Fig. I.4.6. Produsul cartezian dintre relaiile LOCALIT i TARIFE

Proiecia

Proiecia reprezint o operaie a algebrei relaionale definit asupra unei relaii R, n urma creia se construiete o nou relaie P, n care se gsesc acele atribute din R specificate explicit n cadrul operaiei (se poate interpreta ca tiere vertical).

Prin operaie de proiecie se trece de la o relaie de grad n (are n atribute) la o relaie de grad mai mic, p (pn).

Notaie: )(,...,, Rmjj AAA

],...,,[ mjj AAAR

),...,,,( mjj AAARPROJECT

Reprezentarea grafic:

Localit D1 Jude D1 Bora Maramure Braov Braov

Transport D3 Tarif D4 autobuz 1,1 troleibuz 1,0

Localit D1 Jude D1 Transport D3 Tarif D4 Bora Maramure autobuz 1,1 Braov Braov autobuz 1,1 Bora Maramure troleibuz 1,0 Braov Braov troleibuz 1,0

TRANSPORT:

TARIFE: LOCALIT:

R1

R3

R2

20

Fig. I.4.7. Reprezentarea grafic a operaiei de proiecie

Exemplu: Pentru a obine o list cu numele i numerele de telefon ale ofertanilor/cumprtorilor, se poate aplica operaia de proiecie a relaiei DATE_PERSOANE asupra atributelor numele, nr_telefon

Fig. I.4.8. . Proiecia relaiei DATE_PERSOANA pe atributele numele,

nr_telefon

Selecia

Selecia reprezint o operaie definit asupra unei relaii R, n urma creia se construiete o nou relaie S, cu aceeai schema ca R, avnd extensia construit din acele tupluri din R care satisfac o condiie menionat explicit n cadrul operaiei (se poate interpreta ca tiere orizontal: nu toate tuplurile din R satisfac aceast condiie).

Condiia precizat n cadrul operaiei de selecie se reprezint sub forma:

unde operator de comparaie poate fi unul din semnele sau .

Notaie: condiie (R) R [condiie]

RESTRICT (R, condiie) Reprezentarea grafic:

DATE_PERSOANA:

cnp numele adresa nr_telefon email 2660805270023

Pop Ana Str. Viilor, nr.55/4, Oradea, Bihor

-

pa@yahoo.it

1701205230032 Sas Ioan Str. Victoriei, nr.22/12, Baia Mare, Maramures

0362/409209 -

numele, nr_telefon

numele nr_telefon Pop Ana - Sas Ioan 0362/409209

REZ:

atribut, operator de comparaie, valoare

Aj,Aj,...,Am

P

R

21

Fig. I.4.9. Reprezentarea grafic a operaiei de selecie

Exemplu: n cazul n care se dorete afiarea ofertelor/cererilor anterioare datei de 2006-07-03, se poate aplica operaia de selecie asupra relaiei CERERI_OFERTE, dup cum se va vedea n figura I.4.10.

Fig. I.4.10. Selecia efectuat asupra relaiei CERERI_OFERTE

Jonciunea

Jonciunea (joinul) reprezint o operaie definit pe dou relaii: R1 i R2, n

urma creia se construiete o alt relaie R3, prin concatenarea unor tupluri din R1 cu tupluri din R2 care ndeplinesc o anumit condiie, specificat explicit n cadrul operaiei.

Notaie: R1 R2; JOIN(R1,R2,condiie)

Reprezentarea grafic:

Fig. I.4.11. Reprezentarea grafic a operaiei de jonciune

OFERTE:

Data_nreg

22

Condiia de concatenare din cadrul operaiei de jonciune este de forma:

n funcie de operatorul de comparaie din condiia de concatenare, joiciunea

poate fi de mai multe feluri, ns cel mai important este echijonciunea (equijoinul):

Exemplu: Aplicnd operaia de echijoncine relaiilor DATE_PERSOANE i FACTURI pentru atributul cnp, se obin informaii referitoare la clienii care au ncheiat facturi. Pentru a nu ncrca figura, pentru cele dou relaii s-au ales doar cteva atribute.

Fig. I.4.12. Operaia de echijoncine a relaiilor DATE_PERSOANA i FACTURI Observaie: Operaia de jonciune se poate exprima cu ajutorul operaiilor de produs cartezian i selecie, rezultatul unei jonciuni fiind asemenea cu cel al operaiei de selecie asupra unui produs cartezian: JOIN (R1, R2, condiie) = RESTRICT (PRODUCT (R1, R2), condiie). Este indicat utilizarea jonciunii n locul produsului cartezian, de cte ori este posibil, deoarece are o mai bun vitez de execuie. Tipuri de jonciuni

n funcie de tipul condiiilor de conectare, modul de definire a schemei i de rezultatul obinut, exist mai multe tipuri de jonciuni, cele mai utilizate n practic fiind

1. jonciunea natural 2. jonciunea extern 3. semijonciunea.

cnp cnp

=

DATE_PERSOANA: FACTURI:

cnp numele adresa nr_telefon nr_ factura

id_co cnp

1551212245038 Pop Radu

Str. Al. Cuza, nr.4/34, Ploiesti

0744304505 22 43 1551212245038

nr_ factura#

id_co cnp

22 43 1551212245038

cnp# numele adresa nr_ telefon

1551212245038 Pop Radu

Str. Al. Cuza, nr.4/34, Ploiesti

0744304505

2560405570053 Chis Alina

Str. Luminii, 76, Buzau

0721435622

atribut din R1 operator de comparaie atribut din R2

atribut din R1 = atribut din R2

REZ

23

Jonciunea natural

Jonciunea natural este jonciunea n care atributele au acelai nume i aceleai valori. Prin definiie, jonciunea natural este o operaie definit pe dou relaii R1 i R2, n urma creia se construiete o nou relaie R3, a crei schem este obinut prin reuniunea atributelor din relaiile R1 i R2 (atributele cu aceleai nume se iau o singur dat) i a crei extensie conine tuplurile obinute prin concatenarea tuplurilor din R1 cu cele din R2 care prezint aceleai valori pentru atributele cu aceleai nume.

Jonciunea natural elimin inconvenientul ce apare n cazul echijonciunii i anume: schema relaiei n cazul echijoncinuii conine toate atributele celor dou relaii. Astfel, n relaia rezultat R3 a jonciunii naturale, atributele cu acelai nume vor aprea o singur dat.

Reprezentarea grafic:

Fig. I.4.13. Reprezentarea grafic a operaiei de jonciune natural

Exemplul 1: Relund exemplul anterior, prin jonciunea natural se elimin atributul repetitiv cnp.

Fig. I.4.14. Operaia de jonciune natural a relaiilor DATE_PERSOANA i

FACTURI

Exemplul 2: Dac se dorete aflarea denumirilor localitilor n care sunt oferte sau cereri, cum n relaia CERERI_OFERTE se afl doar codul localitii respective iar n

cnp cnp

=

DATE_PERSOANA: FACTURI:

REZ cnp numele adresa nr_telefon nr_ factura

id_co

1551212245038 Pop Radu

Str. Al. I. Cuza, nr.4/34, Ploiesti

0744304505 22 43

nr_ factura#

id_co cnp

22 43 1551212245038

cnp# numele adresa nr_ telefon

1551212245038 Pop Radu

Str. Al. Cuza, nr.4/34, Ploiesti

0744304505

2560405570053 Chis Alina

Str. Luminii, 76, Buzau

0721435622

R1 R2

R3

24

relaia LOCALITATI este asociat fiecrui cod de localitate denumirea localitii, trebuie s se realizeze o jonciune natural ntre aceste dou relaii. Astfel rezultatul jonciunii va fi cel prezentat n figura I.4.15.

Fig. I.4.15. Jonciunea natural a relaiilor CERERI_OFERTE i LOCALITATI

Observaie: Jonciunea natural este comutativ.

Jonciunea extern

Jonciunea extern este jonciunea n a crei rezultat R3 apar i tuplurile din R1 i R2 care nu au participat la jonciune. Aceste tupluri sunt completate cu valora NULL. Jonciunea extern elimin inconvenientul cauzat de jonciunea natural i anume pierderea de tupluri (vezi figura I.4.15, tuplul nu mai apare n relaia REZ deoarece simbolul MM430 corespunztor atributului cod_loc din relaia CERERI_OFERTA nu figureaz printre valorile atributului cu acelai nume din relaia LOCALITATI.

Reprezentarea grafic:

Fig. I.4.16. Reprezentarea grafic a operaiei de jonciune extern

Observaie: Jonciunea extern este o operaie care din punct de vedere al programrii prezint inconvenientul manipulrii valorilor nul.

cod_loc cod_loc LOCALITATI:

CERERI_OFERTE:

REZ:

id_ co#

tipul cnp data_ inreg

cod_ loc

id_ strada

nr_ imobil

pret_ min

pret_ max

tip_ solutionare

nume _loc

simbol_ judet

12 oferta 1701205230023 2006-07-03

BV230 120 52 30 35 da Brasov BV

234 cerere 2760805270024 2006-05-27

CJ147 120 22 45 47 da Cluj-Napoca

CJ

cod_loc# nume_loc simbol _judet

CJ147 Cluj-Napoca

CJ

BV230 Brasov BV

id_ co#

tipul cnp data_ inreg

cod_loc id_ strada

nr_ imobil

pret_ min

pret_ max

tip_ solutionare

12 oferta 1701205230023 2006-07-03 BV230 120 52 30 35 da 234 cerere 2760805270024 2006-09-21 CJ147 120 22 45 47 da 44 oferta 2661111246642 2006-09-17 MM430 133 4 50 nu

R1 R2

R3

25

Exemplu: n relaia REZ, localitii Botoani nu i s-a asignat nici un jude.

Fig. I.4.17. Operaia de jonciune extern a relaiilor LOCALITI i JUDEE

Observaie: Jonciunea extern nu este comutativ. Exemplu: Rezultatul jonciunii externe dintre relaiile JUDETE i LOCALITATI (am comutat cele dou relaii) este diferit fa de cel din fig. I.4.17, dup cum se poate observa n figura de mai jos (fig. I.4.18).

Fig. I.4.18. . Operaia de jonciune extern a relaiilor JUDETE i LOCALITATI

Observaie: Se poate construi i jonciunea extern complet (Full Outer Join). Exemplu: Aplicnd jonciunea extern complet relaiilor LOCALITATI i JUDETE se obine relaia REZ:

simbol_judet# nume_judet MM Maramures CJ Cluj BV Brasov

simbol_judet simbol_judet LOCALITATI:

JUDETE:

simbol_judet nume_judet cod_loc nume_loc simbol_judet MM Maramures MM430 Baia

Mare MM

MM Maramures MM435 Borsa MM CJ Cluj CJ147 Cluj-

Napoca CJ

BV Brasov NULL NULL NULL

cod_loc# nume_loc simbol_judet MM430 Baia Mare MM MM435 Borsa MM CJ147 Cluj-Napoca CJ BT710 Botosani BT

cod_loc# nume_loc simbol_judet MM430 Baia Mare MM MM435 Borsa MM CJ147 Cluj-Napoca CJ BT710 Botosani BT

simbol_judet# nume_judet MM Maramures CJ Cluj BV Brasov

simbol_judet simbol_judet

LOCALITATI: JUDETE:

cod_loc Nume_loc simbol_judet simbol_judet nume_judet MM430 Baia Mare MM MM Maramures MM435 Borsa MM MM Maramures CJ147 Cluj-Napoca CJ CJ Cluj BT710 Botosani BT NULL NULL

REZ:

26

Fig. I.4.19. Operaia de jonciune extern complet a relaiilor LOCALITATI i

JUDETE Semijonciunea

Semijonciunea este jonciunea care are ca rezultat tuplurile relaiei R1, conservnd atributele relaiei R1.

Notaie: R1 R2; SEMIJOIN(R1, R2). Reprezentarea grafic:

Fig. I.4.20. Reprezentarea grafic a operaiei de semijonciune

Exemplu: Semijonciunea urmtoare realizeaz lista localitilor care au referin n relaia JUDETE. (Vezi figura I.4.21.) Observaii: 1. Aceast operaie a fost introdus de P.A. Bernstein, fiind necesar la optimizarea cererilor de date. 2. Semijonciunea produce acelai rezultat ca operaia de proiecie pe atributele din relaia R1 efectuat asupra jonciunii dintre R1 i R2 PROJECT (JOIN (R1, R2, condiia), A1, A2, A3)=SEMIJOIN (R1, R2).

cod_loc# nume_loc simbol_judet MM430 Baia Mare MM MM435 Borsa MM CJ147 Cluj-Napoca CJ BT710 Botosani BT

simbol_judet# nume_judet MM Maramures CJ Cluj BV Brasov

simbol_judet simbol_judet LOCALITATI: JUDETE:

cod_loc Nume_loc simbol_judet simbol_judet nume_judet MM430 Baia Mare MM MM Maramures MM435 Borsa MM MM Maramures CJ147 Cluj-Napoca CJ CJ Cluj BT710 Botosani BT NULL NULL NULL NULL NULL BV Brasov

R1 R2

R3

27

Fig. I.4.21. Operaia de semijonciune a relaiilor LOCALITATI i JUDETE

Intersecia

Intersecia reprezint o operaie definit pe dou relaii, R1 i R2, ambele cu aceeai schem, n urma creia se construiete o nou relaie R3, cu schema identic cu a operanzilor i format din tuplurile comune lui R1 i R2.

Notaie: R1R2 INTERSECT (R1, R2) AND (R1, R2) Reprezentarea grafic:

Fig. I.4.22. Reprezentarea grafic a operaiei de intersecie

Exemplu:

Fig. I.4.23. Intersecia relaiilor ORASE i MUNICIPII

localitate judete populatie Borsa Maramures 27 000 Brasov Brasov 350 000

I ORASE: MUNICIPII:

REZ:

localitate judete populatie Baia Mare Maramures 148 270 Brasov Brasov 350 000

localitate judete populatie Brasov Brasov 350 000

cod_loc# nume_loc simbol_judet MM430 Baia Mare MM MM435 Borsa MM CJ147 Cluj-Napoca CJ BT710 Botosani BT

simbol_judet# nume_judet MM Maramures CJ Cluj BV Brasov

simbol_judet

LOCALITATI: JUDETE:

cod_loc# nume_loc simbol_judet MM430 Baia Mare MM MM435 Borsa MM CJ147 Cluj-Napoca CJ

REZ:

R1

R3

R2

I

28

Observaie: Intersecia se poate exprima prin intermediul unor operaii de baz. De aceea intersecia este o operaie derivat.

R1R2=R1-(R1-R2) R1R2=R2-(R2-R1)

Diviziunea

Diviziunea reprezint o operaie a algebrei relaionale definit asupra unei relaii R cu schema R(A1:D1, , Ap:Dk, , Ap+1:Di, , An:Dm), n urma creia se construiete o nou relaie Q cu ajutorul unei relaii r cu schema r (Ap+1:Dl, , An:Dm), relaia Q avnd schema: Q(A1:D1, , Ap:Dk).

Tuplurile relaiei Q concatenate cu tuplurile relaiei r permit obinerea tuplurilor relaiei R.

Notaie: Rr Division (R, r).

Reprezentarea grafic:

Fig. I.4.24. Reprezentarea grafic a operaiei de diviziune

Exemplu: Se consider o list ce conine cercettorii i proiectele la care acetia iau parte, list cuprins n relaia PROGRAMARI (id_cercetator, nr_proiect) de mai jos. S se afieze acei cercettori care particip la toate proiectele.

Fig. I.4.25. Rerezentarea operaiei de diviziune pentru relaia R Observaii: 1. Operaia de diviziune este o operaie derivat deoarece se poate exprima prin intermediul operaiilor de baz: a diferenei, a produsului cartezian i a proieciei:

id_cercetator# nr_proiect# C1 P3 C2 P2 C1 P1 C3 P3 C3 P2 C3 P1

nr_proiect P1 P2 P3

R r

id_cercetator C3

R

Q

r

29

)))((()( ,...,,...,,..., 111 RrRRrR ppp AAAAAA = .

2. Toate aceste operaii trebuie s asigure proprietatea de nchidere, adic rezultatul fiecrei operaii trebuie s fie tot o relaie. Aceast proprietate permite efectuarea operaiilor imbricate: proiecia unei jonciuni dintre o relaie i restricia aplicat altei relaii, etc. Restricia i proiecia sunt operaii unare (au un singur operand, o relaie); operatiile pe mulimi, jonciunea i diviziunea sunt operaii binare (au doi operanzi, dou relaii).

30

II. Restricii de integritate ale modelului relaional

II.1. Restricii de integritate ale modelului relaional

Restriciile de integritate ale modelului relaional reprezint cerine pe care trebuie s le ndeplineasc datele din cadrul bazei de date pentru a putea fi considerate corecte i coerente n raport cu lumea real pe care o reflect. Dac o baz de date nu respect aceste cerine, ea nu poate fi utilizat cu un maxim de eficien.

Restriciile sunt de dou tipuri: 1. restricii de integritate structurale, care se definesc prin egalitatea sau inegalitatea unor valori din cadrul relaiilor:

i. restricia de unicitate a cheilor; ii. restricia entitii; iii. dependenele ntre ele;

2. restricii de integritate de comportament care in cont de semnificaia valorilor din cadrul bazei de date.

Utilizarea modelului relaional nu impune definirea i verificarea tuturor acestor tipuri de restricii de integritate. Din acest punct de vedere exist restricii de integritate minimale. Acestea sunt obligatoriu de definit i de respectat cnd se lucreaz cu modelul relaional. Dintre restriciile minimale fac parte:

i. restricia de unicitate a cheii; ii. restricia referenial; iii. restricia entitii.

Alte restriciii de integritate sunt: iv. dependenele; v. restricii de comportament.

Restricii de integritate minimale

Restriciile de integritate minimale sunt definite n raport cu noiunea de cheie a unei relaii. Cheia identific un tuplu n cadrul unei relaii fr a face apel la toate valorile din tuplu.

Cheia unei relaii reprezint ansamblul minimal de atribute prin care se poate identifica n mod unic orice tuplu al relaiei.

Cheia unei relaii poate fi: - cheie simpl, cnd cheia este construit dintr-un singur atribut; - cheie compus, cnd cheia este construit din mai multe atribut. Exemplul 1:

Fig. II.1.1 Chei simple i chei compuse n cadrul relaiilor JUDEE, respectiv STRZI

simbol_judet# nume_jud MM Maramures AB Alba

JUDETE: STRAZI: simbol_jude cod_loc# id_strad# nume_str BV BV230 120 Independenei BV BV230 078 Grii CJ CJ147 123 Cireilor

31

Observaie: Cheia relaiei JUDEE este simbol_jude deoarece fiecare jude are o codificare unic a denumirii sale, deci fiecrui jude i corspunde un singur simbol. Cheia relaiei STRZI este compus din atributele cod_loc i id_strada. Dac s-ar alege drept cheie primar doar atributul id_strada, acesta nu ar identifica n mod unic numele unei strzi dintr-o localitate, numele strzii respective putndu-se regsi i n alte localiti ale aceluiai jude sau a altui jude. La fel, dac s-ar alege drept cheie primar atributul cod_loc, acesta nu ar mai identifica n mod unic un tuplu, deoarece ntr-o localitate exist mai multe strzi. O relaie poate avea mai multe combinaii de atribute, cu proprietatea de identificare unic a tuplurilor. Se spune n acest caz c relaia posed mai muli candidai cheie (chei candidate).

Definiia 1. Se numete cheie primar, cheia aleas dintre cheile candidate care s serveasc n mod efectiv la identificarea tuplurilor.

Cheia primar nu poate fi reactualizat. Cheia primar a unei relaii nu este altceva dect atributul de identificare a unei entiti, prin urmare se reprezint fie prin subliniere, fie urmate de semnul #.

Definiia 2. Se numete cheie extern atributul/grupul de atribute dintr-o relaie R1 a crui/cror valori sunt definite pe acelai domeniu/aceleai domenii ca i cheia primar a unei alte relaii R2 i care are rolul de a modela asocierea ntre entitile reprezentate prin relaiile R1 i R2. n acest caz, R1 se numete relaie printe, iar R2 se numete relaie copil. Exemplul 1:

Fig. II.1.2 Reprezentarea legturii dintre relaiile CERERI_OFERTE i

LOCALITATI cu ajutorul cheii externe cod_loc din cadrul relaiei CERERI_OFERTE

Observaii: 1. n exemplul de mai sus, relaia CERERI_OFERTE are drept cheie primar atributul id_co, iar ca i cheie extern atributul cod_loc, iar relaia LOCALITATI are drept cheie primar cheia cod_loc. Relaia CERERI_OFERTE este relaia copil, iar LOCALITATI este relaia printe. 2. ntre cele dou relaii (entiti) avem urmtoarea asociere:

Fig. II.1.3 Asocierea dintre entitile CERERI_OFERTE i LOCALITATI

id_co# tipul cnp cod_loc 12 oferta 2660805270023 BV230 13 oferta 1701205230023 CJ147

cod_loc# simbol_judet nume_loc BV230 BV Brasov CJ147 CJ Cluj MM430 MM Baia

Mare

CERERI_OFERTE: LOCALITATI:

CERERI_OFERTE LOCALITATI (1,1) (0,n)

figureaz n

32

Exemplul 2:

Fig. II.1.4 Reprezentarea legturii ntre relaiile JUDETE i LOCALITATI cu

ajutorul cheilor externe simbol_judet i cod_localitate Restricia de unicitate a cheii

Restricia de unicitate a cheii impune ca ntr-o relaie s nu existe dou tupluri identice (tupluri care s nu conin aceleai valori pentru toate atributele). Altfel spus, restricia de unicitate a cheii impune ca ntr-o relaie s nu existe dou tupluri cu o aceeai valoare pentru atributul cheie.

Exemplele 1 i 2 respect restricia de unicitate a cheii.

Restricia referenial Restricia referenial impune ca ntr-o relaie R1 care refer o relaie R2,

valorile cheii externe s figureze printre valorile cheii primare din R2 sau s fie valori null (nedefinite). R1 i R2 nu trebuie s fie neaprat distincte.

Exemplele 1 i 2 prezint un mecanism de legare a relaiilor i respect restricia referenial a cheii.

Restricia entitii

Restricia entitii impune ca ntr-o relaie atributele cheii primare s fie nenule. (Atributele cheie s nu conin valori nule). Dac exist valori null, cheia i poate pierde rolul de identificator de tuplu. Astfel, la ncrcarea unui tuplu, valoarea cheii trebuie s fie cunoscut, pentru a se putea verifica faptul c aceast valoare nu exist deja ncrcat.

Exemplele 1 i 2 respect restricia entitii. Relaia REZ din figura I.4.17 ncalc restricia entitii deoarece exist chei primare ce conin valori nule, dup cum este cazul judeului Botoani.

Alte restricii de integritate

n categoria restricii de integritate intr urmtoarele tipuri de restricii: a) restricii referitoare la dependena datelor:

dependen funcional; dependen multivaloare; dependen jonciune

simbol_judet# nume_jud BV Braov CJ Cluj

JUDETE:

simbol_jude cod_loc# nume_loc BV BV230 Braov CJ CJ147 Cluj

LOCALITATI:

simbol_judet cod_loc# id_strada# nume_str BV BV230 001 Independenei BV BV230 002 Grii CJ CJ147 003 Cireilor

STRZI:

33

b) restricii de comportament: restricii de domeniu; restricii temporale.

Restriciile referitoare la dependena datelor, reprezint modul n care datele

depind unele de altele.

Dependenele funcionale

Dependenele funcionale reprezint dependena ntre date prin care se poate identifica un atribut/grup de atribute prin intermediul altui atribut/grup de atribute.

Dac X i Y sunt dou subansamble de atribute ale atributelor relaiei R, spunem c ntre X i Y exist o dependen funcional, notat YX , dac i numai dac: (i) fiecare valoare a lui X poate fi asociat unei singure valori din Y, i (ii) dou valori distincte ale lui X nu pot fi asociate dect aceleiai valori ale lui Y.

X se numete determinantul (sursa) dependenei, iar Y se numete determinatul (destinaia) dependenei. Exemple: Urmtoarele atribute se afl n dependen funcional: cod_potallocalitate, deoarece unui cod potal i corespunde o singur localitate (sensul dependenei este foarte important, deoarece, viceversa ar nsemna: o localitate are un singur cod_potal); nr_facturdata_factur, deoarece cunoaterea numrului facturii determin cu exactitate data facturii.

Dou atribute nu sunt n dependen funcional, notat X Y, atunci cnd cunoaterea unei valori a primului atribut fie nu permite cunoaterea nici uneia dintre valorile celui de al doilea atribut, fie permite cunoaterea mai multor valori ale celui de al doilea atribut. Exemplu: Urmtorul atribut nu se afl n dependen funcional: cnpnr_factur, deoarece pentru o persoan se pot ntocmi mai multe facturi (aferente fiecrei vnzri).

n cadrul modelului relaional, o dependen funcional este reprezentat printr-o sgeat ce pornete din surs i se termin n destinaie.

Fig. II.1.5 Reprezentarea dependenei funcionale ntre atributele nr_factura i

data_facturii Dependenele multivaloare

Dependenele multivaloare reprezint dependena n care un atribut/ grup de atribute poate reprezenta/ identifica mai multe valori pentru o singur valoare a unui alt atribut/ grup de atribute.

Dac X,Y i Z sunt trei subansambluri de atribute ale atributelor relaiei R, spunem c ntre X i Y exist o dependen multivaloare, notat YX sau

ZYX | , dac i numai dac: (i) la fiecare valoare a lui X poate fi asociat una sau mai multe valori ale lui Y, i

nr_factura# data_facturii cnp 1 2006-07-05 1701205230023 2 2006-06-28 2581023457723

FACTURI:

34

(ii) aceast asociere nu depinde de apariiile lui Z. Altfel spus, dac YX i (x,y,z), (x,y,z) sunt dou tupluri din R, atunci i

(x,y,z), (x,y,z) sunt tupluri din R. Exemplu: n relaia OFERTE (alctuit din atributele: id_tip_oferte, cnp i simbol_judet) valorile atributului id_tip_oferte au urmtoarea semnificaie: 01 desemneaz imobil de tip apartament, iar 02, imobil de tip cas. Astfel, urmtoarea relaie conine dependene multivaloare:

deoarece

Fig. II.1.6 Relaia OFERTE n care exist dependen multivaloare

Dependenele de jonciune

Dac X1, X2, ...,Xm sunt m subansambluri de atribute din relaia R, spunem c exist o dependen de jonciune de ordinul m ntre X1, X2, ...,Xm, notat X1/ X2/ .../Xm, dac i numai dac R reprezint jonciunea proieciilor sale pe X1, X2, ...,Xm.

II.2. Prelucrarea/evaluarea i optimizarea cerinelor Regulile lui Codd

Prin sistem de gestiune a bazelor de date relaionale (SGBDR) se nelege un SGBD care utilizeaz drept mod de organizare a datelor modelul relaional.

Definirea unui SGBDR impune o detaliere a caracteristicilor pe care trebuie s le prezinte un SGBD pentru a putea fi considerat relaional. n acest sens, Codd a formulat (n 1985) 13 reguli, care exprim cerinele pe care trebuie s le satisfac un SGBD. Aceste reguli sunt deosebit de utile n evaluarea unui SGBDR.

R0: Regula privind gestionarea datelor la nivel de relaie Sistemul trebuie s gestioneze BD numai prin mecanisme relaionale. R1: Regula privind reprezentarea logic a datelor ntr-o baz de date relaionat, informaia este reprezentat la nivel logic sub forma unor tabele (relaii), ceea ce nseamn c toate datele trebuie s fie memorate i prelucrate n acelai mod.

id_tip_oferte cnp simbol_judet 01 1701205230023 MM 01 2581023457723 MM 02 1701205230023 SM 01 2581023457723 SM 01 2581023457723 SM 02 2581023457723 CJ

OFERTE:

01 1701205230023 MM

01 2581023457723 SM

01 1701205230023 SM

01 2581023457723 MM

35

R2: Regula privind reprezentarea logic a datelor Orice data din baza de date relaionat trebuie s poat fi accesat prin specificarea numelui relaiei, valorii cheii primare i numele atributului.

R3: Regula privind valorile nule Sistemul trebuie s permit declararea i manipularea sistematic a valorilor NULL (care semnific lipsa unor date), cu observaia c valorile NULL difer de irurile de caractere spaiu, irurile vide de caractere.

R4: Regula privind metadatele Utilizatorii autorizai trebuie s poat aplica asupra descrierii bazei de date aceleai operaii ca i asupra datelor obinuite.

R5: Regula privind facilitile limbajelor de utilizare: Trebuie s existe cel puin un limbaj care s exprime oricare din urmtoarele operaii: definirea relaiilor, sa vizualizeze, s regseasc informaia, s poat reactualiza informaia, s verifice i s corecteze datele de intrare, etc. n general, toate implementrile SQL respect aceast regul.

R6: Regula privind actualizarea tabelelor virtuale: Toate tabelele/relaiile virtuale trebuie s poat fi actualizate. Exemplu: Fie tabela de baz PROD, cu urmtoarea schem PROD(Denp:D1, Cant:D2, Pret:D3), cu ajutorul tabelei PROD este definit o tabel virtual DISP, cu schema: DISP (Denp:D1, Cant:D2, Pret:D3, Val:D4). Valorile atributului Val se calculeaz astfel:

Val=Cant*Pret. Presupunem c se dorete schimbarea preului unitar la un anumit produs, aceast schimbare trebuie efectuat n tabela de baz PROD, atributul Pret din tabela virtual DISP, fiind actualizabil, ntruct actualizarea se poate propaga spre tabela de baz. Presupunem c se dorete schimbarea valorii Val la un anumit produs: - modificarea de la tabela virtual spre tabela de baz nu mai este posibil, atributul Val nu este actualizabil, deoarece schimbarea valorii Val se poate datora schimbrii cantitii Cant i/sau a preului unitar Pret. - astfel trebuie s existe un mecanism prin care s se poat determina dac anumite vizualizri pot fi modificate sau nu. Majoritatea implementrilor SQL ndeplinesc aceast cerin.

R7: Regula privind inserrile, modificrile i tergerile din baza de date Un SGBDR nu trebuie s oblige utilizatorul s caute ntr-o relaie, tuplu cu tuplu, pentru a regsi informaia dorit.

R8: Regula privind independena fizic a datelor O schimbare a structurii fizice a datelor nu trebuie s blocheze funcionarea programelor de aplicaii, ceea ce nseamn c ntr-un SGBDR trebuie s se separe aspectul fizic al datelor (stocare sau acces la date) de aspectul logic al datelor.

R9: Regula privind independena logic a datelor. O schimbare a relaiilor bazei de date nu trebuie s afecteze programele de aplicaie.

R10: Regula privind restriciile de integritate Restriciile de integritate trebuie s fie definite ntr-un limbaj relaional, nu n programul de aplicaie.

R11: Regula privind distribuirea geografic a datelor Distribuirea datelor pe mai multe calculatoare dintr-o reea de comunicaii de date, nu trebuie s afecteze programele de aplicaie. R12: Regula privind prelucrarea datelor la nivelul de baz

36

Dac sistemul posed un limbaj de baz orientat pe prelucrarea de tupluri i nu pe prelucrarea relaiilor, acest limbaj nu trebuie s fie utilizat pentru a evita restriciile de integritate.

Clasificarea regulilor lui Codd

n funcie de tipul de cerine pe care le exprim, regulile sunt grupate n 5

categorii: 1. Reguli de baz: R0 i R12; 2. Reguli structurale: R1 i R6; 3. Reguli privind integritatea datelor: R3 i R10; 4. Reguli privind manipularea datelor: R2, R4, R5, R7; 5. Reguli privind independena datelor: R8, R9, R11.

Evaluarea/prelucrarea cerinelor i optimizarea n SGBDR interfaa cu utilizatorul este de tip neprocedural. Utilizatorul definete datele pe care dorete s le vizualizeze fr a da algoritmi de acces. Sistemul trebuie s converteasc cererea utilizatorului ntr-o cerere optimal. Evaluarea unei cereri se efectueaz n trei etape: 1. Analiza cererii ce const n studierea sintactic i semantic a cererii pentru a verifica corectitudinea sa i a simplifica criteriului de cutare. 2. Ordonanarea presupune descompunerea cererii ntr-o mulime de operaii elementare i determinarea ordinii optimale a acestor aplicaii. 3. Execuia n paralel i/sau secvenial a operaiilor elementare pentru a obine rezultatul cererii

Presupunem c utilizatorul transmite sistemului de gestiune o cerere exprimat prin ordine SQL. Pentru a rspunde cererii, SGBD-ul trebuie s neleag cererea utilizatorului. Cererea trebuie s fie corect sintactic, datele trebuie s fie disponibile utilizatorului i trebuie localizate analiznd diferite drumuri de acces la ele.

Ideea general este concretizat n schema de mai jos:

adic optimizarea cererilor de date se realizeaz prin parcurgerea urmtoarelor etape:

1. exprimarea cererilor sub forma unei expresii algebrice relaionale; 2. aplicarea unor transformri algebrice asupra expresiilor obinute n etapa

precedent, n scopul executrii mai eficiente a lor; 3. planul de execuie; 4. optimizarea. Un plan de execuie implic o secven de pai pentru evaluarea cererii (n mod

obinuit, fiecare pas din planul de execuie corespunde unei operaii relaionale) precum i metoda care va fi folosit pentru evaluarea operaiei. De obicei, pentru o operaie relaional dat, exist mai multe metode ce pot fi folosite pentru evaluarea acesteia.

Dou planuri de execuie diferite care au ntotdeauna acelai rezultat se numesc echivalente. Planuri de execuie echivalente pot avea diferite costuri. Scopul optimizrii cererilor este de a gsi, printre diversele planuri de execuie echivalente, pe acela de cost minim. ntr-un sistem centralizat, costul evalurii unei cereri este suma a dou

cerere arbore algebric (nu este unic) plan de executie optimizare

37

componente, costul I/O (transferuri de date) i costul CPU (verificare de condiii, operaii jonciuni etc.).

Strategiile de optimizare pot fi de dou tipuri: 1. Strategii generale de optimizare (independente de modul de memorare al

datelor); 2. Strategii specifice anumitor SGBDR (in cont de modul de memorare al

datelor). Implementarea strategiilor generale de optimizare este permis datorit

proprietilor operaiilor din algebra relaional. Aceste proprieti sunt: Comutativitatea operaiilor de join i produs cartezian

E1E2 E2E1

E1 E2 E2 E1 Asociativitatea operaiilor de join i produs cartezian

(E1 E2) E3 E1 (E2 E3)

(E1 E2 ) E3 E1 (E2E3 ) Compunerea proieciilor

A1,...,Am (B1,...,Bn (R)) = A1,...,Am (R), unde A1,...,Am trebuie s aparin de B1,...,Bn. Compunerea seleciilor

)())(( 2121 RR FFFF . Deoarece 1221 FFFF = , seleciile se pot comuta

))(())(( 1221 RR FFFF . Comutarea seleciei i proieciei - Dac condiia F implic numai atributele A1,...,An, atunci

))(())(( ,...,1,...,1 RR AnAFFAnA = .

- Dac condiia F implic i atributele B1,...,Bm, care nu aparine de A1,...,An atunci

)))((())(( ,...,1,,...,1,...,1,...,1 RR BmBAnAFAnAFAnA = .

Comutarea seleciei cu produsul cartezian - Dac toate atributele menionate n F sunt atribute ale lui E1, atunci

2121 )()( EEEE FF . -Dac, n plus, F este de forma 21 FFF = i F1 implic numai atributele din E1, iar F2 implic numai atributele din E2, atunci

)()()( 221121 EEEE FFF . Daca F1 implic numai atribute din E1, dar F2 implic atribute att din E1 ct i din E2, atunci

))(()( 211221 EEEE FFF .

Comutarea seleciei cu reuniunea )()()( 2121 EEEE FFF .

Comutarea seleciei cu diferena )()()( 2121 EEEE FFF .

Comutarea proieciei cu produsul cartezian Dac A1,A2,...,An sunt atribute din cadrul a dou expresii E1 i E2, formate din atributele B1,...,Bm ale lui E1 i din atributele C1,...,Ck ale lui E2, atunci

38

)()()( 2,...,11,...,121,...,1 EEEE CkCBmBAnA .

Comutarea proieciei cu reuniunea )()()( 2,...,11,...,121,...,1 EEEE AnAAnAAnA .

Aceste proprieti permit definirea unor strategii generale de optimizare a cererilor de date i anume:

Regula de optimizare 1. Seleciile se execut ct mai devreme posibil. Motivaia acestei reguli este c seleciile reduc substanial dimensiunea relaiilor. Regula de transformare 4 poate fi folosit pentru a separa dou sau mai multe selecii n selecii individuale care pot fi distribuite jonciunii (join-ului) sau produsului cartezian folosind comutarea seleciei cu jonciunea (join-ul).

Regula de optimizare 2. Produsele carteziene se nlocuiesc cu joncinuea, ori de cte ori este posibil. Un produs cartezian ntre dou relaii este de obicei mult mai scump (ca i cost) dect o jonciune ntre cele dou relaii, deoarece primul genereaz concatenarea tuplurilor n mod exhaustiv i poate genera un rezultat foarte mare. Aceast transformare se poate realiza folosind legtura dintre produs cartezian, jonciune i selecie.

Regula de optimizare 3. Dac sunt mai multe jonciuni atunci cea care se execut prima este cea mai restrictiv. O jonciune este mai restrictiv dect alta dac produce o relaie mai mic. Se poate determina care jonciune este mai restrictiv pe baza factorului de selectivitate sau cu ajutorul informaiilor statistice. Algebric, acest lucru se poate realiza folosind regula de transformare 2.

Regula de optimizare 4. Proieciile se execut la nceput pentru a ndeprta atributele nefolositoare. Dac un atribut al unei relaii nu este folosit n operaiile ulterioare atunci trebuie ndeprtat. n felul acesta se va folosi o relaie mai mic n operaiile ulterioare. Aceasta se poate realiza folosind comutarea proieciei cu jonciunea.

II.3. Tehnica normalizrii relaiilor II.3.1 Preliminarii

La proiectarea structurii unei baze de date relaionale trebuie stabilite (dup cum s-a vzut n cursurile anterioare) n primul rnd tabelele n care vor fi memorate datele i asocierile dintre tabele. Acestea sunt stabilite ntr-o form iniial, dup care, prin rafinare succesiv se ajunge la forma definitiv. Acestei structuri iniiale i sunt aplicate un set de reguli care reprezint paii de obinere a unei baze de date normalizate. Dac o baz de date nu este normalizat ea nu poate fi utilizat cu un maxim de eficien. Algoritmul de normalizare a bazelor de date relaionale precum i paii acestuia au fost descrii de ctre E. F. Codd n 1972.

Normalizarea este procesul reversibil de transformare a unei relaii n relaii cu o structur mai simpl. (Procesul este reversibil n sensul c nici o informaie nu este pierdut n timpul transformrii). Scopul normalizrii este de a suprima redundanele logice, de a evita anomaliile la reactualizare i rezolvarea problemei reconexiunii. Exemplu: Pentru a evidenia cteva exemple de redundane i anomalii, se va considera cazul relaiei iniiale OFERTANTI. Pentru a nu ncrca relaia, se vor considera valori ale atributelor prescurtate.

39

Fig. II.3.1 Relaia OFERTANTI

- Redundana logic: Tripletul (N1, Str. Victoriei, nr.22/12, Baia Mare, Maramures, Nr1) apare de dou ori. - Anomalii la inserare: Dac o persoan ofer spre vnzare mai multe imobile, pentru nregistrarea ofertei trebuie rescris codul numeric personal nc o dat, deci cheia devine duplicat. - Anomalii de tergere: tergerea unei persoane din baza de date atrage dup sine pierderea informaiilor despre oferta respectiv. - Anomalii la modificare: Dac se modific numele strzii Victoriei din localitatea Baia Mare n strada Independenei, modificarea trebuie efectuat pentru fiecare ofert din Baia Mare amplasat pe strada Victoriei. Dac ar exista 25 de oferte n aceast localitate pe strada Victoriei, costul modificrii ar fi mare pentru a modifica toate nregistrrile. Aceast redundan este eliminat dac atributul adresa este mprit n alte trei atribute: simbol_judet, cod_loc, id_strada. Valorile acestea vor fi codul judeului, localitii, respectiv a strzii preluate din relaiile deja existente JUDETE, LOCALITATI, respectiv STRAZI. n acest caz, modificarea se face doar o singur dat, n tabela STRAZI.

Normalizarea

Codd a definit iniial 3 forme normale, notate prin FN1, FN2 i FN3. Deoarece ntr-o prim formulare, definiia FN3 ridic ceva probleme, Codd i Boyce au elaborat o nou variant, cunoscut sub numele de Boyce-Codd Normal Form (BCNF). Astfel BCNF este reprezentat separat n majoritatea lucrrilor. R. Fagin a tratat cazul FN4 i FN5.

O relaie este ntr-o form normal dac satisface o mulime de constrngeri specificat n figura II.3.2. De exemplu, se spune c o relaie se afl n a doua form normal FN2 dac i numai dac se afl n FN1.

cnp# numele adresa_ client nr_ telefon

oferta adresa_ imobil

Cnp1 N1 Str. Victoriei, nr.22/12, Baia Mare, Maramures

Nr1 casa A_imobil1

Cnp1 N1 Str. Victoriei, nr22/12, Baia Mare, Maramures

Nr1 hala A_imobil2

Cnp2 N2 Str. Viilor, nr.55/4, Oradea, Bihor

Nr2 casa A_imobil3

Cnp3 N3 Str. Grii, nr. 14, Bucuresti

Nr3 teren A_imobil4

OFERTANTI:

40

Fig. II.3.2 Formele normale ale relaiilor dintr-o BDR

Normalizarea bazei de date relaionale poate fi imaginat ca un proces prin care pornindu-se de la relaia iniial/universal R se realizeaz descompunerea succesiv a acesteia n subrelaii, aplicnd operatorul de proiecie. Relaia R poate fi ulterior reconstruit din cele n relaii obinute n urma normalizrii, prin operaii de jonciune. II.3.2 Prima form normal (FN1) FN1 este strns legat de noiunea de atomicitate a atributelor unei relaii. Astfel, aducerea unei relaii n FN1 presupune introducerea noiunilor de:

2. atribut simplu; 3. atribut compus; 4. grupuri repetitive de atribute.

Atributul simplu- Atribut compus

Prin atribut simplu (atribut atomic) se nelege un atribut care nu mai poate fi descompus n alte atribute, n caz contrar, atributul este compus (atribut neatomic). Exemplu: Urmtoarele exemple de atribute pot fi considerate simple sau compuse n funcie de circumstane i de obiectivele bazei de date. 1. Data calendaristic este un atribut n care apar cmpurile: zi, lun, an; 2. Adresa este un atribut n care apar cmpurile: strada, nr, bloc, scara, etaj, apartament, localitate, jude; 3. Data operaiunii bancare este un atribut n care apar cmpurile data, ora; 4. Buletin/carte identitate este un atribut n care apar cmpurile: seria, nr. Aceste atribute pot fi atomice sau neatomice. Astfel adresa clienilor ageniei imobiliare intereseaz la nivel global, pe cnd pentru adresa ofertei sau a cererii de imobile este vital prelucrarea separat a fiecrui cmp considerat. Analog, atributul nume reprezent un atribut simplu al acestei baze de date, deoarece numele clientului intereseaz la nivel global. Grupuri repetitive de atribute

Un grup repetitiv este un atribut (grup de atribute) dintr-o relaie care apare cu valori multiple pentru o singur apariie a cheii primare a relaiei nenormalizate.

Relaia universal

FN1

FN2

FN3

BCFN FN4

FN5

41

Exemplu: Fie relaia nenormalizat (primar) FACTURI. Dorim s stabilim o structur de tabele care s permit stocarea informaiilor coninute n document (factur) i obinerea unor situaii sintetice privind evidena sumelor facturate pe produse, pe clinei, pe anumite perioade de timp.

Fig. II.3.3 Relaia FACTURI nenormalizat

n cazul n care o factur conine mai multe produse, relaia de mai sus va avea grupurile repetitive: cod_produs, denumire_produs, unitate_de_masa, cantitate, pret_unitar, valoare, valoare_tva. Aducerea unei relaii universale la FN1

FN1 este tratat n general cu superficialitate, deoarece principala cerin atomicitatea valorilor este uor de ndeplinit. Dintre toate formele normale, doar FN1 are caracter de obligativitate. Se spune c o baz de date este normalizat daca toate relaiile se afl mcar n FN1.

Definiie: O relaie este n FN1 dac domeniile pe care sunt definite atributele relaiei sunt constituite numai din valori atomice. Un tuplu nu trebuie s conin atribute sau grupuri de atribute repetitive. Aducerea relaiilor n FN1 presupune eliminarea atributelor compuse i a celor repetitive. Se cunosc trei soluii pentru eliminarea grupurilor repetitive: - eliminarea grupurilor repetitive pe orizontal (n relaia R iniial, n locul atributelor compuse se trec componentele acestora, ca atribute simple); - eliminarea grupurilor repetitive prin adugarea de tupluri; - eliminarea grupurilor repetitive prin construirea de noi relaii

Primele dou metode genereaz relaii stufoase prin duplicarea forat a unor atribute, respectiv tupluri, crendu-se astfel redundane masive cu multiple anomalii de actualizare.

FACTURI nr_factura# data_factura nume_client adresa_client banca_client nr_cont_client delegat cod_produs denumire_produs unitate_de_masura cantiate pret_unitar valoare valoare_tva toal_valoare_factura toal_valoare_tva

42

Metoda a treia presupune eliminarea grupurilor repetitive prin construirea de noi relaii, ceea ce genereaz o structur ce ofer cel mai mic volum de redundan.

Etapele de aducere a unei relaii n FN1 sunt: I. se construiete cte o relaie pentru fiecare grup repetitiv; II. n schema fiecrei noi relaii obinute la pasul 1 se introduce i cheia

primar a relaiei R nenormalizate; III. cheia primar a fiecrei noi relaii va fi compus din atributele chei ale

relaiei R, plus unul sau mai multe atribute proprii. Exemplu: Deoarece o factur poate avea unul sau mai multe produse nscrise pe aceasta, informaiile legate de produse vor fi separate ntr-o alt tabel. Aplicnd etapele de aducere la FN1, se obin dou relaii:

Fig. II.3.4 Relaia FACTURI adus n forma normal FN1

Observaii: 1. Cmpul adresa_client curpinde informaii despre judeul, localitatea, strada i numrul domicililului clientului. Dac se consider c este de interes o eviden a sumelor factorizate pe judee sau localiti, se vor pune n locul cmpului adresa_client trei cmpuri distincte: judet_client, localitate_client, adresa_client, uurnd n acest fel interogrile. 2. ntre tabela FACTURI i tabela LINII_FACTURI exist o relaie de unu la muli, adic unui numr unic de factur i pot corespunde unul sau mai multe produse care sunt memorate ca nregistrri n tabele LINII_FACTURI. Cheia primar n aceast tabel este o cheie compus, format din dou cmpuri: nr_factura i cod_produs.

ns eliminarea grupurilor repetitive, adic aducerea unei relaii la FN1, nu rezolv complet problema normalizrii.

II.3.3 A doua form normal (FN2)

FN2 este strns legat de noiunea de dependen funcional. Noiunea de

dependen funcional a fost prezentat n capitolul Restricii de integritate ale modelului relaional.

O relaie se afl n a doua form normal FN2 dac: 1. se afl n forma normal FN1 i 2. fiecare atribut care nu este cheie este dependent de ntreaga cheie primar.

Etapele de aducere a unei relaii de la FN1 la FN2 sunt: I. Se identific posibila cheie primar a relaiei aflate n FN1;

FACTURI nr_factura# data_factura nume_client adresa_client banca_client nr_cont_client delegat toal_valoare_factura toal_valoare_tva

LINII_FACTURI nr_factura# cod_produs# denumire_produs unitate_de_masura cantiate pret_unitar valoare valoare_tva

43

II. Se identific toate dependenele dintre atributele relaiei, cu excepia acelora n care sursa este un atribut component al cheii primare;

III. Se identific toate dependenele care au ca surs un atribut sau subansamblu de atribute din cheia primar;

IV. Pentru fiecare atribut (sau subansamblu) al cheii de la pasul III se creeaz o relaie care va avea cheia primar atributul (subansamblul) respectiv, iar celelalte atribute vor fi cele care apar ca destinaie n dependenele de la etapa III.

Exemplu: Relaia care conine date redundante (de exemplu, modificarea denumirii unui produs atrage dup sine modificarea n fiecare tuplu n care apare acest produs) este relaia LINII_FACTURI. Se observ ca nu exist nici o dependen funcional ntre atributele necomponente ale cheii. n schimb, toate atributele care nu intr n alctuirea cheii compuse sunt dependente de aceasta, iar DF dintre atributul component al cheii primare sunt: cod_produs --> denumire_produs, cod_produs --> unitate_de_masura. Ca urmare se formeaz nc dou relaii.

Fig. II.3.5 Relaia FACTURI n a doua forma normal FN2

Chiar dac au fost eliminate o parte din redundane, mai rmn i alte redundane ce se vor elimina aplicnd alte forme normale.

II.3.4 A treia form normal (FN3)

O relaie este n forma normal trei (FN3) dac se gsete n FN2 i dac fiecare atribut care nu particip la o cheie depinde direct doar de cheia primar. A treia regul de normalizare cere ca toate cmpurile din tabele s fie independente ntre ele.

Etapele de aducere a unei relaii de la FN2 la FN3 sunt: I. Se identific toate atributele ce nu fac parte din cheia primara i sunt surse ale

unor dependene funcionale; II. Pentru aceste atribute, se construiete cte o relaie n care cheia primar va

fi atributul respectiv, iar celelalte atribute, destinaiile din DF considerate; III. Din relaia de la care s-a pornit se elimin atributele destinaie din DF

identificat la pasul I, pstrndu-se atributele surse. Exemplu: n relaia FACTURI se observ c atributul nume_client determin n mod unic atributele adresa_client, banca_client i nr_cont_client. Deci pentru atributul

LINII_FACTURI nr_factura# cod_produs# cantiate pret_unitar valoare valoare_tva

PRODUSE cod_produs# denumire_produs unitate_de_masura

FACTURI nr_factura# data_factura nume_client adresa_client banca_client nr_cont_client delegat toal_valoare_factura toal_valoare_tva

44

nume_client se construiete o relaie CLIENTI n care cheia primar va fi acest atribut, iar celelalte atribute vor fi adresa_client, banca_client i nr_cont_client. Cmpurile valoare i valoare_tva depind de cmpurile cantitate, pret_unitar, i de un procent fix de TVA. Fiind cmpuri ce se pot calcula n orice moment ele vor fi eliminate din tabel LINII FACTURI deoarece constituie informaie memorat redundant.

Fig. II.3.6 Relaia FACTURI n a treia forma normal FN3

Observaii: 1. Aceast form normal mai poate suferi o serie de rafinri pentru a putea obine o structur performant de tabele ale bazei de date. De exemplu se observ c nume_client este un cmp n care este nscris un text destul de lung format dintr-o succesiune de litere, semne speciale (punct, virgul, cratim), spaii, numere. Ordonarea i regsirea informaiilor dup astfel de cmpuri este lent i mai greoaie dect dup cmpuri numerice. Din acest motiv se poate introduce un nou atribut cod_client care s fie numeric i care s fie cheia primar de identificare pentru fiecare client. 2. Alt observaie care poate fi fcut n legtur cu tabelele aflate n cea de a treia form normal este aceea c total_valoare_factura este un cmp care ar trebui s conin informaii sintetice obinute prin nsumarea valorii tuturor ofertelor aflate pe o factur. Este de preferat ca astfel de cmpuri s fie calculate n rapoarte sau interogri i s nu fie memorate n tabelele bazei de date.

Verificarea aplicrii corecte a procesului de normalizare se realizeaz astfel

nct uniunea acestora s produc relaia iniial, cu alte cuvinte, descompunerea este fr pierderi.

Celelalte forme normale se ntlnesc mai rar n practic. Aceste forme nu sunt folosite, n general, pentru c beneficiile de eficien pe care le aduc nu compenseaz costul i munca de care este nevoie pentru a le realiza.

LINII_FACTURI nr_factura# cod_produs# cantiate pret_unitar

PRODUSE cod_produs# denumire_produs unitate_de_masura

FACTURI nr_factura# cod_client data_factura delegat

CLIENTI cod_client# nume_client adresa_client banca_client nr_cont_client

LINII_FACTURI nr_factura# cod_produs# cantiate pret_unitar

PRODUSE cod_produs# denumire_produs unitate_de_masura

FACTURI nr_factura# data_factura nume_client delegat toal_valoare_factura toal_valoare_tva

CLIENTI nume_client# adresa_client banca_client nr_cont_client

45

III. SQL

III.1. Limbajul SQL III.1.1 Noiuni introductive

Limbajul SQL (Structured Query Language) este limbajul utilizat de majoritatea sistemelor de baze de date relaionale (SGBDR) pentru definirea i manipularea datelor.

Din punct de vedere istoric ar trebui menionat faptul c limbajul SQL a fost dezvoltat ntr-un prototip de sistem de gestiune a bazelor de date relaionale la IBM, n 1970. n 1979 corporaia Oracle a introdus prima implementare a limbajului SQL n varianta comercial. n 1987 Institutul Naional de Standarde (ANSI) a elaborat standardul limbajului SQL. Ulterior au avut loc mai multe revizii ale acestui standard.

Majoritatea limbajelor posed un set de instruciuni comun unanim acceptat de toate marile companii productoare de soft, precum MICROSOFT sau ORACLE.

Termenii utilizai de limbajul SQL sunt - tabel (Table) utilizat pentru a desemna o relaie; - linie (row) utilizat pentru a desemna un tuplu; - coloan (column) utilizat pentru a desemna un atribut.

Componentele pe care le cuprinde limbajul SQL sunt urmtoarele: 1. componenta de descriere a datelor relaionale (limbajul de descriere a datelor - LDD), 2. componenta de manipulare a datelor relaionale (limbajul de manipulare a datelor - LMD), ambele fiind absolut necesare n gestiunea BD.

Pe lng aceste componente principale, standardul SQL2 mai prevede i alte componente ale limbajului: 3. controlul tranzaciilor; 4. controlul securitii i refacerea datelor.

Controlul tranzaciilor conine comenzi pentru specificarea tranzaciilor. Unele implementri adaug comenzilor prevzute n standard i alte comenzi suplimentare de control al concurenei i refacerea datelor. Controlul securitii i refacerea datelor conine comenzi de administrare a bazei de date pentru definirea utilizatorilor i a drepturilor acestora de acces la tabele. Aceast component este dependent de SGBD, iar pentru sisteme performante, administratorul BD este obiectul activitii unei categorii speciale de utilizatori ai BD administratori ai BD. III.1.2 Structura lexical a limbajului SQL

Elementele unei instruciuni (statement) sunt:

46

- cuvintele cheie (key words), dintre care fac parte comenzile (SELECT, UPDATE, INSERT etc), operatorii (AND, OR, NOT, LIKE), clauzele (WHERE, SET, VALUES etc);

- identificatorii (identifier) sunt elementele care denumesc tabela, atributul sau alt obiect BD; SQL face diferena ntre literele mari i mici, deci este case-sensitive; identificatorul care conine ghilimele se numete identificator delimitat;

- constantele (literal) reprezint iruri de caractere ( ), numere ntregi, numere reale (ex. 3.5; 4. ; .001; 5e2), constanta NULL care simbolizeaz lipsa de informare, constante de tip logic (1 pentru TRUE i 0 pentru FALSE);

- caracterele speciale, cum ar fi ; care semnific terminarea comenzilor, . care semnific virgula zecimal, sau * care simbolizeaz operatorul de nmulire.

III.1.3 Operatori SQL SQL are urmtorii operatori: operatori aritmetici binari: + (sum), - (diferen), * (produs), / (mprire), % (modulo), & (AND orientat pe bii), | (OR orientat pe bii), # (XOR orientat pe bii), > (deplasare la dreapta).

operatori binari de comparaie: < (mai mic), > (mai mare), = (mai mare sau egal), = (egal), (diferit) sau != (diferit).

operatori de comparaie: x BETWEEN min AND max verific dac minxmax), x IN (v1,...,vn) compar x cu o list de valori, x IS NULL, x IS NOT NULL, x LIKE model_ir.

operatori logici

Operatorii logici sunt legai prin cuvintele cheie AND, OR, NOT i ei returneaz una dintre valorile logice TRUE, FALSE sau NULL.

operatori relaionali: UNION (reuniune), INTERSECT (intersecie), MINUS (diferen).

III.1.4 Funcii definite n SQL

Funcii agregat

Funciile agregat calculeaz un rezultat din mai multe tupluri ale unui

tabel (funcii de totalizare): COUNT - furnizeaz numrul de tupluri ale unui rezultat; SUM - execut suma tuturor valorilor dintr-un cmp;

47

MAX - returneaz valoarea cea mai mare dintr-un cmp; MIN - returneaz valoarea cea mai mic dintr-un cmp; AVG - calculeaz media valorilor dintr-un cmp.

Aceste funcii vor fi folosite n instruciunea SELECT.

Funcii scalare

Funciile scalare primesc unul sau mai multe argumente i returneaz valoarea calculat sau NULL n caz de eroare. Argumentele funciilor pot fi constante sau valori ale atributelor specificate prin numele atributelor corespunztoare. Dintre funciile scalare amintim: funcii numerice

- de calcul trigonometric: sin, cos, tan, cot etc. - de calcul al logaritmului: ln, log, lg - de calcul al puterilor: pow - valoarea absolut: abs - de rotunjire: floor, ceil etc.

funcii pentru manipularea irurilor de caractere funcii pentru data calendaristic funcii de conversie III.1.5 Tipuri de date

n limbajul SQL sunt definite mai