oracle clasa 12
TRANSCRIPT
-
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;
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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.
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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/8/2019 Oracle Clasa 12
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)
-
8/8/2019 Oracle Clasa 12
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)
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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)
-
8/8/2019 Oracle Clasa 12
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)
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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:
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
18/103
18
- fiecare coloan definete un domeniu distinct i nu se poate repeta n cadrulaceleiai relaii.
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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
-
-
8/8/2019 Oracle Clasa 12
21/103
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
23/103
-
8/8/2019 Oracle Clasa 12
24/103
24
Notaie: R1 R2;JOIN(R1,R2,condiie)
Reprezentarea grafic:
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
-
8/8/2019 Oracle Clasa 12
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.
Reprezentarea grafic:
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
-
8/8/2019 Oracle Clasa 12
26/103
-
8/8/2019 Oracle Clasa 12
27/103
-
8/8/2019 Oracle Clasa 12
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).
Reprezentarea grafic:
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
-
8/8/2019 Oracle Clasa 12
29/103
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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:
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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.
-
8/8/2019 Oracle Clasa 12
34/103
-
8/8/2019 Oracle Clasa 12
35/103
-
8/8/2019 Oracle Clasa 12
36/103
-
8/8/2019 Oracle Clasa 12
37/103
-
8/8/2019 Oracle Clasa 12
38/103
-
8/8/2019 Oracle Clasa 12
39/103
-
8/8/2019 Oracle Clasa 12
40/103
-
8/8/2019 Oracle Clasa 12
41/103
-
8/8/2019 Oracle Clasa 12
42/103
-
8/8/2019 Oracle Clasa 12
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.
-
8/8/2019 Oracle Clasa 12
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:
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
47/103
-
8/8/2019 Oracle Clasa 12
48/103
-
8/8/2019 Oracle Clasa 12
49/103
49
Chiar dac au fost eliminate o parte din redundane, mai rmn i alteredundane ce se vor elimina aplicnd alte forme normale.
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
52/103
-
8/8/2019 Oracle Clasa 12
53/103
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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;
-
8/8/2019 Oracle Clasa 12
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.
-
8/8/2019 Oracle Clasa 12
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);
-
8/8/2019 Oracle Clasa 12
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;
-
8/8/2019 Oracle Clasa 12
59/103
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
61/103
-
8/8/2019 Oracle Clasa 12
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.
-
8/8/2019 Oracle Clasa 12
63/103
63
REVOKE UPDATE ON DESCRIERE_IMOBIL FROM Zita;
-
8/8/2019 Oracle Clasa 12
64/103
-
8/8/2019 Oracle Clasa 12
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;
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
67/103
-
8/8/2019 Oracle Clasa 12
68/103
-
8/8/2019 Oracle Clasa 12
69/103
-
8/8/2019 Oracle Clasa 12
70/103
-
8/8/2019 Oracle Clasa 12
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.
-
8/8/2019 Oracle Clasa 12
72/103
-
8/8/2019 Oracle Clasa 12
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.
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
76/103
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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
-
8/8/2019 Oracle Clasa 12
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.
-
8/8/2019 Oracle Clasa 12
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,'?');
-
8/8/2019 Oracle Clasa 12
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);
-
8/8/2019 Oracle Clasa 12
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.
-
8/8/2019 Oracle Clasa 12
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;
-
8/8/2019 Oracle Clasa 12
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