Oracle Clasa 12

8/8/2019 Oracle Clasa 12

1/103

1

BAZE DE DATE

CAPITOLUL I. PROIECTARE (DESIGN) DE DATE (9 sapt.)

CURS 1. Preliminarii

Bazele de date reprezint un instrument indispensabil pentru sistemeleinformatice. Modelarea bazelor de date constitue un subiect vast care nu poate fitratat complet ntr-un singur curs. Baza de date reprezint o modalitate de stocare peun suport extern a unei mulimide date care modeleaz un proces (sistem) din lumeareal, cu posibilitatea regsirii acesteia. De obicei o baz de date este memorat ntr-unul sau mai multe fiiere. Baza de date nsi poate fi privit ca un fel de cutie deumplere electronic - adic un container pentru o colecie de fiiere de date digitale.Bazele de date sunt manipulate cu ajutorul sistemelor de gestiune a bazelor de date.

Acestea, SGBD-urile, sunt responsabile cu crearea, manipularea intreinerea unei baze de date. Principala funcie a acestuia este de a permiteutilizatorilor (prin intermediul programelor) s acceseze date dintr-o baz de date.

Cel mai rspndit model de baze de date este cel relaional, n care datelesunt memorate n tabele. Pe lng tabele, o baz de date relaional mai poateconine: indeci, proceduri stocate, trigger-e, utilizatori i grupuri de utilizatori, tipuride date, mecanisme de securitate i de gestiune a tranzaciilor etc.

Cursul propune trecerea n revist a principalelorprobleme care apar nproiectarea i implementarea bazelor de date relaionale. Pentru exemplificareaconceptelor se utilizeaz sistemul de gestiune MySql.

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.

- este definit ca o colecie de date aflate n interdependen, mpreun cudescrierea datelor i a relaiilor dintre ele.

2. Organizarea bazei de date se refer la structura bazei de date i reprezintun ansamblu de instrumente pentru descrierea datelor, relaiilor, restriciilor lacare 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, FoxPro, PARADOX, ORACLE, MySQL)

- este software-ul bazei de date care asigur:1) definirea structurii bazei de date;2) ncrcarea datelor n baza de date;


2/103

2

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);6) securitatea datelor.

4. Cele teri conceptele de bazutilizate n organizarea bazei de date sunt:- entitatea- atributul- valoarea

Prin entitatese nelege un obiect concret sau abstract reprezentat prinproprietile sale. O proprietate a unui obiect poate fi exprimat prin perechea(ATRIBUT, VALOARE).

Exemplu: n exemplul Masa x are culoarea alba, atributul esteculoare, iar valoarea este reprezentat de cuvntul alb. Alte exemple arputea fi: (Sex, Feminin), (Nume, POP), (Profesie, Medic), (Salariu, 200).Observaie:Atributele pot caracteriza o clas de entiti, nu doar o entitate.

5. Data este un model de reprezentare a informaiei, accesibil unui anumitprocesor (om, program, calculator) i se definete prin:

- Identificator;- Atribut;- Valoare.

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 unSGBD, parcurge uzual urmtoarele etape:1. Utilizatorul, aflat la un terminal electronic, pune o "ntrebare" sau lanseazo cerere de date, 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 estefamiliarizat cu acest limbaj - de exemplu, SQL, FoxPro, dBase, Oracle) sau utilizatorulpoate fi asistat n adresarea cererii de date de ctre SGBD (produsul soft pe care lfolosete) printr-un sistem de meniuri, butoane sau ferestre de dialog (obiecte decontrol).2. ntrebarea este analizat de ctre calculator, de fapt de SGBD, iar dac estecorect, se ncearc (SGBD) s i se dea 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 prinfurnizarea datelor carendeplinescproprietile cerute se numete interogareabazei dedate.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


3/103

3

- 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 1.1 este prezentat o baz de date foarte mic, ce conine unsingur fiier, numit VINOTECA; la rndul su, aceasta cuprinde date despreconinutul unei anumite vinoteci.n figura 1.2este prezentat un exemplu de operaiede consultare din baza de date, mpreun cu datele returnate prin aceast operaie.

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

Fig. 1.2 Exemplu de consultare

Observaii: n limbajul SQL, fiierul VINOTECA din figura 1.1 este numittabela, rndurile unei astfel de tabele pot fi considerate ca nregistrridin fiier, iar coloanele pot fi considerate drept cmpuri.

1.3 Realizarea unei baze de date

Realizarea unei baze de date presupune parcurgerea etapelor:1. analiza domeniului (sistemului) pentru care se realizeaz baza de date;2. proiectarea structurii bazei de date;3. ncrcarea datelor n baza de date;

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

Consultare:

SELECT vin, raft, producator

FROM VINOTECA

WHERE lansat = 2004;

Rezultat ce apare pe monitorul unui PC:

vin raft producatorCab. Sauvignon 2 Windsor

Pinot Noir 3 Fetzer

Merlot 50 Clos du Bois


4/103

4

4. exploatarea i ntreinerea bazei de date.

Fig. 1 Realizarea unei baze de date

a) Analiza sistemului

Analiza sistemului presupune stabilirea temei, analiza componentelorsistemului i analiza legturilor (asocierilor) dintre aceste componente. Rezultatulanalizei formeaz modelul conceptual al bazei de date.

Cele patru etape necesare realizrii unei baze de date vor fi tratate peparcusul ntregului curs urmrind un exemplu concret i anume o baz de datepentru o agenie imobiliar din ar, denumit AGENIE IMOBILIAR, carefaciliteaz tranzacii de vnzare cumprare ntre vnztor i cumprtor, caregestioneaz documentelegate de oferte imobiliare, de ntreinere a nomenclatoarelorspecifice domeniului i care ofero gam larg de rapoarte privind situaia vnzare-cumprare.

Odat stabilit tema proiectului, se trece la etapa urmtoare, i anume laidentificarea tuturor tipurilor de informaii, a legturilor dintre informaii i a operaiilornecesare pentru gestionarea lor. Aceast etap va fi detaliat n cursul urmtor.

b) Proiectarea structurii bazei de date

Dac etapa de analiz a modelului conceptual se realizeaz independent deun SGBD, prin etapa de proiectare a structurii bazei de date se trece la luarea nconsiderare a SGBD-ului cu ajutorul cruia va fi implementat i exploatat baza dedate.

Proiectarea structurii bazei de date reprezint transpunerea rezultatelorobinute n urma analizei modelului conceptual n termenii unui model al datelorsuportat de un anumit SGBD. Compilatorul limbajului de descriere a datelor permiteaducerea schemei bazei de date la nivelul la care s poat fi memorat n baza dedate.

Astfel, proiectarea presupune o detaliere, de exemplu, de tip pseudocod amodulelor necesare realizrii bazei de date: module pentru crearea fiierelor, pentruintroducerea datelor, pentru prelucrarea i extragerea rezultatelor, pentru tratareaerorilor etc.

LMD

LMD LDD

LMD, LDD

1. Analiza

3. ncrcareadatelor 2. Proiectarea

Bazade

date4. ntreinerea 4. Exploatarea


5/103

5

c) 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.

d) Exploatarea i ntreinerea bazei de date

Exploatarea bazei de date de ctre diferii utilizatori finali este realizat nscopul satisfacerii cerinelor de informare ale acestora. SGBD sprijin utilizatorii finali

n exploatarea bazei de date, oferind o serie de mecanisme i instrumente cum ar filimbajele de manipulare a datelor (LMD).

ntreinerea bazei de date reprezint o activitate complex, realizat, nprincipal, de ctre administratorul bazei de date i care se refer la actualizareadatelor din cadrul bazei de date.


6/103

6

CURS 2. Construirea de diagrame entitate-relaie

Prima etap pentru realizarea unei baze de date const n analiza sistemului.Se cunosc mai multe tehnici de analiz, dar cea mai des ntlnit este tehnicaentitate-relaie.

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

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

a) Identificarea entitilor

Prin entitate se nelege un obiect concret sau abstract reprezentat prinproprietile sale.Prin convenie, entitile sunt substantive, se scriu cu litere mari ise reprezint prin dreptunghiuri.ntr-o diagram nu pot exista dou entiti cu acelainume, sau o aceeai entitate cu nume diferite.

Pentru baza de date din domeniul imobiliar considerat anterior, se pot punen 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 devnztori, respectiv cumprtori;

- DESCRIERE_IMOBILstocheaz informaiile referitoare la imobile;- JUDEE entitate ce conine judeele n care sunt amplasate imobilele;- LOCALITI - entitate ce conine localitile n care sunt amplasate

imobilele;

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

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

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

b) Identificarea asocierilor dintre entiti i calificarea lor

ntre majoritatea componentelor (adic a entitilor) unui sistem economic sestabilesc legturi (asocieri).

DATE_

PERSOANA

CERERI_

OFERTE

DESCRIERE_IMOBIL STRAZI

JUDETE

LOCALITATI

FACTURI


7/103

7

Exemplu: Exist o asociere ntreentitile CERERI_OFERTE i FACTURI deoarecefacturile reprezint finalizarea unei cereri/oferte. Aceast asociere se reprezint ca nfigura de mai jos.

Fig. 2.2. Prezentarea asocierii dintre entitile CERERI_OFERTE i FACTURI

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

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

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

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

entiti cu cealalt entitate asociat.Exemplu: Cardinalitatea (1,1) ataat entitii CERERI_OFERTA nseamn c ofactur poate fi rezultatul tranzacionrii a minim unei cereri/oferte i a unui numrmaxim 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 (0facturi) . Aceast cardinalitate reiese din analiz:

Fig. 2.3. Determinarea cardinalitii asocierii dintre entitileCERERI_OFERTE i FACTURI

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

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 dei) cardinalitatea asocierii;ii) numrul de entiti distincte care particip la asociere.

i. Dup cardinalitatea asocieriin funcie de maxima cardinalitii (gradul de asociere), se cunosc trei tipuri de

asocieri, care, 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 unuo asocieri totale de tip unu la unu

CERERI_

OFERTE

1

2

3

FACTURI

F1

F2

CERERI_OFERTE sunt finalizateprin

FACTURI(1,1) (0,1)


8/103

8

asocieri de tip unu la mai mulio asocieri pariale de tip unu la mulio asocieri totale de tip unu la muli

asocieri de tip muli la mulio asocieri pariale de tip muli la mulio 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 maimult 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 unusunt asocieri n care maximele cardinalitii auvaloarea 1.

Fig. 2.4. Asociere de tip unu la unu

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

2. Asocieride tip unu la mai mulisunt asocieri n care maxima cardinalitiiunei entiti este unu, iar a celeilalte entiti are valoarea muli.

Fig. 2.5. Asociere de tipul unu la mai muliExemplu:

Fig. 2.6. Asociere de unu la mai muli ntre entitile LOCALITI iCERERI_OFERTE

3. Asocieri de tipul muli la mulisunt asocieri n care maximele cardinalitii auvaloarea muli.

(0,n)(1,1)LOCALITATI CERERI_OFERTEi corespunde

LOCALITATI

L1

L2

L3

CERERI_OFERTE

1

2

3

A B

E1 E2A(...,1) (...,n)

E1 E2A(...,n) (...,1)

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


9/103

9

Fig. 2.7. Asociere de tipul muli la muli

Exemplu:

Fig. 2.8. Asociere de tipul muli la muli ntre entitile DEPOZITi PRODUS

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

Fig. 2.9. Transformarea unei asocieri de tipul muli la muli (a) n asocieri de tipul unula muli (b)

Exemplu: n cazul exemplului de mai sus (vezi figura 2.8), transformarea asocieriimuli la muli n asocieri de tipul unu la muli se poate realiza prin construirea uneinoi entiti DEPOZIT_PRODUS astfel:

Fig. 2.10. Transformarea asocierii de tipul muli la muli n asocieri de tipul unu lamuli

Asocieri parialei totale

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

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

A A1 A2

a) b)

DEPOZIT PRODUSnmagazineaz

(0,n)(0,n)

DEPOZIT

D1

D2

D3

PRODUS

P1

P2

P3

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

DEPOZIT

D1 D2

D3

D4

PRODUS

P1

P2

P3

P4

DEPOZIT_

PRODUS

D1-P1

D1-P3

D2-P1

D3-P4

(1,n)asociaz asociaz

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


10/103

10

Printr-o asociere parial se nelege o asociere n care nu existobligativitatea participrii la aceast asociere a tuturor entitilor vizate, ci numai aunora dintre ele sau a nici uneia. Asocierea parial se caracterizeaz prin faptul cminima cardinalitii ataat unei entiti este zero.

Observaii(asupra minimii cardinalitii)- minima cardinalitii este zero, are drept rezultat lipsa obligativitii participri

partenerului la aceast asociere;- minima cardinalitii este mai mare dect zero, are drept rezultat

obligativitatea participrii.

Fig. 2.11 Asocieri pariale ntre entitile E1 i E2

Exemplu: Asocierea dintre entitile CERERI_OFERTE i FACTURI din fig. 2.3reprezint o asociere parial, deoarece participarea entitii FACTURI nu este

obligatorie, minima caracteristicii corespunztoareentitii 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. 2.12 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

Exemplu: Asocieri totale de tip unu la unu

E1 E2A

a)

(1,) (1,)

E1 E2(1,) (n,)

E1 E2(n,) (n,)

E1 E2(n,) (1,)

b) c)

d)

A

A

A

E1 E2 E1 E2A A

a) b)

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

CERERI_OFERTE

1

2

3

FACTURI

F1

F2


11/103

11

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. 2.13 Asocieri dup gradul i obiectivitatea lor

n exemplul bazei de date AGENTIE_IMOBILIARA, tipurile de asocieri dintreentiti stabilite n funcie de modul n care se desfoar activitatea modelat sunt:

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

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

12

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

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

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

- CERERI_OFERTE-DECRIERE_IMOBIL 1:1 fiecrui i se face o

singur descriere;- FACTURI- DATE_PERSOANA 1:1 o factur este ncheiat de o

singur persoan;- DATE_PERSOANA -CERERI 1:n o persoan poate lansa mai multe

cereri sau oferte de imobil.

c) Identificarea atributelor entitilor i a asocierilordintreentiti

Atributele unei entiti reprezint proprieti ale acestora. Atributele suntsubstantive, iar pentru fiecare atribut i se va preciza tipul fizic ( integer, float, char,stringetc.)

Exemplu: Entitatea LOCALITI are urmtoarele atribute: codul localitii, notatcod_loc, simbolul de identificare al judeului simbol_jude i denumirea localitiinume_loc.

d) Stabilirea atributelor de identificare a entitilor

Un atribut de identificare (numit cheieprimar), reprezint un atribut care secaracterizeaz prin unicitatea valorii sale pentru fiecare instan a entitii.

n cadrul diagramei entitate-asociere, un atribut de identificare se marcheazprin subliniere sau prin marcarea cu simbolul # plasat la sfritul numelui acestuia.

Fig. 2.14. Notaii uzuale pentru atributele de identificare

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

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

- ofer o identificare unic n cadrulentitii;- este uor de utilizat:- 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 muli candidai cheie se va selecta unul,preferndu-se unul cu valori mai scurte i mai puin volatile.

Exemplu: n urma analizrii celor 4 etape necesare construirii diagramei entitate-asociere:- identificarea entitilor domeniului sau a sistemului economic;

Ea

(a)

Ea#

(b)


13/103

13

- 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 domeniului ales n exemplu.

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

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

CERERI_OFERTE

tipul

data_inreg

cod_loc

id_strada

nr_imobil

pret_max

tip_solutionare

cnp

pret_min

id_co#

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,1)


14/103

14

Fig. 2.16. Reprezentarea atributelor aferente entitii CERERI_OFERTE (detaliudintr-o diagram E-R)

n reprezentarea atributelor aferente entitii CERERI_OFERTE semnificaiaatributelor este urmtoarea: cheia primar a entitii id_co reprezint numrul deordine al cererii sau ofertei de imobil lansat de o anumit pesoan, atributul tipulspecific dac este vorba de o cerere sau de o ofert, prin cnp se precizeaz codulnumeric personal al clientului, data_inreg reprezint data la care s-a nregistratoferta/cererea, apoi uremaz cteva date legate de imobil: codul strzii id_strada,numrul imobilului nr_imobil, preul minim, respectiv preul maxim al imobiluluipret_min, pret_max. Ultimul atribut, tip_solutionare precizeaz daccererea/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 acror asociere a fost prezentat n figura 2.16.

Fig. 2.16. Baza de date AGENIE IMOBILIAR- entiti i atribute

DATE_PERSOANA

cnp#

numele

adresa

nr_telefon

email

banca_client

nr_cont_client

STRZIid_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

TVAtotal


15/103

15

CURS 3. Proiectarea modelului relaional

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

Modelul relaional este cel mai utilizat dintre modelele de date existente(modele ierarhice, modele reea, modele orientate pe obiect). Fa de modeleierarhic i reea, modelul relaional prezint cteva avantaje:

- propune structuri de date uor de utilizat;- amelioreaz 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-aconturat n dou articole publicate de ctre F.E. Codd n 1969 i 1970, matematicianla 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 esteimplementat. 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 utilizatesisteme de gestiune a bazelor de date relaionale (SGBDR) dedicate uzului individualsunt: ACCESS, PARADOX, Visual Fox Pro. Pentru aplicaiile complexe din bnci iinstituii de mari dimensiuni se folosesc SGBDR-urile de categorie grea, ORACLE,DB2 IBM, Informix IBM, SyBase (SyBase), SQL Server (MicroSoft). Sunt mult mairobuste, fiabile, dar i costisitoare. n ultimul timp i-au fcut apariia aa-ziseleSGBD-uri (aproape) gratuite: PostgreSQL, MySQL, mSQL, FireBird etc. (Acestearuleaz de obicei pe sisteme de operare Linux).

Modelul relaional are la baz teoria matematic a relaiilor i poate fi privit cao mulime de tabele obinute prin metoda normalizrii, eliminndu -se astfel anomaliilede actualizri.

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

3.1 Structura relaional a datelor

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

n cadrul bazei de date relaionale, datele sunt organizate sub forma unortablouri bidimensionale (tabele) de date, numite relaii. Asocierile dintre relaii sereprezint prin atributele de legtur. n cazul legturilor de tip unu la muli, acesteatribute figureaz ntr-una dintre relaiile implicate n asociere. n cazul legturilor detip muli la muli, atributele sunt situate ntr-o relaie distinct, construit specialpentru explicarea legturilor ntre relaii.

Prezentarea structurii relaionale a datelor impune definirea noiunilor de:


16/103

16

- domeniu;- relaie;- atribut;- schem a unei relaii.

Conceptele utilizate pentru a descrie formal, uzual sau fizic elementele debaz ale organizrii datelor sunt date n urmtorul tabel:

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

Domeniul

Domeniul reprezint o mulime de valori, notat prin litere mari D1,D2 etc.,caracterizat printr-un nume.

Modalitile de definire a unui domeniu sunt:- explicit: prin enumerarea tuturor valorilor aparinnd domeniului;- implicit: prin precizarea proprietilor pe care le au valorile din cadrul

domeniului.-

Exemplu: D1: {Da, Nu} reprezint un domeniu definit explicit. D2: {x/ x este de datcalendaristic} sau D3: {s/ s este numr decimal} reprezint domenii definite implicit,unde prin numr decimalse nelege un numr zecimal pentru care se precizeaznumrul de cifre componente.

Printr-un tupluse nelege o succesiune de valori de diferite tipuri. Un tuplu se

noteaz enumernd valorile sale , unde V1 este o valoare dindomeniul D1, V2D2 etc.Exemplu: Considerm c tuplul referitor la persoana x din entitateaCERERI_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 multedomenii D1, D2, ..., Dn, reprezentat sub forma unei tabele de date (tabelulbidimensional) i deci, o mulime de tupluri.Exemplu: Considerm c:- D1 cuprinde valori referitoare la tipul soluionrii tranzaciei: Da, dactranzacia a fost soluionat, Nu, n caz contrar;- D2 cuprinde valori ale datei calendaristice;- D3conine valori care exprim cnp-ul persoanei.

Formal Uzual Fizic

Realie Tablou FiierTuplu Linie nregistrare

Atribut Coloan CampDomeniu Tip de dat Tip de dat


17/103

17

De asemenea considerm c se cunosc datele a doi ofertani i c fiecare pune nvnzare doar cte un imobil.Atunci definim relaia R prin tuplurile care descriu acesteinformaii ale ofertelor celor dou persoane:

R: {, }.sau

R:D2 D3 D1

2661805270023 2006-05-27 Da1701205230023 2006-07-03 Nu

Fig. 3.2. Variante de prezentare a unei relaii R

Observaia 1.ntr-o relaie, tuplurile trebuie s fie distincte.Observaia 2. Cardinalulrelaiei este numrul tuplurilor dintr-o relaie.

Gradulrelaiei este numrul valorilor dintr-un tuplu.

Atributul

Atributulreprezint coloana unei tabele de date, caracterizat printr-un nume.Exemplu:R:

Fig. 3.3. Relaia R reprezentat cu ajutorul atributelor

Atributele sunt utile atunci cnd ntr-o relaie un domeniu apare de mai multeori. Prin numele dat fiecrei coloane (atribut), se difereniaz coloanele 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, pentrufiecare atribut precizndu-se domeniul asociat.

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

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

A1:D1 ... An:Dm

Fig. 3.4. Reprezentarea schemei relaiei R

Ca o concluzie, dintre caracteristicile modelului relaional menionm:- nu exist tupluri identice;- ordinea liniilor i a coloanelor este arbitrar;- articolele unui domeniu sunt omogene;

cnp: D2 data_nreg:D3 tip_soluionare:D3

2661805270023 2006-05-27 Da1701205230023 2006-07-03 Nu


18/103

18

- fiecare coloan definete un domeniu distinct i nu se poate repeta n cadrulaceleiai relaii.


19/103

19

CURS 4. Operatorii modelului relaional

3.2 Operatorii modelului relaional

Modelul relaional ofer dou colecii de operatori pe relaii:

- algebra relaional;- calcul relaional:

calcul relaional orientat pe tuplu; calcul relaional orientat pe domeniu.

n acest curs va fi tratat doar cazul algebrei relaionale.Algebra relaionaleste o colecie de operaii pe relaii, fiecare operaie avnd

drept operanzi una sau mai multe relaii, rezultatul fiind o alt relaie.Exist mai multe criterii de grupare a operaiilor:

- operaii de baz: reuniunea;

diferena;

produsul cartezian etc.- operaii derivate:

intersecia; diviziunea etc.

sau- operaii tradiionale pe mulimi (reuniune, intersecie, diviziune, produs

cartezian)- operaii relaionale speciale (selecia, proiecia, jonciunea, etc.)

Reuniunea

Reuniuneareprezint o operaie a algebrei relaional definit pe dou relaii:R1i R2, ambele cu aceeai schem, n urma creiase construiete o nou relaieR3, cu aceeai schem ca i R1i R2i avnd drept extensie tuplurile din R1i R2,luate mpreun o singur dat.

Notaii: R1U R2OR (R1, R2)APPEND (R1, R2)UNION (R1, R2)

Reprezentarea grafic

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

R1

R3

R2


20/103

20

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

Fig. 4.2. Reuniunea relaiilorARHIVA_OFERTE i ARHIVA_CERERI

Diferena

Diferenareprezint o operaie a algebrei relaionale definit pe dou relaii R1i R2, ambele cu o aceeai schem, n urma creia se construiete o nou relaie R3,cu schema identic cu R1i R2, avnd drept extensie acele tupluri ale relaiei R1 carenu se regsesc n relaia R2.

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

Reprezentarea grafic:

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

Exemplu: Presupunnd c exist clienicare au nregistrri n ambele tabele (adicau oferit imobil spre vnzare, dar i au achiziionat un alt imobil n acelai timp),pentru a afla care au fost doarofertanii de imobile, se aplic diferena dintre relaiileARHIVA_OFERTE i ARHIVA_CERERI.

id tipul cnp tip_solutionare1066 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

220 cerere 2820506300898 Da

1316 cerere 1881106300897 Da

id tipul cnp tip_solutionare210 cerere 2820506300898 Da

1316 cerere 1881106300897 Da

R1

R3

R2

-


21/103


22/103

22

Fig. 4.6. Produsul cartezian dintre relaiileLOCALIT i TARIFE

Proiecia

Proieciareprezint o operaie a algebrei relaionale definit asupra unei relaiiR, n urma creia se construiete o nou relaie P, n care se gsesc acele atributedin R specificate explicit n cadrul operaiei.

Prin operaie de proiecie se trece de la o relaie de grad n (are n coloane) la orelaie de grad mai mic, p (p


23/103


24/103

24

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


Fig. 4.11. Reprezentarea grafic a operaiei de jonciune

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

n funcie de operatorul de comparaie din condiia de concatenare, joinulpoate fi de mai multe feluri, ns cel mai important este equijoinul:

Exemplu: Aplicnd operaia de equijoin relaiilor DATE_PERSOANE i FACTURIpentru atributul cnp, se obininformaii referitoare laclienii care au ncheiat facturi.Pentru a nu ncrca figura, pentru cele dou relaii s-au ales doar cteva atribute.

Fig. 4.12. Operaia de equijoin a relaiilorDATE_PERSOANA i FACTURI

Observaie: Operaia de jonciune se poate exprima cu ajutorul operaiilor deprodus cartezian i selecie, rezultatul unui join fiind asemenea cu cel al operaiei deselecie asupra unui produs cartezian:

JOIN (R1, R2, condiie) = RESTRICT (PRODUCT (R1, R2), condiie).Este indicat utilizarea joinului n locul produsului cartezian, de cte ori este posibil.

cnp cnp

=

DATE_PERSOANA: FACTURI:

REZ

cnp numele adresa nr_telefon nr_

factura

id_co cnp

1551212245038 Pop

Radu

Str. Al.

Cuza,

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 ChisAlina

Str. Luminii, 76,Buzau

0721435622

atribut din R1 = atribut din R2

atribut din R1 operator de comparaie atribut din R2

atribut

din R1

atribut

din R2

Operator decomparaie

R1 R2

R3


25/103

25

Tipuri de jonciuni

n funcie de- tipul condiiilor de conectare- modul de definire a schemei- extensia relaiei rezultate prinjonciune,

vom studia:- jonciunea natural- jonciunea extern- semijonciunea.

Jonciunea natural

Jonciunea natural este o operaie definit pe dou relaii R1i R2, n urmacreia se construiete o nou relaie R3, a crei schem este obinut prin reuniuneaatributelor din relaiile R1i R2(atributele cu aceleai nume se iau o singur dat) i acrei extensie conine tuplurile obinute prin concatenarea tuplurilor din R1 cu cele din

R2care prezint aceleai valori pentru atributele cu aceleai nume.Jonciunea natural elimin inconvenientul ce apare n cazul equijoinului i

anume: schema relaiei n cazul equijoinului conine toate atributele celor dou relaii.Astfel, n relaia R3 a jonciunii naturale, atributele cu acelai nume vor aprea osingur dat.


Fig. 4.13. Reprezentarea grafic a operaiei de jonciune natural

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

R1 R2

R3


26/103


27/103


28/103

28

Semijonciuneaeste o operaie definit pe dou relaii R1i R2, n urma creiase construiete o nou relaie R3, a crei extensie conine tuplurile relaiei R1 careparticip la jonciunea celor dou relaii, conservnd atributele relaiei R1.

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


Fig. 4.18. Reprezentarea grafic a operaiei de semijonciune

Exemplu: Semijonciunea urmtoare realizeaz lista localitilor care au referin nrelaia JUDETE.

Fig. 4.19. Operaia de semijonciune a relaiilor LOCALITATI i JUDETE

Observaia 1. Aceast operaie a fost introdus de P.A. Bernstein, fiind

necesar la optimizarea cererilor de date.Observaia 2. Semijonciunea produce acelai rezultat ca operaia de proieciepe atributele din relaia R1efectuat asupra jonciunii dintre R1i R2PROJECT (JOIN (R1, R2, condiia), A1, A2, A3)=SEMIJOIN (R1, R2).

Intersecia

Interseciareprezint o operaie algebrei relaionale definit pe dou relaii, R1i R2, ambele cu aceeai schem, n urma creia se construiete o nou relaie R 3,

cod_loc# nume_loc simbol_judet

430 Baia Mare MM

435 Borsa MM

400 Cluj-Napoca CJ

710 Botosani BT

simbol_judet# nume_judet

MM Maramures

CJ Cluj

BV Brasov

simbol_judet

LOCALITATI:JUDETE:

cod_loc# denumire simbol_judet denumire430 Baia Mare MM Maramures

435 Borsa MM Maramures

400 Clu -Na oca CJ Clu

REZ:

R1 R2

R3


29/103


30/103

30

Fig. 4.22. Reprezentarea grafic a operaiei de diviziune

Exemplul se lasa ca exerciiu suplimentar.

Observaie: Operaia de diviziune este o operaie derivat deoarece se poateexprima prin intermediul operaiilor de baz: a diferenei, a produsului cartezian i aproieciei:

)))((()(,...,,...,,..., 111

RrRRrRppp AAAAAA

.

Complementarea

Complementarea reprezint o operaie (adiional) a algebrei relaionaledefinit asupra unei relaii R, n urma creia se construiete o nou relaie C, numitcomplementarea relaiei R. Extensia relaiei C va conine ansamblul tuplurilor dinprodusul cartezian al domeniilor asociate atributelor relaiei, care nu figureaz nextensia relaiei considerate.

Notaii:RNOT (R)COMP(R)

Exemplu: Fie relaia: R(A1:D1, A2:D2), undeA1 = culoare;A2= numr;D1= {Rou, Galben, Albastru}D2 = {1, 2, 3}

reprezentat prin tabelul:R:

A1:D1 A2.D2Rou 1Rou 2Galben 3

a) relaia R

Complementarea relaiei R va fi relaia NOT (R) repezintat prin tabelul: NOT (R):

A1:D1 A2:D2Rou 3Galben 1Galben 2Albastru 1

R

Q

r


31/103

31

Albastru 2Albastru 3

b) relaia not RFig. 4.24. Complementarea relaiei R

Observaie: Complementaritatea este puin utilizat, datorit rezultatului foartemare de tupluri.

Splitarea

Splitarea (spargerea) reprezint o operaie (adiional) a algebrei relaionaledefinit asupra unei relaii R, n urma creia se construiesc dou relaii R1i R2 cuaceeai schem cu R, relaii obinute pe baza unei condiii definite asupra atributelordin R.

Extensia lui R1conine tuplurile din R care verific condiia specificat, iar R2conine tuplurile din R care nu verific aceast condiie.

Exemplu: Considernd relaia R din figura 4.24 (a) i condiia A2>2, operaia de

splitare a relaiei R produce relaiile R1i R2 reprezentate prin tabelele:R1

A1:D12:D2

GalbenR2

A1:D1 A2:D2Rou 1Rou 2

Figura 4.25. Rezultatul operaiei de splitare a relaiei R din figura 4.24(a) pe baza condiiei A2>2

nchiderea tranzitiv

nchiderea tranzitiveste o operaie (adiional) a algebrei relaionale, definitasupra unei relaii R, a crei schem conine dou atribute A1 i A2 cu acelaidomeniu asociat, operaie care const n adugarea la relaia R a tuplurilor care seobin succesiv prin tranzitivitate: dac n R exist tuplurile: i se vaaduga la R tuplul .

Notaie: )(R R+

CLOSE(R)Exemplu:


32/103

32

Fig. 4.26. nchiderea tranzitiv a relaiei R

Persoana: D Urma: DAna Maria

Ana Ion

Ion Vasile

Ion Nicoleta

Maria Oana

Persoana: D Urma: DAna Maria

Ana Ion

Ion Vasile

Ion Nicoleta

Maria OanaAna Oana

Ana Vasile

Ana Nicoleta

a)

b

R: :R


33/103

33

CURS 5. Restricii de integritate ale modelului relaional

3.3 Restricii de integritate ale modelului relaional

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

Restriciile sunt de dou tipuri:- restricii de integritate structurale, care se definesc prin egalitatea sau

inegalitatea unor valori din cadrul relaiilor: restricia de unicitate a cheilor; restricia entitii; dependenele ntre ele;

- restricii de integritate de comportament care in cont de semnificaia valorilordin 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 deintegritate minimale. Acestea sunt obligatoriu de definit i de respectat cnd selucreaz cu modelul relaional.Dintre restriciile minimale fac parte:

restricia de unicitate a cheii; restricia referenial; restricia entitii.

Alte restriciii de integritate ar fi dependenele;

restricii de comportament.

Restricii de integritate minimale

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

Cheia unei relaiireprezint ansamblul minimal de atribute prin care se poateidentifica n mod unic orice tuplu al relaiei.

Oricare relaie posed cel puin o cheie:- cheie simpl, cnd cheia este construit dintr-un singur atribut;

- cheie compus, cnd cheia este construit din mai multe atribute.


34/103


35/103


36/103


37/103


38/103


39/103


40/103


41/103


42/103


43/103

43

transformare 4 poate fi folosit pentru a separa dou sau mai multe selecii n seleciiindividuale care pot fi distribuite jonciunii (join-ului) sau produsului cartezian folosindcomutarea seleciei cu jonciunea (join-ul).

Regula de optimizare 2. Produsele carteziene se nlocuiesc cu join-uri, ori decte ori este posibil. Un produs cartezian ntre dou relaii este de obicei mult maiscump (ca i cost) dect un join ntre cele dou relaii, deoarece primul genereazconcatenarea tuplurilor n mod exhaustiv i poate genera un rezultat foarte mare.

Aceast transformare se poate realiza folosind legtura dintre produs cartezian, joiniselecie.

Regula de optimizare 3. Dac sunt mai multejoin-uri atunci cel care se executprimul este cel mai restrictiv. Un join este mai restrictiv dect altul dac produce orelaie mai mic. Se poate determina carejoineste mai restrictiv pe baza factorului deselectivitate sau cu ajutorul informaiilor statistice. Algebric, acest lucru se poate realizafolosind regula de transformare 2.

Regula de optimizare 4. Proieciile se execut la nceput pentru a ndeprtaatributele nefolositoare. Dac un atribut al unei relaii nu este folosit n operaiileulterioare atunci trebuie ndeprtat. n felul acesta se va folosi o relaie mai mic noperaiile ulterioare. Aceasta se poate realiza folosind comutarea proieciei cujoin-ul.


44/103

44

CURS 7. Tehnica normalizrii relaiilor

La proiectarea structurii unei baze de date relaionale trebuie stabilite (dupcum s-a vzut n cursurile anterioare) n primul rnd tabelele n care vor fi memoratedatele i asocierile dintre tabele. Acestea sunt stabilite ntr-o form iniial, dupcare, 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 datenormalizate. Dac o baz de date nu este normalizat ea nu poate fi utilizat cu unmaxim de eficien. Algoritmul de normalizare a bazelor de date relaionale precum ipaii acestuia au fost descrii de ctre E. F. Codd n 1972.

Normalizareaeste procesul reversibil de transformare a unei relaii n relaii destructur mai simpl. (Procesul este reversibil n sensul c nici o informaie nu estepierdut n timpul transformrii). Scopul normalizrii este de a suprima redundanelelogice, de a evita anomaliilela reactualizare i rezolvarea problemei reconexiunii.

Exemplu: Pentru a evidenia cteva exemple de redundane i anomalii, se vaconsidera cazul relaiei iniiale OFERTANTI. Pentru a nu ncrca relaia, se vorconsidera valori ale atributelor prescurtate.

Fig.7.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, decicheia devine duplicat.

- Anomalii de tergere: tergerea unei persoane din baza de date atrage dupsine pierderea informaiilordespre oferta respectiv.

- Anomalii la modificare: Dac se modific numele strzii Victoriei dinlocalitatea Baia Mare n strada Independenei, modificarea trebuie efectuat pentrufiecare ofert din Baia Mare amplasat pe strada Victoriei. Dac ar exista 25 deoferte n aceast localitate pe strada Victoriei, costul modificrii ar fi mare pentru amodifica toate nregistrrile. Aceast redundan este eliminat dac atributuladresa este mprit n alte trei atribute: simbol_judet, cod_loc, id_strada.Valorile acestea vor fi codul judeului, localitii, respectiv a strzii preluate din

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,

nr. 4/5, Cluj-

Napoca, Cluj

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:


45/103

45

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, FN2i FN3. ntruct 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). AstfelBCNF este reprezentat separat n majoritatea lucrrilor. R. Fagin a tratat cazul FN4i FN5.

O relaie este ntr-o form normaldac satisface o mulime de constrngerispecificat n figura 7.2. De exemplu, se spune c o relaie se afl n a doua formnormal FN2 dac i numai dac se afl n FN1.

Fig.7.2. Formele normale ale relaiilor dintr-o BDR

Normalizarea bazei de date relaionale poate fi imaginat ca un proces prin carepornindu-se de la relaia iniial/universal R se realizeaz descompunerea

succesiv a acesteia n subrelaii, aplicnd operatorul de proiecie.

Relaia R poate fiulterior reconstruit din cele n relaii obinute n urma normalizrii, prin operaii dejonciune.

7.1 Prima form normal (FN1)

FN1 este strns legat de noiunea de atomicitate a atributelor unei relaii.Astfel, aducerea unei relaii n FN1presupune introducerea noiunilor de:

- atribut simplu;- atribut compus;- grupuri repetitive de atribute.

Atributul simplu- Atribut compus

Prin atribut simplu (atribut atomic) se nelege un atribut care nu mai poate fidescompus n alteatribute, n caz contrar, atributul este compus(atribut neatomic).

Exemplu: Urmtoarele exemple de atribute pot fi considerate simple sau compuse nfuncie de circumstane i de obiectivele bazei de date.- Data calendaristic este un atribut n careapar cmpurile: zi, lun, an;

Relaia universal

FN1

FN2

FN3

BCFNFN4

FN5


46/103

46

- Adresaeste un atribut n care apar cmpurile: strada, nr, bloc, scara, etaj,apartament, localitate, jude;- Data operaiunii bancare este un atribut n care apar cmpurile data, ora; - Buletin/carte identitate este un atribut n care apar cmpurile: seria, nr.

Aceste atribute pot fi atomice sau neatomice. Astfel adresa clieniloragenieiimobiliare intereseaz la nivel global, pe cnd pentru adresa ofertei sau a cererii deimobile 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 repetitiveste un atribut (grup de atribute) dintr-o relaie care apare cuvalori multiple pentru o singur apariie a cheii primare a relaiei nenormalizate.

Exemplu: Fie relaia nenormalizat(primar) FACTURI. Dorim s stabilim o structurde tabele care s permit stocarea informaiilor coninute n document (factur) iobinerea unor situaii sintetice privind evidena sumelor facturate pe produse, pe

clinei, pe anumite perioade de timp.

Fig. 7.3. Relaia FACTURI nenormalizat

n cazul n care o factur conine mai multe produse, relaia de mai sus va aveagrupurile repetitive: cod_produs, denumire_produs, cantitate, pret_unitar,valoare, valoare_tva.

Aducerea unei relaii universale la FN1

FN1 este tratat n general cu superficialitate, deoarece principala cerin atomicitatea valoriloreste uor de ndeplinit (cel puin la prima vedere).

FACTURI

nr_factura#

data_factura

nume_clientadresa_client

banca_client

nr_cont_clientdelegat

cod_produsdenumire_produs

unitate_de_masuracantiate

pret_unitar

valoarevaloare_tva

toal_valoare_factura

toal_valoare_tva


47/103


48/103


49/103

49

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


50/103

50

CURS 8. A treia form normal

7.3. A treia form normal (FN3)

O relaie este n forma normal trei FN3dac:1. se gseten FN2 i2. fiecare atribut care nu este cheie (nu particip la o cheie) depinde direct de cheiaprimar.

A treia regul de normalizare cere ca toate cmpurile din tabele s fieindependente 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 modunic atributele adresa_client, banca_client i nr_cont_client. Deci pentru atributulnume_client se construiete o relaie CLIENTI n care cheia primar va fi acestatribut, iar celelalte atribute vor fi adresa_client, banca_client i nr_cont_client.Cmpurile valoare i valoare_tva depind de cmpurile cantitate, pret_unitar, ide un procent fix de TVA. Fiind cmpuri ce se pot calcula n orice moment ele vor fieliminate din tabel LINII FACTURI deoarece constituie informaie memoratredundant.

Fig. 8.1. Relaia FACTURI n a treia forma normal FN3

Observaia 1: Aceast a treia form normal mai poate suferi o serie de rafinripentru a putea obine o structur performant de tabele ale bazei de date. Deexemplu se observ c nume_client este un cmp n care este nscris un text destulde lung format dintr-o succesiune de litere, semne speciale (punct, virgul, cratim),spaii, numere. Ordonarea i regsirea informaiilor dup astfel de cmpuri este lenti mai greoaie dect dup cmpuri numerice. Din acest motiv se poate introduce un

LINII_FACTURI

nr_factura#

cod_produs#

cantiatepret_unitar

PRODUSE

cod_produs#

denumire_produs

unitate_de_masura

FACTURI

nr_factura#

data_facturanume_client

delegat

toal_valoare_factura

toal_valoare_tva

CLIENTI

nume_client#

adresa_client

banca_clientnr_cont_client


51/103

51

nou atribut cod_client care s fie numeric i care s fie cheia primar de identificarea pentru fiecare client.Observaia 2: O alt observaie care poate fi fcut n legtur cu tabelele aflate ncea de a treia form normal este aceea c total_valoare_factura este un cmpcare ar trebui s conin informaii sintetice obinute prin nsumarea valorii tuturorofertelor aflate pe o factur. Este de preferat ca astfel de cmpuri s fie calculate nrapoarte 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, descompunereaeste fr pierderi.Celelalte forme normale se ntlnesc mai rar n practic. Aceste forme nu sunt

respectate, n general, pentru c beneficiile de eficien pe care le aduc nucompenseaz costul i munca de care este nevoie pent ru a le respecta.

LINII_FACTURI

nr_factura#

cod_produs#

cantiate

pret_unitar

PRODUSE

cod_produs#

denumire_produs

unitate_de_masura

FACTURI

nr_factura#

data_factura

CLIENTI

cod_client#

nume_client

adresa_client

banca_clientnr_cont_client


52/103


53/103


54/103

54

@ valoarea absolut! factorial!! factorial, operator postfix~ NOT orientat pe bii

operatori de comparaie

A BETWEEN min AND max (compar A cu dou valori: min i max) A IN (v1,...,vn) compar A cu o list de valoriA IS NULLA IS NOT NULLA LIKE model_ir

operatori logici

Operatorii logici sunt legai prin cuvintele cheie AND, OR, NOT i

returneaz o valoare logic TRUE, FALSE sau NULL. operatori relaionali

UNION (reuniune)INTERSECT (intersecie)MINUS (diferena).

Funcii definite n SQL

Funcii agregat

Funciile agregat calculeaz un rezultat din mai mul te linii ale unuitabel (funcii de totalizare):COUNT (furnizeaz numrul de linii ale unui rezultat);SUM (execut suma tuturor valorilor dintr-o coloan);MAX (returneaz valoarea cea mai mare dintr-o coloan);MIN (returneaz valoarea cea mai mic dintr-o coloan);AVG (calculeaz media valorilor dintr-o coloan).Aceste funcii vor fi folosite n instruciunea SELECT.

Funcii scalare

Funciile scalare primesc unul sau mai multe argumente i returneazvaloarea calculat sau NULL n caz de eroare. Argumentele funciilor pot ficonstante sau valori ale atributelor specificate prin numele coloanelorcorespunztoare. Dintre funciile scalare amintim: funcii numerice

- de calcul trigonometric: sin, cos, tg, ctg etc.- de calcul al logaritmului: ln, log, lg


55/103

55

- de calcul al puterilor: pow- de rotunjire: floor, ceil etc.

funcii pentru manipularea irurilor de caractere funcii pentru data calendaristic funcii de conversie

Tipuri de date

n limbajul SQL sunt definite mai multe tipuri de date: numeric, ir decaractere, ir de bii, data (calendaristic), timp.

Denumirile tipurilor de date precum i limitele acestora difer de la unSGBD la altul, dar n general, sunt destul de asemntoare.

Tipul numeric include

- numere ntregi: INTEGER sau INT reprezentat pe 4 octei;

SMALLINT reprezentat pe 2 octei;- numere reale reprezentate n virgul flotant, cu diferite precizii:FLOAT reprezentat pe 4 octei;REAL reprezentat pe 8 octei;DOUBLE [PRECISION] reprezentat pe 8 octei;

- numere zecimale reprezentate cu precizia dorit:tipul NUMERIC sau DECIMAL, cu forma numeric[(p,s)],unde p este numrul total de cifre afiate, iar s estenumrul de cifre dup punctul zecimal.

Tipul ir de caractere

CHARACTER (n) sau CHAR (n) definesc iruri de caractere culungimea fix.CHARACTER VARYING sau VARCHAR (n) definete irul de caractere culungimea variabil.

Asemnarea dintre cele dou tipuri prezentate mai sus este aceea cambele reprezint iruri de maxim n caractere, iar deosebirea este aceea cpentru iruri cu numr de caractere mai mic ca n, CHAR (n) completeaz irul cuspaii albe pn la n caractere, iar VARCHAR (n) memoreaz numai atteacaractere cte are irul dat.

Tipul iruri de bii

BIT (n) definete secvene de cifre binare (care pot lua valoarea 0 sau 1) delungime finit n;BIT VARYING (n) definete secvene de lungime variabil, cu limita maxim n.

Tipuri pentru data calendaristic i timp

DATE permite memorarea datelor calendaristice n formatul yyyy-mm-dd;


56/103

56

TIME permite memorarea timpului, folosind trei cmpuri hh:mm:ss;TIMESTAMP(p) permite memorarea combinat a datei calendaristice i atimpului, cu precizia ppentru cmpul SECOND (al secundelor); valoarea implicita lui peste 6;INTERVAL este utilizat pentru memorarea intervalelor de timp.

Tipurile de date sunt case-insensitive, deci nu in cont de caracterelemari sau mici.


57/103

57

CURS 10. Limbaje relaionale de definire a datelor (LDD)

Limbajul de definire a datelor (a schemei unei BD) include instruciuni cepermit:

- crearea schemei bazei de date;- adugarea relaiilor la schema bazei;- tergerea unor relaii existente;- adugarea de noi atribute relaiilor existente;- optimizarea bazei de date (index, grup, declanator);- definirea structurii fizice i logice a unei BD;- restricii cu privire la utilizarea structurii de mai sus.

Comenzi pentru crearea unei baze de date

Comanda pentru crearea unei baze de date este

CREATE DATABASE nume_baza;

Exemplu: S se creeze baza de date AGENTIA_IMOBILIARA.CREATE DATABASE AGENTIA_IMOBILIARA;

Aceast comand creeaz o BD cu numele nume_baza. Nu toate SGBDRsuport noiunea explicit de BD, dei utilizarea unei asemenea noiuni poatefacilita controlul drepturilor de acces la relaiile BD. Sisteme precum DB2 nuposed noiunea explicit de BD, n timp ce sistemul dBASE o suport.

Creatorul bazei de date devine automat administratorul BD.

Comenzi pentru suprimarea unei baze de date

Comanda pentru suprimarea unei baze de date este

DROP DATABASE nume_baza;

Aceast comand distruge BD cu numele nume_baza.

Comenzi pentru crearea relaiilor de baz

n cadrul acestor comenzi se precizeaz numele relaiei precum i numelei tipul atributelor.

n SQL, cele mai frecvente tipuri de date sunt:

CHAR pentru ir de caractere de lungime fix;VARCHAR2 pentru ir de caractere de lungime variabil;NUMBER pentru numere ntregi sau reale de lungime variabil;DATE pentru date calendaristice;LONG pentru texte de lungime variabilRAW pentru informaie binar de lungime variabil.

Comanda de creare a unei relaii esteCERATE TABLE nume_tabela (atribute);


58/103

58

Crearea unei relaii indicnd cheia la nivel de coloan

Exemplu: S se creeze relaia JUDETE (simbol_judet, nume_judet).CREATE TABLE JUDETE

(simbol_judet CHAR(2) PRIMARY KEY,nume_judet VARCHAR(30));

Crearea unei relaii indicnd cheile la nivel de tabel

Exemplu: S se creeze relaia LOCALITATI (cod_loc, simbol_judet, nume_loc).CREATE TABLE LOCALITATI

(cod_loc VARCHAR(7),simbol_judet CHAR (2),nume_loc VARCHAR (50),PRIMARY KEY (cod_loc, simbol_judet),

FOREIGN KEY (simbol_judet)REFERENCES JUDETE(simbol_judet));

Dac cheia primar are mai mult de o coloan atunci cheile trebuieindicate la nivel de tabel.

Crearea unui tabel prin copiere

Exemplu: S se creeze relaia LOCALITATI_CLUJ (cod_loc, simbol_judet,nume_loc) utiliznd copierea datelor din relaia LOCALITATI.

CREATE TABLE LOCALITATI_CLUJSELECT

cod_loc ,simbol_judet ,nume_loc

FROM LOCALITATIWHERE simbol_judet LIKE 'CJ';

Comenzi pentru suprimarea unei relaii de baz

Comanda de suprimarea unei relaii esteDROP TABLE nume_tabela;

Comanda SQL distruge relaia nume_tabela. Comenzi pentru schimbareanumelui unei relaii

Comanda SQL pentru schimbarea numelui unei relaii esteRENAME nume_tabela TO nume_tabela_nou;


59/103


60/103

60

DEFAULTNOT NULLUNIQUECHECK

- constrngeri de integritate a entitii care precizeaz cheia primar

PRIMARY KEY- constrngeri de integritate referenial care asigur corespondena ntrecheile primare i cheile externe corespunztoare

FOREIGN KEYFiecrei restricii i se poate da un nume, lucru util atunci cnd, la un

moment dat (salvri, restaurri, ncrcarea BD) se dorete dezactivarea uneiasau mai multora dintre acestea. Astfel se prefigureaz numele fiecrei restriciicu tipul su:pk_(PRIMARY KEY) pentru cheile primareun_(UNIQUE) pentru cheile alternativenn_(NOT NULL) pentru atributele obligatorii

ck_(CHECK) pentru reguli de validare la nivel de atributfk_(FOREIGN KEY) pentru cheile strine.

Exemplu: S se realizeze constrngerea de cheie primar, de cheie extern iconstrngerea de domeniupentru relaia DESCRIERE_IMOBIL.

CREATE TABLE DESCRIERE_IMOBIL(id_co SMALLINT (7) NOT NULL,

CONSTRAINT FOREIGN KEY fk_co(id_co)REFERENCES CERERI_OFERTE(id_co),CONSTRAINT pk_co PRIMARY KEY (id_co),

tip_imobil VARCHAR(10),etaj VARCHAR(10),nr_camere SMALLINT (6),suprafata DECIMAL (12,4),garaj TINYINT (4),centrala_termica TINYINT (4),termopane TINYINT (4));

Observaia 1: Liniile ce nu respect constrngerea sunt depuse automatntr-un tabel special.

Observaia 2: Constrngerile previn tergerea unui tabel dac existdependene. (vezi cursul tergerea datelor)

Observaia 3: Constrngerile pot fi activate sau dezactivate n funcie denecesiti.

Observaia 4: Constrngerile pot fi create o dat cu tabelul sau dup ceacesta a fost creat.

Modificarea unei restricii de integritate

Comanda de modificare a unei restricii este


61/103


62/103

62

Observaia 3: Dacexist o cheie extern care refer o cheie primar idac se ncearc tergerea cheii primare, aceast tergere nu se poate realiza(tabelele sunt legate prin declaraia de cheie extern). tergerea este totuipermis dac n comanda ALTER apare opiunea CASCADE, care determin itergereacheilor externe ce refer cheia primar urmrind sintaxa

ALTER TABLE Nume_tabelaDROP PRIMARY KEY CASACDE;

Ex

Comenzi pentru acordarea drepturilor de acces la baza de date

La nivel logic, limbajele relaionale ofer comenzi pentru acordareadrepturilor de acces la baza de date. Accesul unor persoane la BD se poaterealiza doar n condiiile recunoaterii acestora de ctre sistem drept utilizatoriautorizai.

Creatorul unei relaii primete n mod automat toate privilegiile de operare

asupra acestei relaii:- cutri de date n relaie- actualizri ale datelor n relaie- actualizri ale schemei relaiei- ataarea unor restricii de integritate- suprimarea relaiei.

Poate acorda, la rndul su, privilegii asupra relaiei i altor utilizatori nfuncie de sistem:- sistem centralizat (INGRES) n cadrul cruia singurul care poate acordadrepturi de acces la BD este administratorul bazei de date, funcia deadministrator neputnd fi delegat altor persoane;

- sistem descentralizat (DBz, SABRINA, ORACLE) n cadrul cruiaadministratorul poate da drepturi de acces la BD, dar, n acelai timp, putnddelega i alte persoane s fie administratori.

Comanda n SQL de acordare a drepturilor utilizatorilor esteGRANT SELECT, UPDATE ,... ON nume_tabela TOnume_utilizator;

Exemplu: S se confere utilizatorului cu numele Zita i cu parola BDZ dreptul deconectare la BD, precum i unele drepturi de acces la una dintre tabelele bazeide date.

GRANT SELECT, UPDATE ON DESCRIERE_IMOBIL TO

Zita IDENTIFIED BY 'BDZ';

Comenzi pentru retragerea drepturilor de acces la baza de date

Comanda SQL pentru retragerea drepturilor de acces la BD este

REVOKE UPDATE ON nume_tabela FROM nume_utilizator;

Exemplu: S se retrag drepturile utilizatorului Zita de actualizare a datelor.


63/103

63

REVOKE UPDATE ON DESCRIERE_IMOBIL FROM Zita;


64/103


65/103

65

WHERE nr_telefon IS NOT NULL;

Interogarea datelor folosind operatorii logici AND, OR, NOT

Sintaxa pentru interogarea care utilizeaz un operator logic este

condiie 1 AND condiie 2;condiie1 OR condiie 2;NOT condiie;

Exemplu: S se determine numrul facturii i codul numeric personal pentruofertele soluionate dup date de 2006-05-01 i cu un pre final mai mare sauegal ca 100.000.

SELECT cnp,nr_factura FROM FACTURIWHERE data_factura=2006-08-01 ANDtotal>=100000;

Interogarea datelor folosind operatorul IN

Sintaxa este

SELECT valoare_cmp IN (valoare1, valoare2,...);Aceast sintax a operatorului IN este similar cu urmtoarea list de

disjuncii:Valoare_cmp=valoare1 OR valoare_cmp=valoare2 OR ...;

Exemplu: S se selecteze numrul facturii, id-ul cererii/ofertei, data facturii,valoarea total a facturii, valoarea TVA i codul numeric personal pentrucererile/ofertele soluionate cu valoarea total de 70.000,80.000, 90.000.

SELECT * FROM FACTURI

WHERE total IN (119000.00,178500.00,90000);

Interogarea datelor folosind sintaxa DISTINCT

Pentru a selecta seturi de valori distincte, adic eliminarea valorilorduplicat, n SQL se folosete sintaxa DISTINCT, micornd astfel setul de date.Sintaxa acestei comenzi este

SELECT DISTINCT nume_cmp1, nume_cmp2,... FROMnume_tabelaWHERE comenzi;sau

SELECT DISTINCT * FROM nume_tabela;Sintaxa DISTINCT se refer la o nregistrare care poate cuprinde unul saumai multe cmpuri.

Exemplu: S se afieze toate datele distincte n care s-au nregistrat cereri sauoferte.

SELECT DISTINCT data_inreg FROM CERERI_OFERTE;


66/103

66

Interogarea datelor folosind operatorul LIKE

Se cunosc mai multe modaliti de utilizare a expresiei LIKE, i anume:- pentru o expresie care ncepe cu o anumit liter, de exemplu litera A:

LIKE A%;

- pentru o expresie care se termin cu o anumit liter, de exemplu literaA: LIKE %A;- pentru o expresie care include o anumit liter, de exemplu litera A: LIKE

%A%;

Exemplu: S se selecteze numele, adresa i emailul tuturor persoanelor femininecare au adres de email pe yahoo sau personal.

SELECT numele, adresa, email FROM DATE_PERSOANAWHERE adresa LIKE %BAIA MARE% AND (email LIKE%yahoo% OR email LIKE %personal%);

Interogarea datelor folosind operatorul BETWEEN

Operatorul se utilizeaz n combinaie cu dou valori ntre care seaflvaloarea la care se refer operatorul. Sintaxa este

val BETWEEN minim AND maxim;

sau

val>=min AND val


67/103


68/103


69/103


70/103


71/103

71

Exemplul 1: Selectai codul ofertei/cererilor i codul localitilor fiecrei oferte folosindoperaia de join, apoi utiliznd clauza WHERE.

SELECT CO.id_co, CO.cod_locFROM CERERI_OFERTE CO INNER JOIN LOCALITATI L

ON (CO.cod_loc=L.cod_loc);

SELECT CO.id_co, CO.cod_loc

FROM CERERI_OFERTE CO, LOCALITATI LWHERE CO. cod_loc=L.cod_loc;

Observaie: Rezultatul este acelai. Valorile NULL vor fi ignorate.

Exemplul 2: Selectai numele persoanelor care ofer imobile, codul ofertelor, precumi denumirile localitilor, ordonnd alfabetic localitile.

SELECT DP.numele, CO.id_co, L.nume_locFROM DATE_PERSOANA DPINNER JOIN CERERI_OFERTE CO ON (DP.cnp=CO.cnp)INNER JOIN LOCALITATI L ON (CO.cod_loc=L.cod_loc)WHERE CO.tipul LIKE oferta

ORDER BY L.nume_loc;

SELECT DP.numele, CO.id_co, L.nume_locFROM DATE_PERSOANA DP, CERERi_OFERTE CO,LOCALITATI LWHERE CO.tipul LIKE ofertaAND DP.cnp=CO.cnpAND CO.cod_loc=L.cod_locORDER BY L.nume_loc;

Observaie: Sintaxei SELECT-FROM-INNER JOIN i se pot aduga i alte condiii,

neincluse n condiiile de join, dac acestea se refer la alte cmpuri dect cele careparticip la join.

Exemplul 3: Selectai numele persoanelor care ofer imobile n judeul Maramure,codul ofertelor, tipul acestora, precum i denumirile localitilor i a strzilor,ordonnd alfabetic localitile i strzile.

1) Folosind INNER JOINSELECT DP.numele, CO.id_co, S.nume_str, DI.tip_imobil,

L.nume_locFROM DATE_PERSOANA DP INNER JOIN

CERERI_OFERTE CO ON (DP.cnp=CO.cnp)

INNER JOIN STRAZI S ON (CO.id_strada=S.id_strada )INNER JOIN LOCALITATI L ON (CO.cod_loc=L.cod_loc

AND L.cod_loc LIKE 'MM%')INNER JOIN DESCRIERE_IMOBIL DI ON

(CO.id_co=DI.id_co AND CO.tipul='oferta')ORDER BY L.nume_loc, S.nume_str;

Observaie: Toate condiiile ce se refer la cmpurile din join se vor prezenta ncadrul condiiilor de join.


72/103


73/103

73

DI.nr_camere, DI.suprafata, DI.garaj,DI.centrala_termica, DI.termopane

FROM CERERI_OFERTE CO LEFT OUTER JOINDESCRIERE_IMOBIL DI ON(DI.id_co=CO.id_co)

INNER JOIN STRAZI S ON S.id_strada = CO.id_strada ANDCO.cod_loc=S.cod_loc

INNER JOIN LOCALITATI L ON CO.cod_loc = L.cod_loc

WHERE CO.tipul LIKE 'oferta';

c) Sintaxa

SELECT ...FROM tabel_A RIGHT OUTER JOIN tabel_B ON(condiii de join)

selecteaz toate informaiile din B, pe care le completeaz cu informaii din A, nmsura n care satisfac condiiile de join; acolo unde nu vor exista informaii din A,acestea vor fi completate cu NULL.

Exemplu: Selectai toate localitile i, n localitile n care exist cererinesoluionate, afiai numele clienilor i tipul de cerere de imobil respectiv.

SELECT L.nume_loc, DP.numele, CO.tip_solutionare,DI.tip_imobil

FROM LOCALITATI L RIGHT OUTER JOIN CERERI_OFERTECO ON (L.cod_loc=CO.cod_loc)INNER JOIN DATE_PERSOANA DP ON (DP.cnp=CO.cnp)INNER JOIN DESCRIERE_IMOBIL DI ON

(CO.id_co=DI.id_co AND CO.tipul = cerere)WHERE CO.tip_solutionare=0;

Observaie: Sintaxa RIGHT OUTER JOIN este utilizat mai rar; de obicei seutilizeaz sintaxa LEFT OUTER JOIN.


74/103

74

CURS 13. Limbaje relaionale de manipulare a datelor (LMD) -Interogarea datelor din mai multe relaii (continuare)

Interogarea datelor din mai multe relaii folosind instruciunea UNION

Sintaxa interogrii datelor din mai multe relaii folosind instruciunea UNIONeste

SELECT Cmp 1, Cmp 2, ..., Cmp nFROM Tabel 1UNION (ALL)SELECT Cmp 1A, Cmp 2A,..., Cmp nAFROM Tabel 2

i returneaz nregistrri distincte, dac este folosit instruciunea UNION i toate nregistrrile, dac se folosete UNION ALL. Astfel operatorul UNION eliminduplicatele, iar UNION ALL vizualizeaz toate nregistrrile, inclusiv duplicatele.

Pentru a utiliza aceast interogare, trebuie s se in seama de dou cerine:domeniile Cmp 1A, Cmp 2A,..., Cmp nA i Cmap 1, Cmp 2, ..., Cmp n trebuies fie respectiv aceleai i, numrul de cmpuri din fiecare interogare trebuie scoincid.

Operatorul UNION se folosete atunci cnd ntre relaii nu exist o asocieredirect.

Exemplul 1: Pentru exemplificare se vor considera relaiile: PROFESORI (prof_id,nume, prenume), respectiv STUDENTI (stud_id, nume, prenume). Selectai listanumelor tuturor profesorilor i a studenilor.

SELECT nume, prenume FROM PROFESORIUNION ALLSELECT nume, prenume FROM STUDENTI;

Rezultatul generat de interogare va fi

Fig. 13.1. Interogarea mai multor relaii folosind operatorul UNION ALL

Observai: Problema mai poate fi soluionat utiliznd alte interogri, dar acestearmn ca exerciii individuale.

Exemplul 2: S se determine care sunt ofertele i cererile soluionate prin facturi,afind ntr-o list id_ul cererii/ofertei i cnp-ul cleintului, att din tabelaCERERI_OFERTE ct i din tabela FACTURI.

SELECT cnp, id_co FROM FACTURIUNION ALLSELECT cnp, id_co FROM CERERI_OFERTE;

Rezultatul generat de interogare va fi o lista greu de urmrit, dup cum este i ceadin figura 13.1, deoarece nu se specific clar care inregistrare corespunde facturilor,i care tabelei CERERI_OFERTE (acest neajuns va fi nlturat utilizndconcatenarea):

nume prenume

POP VASILE

ION ANA


75/103

75

Fig. 13.2. Interogarea mai multor relaii folosind operatorul UNION ALL (cazul neclar)

Interogarea datelor mai multor relaii folosind operatorul de concatenare adou iruri de caractere

Rolul operatorului de concatenare a dou iruri de caractere este de a unidou iruri de caractere ntr-unul singur. Este utilizat n toate SGBD-urile, cu micimodificri ale simbolului: n Tranzact SQL se folosete simbolul +, n Oraclesimbolul || etc.

Se pot concatena o constant cu un cmp, sau dou cmpuri. Cmpuriletrebuie s fie de tip text.

Sintaxa pentru concatenarea a dou cmpuri esteCONCAT(Cmp1, Cmp2)

sau insernd virgula, spaiu sau oricare marcaj de delimitareCONCAT(Cmp1,,, Cmp2) sau CONCAT (Cmp1, , Cmp2).

Sintaxa

CONCAT(Ceva, Cmp)concateneaz cmpul i valoarea returnnd o singur valoare.

SintaxaCONCAT(Ceva1, Ceva1)

concateneaz cele dou constante ntr-una singur Ceva1Ceva2.

Exemplu: S se determine care sunt ofertele i cererile soluionate prin facturi,afind ntr-o list id_ul cererii/ofertei i cnp-ul cleintului, att din tabela


76/103


77/103

77

MAX(Nume_cmp) FROM Tabelareturneaz un numr egal cu valoarea maxim a cmpului Nume_cmp din relaiaTabela, valorile null fiind ignorate.

Exemplu: Selectai cea mai recent nregistrare din tabela CERERI_OFERTE, fr ada un nume rezultatului, apoi cu nume pentru cmpul rezultat.

SELECT MAX(data_inreg) FROM CERERI_OFERTE;SELECT MAX(data_inreg) ASdata_ultimei_nregistrari FROMCERERI_OFERTE;

b) Interogarea datelor folosind funcia MIN

Funcia MIN este o funcie similar cu funcia MAX, cu ajutorul creia se poatedetermina valoarea cea mai mic dintr-un cmp.

Att funcia MIN ct i funcia MAX se poate aplica doar pentru tipurile de datenumeric sau dat calendaristic.

c) Interogarea datelor folosind funcia COUNT

Sintaxa

COUNT (*) FROM Nume_tabela

returneaz un numr egal cu numrul de nregistrri ale tabelei Nume_tabela.

Exemplu: Precizai numrul de oferte nregistrare.SELECT COUNT(*) AS numar_de_oferteFROM CERERI_OFERTEWHERE tipul LIKE oferta;

Sintaxa

COUNT (Nume_cmp) FROM Tabelareturneaz un numr egal cu numrul de valori nenule ale cmpului Nume_cmp dintabela Nume_tabela. Sunt ignorate valorile null.

Exemplu: Precizai numrul de cereri nesoluionate.SELECT COUNT(tip_solutionare) AS cereri_solutionateFROM CERERI_OFERTEWHERE tip_solutionare=1 AND

tipul=cerere;

Sintaxa COUNT(DISTINCT Nume_cmp) FROM Tabelareturneaz un numr egal cu numrul de valori distincte nenule ale cmpuluiNume_cmp din tabela Nume_tabela. Sunt ignorate valorile null.

Exemplu: Precizai numrul de localiti din care provin ofertele.SELECT COUNT(DISTINCT cod_loc) FROM CERERI_OFERTEWHERE tipul=oferta;

d) Interogarea datelor folosind funcia SUM


78/103

78

Sintaxa

SUM (Nume_cmp) FROM Tabelareturneaz un numr egal cu suma tuturor valorilor cmpului Nume_cmp din relaiaNume_Tabela. Sunt ignorate valorile null.

Exemplu: Precizai suma tuturor ncasrilor existente pe facturile emise. SELECT SUM(DISTINCT total) FROM FACTURI;

Sintaxa

SUM (DISTINCT Nume_cmp) FROM Tabelareturneaz un numr egal cu suma valorilor distincte ale cmpului Nume_cmp dinrelaia Nume_Tabela.

Funcia SUM se aplic acelor cmpuri care au domeniul de valori de tipulFLOAT, DECIMAL, NUMERIC, INT etc. i nu are sene pentru cmpuri de tip text.

e) Interogarea datelor folosind funcia AVG

Sintaxa

AVG (nume_cmp) FROM Nume_tabelareturneaz un numr egal cu media aritmetic a tuturor valorilor cmpuluiNume_cmp din relaia Nume_tabela. Valorile null sunt ignorate.

Funcia AVG se utilizeaz doar pentru date de tip numeric: INT, FLOAT,NUMERIC.

Exemplu: Selectai media valorilor vnzrilor din agenia imobiliar.SELECT AVG (total) FROM FACTURI;

Interogarea datelor folosind instruciunea GROUP BY

Prin instruciunea GROUP BY se grupeaz datele dup fiecare produs nparte.

Exemplu: Selectai fiecare tip de imobil n parte grupndu-le alfabetic i precizainumrul de imobile vndute din fiecare tip.

SELECT DI.tip_imobil, COUNT(F.id_co) AS sumaFROM DESCRIERE_IMOBIL DI, FACTURI FWHERE F.id_co=DI.id_coGROUP BY DI.tip_imobil;

Interogarea returneaz urmtoarele informaii:

Fig. 13.4 Rezultatul interogrii folosind instruciunea GROUP BY i funcia SUM

Menionarea clauzelor SELECT, FROM, WHERE, GROUP BY, ORDER BY naceast ordine este obligatorie. Greeala frecvent care duce la apariia unor mesaje


79/103

79

de eroare este aceea a introducerii unor cmpuri dup care se grupeaz n clauzaSELECT i neintroducerea lor n clauza GROUP BY.

Pentru a evita pierderea de informaii, este indicat ca atributul dup care segrupeaz s fie cheie primar.

SELECT CO.id_co, J.nume_judet, L.nume_loc, S.nume_str,CO.pret_min, CO.pret_maxFROM JUDETE J, CERERI_OFERTE CO, LOCALITATI L,

STRAZI SWHERE CO.cod_loc=L.cod_locAND CO.id_strada=S.id_stradaAND L.simbol_judet=J.simbol_judetGROUP BY J.nume_judet;

Fig.13.5. Rezultatul interogrii folosind instruciunea GROUP BY cu pierderi deinformaii

Acest inconvenient este nlturat dac se grupeaz dup numele judeului,localitii i a strzii, caz n care vor aprea tupluri duplicat.

SELECT CO.id_co,J.nume_judet, L.nume_loc, S.nume_str,CO.pret_min, CO.pret_maxFROM JUDETE J, CERERI_OFERTE CO, LOCALITATI L,

STRAZI SWHERE CO.cod_loc=L.cod_locAND CO.id_strada=S.id_stradaAND L.simbol_judet=J.simbol_judetGROUP BY L.cod_loc;

Fig.13.6. Rezultatul interogrii folosind instruciunea GROUP BY fr pierderi deinformaii

Interogarea datelor folosind instruciunea HAVING


80/103

80

Instruciunea HAVING se utilizeaz numai n combinaie cu instruciuneaGROUP BY. Dac gruparea de date trebuie s satisfac vreo condiie, aceastcondiie se exprim cu ajutorul sintaxei HAVING.

Clauza HAVING este utilizat cnd se dorete filtrarea datelor grupateconform unor criterii. Aceste criterii presupun compararea unor valori obinute prinapelarea unor funcii totalizatoare. Aceste tipuri de comparri presupun gruparea

datelor. Din aceast cauz, HAVING cere obligatoriu clauza GROUP BY.

Exemplu: Selectai adreseleofertelor grupate dup judee, localiti i strzi care aupreul minim cuprins ntre 50000 i 300000.

SELECT CO.id_co,J.nume_judet, L.nume_loc, S.nume_str,CO.pret_min, CO.pret_maxFROM JUDETE J, CERERI_OFERTE CO, LOCALITATI L,STRAZI SWHERE CO.cod_loc=L.cod_locAND CO.id_strada=S.id_stradaAND L.simbol_judet=J.simbol_judet

GROUP BY CO.id_coHAVING CO.pret_min BETWEEN 50000 AND 300000;

Ordinea obligatorie a unei fraze SELECT complete este: SELECT, FROM,WHERE, GROUP BY, HAVING, ORDER BY.


81/103

81

Curs 14. Funcii utilizate n interogri

Cele mai des ntlnite funcii n interogri sunt:a) funcii pentru iruri de caractereb) funcii pentru valori numericec) funcii pentru date calendaristiced) funcii de conversie dintr-un tip n altul.

a) Funcii pentru iruri de caractere

- CONCAT: concateneaz dou iruri de caractereSELECT CONCAT('My', 'S', 'QL');

-> 'MySQL'SELECT CONCAT('My', NULL, 'QL');

-> NULLSELECT CONCAT(14.3);

-> '14.3'- REPLACE: nlocuirea unui ir de caractere cu un altul ntr-o expresie de acest

tip;SELECT REPLACE('www.mysql.com', 'w', 'Ww');

->WwWwWw.mysql.com- CHAR_LENGTH: returneaz numrul de caractere dintr-un ir;- FIELD(str,str1,str2,str3,): returneaz poziia irului de caractere str n listairurilor de caractere str1,str2,str3; dac irul str nu este gsit, returneazvaloarea 0.

SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');-> 2

SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');-> 0

- LEFT(str,n): returneaz subirul fromat dintr-un numr de n caractere;SELECT LEFT('paralelipiped', 5);

-> 'paral'- LOWER(str): toate literele din str vor fi convertite n minuscule;

SELECT LOWER('LITERE MARI');-> 'litere mari'

- UPPER(str): toate literele din str vor fi convertite n majuscule;SELECT LOWER('LITERE mari');

-> 'LITERE MARI'

- LPAD(str,n,caracter): completeaz la stnga cu un caracter pn la atingereaunei lungimi specificate

SELECT LPAD('buna',6,'?');->??buna

SELECT LPAD('buna',1,'??');->b

- RPAD(str,n,caracter): completeaz la dreapta cu un caracter pn laatingerea unei lungimi specificate

SELECT RPAD('buna',5,'?');


82/103

82

->buna?- LTRIM(str): elimin spaiile de la stnga valorii str;

SELECT LTRIM(' barbar');->barbar

- RTRIM(str): elimin spaiile de la dreapta valorii str;SELECT RTRIM('barbar ');

->barbar

- TRIM: eliminarea simultan a spaiillor la stnga i la dreapta;SELECT TRIM(' bar ');

->bar- SUBSTR(sir,n): extragerea unei poriuni dintr-un ir ncepnd cu a n-a liter;

SELECT SUBSTRING('Paralelipiped',5);->lelipiped

b) Funcii pentru valori numerice

- CEIL(p): ntoarce cel mai mic ntreg mai mare sau egal cu argumentul p; SELECT CEIL(1.23);

->2SELECT CEIL(-1.23);

->-1- FLOOR(p): ntoarce cel mai mare ntreg mai mic sau egal cu argumentul p;

SELECT FLOOR(-1.23);->-2

SELECT FLOOR(1.23);->1

- ROUND(p,n): rotunjete rezultatul unei expresii (p) la un numr de poziiifuncionare dac n este pozitiv, sau, dac n este negativ, se face la ordinulzecilor, sutelor, miilor etc.

SELECT ROUND(-1.23);->-1

SELECT ROUND(-1.58);->-2

SELECT ROUND(1.58);->2

SELECT ROUND(1.298, 1);->1.3

SELECT ROUND(1.298, 0);->1

SELECT ROUND(23.298, -1);

->20- TRUNC(p,n): are efect similar funciei ROUND, numai c n loc de rotunjire se

face trunchiere.SELECT TRUNCATE(1.223,1);

->1.2SELECT TRUNCATE(1.999,1);

->1.9SELECT TRUNCATE(1.999,0);

->1SELECT TRUNCATE(-1.999,1);


83/103

83

->-1.9SELECT TRUNCATE(122,-2);

->100

c) Funcii pentru date calendaristice

- CURRENT_DATE(): furnizeaz data curent sub forma YYYY-MM-DD;SELECT current_date();

-> 2006-08-15- SYSDATE(): furnizeaz data curent i ora exact sub forma YYYY-MM-DD

HH-MM-SS;SELECT SYSDATE();

-> 2006-08-15 15:06:44- CURRENT_TIMESTAMP(), NOW(), SYSTIMESTAMP: sunt sinonime cu

SYSDATE;- DATE_ADD (data,INETRVAL nr.): adun un numr de ani, luni sau zile la data

argument;SELECT DATE_ADD('2006-08-15', INTERVAL 1 MONTH);

-> 2006-09-15- LAST_DAY(data): furnizeaz ultima zi din luna n care se afl data argument;

SELECT LAST_DAY('2008-02-15');-> 2008-02-29

- DATEDIFF(data1,data2): calculeaz numrul de zile dintre cele dou datecalendaristice;

SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');->1

SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');->-31

- DAYNAME(data): afieaz denumirea zilei corespunztoare datei argument;

SELECT DAYNAME(2006-08-19);-> 'Saturday'

- DAYOFMONTH(data), DAYOFYEAR(data): (ziua din lun, ziua din an)furnizeaz numrul de ordine n cadrul lunii, respective a anului pentru o datcalendaristic.

SELECT DAYOFYEAR('2006-08-19');->231

d) Funcii de conversie dintr-un tip n altul

Funcia de conversie cel mai des utilizat este funcia CAST.

- CAST: realizeaz diferite conversii, de exempluSELECT CONCAT('Data: ', CAST(NOW() AS DATE));->Data: 2006-08-18

concateneaz irul de caractere data: cu data calendaristic actual, convertit lair de caractere.

Exemple

Exemplul 1: Modificai toate numerele de telefon din judeul Maramure, astfel caprefixul s nu mai fie 0262 ci 0362, din baza de date AGENTIE_IMOBILIARA.


84/103

84

UPDATE DATE_PERSOANA SETnr_telefon=CONCAT('0362', SUBSTR(nr_telefon, 5))WHERE SUBSTR(nr_telefon,1,4)='0262';

Pentru modificarea datelor s-a folosit comandaUPDATE nume_tabel SET instructiuniWHERE conditii,

iar funciaSUBSTR(sir,nr)

extrage ciferele din ntregul numr de telefon, ncepnd cu a cincea poziie, cifre cevor fi concatenate cu prefixul 0362. De asemenea, prin funcia

SUBSTR(nr_telefon,1,4)se realizeaz extragerea primelor patru numere din ntregul numr de telefon.

Exemplul 2: Afiai numele i data naterii clienilor din baza de dateAGENTIE_IMOBILIARA, cunoscnd codul numeric personal al acsotra.

SELECT CONCAT('Numele: ',numele) AS numele,CONCAT('Anul: ', '19',SUBSTR(cnp,2,2),' , ','Luna:',

SUBSTR(cnp,4,2), ', ','Ziua: ',

SUBSTR(cnp,6,2))AS data_nasterii

FROM DATE_PERSOANA;Observaie: n interogarea de mai sus se concateneaz irul Numele cu numeleclientului, apoi se concateneaz irul Anul cu irul 19 (deoarece se consider c nusunt clieni nscui dup anul 1999) i cu cele dou cifre ale codu lui numericpersonal care desemneaz anul naterii unei persoane (SUBSTR(cnp,2,2)). n modanalog se efectueaz i celelalte concatenri.

Fig. 14.1. Utilizarea funciilor CONCAT i SUSTR

Exemplul 3: Afiai preurile ofertelor (preul minim i preul maxim) folosind alinierea

la dreapta i la stnga.SELECT LPAD(pret_min,30,' ') AS 'pret minim',RPAD(pret_max,30,' ') AS 'pret maxim'

FROM CERERI_OFERTEWHERE tipul LIKE ofertaORDER BY pret_min,pret_max;


85/103

85

Fig. 14.2. Utilizarea funciilor LPAD i RPAD

Exemplul 4: S se afieze numrul de cereri de imobile primite n prima jumtate aoricrei luni fa

Oracle Clasa 12

Documents

Transcript of Oracle Clasa 12