1/ 37
Baze de date Algebra relat¸ional˘ a
Nicolae-Cosmin Vˆarlan
October 8, 2019
Elemente ale modelului relat¸ional
I U mult¸ime de atribute: U ={A1, A2, . . . , An};
I dom(Ai) - domeniul valorilor atributuluiAi; Definimuplu pesteU ca fiind funct¸ia:
ϕ:U → [
1≤i≤n
dom(Ai) a.i.ϕ(Ai)∈dom(Ai),1≤i≤n
Fie valorilevi astfel ˆıncˆat vi=ϕ(Ai).
Not˘am cu {A1 :v1, A2 :v2, . . . , An:vn} asocierea dintre atributele existente ˆınU ¸si valorile acestora. ˆIn cazul ˆın care sunt considerate mult¸imi ordonate (de forma (A1, A2, . . . , An)), notat¸ia va fi de forma: (v1, v2, . . . , vn).
3/ 37
Elemente ale modelului relat¸ional
Consider˘am mult¸imea ordonat˘a (A1, A2, . . . An). Pentru orice uplu ϕ, exist˘a vectorul (v1, v2, . . . vn) a.ˆı. ϕ(Ai) =vi, 1≤i≤n.
Pentru un vector(v1, v2, . . . vn)cu vi∈dom(Ai), 1≤i≤nexist˘a un upluϕa.ˆı. ϕ(Ai) =vi.
ˆIn practic˘a este considerat˘a o anumit˘a ordonare a atributelor.
Elemente ale modelului relat¸ional
O mult¸ime de uple pesteU se nume¸sterelat¸ie¸si se noteaz˘a cur.
r poate varia ˆın timp dar nu ¸si ˆın structur˘a.
Exemplu:
r={(v11, v12, . . . v1n),(v21, v22, . . . v2n), . . . ,(vm1, vm2, . . . vmn)}.
Structura relat¸iei se va nota cuR[U]undeR se nume¸stenumele relat¸ieiiarU este mult¸imea de atributecorespunz˘atoare.
Notat¸ii echivalenteR(U),R(A1, A2, . . . , An),R[A1, A2, . . . , An].
R[U]se mai nume¸ste ¸si schem˘a de relat¸ie.
5/ 37
Elemente ale modelului relat¸ional
ˆIn practic˘a, o relat¸ier poate fi reprezentat˘a printr-o matrice:
r:
A1 A2 . . . An
v11 v12 . . . v1n . . . . vm1 vm2 . . . vmn
unde(vi1, vi2, . . . , vin) este un uplu dinr, 1≤i≤m¸si vij ∈dom(Aj), 1≤j≤n,1≤i≤m
Vom nota cuti linia (tuplul) cu num˘arulidin matrice:
ti = (vi1, vi2, . . . , vin),∀i∈[1, m]
Elemente ale modelului relat¸ional
O mult¸ime finit˘aD de scheme de relat¸ie se nume¸steschem˘a de baze de date. Formal, D={R1[U1], . . . , Rh[Uh]} undeRi[Ui]este o schem˘a de relat¸ie, 1≤i≤h.
Obaz˘a de date peste Deste o corespondent¸˘a ce asociaz˘a fiec˘arei scheme de relat¸ie dinD o relat¸ie.
Exemplu:
r1, r2, . . . rh este o baz˘a de date peste D={R1[U1], . . . , Rh[Uh]}.
ConsiderˆandD ca fiind ordonat˘aD= (R1[U1], . . . , Rh[Uh]), putem nota baza de date sub forma(r1, r2, . . . rh)
7/ 37
Corespondent¸a cu terminologia din practic˘ a
I atribut (Ai) = denumirea unei coloane dintr-un tabel;
I valoarea atributului Ai (ϕ(Ai) sauvi) = valoarea dintr-o celul˘a a tabelului
I relat¸ie (r) = tabel
I schema de relat¸ie (R[U]) = schema tabelei I tuplu (ti) = linie din tabel
Operat¸ii
Asupra unei mult¸imi de relat¸ii putem efectua o serie de operat¸ii.
Exist˘a dou˘a categorii de operatori:
I Operatori din teoria mult¸imilor: Reuniunea(∪), Intersect¸ia (∩), Diferent¸a(−), Produsul Cartezian(×)
I Operatori specifici algebrei relat¸ionale: Proiect¸ia (π), Select¸ia(σ), Redenumirea(ρ), Joinul Natural(on), θ-Joinul, equijoinul, Semijoinul(n¸sio), Antijoinul(.), Divizarea(÷), Joinul la Stˆanga (./), Joinul la Dreapta(./), Joinul Exterior(./)
9/ 37
Operat¸ii pe mult¸imi de tuple - Reuniunea: ∪
ˆIn cazul operat¸iilor pe mult¸imi (cu except¸ia Produsului Cartezian), acestea se realizeaz˘a ˆıntre dou˘a relat¸iir1 ¸sir2 care sunt
NEAP˘ARAT construite peste aceea¸si mult¸ime de atribute.
Reuniunea a dou˘a relat¸iir1 ¸sir2, ambele peste o aceea¸si mult¸ime de atributeU (sau peste aceea¸si schem˘a de relat¸ie R[U]), este o relat¸ie notat˘a cu r1∪r2 definit˘a astfel:
r1∪r2={t|t=uplu, t∈r1 saut∈r2}
ˆIn practic˘a, acest lucru se realizeaz˘a utilizˆand cuvˆantul cheie UNION. Student¸ii din anii 1 ¸si 3 sunt selectat¸i de interogarea:
SELECT * FROM studenti WHERE an=1 UNION
SELECT * FROM studenti WHERE an=3;
Operat¸ii pe mult¸imi de tuple - Diferent¸a: −
Diferent¸a a dou˘a relat¸ii r1 ¸sir2, ambele peste o aceea¸si mult¸ime de atributeU (sau peste aceea¸si schem˘a de relat¸ieR[U]), este o relat¸ie notat˘a cu r1−r2 definit˘a astfel:
r1−r2 ={t|t=uplu, t∈r1si t6∈r2}
ˆIn practic˘a, acest lucru se realizeaz˘a utilizˆand cuvˆantul cheie MINUS. Pentru a-i selecta pe student¸ii din anul 2 f˘ar˘a burs˘a, putem s˘a ˆıi select˘am pe tot¸i student¸ii din anul 2 ¸si apoi s˘a ˆıi elimin˘am pe cei cu burs˘a:
SELECT * FROM studenti WHERE an=2 MINUS
SELECT * FROM studenti WHERE bursa IS NOT NULL;
11/ 37
Operat¸ii pe mult¸imi de tuple - Intersect¸ia: ∩
Intersect¸ia a dou˘a relat¸iir1 ¸sir2, ambele peste o aceea¸si mult¸ime de atributeU (sau peste aceea¸si schem˘a de relat¸ie R[U]), este o relat¸ie notat˘a cu r1∩r2 definit˘a astfel:
r1∩r2={t|t=uplu, t∈r1si t∈r2}
ˆIn practic˘a, acest lucru se realizeaz˘a utilizˆand cuvˆantul cheie INTERSECT. Putem afla care student¸i din anul 2 au burs˘a rulˆand:
SELECT * FROM studenti WHERE an=2 INTERSECT
SELECT * FROM studenti WHERE bursa IS NOT NULL;
Operatorul de intersect¸ie poate fi obt¸inut din ceilalt¸i doi:
r1∩r2=r1−(r1−r2).
Operat¸ii pe mult¸imi de tuple - Produsul Cartezian: ×
Produsul cartezian a dou˘a relat¸iir1 definit˘a pesteR1[U1]¸sir2 definit˘a peste R2[U2]cuU1∩U2=∅este o relat¸ie notat˘a cu r1×r2 definit˘a astfel:
r1×r2 ={t|t=uplu peste U1∪U2, t[U1]∈r1 ¸sit[U2]∈r2} De aceasta dat˘a, cele dou˘a relat¸ii nu trebuie s˘a fie peste aceea¸si mult¸ime de atribute. Rezultatul va fi o nou˘a relat¸ie peste o mult¸ime de atribute format˘a din atributele relat¸iilor init¸iale.
13/ 37
Operat¸ii pe mult¸imi de tuple - Produsul Cartezian: ×
Dac˘a un atribut s-ar repeta, el va fi identificat diferit. Spre exemplu, chiar dac˘a tabelele note ¸si cursuri au un acela¸si atribut (id curs), nu se face nici o sincronizare dup˘a acesta ci se vor crea dou˘a atribute diferite: note.id curs respectiv cursuri.id curs.
Produsul cartezian ˆıntre aceste tabele, ˆın practic˘a, se obt¸ine executˆand interogarea:
SELECT * FROM cursuri, note;
Operat¸ii specifice algebrei relat¸ionale
Operat¸iile pe mult¸imi aveau ca elemente tuplele. Uneori aceste tuple nu sunt compatibile (de exemplu nu putem reuni o relat¸ie pesteR1[U1]cu una pesteR2[U2]dac˘a U1 6=U2).
Pentru a opera asupra atributelor ce definesc tuplele din rezultat, avem nevoie de o serie de operatori specifici algebrei relat¸ionale.
15/ 37
Operat¸ii ˆın algebra relat¸ional˘ a - Proiect¸ia: π
Consider˘am:
I R[U]= schem˘a de relat¸ie;
I X ⊆U;
I t = uplu peste R[U](t∈r).
Se nume¸steproiect¸ia lui trelativ˘a laX ¸si notat˘a cuπX[t], restrict¸ia luit la mult¸imea de atributeX. (Uneori vom scrie t[X]) Exemplu:
Dac˘a U = (A1, A2, . . . , An) atuncit= (v1, v2, . . . , vn).
Consider˘amX = (Ai1, Ai2, . . . , Aik),1≤i1 < i2 < . . . < ik ≤n.
atunciπX[t] = (vi1, vi2, . . . , vik);
Operat¸ii ˆın algebra relat¸ional˘ a - Proiect¸ia: π
Dac˘a r este o relat¸ie pesteR[U]¸siX⊆U, atunci proiect¸ia luir relativ˘a laX este πX[r] ={πX[t]|t∈r}
Exemplu:
Dac˘a U = (A1, A2, . . . , An) atunci
r={(v11, v12, . . . v1n),(v21, v22, . . . v2n), . . . ,(vm1, vm2, . . . vmn)}.
Consider˘amX = (Ai1, Ai2, . . . , Aik),1≤i1 < i2 < . . . < ik ≤n.
atunci
πX[r] ={(v1i1, v1i2, . . . v1ik),(v2i1, . . . v2ik), . . . ,(vmi1, . . . vmik)}
ˆIn practic˘a, proiect¸ia se realizeaz˘a selectˆand doar anumite cˆampuri ale tabelei (anumite atribute):
SELECT nume, prenume FROM studenti;
17/ 37
Operat¸ii ˆın algebra relat¸ional˘ a - Proiect¸ia: π
Ca ¸si exemplu, vom scrie o interogare care s˘a returneze toate persoanele care trec pragul Facult˘at¸ii (student¸i ¸si profesori):
SELECT nume, prenume FROM studenti UNION
SELECT nume, prenume FROM profesori;
ˆIn cazul ˆın care cele dou˘a cˆampuri (nume, prenume) din cele dou˘a tabele au acela¸si tip (de exemplu nume este de tip
VARCHAR2(10) ˆın ambele tabele), interogarea va afi¸sa toate persoanele ce “trec pragul Facult˘at¸ii”.
Observat¸ie: Pentru a modifica tipul nume din tabela profesori la VARCHAR2(10) executat¸i comanda:
ALTER TABLE profesori MODIFY nume VARCHAR2(10);
Operat¸ii ˆın algebra relat¸ional˘ a - Select¸ia: σ
Fier o relat¸ie peste R[U],A, B ∈U ¸sic este o constant˘a Oexpresie elementar˘a de select¸ieeste definit˘a prin urm˘atoarea formul˘a (forma Backus-Naur):
e=A ϕ B |A ϕ c|c ϕ B Undeϕ este o relat¸ie boolean˘a ˆıntre operanzi.
Se nume¸steexpresie de select¸ie (forma Backus-Naur):
θ=e |θ∧θ|θ∨θ|(θ)
19/ 37
Operat¸ii ˆın algebra relat¸ional˘ a - Select¸ia: σ
Fieθ o expresie de select¸ie. Atunci:
I cˆandθ=A ϕ B,t satisfaceθdac˘a are locπA[t]ϕ πB[t], I cˆandθ=A ϕ c,t satisfaceθdac˘a are locπA[t]ϕ c, I cˆandθ=c ϕ B,t satisfaceθdac˘a are locc ϕ πB[t],
I cˆandθ=θ1∧θ2,t satisfaceθdac˘at satisface atˆat peθ1 cˆat
¸si pe θ2,
I cˆandθ=θ1∨θ2,t satisfaceθdac˘at satisface m˘acar pe unul dintreθ1 ¸siθ2.
Dac˘a θeste o expresie de select¸ie atunci select¸iase noteaz˘a cu σθ(r)¸si este definit˘a ca:
σθ(r) ={t|t∈r, tsatisfaceθ}
Operat¸ii ˆın algebra relat¸ional˘ a - Select¸ia: σ
ˆIn SQL, select¸ia se obt¸ine utilizˆand o formul˘a logic˘a ce are rolul de a selecta doar anumite rˆanduri.
Exemplu:
SELECT * FROM studenti
WHERE((an=2) AND (bursa IS NULL));
ˆIn acest exemplu,θ1 estean= 2,θ2 estebursa IS N U LL, θ=θ1∧θ2 ¸sir este mult¸imea de rˆanduri din tabela student¸i.
Rezultatul este mult¸imea student¸ilor din anul 2 care nu au burs˘a.
21/ 37
Operat¸ii ˆın algebra relat¸ional˘ a - Redenumirea: ρ
Operatorul deredenumire are rolul de a schimba numele unui atribut cu alt nume. Formal, dac˘a dorim s˘a schimb˘am atributulA1
ˆınA01 vom utiliza scriereaρA1/A0
1(r). Restul atributelor peste care a fost construitr vor r˘amˆane neschimbate.
ˆIn SQL, redenumirea se realizeaz˘a prin utilizarea cuvˆantului AS:
Exemplu:
SELECT bursa * 1.25 AS “BursaNoua” FROM studenti;
SELECT bursa + bursa/4 AS “BursaNoua” FROM studenti;
Dac˘a nu am redenumi atributul nou obt¸inut, cele dou˘a relat¸ii ar fi considerate diferite (ˆın prima numele atributului ar fi “bursa * 1.25”, iar ˆın a doua ar fi fost “bursa + bursa/4”) - ATENTIE cand introduceti exercitii.
Operat¸ii ˆın algebra relat¸ional˘ a - Join natural: o n
Consider˘am:
I r1 relat¸ie pesteR1[U1];
I r2 relat¸ie pesteR2[U2];
Se nume¸steJoin natural a relat¸iilorr1 ¸si r2, relat¸ia r1 onr2 peste U1∪U2 definit˘a prin:
r1 onr2 ={t|tuplu peste U1∪U2, t[Ui]∈ri, i= 1,2}
Dac˘a R este un nume pentru relat¸ia pesteU1∪U2 atuncir1onr2
este definit˘a peste R[U1∪U2]
Pentru simplitate vom notaU1∪U2 cuU1U2.
23/ 37
Operat¸ii ˆın algebra relat¸ional˘ a - Join natural: o n
Exemplu:
FieR1[A, B, C, D],R2[C, D, E]¸si r1, r2 a.i.:
r1 :
A B C D
0 1 0 0
1 1 0 0
0 0 1 0
1 1 0 1
0 1 0 1
r2:
C D E
1 1 0
1 1 1
0 0 0
1 0 0
1 0 1
Atunci: r1 onr2:
A B C D E
0 1 0 0 0
1 1 0 0 0
0 0 1 0 0
0 0 1 0 1
Operat¸ii ˆın algebra relat¸ional˘ a - Join natural: o n
Urm˘atoarea interogare identific˘a cui apart¸ine fiecare nota din tabelul note. Joinul se face dup˘a cˆampul nr matricol ˆıntre tabelele studenti ¸si note:
SELECT nume, valoare FROM studenti NATURAL JOIN note;
SELECT nume, valoare FROM studenti
JOIN note ON studenti.nr matricol = note.nr matricol;
Se poate observa c˘a dac˘a din produsul cartezian am elimina acele cazuri ˆın care cˆampul “nr matricol” nu este identic ˆın ambele tabele, am obt¸ine, de fapt, acela¸si rezultat. Din acest motiv, joinul de mai sus poate fi scris ¸si sub forma:
SELECT nume, valoare FROM studenti,note WHERE studenti.nr matricol = note.nr matricol;
25/ 37
Propriet˘ at¸i ale Joinului natural
I (r1 onr2)[U1]⊆r1 I (r2 onr1)[U2]⊆r2 Dac˘a X=U1∩U2 ¸si:
r01 ={t1|t1 ∈r1,∃t2 ∈r2 a.i. t1[X] =t2[X]}¸sir1” =r1−r01, r02 ={t2|t2 ∈r2,∃t1 ∈r1 a.i. t1[X] =t2[X]}¸sir2” =r2−r02, atunci: r1onr2 =r10 onr02,(r1onr2)[U1] =r10,(r2 onr1)[U2] =r02. Dac˘a r1 ⊆r1, r2 ⊆r2 ¸sir1 onr2=r1 onr2 atuncir01⊆r1 si r02 ⊆r2
Dac˘a U1∩U2 =∅ atuncir1onr2 =r1×r2.
Extindere Join natural
Fieri relat¸ie peste Ri[Ui], i= 1, h atunci:
r1 onr2 on. . .onrh ={t|t uplu peste U1, . . . Uh, a.ˆı. t[Ui]∈ri, i= 1, h}
Notat¸ii echivalente:
I r1onr2 on. . .onrh
I ./hri, i= 1, hi I ∗hri, i= 1, hi
Operat¸ia join este asociativ˘a.
27/ 37
Operat¸ii ˆın algebra relat¸ional˘ a - θ-join, equijoin
Fieri pesteRi[Ui],i= 1,2 cuAα1, Aα2, . . . Aαk ∈U1 ¸si Bβ1, Bβ2, . . . Bβk ∈U2 ¸si
θi :dom(Aαi)×dom(Bβi)→ {true, f alse},∀i= 1, k
θ-joinula dou˘a relat¸iir1 ¸sir2, notat cur1 ./θ r2, este definit prin:
r1 ./
θ r2 ={(t1, t2)|t1∈r1, t2 ∈r2, t1[Aαi]θit2[Bβi], i= 1, k}
undeθ= (Aα1θ1Bβ1)∧(Aα2θ2Bβ2)∧. . .∧(AαkθkBβk) Dacaθi este operatorul de egalitate, atunci θ-joinul se mai numeste siequijoin.
Operat¸ii ˆın algebra relat¸ional˘ a - θ-join, equijoin
Observat¸ie 1: un join oarecare cu condit¸ia TRUE pentru orice combinat¸ie de tuple este un produs cartezian: r1 ./
truer2=r1×r2
Observat¸ie 2: Joinul oarecare poate fi considerat ca fiind o filtrare dup˘a anumite criterii ale rezultatelor unui produs cartezian:
r1 ./θ r2 =σθ(r1×r2)
Exemplu SQL:
SELECT s.nume, p.nume FROM studenti s, profesori p WHERE s.nume> p.nume;
29/ 37
Operat¸ii ˆın algebra relat¸ional˘ a - Semijoin: n ¸si o
Operat¸ia desemijoin stˆangselecteaz˘a acele rˆanduri din relat¸ia aflat˘a ˆın partea stˆang˘a (n) care au corespondent (ˆın sensul joinului natural) ˆın relat¸ia din partea dreapta.
Formal, definim semijoinul stˆang a dou˘a relat¸iir1 pesteR1[U1]¸si r2 peste R2[U2]ca fiind:
r1nr2 =πU1(r1 onr2)
Deja ˆıntˆalnit la proprietat¸ile Joinului natural sub denumirear01. Semijoinul drept este definit similar dar preluˆand liniile din relat¸ia aflat˘a ˆın dreapta (doar cele ce au corespondent ˆın relat¸ia din stˆanga).
Operat¸ii ˆın algebra relat¸ional˘ a - Antijoin: .
Tuplele r˘amase din relat¸ia din stˆanga (care nu au fost preluate de semijoinul stˆang), formeaz˘a rezultatul operatorului Antijoin.
Formal, definim antijoinul stˆang a dou˘a relat¸iir1 pesteR1[U1]¸sir2
pesteR2[U2]ca fiind:
r1. r2=r1−πU1(r1onr2)
. . .r1”
31/ 37
Operat¸ii ˆın algebra relat¸ional˘ a - Joinul la Stˆ anga: ./
Fier1 ¸sir2 dou˘a relat¸ii ˆın care nu toate tuplele din r1 au un corespondent ˆınr2.
Operat¸iaJoin la Stanga a celor dou˘a relat¸iir1 ¸sir2 este reuniunea dintre tuplele existente ˆınr1onr2 ¸si tuplele dinr1 ce nu sunt utilizate ˆın join completate cu valoarea NULL pentru atributele din U2.
r1 ./ r2=r1onr2 UNIONπU1U2(r1−πU1(r1onr2)) Joinul la Dreapta este definit similar, de aceast˘a dat˘a preluˆand ¸si liniile ce nu s-au folosit in Joinul natural din tabela din dreapta (r2).
Operat¸ii ˆın algebra relat¸ional˘ a - Joinul Extern: ./
Operat¸ia de Join exterior cuprinde toate liniile din Joinul la Stˆanga
¸si din Joinul la Dreapta.
r1 ./ r2 = (r1 ./ r2)∪(r1./ r2)
33/ 37
Operat¸ii ˆın algebra relat¸ional˘ a - Joinul Extern: ./
Exemple:
SELECT * FROM studenti LEFT JOIN profesori ON studenti.prenume = profesori.prenume;
(Tot¸i student¸ii ¸si asociat¸i cu profesorii cu acela¸si prenume cˆand este cazul)
SELECT * FROM studenti RIGHT JOIN profesori ON studenti.prenume = profesori.prenume;
(Unii student¸i care sunt asociat¸i cu profesorii avˆand acela¸si prenume ˆımpreun˘a cu restul profesorilor)
SELECT * FROM studenti FULL JOIN profesori ON studenti.prenume = profesori.prenume;
(Student¸ii ¸si profesorii ¸si asocierile ˆıntre ei, dac˘a exist˘a)
Notat¸ii (alternative) pentru operatorii din alg. relat¸ional˘ a
Proiect¸ia (πU(r1)): r1[U] Join natural (r1 ./ r2): r1∗r2
Join oarecare (sau theta-join): r1 ./
θ r2
Select¸ia : σθ(r1)[obs: r1 ./θ r2 =σθ(r1×r2)]
Join la stˆanga: r1B◦CLr2
Join la dreapta: r1B◦CRr2
Full outer join : r1B◦Cr2
Redenumirea: Dac˘a r este definit peste B1, B2, . . . , Bn¸si vrem s˘a redenumim numele atributelor, vom folosi operatorul de
redenumireρ:r0 =ρ(r1)A1,A2,...,An - redenumirea atributelor luir ˆınA1, A2, . . . , An
35/ 37
Exercit¸ii:
1. Pentrur1,r2 exemplificate la Joinul natural, construit¸i restul tipurilor de Join studiate.
2. Utilizˆand schema de baze de date de la laborator, scriet¸i ˆın algebra relat¸ional˘a expresii de select¸ie pentru urm˘atoarele:
I Cursurile din facultate ˆımpreun˘a cu numele prof. ce le t¸in.
I Numele ¸si prenumele student¸ilor din anul 1 ¸si care au burs˘a mai mare de 300 ron.
I Prenumele student¸ilor care au acela¸si nume de familie ca m˘acar unul din profesori.
I Numele ¸si prenumele student¸ilor, cursurile pe care le-au urmat
¸si notele pe care le-au obt¸inut.
Scriet¸i interog˘arile SQL asociate formulelor din algebra relat¸ional˘a scrise mai sus.
Bibliografie
I Baze de date relat¸ionale. Dependent¸e - Victor Felea; Univ. Al.
I. Cuza, 1996
37/ 37
Software
I Relational