Capitolul 3: Interogarea bazelor de dateLimbaje de interogare Algebra relationala si calculul relational Operatiile pe multimi ale algebrei relationale Reuniunea Intersectia Diferenta Produsul Cartesian Selectia Proiectia Jonctiunea Diviziunea

Operatiile speciale ale algebrei relationale

Interogarea bazelor de date Interogarea intr-o singura relatie Interogarea in doua sau mai multe relatii

Prof. Felicia Ionescu

Cap. 3 - Interogarea bazelor de date

1

Limbaje de interogareInterogarea (query): operaia prin care se obin informatiile dorite dintr-o baz de date, selectate conform unui anumit criteriu (condiie); Limbaje de interogare: abstracte si concrete (reale - implementari in diferite SGBD-uri) Limbaje de interogare abstracte: algebra relationala si calculul relational Algebra relationala (relational algebra) - const dintr-o mulime de operaii care au ca operanzi relaii, iar rezultatul este tot o relaie Calculul relaional (relational calculus) - bazat pe calculul predicatelor exprim o interogare definind rezultatul dorit ca expresie de calcul relaional Calculul relational al tuplurilor foloseste variabile de tuplu (variabile definite pe mulimea tuplurilor unei relaii) Calculul relational al domeniilor foloseste variabile de domeniu (variabile definite pe domenii de definiie ale atributelor) Cele trei limbaje formale sunt echivalente din punct de vedere al interogarilor Limbajele de interogare reale sunt definite pe baza unuia sau altuia din limbajele de interogare abstracte, sau pe o combinaie a acestora. De exemplu, limbajul SQL2 este n cea mai mare parte bazat pe algebra relaional, dar mai conine i construcii derivate din calculul relaional.Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 2

Algebra relationalaAlgebra relaional (relational algebra) - interogrile sunt expresii compuse din operatii care au ca operanzi relatii si rezultatul este o relatie Operatiile algebrei relationale: operatii pe multimi si operatii speciale, la care se adauga operatia de redenumire (rename) a atributelor (E.Codd) Operatiile relationale pe multimi acioneaz asupra relaiilor vzute ca mulimi (de tupluri), fr a lua n consideraie compoziia fiecrui tuplu; acestea sunt: Reuniunea Intersecia Diferena Produsul cartezian

Operaiile relaionale speciale iau n consideraie compoziia tuplurilor, formate din valori ale atributelor relaiilor; acestea sunt: Restricia Proiecia Jonciunea Diviziunea

Proprietatea de nchidere: operanzii (unul sau doi operanzi) sunt relatii, rezultatul este o relaie; aceast proprietate permite operaii imbricate: proiecia unei jonciuni etc.Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 3

Operatia de ReuniuneReuniunea (union) a dou relaii compatibile r(R) i s(S): q = r s = { t | t r or t s} Pentru ca r si s sa fie compatibile, trebuie ca: r si s sa aiba acelasi grad (acelasi numar de atribute) Atributele corespondente (in ordine pozitionala) sa fie compatibile

Tuplurile care aparin ambelor relaii se introduc n relaia rezultat o singur dat (nu se duplic) Relatia rezultat are un numar de tupluri (cardinalitatea) mai mic sau egal cu suma numerelor de tupluri ale celor doi operanzi Exemplu: A B 1 2 1 rProf. Felicia Ionescu Cap. 3 - Interogarea bazelor de date

A

B 2 3 s

A

B 1 2 1 34

rs

Operatiile de Intersectie si DiferentaIntersectia (set-intersection) a dou relaii compatibile r(R) i s(S): q = r s = { t | t r and t s} Exemplu:A B A B A B 1 2 1 s 2 3 2

rs

r Diferenta (set-difference) a dou relaii compatibile r(R) i s(S): q = r - s = { t | t r and t s} Exemplu: A 1 2 1 B A s B 2 3 A B 1 1

r-s r Reuniunea si intersectia sunt comutative si asociative (r s = s r ; r (s t) = (r s) t); diferenta nu este nici nici comutativa, nici asociativaProf. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 5

Operatia de Produs CartesianProdusul cartesian (Cartesian-Product) a dou relaii r(R) i s(S): q = r x s = { tp | t r and p s}, Q = R S Se presupune ca multimile R si S sunt disjuncte, adica R S = Daca atributele din R si S nu sunt disjuncte, atunci (unele): se pot redenumi (RENAME nume_atribut AS noul_nume_atribut) sau se pot califica cu numele relatiei careia ii apartin (folosind operatorul punct)

Tuplurile relaiei rezultat se obtin prin concatenarea valorilor atributelor fiecrui tuplu din prima relaie cu valorile atributelor tuturor tuplurilor din a doua relaie Relatia rezultata are numarul de tupluri (cardinalitatea) egal cu produsul numarului de tupluri ale relatiilor operand A B C D E Exemplu: C D E A B 1 10 a 10 a 1 10 a 1 10 a 1 20 b 2 20 b 1 10 b r 10 b 2 10 a 2 10 a s 2 20 b 2 10 b rxs Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de 6date

Exprimarea operatiilor pe multimi in SQL (1)Reuniunea:SELECT lista_coloane1 FROM tabel1 [WHERE condiie1] UNION SELECT lista_coloane2 FROM tabel2 [WHERE condiie2];

Exemplu (MySQL, Intreprindere):SELECT Nume, Prenume, Adresa FROM FURNIZORI UNION SELECT Nume, Prenume, Adresa FROM CLIENTI; Afiseaza numele tuturor furnizorilor si al clientilor, precum si adresa acestora

Intersectia:SELECT lista_coloane1 FROM tabel1 [WHERE condiie1] INTERSECT SELECT lista_coloane2 FROM tabel2 [WHERE condiie2];

Diferenta:SELECT lista_coloane1 FROM tabel1 [WHERE condiie1] MINUS SELECT lista_coloane2 FROM tabel2 [WHERE condiie2];

Prof. Felicia Ionescu

Cap. 3 - Interogarea bazelor de date

7

Exprimarea operatiilor pe multimi in SQL (2)Produsul Cartesian:SELECT * from R, S; SELECT lista_col_R, lista_col_S from R, S; Exemplu (MySQL, Intreprindere): SELECT * FROM ANGAJATI, SECTII; SELECT IdAngajat, Nume, Prenume, DataNasterii, Adresa, Functia, Salariu, ANGAJATI.IdSectie, SECTII.IdSectie, Denumire, Buget FROM ANGAJATI, SECTII;

Produsul Cartesian este implementat in toate SGBD-urile (instructiunea SQL SELECT) In sistemul Oracle sunt implementate toate operatiile pe multimi In alte SGBD-uri nu sunt implementate toate operaiile pe mulimi; in SQL Server 2000 si in MySQL 5.0 nu sunt implementate operaiile INTERSECT i MINUSProf. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 8

Operatia de SelectieSelectia (sau restrictia select, restriction) intr-o relatie r(R) - definita astfel:

p(r) = {t | t r and p(t)}unde p, predicatul selectiei, este o formula de calcul propozitional compusa din termeni conectati prin operatorii and (), or (v) not ()

Fiecare termen este de forma: op op sau , unde

op este un operator de comparatie: =, , >, . 10000000;Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 9

Operatia de ProiectieProiectia (project) pe atributele A1, A2, .. Ak intr-o relatie r(R) se noteaz: A1, A2, Ak (r), unde A1 R, A2 R, Ak R Rezultatul este o relatie cu k atribute, cele din lista data Daca {A1, A2, Ak} nu contine o supercheie, pot sa apara tupluri duplicat; teoretic, tuplurile duplicat se elimina, dat fiind ca rezultatul este o multime Exemplu: A B 10 20 30 40 C 1 1 1 2 A C 1 1 1 2 = A C 1 1 2

r

A,C (r)

In limbajul SQL proiectia se exprima astfel:SELECT DISTINCT A1, A2, Ak FROM R Daca nu se introduce parametrul DISTINCT, nu se elimina tuplurile duplicat

Exemplu (MySQL, WORLD):SELECT DISTINCT CountryCode FROM City;Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 10

Operatia de jonctiune naturala (1)Jonctiunea naturala (natural join) combina tuplurile din doua relatii Fie multimile de atribute: A = {A1,A2,...Am} , B= {B1,B2,...Bn}, C={C1,C2,Ck} si doua relatii r(R) si s(S), unde: R ={A, B}, S = {B, C} deci atributele R S = B = {B1, B2,...Bn} sunt comune celor dou relaii

Jonciunea natural este o relatie q = r

s, care se obine n felul urmtor:

se calculeaz produsul cartesian al celor doua relatii: p = r x s, P = {A, R.B, S.B, C}; din tuplurile produsului cartesian se selecteza acele tupluri care au valori egale pentru atributele comune (B1, B2,...Bn): R.B = S.B, adic R.B1=S.B1, R.B2=S.B2,.. se face proiectia rezultatului pe multimea de atribute R S = {A, B, C}

Schema relatiei rezultat este Q = R S = {A, B, C} q=r s = A,B,C (r.B1=s.B1 AND r.B2=s.B2 AND r.Bn = s.Bn) (r x s) Atributele comune R.B si S.B trebuie s fie compatibile in cele doua relatii; daca sunt compatibile, ele se considera identice chiar dac au denumiri diferite, si n reuniunea atributelor R S se introduc o singur dat Cazul cel mai frecvent de jonctiune naturala: intre doua relatii asociate (1:N), atributul comun fiind cheia straina cheia primara (candidata) referitaProf. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 11

Operatia de jonctiune naturala (2)Exemplul 1: rA B 1 2 4 1 2 C s = A,B,C,D,E (r.D = s.D ) (r x s) D a a b a b D a b c d e E A B 1 2 4 1 2 C D E

a a b a b r s s r In SQL trebuie sa fie introduse explicit lista atributelor rezultatului si conditiile de egalitate ale atributelor comune:

SELECT A,B,C,R.D,E FROM R, S WHERE R.D = S.D;

Exemplul 2: ANGAJATI

SECTII; cheia straina: ANGAJATI.IdSectie

SELECT IdAngajat, ANGAJATI.Nume, Prenume, DataNasterii, Adresa, Salariu, ANGAJATI.IdSectie, SECTII.Nume, Buget FROM ANGAJATI, SECTII WHERE ANGAJATI.IdSectie=SECTII.IdSectie;

Exemplul 3:(MySQL-WORLD)city

country; cheia straina: city.countryCode

SELECT ID, city.Name Oras, CountryCode 'Cod Tara', city.Population, country.Name Tara, Continent, . from city, country where city.countryCode=country.CODE order by country.Name; Daca nu se afiseaza toate atributele jonciunii, nseamna ca s-a combinat cu o proiecieProf. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 12

Jonciuni interne i externeJonciunea natural se mai numete i jonciune intern i se mai poate exprima in SQL cu cuvintele cheie INNER JOIN Exemplu de jonciune (combinat cu o proiecie si o selectie)(MySQL world)SELECT city.Name Oras, Code 'Cod Tara', country.Name Tara, Continent FROM city INNER JOIN country ON CountryCode=Code WHERE Continent='Antarctica' OR Continent = 'Europe' ORDER BY Continent;

Jonciunea extern introduce n plus toate liniile care exit n prima relaie (pentru LEFT OUTER JOIN) sau n cea de-a doua relaie (pentru RIGHT OUTER JOIN) i pentru care nu exist linii n ceallalt relaie care s ndeplineasc condiia de join; exemplu:SELECT city.Name Oras, Code 'Cod Tara', country.Name Tara, Continent FROM city RIGHT OUTER JOIN country ON CountryCode=Code WHERE Continent='Antarctica' OR Continent = 'Europe' ORDER BY Continent; Se vor afia si rile care nu au nici un oras nscris n tabelul city.

Prof. Felicia Ionescu

Cap. 3 - Interogarea bazelor de date

13

Operaia de diviziuneFie relaiile r(R) si s(S), unde: R = {A, B} unde A={A1,A2,..Am}, B={ B1,B2,..Bn} S = {B}

Relaia q = r s are schema Q = R S = {A} si: r s = { t | t R-S (r) u s ( tu r ) }unde tu inseamna concatenarea tuplurilor t si u

n limbajul SQL, diviziunea se exprim printr-o instruciune SELECT, introducnd explicit toate conditiile impuse valorilor atributelor Exemplu: A B A B

rProf. Felicia Ionescu

1 2 3 1 2 1 3

1 2 s

rs

Cap. 3 - Interogarea bazelor de date

14

Concluzii: operatiile algebrei relationaleAlgebra relaional este o colecie de operaii asupra relaiilor Cele opt operaii propuse de E.F.Codd nu constituie o mulime minim de operaii ale algebrei relaionale Mulimea minim de operaii ale algebrei relaionale consta din cinci operaii primitive, pe baza crora se poate construi orice expresie de algebra relaionala: Reuniunea Diferena Produsul Cartesian Restricia (selectia) Proiecia

Celelalte operaii se pot exprima prin intermediul acestora:

Intersecia se poate exprima prin expresia: R S = R (R S); Jonciunea este o proiecie a unei restricii a produsului cartezian al relaiilor; Diviziunea este o proiecie a unei restricii asupra relaiei demprit

Si celelalte trei operaii sunt deosebit de utile n formularea interogrilor, astfel nct algebra relaional a pstrat toate cele opt operaii propuse de E.F.Codd, la care s-a adugat operaia de redenumire a atributelorProf. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 15

Formularea interogarilorInterogarea este operatia prin care se obtin informaiile dorite (care indeplinesc o anumita conditie) dintr-o baz de date. O interogare: se formuleaza mai intai n limbaj natural, apoi se exprima ntr-un limbaj abstract de interogare (algebra relaional sau calculul relaional), se transpune n limbajul de interogare al SGBD-ului folosit (ex., limbajul SQL), iar aplicatia client transmite SGBD-ului instructiunea (sau instructiunile) obtinute

Sistemul SGBD prelucreaza programul interogarii n mai multe faze: analiza lexical, sintactic i semantic optimizarea interogarii generarea codului executia si returnarea rezultatului

n algebra relaional o interogare se formuleaz printr-o expresie care defineste urmtoarele elemente: Lista atributelor relaiei rezultat, care se numesc atribute de proiecie; Lista relaiilor din care se extrag informaiile Condiiile pe care trebuie s le ndeplineasc tuplurile relaiei rezultat.

Sunt posibile dou situaii: interogri care se rezolv n cadrul unei singure relaii interogri care se rezolv folosind dou sau mai multe relaii ale bazei de dateProf. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 16

Interogri ntr-o singur relaieInterogare in relatia r(R): Expresia de algebra relationala: q = lista_atribute p(r) Instructiunea SQL: SELECT lista_atribute FROM R WHERE p = TRUE;

Exemplul 1: Fie relaia ANGAJATI i interogarea: Care sunt numele i prenumele angajailor care au un salariu mai mare sau egal cu 2000?. Expresia de algebr relaional: q = Nume, Prenume Salariu >= 2000 (ANGAJATI) Instruciunea SQL: SELECT Nume, Prenume FROM ANGAJATI WHERE Salariu >= 2000;

Exemplul 2: (MySQL - WORLD): Care sunt numele si populatia oraselor din tara cu codul ROM ? Expresia de algebr relaional: q = Name, Population country_id=ROM (city) Instructiunea SQL: SELECT Name, Population FROM city WHERE CountryCode=ROM';

Exemplul 3: Fie relaia ANGAJATI i interogarea: Care sunt numele, prenumele si adresa angajailor care lucreaza in sectia numarul 1?. Expresia de algebr relaional: q = Nume, Prenume, Adresa IdSectie = 1 (ANGAJATI) Instructiunea SQL: SELECT Nume, Prenume, Adresa FROM ANGAJATI WHERE IdSectie=1;Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 17

Interogri n dou sau mai multe relaiiDaca atributele de proiecie i atributele din condiia de interogare nu aparin unei singure relaii, pentru rezolvarea interogrii trebuie s fie folosite toate acele relaiile care, mpreun, conin atributele i asocierile necesare Conceptual, o astfel de interogare se rezolv astfel: se construieste mai nti o relaie care s conin toate atributele implicate prin combinarea relaiilor necesare, folosind operaii de produs cartezian sau jonciuni; in relatia obtinuta se aplica o selectie (restrictie) (cu condiia de interogare p); apoi se face proiecia (pe atributele de proiecie).

Expresia generala de algebra relationala a interogarii este: q = lista_atribute p(r x s x t...) Daca intre relatiile din produsul cartesian exista atribute comune care trebuie sa aiba valori egale (de regula, perechile cheie strin - cheie candidata) atunci se pot face operaii de jonciune: s t...) q = lista_atribute p AND conditii-join(r

O interogare poate conine una sau mai multe subinterogriIn limbajul SQL, o interogare se exprima prin instructiuni SELECT in care:clauza WHERE combina atat conditiile impuse valorilor atributelor cat si conditiile de jonctiuni Jonctiunile se pot specifica i n clauza FROM (cu INNER JOIN, OUTER JOIN)Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 18

Exemplu: interogare n dou relaiiFie interogarea: Care sunt numele, prenumele, funcia, salariul i denumirea seciei n care lucreaz angajaii? Expresia de algebr relaional este:q = Nume, Prenume,Salariul, Denumire (ANGAJATI SECTII)

Instructiunea SQL corespunzatoare acestei interogri:SELECT Nume, Prenume, Salariul, Denumire FROM ANGAJATI, SECTII WHERE SECTII.IdSectie = ANGAJATI.IdSectie

Se efectueaza o navigare n baza de date, pe atributul comun (IdSectie)ANGAJATIIdAngajat Nume Prenume DataNasterii Adresa Salariul IdSectie

SECTIIBuget Denumire IdSectie

Fie interogarea: Care sunt numele, prenumele, funcia i salariul angajailor care lucreaz n secia cu denumirea Productie?q = Nume, Prenume, Functia, Salariul Denumire= Productie (ANGAJATI SECTII)SELECT Nume, Prenume, Functia, Salariul FROM ANGAJATI, SECTII WHERE SECTII.IdSectie = ANGAJATI.IdSectie AND Denumire = Productie; Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 19

Exemplu: interogare in trei relatii (1)Fie urmatoarele relatii asociate din baza de date SAKILA (MySQL): FILM (film_id, title, description, release_year, ....) ACTOR (actor_id, first_name, last_name, last_update) FILM_ACTOR (film_id, actor_id, last_update)FILM 1 N FILM_ACTOR N 1 ACTOR

Interogarea: n ce filme au jucat fiecare din actorii din baza de date sakila ? film_actor actor) q = actor_id, first_name, last_name, title (film Instructiunea SQL:SELECT ACTOR.actor_id, first_name, last_name, title FROM FILM, FILM_ACTOR, ACTOR WHERE FILM.film_id = FILM_ACTOR.film_id AND ACTOR.actor_id = FILM_ACTOR.actor_id ORDER BY ACTOR.actor_id;

Se poate folosi i sintaxa INNER JOIN:SELECT ACTOR.actor_id, first_name, last_name, title FROM (FILM INNER JOIN FILM_ACTOR ON FILM.film_id=FILM_ACTOR.film_id) INNER JOIN ACTOR ON ACTOR.actor_id = FILM_ACTOR.actor_id ORDER BY.... ;Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 20

Exemplu: interogare in trei relatii (2)

Prof. Felicia Ionescu

Cap. 3 - Interogarea bazelor de date

21

Exemplu: interogare in trei relatii (3)

Prof. Felicia Ionescu

Cap. 3 - Interogarea bazelor de date

22

SubinterogriSubinterogrile sunt operaii care determin diferite date (valori scalare, tabele rezultat, numr de elemete etc.) folosite n interogarea de baz Subinterogrile pot conine la rndul lor alte subinterogri Exemplul 1: Care sunt angajaii (nume, prenume, adresa) care lucreaz n aceeai secie cu angajatul cu numele Ionescu i prenumele Ion?Se determin printr-o subinterogare n ce secie lucreaz angajaul dat Se selecteaz toi angajaii din acea secie:SELECT Nume, Prenume, Adresa FROM ANGAJATI WHERE IdSectie = (SELECT IdSectie FROM ANGAJATI WHERE Nume = 'Ionescu' AND Prenume = 'Ion');

Exemplul 2: Care sunt numele, prenumele, denumirea seciei i salariul angajailor care au salariul egal cu salariul maxim pe una din secii:Se determin printr-o subinterogare tabelul cu valori maxime ale salariului n fiecare secie Se selectez angajaii care au salariul n mulimea salariilor maxime pe sectii:SELECT Nume, Prenume, Salariul, Denumire FROM ANGAJATI INNER JOIN SECTII ON ANGAJATI.IdSectie=Sectii.IdSectie WHERE Salariul IN (SELECT MAX(Salariul) FROM ANGAJATI Group By IdSectie);Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date 23

Prof. Felicia Ionescu

Cap. 3 - Interogarea bazelor de date

24