Vederi (Tabele virtuale)

Vederi (Tabele virtuale)

Tabelele unei baze de date definesc structura si organizarea datelor acesteia. SQL ne permite sa privim datele din baza de date si in alte moduri decat cele prezentate de tabele, prin utilizarea vederilor. O vedere este o interogare SQL stocata in baza de date si careia i se asociaza un nume. SQL permite accesul la rezultatele acestei interogari ca si cand aceasta ar fi o tabela reala. Vederile sunt o parte importanta a SQL-ului,    din cateva motive:

• Ofera utilizatorilor mai multe perspective asupra datelor din baza de date.
• Restrictioneaza accesul la date, astfel incat un utilizator va avea acces numai la anumite linii sau coloane dintr-o tabela.
• Simplifica accesul la baza de date prin faptul ca prezinta unui anumit utilizator datele din baza de date in modul cel mai natural pentru acesta.

O vedere este deci o tabela virtuala a bazei de date, al carei continut este definit pe baza unei interogari. Pentru utilizatorii bazei de date, vederea apare ca si o tabela reala, cu un set de nume de coloane si linii cu date. Dar, spre deosebire de o tabela reala, o vedere nu exista in baza de date ca o multime de valori stocate. Liniile si coloanele vizibile prin intermediul unei vederi sunt de fapt rezultatele interogarii care defineste vederea. SQL creeaza iluzia unei vederi prin faptul ca atribuie interogarii un nume, ca si in cazul unei tabele, si stocheaza definitia vederii in baza de date.

Figura 5.26 O vedere cu doua tabele sursa

Vederea din figura 5.26 este una tipica. A fost denumita REPDATA si este definita pe baza urmatoarei interogari:

Datele din vedere provin din tabelele SALESREPS si OFICES. Acestea sunt numite tabelele sursa ale vederii deoarece ele reprezinta sursa datelor care vor fi vizibile in vederea respectiva. Aceasta vedere contine cate o linie pentru fiecare agent de vanzari si cuprinde, in plus, orasul in care lucreaza si regiunea in care este situat acesta.

Dupa ce o vedere a fost definita, ea poate fi utilizata in instructiunea SELECT, ca o tabela reala.

Sa se afiseze agentii care si-au depasit cota de vanzari, precizand orasul si regiunea in care lucreaza.

Numele vederii, REPDATA, apare in clauza FROM la fel ca si numele unei tabele iar coloanele vederii sunt referite in instructiunea SELECT la fel ca si coloanele unei tabele reale. Pentru unele vederi putem utiliza, de asemenea, instructiunile INSERT, DELETE, si UPDATE pentru a modifica datele din vedere, la fel ca si in cazul tabelelor reale. Asadar, in practica, vederea poate fi utilizata in instructiunile SQL ca si o tabela reala.

Cand SGBD-ul intalneste o referinta la o vedere intr-o instructiune SQL, cauta definitia acesteia in baza de date apoi transforma cererea care face referire la vedere intr-una echivalenta dar care se refera la tabelele sursa.

Crearea unei vederi (CREATE VIEW

Pentru crearea unei vederi se utilizeaza instructiunea CREATE VIEW. Instructiunea atribuie un nume vederii si precizeaza interogarea care defineste vederea. Optional, instructiunea CREATE VIEW poate atribui un nume coloanelor vederii nou create. Daca este prezenta o lista cu nume de coloane, aceasta trebuie sa aiba acelasi numar de elemente ca si al coloanelor furnizate de interogare. Mentionam ca nu pot fi specificate decat numele coloanelor; tipul de data, dimensiunea sau alte caracteristici ale coloanelor sunt derivate din definitiile coloanelor din tabelele sursa. Daca lista de coloane este omisa, fiecare coloana din vedere va avea acelasi nume cu al coloanei din interogare. Numele coloanelor unei vederi trebuie specificate explicit doar in cazul in care interogarea cuprinde coloane calculate sau doua coloane cu acelasi nume, provenite din tabele sursa diferite.

Vederi orizontale

Una dintre utilizarile obisnuite ale vederilor este de a permite accesul utilizatorilor numai la unele dintre liniile unei tabele. De exemplu, dorim sa permitem unui manager sa vada numai acele linii din tabela SALESREPS care corespund agentilor din regiunea managerului respectiv.

Sa se creeze o vedere pentru agentii de vanzari din regiunea Eastern.

Sa se creeze o vedere pentru agentii de vanzari din regiunea Western.

Acum putem da fiecarui manager permisiunea de a accesa doar una dintre vederile EASTREPS sau WESTREPS; in acest mod fiecare manager va avea o vedere personalizata asupra tabelei SALESPEPS, avand acces doar la agentii din aceeasi regiune cu el.

Vederile de acelasi tip cu EASTREPS sau WESTREPS sunt numite vederi orizontale. Asa cum se observa si in Figura 5.27, o vedere orizontala imparte tabela sursa in portiuni orizontale. Doar anumite linii din tabela sursa sunt vizibile in vedere.

Figura 5.27 Vederi orizontale

In continuare sunt prezentate alte exemple de vederi orizontale.

Sa se defineasca o vedere care sa contina numai birourile din regiunea Eastern.

Sa se defineasca o vedere corespunzatoare agentului de vanzari Sue Smith (agentul cu numarul 102) care sa contina numai comenzile efectuate de clientii pentru care este chiar el reprezentant de vanzari.

Sa se defineasca o vedere care sa cuprinda numai acei clienti care au efectuat comenzi in valoare totala de cel putin $30.000,00.

In fiecare dintre aceste exemple, vederile deriva dintr-o singura tabela. Vederea este definita de o interogare SELECT * si, ca urmare, are exact aceleasi coloane ca si tabela sursa. Clauza WHERE determina care dintre liniile tabelei sursa sunt vizibile in vedere.

Vederi verticale

O alta utilizare frecventa a vederilor este pentru a permite accesul utilizatorilor doar la anumite coloane ale unei tabele. De exemplu, departamentul de procesare a comenzilor este suficient sa aiba acces numai la numarul angajatului, numele si biroul la care acesta lucreaza deoarece aceste informatii sunt suficiente pentru procesarea corecta a unei comenzi. Nu are nevoie sa cunoasca data angajarii sau cota de vanzari a unui angajat.

O vedere similara cu REPINFO este numita adesea vedere verticala. Asa cum se observa si in Figura 5.28, o vedere verticala imparte tabela sursa in portiuni verticale.

Figura 5.28 Vederi verticale

Sa se defineasca o vedere derivata din tabela OFFICES care sa cuprinda doar informatii despre numarul birourilor, orasul si regiunea in care sunt acestea situate.

Sa se defineasca o vedere derivata din tabela CUSTOMERS, care sa cuprinda numai numele clientului si reprezentantul de vanzari al acestuia.

In fiecare din aceste exemple, vederea este derivata dintr-o singura tabela sursa. Lista de selectie din definitia vederii precizeaza care dintre coloanele tabelei sursa sunt vizibile in vedere. Deoarece sunt vederi verticale, toate liniile din tabela sursa sunt reprezentate in vedere iar definitia vederii nu contine clauza WHERE.

Vederi oarecare

SQL nu restrictioneaza definitiile vederilor la cele pur orizontale sau verticale. De fapt, limbajul SQL nu cuprinde notiunile de vedere orizontala si vedere verticala. Aceste concepte, mai degraba, ne ajuta sa vizualizam cum sunt prezentate in vederi datele din tabelele sursa. Este un fapt destul de obisnuit definirea unei vederi care sa imparta tabela sursa atat in portiuni orizontale cat si verticale, ca in urmatorul exemplu.

Sa se defineasca o vedere care sa contina numarul clientului, numele companiei si limita creditului pentru toti clientii al caror reprezentant de vanzari este Bill Adams (angajatul cu numarul 105).

Datele care sunt vizibile in aceasta vedere reprezinta o submultime de linii si respectiv de coloane din tabela CUSTOMERS. Numai coloanele specificate explicit in lista de selectie si liniile care satisfac conditia de selectie vor fi vizibile in vedere.

Vederi la nivel de grup

Interogarea specificata in definitia unei vederi poate include clauza GROUP BY. Un astfel de tip de vedere poarta numele de vedere la nivel de grup. Vederile de grup realizeaza aceleasi functii ca si interogarile la nivel de grup; grupeaza liniile si furnizeaza ca rezultat al interogarii cate o singura linie, pentru fiecare grup.

Sa se defineasca o vedere care sa contina informatii sintetizatoare despre comenzile primite de fiecare agent de vanzari.

Asa cum se observa din acest exemplu, definitia unei vederi la nivel de grup contine intotdeauna o lista cu numele coloanelor. Lista atribuie nume coloanelor din vedere, coloane care sunt derivate din functii de agregare (la nivel de grup) cum sunt SUM( ), MIN( ) etc. De asemenea, se poate atribui unei coloane din vedere un alt nume decat cel pe care il are aceasta in tabela de baza. In acest exemplu, coloana REP din tabela ORDERS a devenit WHO in vederea ORD_BY_REP.

Dupa ce a fost definita, o vedere la nivel de grup poate fi utilizata pentru a simplifica interogarile. De exemplu, urmatoarea interogare genereaza un raport care sintetizeaza informatiile despre comenzile primite de fiecare agent de vanzari.

Sa se afiseze numele, numarul de comenzi, valoarea totala a comenzilor precum si media valorilor comenzilor primite de catre fiecare agent de vanzari.

Spre deosebire de vederile orizontale sau de cele verticale, in cazul vederilor la nivel de grup nu exista o corespondenta unu-la-unu intre liniile din vedere si cele din tabela sursa. O vedere la nivel de grup nu este doar un filtru asupra tabelei sursa, care sa afiseze doar anumite linii sau coloane ale acesteia; ea sintetizeaza informatiile din tabela sursa si din aceasta cauza SGBD-ul depune un efort substantial pentru procesarea interogarii din definitia vederii si pentru a da iluzia unei tabele.

Vederile la nivel de grup pot fi utilizate in interogari dar nu pot fi actualizate. Spunem ca vederile la nivel de grup sunt read-only.

Reamintim ca functiile de agregare imbricate, ca de exemplu MIN(MIN(A)), nu sunt permise in SQL. Desi vederile la nivel de grup ascund functiile de agregare din lista de selectie, SGBD-ul stie de existenta acestora si impiedica incalcarea acestei restrictii. Sa consideram urmatorul exemplu:

Pentru fiecare birou de vanzari, sa se arate intervalul in care sunt cuprinse vanzarile medii ale angajatilor care lucreaza la biroul respectiv

Aceasta interogare produce o eroare, desi pare corecta. Este o interogare a doua tabele, care grupeaza liniile din vederea ORD_BY_REP dupa codul biroului de vanzari. Dar functiile de agregare MIN( ) si MAX( ) din lista de selectie determina aparitia problemei. Argumentul acestor functii, coloana AVERAGE, este ea insasi rezultatul unei functii de agregare. Interogarea care va fi procesata de SQL este, de fapt, urmatoarea:

Aceasta interogare este incorecta din cauza prezentei de doua ori a clauzei GROUP BY si a functiilor de agregare.

Vederi de tip join

Una dintre cele mai uzuale utilizari ale vederilor este pentru simplificarea interogarilor. Prin specificarea, in definitia vederii, a unei interogari care se refera la doua sau trei tabele putem crea o vedere de tip join, care culege date din doua sau mai multe tabele diferite si prezinta rezultatele interogarii sub forma unei tabele virtuale. Dupa ce a fost definita, putem utilizata o interogare simpla asupra vederii , ca in cazul unei tabele reale, in locul unei interogari asupra a doua sau mai multor tabele.

De exemplu, sa presupunem ca Sam Clark, vicepresedinte de vanzari, interogheaza adesea tabela ORDERS. Totusi, el nu doreste sa lucreze cu id-urile agentilor de vanzari sau ale clientilor ci cu numele acestora.

Sa se creeze o vedere similara tabelei ORDERS dar care sa utilizeze nume in locul id-urilor.

Vederea este definita pe baza unei interogari care implica trei tabele. Ca si in cazul vederilor la nivel de grup, procesarile efectuate de SGBD pentru a crea iluzia unei tabele, sunt considerabile. Fiecare linie din vedere este derivata din combinarea a trei linii, provenind - cate una - din tabelele ORDERS, CUSTOMERS si SALESREPS. Desi are o definitie relativ complicata, aceasta vedere furnizeaza avantaje considerabile. In continuare, este prezentata o interogare care genereaza un raport referitor la situatia comenzilor pe agenti de vanzari.

Sa se afiseze totalul comenzilor primite de fiecare agent de vanzari

Observam ca aceasta interogare se refera la o singura tabela si este considerabil mai simpla decat instructiunea SELECT din definitia vederii.

Sa se genereze un raport cu privire la comenzile mai mari de $20.000,00 , aratand clientii care le-au efectuat si agentii de vanzari care le-au primit.

Aceste interogari sunt mult mai usor de exprimat prin utilizarea vederilor decat daca am utiliza o interogare asupra tuturor celor trei tabele.

Actualizarea vederilor

Ce inseamna adaugarea unei linii intr-o vedere, stergerea unei linii dintr-o vedre sau modificarea unei linii deja existente intr-o vedere? Pentru unele vedrei aceste operatii pot fi transformate usor in operatii echivalente asupra tabelei sau tabelelor sursa. De exemplu, sa consideram iarasi vederea EASTREPS, definita anterior in acest paragraf.

Sa se creeze o vedere corespunzatoare agentilor de vanzari din regiunea EASTERN.

Aceasta este o vedere orizontala simpla, derivata dintr-o singura tabela sursa. Asa cum se arata in Figura 5.29, are sens sa vorbim despre adaugarea unei linii in aceasta vedere; inseamna ca noua linie va fi adaugata in tabela SALESREPS, din care deriva vederea. De asemenea, are sens sa vorbim despre stergerea unei linii din vederea EASTREPS; acest lucru va determina stergerea liniei corespunzatoare din tabela SALESREPS. In sfarsit, are sens modificarea unei linii din vederea EASTREPS; aceasta inseamna modificarea liniei corespunzatoare din tabela SALESREPS. In fiecare caz, actiunea efectuata asupra vederii poate fi transformata intr-o actiune similara efectuata asupra tabelei sursa deoarece se pastreaza atat integritatea tabelei sursa cat si a vederii.

Figura 5.29 Actualizarea datelor prin intermediul unei vederi

Sa consideram acum vederea la nivel de grup ORDS_BY_REP, definita anterior in acest paragraf.

Sa se defineasca o vedere care sa contina informatii sintetizatoare despre comenzile primite de fiecare agent de vanzari.

In acest caz nu exista o corespondenta unu-la-unu intre liniile vederii si liniile tabelei ORDERS, de aceea nu are sens sa vorbim despre adaugarea, stergerea sau modificarea unei linii a acestei vederi. Vederea ORD_BY_REP nu este actualizabila; este o vedere read_only.

Vederile EASTREPS si ORD_BY_REP sunt doua exemple extreme din punct de vedere al complexitatii definitiei. Exista vederi mult mai complexe decat vederea EASTREPS in care au sens operatiile de actualizare, si exista vederi mult mai simple decat ORD_BY_REP in cazul carora aceste operatii nu au sens. De fapt, problema stabilirii tipurilor de vederi care pot fi actualizate si a celor care nu pot fi actualizate este, de multi ani, un important subiect de cercetare in domeniul bazelor de date relationale.

Vederile actualizabile si standardul ANSI/ISO

Standardul ANSI/ISO precizeaza ca o vedere este actualizabila daca satisface urmatoarele conditii:

Nu trebuie sa contina clauza DISTINCT; liniile duplicat nu trebuie eliminate din rezultatul interogarii.

Clauza FROM trebuie sa contina o singura tabela; asadar vederea trebuie sa aiba o singura tabela sursa. Daca tabela sursa este, la randul ei, o vedere atunci fiecare element din lista de selectie trebuie sa fie o referinta la o coloana; lista de selectie nu poate contine expresii, campuri calculate sau functii de agregare.

Clauza WHERE nu trebuie sa includa subinterogari.

Interogarea nu trebuie sa cuprinda clauzele GROUP BY sau HAVING.

Conceptul care sta la baza acestor restrictii este mai usor de memorat decat regulile: pentru ca o vedere sa fie actualizabila, SGBD-ul trebuie sa poata identifica, pentru fiecare linie din vedere, linia corespunzatoare din tabela sursa. Similar, SGBD-ul trebuie sa poata stabili, pentru fiecare coloana din vedere, coloana corespunzatoare din tabela sursa. Daca o vedre indeplineste aceste criterii, atunci putem defini pentru aceasta operatii de adaugare, stergere sau actualizare.

Vederi actualizabile si clauza CHECK

Daca o vedere este definita pe baza unei interogari care contine clauza WHERE, numai acele linii din tabela sursa care satisfac conditia se selectie vor fi vizibile in vedere. De exemplu, vederea EASTREPS, definita anterior in acest paragraf, contine numai acele linii din tabela SALEREPS care au anumite valori pentru coloana REP_OFFICE.

Sa se creeze o vedere corespunzatoare agentilor de vanzari din regiunea EASTERN.

Aceasta este o vedere actualizabila. Putem adauga un nou agent de vanzari, cu o instructiune INSERT:

SGBD-ul va adauga noua linie in tabela SALESREPS, iar aceasta linie va fi vizibila in vederea EASTREPS. Dar sa vedem ce se va intampla cand adaugam o noua linie in vederea EASTREPS, cu aceasta instructiune INSERT:

Aceasta este o instructiune SQL corecta, si SGBD-ul va adauga o noua linie in tabela SALESREPS. Totusi, linia nou adaugata nu indeplineste conditia de selectie din clauza WHERE a definitiei vederii. Valoarea coloanei REP_OFFICE (21) corespunde biroului din Los Angeles, care este situat in regiunea Western. Ca urmare, linia nou adaugata nu apare in vedere.

Acelasi lucru se intampla si daca modificam biroul asignat unui agent de vanzari care apare in vedere.

Aceasta instructiune UPDATE modifica una dintre coloanele corespunzatoare liniei lui Bob Smith si determina disparitia acestei linii din vedere.

De fapt, eliminarea unor linii din vedere, ca rezultat al operatiilor INSERT sau UPDATE, poate produce confuzii. Am vrea ca SGBD-ul sa detecteze si sa previna aparitia acestui tip de operatii INSERT sau UPDATE. SQL ne permite sa specificam acest tip de restrictii asupra vederilor, prin includerea in definitia vederii a unei clauze check. Clauza check este specificata in instructiunea CREATE VIEW, ca in urmatorul exemplu:

Cand clauza check este inclusa in definitia unei vederi, SQL verifica automat fiecare operatie    INSERT sau UPDATE referitoare la vederea respectiva pentru a se asigura ca liniile respective indeplinesc conditiile de selectie din definitia vederii. Daca una dintre liniile adaugate sau modificate nu satisface aceste conditii, instructiunea INSERT sau UPDATE esueaza.

Standardul SQL2 permite adaugarea unei optiuni suplimentare la clauza check: CASCADED sau LOCAL. Aceasta optiune se aplica la crearea unei vederi, cand vederea respectiva este definita nu pe baza unei tabele de baza ci pe baza uneia sau mai multor vederi definite anterior. Acestea, la randul lor, ar putea fi definite pe baza altor vederi, s.a.m.d. fiecare dintre vederile in cauza pot sau nu sa aiba specificata clauza check in definitie. Daca noua vedere este creata cu optiunea WITH CASCADED CHECK OPTION, orice incercare de actualizare a acesteia va determina SGBD-ul sa parcurga intreaga ierarhie a definitiilor vederilor pe care se bazeaza si sa proceseze clauza check pentru fiecare dintre ele. Daca vederea noua este creata cu optiunea WITH LOCAL CHECK OPTION, atunci SGBD-UL verifica numai aceasta vedere.

Standardul SQL2 precizeaza ca optiune implicita optiunea CASCADED.

Stergerea unei vederi (DROP VIEW)

Standardul SQL2 ofera suport pentru stergerea unei vederi, prin intermediul instructiunii DROP VIEW. De asemenea, furnizeaza suport pentru controlul stergerii unei vederi de a carei definitie depind alte vederi. De exemplu, sa consideram doua vederi derivate din tabela SALESREPS:

Vederea NYREPS este definita pe baza vederii EASTREPS. Conform standardului SQL2, urmatoarea instructiune DROP VIEW va sterge ambele vederi din baza de date:

Optiunea CASCADE ii spune SGBD-ului sa stearga nu numai vederea in cauza ci si toate vederile care depind de aceasta.

Instructiunea DROP VIEW urmatoare

va esua, deoarece clauza RESTRICT in spune SGBD-ului sa stearga vederea numai daca nu exista alte vederi care sa depinda de ea.

Standardul SQL2 cere ca una dintre cele doua optiuni RESTRICT sau CASCADE sa fie prezenta in instructiunea DROP VIEW dar multe dintre produsele comerciale SQL suporta o versiune a instructiunii DROP VIEW fara vreo optiune explicita. In acest caz,    comportamentul implicit al instructiunii DROP VIEW depinde de fiecare SGBD in parte.