1

SISTEME DE GESTIUNE A BAZELOR DE DATE

1: SISTEME DE GESTIUNE A BAZELOR DE DATE RELA łIONALE

1.1. Defini Ńii, terminologie ApariŃia şi dezvoltarea calculatoarelor electronice au condus la amplificarea

activităŃilor legate de stocarea, interogarea şi administrarea colecŃiilor de date. Astăzi, cele mai multe dintre activităŃile noastre zilnice necesită accesarea şi actualizarea informaŃiei dintr-o bază de date: extragerea unei sume de bani din contul bancar, rezervarea unei camere de hotel, cumpărarea unui bilet de avion, împrumutarea unei cărŃi de la bibliotecă, plătirea facturilor de telefon, curent electric etc. Toate acestea se pot face rapid şi în siguranŃă pentru că datele respective sunt bine organizate într-o bază de date şi administrate de un sistem de gestiune a bazelor de date .

Bază de date (BD) = o colecŃie de date aflate în relaŃie unele cu altele şi structurată astfel încât să poată servi unui anumit scop = un set de date corelate şi organizate în scopul prelucrării lor rapide şi concomitente de către mai multe persoane. Exemple 1) baza de date a unui muzeu, în care sunt înregistrate operele de artă (grupate după tip, autor, tehnică de lucru) şi expoziŃiile itinerante (descrise prin perioadă, itinerariu, responsabil, custozi participanŃi); 2) baza de date a unui magazin de muzică, în care sunt înregistrate albumele de muzică în funcŃie tipul de suport fizic (CD, casetă etc.), stil, autori, solişti, anul apariŃiei etc. Observa Ńie Termenul "Database" (bază de date, în limba engleză) a apărut pentru prima dată în titlul unei conferinŃe organizate la Santa Monica, SUA, în 1964: ”Development and Management of Computer Centered DataBase”.

Figura 1: Structura simplificată a unei baze de date Sistem de gestiune a bazelor de date (SGBD) = un ansamblu de programe care permit crearea şi administrarea unei baze de date. Prin urmare, un SGBD (Database

Sistem de gestiune a bazei de date

Baza de date

Programe şi aplicatii Utilizatori

2

Management System) este un pachet software de nivel înalt care permite proiectarea, construirea şi administrarea bazelor de date dedicate rezolvării problemelor din cele mai variate domenii ale vieŃii reale. Exemple IMS, DB2 (până la DB9, de la IBM), Ingres II (de la Computer Associates International Inc.), Oracle 8i (de la Oracle Corporation), Ms Access (studiat în clasa a X-a), FoxPro (de la Microsoft), Paradox , Visual dBase (de la Borland), Sybase Adapted Server (de la Sybase Inc.), IRIS (de la Hewlett-Packard). Aten Ńie Nu orice colecŃie de date este o bază de date. De exemplu, lista cărŃilor dintr-o bibliotecă NU este o bază de date ci un simplu inventar de obiecte, o list ă, un tabel . Prin urmare, faŃă de un inventar (un tabel), o bază de date are următoarele proprietăŃi: • reprezintă un anumit aspect al lumii reale, numit microuniversul bazei de date; orice modificare care se produce in acest microunivers se reflectă în baza de date (de exemplu: cumpărarea unei noi casete în vederea inchirierii, modificarea diferenŃei permise între cursul de cumpărare şi cel de vânzare al valutei etc.); • este o colecŃie de date coerentă din punct de vedere logic şi având un înŃeles intrinsec (de exemplu: din baza de date asociată bibliotecii liceului nu vor face parte cărŃile de telefon sau lista de materiale didactice din laboratorul de chimie); • este proiectată, construită şi populată cu date având permanent în vedere un anumit scop; o bază de date este destinată utilizării de către un anumit grup de persoane şi permite efectuarea unui anumit set de operaŃii.

Un SGBD foloseste in principiu trei limbaje: un limbaj de descriere al datelor fizice, un limbaj de descriere al datelor logice si un limbaj de prelucrare al datelor. Aceste limbaje pot fi de sine statatoare sau grefate pe un limbaj de programare general (de exemplu, C, COBOL, PL/I etc.).

Arhitectura bazelor de date evidentiaza componentele acestora: • baza de date propriu-zisa in care se memoreaza datele; • sistemul de gestiune a bazei de date, care realizeaza gestiunea si prelucrarea complexa a datelor; • un dictionar al bazei de date (metabaza de date ),ce contine informatii despre date, despre structura acestora, statistici, documentatie; • mijloace hard utilizate (comune sau specializate); • reguli administrative destinate bunei functionari a intregului sistem. • personalul implicat (utilizatori finali, administrator, programatori, operatori).

Dintre cerintele care se impun unei baze de date remarcam : • sa furnizeze in timp util informatiile solicitate (timp de raspuns la o interogare ); • sa asigure costuri minime deprelucrare si intretinere, redundanta minima; • sa aiba capacitatea de a satisface cu aceleasi date, necesitatiile informationale, ale unui numar mare de utilizatori, sa permita adaptarea la cerinte noi, raspunsuri la interogari neprevazute initial (flexibilitate ); • sa permita exploatarea simultana a datelor de catre mai multi utilizatori (sincronizare); • sa asigure securitatea datelor prin mecanisme de protectie impotriva accesului neautorizat (confidentialitate); • sa contina facilitati destinate validari datelor si recuperarii lor in cazul unor deteriorari accidentale (integritate); • sa permita valorificarea eforturilor anterioare si anticiparea nevoilor viitoare (compatibilitate si expandabilitate); • sa permita, prin ierarhizarea datelor dupa criteriul frecventei acceselor, reorganizari (eventual dinamice) care sporesc performantele bazei.

3

Cuvântul “dată” este de origine latină şi provine de la verbul a da. În limba engleză, substantivul dată (date , la plural) se traduce prin datum (data , la plural). Exemple de date sunt: cantităŃile de mere obŃinute anual într-o livadă de pomi fructiferi, activităŃile turistice propuse de ghid participanŃilor la o excursie; modificările climatice suferite de o regiune a globului terestru de-a lungul unui număr de ani, cursul bancar al unei valute de-a lungul unei luni sau a unui an calendaristic etc.

Există o diferenŃă esenŃială între date, informaŃii şi cunoştinŃe: 1. datele sunt informaŃii primare – care au fost doar culese şi inregistrate; 2. informa Ńiile sunt date prelucrate, structurate (validate, corectate, organizate,

sortate, relaŃionate); 3. cuno ştin Ńele sunt informaŃii contextualizate. Arhitectura sistemelor de gestiune a bazelor de date este puternic determinată de modelul de date al bazelor de date. Dincolo de definiŃiile date până acum, ce este de fapt o bază de date? Este un obiect (asemenea numerelor, funcŃiilor, mulŃimilor)? Este o metodă (asemenea algoritmilor, procedurilor)?

O bază de date este în primul rând un model al microuniversului la care se referă. Model = (în sens strict) un sistem teoretic sau material cu ajutorul căruia pot fi studiate indirect proprietăŃile şi transformările unui alt sistem, mai complex, cu care primul sistem prezintă o analogie; = (în sens larg) ceea ce poate servi ca orientare pentru reproduceri (un tipar). (cf. DEX) O bază de date oferă un anumit grad de abstractizare a datelor (asemenea celor mai multe limbaje de programare), ascunzând detaliile de implementare, detalii care nu sunt necesare celor mai mulŃi dintre utilizatori. Cu alte cuvinte, programele specifice unei baze de date nu depind de modul de stocare şi accesare a datelor la nivel fizic. Acest concept se numeşte independen Ńă a datelor, se realizează cu ajutorul unui model de date ( Data Model 1) şi este principalul mecanism care asigură partajarea datelor din baza de date între diferitele aplicaŃii care le accesează. Model de date = un ansamblu format din: 1) o colec Ńie de concepte necesare pentru descrierea structurii bazei de date (a tipurilor de date incluse în baza de date, a relaŃiilor dintre ele şi a restricŃiilor (Constraints) pe care trebuie să le respecte); 2) un set de opera Ńii de baz ă (care să specifice modul de efectuare a extragerii şi actualizării datelor din baza de date).

1.2. Modele de date: perspectiv ă istoric ă EvoluŃia modelelor de date pentru bazele de date şi SGBD-uri a fost sugestiv

sintetizată de R.G.G. Canttell în articolul său "What Are Next-Generation DB Systems?", publicat în revista Communications of the ACM, în octombrie 1991: "Istoria informaticii a cunoscut multe generaŃii de sisteme de gestiune a datelor, începând cu sistemele de fişiere indexate, continuând apoi cu sistemele de tip ierarhic şi de tip reŃea, iar – mai nou – cu sistemele relaŃionale. Acum suntem pe punctul de a intra într-o nouă generaŃie de sisteme de gestiune a bazelor de date care oferă administrare de obiecte, şi care acceptă tipuri de date mult mai complexe".

Cu toate că a generat o activitate de cercetare foarte susŃinută dar şi o activitate practică, industrială extrem de productivă, domeniul bazelor de date este unul dintre cele mai tinere domenii ale informaticii. Este general acceptat faptul că "rădăcinile" sale trebuie căutate aproximativ acum 40 de ani2 în obiectivul fixat de Preşedintele J.F. Kennedy pentru programul Apollo: aducerea primului om pe Lună până la sfârşitul anilor '60. In acel moment nu exista nici un instrument informatic care să funcŃioneze efectiv şi care să poată administra uriaşele volume de date implicate în programul 1 E.F. Codd este considerat a fi "părintele" conceptului de model de date, în general, şi al conceptului de model de date relaŃional, în particular. 2 Data apariŃiei primului sistem comercial de gestiune a bazelor de date

4

spaŃial. Ca urmare, North American Aviation (NAA) primul contractor al proiectului, a dezvoltat un software bazat pe o structură ierarhică (părŃile se agregă în componente din ce în ce mai ample) denumit GUAM (Generalized Update Access Method ). Spre mijlocul anilor '60, IBM s-a alăturat NAA dezvoltând în continuare GUAM şi producând unul dintre primele sisteme comerciale de gestiune a bazelor de date: IMS (Information Management System ). IBM a preluat modelul ierarhic pentru a respecta cerinŃa de stocare a datelor pe benzi magnetice (deci în acces secvenŃial). Ulterior, această restricŃie a fost înlăturată şi IMS continuă să fie principalul SGBD ieirarhic utilizat de majoritatea calculatoarelor mainframe3.

Construirea bazelor de date a cunoscut o evoluŃie foarte rapidă, trecând prin mai multe abordări, clasificate după cum urmează: • sistemele de fişiere; • sistemele prerelaŃionale (sau "istorice"4): ierarhic şi reŃea, • sistemul relaŃional; • sistemele postrelaŃionale: orientat obiect şi hibrid (obiect-relaŃional); • sistemele semantice: multi-dimensional şi logic (deductiv).

1.2.1. Modelele prerela Ńionale Pot fi caracterizate ca modele de moment: au oferit soluŃii pentru problemele

vremii lor dar nu au avut un fundament teoretic puternic şi riguros. (I.) Sistemul de gestiune bazat pe fi şiere , considerat de fapt un predecesor al sistemelor de gestiune a bazelor de date, este o colecŃie de programe care realizează – fiecare – câte "un serviciu" pentru utilizatorii datelor (de obicei: generarea de rapoarte). Fiecare program îşi definea şi îşi administra propriile date. Chiar dacă a avut numeroase dezavanataje (abordarea descentralizată în stocarea informaŃiilor, gradul mare de redundanŃă şi dependenŃă program-date), sistemul de gestiune bazat pe fişiere a constituit un salt semnificativ faŃă de fişierele administrate manual: saltul de la abordarea informaŃională la cea informatică. (a) (b)

Figura 1: Modele de baze de date: (a) ierarhic; (b) reŃea (II.) Atât în modelul ierarhic cât şi în modelul re Ńea, datele erau reprezentate ca mulŃimi de inregistrări (în sensul limbajului de programare Pascal: colecŃii de date de diferite tipuri: Integer, Boolean, Real etc.). RelaŃiile dintre ele erau reprezentate prin

3 Un calculator mainframe este un calculator cu capacitate de memorie şi viteză de lucru foarte mari, utilizat de marile corporaŃii pentru a stoca volume foarte mari de date şi pentru a coordona sute sau mii de terminale (inclusiv calculatoare personale) conectate la el. Operarea unui mainframe necesită de obicei un personal specializat. 4 numite şi navigante sau tradiŃionale (legacy systems)

5

legaturi de tip pointer (adrese de locaŃii fizice de memorie). Inregistrările care formau baza de date erau organizate: • în modelul ierarhic: ca o mulŃime de arbori; • în modelul reŃea: ca o mulŃime de grafuri.

Ambele modele prerelaŃionale permiteau accesul la date de-a lungul unor drumuri (căi) predefinite, explicit stabilite la nivelul programelor de aplicaŃii (de unde şi numele de modele navigante). Ca urmare, orice modificare a structurii bazei de date antrena modificarea acestor căi în programele deja scrise. Exemple: pentru modelul ierarhic: IMS (amintit mai sus); pentru modelul reŃea: IDS II (de la Honeywell), IMAGE (de la Hewlett Packard).

Aplica Ńie: Modelarea activit ăŃii didactice Intr-o facultate, cadrele didactice desfăşoară activităŃi didactice de curs sau examen; aceste activităŃi sunt pentru studenŃi şi se desfăşoară în locaŃii (amfiteatre sau laboratoare). De asemenea, cadrele didactice participă la proiecte de cercetare ştiinŃifică. Figura 2 prezintă modelul ierarhic al bazei de date; Figura 3 prezintă modelul reŃea. Figura 2: Modelul ierarhic

Figura 3: Modelul reŃea

Proiect Cadru didactic

Predare

Facultate

Student Cadru didactic Activitate didactică LocaŃie

Curs Examen Proiect

Examen

Inscriere

Desfăşurare

Facultate

Student

Activitate didactică

LocaŃie

Curs

6

1.2.2. Modelul rela Ńional Considerată drept cel mai important eveniment din istoria bazelor de date,

apariŃia modelului relaŃional s-a produs în iunie 1970, odată cu publicarea – în revista Communications of the ACM – a articolului fundamental al lui Edgar Frank Codd5 (de la IBM Research Laboratory): "A Relational Model of Data for Large Shared Databanks". În acest articol, autorul aplica o serie de concepte din algebra relaŃională pentru a rezolva problemele legate de stocarea volumelor mari de date şi enunŃa "celebrele" 12 reguli (condiŃii) pe care trebuie să le îndeplinească un SGBD pentru a fi declarat relaŃional.

Să amintim însă existenŃa unui precursor: modelul bazat pe teoria mulŃimilor, propus de D.L. Childs în articolul său: "Feasability of a Set-Theoretical Data Structure", apărut în 1968 în Proc. Fall Joint Computer Conference.

Cele mai importante prototipuri de sisteme de gestiune a bazelor de date de tip relaŃional au fost: • System R , dezvoltat la San Jose Research Laboratory din California spre sfârşitul anilor '70. Acest model a condus la:

o apariŃia unui limbaj structurat de interogare a bazelor de date: SQL, o producerea mai multor SGBD-uri relaŃionale comerciale: DB2 şi SQL/DS de la

IBM şi, respectiv, ORACLE de la Oracle Corporation (în deceniul 9 al secolului trecut);

• INGRES (Interactive Graphics Retrival System ), dezvoltat la Universitatea Berkeley din California; • Peterlee Relational Test Vehicle , dezvoltat la IBM UK Centre din Peterlee, Marea Britanie.

Numărul sistemelor relaŃionale comerciale a ajuns acum la câteva sute, dintre care cele mai cunoscute sunt: DB2 (de la IBM), Ingres II (de la Computer Associates International Inc.), Oracle 8i (de la Oracle Corporation), Ms Access, FoxPro (de la Microsoft), Paradox , Visual dBase (de la Borland), Sybase Adapted Server (de la Sybase Inc.). Succesul acestui model continuă să fie atât de mare încât multe sisteme nerelaŃionale oferă acum şi o interfaŃă cu utilizatorii de tip relaŃional, indiferent de modelul de date pe care se bazează de fapt.

Modelul relaŃional s-a dovedit a fi şi un instrument didactic ideal de prezentare a principiilor bazelor de date, tocmai datorită fundamentării sale riguroase pe principii logice şi matematice.

Ce este de fapt un model relaŃional de date? Informal, îl putem defini ca un model în care: • datele sunt percepute de utilizatori ca nişte tabele şi numai ca nişte tabele; • operaŃiile disponibile pentru utilizatori (spre exemplu, pentru obŃinerea informaŃiilor) sunt operaŃii care generează noi tabele pe baza tabelelor vechi: operaŃia de selecŃie (SELECT) extrage o submulŃime de rânduri dintr-o tabelă, operaŃia de proiecŃie (PROJECT) extrage o submulŃime de coloane, operaŃia de juxtapunere (JOIN) asociază două tabele pe baza valorilor identice pe care le conŃin în anumite coloane, de asemenea identice; ori, toate aceste submulŃimi rezultate pot fi privite şi ele însele ca nişte tabele.

5 E.F. Codd s-a născut la 23 august 1923 în Portland, Marea Britanie, şi a murit în 18 aprilie 2003, în Florida. A făcut studii de matematică şi chimie la Oxford şi s-a mutat în Statele Unite în 1948, pentru a lucra la IBM. A introdus termenul OLAP (OnLine Analytical Processing ) şi a impus modelul relaŃional; a avut, de asemenea, contribuŃii în domeniul modelelor de calculabilitate prin lucrările sale privind automatele celulare. A obŃinut de două ori Premiul Turing: în 1981 şi 1994.

7

(a) (b) (c)

Figura 4: OperaŃii cu tabele: (a) selecŃie; (b) proiecŃie, (c) asociere

Numele modelului (model rela Ńional ) provine de la conceptul matematic de rela Ńie. Aşa cum o funcŃie f : {1, 2,…,n} ⊂ N →→→→ R are mai multe reprezentări convenŃionale, dintre care cea mai comodă este cea de vector, tot astfel relaŃia poate avea mai multe reprezentări, una dintre ele fiind tabela. Din acest motiv, cel pu Ńin la nivel informal, termenii de rela Ńie şi tabel ă pot fi considera Ńi sinonimi.

Principalele concepte cu care lucrează modelul de date de tip relaŃional sunt (exemplificarea se face pentru baza de date asociată unui liceu): • entitatea (Profesori, Elevi, PersonalAuxiliar, Clase etc.), • rela Ńia dintre entit ăŃi (PredăLa, AreDirigintePe, AreLocIn etc.), • atributul (Nume, GradDidactic, DataNaşterii, NrLocuri, LocaŃie etc.).

1.2.3. Modelele postrela Ńionale Chiar dacă se regăseşte în descrierea unor situaŃii reale, cu o organizare

intrinsec piramidală, modelul ierarhic şi-a atins rapid limitele. La fel, modelul relaŃional a devenit impropriu pentru rezolvarea unor probleme din realitatea înconjurătoare care presupun manipularea unor volume uriaşe de informaŃie, a unei mari varietăŃi de tipuri de date: hărŃi meteorologice sau geografice necesare previziunilor meteorologice sau dirijării traficului, imagini transmise prin satelit utilizate în măsurarea factorilor poluanŃi, date neconvenŃionale pentru proiectarea asistată de calculator în inginerie sau arhitectură, serii dinamice implicate în tranzacŃiile bursiere sau bancare, stocarea obiectelor binare mari (BLOBs = Binary Large Objects) necesare în digitalizarea informaŃiei conŃinută în fişierele audio sau video. Au apărut astfel şi s-au dezvoltat modelele postrelaŃionale, de generaŃia a treia: modelul orientat obiect şi modelul obiect-relaŃional. (I.) Modelul orientat obiect permite inglobarea semanticii obiectelor celor mai variate, la fel ca în limbajele de programare orientate-obiect. De altfel, una dintre deosebirile majore faŃă de modelul relaŃional constă în distanŃarea de conceptul de independen Ńă faŃă de limbajele de programare şi dezvoltarea conceptului de integrare a limbajelor de programare în sistemul de gestiune a bazei de date (invocarea unor funcŃii C++ mai degrabă decât inglobarea unui limbaj special pentru interogarea datelor, ca de exemplu SQL). Acest fapt a fost determinat de: • utilizarea aproape exclusivă a limbajelor de programare orientate obiect pentru dezvoltarea aplicaŃiilor software; • includerea în aproape orice aplicaŃie software a unei baze de date ca element fundamental al acesteia.

Cele mai cunoscute prototipuri de baze de date orintate obiect sunt: OPENOODB (de la Texas Instruments), IRIS (de la Hewlett Packard), iar ca variantă comercială: GEMSTONE/OPAL (de la GemStone Systems), VERSANT (de la Versant Object Technology). Deşi cu o cotă de piaŃă semnificativ inferioară sistemului relaŃional (150 milioane dolari faŃă de 10 miliarde, numai în SUA în anul 1999), modelul orientat obiect este creditat cu o creştere anuală extrem de rapidă: 50%. În ciuda caracterului intuitiv şi a altor avantaje evidente ale modelului orientat obiect, modelul relaŃional continuă să domine piaŃa sistemelor de gestiune a bazelor de date. Motivele sunt numeroase: fundamentarea matematică riguroasă, simplitatea, volumul mare de date deja stocate după acest model şi costul enorm al migrării spre un model complet diferit.

8

(II) Modelul hibrid extinde modelul relaŃional oferind un set de tipuri de date mai bogat, şi include şi orientarea obiect. Se incearcă astfel combinarea avantajelor celor două abordări: cea relaŃională şi cea orientată obiect. Astfel, atributele şi instanŃele entităŃilor pot avea tipuri complexe şi pot evita unele dintre restricŃiile specifice modelului relaŃional. De exemplu, în timp ce în modelul relaŃional fiecare atribut trebuie să ia pentru fiecare instanŃă a unei entităŃi o valoare şi numai una din domeniul lui de definiŃie, în modelul hibrid poate lua un subset de valori (de exemplu: pentru un angajat oarecare, atributul Telefon poate lua ca valori numărul telefonului fix de acasă şi de la servicu, al telefonului mobil propriu şi de serviciu, dacă angajatul dispune de toate patru).

Cel mai cunoscut exemplu: Informix Universal Server care combină tehnologiile relaŃionale şi orientate obiect din două produse preexistente: Informix şi Illustra .

Principalele avantaje şi dezavantaje ale modelelor de date (şi ale sistemelor de gestiune a bazelor de date corespunzătoare) au fost sintetizate de M. Stonebraker prin diagrama din Figura 7 (vezi [18]): modelul relaŃional permite realizarea – chiar simultană – a unor interogări variate şi rapide dar complexitatea datelor stocate nu diferă prea mult de complexitatea datelor memorate în baze de date de tip ierarhic sau reŃea; cu modelul orientat obiect se poate stoca informaŃie variată şi complexă (de la texte la sunete şi imagini) dar viteza de interogare (în cazul imaginilor şi mai ales al sunetelor) este foarte scăzută; modelul care pare să elimine toate dezavantajele şi să cumuleze toate avantajele modelelor anterioare este modelul obiect-relaŃional.

Figura 7: Clasificarea Michael Stonebraker pentru sistemele de gestiune a bazelor de date

SGBD relaŃionale SGBD hibride

SGBD prerelaŃionale SGBD orientate obiect

FacilităŃi de interogare / asistenŃa multi-user

Complexitatea datelor / posibile extinderi

9

1.3. Arhitectura SGBD Datele din baza de date pot fi descrise pe trei nivele: extern, conceptual şi intern:

Figura 5: Arhitectura ANSI6-SPARC7 pe 3 nivele pentru bazele de date Nivelul extern reprezintă modul în care utilizatorul percepe datele. Intrucât anumite părŃi din baza de date sunt relevante pentru unii utilizatori dar irelevante pentru alŃii putem spune că o bază de date are atâtea nivele externe câŃi utilizatori o accesează. Mai mult, există entităŃi care deşi sînt reprezentate în baza de date nu apar la acest nivel deoarece sînt irelevante pentru anumiŃi utilizatori. Nivelul intern (nivelul fizic) reprezintă modul în care SGDB-ul şi sistemul de operare percep datele. La acest nivel:

• este descrisă reprezentarea fizică a bazei de date în calculator (sunt specificate: spaŃiul de stocare a datelor, modul de stocare a acestora, structurile de date, organizarea fişierelor etc.);

• sunt utilizate funcŃii ale sistemului de operare pentru plasarea datelor pe dispozitivele de stocare, pentru construirea indecşilor, pentru citirea datelor etc.;

Nivelul conceptual (nivelul logic) realizează trecerea de la nivelul intern la nivelul extern şi asigură independenŃa acestora. Acest nivel:

• grupează percepŃiile tuturor utilizatorilor bazei de date (deoarece conŃine fiecare viziune (view) din nivelul extern, direct sau indirect);

• conŃine structura logică a bazei de date descrisă prin conceptele de entitate, atribut şi relaŃie, constrângeri referitoare la date, informaŃii de securitate şi integritate;

6 ANSI = American National Standards Institute 7 SPARC = Standards Planning and Requirements Committee

Nivelul extern : imaginea fiecărui utilizator asupra BD

Nivelul conceptual (structura logică a BD): ansamblul datelor stocate în BD şi a relaŃiilor dintre ele (fără detalii de implementare)

Nivelul intern :

implementarea fizică a BD (structuri de date, indexare, acces)

Organizarea fizică a datelor, coordonată de SGBD şi sistemul de operare

Schema externă 2

Schema externă 3

Schema externă 1

Schema conceptuală

Baza

de date

Schema internă

10

• descrie datele stocate în baza de date şi relaŃiile dintre ele dar nu conŃine detalii referitoare modul de stocare a datelor pe suportul fizic (numărului de octeŃi ocupaŃi pe disc etc.).

Scopul arhitecturii pe cele trei nivele este acela de separare a percepŃiei fiecărui utilizator individual aspura datelor de modul de reprezentare fizică a acestora în baza de date. Figura 2 ilustrează acest lucru reprezentând nivelul intern, nivelul conceptual şi două vederi corespunzătoare la nivelul extern: una aparŃinând unui utilizator de PL/I, cealaltă aparŃinând unui utilizator de COBOL.

Nivel extern (PL/I) Nivel extern (COBOL)

DCL 1 ANG, 2 ANG# CHAR(6), 2 SAL FIXED BIN(31);

01 ANGAJ. 02 CODANGAJ PIC X(6). 02 CODDEPT PIC X(6).

Nivel conceptual ANGAJAT COD_ANGAJAT CHARACTER (6) COD_DEPARTAMENT CHARACTER (4) SALARIU NUMERIC (5) Nivel intern STORED__ANG BYTES=20 PREFIX TYPE=BYTE(6), OFFSET=0 ANG# TYPE=BYTE(6), OFFSET=6, INDEX=ANGX DEPT# TYPE=BYTE(4), OFFSET=12 SALAR TYPE=FULLWORD, OFFSET=16

Figura 6: Exemplu de arhitectură pe 3 niveluri

Observa Ńie În timp ce nivelele extern şi conceptual trebuie să urmeze acelaşi model (relaŃional, orientat-obiect etc.), nivelul intern nu are nimic de a face cu modelul de date al bazei - el constând din înregistrări memorate, pointeri, indecşi etc.

Intr-o baza de date sunt necesare trei nivele de independenta a datelor: • independenta fizica: asigura posibilitatea modificarii schemei fizice a datelor fara ca aceasta sa oblige la modificarea schemei conceptuale, schemei logice si a programelor de aplicatie. • independenta logica: asigura posibilitatea modificarii schemei conceptuale a datelor fara ca aceasta sa oblige la modificarea schemei logice si a programelor de aplicatie. • independenta fata de strategiile de acces: permite programului sa precizeze data pe care doreste sa o acceseze, dar nu modul cum acceseaza aceasta data. SGBD va stabili drumul optim de acces la date.

11

Figura 7: Modelarea datelor şi arhitectura ANSI-SPARC

1.4. Modelul conceptual al bazelor de date relatio anle

A. Prima etap ă a model ării conceptuale Aşa cum am văzut în paragraful anterior, proiectarea unei baze de date incepe

cu analiza situaŃiei reale care trebuie modelată prin baza de date. Această analiză necesită un dialog între proiectantul bazei de date şi viitorii ei utilizatori. Astfel, sunt puse în evidentă: • cerin Ńele utilizatorilor privind datele care trebuie stocate şi administrate; • cerin Ńele utilizatorilor privind opera Ńiile care trebuie efectuate cu aceste date.

Etapa următoare constă în realizarea modelului conceptual al bazei de date. În cazul modelului relaŃional, se începe cu o descriere detailată a entitităŃilor şi atributelor, a relaŃiilor dintre entităŃi şi a condiŃiilor pe care trebuie să le îndeplinească. Această descriere poate îmbraca mai multe forme schema conceptual ă, diagrama entitate-rela Ńie (diagrama ER ).

B. Entit ăŃi, atribute, cheie primar ă Conform DicŃionarului Explicativ al Limbii Române: o entitate este un conŃinut de

sine stătător, o existenŃă determinată (ca întindere, importanŃă, valoare etc.). In literatura dedicată bazelor de date există mai multe definiŃii pentru acest termen: Defini Ńii Entitate = un "lucru" sau un "obiect" din lumea reală care poate fi distins (deosebit) de toate celelalte lucruri sau obiecte = un obiect (precum o rachetă, un tablou), un eveniment (precum naşterea unei persoane, marcarea unui gol), o activitate (producŃia de oŃel a unei uzine, închirierea unei maşini) din lumea reală care poate fi descris(ă) prin caracteristici bine definite (despre care există date care pot fi stocate). Defini Ńii

Schema externă

Stocare fizică

Schema conceptuală

Schema internă

Proiectarea la nivel fizic

Proiectarea la nivel logic / conceptual

12

Entitate şi Instan Ńă Prin entitate înŃelegem mulŃimea tuturor elementelor de un anumit tip (care prezintă aceleaşi caracteristici distinctive). Prin instan Ńă a unei entităŃi înŃelegem un singur element, bine individualizat, unic, din mulŃimea elementelor care formează entitatea respectivă. Observa Ńie • EntităŃile dintr-o bază de date pot fi disjuncte sau nu; în al doilea caz avem de a face cu subentit ăŃi (de exemlu, entităŃile PiloŃi şi MecaniciDeBord sunt disjuncte şi sunt subentităŃi ale entităŃii PersonalNavigant). • Intr-o bază de date pot exista entităŃi a căror existenŃă este determinată de existenŃa altora (de exemplu, entitatea PersoaneInIntretinere depinde de entitatea SalariaŃi); primele se numesc entităŃi dependente , celelalte se numesc entităŃi principale . Defini Ńie Atribut = o caracteristică a unei entităŃi.

Un atribut posedă un nume şi – pentru fiecare instanŃă a entităŃii – poate lua o valoare dintr-o mulŃime fixată de valori, numită domeniul de valori ale atributului . Atributele se pot clasifica după complexitate în: • atribute compuse şi atribute simple sau elementare , după cum ele se mai pot descompune sau nu în alte atribute, de mai mică complexitate. Există atribute care nu pot fi decât simple (atributele Capitală, Suprafată, Continent ale entităŃii Tari). Există însă atribute care pot fi considerate fie simple, fie compuse. De exemplu: atributul DataNasterii cu valorile: 1 ianuarie 2000, 2 Mai 1990 etc. poate fi privit fie ca un atribut simplu, fie ca unul compus din atributele Zi, Lună, An. Este indicat să îl tratăm ca un atribut compus dacă prevedem necesitatea de a avea acces direct la luna sau anul de naştere al unei persoane înregistrate în baza de date. Dacă însă o astfel de necesitate nu este probabilă şi dacă dorim să simplificăm structura entităŃii (şi deci a bazei de date) atunci este preferabil să îl tratăm ca atribut simplu; Atributele se pot clasifica după mul Ńimea de valori în: • atribute cu valori unice şi atribute cu valori multile , după cum ele pot lua pentru instanŃele entităŃii respective câte o singură valoare (de exemplu, atributele Capitală, Suprafată, Continent ale entităŃii Tări) sau, dimpotrivă, pentru unele instanŃe pot lua câte o singură valoare, pentru altele mai multe valori iar pentru altete nici o valoare (de exemplu, atributul OrasCuMinimum2MilioaneLocuitori al entităŃii Tări). Când este cazul, se pot defini limite inferioare şi/sau superioare pentru numărul de valori pe care le poate lua un astfel de atribut pentru o instanŃă oarecare (de exemplu, putem specifica faptul că atributul NrTelefon al entităŃii Persoane poate lua minimum o valoare – telefonul de serviciu – şi maximum trei. Atributele se pot clasifica după stabilitate în: • atribute de baz ă şi atribute derivate , după cum ele au valori de sine stătătoare sau care pot fi calculate din valorile altor atribute. De exemplu, să considerăm entităŃile corelate CărŃi, cu atributul NumărAutori – şi Autori, cu atributul Titlu; atributul NumărAutori este un atribut derivat: valorile pe care le ia pentru diferite instanŃe ale entităŃii CărŃi pot fi calculate pe baza numărului de apariŃii ale atributului Titlu pentru diferite instanŃe ale entităŃii Autori.

Utilizarea instanŃelor unei entităŃi ridică două probleme foarte importante: • modul de adresare a fiecărei instanŃe a unei entităŃi; • determinarea instanŃelor care se repetă. Pentru a simplifica referirea la instanŃele unei entităŃi s-a recurs la mecanismul identificatorului unic (sau al cheii primare). Defini Ńie Identificatorul unic (sau cheie primar ă) = un atribut sau cea mai mică mulŃime de atribute ale unei entităŃi care iau, pentru fiecare instanŃă a entităŃii respective, o valoare şi numai una. Atunci când nici un atribut sau grup de atribute ale entităŃii rezonabil de

13

numeros nu ia valori disticte pentru fiecare instanŃă a acesteia se poate adăuga un “atribut convenŃional” care să îndeplinească această condiŃie. De obicei, acest atribut este denumit cu ajutorul prefixelor cod sau id .

C. RelaŃii Oridecâteori un atribut al unei entităŃi se referă la altă entitate din baza de date

se stabileşte, de fapt, o relaŃie între cele două entităŃi. Când proiectăm baza de date, aceste referiri nu ar trebui să fie reprezentate ca atribute ale entităŃilor ci ca rela Ńii (atât în sensul real cât şi în sensul matematic al cuvântului) între entităŃi. Atributele prin care se stabileşte această relaŃie se numesc chei sau câmpuri de legatur ă. Defini Ńie RelaŃie într-o bază de date = o legătură logică între două sau mai multe entităŃi.

Modelul conceptual al bazelor de date relaŃionale poate fi reprezentat printr-o schem ă conceptual ă sau printr-o diagram ă entitate-rela Ńie.

RelaŃiile sunt caracterizate prin grad şi cardinalitate (sau tip). Defini Ńie Gradul unei rela Ńii = numărul de entităŃi care participă la relaŃia respectivă. După grad, relaŃiile pot fi • binare (între două entităŃi; de exemplu: relaŃia JoacăIn este o relaŃie binară între entităŃile Actori şi Filme); • ternare (între trei entităŃi; de exemplu: relaŃia LanseazăMelodia este o relaŃie ternară între entităŃile Compozitori, Textieri şi Solişti); • n-are (între mai multe entităŃi; de exemplu: relaŃia Montează este o relaŃie de gradul cinci între entităŃile Regizori, Scenografi, DesigneriCostume, Actori şi PieseDeTeatru). Defini Ńie Cardinalitatea (tipul) unei rela Ńii binare = = numărul de instanŃe ale celor două entităŃi care sunt asociate prin relaŃia respectivă.

Să considerăm două entităŃi E1 şi E2; după cardinalitate (sau tip), relaŃiile dintre cele două entităŃi pot fi • 1 – 1 (one-to-one); • 1 – m (one-to-many); • n – m (many-to-many). Defini Ńie RelaŃie 1–1 = o relaŃie între două entităŃi E1 şi E2 în care unei instanŃe a entităŃii E1 ii corespunde o singura instanŃă din entitatea E2 şi reciproc. Defini Ńie RelaŃie 1–m = o relaŃie între două entităŃi E1 şi E2 în care unei instanŃe a entităŃii E1 (numită entitate dominantă) ii pot corespunde mai multe instanŃe din entitatea E2 dar unei instanŃe din E2 nu-i poate corespunde decât cel mult o instanŃă din E1 . Defini Ńie RelaŃie n–m = o relaŃie între două entităŃi E1 şi E2 în care unei instanŃe a entităŃii E1 ii pot corespunde mai multe instanŃe din entitatea E2 şi, reciproc, unei instanŃe din entitatea E2 îi pot corespunde mai multe instanŃe din entitatea E1 .

D. Modelul rela Ńional: fundamentarea teoretic ă Conceptul matematic aflat la baza modelului relaŃional al bazelor de date este cel

de relaŃie: Defini Ńie Se numeşte rela Ńie peste mulŃimile M1, M2, …Mn orice submulŃime a produsului lor cartezian: R ⊆⊆⊆⊆ M1, x M2, x … x Mn Exemplu

14

Fie mulŃimile Marca = {Dacia, Ford, Fiat, Audi, Opel, Volvo}, Tip = {benzină, motorină}, CapacCil = {1100, 1200, 1300, 1400, 1600}, NrLoc = {4,5}, NrUşi = {2, 4, 5}. Atunci, entitatea Automobil poate fi reprezentată ca o relaŃie peste aceste mulŃimi: Automobil ⊆⊆⊆⊆ Marca x Tip x CapacCil x NrLoc x NrUşi Iată câteva instanŃe ale acestei entităŃi: (Dacia, benzină, 1400, 5, 4), (Dacia, motorină, 1400, 5, 4), (Dacia, benzină, 1100, 5, 4), (Dacia, motorină, 1400, 5, 5), (Ford, motorină, 1400, 5, 5), (Ford, benzină, 1600, 5, 4), (Fiat, benzină, 1300, 5, 4), (Fiat, benzină, 1100, 5, 4), (Audi, motorină, 1600, 5, 4), (Opel, benzină, 1400, 5, 5), (Volvo, benzină, 1400, 5, 5), (Volvo, motorină, 1600, 5, 4). Dacă generalizăm exemplul de mai sus obŃinem: E ⊆⊆⊆⊆ A1 x A2 x … x An unde am notat cu E entitatea şi cu A1, A2, … , An mulŃimile de valori (domeniile) atributelor sale. Un element al acestei relaŃii (adică un tuplu al produsului cartezian) reprezintă o instanŃă ei a entităŃii E şi constă din valori particulare ale atributelor. Pentru simplitatea reprezentării, entităŃile nu sunt reprezentate ca mulŃimi de tupluri (ca în exemplul nostru de mai sus) ci ca tabele (vezi Tabelul 4), tot aşa cum, în loc să notăm cu <(3,5) instanŃa relaŃiei de ordine < ⊆⊆⊆⊆ N X N dintre numerele naturale 3 şi 5, scriem 3 < 5.

Marca Tip CapacCil NrLoc NrU şi Dacia benzină 1400 5 4 Dacia motorină 1400 5 4 Dacia benzină 1100 5 4 Dacia motorină 1400 5 5 Ford motorină 1400 5 5 Ford benzină 1600 5 4 Fiat benzină 1300 5 4 Fiat benzină 1100 5 4 Audi motorină 1600 5 4 Opel benzină 1400 5 5 Volvo benzină 1400 5 5 Volvo motorină 1600 5 4

Tabelul 4: O entitate, o relaŃie matematică, o tabelă

Puterea şi eleganŃa modelului relaŃional este dată de faptul că şi relaŃiile dintre entităŃi pot fi reprezentate tot prin tabele.

E. Stabilirea rela Ńiilor între entit ăŃi La nivelul modelului conceptual, cardinalitatea relaŃiilor este o caracteristică

instrinsecă a acestora; atunci când proiectăm însă baza de date relaŃiile dintre entităŃi trebuie definite în mod efectiv. Acest lucru se realizează în mod diferit pentru fiecare tip de relaŃie.

Fie două entităŃi U şi V şi fie T1 şi T2 tabelele prin care sunt reprezentate (conform convenŃiilor din paragraful anterior). Pentru simplitate, vom presupune că entităŃile aflate în relaŃie sunt identificate prin chei primare formate dintr-un singur atribut.

E.1. Rezolvarea rela Ńiilor 1-1 Pentru a stabili o relaŃie 1-1 între două entităŃi utilizăm aceelaşi atribut pe post de cheie primară pentru ambele entităŃi.

15

Figura 8: Implementarea unei relaŃii 1-1 în Ms Access

E.2. Rezolvarea rela Ńiilor 1-m În acest caz, trebuie să definim noŃiunea de cheie externă: Fie două entităŃi U şi V (de exemplu: Clase şi Elevi) având atributele a1 (cheie

primară), a2, … , an şi respectiv b1 (cheie primară), b2, … , bm, a1, (de exemplu: CodClasă, LocaŃie, nrBanci, nrTable, respectiv CNP, Nume, Prenume, Adresa, CodClasă). Prin cheie extern ă înŃelegem un atribut al entităŃii V a cărui mulŃime de valori coincide cu mulŃimea valorilor cheii primare din entitatea U (aici: atributul CodClasă este cheie primară pentru entitatea Clase şi cheie externă pentru entitatea Elevi).

Pentru a stabili o relaŃie 1-m între două entităŃi (aici relaŃia InCareInvaŃă între entităŃile Clase şi Elevi) procedăm astfel: (1.) includem în descrirea ambelor entităŃi un acelaşi atribut (aici: CodClasă); (2.) definim acest atribut drept cheie primară pentru entitatea principală şi drept cheie

externă pentru entitatea secundară.

E.3. Rezolvarea rela Ńiilor n-m In acest caz, ne bazăm pe faptul că în modelul relaŃional nu numai entităŃile ci şi

relaŃiile dintre ele sunt relaŃii în sens matematic şi, ca urmare, pot fi reprezentate prin tabele. Am observat, de asemenea, că relaŃiile dintre entităŃi pot avea atribute (inclusiv chei primare).

Pentru a o stabili o relaŃie n-m între două entităŃi (aici: relaŃia Comandă între entităŃile ClienŃi şi Furnizori) procedăm astfel:

16

(1.) includem în descrierea relaŃiei (aici: Comandă) – pe post de chei externe – două atribute care să corespundă atributelor care funcŃionează drept chei primare pentru cele două entităŃi (aici: CodClient şi CodFurnizor);

(2.) reducem astfel stabilirea unei relaŃii n-m (aici: relaŃia dintre ClienŃi şi Furnizori) la stabilirea a două relaŃii 1-m (aici: relaŃiile dintre ClienŃi şi Comandă şi respectiv dintre Furnizori şi Comandă).

Figura 9: Implementarea unei relaŃii de tip n-m în Ms Access

F. Reguli de integritate pentru bazele de date După modelarea bazei de date la nivel structural (definirea entităŃilor, a atributelor

lor şi a relaŃiilor dintre ele) urmează nivelul operaŃional al modelării: • stabilirea tipurilor de operaŃii care se pot efectua asupra datelor stocate (sortare,

căutare, vizualizare, adăugare, ştergere, modificare etc., prezentate mai jos); • verificarea respectării regulilor de integritate (ceea ce va asigura corectitudinea şi

consistenŃa datelor). Distingem următoarele tipuri principale de reguli integritate :

• a entit ăŃilor ; • a rela Ńiior (numită şi regula de integritate referen Ńială);

17

La acestea se adaugă şi regulile de integritate impuse de situaŃia reală modelată prin baza de date, numite restric Ńiile contextuale .

F.1. Valorea special ă Null a atributelor Pentru a putea formula prima regulă de integritate trebuie să analizăm situaŃia

unei valori speciale pe care o pot lua atributele entităŃilor: valoarea Null. Defini Ńie Null = valoarea pe care o ia un atribut pentru o instanŃă a unei entităŃi atunci când pentru respectiva instanŃă: • nu există o valoare, • există o valoare dar nu a fost înregistrată (de exemplu, atributul

SerieNrCarteIdentitate), • nu ştim dacă există sau nu o valoare (de exemplu, atributul NrApartament).

Prin urmare, această valoare – artificială, care nu trebuie confundată cu valoarea 0 sau cu stringul vid – a fost incorporată modelului relaŃional al bazelor de date pentru a permite tratarea excepŃiilor şi a datelor incomplete. In ciuda alternativei, mult mai "nocive": introducerea unor date false, utilizarea valorii Null este incă destul de controversată (E.F. Codd a susŃinut-o iar C.J. Date o respinge); există SGBD-uri care nu implementează valoarea Null.

F.2. Integritatea entit ăŃilor Prima regulă de integritate se aplică cheilor primare. Defini Ńie Oricare ar fi entitatea E din baza de date, nici un atribut care face parte din cheia sa primară nu poate lua valoarea Null pentru nici o instanŃă a entităŃii.

Dacă am permite ca un atribut din componenŃa cheii primare a entităŃii să ia valoarea Null, am contrazice cerinŃa de minimalitate a cheii primare (ar insemna ca restul atributelor care formează cheia şi care iau numai valori din domeniile lor respective de valori ar fi suficiente pentru a identifica în mod unic fiecare instanŃă a entităŃii).

F.3. Integritatea referen Ńială A doua regulă de integritate se aplică cheilor externe. Defini Ńie Fie două entităŃi U şi V relaŃionate; pentru orice instanŃă a entităŃii V (secundară) valoarea cheii externe trebuie să corespundă valorii cheii primare a unei instanŃe oarecare a entităŃii U (principală) sau să fie Null.

Figura 10: Stabilirea integrităŃii referenŃiale în Ms Access

18

F.4. Restric Ńii contextuale In etapa de analiză a situaŃiei reale care trebuie modelată prin baza de date, în discuŃiile dintre proiectanŃii bazei de date şi viitorii ei proprietari şi utilizatori, pot apărea informaŃii suplimentare privind restricŃiile pe care trebuie să le îndeplinească datele stocate şi operaŃiile efectuate asupra lor. Defini Ńie Restric Ńii contextuale = reguli suplimentare privind modul de înregistrare a datelor şi de efectuare a operaŃiilor, specifice situaŃiei reale sau impuse de diferitele categorii de participanŃi de proiectarea, construirea, administrarea şi utilizarea bazei de date.

1.5. Normalizarea bazelor de date

A. Introducere: defini Ńii, terminologie Normalizarea poate fi privită ca ultima etapă în proiectarea unei baze de date.

Aşa cum am văzut, acest proces – de tip top-down – începe cu identificarea principalelor entităŃi şi relaŃii; urmează ca acestea să fie examinate (inclusiv la nivelul raporturilor dintre atributele care le caracterizează) în scopul eliminării tuturor "defectelor" lor şi transformării într-un set de relaŃii adecvat, coerent şi bine structurat. Această tehnică a fost iniŃiată tot de E.F. Codd (a se vedea [6]). El a propus iniŃial trei seturi de reguli pe care o relaŃie trebuie să le satisfacă pentru a fi coerentă şi pe care le-a denumit prima (FN1), a doua (FN2), respectiv a treia (FN3) formă normală (dând astfel şi numele tehnicii însăşi). Ulterior, R. Boyce a introdus, împreună cu E.F. Codd, o definiŃie mai tare a FN3 – denumită forma normală Boyce-Codd (FNBC). In fine, au mai fost definite încă două forme normale: a patra (FN4) şi a cincea (FN5) formă normală, care însă au în vedere situaŃii destul de rar intalnite. Să remarcăm caracterul "progresiv" al acestor forme normale (ilustrat şi prin Figura 1): o relaŃie aflată în FN3 este automat în FN2 şi deci şi în FN1. De fapt, câteodată – din punct de vedere al performanŃelor în exploatare – este preferabil ca baza de date să fie lăsată intr-o formă normală inferioară (se execută procesul invers normalizării, denumit denormalizare a bazei de date).

Figura 11: Formele normale

Este justificată întrebarea: câte forme normale mai aşteaptă să fie descoperite? Răspunsul a fost dat de R. Fagin în 1981 (a se vedea [11]). In acest articol este introdusă o formă normală care se bazează pe noŃiunile de domeniu de valori şi cheie primară (FN/DK)şi se demonstrează că o relaŃie este în FN/DK dacă şi numai dacă nu prezintă anomalii la modificarea datelor. Această teoremă arată că nu mai este nevoie de nicio altă formă normală (cel puŃin din punctul de vedere al eliminării anomaliilor la modificarea datelor). Defini Ńie

19

Normalizare = un proces prin care un set de relaŃii care încalcă anumite principii de proiectare este înlocuit cu un alt set de relaŃii adecvat, coerent şi bine structurat.

Acest proces se desfăşoară în mai multe etape: în fiecare etapă se urmăreşte eliminarea unui alt tip de defecte ale relaŃiilor astfel încât, pe măsură ce relaŃiile trec în forme normale superioare, ele devin din ce în ce mai puŃin vulnerabile faŃă de anomaliile de actualizare a datelor.

Pentru a prezenta procesul de normalizare, este necesar să definim următoarele două concepte: • anomalie la actualizare, • dependenŃă funcŃională.

Pentru simplificare, vom utiliza – acolo unde este cazul – reprezentarea prin tabele a entităŃilor şi relaŃiilor din baza de date.

B. Anomalii la actualizare Unul dintre principalele obiective în proiectarea bazelor de date este eliminarea redundanŃelor (a înregistrării aceloraşi date de mai multe ori). Fie, de exemplu, entităŃile : Elevi {CNP, CodClasă, Nume, Prenume, Adresă}; Clase {CodClasă, LocaŃie, NrBanci, NrTable}; EleviClase {CNP, Nume, Prenume, Adresă, CodClasă, LocaŃie, NrBanci, NrTable}.

Dacă în baza de date includem tabelele Elevi şi Clase nu vom avea redundanŃe în date; în schimb, dacă includem tabelele Clase şi EleviClase (sau Elevi şi EleviClase) redundanŃa va fi evidentă. Tabelele care conŃin date redundante pot genera probleme în momentul actualizării informaŃiei. Acestea se numesc anomalii la actualizare şi sunt de trei tipuri: 1. anomalii la adăugare; 2. anomalii la ştergere; 3. anomalii la modificare. Le vom ilustra prin exemple pentru cazul în care în baza de date includem tabelele Clase şi EleviClase definite în schema conceptuală de mai sus:

CodClas ă Loca Ńie NrBanci NrTable XIB Cam23 18 3 XA Cam12 21 2 IXC Cam15 18 2

(a) Entitatea Clase

CNP Nume Prn Adr CodCl Loc NrB NrT

1900530123 Savu Ion B IXC Cam09 15 2 1900924456 Rosu R CJ XA Cam15 21 3 2900225789 Banu M B XA Cam15 21 3 2900807246 Rona C AR IXC Cam09 15 2 1901010357 Mares D DJ XIB Cam23 18 3

(b) Entitatea EleviClase

Figura 12: Două relaŃii care pot produce anomalii de actualizare

20

(1.) Distingem două tipuri de anomalii la adăugare: (a.)

CNP Nume Prn Adr CodCl Loc NrB NrT 1900530123 Savu Ion B IXC Cam09 15 2 1900924456 Rosu R CJ XA Cam15 21 3 2900225789 Banu M B XA Cam15 21 3 2900807246 Rona C AR IXC Cam09 15 2 1901010357 Mares D DJ XIB Cam23 18 3 1900404135 Olaru S TL XIB Cam23 18 3 2901010555 Manu D PL XIB Cam23 18 2

(b.)

CNP Nume Prn Adr CodCl Loc NrB NrT 1900530123 Savu Ion B XIB Cam23 18 3 1900924456 Rosu R CJ XIB Cam23 18 3 2900225789 Banu M B XA Cam15 21 3 2900807246 Rona C AR IXC Cam09 15 2 1901010357 Mares D DJ XIB Cam21 21 3 Nul Null Nul Null XE Cam11 16 2

(2.) Anomalii la ştergere

CodClas ă Loca Ńie NrBanci NrTable XA Cam12 21 2 IXC Cam15 18 2

CNP Nume Prn Adr CodCl Loc NrB NrT 1900530123 Savu Ion B XIB Cam23 18 3 1900924456 Rosu R CJ XIB Cam23 18 3 2900225789 Banu M B XA Cam15 21 3 2900807246 Rona C AR IXC Cam09 15 2 1901010357 Mares D DJ XIB Cam21 21 3

Figura 14: Adăugarea unei noi clase (în care nu există încă elevi) necesită introducerea valorii Null în celulele destinate

datelor despre elevi, deci inclusiv în cheia primară. Acest lucru contravine regulii de integritate a entităŃilor

Figura 15: Datele despre clasa a XIB au fost şterse din tabela Clase dar datele elevilor din acea clasă au rămas în tabela EleviClase

Figura 13 : Repetarea datelor despre clasă la înregistrarea fiecărui nou elev trebuie să se facă exact

21

(3.) Anomalii la modificare

CodClas ă Loca Ńie NrBanci NrTable XIB Cam23 20 4 XA Cam12 21 2 IXC Cam15 18 2

CNP Nume Prn Adr CodCl Loc NrB NrT 1900530123 Savu Ion B XIB Cam23 18 3 1900924456 Rosu R CJ XIB Cam23 18 3 2900225789 Banu M B XA Cam15 21 3 2900807246 Rona C AR IXC Cam09 15 2 1901010357 Mares D DJ XIB Cam21 21 3

C. Dependen Ńe func Ńionale Procesul de normalizare se bazează pe examinarea relaŃiilor dintre atributele

entităŃilor, oglindite prin conceptul de dependenŃă funcŃională. Defini Ńie Dependen Ńă func Ńional ă = o restricŃie care apare între atributele unei entităŃi la nivelul semanticii (semnificaŃiei) acestora: fie a1 şi a2 atributele unei entităŃi E; spunem că atributul a2 este dependent funcŃional de atributul a1 dacă fiecărei valori a atributului a1 îi corespunde o valoare şi numai una a atributului a2 . Observa Ńie • Observăm că unei valori a atributului a2 îi pot corespunde mai multe valori ale atributului a1. (putem spune că a1 este argumentul iar a2 este imaginea unei funcŃii în sensul matematic al cuvântului). • Se pot afla în dependenŃă funcŃională nu numai atribute individuale ci şi grupuri de atribute. Vom ignora dependenŃele triviale, adică dependenŃele a1 →→→→ a2 în care a2 depinde funcŃional de un subset al a1.

Notăm dependenŃa funcŃională a atributelor a1 şi a2 prin a1 →→→→ a2 şi o reprezentăm grafic ca în Figura 7.

Figura 17: Reprezentarea grafică a dependenŃei funcŃionale Defini Ńie Determinantul unei dependen Ńe func Ńionale = atributul care, prin valorile sale, determină valorile celuilalt atribut (adică: atributul aflat, în oricare dintre cele două reprezentări, în stânga săgeŃii). Observa Ńie Examinarea dependenŃelor funcŃionale dintre atributele unei entităŃi ne permite să determinăm care dintre cheile candidate trebuie să fie aleasă drept cheie primară: este

Figura 16: In tabela Clase s-au operat modificări în datele care descriu una dintre clase dar în tabela EleviClase au

rămas încă datele vechi despre respectiva clasă

atributul a2 depinde

funcŃional de a1 a1 a2

22

aleasă cheia candidat care apare ca determinant în toate dependenŃele funcŃionale identificate la nivelul entităŃii respective (a se vedea al doilea exemplu de mai sus.

D. Procesul de normalizare Normalizarea unei baze de date este un proces care se desfăşoară în mai mulŃi

paşi. Fiecare pas (cu excepŃia aducerii bazei de date la FN1) presupune: 1. identificarea dependenŃelor funcŃionale; 2. verificarea îndeplinirii unor anumite proprietăŃi denumite generic forme normale.

Pe măsură ce procesul de normalizare progresează, relaŃiile care compun baza de date (fie că acestea corespund unor entităŃi sau unor relaŃii dintre entităŃi) devin din ce în ce mai riguroase (forma normală pe care o satisfac este mai restrictivă: a se vedea Figura 1 de mai sus).

Din punctul de vedere al modelului relaŃional, singura formă normală obligatorie pentru toate relaŃiile din baza de date este FN1; dacă însă dorim să evităm toate anomaliile de actualizare (analizate mai sus) este necesar să continuăm procesul de normalizare cel puŃin până la FN3. Observa Ńie Reamintim faptul că în modelul relaŃional, orice entitate şi relaŃie dintre entităŃi este modelată matematic prin conceptul de relaŃie şi reprezentată convenŃional printr-o tabelă. In continuare, prin rela Ńie vom înŃelege modelul matematic al unei entităŃi sau al unei relaŃii între entităŃi, reprezentată convenŃional printr-o tabelă, ca mai sus.

E. Prima form ă normal ă (FN1) După definirea structurii şi a operaŃiilor cu baza de date, urmează introducerea

datelor (crearea instanŃelor entităŃilor şi a relaŃiilor dintre ele). Acest lucru înseamnă – în principal – transferarea datelor concrete din formularele de culegere a datelor în tabelele asociate entităŃilor / relaŃiilor. După parcurgerea acestui pas, se obŃin – de obicei – tabele nenormalizate , adică tabele în care – unele celule – conŃin mai multe valori ale aceluiaşi atribut sau repetă valori din alte celule. � Exemplu Fie baza de date a unei firme de transport auto care se ocupă cu transportul de persoane; dintre entităŃile care apar intr-o astfel de bază de date enumerăm: AngajaŃi, Vehicule, Garaje, ClienŃi, Trasee. Să presupunem că formularele de culegere date completate de şeful fiecărui garaj conŃin (pe lângă adresa garajului): tipul vehiculelor deŃinute (limuzină, microbuz, autocar), numerele de înmatriculare ale acestora, datele de identificare ale şoferilor care le conduc. Transcrirea directă a datelor din aceste formulare în tabela Garaje poate conduce la rezultatul din Figura 15.

Figura 18: Tabela Garaje11 în Ms Access

23

Dacă examinăm celulele din coloana tipAuto (precum şi din coloana Soferi)

constatăm că tabela Garaje se află în form ă nenormalizat ă. Ca urmare, tabela trebuie trecută în FN1. Defini Ńie RelaŃie aflat ă în prima forma normal ă (FN1) = o relaŃie cu proprietatea că oricare dintre celulele tabelei care o reprezintă convenŃional conŃine o valoare şi numai una (nu există atribute cu valori multiple).

Se cunosc două metode de a aduce o relaŃie în FN1; o vom prezenta pe cea mai eficientă dintre ele (vom presupune că un singur atribut nu respectă condiŃia din FN1): Aducerea unei rela Ńii la FN1 Fie E o entitate (aici: Garaje), a atributul său (aici: tipAuto) responsabil pentru forma nenormalizată a tabelei T corespunzătoare entităŃii (aici: tabela Garaje11). Aducerea tabelei la FN1 necesită: (1.) eliminarea atributului a din entitatea E / a coloanei corespunzătoare din tabela T (aici: a coloanei TipAuto din tabela Garaje11; denumim Garaje22 tabela astfel rezultată); (2.) crearea – pornind de la atributul a – a unei noi entităŃi E' ; fie T' tabela care o reprezintă (aici: pe baza atributului TipAuto creăm entitatea Vehicule cu cheia primară NrInmatr şi atributele: TipAuto, Marca, NrStele, NrLocuri, Culoare); (3.) stabilirea relaŃiei dintre cele două entităŃi, relaŃie care este – după caz – de tip 1-m sau n-m (aici: relaŃia dintre entităŃile Garaje şi Vehicule este de tip 1-m). Prin urmare, cheia primară a entităŃii E trebuie să inclusă – cu rol de cheie externă – printre atributele entităŃii E' (aici: includem atributul CodGaraj – cu rol de cheie primară pentru entitatea Garaje – pe post de cheie externă pentru entitatea Vehicule).

Figura 19: Tabela Garaje după prima modificare

24

Figura 20: Tabela nou creată, Vehicule

Figura 21: RelaŃiile dintre noile tabele Aten Ńie Intr-o relaŃie pot exista mai multe atribute cu valori multiple, deci care să fie – fiecare la rândul său – responsabile pentru forma nenormalizată a relaŃiei (tabelei). In acest caz, aducerea relaŃiei la FN1 se desfăşoare în tot atâŃia paşi câte astfel de atribute există (aici: noua tabelă Garaje22 este tot în formă nenormalizată din cauza atributului Soferi). Prin urmare, vom proceda pentru acest atribut aşa cum am procedat şi pentru atributul TipAuto: a se vedea Figura 12 (tabela Garaje33 aflată acum în FN1) şi Figura 13 (tabela Soferi nou creată) precum şi Figura 14 (tabela de joncŃiune Conduc prin care vom realiza relaŃia de tip n–m dintre entităŃile nou create Soferi şi Vehicule).

25

Figura 22: Tabela Garaje33 (complet normalizată)

Figura 23: Tabela nou creată, Soferi

26

Figura 24: Tabela de jocntiune Conduce Aten Ńie

Procesul de aducere la FN1 a evidenŃiat necesitatea inregistrării unor informaŃii noi: numerele de înmatriculare ale autovehiculelor (la primul pas), datele personale ale şoferilor (la al doilea pas). Pe de altă parte, în forma iniŃială, nenormalizată, tabela Garaje11 conŃinea o serie de informaŃii auxiliare (ce tip de vehicule se află în fiecare garaj, ce şoferi le conduc) care se pot pierde la normalizare. Acest lucru poate fi evitat dacă între noile entităŃi apărute prin normalizare se stabilesc relaŃiile corespunzătoare (aici: la primul pas s-a stabilit o relaŃie 1-m între entitatea Garaje modificată şi entitatea Vehicule nou creată, iar la pasul al doilea s-a stabilit o relaŃie n–m între entităŃile nou create Soferi şi Vehicule, deoarece – în principiu – un şofer poate conduce mai multe vehicule şi un vehicul poate fi condus de mai mulŃi şoferi iar o restricŃie contextuală a bazei de date poate stipula ce vehicule poate conduce fiecare şofer şi ce şoferi pot conduce fiecare vehicul. De aceea, am creat tabela de joncŃiune Conduc cu atributele CNP, NrInmatr. Acestea sunt chei primare în tabelele Soferi respectiv Vehicule şi sunt chei externe în tabela Conduc, dar formează împreună cheia primară pentru tabela Conduc).

27

Figura 25: Setul de relaŃii rezultat după incheierea operatiei de aducere la FN1 a entităŃii Garaje

F. A doua form ă normal ă (FN2) O relaŃie care este în FN1 dar nu este în FN2 poate suferi de anomalii de

modificare, ca în exemplul de mai jos. Exemplu Fie baza de date a unui institut de cercetări care are mai multe filiale şi în care salariaŃii sunt plătiŃi în funcŃie de numărul de ore lucrate în cadrul unui proiect de cercetare sau al altuia. Dintre entităŃile care apar într-o astfel de bază de date enumerăm: Filiale = {CodFil, NumeFil, LocFil}, AngajaŃi = {CNP, CodFil, NumeAng, Adresa, SalariuPeOra}, Proiecte = {CodPrj, TitluPrj, CodFil, DataPredării}. Pe lângă acestea, am mai introdus în baza de date şi entitatea AngajaŃiProiecte = {CNP, NumeAng, CodPrj, TitluPrj, NrOre, DataPredării} cu instanŃele din Figura 16. Să presupunem că data de predare a proiectului tr1 a fost devansată cu o lună; dacă nu operăm această modificare în ambele înregistrări din tabelă care se referă la acest proiect atunci apare o anomalie de actualizare şi baza de date işi pierde consistenŃa.

28

Figura 26: Tabela AngajaŃiProiecte

Observa Ńie FN2 se referă numai la relaŃii a căror cheie primară este formată din mai multe atribute deoarece se bazează pe conceptul de dependenŃă funcŃională completă. Defini Ńie Dependen Ńa func Ńional ă complet ă Fie a1 şi a2 două (mulŃimi de) atribute ale entităŃii E; spunem că a2 este complet dependent func Ńional de a1 dacă şi numai dacă a2 este dependent funcŃional de a1 dar nu este dependent funcŃional de nicio submulŃime proprie a lui a1. Dacă, dimpotrivă, putem elimina un atribut din mulŃimea de atribute a1 iar a2 continuă să fie dependent funcŃional de a1 atunci spunem că a2 este doar parŃial dependent func Ńional de a1. Contraexemplu Să examinăm relaŃia Vehicule din exemplul de mai sus: dependenŃa funcŃională NrInmatr, TipAuto →→→→ CodGaraj nu este completă: atributul CodGaraj este dependent funcŃional de un subset al {NrInmatr, TipAuto }, şi anume de NrInmatr. Defini Ńie A doua form ă normal ă O relaŃie este în FN2 dacă şi numai dacă: (1) este deja în FN1; (2) oricare dintre atributele sale care nu fac parte din cheia primară este complet dependent funcŃional de cheia primară. Aducerea unei rela Ńii la FN2 Fie E o entitate aflată în FN1; aducerea ei la FN2 necesită: (1.) identificarea tuturor dependenŃelor funcŃionale dintre atributele entităŃii E; (2.) descompunerea relaŃiei E într-un număr de noi relaŃii astfel: o fiecare dependenŃă funcŃională completă defineşte o nouă relaŃie; o din fiecare dependenŃă funcŃională parŃială se elimină acea parte a cheii

primare care este răspunzătoare de incompletitudinea dependenŃei şi apoi se defineşte noua relaŃie.

(3.) stabilirea relaŃiilor dintre noile entităŃi (în scopul recuperării informaŃii de legatură, pierdute eventual prin inlocuirea entităŃii iniŃiale cu entităŃile normalizate). Aducerea unei rela Ńii la FN2

29

Ilustrăm metoda de mai sus pentru entitatea AngajaŃiProiecte din Figura 17. La prima vedere, fiecare dintre atributele entităŃii: NumeAng, titluPrj, nrOre, dataPredării depinde funcŃional de cheia primară a acesteia: CNP, codPrj. Aplicând definiŃia dependenŃei funcŃionale complete observăm că numai atributul nrOre depinde funcŃional complet de ambele atribute care formează cheia primară (a se vedea dependenŃa funcŃională d.f.1 din Figura 17). Celelalte dependenŃe sunt parŃiale; din ele obŃinem următoarele dependenŃe complete: d.f.2 şi d.f.3. Ca urmare, vom înlocui entitatea AngajaŃiProiecte cu entităŃile AP1, AP2, AP3 (Figura 18) şi vom stabili relaŃiile dintre ele. d.f.2 d.f.1

CNP codPrj nrOre NumeAng titluPrj dataPredării

d.f.3 Figura 27: DependenŃele funcŃionale complete din entitatea AngajaŃiProiecte AP1 AP2

AP3

codPrj titluPrj dataPredării

Figura 27: Normalizarea entităŃii AngajaŃiProiecte

Figura 28: RelaŃiile dintre noile tabele aflate în FN2

CNP NumeAng CNP codPrj nrOre

30

G. A treia form ă normal ă (FN3) O relaŃie care este în FN2 dar nu este în FN3 poate suferi de anomalii de

modificare, ca în exemplul de mai jos. Exemplu Fie baza de date a institutului de cercetări descrisă în paragraful anterior. Să presupunem că am mai introdus în baza de date şi entitatea AngajaŃiFiliale = {CNP, NumeAng, Adresa, Oras, CodFil, NumeFil, LocFil} cu instanŃele din Figura 20. In cazul în care una dintre filiale işi schimbă sediul (de exemplu filiala CercAero se mută de la Huşi la Iaşi), operarea modificării numai în una dintre cele două înregistrări care se referă la filiala respectivă determină apariŃia unei anomalii de actualizare şi baza de date işi pierde consistenŃa.

Figura 29: Tabela AngajaŃiFiliale

Observa Ńie FN3 se bazează pe conceptul de dependenŃe funcŃionale tranzitive. Defini Ńie Dependen Ńe func Ńioanle tranzitive Fie a1 , a2 şi a3 trei atribute ale unei entiăŃi E cu proprietatea că: (1) a1 →→→→ a2 şi a2 →→→→ a3 (2) a1 nu depinde funcŃional nici de a2 nici de a3 Atunci: a1 →→→→ a3 ( a3 depinde func Ńional de a1 via a2 ).

Exemplu Să examinăm relaŃia EleviClase din paragraful 4.2:

CNP Nume Prn Adr CodCl Loc NrB NrT

1900530123 Savu Ion B IXC Cam09 15 2

1900924456 Rosu R CJ XA Cam15 21 3

2900225789 Banu M B XA Cam15 21 3

2900807246 Rona C AR IXC Cam09 15 2

1901010357 Mares D DJ XIB Cam23 18 3

Avem următoarele dependenŃe funcŃionale: CNP →→→→ CodCl şi CodCl →→→→

Loc; atunci avem şi CNP →→→→ Loc via atributul CodCl deoarece atributul CNP nu depinde funcŃional nici de CodCl nici de Loc.

31

Defini Ńie A treia form ă normal ă O relaŃie este în FN3 dacă şi numai dacă: (1) este deja în FN2; (2) nici unul dintre atributele sale care nu fac parte din cheia primară nu este, prin tranzitivitate, dependent funcŃional de cheia primară. Aducerea unei rela Ńii la FN3 Fie E o entitate (aici: AngajaŃiFiliale) aflată în FN2 şi a1 , a2 şi a3 trei atribute ale sale cu proprietatea că: a1 →→→→ a2 şi a2 →→→→ a3 (aici: CNP, CodFil , LocFil : CNP →→→→ CodFil , CodFil →→→→ LocFil): Aducerea tabelei la FN3 necesită: (1.) identificarea tuturor dependenŃelor tranzitive dintre atributele entităŃii E; (2.) descompunerea relaŃiei E într-un număr de noi relaŃii astfel: o atributul a1 impreună cu toate atributele care depind funcŃional de el (deci

inclusiv a2 ) formează o nouă relaŃie (aici: atributele CNP, NumeAng, Adresa, Oras, codFil formează entitatea Ang1);

o atributul a2 impreună cu atributul a3 şi cu alte atribute care depind funcŃional de a1 prin tranzitivitate formează o nouă relaŃie (aici: atributul NumeFil se adaugă atributelor CodFil şi LocFil pentru a forma entitatea Fil1);

(3.) definirea atributului a2 drept cheie primară a celei de-a doua entităŃi nou create; (4.) stabilirea relaŃiilor dintre noile entităŃi (în scopul recuperării informaŃiilor de legatură, pierdute eventual prin inlocuirea entităŃii iniŃiale cu entităŃi normalizate).

CNP NumeAng Adresa Oras CodFil NumeFil LocFil

Figura 30: DependenŃele funcŃionale tranzitive din entitatea AngajaŃiFiliale

CNP NumeAng Adresa Oras CodFil

Figura 31: Normalizarea entităŃii AngajaŃiFiliale

Observa Ńie Observăm că atributul care asigură tranzitivitatea (atributul notat a2) nu este nici cheie primară în relaŃia respectivă nici măcar parte a cheii primare. Tocmai din acest motiv, dependenŃa a2 →→→→ a3 nu este dezirabilă la nivelul relaŃiei respective.

CodFil NumeFil LocFil

32

Condi Ńie de verificat Solu Ńie (normalizare)

FN1 Toate atributele relaŃiei trebuie să fie atomice

Fiecare atribut neatomic se transformă intr-o nouă relaŃie

FN2

• RelaŃia este în FN1; • Cheia sa primară constă din mai multe atribute; • Toate atributele care nu fac parte din cheia primară sunt complet dependente funcŃional de cheia primară

• Fiecare parte a cheii primare, împreună cu atributele care depind funcŃional complet de ea formează o nouă relaŃie; • Se stabilesc relaŃiile necesare între noile relaŃii care au înlocuit-o pe cea iniŃială

FN3

• RelaŃia este în FN2; • Nici un atribut care nu face parte dintr-o cheie candidat nu este funcŃional dependent de un alt atribut care nu face nici el parte dintr-o cheie candidat (nici un atribut care nu face parte dintr-o cheie candidat nu este funcŃional dependent de cheia primară prin tranzitivitate)

• Se păstrează în relaŃia iniŃială numai cheia primară şi atributele care depind funcŃional de ea direct (inclusiv atributul "incriminat"); • Se creează câte o nouă relaŃie din fiecare atribut care nu face parte din cheia primară împreună cu toate atributele (care nu fac nici ele parte din cheia primară a relaŃiei iniŃiale) care sunt dependente funcŃional de acesta; • Se stabilesc relaŃiile necesare între noile relaŃii şi relaŃia iniŃială modificată

Tabelul 6: Recapitulare a primelor trei etape din procesul de normalizare

1.6. Trecerea de la modelul conceptual la modelul fizic După cum ami observat în capitolele anterioare, regulile ce se pot extrage dintr-

un studiu de caz pot fi descrise prin elemente ale modelului conceptual: entităŃi, atribute, identificatori unici, relaŃii între entităŃi.

Acest model poate fi utilizat pentru determinarea modelului fizic al oricărui tip de bază de date.

La nivelul modelului fizic: • tabela este o structură utilizată pentru stocarea şi organizarea datelor. Tabelele sunt formate din linii şi coloane; • fiecare coloană va reŃine date de un anumit tip şi corespunde unui atribut al entităŃii; numele atributului devine antetul unei coloane din tabelă; • un rând din tabelă corespunde unui element al entităŃii (instanŃă a entităŃii) şi se numeşte înregistrare. Aceasta va descrie complet proprietăŃile unei instanŃe; • cheie primară este reprezentată de o coloană sau o combinaŃie de coloane ale căror valori sunt unice la nivelul tabelei şi sunt completate obligatoriu. Cheile primare provin din identificatorii unici ai entităŃii.

Crearea unei tabele se realizează în două etape: 1. în prima etapă se stabileşte structura tabelei, specificându-se numele câmpurilor,

lungimile acestora, precum şi tipul informaŃiilor care vor fi introduse în fiecare câmp.

2. În a doua etapă se încarcă efectiv datele în tabelă.

33

A. Exemplificare în limbajul de programare ACCESS După definirea structurii putem trece la introducerea propriu-zisă a datelor în

câmpurile tabelei, încheind astfel operaŃia de creare (ulterior, tabela va putea fi folosită în operaŃii de modificare a structurii sau de actualizare a datelor). Pentru aceasta: Pas 1. se afişează tabela în modul Datasheet View → Pas 2. se introduc direct datele în câmpurile corespunzătoare (se plasează cursorul în celulă, se tastează valoarea şi se acŃionează tasta ENTER). Se foloseşte tasta TAB pentru a trece dintr-o celulă în alta. Se folosesc tastele cu săgeŃi pentru a parcurge tabela pe orizontală şi pe verticală.

B. Exemplificare în limbajul de programare FOXPRO

A) Introducerea datelor Adăugarea articolelor se face la sfârşitul tabelei active. Adăugarea unui articol vid se realizează cu ajutorul comenzii APPEND BLANK. Un câmp vid are una din valorile: zero pentru câmpul numeric, spaŃiu pentru câmpul caracter, .F. (fals) pentru câmpurile logice, valoarea {/ /} pentru dată calendaristică. Structura tabelei se creează prin comanda (de exemplu:

tabela Elevi): create table elevi (număr_matricol n(5), nume c(5),

prenume c(13), data_naşterii d, clasa c(2) ) Se adaugă înregistrările folosind o structură de control repetitivă cu număr cunoscut de paşi (for), comenzi de afişare pe ecran de elemente de control începând de la o linie şi o coloană specificată (de tipul l, c say [introduceŃi... ]) şi comanda de tipul read (ce realizează adăugarea efectivă a datelor citite în înregistrarea vidă). for i=1 to 8 do append blank @4,4 say [număr matricol:] get număr_matricol @5,4 say [nume:] get nume @6,4 say [prenume:] get prenume @7,4 say [data naşterii:] get data_naşterii @8,4 say [clasa:] get clasa read end for Introducerea datelor se poate realiza vizual folosind oricare din editoarele Browse sau Edit, utilizând opŃiunile meniului View. Salvarea informaŃiilor se poate realiza folosind

34

combinaŃia de taste Ctrl + W sau executând clic pe butonul de închidere al ferestrei de editare. Pentru adăugarea vizuala de înregistrări vide la sfârşitul unei tabele se poate utiliza opŃiunea Append New Record din meniul Table sau opŃiunea Append Mode din meniul View.

B) Modificarea datelor Comanda REPLACE este utilizată pentru modificarea valorilor din ultima tabelă selectată cu expresii ce pot fi evaluate în momentul executării comenzii: Forma generală a comenzii REPLACE este: REPLACE <câmp1> WITH <expresie 1> [,<câmp2> WITH <expresie2>] [domeniu] [FOR <condiŃie>] [WHILE <condiŃie>] Pentru actualizarea şi vizualizarea datelor poate fi utilizată şi comanda BROWSE care va afişa tabela activă pe linii şi pe coloane (pe prima linie sunt afişate denumirile câmpurilor din structura tabelei, iar în continuare sunt afişate liniile cu date).

C. Exemplificare în limbajul de programare ORACLE

A) Introducerea datelor Comanda INSERT este utilizată pentru introducerea unei noi înregistrări într-o

tabelă. Sintaxa generală a acestei comenzi este: INSERT INTO <nume_tabelă> [(nume_coloană1, [nume_coloană2, ... ])] VALUES (expresie1, expresie2, ... ) Pentru adăugarea unui nou articol poate fi utilizată metoda explicită (când sunt

specificate explicit câmpurile ce vor fi completate cu valorile din clauza VALUES) sau metoda implicită (când nu se specifică niciun câmp dar se cunoaşte structura tabelei şi câmpurile sunt completate cu valorile corespunzătoare din clauza VALUES).

B) Modificarea datelor Pentru modificarea valorilor existente într-un tabel se utilizează comanda

UPDATE care are următoarea sintaxă generală: UPDATE <nume tabel> SET nume_coloană1 = expresie1, nume_coloană2 = expresie2, ... ... nume_coloanăn = expresien [WHERE condiŃie]

Vom reveni asupra codului SQL.

2. INTEROGAREA BAZELOR DE DATE

2.1. Generalit ăŃi Pentru utilizator, o interogare este o metodă de a regăsi anumite informaŃii

dintr-o bază de date, prin intermediul unei aplicaŃii de baze de date. Din punctul de vedere al programatorului aplicaŃiei de baze de date, interogarea se exprimă printr-o comandă echivalentă expresiei de interogare, comandă care se transmite sistemului SGBD.

35

Din punct de vedere al sistemului de gestiune, o interogare este un program (de exemplu, în limbajul SQL8) pe care îl compilează şi apoi îl execută. Ca orice program, o interogare este prelucrată de către SGBD în mai multe faze: analiza lexicală, analiza sintactică şi analiza semantică, pentru validarea interogării, urmate de generarea codului. De asemenea, dacă există mai multe soluŃii pentru aceeaşi interogare, sistemul de gestiune selectează soluŃia optimă.

Conceptual, subsistemul SGBD de prelucrare a interog ărilor constă din următoarele componente: • Compilatorul de interogări, care efectuează analiza lexicală şi sintactică a interogării; acesta validează din punct de vedere sintactic interogarea, adică verifică existenŃa relaŃiilor, a vederilor, a indexurilor şi a atributelor implicate în interogare şi utilizarea corectă a acestora. • Optimizatorul de interogări, care efectuează analiza semantică a interogării şi selectează alternativa optimă dintre mai multe soluŃii posibile de execuŃie a interogării. • Generatorul de cod, care generează programul de execuŃie al interogării, conform optimizărilor efectuate. • Componenta de execuŃie (runtime), care execută programul interogării.

Compilarea interogării se realizează la fel ca orice compilare a programelor, fără aspecte specifice sistemelor de baze de date. Optimizarea interogărilor este o operaŃie specifică sistemelor de gestiune şi utilizează proprietăŃile operaŃiilor relaŃionale pentru a obŃine performanŃe de execuŃie a interogărilor cât mai bune. Optimizarea este efectuată de către SGBD, transparent, fără intervenŃia programatorului.

Interogarea (query) este operaŃia prin care se obŃin datele dorite dintr-o bază de date, selectate conform unui anumit criteriu (condiŃie). Dat fiind că operaŃia de interogare este cea mai importantă operaŃie de manevrare a datelor, de multe ori limbajele de manevrare a datelor sunt denumite limbaje de interogare.

Pentru formularea conceptuală a interogărilor în bazele de date relaŃionale s-au dezvoltat două limbaje abstracte de interogare: algebra relaŃională şi calculul relaŃional. • Algebra relaŃională (relational algebra) constă dintr-o mulŃime de operaŃii care au ca operanzi relaŃii, iar rezultatul este tot o relaŃie. • Calculul relaŃional (relational calculus) este bazat pe calculul predicatelor şi exprimă o interogare formulând o definiŃie a rezultatului dorit (de regulă, o relaŃie) printr-o expresie de calcul relaŃional.

Limbajele de interogare reale implementate în sistemele de baze de date relaŃionale sunt limbaje definite pe baza unuia sau altuia din limbajele de interogare abstracte, sau pe o combinaŃie a acestora. Astfel: • Limbajul SQL este în cea mai mare parte bazat pe algebra relaŃională, dar mai conŃine şi construcŃii derivate din calculul relaŃional. • Limbajul ISBL (Information System Base Language) al firmei IBM este bazat în întregime pe algebra relaŃională. • Limbajul QUEL al SGBD Ingres este bazat pe calculul relaŃional al tuplurilor. • Limbajul QBE (Query by Example), dezvoltat la firma IBM este bazat pe calculul relaŃional al domeniilor.

Un limbaj de interogare real este denumit relaŃional complet dacă implementează toate operaŃiile prevăzute de unul din limbajele de interogare abstracte. În general, toate limbajele relaŃionale implementate în sistemele SGBD sunt limbaje

8 SQL (Structured Query Language) este un limbaj specializat pentru interogarea, actualizarea si administrarea bazelor de date relationale. Ca sintaxa, instructiunile SQL se termina cu ; (punct si virgula) iar parametrii din listele de parametri sunt separati prin , (virgula). SQL fiind un limbaj structurat clauzele care compun instructiunile sale trebuie sa respecte ordinea impusa de sintaxa.

36

relaŃionale mai mult decât complete, conŃinând şi operaŃii care nu sunt prevăzute în limbajele relaŃionale abstracte, ca de exemplu, efectuarea unor calcule aritmetice asupra valorilor unor atribute (sumă, medie, minim, maxim), funcŃii de tipărire a relaŃiilor, etc.

Limbajul SQL este limbajul cel mai utilizat în sistemele relaŃionale.

2.2. Codul SQL Ilustram codul SQL cu ajutorul unei baze de date Ms Access,

AssistRom.mdb:

Instructiunea SELECT

Efect: Se returneaza informatia ceruta sub forma unui set de inregistrari.

Sintaxa: SELECT [predicat] { * | tabel.* | [tabel.]ca mp1 [AS alias1] [, [tabel.]camp2 [AS alias2] [, ...]]} FROM expresie_tabel [, ...] [IN baza_de_date_e xterna] [WHERE... ] [GROUP BY... ] [HAVING... ] [ORDER BY... ] [WITH OWNERACCESS OPTION] Instructiunea SELECT are urmatoarele parti:

predicat

37

este unul dintre urmatoarele predicate: ALL, DISTINCT, DISTINCTROW, sau TOP. Predicatul folosit determina o restrictionare a numarului de inregistrari returnate. Daca nu este specificat, se ia implicit predicatul ALL; *(asterisc) indica faptul ca trebuie selectate toate campurile din tabelele specificate; tabel

numele tabelului care contine campurile care intereseaza pentru selectia inregistrarilor;

camp1, camp2 numele campurilor care contin datele ce trebuie returnate. Daca sunt indicate mai multe campuri, atunci datele acestora sunt returnate conform ordinii din lista de campuri; alias1, alias2 nume de coloane care pot fi folosite ca antete pentru campuri in locul antetelor respective din tabel; expresie_tabel

o expresie care identifica unul sau mai multe tabele din care vor fi returnate date. Expresia poate fi un nume unic de tabel, numele unei interogari deja salvate sau o combinatie obtinuta prin oricare dintre cele 3 tipuri de asociere: INNER JOIN, LEFT JOIN, sau RIGHT JOIN;

baza_de_date_externa numele BD care contine tabelul / tabelele specificate in expresie_tabel, daca acestea nu fac parte din BD curenta; Sintaxa minimala pentru instructiunea SELECT este: SELECT campuri FROM tabel ;

Clauzele WHERE,GROUP BY, HAVING, ORDER BY si WITH OWNERACCESS OPTION au rolul de a organiza setul de inregistrari returnate si de a introduce restrictii suplimentare asupra acestuia. Astfel,

• daca trebuie eliminate inregistrarile duplicate sau afisate numai o parte dintre inregistrari etc., atunci trebuie introdus un predicat adecvat: DISTINCTROW, TOPn etc., • daca trebuie afisate valorile unui camp atunci numele acestuia trebuie inserat dupa verbul SELECT, in lista_de_campuri; daca mai multe campuri din tabele diferite au acelasi nume ele atunci numele lor va fi precedat de numele tabelului, • daca interogarea se bazeaza pe un tabel si / sau interogare deja creata atunci numele acestuia trebuie sa fie inclus in expresia_tabel a clauzei FROM, • daca inregistrarile returnate de interogare trebuie filtrate atunci criteriile de filtrare se introduc prin clauza WHERE, • daca trebuie efectuata o grupare a inregistrarilor si o filtrare a inregistrarilor grupate atunci campul sau campurile dupa care se grupeaza se introduc prin clauza GROUP BY iar criteriile de filtrare prin clauza HAVING, • daca inregistrarile returnate de interogare trebuie sortate atunci campul-cheie de sortare si ordinea de sortare se indica in clauza ORDER BY, • daca proprietarul BD se schimba atunci trebuie indicata noua valoare, Owner’s, in clauza WITH_OWNERACCESS_OPTION.

38

Modul de lucru Programul parcurge tabelul / tabelele specificate in parametrul expresie_tabel, extrage coloanele indicate in parametrii camp1, camp2 etc., selecteaza inregistrarile care verifica criteriul de selectie si sorteaza sau grupeaza inregistrarile rezultate in ordinea specificata.

Observatie Instructiunile SELECT este specifica interogarilor simple (de selectie); ea nu modifica datele din BD. Verbul SELECT este, de obicei, primul cuvant dintr-o instructiune SQL. Cele mai frecvent folosite instructiuni SQL sunt SELECT si SELECT INTO.

Exemple Afisarea numelui si prenumelui clientilor firmei AsistRom ➜

SELECT NumeClient, Prenume FROM Clienti; Utilizarea asteriscului pentru a selecta toate campurile dintr-un tabel. In exemplul de mai jos se selecteaza toate campurile din tabelul “Clienti” ➜

SELECT * FROM Clienti; Utilizarea operatorului . (punct) pentru cazul in care tabelele din clauza FROM contin campuri cu acelasi nume. Atunci, toate campurile care apar in instructiunea SELECT trebuie insotite de numele tabelului din care provin. In exemplul de mai jos campul “CodCentru” apare si in tabelul “Clienti” si in tabelul “CentreCons”; instructiunea SQL trebuie sa selecteze numele clientilor din tabelul “Clienti” si denumirea completa a centrului din tabelul “CentreCons” (vezi codul SQL al interogarii “Q_SubQuery_Clienti_CentreCons” din BD AsistRom ) ➜

SELECT DISTINCTROW Clienti.CodClient, Clienti.NumeClient, Clienti.Prenume,

(SELECT [NumeCentru] FROM [CentreCons]

WHERE [Clienti].[CodCentru] = [CentreCons].[CodCentru])

AS [Centrul de Consultanta]

FROM CentreCons INNER JOIN Clienti ON CentreCons.CodCentru = Clienti.CodCentru;

Utilizarea clauzei AS pentru crearea in RecordSet (setul de inregistrari intoarse de interogare) a unui nume de camp diferit de cel din tabel. In exemplul de mai jos campul “TelefonAcasa” capata in RecordSet numele “NrTelefon” ➜

SELECT Clienti.TelefonAcasa AS NrTelefon FROM Clienti; Aceasta manevra este recomandata mai ales in cazul utilizarii in interogare a functiilor predefinite din categori Total sau a criteriilor care returneaza rezultate ambigui sau duplicate. Clauza AS creeaza un nume alternativ pentru campul–rezultat intors de interogare. In exemplul de mai jos, clauza AS atribuie numele “TotalNrClienti” campului in care se face numararea acestora ➜

SELECT COUNT(CodClient) AS TotalNrClienti FROM Clienti; Utilizarea clauzei AS pentru duplicarea unui camp in vederea efectuarii unor calcule. In exemplul de mai jos, pretul actual al caietelor produse este returnat in campul denumit “CostActual” iar in campul “CostPropus” este afisat noul pret al caietelor daca

39

pretul unitar creste cu 10% (vezi codul SQL al interogarii “Q_AS_Produse” din BD AsistRom )➜

SELECT Produse.CodFirma, Produse.CodProdus, Produse.NumeProdus, Produse.PretUnitar, Produse.Cantitate, [PretUnitar] * [Cantitate] AS Cost, [Cost]*1.1 AS CostPropus FROM Produse;

In exemplul de mai jos sunt numarate produsele din tabelul “Produse” si este calculat pretul unitar mediu si pretul unitar maxim (vezi “Q_CountAvgMax_Produse” din BD AsistRom )➜

SELECT Count(*) AS NrTotalProduse, Avg(PretUnitar) AS [Pret Unitar Mediu], Max([PretUnitar]) AS [Pret Unitar Maxim] FROM Produse;

Includerea unui text intre valorile numerice returnate. In exemplul urmator sunt afisate pentru fiecare produs din tabelul “Produse” numele si pretul sau unitar (aflate in campurile “NumeProdus”, respectiv “PretUnitar”), separate de secventa ‘are urmatorul pret unitar’ (vezi “Q_ConcatText_Produse” din BD AsistRom )➜

SELECT NumeProdus, 'are urmatorul pret unitar’, PretUnitar FROM Produse; Definirea unui filtru de selectie pentru inregistrarile returnate. In exemplul urmator sunt afisate numele si prenumele clientilor absolventi ai Universitatii din Bucuresti ➜

SELECT [Prenume], [NumeClient] FROM [Clienti] WHERE [Studii] = "Univ.Bucuresti";

In exemplul urmator sunt afisate numele si prenumele clientilor din tabelul “Clienti” care au apelat la un centru de consultanta al carui cod se afla printre codurile din formularul deschis “CentreConsNoi” ➜

SELECT [Prenume], [NumeClient] FROM [Clienti] WHERE [CodCentru] = Forms![CentreConsNoi]![CodCentru];

Selectarea inregistrarilor in vederea efectuarii unui calcul. In exemplul urmator este calculat pretul unitar mediu numai pentru produsele al caror pret unitar depaseste valoarea 5; acest pret mediu este afisat in campul nou creat numit “PretulUnitarMediuPeste5” ➜

SELECT Avg([PretUnitar]) AS [PretulUnitarMediuPeste5] FROM [Produse] WHERE [PretUnitar] > 5;

Gruparea inregistrarilor in vederea efectuarii unui calcul la nivelul fiecarui grup de inregistrari, dupa ce au fost eliminate inregistarile care nu verifica un criteriu de selectie anumit. In exemplul urmator este calculat pretul unitar mediu al produselor al caror pret unitar depaseste valoarea 5 la nivelul fiecarei firme (vezi “Q_GroupBy_Where_Produse_Firma” din BD AsistRom )➜

SELECT Produse.CodFirma, Count(Produse.CodFirma) AS NumarProduse, Avg(Produse.PretUnitar) AS PretulUnitarMediuPeste5 FROM Produse

WHERE (((Produse.PretUnitar) > 5))

GROUP BY Produse.CodFirma; Gruparea inregistrarilor in vederea efectuarii unui calcul la nivelul fiecarui grup de inregistrari si efectuarea acestui calcul numai pentru grupurile de inregistrari care verifica un criteriu de selectie anumit.

40

In exemplul urmator este calculat numarul total de produse fabricat de fiecare firma, numai daca firma respectiva produce cel putin doua sortimente distincte (vezi “Q_GroupBy_Having_Produse” din BD AsistRom )➜ SELECT Produse.CodFirma, Count([Produse].[CodFirma]) AS NumarProduse

FROM Produse GROUP BY Produse.CodFirma HAVING Count ([CodFirma]) >= 2;

Clauza FROM

Efect Specifica tabelele sau interogarile care contin campurile enumerate in instructiunea SQL.

Sintaxa SELECT lista_de_campuri FROM expresie_tabel [, ...] [IN baza_de_dat e_externa] O instructiune SELECT care foloseste clauza FROM are urmatoarele parti: lista_de_campuri numele campurilor (inclusiv alias-uri) folosite in interogare si, eventual: functii predefinite SQL, predicate de selectie SQL (ALL, DISTINCT, DISTINCTROW, or TOP) sau alte optiuni ale instructiunii SELECT

expresie_tabel

o expresie care identifica unul sau mai multe tabele din care vor fi returnate date. Expresia poate fi un nume unic de tabel, numele unei interogari deja salvate sau o combinatie obtinuta prin oricare dintre cele 3 tipuri de asociere: INNER JOIN, LEFT JOIN, sau RIGHT JOIN;

baza_de_date_externa

drumul complet catre BD externa care contine toate tabelele din expresia_tabel.

Observatii Clauza FROM este obligatorie pentru orice instructiune SELECT; Ordinea numelor tabelelor in expresia-tabel nu este semnificativa;

Exemple Afisarea numelui si prenumelui clientilor din tabelul “Clienti” ➜

SELECT Prenume, NumeClient FROM Clienti;

Afisarea tuturor informatiilor despre clienti (deci a tuturor campurilor din tabelul “Clienti”) ➜

SELECT * FROM Clienti; Numararea clientilor (deci a valorilor din campul “CodCentru” din tabelul “Clienti”); rezultatul este depus in campul “NrTotalClienti” ➜

SELECT Count(CodCentru) AS NrTotalClienti FROM Clienti;

41

Calculul noului pret unitar al produselor firmelor-client in urma aplicarii unei cresteri de 10% (in tabelul “Produse” valorile din campul “PretUnitar” – preturile actuale – raman neschimbate)

SELECT Produse.CodFirma, Produse.CodProdus, Produse.NumeProdus, Produse.Cantitate, Produse.PretUnitar AS PretActual, PretUnitar * 1.1 AS PretPropus FROM Produse;

Evident interogarea poate fi parametrizata si poate returna diferite preturi unitare pentru diferite procente (vezi interogarea “QProduse_From” din BD AsistRom ) ➜

SELECT Produse.CodFirma, Produse.CodProdus, Produse.NumeProdus, Produse.Cantitate, Produse.PretUnitar AS PretActual, [Procent] AS [P%], [PretUnitar] * [Procent] AS PretPropus FROM Produse WHERE ((([Procent]) = [Indicati procentul]));

Inserarea unui text explicativ intre antetele unor campuri ➜ SELECT Firma.NumeFirma, 'are ', Firma.NrSalariati, 'salariati' FROM Firma;

Clauza IN

Efect Identifica tabele in orice BD externa creata cu o aplicatie compatibila cu MS Access: FoxPro, dBASE etc.

Sintaxa pentru identificarea unui tabel-destinatie [SELECT | INSERT] INTO destinatie IN {drum | ["drum" "tip"] | ["" [tip; DA TABASE = drum]]}

Sintaxa pentru identificarea unui tabel-sursa FROM expresie_tabel IN {drum| ["drum" "tip"] | ["" [tip; DATAB ASE = drum]]} O instructiune SELECT care contine o clauza IN are urmatoarele parti: destinatie numele bazei de date externe in care sunt inserate informatiile; expresie_tabel o expresie care identifica unul sau mai multe tabele din care vor fi returnate date. Expresia poate fi un nume unic de tabel, numele unei interogari deja salvate sau o

42

combinatie obtinuta prin oricare dintre cele 3 tipuri de asociere: INNER JOIN, LEFT JOIN, sau RIGHT JOIN;

drum drumul complet catre directorul sau fisierul care contine tabelul; tip extensia specifica aplicatiei cu care a fost creata BD externa, daca este alta decat MS Access.

Predicatele ALL, DISTINCT, DISTINCTROW, TOP Efect Specifica inregistrarile selectate de interogarile SQL Sintaxa SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]] FROM tabel O instructiune SELECT care foloseste aceste predicate are urmatoarele parti: ALL Este predicatul implicit. Programul selecteaza toate inregistrarile care indeplinesc criteriul specificat in instructiunea SQL. Urmatoarele instructiuni SQL sunt echivalente si returneaza toate inregistrarile din tabelul “CentreCons” (ordonate dupa valorile campului “CodCentru”)➜

SELECT ALL * FROM CentreCons ORDER BY CodCentru; SELECT * FROM CentreCons ORDER BY CodCentru;

DISTINCT Omite inregistrarile care contin aceleasi date in campurile selectate (pentru a fi incluse in RecordSet, valorile campului din instructiunea SELECT trebuie sa fie unice; daca apar mai multe campuri in instructiunea SELECT atunci combinatia valorilor lor pentru fiecare inregistrare trebuie sa fie unica). De exemplu, trebuie verificat daca fiecare centru de consultanta este activ (are cel putin un client). Vom folosi urmatoarele premize: in tabelul “Clienti” exista un camp numit “CodCentru” care contine codul centrului de consultanta la care s-a inregistrat clientul (deci, vom baza interogarea pe tabelul “Clienti”); o inregistrare dintr-un tabel este considerata fara duplicate (unica) numai in cazul in care combinatia valorilor din toate campurile sale este unica la nivelul intregului tabel (deci, dintre toate campurile tabelului “Clienti” vom include in interogare numai campul “CodCentru”); un camp dintr-un tabel este considerat cu valori unice numai daca oricare dintre valorile sale nu se repeta in nici-o inregistrare din tabel (deci, vom specifica in instructiunea SELECT predicatul DISTINCT). Prin urmare, se foloseste codul: ➜

SELECT DISTINCT CodCentru FROM Clienti; Daca se elimina predicatul DISTINCT atunci sunt returnate toate valorile (cu duplicate

cu tot) din campul “CodCentru”.

DISTINCTROW Omite inregistrarile duplicate, nu numai pe cele care contin campuri duplicate. Problema de mai sus, listarea centrelor de consultanta care au cel putin un client poate fi rezolvata – mai complicat - si astfel: stiind ca tabelul “CentreCons” nu contine inregistrari duplicate dar tabelul “Clienti” contine, se creeaza o interogare pe baza tabelelor “CentreCons” si “Clienti” si se foloseste o instructiune SELECT cu predicatul DISTINCTROW, adica ➜

SELECT DISTINCTROW NumeCentru FROM Clienti INNER JOIN CentreCons ON Clienti.CodCentru = CentreCons.CodCentru

43

ORDER BY NumeCentru; Daca se omite parametrul DISTINCTROW, interogarea produce, pentru fiecare centru, atatea inregistrari cati clienti are centrul respectiv.

TOP n [PERCENT] Returneaza un anume numar de inregistrari aflate pe primele sau pe ultimele locuri ale unui set de inregistrari specificate intr-o clauza ORDER BY.

Sa presupunem ca trebuie returnate primele 2 firme din Bucuresti care au cel mai mare numar de angajati (vezi “QFirma_Topn” din BD AsistRom ) ➜

SELECT TOP 2

NumeFirma, CodOras, NrSalariati FROM Firma

WHERE CodOras = "B" ORDER BY NrSalariati DESC;

Sa presupunem ca trebuie returnate primele 30% firme care au cel mai mare numar de angajati ➜

SELECT TOP 30 PERCENT

Firma.NumeFirma, Firma.NrSalariati FROM Firma

ORDER BY Firma.NrSalariati DESC;

Clauza GROUP BY Efect Combina intr-o inregistrare unica toate inregistrarile care au aceeasi valoare in campurile specificate in instructiunea SELECT; daca instructiunea SELECT contine si o functie predefinita SQL (SUM, COUNT etc.) este inclusa valoarea acesteia pentru fiecare dintre inregistrarile astfel create.

Sintaxa SELECT lista_de_campuri FROM expresie_tabel [WHERE criterii_de_selectie] GROUP BY lista_campurilor_grupate O instructiune SELECT care contine clauza GROUP BY are urmatoarele parti: lista_de_campuri reprezinta numele (inclusiv alias-urile) campurilor din tabelul de baza din care se extrag date si, eventual: functiile predefinite SQL, predicatele de selectie (ALL, DISTINCT etc.) si alte optiuni ale instructiunii SELECT;

expresie_tabel o expresie care identifica unul sau mai multe tabele ale caror date vor fi actualizate. Expresia poate fi un nume unic de tabel, numele unei interogari deja salvate sau o combinatie obtinuta prin oricare dintre cele 3 tipuri de asociere: INNER JOIN, LEFT JOIN, sau RIGHT JOIN;

criterii_de_selectie reprezinta criteriile de selectie; daca instructiunea SELECT comporta si o clauza WHERE atunci programul BD Microsoft Jet grupeaza valorile numai dupa ce aplica criteriile WHERE asupra inregistrarilor; lista_campurilor_grupate consta din numele a maximum 10 campuri folosite pentru gruparea inregistrarilor; ordinea de grupare este ordinea in care apar campurile in grila QBE. Exemple

44

Se creeaza o interogare simpla pe baza tabelului “Produse” fara a se introduce in grila QBE nici un camp ➜ se comuta in modul SQL View si se introduc pe rand instructiunile SQL de mai jos: Calculul pretului unitar mediu al tuturor caietelor produse de aceeasi firma ⇒⇒⇒⇒ se selecteaza campul “CodFirma” ➜ se aplica functia predefinita Avg pretului unitar ➜ se grupeaza inregistrarile dupa codul firmei ➜

SELECT CodFirma, Avg(PretUnitar) AS AvgOfPretUnitar FROM Produse

GROUP BY CodFirma; Calculul pretului unitar maxim pentru produsele din fiecare categorie ⇒⇒⇒⇒ se selecteaza campul “CodProdus” ➜ se aplica functia predefinita Max pretului unitar ➜ se grupeaza dupa codul produsului ➜

SELECT CodProdus, Max(PretUnitar) AS MaxOfPretUnitar

FROM Produse GROUP BY CodProdus; Calculul numarului de produse fabricate de fiecare firma ⇒⇒⇒⇒ se selecteaza campul “CodFirma” ➜ se aplica functia predefinita Count campului “CodProdus” ➜ se grupeaza dupa codul firmei ⇒⇒⇒⇒

SELECT CodFirma, Count(CodProdus) AS CountOfCodProdus FROM Produse GROUP BY CodFirma;

Clauza WHERE Efect Specifica inregistrarile din tabelele enumerate in clauza FROM care vor fi afectate de o instructiune SELECT, UPDATE sau DELETE.

Sintaxa SELECT lista_de_campuri FROM expresie_tabel WHERE criterii_de_selectie O instructiune SELECT care comporta o clauza WHERE are urmatoarele parti: lista_de_campuri numele campurilor (inclusiv alias-uri) folosite in interogare si, eventual: functii predefinite SQL, predicate de selectie SQL (ALL, DISTINCT, DISTINCTROW, or TOP) sau alte optiuni ale instructiunii SELECT;

expresie_tabel o expresie care identifica unul sau mai multe tabele ale caror date vor fi actualizate. Expresia poate fi un nume unic de tabel, numele unei interogari deja salvate sau o combinatie obtinuta prin oricare dintre cele 3 tipuri de asociere: INNER JOIN, LEFT JOIN, sau RIGHT JOIN;

criterii_de_selectie o expresie pe care trebuie s-o satisfaca o inregistrare pentru a fi returnata in dynaset; ea poate contine pana la 40 de operanzi combinati prin operatori logici.

Exemple

45

Afisarea firmelor care au mai mult de 50 salariati ➜ SELECT NumeFirma, NrSalariati FROM Firma WHERE NrSalariati >= 50;

Afisarea firmelor infiintate in anii 1996 si 1997 ➜

SELECT * FROM Firma WHERE Start BETWEEN #01/01/96# AND #12/31/97# ;

� Asa cum am subliniat si mai sus, specificarea datei calendaristice din acest exemplu convine intr-o BD creata in S.U.A; intr-o BD creata in Marea Britanie data trebuie specificata astfel: #31/12/96# . Astfel de dificultati pot fi ocolite prin utilizarea functiei predefinite DateValue care face automat conversiile impuse de setarile internationale:

SELECT * FROM Firma WHERE Start BETWEEN DateValue(#01/01/96#) AND DateValue(#12/31/97#);

Clauza HAVING Efect Specifica inregistrarile grupate care vor fi afisate printr-o instructiune SELECT care contine o clauza GROUP BY. Dupa ce clauza GROUP BY grupeaza inregistrarile, clauza HAVING afiseaza numai acele inregistrari grupate care satisfac criteriul introdus.

Sintaxa SELECT lista_de_campuri FROM expresie_tabel [WHERE criterii_de_selectie] GROUP BY lista_campurilor_grupate HAVING criterii_de_selectie_grup; O instructiune SELECT care contine clauza GROUP BY are urmatoarele parti: lista_de_campuri reprezinta numele (inclusiv alias-urile) campurilor din tabelul de baza din care se extrag date si, eventual: functiile predefinite SQL, predicatele de selectie (ALL, DISTINCT etc.) si alte optiuni ale instructiunii SELECT; expresie_tabel o expresie care identifica unul sau mai multe tabele ale caror date vor fi actualizate. Expresia poate fi un nume unic de tabel, numele unei interogari deja salvate sau o combinatie obtinuta prin oricare dintre cele 3 tipuri de asociere: INNER JOIN, LEFT JOIN, sau RIGHT JOIN;

criterii_de_selectie o expresie pe care trebuie s-o satisfaca o inregistrare pentru a fi returnata in dynaset; ea poate contine pana la 40 de operanzi combinati prin operatori logici; daca instructiunea SELECT comporta si o clauza WHERE atunci programul BD Microsoft Jet grupeaza valorile numai dupa ce aplica criteriile WHERE asupra inregistrarilor;

lista_campurilor_grupate consta din numele a maximum 10 campuri folosite pentru gruparea inregistrarilor; ordinea de grupare este ordinea in care apar campurile in grila QBE; criterii_de_selectie_grup reprezinta criteriile pe care trebuie sa le satisfaca o inregistrare grupata pentru a fi afisata; aceasta expresie poate contine pana la 40 de operanzi combinati prin operatori logici.

Observatie

46

Clauza HAVING este optionala.

Exemple Afisarea preturilor unitare maxime pe fiecare produs, indiferent de firma producatoare, daca acest pret nu depaseste valoarea 5 iar firma este o asociatie familiala (codul ei incepe cu A) ⇒⇒⇒⇒ se creeaza o interogare pe baza tabelului “Produse” ➜ se selecteaza produsele prin codul lor (precum si firma producatoare) ➜ se aplica functia predefinita Max datelor privind pretul unitar ➜ se grupeaza inregistrarile pe produse ➜ se afiseaza numai produsele cu pretul unitar maxim mai mic decat 5 si realizate de firme al caror cod incepe cu litera A ➜

SELECT Produse.CodProdus, Max(Produse.PretUnitar) AS PretMaxUnitar FROM Produse WHERE (((Left([CodFirma],1)) = "A")) GROUP BY Produse.CodProdus HAVING (((Max(Produse.PretUnitar)) <= 5));

Afisarea centrelor de consultanta care au cel putin un client nou ⇒⇒⇒⇒ se creeaza o interogare pe baza tabelului “Clienti” ➜ se selecteaza centrele de consultanta prin campul de cod ➜ se numara clientii noi prin insumarea valorilor booleene din campul “ClientNou” (constantele predefinite TRUE / FALSE fiind convertite la –1 / 0) ➜ se grupeaza pe centre de consultanta ➜ se afiseaza numai inregistrarile grupate care au o valoare nenula in campul “ClientNou” ➜

SELECT CodCentru, Sum(ClientNou) AS SumtOfClientNou FROM Clienti

GROUP BY CodCentru HAVING Sum(ClientNou) <> 0

Clauza ORDER BY

Efect Sorteaza, crescator sau descrescator, dupa unul sau mai multe campuri, inregistrarile returnate de o interogare.

Sintaxa SELECT lista_de_campuri FROM expresie_tabel WHERE criterii_de_selectie [ORDER BY camp1 [ASC | DESC ][, camp2 [ ASC | DESC ]][, ...]]] O instructiune SELECT care contine clauza ORDER BY are urmatoarele parti: lista_de_campuri reprezinta numele (inclusiv alias-urile) campurilor din tabelul de baza din care se extrag date si, eventual: functiile predefinite SQL, predicatele de selectie (ALL, DISTINCT etc.) si alte optiuni ale instructiunii SELECT; expresie_tabel o expresie care identifica unul sau mai multe tabele ale caror date vor fi actualizate. Expresia poate fi un nume unic de tabel, numele unei interogari deja salvate sau o combinatie obtinuta prin oricare dintre cele 3 tipuri de asociere: INNER JOIN, LEFT JOIN, sau RIGHT JOIN;

criterii_de_selectie o expresie pe care trebuie s-o satisfaca o inregistrare pentru a fi returnata in dynaset; ea poate contine pana la 40 de operanzi combinati prin operatori logici; daca

47

instructiunea SELECT comporta si o clauza WHERE atunci programul BD Microsoft Jet grupeaza valorile numai dupa ce aplica criteriile WHERE asupra inregistrarilor;

camp1, camp2 numele campurilor care servesc drept chei de sortare.

Exemple Afisarea clientilor firmei AsistRom in ordine alfabetica ➜

SELECT Prenume, NumeClient FROM Clienti ORDER BY NumeClient ASC;

Afisarea firmelor si clientilor firmei AsistRom in ordinea alfabetica a centrelor de consultanta si apoi a numelui de familie ➜

SELECT Prenume, NumeClient, CodCentru, CodFirma FROM Clienti ORDER BY CodCentru ASC, NumeClient ASC;

Evidentierea centrelor de consultanta cu cea mai buna activitate, ⇒⇒⇒⇒ afisarea centrelor de consultanta in ordinea descrescatoare a numarului de clienti ➜ SELECT Clienti.CodCentru, Count(Clienti.CodCentru) AS NrClienti FROM Clienti GROUP BY Clienti.CodCentru ORDER BY Count(Clienti.CodCentru) DESC;

Operatia INNER JOIN

Efect Combina inregistrarile din 2 tabele corelate daca au aceeasi valoare in campul de legatura.

Sintaxa minimala FROM tabel1 INNER JOIN tabel2 ON tabel1.c amp1 oper_relat tabel2.camp2 Operatia INNER JOIN are urmatoarele parti: tabel1, tabel2 numele tabelelor ale caror inregistrari sunt corelate; camp1, camp2 numele campurilor de legatura; daca nu sunt numerice ele trebuie sa fie de acelasi tip si sa contina acelasi fel de date dar nu trebuie sa aiba neaparat acelasi nume; oper_relat oricare dintre operatorii relationali: "=," "<," ">," "<=," ">=," sau "<>."

Observatii Se poate folosi o operatie INNER JOIN in orice clauza FROM. Acest tip de asociere,

asocierea interna, este cel mai comun. De exemplu, daca trebuie afisati numai clientii care s-au prezentat la un centru de consultanta, trebuie aplicata o operatie INNER JOIN pentru a crea o asociere interna. Daca trebuie afisate toate centrele de consultanta, indiferent daca au sau nu clienti, respectiv daca trebuie afisati toti clientii indiferent daca s-au prezentat deja la un centru de consultanta sau nu, trebuie aplicata o operatie LEFT JOIN respectiv RIGHT JOIN pentru a crea o asociere externa.

Operatia INNER JOIN nu poate fi aplicata campurilor de tip Memo sau OLE Object si nici campurilor numerice de tip diferit: unul Single, celalalt Double.

Exemple

48

Asocierea produselor cu numele patronului firmei producatoare pe baza campului “CodFirma”, prezent in ambele tabele (intre care nu exista o corelatie stabilita la nivelul BD) si gruparea acestora pe clienti si pe produse ➜

SELECT Produse.NumeProdus, Clienti.NumeClient FROM Produse INNER JOIN Clienti ON Produse.CodFirma = Clienti.CodFirma GROUP BY Clienti.NumeClient, Produse.NumeProdus; � Campul de legatura (aici”CodFirma”) nu apare in dynaset; pentru a-l afisa el trebuie inclus in lista de campuri a instructiunii SELECT alaturi de celelalte campuri.

Sintaxa operatiei pentru mai multe clauze ON SELECT lista_campuri FROM tabel1 INNER JOIN tabel2 ON tabel1.camp1 oper_relat tabel2.camp1 AND ON tabel1.camp2 oper_relat tabel2.camp2) OR ON tabel1.camp3 oper_relat tabel2.camp3)];

Sintaxa operatiei pentru mai multe instructiuni JOIN imbricate SELECT lista_campuri FROM tabel1 INNER JOIN (tabel2 INNER JOIN [( ]tabel3 [INNER JOIN [( ]tabelx [INNER JOIN ...)] ON tabel3.camp3 oper_relat tabelx.campx)] ON tabel2.camp2 oper_relat tabel3.camp3) ON tabel1.camp1 oper_relat tabel2.camp2;

Observatie O asociere externa (RIGHT JOIN sau LEFT JOIN) poate fi imbricata intr-o asociere interna, dar invers nu.

Exemplu Afisarea numelui complet al clientilor, al firmei si al centrului consultat ➜ SELECT Clienti.Prenume & " " & Clienti.NumeClient AS [Nume Client], Firma.NumeFirma AS [Nume Firma], CentreCons.NumeCentru AS [Nume Centru Cons] FROM (CentreCons INNER JOIN Clienti ON CentreCons.CodCentru = Clienti.CodCentru) INNER JOIN Firma ON Clienti.CodFirma = Firma.CodFirma;

Operatiile LEFT JOIN si RIGHT JOIN

49

Efect Combina inregistrarile din 2 tabele corelate folosite intr-o clauza FROM.

Sintaxa FROM tabel1 [ LEFT | RIGHT ] JOIN tabel 2 ON tabel1.camp1 oper_relat tabel2.camp2 Operatia LEFT | RIGHT JOIN are urmatoarele parti: tabel1, tabel2 numele tabelelor ale caror inregistrari sunt corelate; camp1, camp2 numele campurilor de legatura; daca nu sunt numerice ele trebuie sa fie de acelasi tip si sa contina acelasi fel de date dar nu trebuie sa aiba neaparat acelasi nume; oper_relat oricare dintre operatorii relationali: "=," "<," ">," "<=," ">=," sau "<>."

Exemple Afisarea tuturor firmelor, fie ca au produse fie ca nu au (relatia 1 – m se bazeaza pe campul “CodFirma”➜

SELECT DISTINCT Firma.NumeFirma, Produse.NumeProdus FROM Firma LEFTJOIN Produse ON Firma.CodFirma = Produse.CodFirma;

Afisarea tuturor produselor, fie ca este mentionata firma producatoare fie ca nu (aceeasi relatie ca mai sus) ➜

SELECT DISTINCT Firma.NumeFirma, Produse.NumeProdus FROM Firma RIGHT JOIN Produse ON Firma.CodFirma = Produse.CodFirma ORDER BY NumeFirma, NumeProdus;

Interogarea parametrizata Definitie O interogare parametrizata este o interogare a carei executie consta in afisarea unei casete-dialog predefinite in care utilizatorul poate introduce criteriul de selectie sau valoarea care trebuie sa apara intr-un camp. Se poate folosi aceasta facilitate si pentru criterii complexe: de exemplu, se poate crea o interogare parametrizata prin care sa se afiseze inregistrarile a caror data calendaristica este cuprinsa intre anumite limite. Interogarile parametrizate sunt comode pentru construirea formularelor si rapoartelor. De exemplu, un raport lunar al incasarilor se poate baza pe o interogare parametrizata; cand se da comanda de listare a raportului, SGBD afiseaza caseta-dialog solicitand luna la care trebuie sa se refere raportul; utilizatorul introduce informatia respectiva si raportul corespunzator lunii este listat. Pentru parametrizarea interogarilor cu criterii complexe se procedeaza analog, SGBD afisand atatea casete-dialog cate ii sunt necesare pentru stabilirea criteriului de interogare. Sa presupunem ca trebuie afisati clientii ai caror firme au fost infiintate intre 1995 si 1997. Pentru aceasta se creeaza mai intai o interogare simpla pe baza tabelului “Firma”; in linia Criteria a campului “Start” se introduce criteriul parametrizat Between [Data initiala:] And [Data finala:]; cand se executa interogarea se tasteaza in prima caseta-dialog valoarea 1/1/95 (1 ianuarie 1995) iar in a doua caseta-dialog valoarea 12/31/96 (31 decembrie 1996)

50

Tipuri de date SQL SGBD defineste urmatoarele 13 tipuri de date primare pentru a fi folosite in codul SQL (cu utilitate deosebita in crearea interogarilor parametrizate). Tip de data Memorare Continutul campului de momorie de acest tip BINARY 1 caracter

pe byte Orice tip de data; nu necesita nici o conversie; datele apar asa cum sunt introduse

BIT 1 byte Valori binare; campuri care nu pot contine decat una din 2 valori

TEXT 1 caracter pe byte

0 pana la 255 caractere

BYTE 1 byte 0 ≤ n ≤ 255 COUNTER 4 bytes Un numar intreg pe care programul BD Microsoft Jet il

mareste automat cu o unitate pentru fiecare inregistrare nou introdusa intr-un tabel; corespunde tipului de data Long al campurilor din tabele

SHORT 2 bytes - 32,768 ≤ n ≤ 32,767 LONG 4 bytes - 2,147,483,648 ≤ n ≤ 2,147,483,647 CURRENCY 8 bytes – 9.5 * 10 ** 15 ≤ x ≤ 9.5 * 10 ** 15 SINGLE 4 bytes Un real x in virgula mobila simpla precizie,

- - 3,40 E 38 ≤ x ≤ - - 1,40 E -45, daca x <0 1,40 E –45 ≤ x ≤ 3,40 E 38, daca x ≥ 0

DOUBLE 8 bytes Un real x in virgula mobila dubla precizie, - 1.79 E 308 ≤ x ≤ - 4.94 E – 324, daca x<0 4.94 E –324 ≤ x ≤ 1.79 E 308, daca x ≥ 0

DATETIME (vezi DOUBLE)

8 bytes O ora sau o data calendaristica cuprinsa intre anii 100 si 9999

LONGTEXT 1 caracter pe byte

0 pana la 1.2 gigabytes

LONGBINARY Dupa caz 0 pana la 1.2 gigabytes (pentru obiecte OLE) GUID 128 bits Un numar de identificare unic folosit in apelul procedurilor

memorate pe server

Declaratia PARAMETERS

Efect Specifica numele si tipul de date al fiecarui parametru dintr-o interogare parametrizata.

Sintaxa PARAMETERS nume tip_de_data [, nume tip_d e_data [, ...]] O declaratie PARAMETERS are urmatoarele parti: nume numele parametrului. El este asignat proprietatii Name a obiectului si folosit pentru identificarea parametrului respectiv in colectia Parameters. Acest nume apare in caseta-dialog creata si afisata de SGBD la executarea interogarii parametrizate. De aceea textele cu spatii trebuie incluse in paranteze drepte; tip_de_data un tip de data recunoscut de program.

Exemple Declararea unei liste de parametri ➜

51

PARAMETERS [Capital Social] Single, [Data Infiintarii Firmei] DateTime; Utilizarea parametrilor in clauzele WHERE si HAVING (vezi interogarea Qfirma_ParametersWH din BD AsistRom ) ➜

PARAMETERS [Capital Social] Single, [Data Infiintarii Firmei] DateTime; SELECT NumeFirma, Capital FROM Firma WHERE Capital > [Capital Social] AND Start >= [Data Infiintarii Firmei];

Afisarea tuturor clientilor fiecarui centru de consultanta ➜

PARAMETERS [Tastati codul centrului, ASE, POLI, CRV sau TIM:] Text; SELECT * FROM Clienti WHERE CodCentru = [Tastati codul centrului, ASE, POLI, CRV sau TIM:];

Afisarea produselor in functie de codul categoriei din care fac parte ➜

PARAMETERS [Dati codul categoriei:] Value; SELECT CodCategorie, NumeProdus, Count([Comenzi].CodComanda) AS

Tally FROM Produse INNER JOIN [Comenzi] ON Produse.CodProdus = [Comenzi].CodProdus GROUP BY CodCategorie, NumeProdus HAVING CodCategorie = [Dati codul categoriei:];

Interogarea de tip tabel intersectat (Crosstab Quer y) Definitie O interogare de tip tabel intersectat sintetizeaza datele in mod cu totul diferit fata de o interogarea simpla (de selectie) desi afiseaza rezultatul sub forma unei foi de date. Ea este tot o interogare pentru calcule, dar acestea sunt mai complexe, deoarece datele campului sunt grupate dupa 2 criterii simultan. De exemplu, daca trebuie numarati clientii in functie de subiectul solicitat (plan de afaceri, asistenta etc.) si de consultantul solicitat atunci o interogare de selectie ar duplica in mod inutil informatia, si asa greu de urmarit.

O interogare Crosstab procedeaza astfel:

efectueaza calculul dorit (sumare, numarare, valoare medie etc.) asupra datelor din campul corespunzator din tabelul de baza (aici campul “CodClient” din tabelul “Consultante”); vom numi acest camp camp principal,

identifica cele doua campuri ale caror valori – distincte la nivelul fiecarui camp – vor deveni valorile celor doua criterii astfel: valorile distincte ale primului camp devin antete de coloane, valorile distincte ale celui de-al doilea camp devin etichete de linii (aici, valorile campului “Subiect” : “Asistenta”, “Consultanta”, “PlanDeAfaceri”, respectiv valorile campului “CodConsultant” : “AZ”, “DM”, “IU”, “MS”); vom numi aceste campuri camp-antete respectiv camp-etichete,

afiseaza la intersectia liniei i cu coloana j rezultatul care verifica valoarea din eticheta liniei i si valoarea din antetul coloanei j (aici, la intersectia liniei “AZ “cu coloana “Asistenta” este afisat numarul clientilor care au solicitat “Asistenta” consultantului “AZ”, de exemplu).

� Rezultatul obtinut cu o interogare Crosstab se poate obtine mai simplu cu ajutorul unei tabele pivot din Ms Excel, importata sub forma unui tabel in BD Access. Pentru crearea tabelei pivot se poate apela procedura de asistenta PivotTable Wizard. In concluzie, o interogare Crosstab genereaza grupuri imbricate de inregistrari; ea are nevoie de cel putin 3 campuri: unul pentru efectuarea calculelor, unul pentru antetele de coloane si unul, doua sau trei campuri pentru etichetele de linii. Evident, este

52

preferabil ca in campul-antete sa existe un numar mic de valori distincte, altfel rezultatele interogarii sunt greu de inteles.

Sintaxa instructiunii TRANSFORM este TRANSFORM functie_predefinita_Totals SELECT_cla uze PIVOT camp_pivot [IN (lista_de_valori)]; unde: functie_predefinita_Totals este una dintre cele 9 functii din categoria Total; argumentul ei este numele campului principal din interogarea Crosstab, clauze reprezinta clauzele dintr-o instructiune SELECT obisnuita camp_pivot reprezinta campul-antete, lista_de_valori reprezinta o lista optionala de valori literale de utilizat ca antete de coloana atunci cand nu toate valorile din campul-antete convin sau cand ordinea alfanumerica de afisare a lor nu convine.

Exemplu Codul SQL al interogarii Crosstab este:

TRANSFORM Count(Consultante.CodClient) AS [The Value] SELECT Consultante.CodConsultant, Count(Consultante.CodClient) AS [Total Of CodClient] FROM Consultante GROUP BY Consultante.CodConsultant PIVOT Consultante.Subiect;

Interogarile – actiune Definitie O interogare-actiune este o interogare care modifica mai multe inregistrari printr-o singura operatie (nu determina afisarea unor informatii pe ecran ci numai modificarea datelor primare depuse in tabelele BD). Prin urmare, interogarile-actiune nu modifica structura tabelelor, ci numai informatia continuta de acestea.

Clasificare Exista 4 tipuri de interogari-actiune: • interogarea Update (Actualizare): modifica valorile din campul specificat intr-un tabel sau in mai multe tabele; • interogarea MakeTable (CreareTabel): creeaza un tabel nou cu ajutorul unei parti sau a tuturor inregistrarilor dintr-un tabel sau din mai multe tabele; • interogarea Append (Adaugare): adauga un grup de inregistrari aflate intr-un tabel sau in mai multe tabele la baza unui tabel sau a mai multor tabele; • interogarea Delete (Stergere): sterge un grup de inregistrari dintr-un tabel sau din mai multe tabele simultan.

Interogarile-actiune trebuie folosite ori de cate ori trebuie gestionata o selectie bine definita de inregistrari printr-o singura manevra. Rezultatul obtinut prin utilizarea interogarilor-actiune poate fi generat si printr-un program VBA care sa parcurga fiecare inregistrare din tabel / BD, sa verifice indeplinirea unei conditii specificate si sa execute actiunea respectiva (adaugare, stergere etc.) in functie de indeplinirea sau nu a conditiei respective (asa cum procedeaza de altfel toate SGBD care nu dispun de facilitati SQL).

Observatii Executarea unei interogari-actiune poate fi intrerupta prin comanda CTRL + BREAK.

53

Interogarea Update (Actualizare) Efect Determina modificarea valorilor din campul specificat aflat intr-un tabel sau in mai multe tabele. Noua valoare poate sa se bazeze pe continutul curent al campului de modificat (sau al altor campuri) sau poate fi o constanta literala. De exemplu, pot fi majorate cu 10% preturile tuturor produselor din tabelul “Produse” sau pot fi setate toate pe valoarea 5. Utilizare Actualizarea unui numar mare de campuri aflate in acelasi tabel. Actualizarea unor campuri care contin aceeasi informatie, aflate in tabele asociate.

Sintaxa instructiunii UPDATE UPDATE expresie_tabel SET lista_de_valori WHERE criterii_de_selectie; unde: expresie_tabel o expresie care identifica unul sau mai multe tabele ale caror date vor fi actualizate. Expresia poate fi un nume unic de tabel, numele unei interogari deja salvate sau o combinatie obtinuta prin oricare dintre cele 3 tipuri de asociere: INNER JOIN, LEFT JOIN, sau RIGHT JOIN;

lista_de_valori o expresie formata din numele campurilor de actualizat si valorile de actualizare asociate fiecaruia (constante sau expresii) criterii_de_selectie o expresie pe care trebuie s-o satisfaca o inregistrare pentru a fi actualizata; ea poate contine pana la 40 de operanzi combinati prin operatori logici. Numai inregistrarile care satisfac criteriile de selectie vor fi actualizate.

O interogare Update actioneaza si asupra tabelelor asociate cu tabelele pe care este proiectata sa le actualizeze, daca optiunea Cascade Update Related Fields a fost selectata. � Interogarea Update nu genereaza un dynaset care sa poata fi consultat inaintea executarii interogarii (o interogare Update se poate afisa numai in modul SQL View, pentru a introduce codul SQL al interogarii, sau in modul Design View, pentru a defini interogarea in grila QBE si pentru a o executa). Operatia de actualizare poate fi intrerupta (CTRL + BREAK) dar nu poate fi anulata. Ca atare, examinarea inregistrarilor selectate pentru actualizare inainte de efectuarea operatiei reprezinta ultima posibilitate de corectare a unor eventuale erori. Informatia anterioara poate fi recuperata numai daca s-au facut copii de siguranta ale tabelelor implicate in operatia de actualizare sau chiar ale intregii BD (cazul actualizarii tabelelor asociate). O alta metoda de prevenire a actualizarilor eronate este crearea in prealabil a unei interogari de selectie care sa contina inregistrarile de modificat.

Exemple Actualizarea mai multor campuri simultan. De exemplu, pretul produselor firmei AF Dana a scazut cu 15% ceea ce a permis ca productia sa creasca la 1000 bucati din fiecare sortiment (pentru siguranta, inainte de definirea interogarii Update, tabelul “Produse” a fost salvat cu numele “ProduseCopie” ; vezi interogarea “Q_Update_And_Produse” din BD) ➜

UPDATE Produse SET Produse.PretUnitar = PretUnitar * 0.85, Produse.Cantitate = 1000 WHERE (((Produse.CodFirma) = "AFD"));

54

� Sa presupunem ca operatia de actualizare nu s-a incheiat cu rezultatul asteptat si prin urmare, trebuie recuperat tabelul de baza in forma anterioara, cu ajutorul copiei facute. Atunci, este preferabil ca refacerea tabelului sa se realizeze nu prin redenumirea copiei cu numele tabelului ci prin copierea informatiei din tabelul-copie in tabelul initial. Motivul este acela ca tabelul initial nu si-a alterat nici structura (deci proprietatile campurilor, indecsii etc.) si nici – mai ales – relatiile cu celelate obiecte din BD. Actualizarea unor campuri si tabele asociate. Sa presupunem ca toate firmele consultante aflate in Bucuresti au urmat exemplul firmei AF Dana. Ca atare, are loc aceeasi operatie de actualizare dar criteriul de selectie se schimba: el se bazeaza pe relatia 1 – m dintre tabelele “Firma” si “Produse” stabilita la nivelul campului “CodFirma” ➜ UPDATE Produse INNER JOIN Firma ON Produse.CodFirma = Firma.CodFirma SET Produse.PretUnitar = PretUnitar * 0.85, Produse.Cantitate = 1000 WHERE (((Firma.CodOras) = "B"));

Actualizarea campurilor cu criterii multiple.

Sa presupunem ca trebuie sa actualizam pretul unitar si nivelul productiei cu valorile de mai sus numai pentru firmele din Bucuresti care au un capital social sub 500 milioane lei ➜

UPDATE Firma INNER JOIN Produse ON Firma.CodFirma = Produse.CodFirma

SET Produse.PretUnitar = PretUnitar * 0.85, Produse.Cantitate = 1000 WHERE (((Firma.CodOras) = "B") AND ((Firma.Capital) <= 500));

Interogarea MakeTable (CreareTabel) Efect Creeaza un tabel nou din campurile / inregistrarile selectate, pentru a fi depus in BD curenta sau pentru a fi exportat in alte BD (create cu SGBD sau cu alte aplicatii). Pot fi folosite campuri / inregistrari dintr-un singur tabel / din mai multe tabele asociate / dintr-o interogare (noul tabel nu va mai fi insa normalizat). Utilizare Crearea unui tabel nou in aceeasi BD. De exemplu, in BD AsistRom exista tabelele “Clienti” si “Firma” care au aceeasi informatie in campurile “CodClient”, “CodFirma”, “NumeClient”, “Adresa”, “CodOras”, “CodPostal”, “NrTelefon” (fiind firme mici, adresa firmei coincide cu adresa de acasa a clientului); ca atare, dupa crearea tabelului “Clienti” tabelul “Firma” poate fi creat cu o interogare MakeTable bazata pe informatia din campurile comune celor 2 tabele. Crearea unei copii a unui tabel. Rezultatul obtinut este echivalent cu cel obtinut – mult mai rapid - prin utilizarea comezii FILE ➜ SAVE AS sau a comenzilor EDIT ➜ COPY si EDIT ➜ PASTE.

55

Crearea unui tabel de siguranta in care sa fie depuse inregistrarile vechi, devenite inutile, care urmeaza sa fie sterse.

De exemplu, inregistrarile din tabelul “Produse” corespunzatoare produselor iesite din fabricatie pot fi depuse intr-un tabel creat astfel, inainte de a fi sterse din tabelul “Produse”. Crearea unui tabel temporar de catre un utilizator intr-o BD accesata in retea. Sa presupunem ca BD AsistRom este folosita in retea; evident, expertii fiecarui centru de consultanta sunt in primul rand interesati de informatia privitoare la proprii clienti. Ei isi pot crea un tabel temporar extragand din tabelul “Clienti” inregistrarile care au in campul “CodCentru” codul centrului respectiv si pot crea cu ele un tabel nou cu ajutorul unei interogari MakeTable, la care pot renunta oricand. Daca operatiile de creare de tabele temporare si de stergere a acestora (cu instructiunea SQL DROP TABLE nume_tabel) se repeta frecvent atunci se impune compactarea cu regularitate a BD Crearea rapoartelor care sa afiseze informatii inregistrate la un anumit moment de

timp. De exemplu, trebuie afisate firmele care au solicitat asistenta firmei AsistRom chiar in anul in care s-au infiintat. Se poate, evident, crea o interogare pe baza tabelului “Firma” si apoi un raport bazat pe aceasta interogare. Raportul si interogarea vor contine insa numai firmele create in 1997, si - intrucat ele se bazeaza pe datele curente din tabelul “Firma” – nu permit obtinerea informatiei similare cu privire la anul 1996, 1995 etc. Stiind ca va fi solicitat un astfel de raport anual se poate crea o interogare MakeTable pe baza tabelului “Firma” in fiecare an pe 31 Decembrie (filtrand inregistrarile dupa campul “Start”). Apoi se poate crea raportul solicitat pe baza tabelului asociat anului respectiv, iar tabelul provizoriu poate fi sters sau pastrat. Accelerarea crearii formularelor si rapoartelor bazate – prin intermediul unei interogari -

pe mai multe tabele. Fiecare afisare a raportului presupune executia interogarii, deci consuma timp. Este preferabila extragerea inregistrarilor necesare din tabelele respective si depunerea lor intr-un tabel nou cu ajutorul unei interogari MakeTable si bazarea raportului pe acest tabel. Dezavantajul este ca tabelul nou creat nu se actualizeaza automat la modificarea tabelelor din care si-a extras inregistrarile.

Sintaxa instructiunii SELECT … INTO SELECT camp1[, camp2[, ...]] INTO tabel_nou [IN baza_de_date_externa] FROM expresie_tabel unde: camp1, camp2 numele campurilor care trebuie copiate in noul tabel; tabel_nou numele noului tabel care trebuie creat baza_de_date_externa calea catre o BD externa expresie_tabel o expresie care identifica unul sau mai multe tabele din care provin datele pentru crearea noului tabel. Expresia poate fi un nume unic de tabel, numele unei interogari deja salvate sau o combinatie obtinuta prin oricare dintre cele 3 tipuri de asociere: INNER JOIN, LEFT JOIN, sau RIGHT JOIN;

Observatii

56

Datele din tabelul nou creat mostenesc numai tipul de data si dimensiunea campurilor din tabelul de baza; ele nu mostenesc insa si celelalte proprietati ale campurilor si nici setarea cheii primare.

Numele dat noului tabel nu trebuie sa coincida cu numele unui tabel existent deoarece atribuirea numelui respectiv pentru noul tabel presupune stergerea tabelului deja existent.

Exemple Crearea unei copii de siguranta pentru tabelul “Consultante”, denumita “ConsultanteBk” ➜

SELECT * INTO [ConsultanteBk] FROM Consultante; Crearea unei copii de siguranta pentru acelasi tabel “Consultante” cu pastrarea numelui si depunerea lui in alta BD Access, denumita “CopieBD” ➜

SELECT Consultante.* INTO Consultante IN CopieBD.mdb FROM Consultante;

Crearea unui tabel temporar, denumit “ClientiPolizu”, cu clientii centrului de consultanta Polizu ➜

SELECT Clienti.Prenume, Clienti.NumeClient INTO ClientiPolizu FROM Clienti WHERE (((Clienti.CodCentru) = "POLI"));

Crearea unui nou tabel, numit “SolicitariCons”, pe baza tabelului “Consultante” si a unui tabel creat ad-hoc, numit “Experti” si constand din 2 campuri: “CodExpert” si “NumeExpert” (cele 2 tabele se afla in relatia 1 – m);

SELECT Consultante.*, Experti.NumeExpert INTO SolicitariCons FROM Experti INNER JOIN Consultante ON Experti.CodExpert = Consultante.CodConsultant;

Interogarea Append (Adaugare) Efect Adauga inregistrari la baza unui tabel.

Utilizare Adaugarea unui grup de inregistrari aflate intr-un tabel sau in mai multe tabele la baza unui tabel sau a mai multor tabele (de exemplu, trebuie adaugati noi clienti in tabelul “Clienti” iar informatiile despre acestia pot fi importate sub forma unui tabel din alta BD – eventual creata cu alta aplicatie –. Pentru a nu a avea doua tabele “Clienti” si pentru a evita reintroducerea lor, aceste date pot fi adaugate la baza tabelului “Clienti” cu ajutorul unei interogari Append); Adaugarea unui grup de inregistrari aflate intr-un tabel la baza unui tabel sau a mai

multor tabele chiar si atunci cand acestea nu au acelasi numar de campuri: interogarea Append adauga inregistrari cu aceeasi structura dar introduce informatie numai in campurile care corespund (de exemplu, tabelul noilor clienti importat in BD curenta nu contine campurile “NrSalariati” si “ClientNou”: interogarea va adauga inregistrarile respective lasand aceste campuri vide);

Adaugarea unui grup de inregistrari filtrate, adica numai a acelor inregistrari care au intr-un camp anumite valori (de exemplu, trebuie adaugati numai clientii unui anumit centru de consultanta).

Sintaxa instructiunii INSERT INTO cazul adaugarii unei singure inregistrari INSERT INTO tabel_destinatie [(camp1[, camp2[ , ...]])] VALUES (valoare1[, valoare2[, ...]);

57

cazul adaugarii mai multor inregistrari odata: INSERT INTO tabel-destinatie [IN baza_de_da te_externa] [(camp1[, camp2[, ...]])] SELECT [tabel-sursa.]camp1[, camp2[, ...] FROM expresie_tabel; unde: tabel-destinatie numele tabelului caruia/ interogarii careia i se adauga inregistrari; baza_de_date_externa calea catre o BD externa; tabel_sursa numele tabelului / interogarii din care fac parte inregistrarile care se adauga; camp1, camp2 numele campurilor-destinatie, daca urmeaza argumentului tabel-destinatie, respectiv numele campurilor-sursa, daca urmeaza argumentului tabel-sursa; expresie_tabel o expresie care identifica unul sau mai multe tabele din care provin datele pentru crearea noului tabel. Expresia poate fi un nume unic de tabel, numele unei interogari deja salvate sau o combinatie obtinuta prin oricare dintre cele 3 tipuri de asociere: INNER JOIN, LEFT JOIN, sau RIGHT JOIN;

valoare1, valoare2 valorile de inserat in campurile specificate ale noii inregistrari; ordinea este esentiala: valoare1 se introduce in camp1, valoare2 se introduce in camp2 etc. Valorile trebuie separate prin virgule iar constantele literale trebuie incluse intre ghilimele.

Exemple Adaugarea unei singure inregistrari. Sa presupunem ca in tabelul “Produse” trebuie adaugat un nou produs executat de firma “Mara SNC”; se foloseste urmatoarea interogare Append (vezi “Q_Append1Rec_Produse”) ➜

INSERT INTO Produse

(CodFirma, CodProdus, NumeProdus, PretUnitar, Cantitate)

VALUES (“MSNC”, “c2”, “caiet matematica”, 6, 600);

Adaugarea tuturor inregistrarilor dintr-un tabel-sursa. Sa presupunem ca mai multi utilizatori lucreaza la crearea tabelului “Produse”; astfel, pe langa tabelul “Produse”, in BD AsistRom exista si tabelul “AlteProduse”, ambele avand aceeasi structura; pentru a introduce informatia din tabelul “AlteProduse” in tabelul “Produse” fara a o retasta se foloseste urmatoarea interogare Append ➜

INSERT INTO Produse SELECT * FROM AlteProduse; Adaugarea acelor inregistrari din tabelul-sursa care verifica un criteriu de selectie. Sa presupunem ca in BD AsistRom exista inca un tabel cu patroni de firme mici si mijlocii care au trimis o scrisoare de solicitare de asistenta, numit “ClientiPotentiali”. Acest tabel are aceeasi structura ca tabelul “Clienti” dar are un camp suplimetar numit “Solicitare”, de tip boolean, cu valoarea implicita No. Periodic, acei clienti din tabelul “ClientiPotentiali” care au si solicitat asistenta (campul “Solicitare” a primit valoarea Yes) trebuie adaugati tabelului “Clienti” ➜

INSERT INTO Clienti SELECT ClientiPotentiali.* FROM ClientiPotentiali WHERE Solicitare =

Yes;

Interogarea Delete (Stergere)

58

Efect Determina stergerea unui grup de inregistrari dintr-un tabel sau din mai multe tabele simultan. O interogare Delete nu actioneaza asupra campurilor ci asupra inregistrarilor in totalitate (de exemplu, pot fi sterse printr-o singura operatie toate informatiile referitoare la centrele de consultanta din tabelul “CentreCons” care nu au nici un client); Utilizare Stergerea unui grup de inregistrari dintr-un singur tabel. Stergerea unui grup de inregistrari din mai multe tabele aflate in relatia 1 – 1 sau 1 – m

atunci cand optiunea Cascade Delete Related Fields a relatiei nu a fost selectata (daca insa criteriile de selectie se refera atat la campuri din tabelul “1” cat si la campuri din tabelul “m” atunci interogarea trebuie executata de 2 ori deoarece ea nu poate sterge printr-o singura operatie atat inregistrarile din tabelul principal cat si pe cele din tabelul secundar).

Sintaxa instructiunii DELETE DELETE [tabel.*] FROM tabel WHERE criterii_de_selectie unde: tabel numele optional al tabelului din care se sterg inregistrari tabel numele obligatoriu al tabelului din care se sterg inregistrari criterii_de_selectie o expresie pe care trebuie s-o satisfaca o inregistrare pentru a fi stearsa; ea poate contine pana la 40 de operanzi combinati prin operatori logici. Numai inregistrarile care satisfac criteriile de selectie vor fi sterse.

Exemplu Stergerea inregistrarilor dintr-un tabel independent care verifica un criteriu de selectie. De exemplu, trebuie sterse inregistrarile referitoare la produsele cu pret unitar mai mare decat 6 unitati ➜

DELETE * FROM Produse WHERE PretUnitar >= 6;

Interogarile specifice SQL Definitie O interogare SQL este o interogare care modifca insasi structura unui tabel. Ea nu poate fi creata decat cu ajutorul unei instructiuni SQL .

Clasificare Exista urmatoarele tipuri de interogari SQL: • interogarea Union (de combinare): combina 2 sau mai multe tabele; • interogarea PassThrough (de transferare): permite aplicatiei SGBD sa transmita

altui SGBD instructiuni SQL si sa primeasca inapoi inregistrari; • interogarea DataDefiniton (de definire a datelor): defineste sau modifica structura

unui tabel.

Interogarea Union (Combinare) Efect

Combina campurile (coloanele) corespunzatoare din 2 sau mai multe tabele sau interogari intr-un singur camp (coloana) cu ajutorul unei operatii UNION.

De exemplu, daca fiecare centru de consultanta isi gestioneaza propriile tabele “Clienti”, “Firma”, “Consultante”, “Produse”, acestea trebuie fuzionate periodic in tabele unice; pentru aceasta, se creeaza o interogare Union care combina toate tabelele

59

“Clienti”, “Firma”, Consultante”, respectiv “Produse” in un singur tabel “Clienti” , “Firma”, Consultante”, respectiv “Produse”; apoi, pe baza respectivelor interogari, se creeaza noi tabele cu ajutorul interogarilor MakeTable.

Sintaxa operatiei UNION [TABLE] query1 UNION [ALL] [TABLE] query2 [UNION [ALL] [TABLE] queryn [ ... ]] unde query1-n pot fi: o instructiune SELECT, numele unei interogari sau numele unui tabel precedat de cuvantul-cheie TABLE.

Exemple Cea mai simpla interogare Union (afiseaza clientii si furnizorii unei firme) ➜

TABLE Clienti UNION TABLE Furnizori; Folosirea criteriilor de selectie in interogarile-argument (de exemplu, afisarea clientilor din Timisoara si a furnizorilor din Brasov) ➜

SELECT [NumeClient], [NumeFirma] FROM Clienti WHERE [CodOras] = “TM”

UNION SELECT [NumeFurnizor], [NumeFirma] FROM Furnizori WHERE [CodOras] = “BV”;

� In selectarea inregistrarilor returnate de interogarile-argument pot diferi nu numai valorile de selectie dar si criteriile, cu conditia ca numele campurilor sa provina din prima interogare-argument (din prima instructiune SELECT). Sortarea rezultatului intors de interogarea Union (de exemplu, afisarea clientilor si furnizorilor in ordinea alfabetica a orasului in care isi au sediul) ➜

SELECT [NumeFirma], [CodOras] FROM [Clienti] UNION SELECT [NumeFirma], [CodOras] FROM [Furnizori] ORDER BY [CodOras];

Redenumirea campurilor in interogarea Union (in interogarea Union antetele campurilor de nume pentru firme au alt nume) ➜

SELECT [NumeFirma] AS [NumeFirma Client/Furnizor], [CodOras] FROM [Clienti] UNION SELECT [NumeFirma] AS [NumeFirma Client/Furnizor], [CodOras] FROM [Furnizori];

Folosirea tuturor acestor clauze intr-o interogare Union ➜

SELECT NumeFirma, Adresa, “NumeFurnizor” AS Nume FROM Furnizori

WHERE CodOras = “CR” UNION SELECT NumeFirma, Adresa, “NumeClient” AS Nume FROM

Clienti WHERE CodOras = “CR” ORDER BY CodOras, Sursa;

60

Returnarea inregistrarilor duplicate intr-o interogare Union (de exemplu, returnarea tuturor inregistrarilor din cele doua tabele “Clienti” si “Furnizori”, inclusiv duplicatele pentru cazul in care aceeasi firma este si client si furnizor) ➜

SELECT [NumeFirma], [CodOras] FROM [Clienti] UNION ALL SELECT [NumeFirma], [CodOras] FROM [Furnizori];

Returnarea tuturor inregistrarilor din primul tabel si numai a unui set de inregistrari selectate din al doilea (vezi interogarea “Q_UnionTS_OraseRef_OraseRef2” din BD AsistRom )➜

TABLE [OraseRef] UNION SELECT * FROM OraseRef2 WHERE CodOras >= "a" AND CodOras <=

"k" ORDER BY CodOras DESC;

� Pentru a combina tabelul “OraseRef” cu tabelul “OraseRef2” integral se poate folosi oricare dintre interogarile Union de mai jos (vezi in BD AsistRom interogarile “Q_UnionTT_OraseRef_OraseRef2” si “Q_Union_TS_OraseRef_OraseRef2”) ➜

TABLE [OraseRef] UNION TABLE [OraseRef2]; TABLE [OraseRef] UNION SELECT * FROM OraseRef2;

Interogarea PassThrough (de transferare)

Efect Trimite comenzi direct bazelor de date accesabile din SGBDprin protocolul ODBC (Object Database Connectivity Protocole = un protocol standard de accesare a informatiei de pe serverele de baze de date SQL, de exemplu, de pe Microsoft SQL Server) intr-un format compatibil cu serverul respectiv. Utilizare • Obtinerea de informatii de pe serverul accesat; • Executarea procedurilor memorate pe serverul ODBC accesat. Pentru crearea unei interogari PassThrough, a se vedea informatiile Help oferite de aplicatia MS Access.

Interogarea DataDefinition (de definire date)

Efect Creeaza sau modifica obiecte ale BD (tabele SGBD sau tabele SQL Server). Interogarea DataDefinition creeaza structura noului tabel; pentru a-l completa cu date se poate utiliza interogarea-actiune Append (cate una pentru fiecare inregistrare). Sintaxa instructiunii CREATE INDEX CREATE [ UNIQUE ] INDEX nume_index ON nume_tabel (nume_camp [ASC | DESC][, n ume_camp [ASC | DESC], . .]) [WITH { PRIMARY | DISALLOW NULL | IGNOR E NULL }] unde: nume_index numele indexului care trebuie creat; nume_tabel numele tabelului din BD curenta care va contine indexul; nume_camp

61

numele campului care trebuie indexat.

Observatii si exemple Pentru a crea un index dintr-un singur camp trebuie specificat numele campului intre

paranteze, dupa numele tabelului. Pentru a crea un index dintr-un grup de campuri trebuie specificate numele tuturor campurilor din grup.

Implicit, indexul creat este ordonat creascator; daca trebuie creat un index ordonat descrescator trebuie specificata optiunea DESC in clauza ON.

CREATE INDEX AltIndex ON Experti ([Nume], [Prenume]); CREATE INDEX IndexCalend ON Experti (DataNasterii, DataAngajarii);

Cu predicatul UNIQUE se defineste campul /grupul de campuri specificat ca index cu valori unice (daca in campul /grupul de campuri specificat exista valori duplicate, inregistrarile respective vor fi sterse automat de catre MS Access);

Cu predicatul PRIMARY se defineste campul /grupul de campuri specificat drept cheie primara pentru tabelul respectiv (daca in campul /grupul de campuri specificat exista valori duplicate sau nule, inregistrarile respective vor fi sterse automat de catre MS Access; de asemenea, daca tabelul are deja o cheie primara aceasta nu va fi inlocuita cu noua cheie ci operatia definita de instructiunea ALTER TABLE respectiva nu se va realiza);

Clauza optionala WITH intr-o instructiune CREATE INDEX impreuna cu una dintre urmatoarele 2 optiuni permite specificarea regulilor de validare a datelor. Astfel:

optiunea DISALLOW NULL nu permite introducerea valorilor nule in campul / campurile de index al unei noi inregistrari;

optiunea IGNORE NULL nu permite includerea in index a inregistrarilor cu valori nule in campul / campurile de index; In exemplul urmator este creat un index cu valori unice si nenule din valorile campului “CodExpert” al tabelului “Experti” din BD AsistRom ➜

CREATE UNIQUE INDEX IndexExperti ON Experti (CodExpert) WITH DISALLOW NULL;

Instructiunea CREATE INDEX este foarte utila pentru consultarea unui tabel dintr-o BD aflata pe serverul SQL. Ea permite construirea unui pseudoindex pentru un tabel dintr-o BD ODBC legat de BD curenta. Tabelele care nu au index sunt read only. Crearea acestui pseudoindex nu le afecteaza pentru ca operatia nu este vizibila pentru ele.

In exemplul urmator este creat un index pentru un tabel ODBC legat de BD AsistRom (se presupune ca tabelul respectiv a fost deja legat de BD) ➜

CREATE UNIQUE INDEX CodComanda ON ComenziDetODBC (CodComanda);

Sintaxa instructiunii DROP DROP {TABLE nume_ tabel | INDEX nume_index ON nume_tabel} unde: nume_tabel numele tabelului care trebuie sters, respectiv al tabelului din care trebuie sters un index; nume_index numele indexului care trebuie sters din tabel.

Observatii Tabelul care trebuie sters sau din care trebuie sters un index trebuie in prealabil inchis. Pentru a putea sterge un tabel / un index trebuie sterse toate legaturile care il implica. Pentru stergerea unui index se mai poate folosi si instructiunea ALTER TABLE

Exemple

62

Stergerea unui tabel ➜ DROP TABLE ProduseB; Stergerea unui index ➜

DROP INDEX IndexExperti ON Experti;

Sintaxa clauzei CONSTRAINT O restrictie (constraint) este similara unui index dar poate fi folosita si pentru stabilirea relatiei cu un alt tabel. Aceasta clauza poate fi folosita ininstructiunile CREATE TABLE si ALTER TABLE pentru a crea sau a elimina restrictii. Exista 2 tipuri de clauze CONSTRAINT: pentru un singur camp si pentru un grup de campuri. Sintaxa clauzei CONSTRAINT pentru un singur camp CONSTRAINT nume_restrictie {PRIMARY KEY | U NIQUE | NOT NULL | REFERENCES nume_tabel_secundar [(nume_camp_secundar1, nume_camp_secundar2)]} Sintaxa clauzei CONSTRAINT pentru mai multe campuri CONSTRAINT nume_restrictie {PRIMARY KEY (cheie_primara1[, cheie_primara2 [, ...]]) | UNIQUE (unic1[, unic2 [, ...]]) | NOT NULL (nenul1[, nenul2 [, ...]]) | FOREIGN KEY (ref1[, ref2 [, ...]]) REFERENCES nume_ tabel_secundar [(nume_camp_secundar1 [, nume_camp_secundar2 [, ...]])]} unde: nume_restrictie numele restrictiei care trebuie creata; cheie_primara1, cheie_primara2 numele campului / campurilor care trebuie definite drept cheie primara; unic1, unic2 numele campului / campurilor care trebuie definite ca avand valori unice; nenul1, nenul2 numele campului / campurilor care trebuie definite ca neavand valori nule; ref1, ref2 numele campului / campurilor de cheie secundara care se afla in relatie cu campuri din alt tabel; nume_tabel_secundar numele tabelului secundar care contine campurile nume_camp_secundar1, nume_camp_secundar2; nume_camp_secundar1, nume_camp_secundar2 numele campului / campurilor din tabelul secundar specificat prin ref1, ref2; aceasta clauza poate fi omisa in cazul in care campul referit este chiar campul de cheie primara al tabelului secundar.

Observatii Clauza CONSTRAINT pentru camp unic trebuie folosita in instructiunile CREATE

TABLE si ALTER TABLE imediat dupa declararea tipului si dimensiunii campului respectiv. Clauza CONSTRAINT pentru un grup de campuri poate fi folosita in instructiunile CREATE TABLE si ALTER TABLE oriunde inafara listei de definitii de campuri.

Cu predicatul FOREIGN KEY se defineste un camp / un grup de campuri drept cheie secundara pentru tabelul secundar. Daca cheie primara a tabelului secundar consta dintr-un grup de campuri trebuie folosita sintaxa clauzei CONSTRAINT

63

pentru un grup de campuri, specificandu-se - in aceeasi ordine - toate campurile de legatura din tabelul principal si toate campurile de legatura din tabelul secundar impreuna cu numele tabelului secundar. Daca insa campurile de legatura din tabelul secundar sunt chiar campurile care formeaza cheia primara a tabelului secundar, ele nu mai trebuie specificate.

Specificarea predicatului NOT NULL pentru un camp este echivalenta cu setarea proprietatii Required a campului respectiv (in panelul de proprietati al ferestrei Design View a tabelului) pe valoarea Yes.

Intrucat clauza CONSTRAINT nu poate aparea independent, exemplele de utilizare a ei vor fi prezentate o data cu exemplele privind utilizarea instructiunilor CREATE TABLE si ALTER TABLE.

Sintaxa instructiunii CREATE TABLE CREATE TABLE nume_tabel (nume_camp1 tip [(d imensiune)] [NOT NULL] [nume_index1] [, nume_camp2 tip [(dimensiune) ] [NOT NULL] [nume_index2] [, ...]] [, CONSTRAINT nume_index_de_grup_de_campuri [, ...]]) unde: nume_tabel numele tabelului care trebuie creat; nume_camp1, nume_camp2 numele campului / campurilor din tabelul de creat (trebuie sa existe cel putin un camp); tip tipul de date al campului din noul tabel; dimensiune dimensiunea campului in caractere (numai pentru campurile de tip Text si Binary); nume_index1, nume_index2 numele unor indecsi de un singur camp definiti cu ajutorul unei clauze CONSTRAINT; nume_index_de_grup_de _campuri numele unui index de mai multe campuri definit cu ajutorul unei clauze CONSTRAINT. Observatie Predicatul NOT NULL se poate aplica o singura data si unui singur camp. Intr-o clauza CONSTRAINT cu nume se poate folosi clauza NOT NULL atat pentru un camp unic cat si pentru un grup de campuri. Exemple Crearea unui tabel; de exemplu, tabelul “Experti” continand date despre angajatii firmei de consultanta AsistRom ; instructiunea atribuie campului “CodConsultant” un index si-l defineste drept camp de cheie primara ➜

CREATE TABLE Experti ([CodConsultant] INTEGER, [Nume] TEXT, [Prenume] TEXT, [DataNasterii] DATE, [DataAngajarii] DATETIME, [Adresa] TEXT, [Telefon] TEXT, [Observatii] MEMO, CONSTRAINT [Index1] PRIMARY KEY ([CodConsultant]));

Crearea unui tabel cu un index cu valori unice format din 3 campuri ➜

CREATE TABLE Experti2 ([Nume] TEXT, [Prenume] TEXT, [DataNasterii] DATETIME, [DataAngajarii] DATETIME, [Adresa] TEXT, [Telefon] TEXT, CONSTRAINT [Restrictie] UNIQUE ([Nume],[Prenume],[DataNasterii]));

Crearea unui tabel cu camp de cheie primara ➜

CREATE TABLE UnTabel (Nume TEXT, Prenume TEXT, Id INTEGER CONSTRAINT Restrictie PRIMARY KEY);

64

Sintaxa instructiunii ALTER TABLE ALTER TABLE nume_tabel {ADD {COLUMN nume_ca mp tip[(dimensiune)] [NOT NULL] [CONSTRAINT nume_index] | CONSTRAINT nume_index_de_grup_de_campuri} | DROP {COLUMN nume_camp I CONSTRAINT nume_ index} } unde: nume_tabel numele tabelului care trebuie modificat; nume_camp numele campului care trebuie adaugat sau eliminat din tabel; tip tipul de date al campului; dimensiune dimensiunea campului in caractere (numai pentru campurile de tip Text si Binary); nume_index numele unui index de un singur camp definit cu ajutorul unei clauze CONSTRAINT; nume_index_de_grup_de _campuri numele unui index de mai multe campuri definit cu ajutorul unei clauze CONSTRAINT; nume_index numele indexului de mai multe campuri care trebuie eliminat. Observatii si exemple Clauza ADD COLUMN intr-o instructiune ALTER TABLE permite adaugarea unei

coloane la tabelul existent; trebuie specificate: numele campului, tipul de date si, optional, dimensiunea. De exemplu, urmatoarea instructiune adauga campul “Observatii” tabelului “Experti” din BD AsistRom ➜

ALTER TABLE Experti ADD COLUMN Observatii TEXT(25); Clauza DROP COLUMN intr-o instructiune ALTER TABLE permite eliminarea unui

camp din tabel; trebuie specificat numai numele tabelului. In exemplul urmator tabelului “Experti” din BD AsistRom i se adauga si apoi i se sterge coloana “Salariu” (fiecare operatie necesita o interogare ALTER TABLE distincta) ➜

ALTER TABLE Experti ADD COLUMN Salariu CURRENCY; ALTER TABLE Experti DROP COLUMN Salariu;

Clauza CONSTRAINT intr-o instructiune ALTER TABLE permite definirea unui index

pentru un camp din tabel. De exemplu, presupunand ca tabelul “Experti” nu are camp de index, adaugam campul ”CodExpert” si-l definim drept camp indexat (nu camp de cheie primara; ca urmare, proprietatea Indexed a campului va fi setata pe valoarea Yes (No Duplicates)) ➜

ALTER TABLE Experti ADD COLUMN CodExpert TEXT(4) CONSTRAINT CodExpert UNIQUE;

Clauza ADD CONSTRAINT intr-o instructiune ALTER TABLE permite crearea unui

index de mai multe campuri. Clauza DROP CONSTRAINT intr-o instructiune ALTER TABLE permite eliminarea unui index de mai multe campuri; trebuie specificat numai numele indexului·imediat dupa cuvantul-cheie CONSTRAINT. In exemplul urmator este eliminata si apoi adaugata cheia secundara din campul “ConsultanteDet” (acest tabel se afla in relatia 1 – m cu tabelul “Experti” din BD AsistRom , el continand detalii despre consultantele acordate de fiecare expert; efectul acestor operatii se poate urmari in fereastra Relationships a BD AsistRom ) ➜

ALTER TABLE ConsultanteDet DROP CONSTRAINT CodCons; ALTER TABLE ConsultanteDet ADD CONSTRAINT CodCons FOREIGN KEY (CodExpert) REFERENCES Experti (CodExpert);

65

Cu o instructiune ALTER TABLE se poate elimina un singur camp / index.

Subinterogarea (Subquery) Definitie O subinterogare consta dintr-o instructiune SELECT inclusa in alta interogare de selectie sau in alta interogare-actiune. Ea poate fi introdusa in • linia Fields a grilei QBE din fereastra Design View pentru a defini un nou camp; • linia Criteria a grilei pentru a defini un criteriu de selctie pentru campul respectiv. Utilizare Verificarea existentei unor rezultate returnate de interogare (prin aplicarea clauzelor

EXISTS sau NOT EXISTS); Gasirea tuturor valorilor din interogarea principala care sunt mai mari, mai mici,

respectiv egale cu valorile returnate de subinterogare (prin aplicarea clauzelor ANY, IN sau ALL);

Crearea subinterogarilor imbricate. Exemple de expresii care folosesc rezultatul unei subinterogari pentru definirea criteriilor de selectie Determinarea produselor al caror pret unitar coincide cu cel al caietelor dictando produse de firma “AF Dana” ➜ in celula Criteria a campului “PretUnitar” din grila QBE se introduce expresia:

(SELECT [PretUnitar] FROM [Produse]

WHERE [NumeProdus] = "caiet dictando" AND [CodFirma] = “AFD”)

Determinarea produselor al caror pret unitar depaseste pretul unitar mediu ➜ in celula Criteria a campului “PretUnitar” din grila QBE se introduce expresia:

> (SELECT AVG([PretUnitar]) FROM [Produse]) Determinarea firmelor care produc caiete de matematica si de muzica ➜ in celula Criteria a campului “CodFirma” se introduce expresia:

ALL (SELECT [CodFirma] FROM [Produse] WHERE ([NumeProdus] LIKE "*caiet matematica*") OR ([NumeProdus] LIKE "*caiet muzica*"))

Determinarea firmelor pentru care valoarea totala a productiei depaseste valoarea medie ➜ intai se creeaza un camp nou introducand in prima celula Fields libera expresia:

Total: [PretUnitar] * [Cantitate] apoi se introduce in celula Criteria a acestuia expresia:

> ALL (SELECT AVG([PretUnitar] * [Cantitate]) FROM [Produse])

2.3. Algebra rela Ńional ă În algebra relaŃională (relational algebra) o interogare se formulează printr-o

expresie constând dintr-o secvenŃă de identificatori (nume de relaŃii, nume de atribute), constante şi operatori. Pentru exprimarea unei interogări printr-o expresie de algebră relaŃională, trebuie să fie precizate următoarele elemente: • Lista atributelor relaŃiei rezultat, care se numesc atribute de proiecŃie. • Lista relaŃiilor din care se extrag informaŃiile. • CondiŃia pe care trebuie să o îndeplinească tuplurile relaŃiei rezultat.

În funcŃie de aceste elemente, se pot studia două situaŃii de rezolvare a interogarilor: interogări care se rezolvă în cadrul unei singure relaŃii şi interogări care se rezolvă folosind două sau mai multe relaŃii ale bazei de date.

66

- Interogări într-o singură relaŃie - dacă toate atributele care intervin în interogare (atributele de proiecŃie şi atributele din condiŃie) sunt atribute ale unei singure relaŃii R, atunci interogarea se poate rezolva la nivelul acelei relaŃii, ca o proiecŃie (pe atributele relaŃiei rezultat) a restricŃiei cu condiŃia impusă asupra relaŃiei date, prin expresia:

Q = Πlista_atribute

σconditie

(R)

- Interogări în două sau mai multe relaŃii - în situaŃia în care atributele de proiecŃie şi atributele din condiŃia de interogare nu aparŃin unei singure relaŃii, pentru rezolvarea interogării trebuie să fie folosite toate acele relaŃiile care, împreună, conŃin aceste atribute. Conceptual, o astfel de interogare se rezolvă construind mai întâi o relaŃie care să conŃină toate atributele necesare prin combinarea a două sau mai multe relaŃii folosind operaŃii de produs cartezian sau joncŃiuni, iar rezultatul interogării se obŃine prin restricŃia (cu condiŃia de interogare) şi proiecŃia (pe atributele de proiecŃie) a acestei relaŃii. Cazul cel mai frecvent de interogare necesită joncŃiunea naturală a două sau mai multe relaŃii asociate, folosind perechea de atribute cheia străină - cheia primară referită pentru fiecare operaŃie de joncŃiune:

Q = Πlista_atribute

σconditie

(R >< S >< T...)

Algebra relaŃională exprimă interogările prin aplicarea unor operatori specializaŃi (operatorii algebrei relaŃionale) asupra relaŃiilor. E.F. Codd a propus opt operaŃii ale algebrei relaŃionale, grupate în două categorii:

- OperaŃii pe mulŃimi: reuniunea (union), intersecŃia (intersection), diferenŃa (difference) şi produsul cartezian (Cartesian product). Aceste operatii reprezintă adaptarea operatiilor corespunzătoare din teoria mulŃimilor şi acŃionează asupra relaŃiilor văzute ca mulŃimi de elemente (tupluri ), fără a lua în consideraŃie compoziŃia fiecărui element.

- OperaŃii relaŃionale speciale: restricŃia (restriction), proiecŃia (projection), joncŃiunea (join) şi diviziunea (division). Aceste operaŃii iau în consideraŃie compoziŃia tuplurilor, formate din valori ale atributelor relaŃiilor.

Toate aceste operaŃii trebuie să asigure proprietatea de închidere, adică rezultatul fiecărei operaŃii trebuie să fie tot o relaŃie. Această proprietate permite efectuarea operaŃiilor imbricate: proiecŃia unei joncŃiuni dintre o relaŃie şi restricŃia aplicată altei relaŃii, etc.

RestricŃia şi proiecŃia sunt operaŃii unare (au un singur operand, o relaŃie); operatiile pe mulŃimi, joncŃiunea şi diviziunea sunt operaŃii binare (au doi operanzi, două relaŃii).

A. Opera Ńii pe mul Ńimi

Reuniunea a două relaŃii compatibile R şi S este o relaŃie QU

= R ∪ S care

conŃine toate tuplurile ce aparŃin fie relaŃiei R, fie relaŃiei S, fie ambelor relaŃii. Tuplurile care aparŃin ambelor relaŃii se introduc în relaŃia rezultat o singură dată, adică nu se duplică.

Intersec Ńia a două relaŃii compatibile R şi S este o relaŃie QI = R ∩ S care

conŃine toate tuplurile care aparŃin atât relaŃiei R cât şi relaŃiei S

Diferen Ńa a două relaŃii compatibile R şi S este o relaŃie QM

= R - S care

conŃine toate tuplurile care aparŃin relaŃiei R, dar nu aparŃin relaŃiei S. În algebra relaŃională, produsul cartezian al relaŃiilor R(A

1,A

2,... A

n) şi

S(B1,B

2,...B

m) este o relaŃie Q

C(A

1,A

2,....A

n,B

1,B

2,...B

m) = R × S care are ca atribute toate

atributele primei relaŃii plus toate atributele celei de-a doua relaŃii. Pentru a se obŃine tuplurile relaŃiei rezultat se combină (se concatenează) valorile atributelor fiecărui tuplu din prima relaŃie cu valorile atributelor tuturor tuplurilor din cea de-a doua relaŃie.

67

OperaŃia produs cartezian este conceptual comutativă, adică R×S = S×R, dacă se consideră că atributele unei relaŃii nu sunt ordonate. Dacă se consideră schema relaŃiei rezultat ca listă a atributelor sale, atunci, prin convenŃie, atributele primei relaŃii operand sunt primele în lista de atribute a relaŃiei rezultat, iar atributele celei de-a doua relaŃii urmează în lista atributelor relaŃiei rezultat.

OperaŃia produs cartezian este asociativă, dacă se consideră că ordinea atributelor într-o schemă de relaŃie şi ordinea tuplurilor într-o relaŃie nu este relevantă: R ×(S × T) = (R × S)× T.

B. Opera Ńii rela Ńionale speciale Pentru a lua în consideraŃie compoziŃia tuplurilor (combinaŃii de valori ale

atributelor) se impun anumite condiŃii atributelor acestora.

Restric Ńia (restriction) este o operaŃie relaŃională unară care selectează dintre tuplurile relaŃiei operand acele tupluri care îndeplinesc o condiŃie dată. ObservaŃie

OperaŃia de restricŃie se mai numeşte şi selecŃie, dar este mai bine să fie evitată această denumire deoarece se poate confunda cu instrucŃiunea SELECT, care are rolul de instrucŃiune generală de interogare.

OperaŃia de restricŃie se notează: σθ(R), unde θ este o expresie logică

specificată asupra atributelor relaŃiei R. În relaŃia rezultat sunt selectate acele tupluri ale relaŃiei R pentru care expresia θ are valoarea 1 (TRUE). RelaŃia rezultat are aceleaşi atribute ca şi relaŃia operand.

Expresia logică θ este formată din una sau mai multe variabile logice v conectate prin operatorii logici AND, OR, NOT, ca de exemplu:

θ = v1 AND (v

2 OR v

3)...

Fiecare variabilă logică v este rezultatul returnat de un operator de comparaŃie. Se pot compara valorile a două atribute sau se poate compara valoarea unui atribut cu o constantă.

O secvenŃă de restricŃii poate fi aplicată în orice ordine, adică:

σcond1

(σcond2

(R)) = σcond2

(σcond1

(R))

Mai mult, se poate observa şi demonstra că orice secvenŃă de restricŃii poate fi înlocuită printr-o singură restricŃie în care expresia logică de condiŃie se obŃine prin conjuncŃia (AND) tuturor condiŃiilor:

σcond1

(σcond2

(..σcondn

(R)..)) = σcond1 AND cond2...AND condn

(R)

Proiec Ńia (projection) este o operaŃie relaŃională unară prin care se selectează o submulŃime de atribute ale relaŃiei operand.

NotaŃia pentru proiecŃie este: Πlista_atribute

(nume_relatie). RelaŃia rezultat a

operaŃiei de proiecŃie conŃine numai atributele din lista de atribute dată ca parametru, care este o submulŃime nevidă a mulŃimii atributelor relaŃiei operand.

Dacă lista atributelor de proiecŃie este o cheie (sau conŃine o cheie) a relaŃiei operand, atunci relaŃia rezultat are toate tuplurile distincte. În această situaŃie numărul de tupluri ale relaŃiei rezultat este egal cu numărul de tupluri ale relaŃiei operand.

Dacă lista de atribute nu este o cheie (sau nu conŃine o cheie) a relaŃiei operand, atunci este posibil ca prin proiecŃie să se obŃină două sau mai multe tupluri identice, dar în relaŃia rezultat sunt eliminate tuplurile duplicat.

Jonc Ńiunea (cuplarea) - (join) este o operaŃie binară a algebrei relaŃionale prin care se combină tuplurile a două relaŃii într-o singură relaŃie.

68

JoncŃiunea se notează cu semnul >< şi este o operaŃie foarte importantă în bazele de date relaŃionale, deoarece ea permite realizarea asocierilor între relaŃii. În continuare vor fi prezentate două forme ale operaŃiei de joncŃiune: θ-joncŃiunea şi joncŃiunea naturală.

θ-jonc Ńiunea a două relaŃii R(A1,A

2,...A

n) şi S(B

1,B

2,...B

m) este o relaŃie

QJ(A

1,A

2,... A

n,B

1,B

2,...B

m) = R ><

θ S, în care fiecare tuplu este o combinaŃie a două

tupluri, unul din relaŃia R (cu atributele A1,A

2,....A

n), iar celălalt din relaŃia S (cu

atributele B1,B2,...,Bm), combinaŃie care satisface condiŃia de joncŃiune θ. Forma generală a condiŃiei de joncŃiune θ este:

θ = cond1 AND cond

2...AND cond

i...AND cond

n

unde fiecare condiŃie parŃială (condi) este o variabilă logică, rezultat al unei operaŃii de

comparaŃie # (unde # poate fi unul din operatorii: =, ≠, <, ≤, >, ≥ ) asupra valorilor a două atribute A

i (care aparŃine relaŃiei R) şi Bi (care aparŃine relaŃiei S), deci:

condi = A

i # B

i

Atributele Ai şi Bi le căror valori se compară trebuie să fie definite pe domenii compatibile. Tuplurile în care atributele din condiŃiile de joncŃiune au valori NULL nu sunt luate în consideraŃie pentru calculul relaŃiei rezultat.

OperaŃia de θ-joncŃiune este asemănătoare cu produsul cartezian, dat fiind că tuplurile relaŃiei rezultat sunt combinaŃii ale tuplurilor relaŃiilor operand, cu număr de atribute (gradul relaŃiei) egal cu suma numărului de atribute (gradul) ale celor doi operanzi. DiferenŃa esenŃială dintre joncŃiune şi produsul cartezian este aceea că în operaŃia de joncŃiune se combină numai tuplurile care îndeplinesc condiŃia de joncŃiune θ, pe câtă vreme în operaŃia produs cartezian în relaŃia rezultat se includ toate combinaŃiile de tupluri din relaŃiile operand. Ca urmare, operaŃia de θ-joncŃiune poate fi scrisă ca restricŃie cu condiŃia θ a produsului cartezian al celor două relaŃii:

R ><θ S = σ

θ(R × S).

Cea mai utilizată formă de θ-joncŃiune este echijoncŃiunea, în care se foloseşte numai operatorul de comparaŃie de egalitate (=).Într-o echijoncŃiune vor exista întotdeauna una sau mai multe perechi de atribute care au valori identice în fiecare din tuplurile relaŃiei rezultat, şi anume perechile de atribute care sunt comparate pentru egalitate.

Jonc Ńiunea natural ă. JoncŃiunea naturală este o echijoncŃiune în care fiecare pereche de atribute comparate pentru egalitate (în condiŃia de joncŃiune) se înlocuieşte cu unul singur. Se poate spune că joncŃiunea naturală este o echijoncŃiune urmată de o proiecŃie pe reuniunea atributelor celor două relaŃii.

Dat fiind că θ-joncŃiunea este o restricŃie a produsului cartezian al celor două relaŃii operand, rezultă joncŃiunea naturală ca o proiecŃie a unei restricŃii a produsului cartezian al celor două relaŃii.

Dacă se notează relaŃiile operand cu R(A1,A

2,...A

n,B

1,B

2,...B

m) şi S(B

1,B

2,...B

m,

C1,C

2,...C

k), cu atributele comune (B

1,B

2,...B

m), rezultatul operaŃiei de joncŃiune naturală

este relaŃia QJ cu expresia:

QJ = R >< S = Π

A1,….An,B1,.…Bm,C1,.…Ck σ

(R.B1=S.B1… AND R.Bm=S.Bm)(R × S)

Atributele (B1,B2,...,Bm) din cele două relaŃii comparate pentru egalitate în joncŃiunea naturală se numesc atribute comune (sau atribute de joncŃiune) şi trebuie să fie definite pe domenii compatibile. Ele se consideră identice (chiar dacă au denumiri diferite) şi în reuniunea atributelor se introduc o singură dată.

69

JoncŃiunea naturală se reprezintă numai cu semnul ><, fără să mai fie însoŃit de condiŃia de joncŃiune, înŃelegând prin aceasta că joncŃiunea are loc pe atributul (sau atributele) comune ale celor două relaŃii.

Diviziunea (division) este o operaŃie binară a algebrei relaŃionale prin care se obŃine o relaŃie care conŃine atributele diferenŃei mulŃimilor de atribute ale relaŃiilor operand.

Fie două mulŃimi de atribute: A = {A1,A

2,..A

n} şi B = {B

1,B

2,..B

m} şi două relaŃii

R(A,B) şi S(B) astfel încât mulŃimea atributelor relaŃiei S să fie o submulŃime a mulŃimii atributelor relaŃiei R. RelaŃia Q

D obŃinută prin operaŃia de diviziune are ca atribute toate

atributele diferenŃei celor două mulŃimi de atribute (adică acele atribute care aparŃin relaŃiei R şi nu aparŃin relaŃiei S) şi conŃine acele tupluri t[A] care au proprietatea că pentru orice tuplu s din S există un tuplu t în R care are atributul B egal cu tuplul s. Se poate scrie:

QD(A) = R ÷ S = Π

A σ

R.B = S.B(R)

Algebra relaŃională este o colecŃie de operaŃii asupra relaŃiilor. Cele opt operaŃii propuse de E.F.Codd (reuniunea, intersecŃia, diferenŃa, produsul cartezian, restricŃia, proiecŃia, joncŃiunea, diviziunea), la care se adaugă operaŃia de redenumire a atributelor, nu constituie o mulŃime minimă de operaŃii ale algebrei relaŃionale, deoarece o parte din operaŃii se pot exprima prin intermediul altora. JoncŃiunea este o proiecŃie a unei restricŃii a produsului cartezian al celor două relaŃii, iar diviziunea este o proiecŃie a unei restricŃii asupra relaŃiei deîmpărŃit. La fel, intersecŃia se poate exprima printr-o expresie construită pe baza operaŃiei de diferenŃă: R ∩ S = R – (R – S). Astfel cele cinci operaŃii (reuniunea, diferenŃa, produsul cartezian, restricŃia, proiecŃia) sunt operaŃii primitive şi constituie mulŃimea minimă de operaŃii ale algebrei relaŃionale. Pe baza lor se poate construi orice expresie a algebrei relaŃionale. Dar şi celelalte trei operaŃii (şi în special joncŃiunea) sunt operaŃii deosebit de utile în formularea interogărilor, astfel încât algebra relaŃională a păstrat toate cele opt operaŃii propuse de E.F.Codd, la care s-a adăugat operaŃia de redenumire a atributelor.

C. Implementarea interogarilor cu ajutorul limbaju lui SQL SQL (limbaj de interogare structurată) este limbajul utilizat de majoritatea

sistemelor de baza de date relaŃionale. Limbajul SQL a fost dezvoltat într-un prototip de sistem de management a bazelor de date relaŃionale - System R - de către IBM, la mijlocul anilor 1970. În 1979, corporaŃia Oracle introduce prima implementare a SQL în varianta comercială. Institutul NaŃional American de Standarde (ANSI) a adoptat SQL ca limbaj standard pentru RDBMS în anul 1986. OrganizaŃia InternaŃională de Standarde (ISO) a adoptat deasemenea SQL ca limbaj standard pentru RDBMS. Toate RDBMS-urile suportă unele forme de SQL şi toŃi vânzătorii de RDBMS intenŃionează să se alinieze la standardele ANSI.

Comanda principală care realizează în SQL interogarea datelor este SELECT. Din punctul de vedere al algebrei relaŃionale, SQL - prin intermediul instructiunii SELECT - implementează următoarele operaŃii:

� OperaŃia de reuniune se exprimă în limbajul SQL ca o reuniune a două tabele obŃinute ca rezultat a două comenzi SELECT, cu sintaxa:

SELECT lista_coloane1 FROM tabel1 [WHERE condi Ńie1] UNION SELECT lista_coloane2 FROM tabel2 [WHERE condi Ńie2];

Cele două liste de coloane din clauzele SELECT trebuie să conŃină atribute compatibile. Tabelele din clauzele FROM, ca şi condiŃiile din clauzele WHERE pot fi identice sau diferite.

70

� La fel ca şi reuniunea, operaŃia de intersecŃie se exprimă în SQL ca intersecŃie a două tabele obŃinute ca rezultat a două comenzi SELECT, cu sintaxa:

SELECT lista_coloane1 FROM tabel1 [WHERE condi Ńie1] INTERSECT SELECT lista_coloane2 FROM tabel2 [WHERE condi Ńie2];

� OperaŃia de diferenŃă se exprimă în SQL ca diferenŃă a două tabele obŃinute ca rezultat a două comenzi SELECT, cu sintaxa:

SELECT lista_coloane1 FROM tabel1 [WHERE condi Ńie1] MINUS SELECT lista_coloane2 FROM tabel2 [WHERE condi Ńie2];

� În limbajul SQL, produsul cartezian a două tabele R şi S se obŃine ca o variantă a instrucŃiunii SELECT, într-una din formele:

SELECT * FROM R,S; SELECT lista_coloane FROM R,S;

În prima formă, limbajul SQL admite operaŃia produs cartezian şi în situaŃia în care în cele două relaŃii operand există două atribute cu acelaşi nume, subînŃelegându-se că atributele rezultatului sunt ordonate, mai întâi fiind atributele primei relatii, urmate de atributele celei de-a doua relatii.

Pentru cea de-a două formă, atributele cu acelaşi nume trebuie să fie calificate cu numele relaŃiei respective.

� RestricŃia se exprimă printr-o formă particulară a instrucŃiunii SELECT, în care lista de atribute este formată din toate atributele unei singure relaŃii, iar clauza WHERE este obligatorie şi introduce condiŃia de restricŃie:

SELECT * FROM tabel WHERE conditie [clauze_secundar e];

Deci in SQL restricŃia selectează o parte din liniile tabelului operand.

� OperaŃia de proiecŃie se obŃine prin introducerea listei de coloane în instrucŃiunea SELECT ca lista a atributelor de proiecŃie. Sub forma:

SELECT DISTINCT lista_coloane FROM nume_tabel;

instrucŃiunea SELECT reprezintă o operaŃie de proiecŃie asupra relaŃiei nume_tabel pe atributele date în lista_coloane.

Dacă lipseşte clauza DISTINCT şi lista de atribute nu este o supercheie a relaŃiei, rezultatul operaŃiei poate conŃine tupluri duplicat (deci nu este o relaŃie în sensul definiŃiei din modelul relaŃional), astfel in SQL proiecŃia realizează o selecŃie a coloanelor unui tabel.

� θ-joncŃiunea se poate exprima direct cu o instrucŃiune SELECT pe două sau mai multe tabele, condiŃia de joncŃiune θ fiind introdusă prin clauza WHERE.

� O joncŃiune naturală se poate exprima în limbajul SQL numai în mod explicit, adică trebuie ca lista de atribute a instrucŃiunii SELECT să conŃină numai atributele diferite din cele două relaŃii (fiecare atribut de joncŃiune se introduce o singură dată), iar în clauza WHERE trebuie introdusă condiŃia de egalitate a atributelor corespondente.

� Diviziunea se exprimă introducând în instrucŃiunea SELECT explicit lista atributelor de proiecŃie şi condiŃia de egalitate a atributelor corespondente din cele două relaŃii prin clauza WHERE.

Există două situaŃii de rezolvare a interogarilor: interogări care se rezolvă în cadrul unei singure relaŃii şi interogări care se rezolvă folosind două sau mai multe relaŃii ale bazei de date.

71

Interogările într-o singură relaŃie se fac atunci când toate atributele care intervin în interogare (atributele de proiecŃie şi atributele din condiŃie) sunt atribute ale unei singure relaŃii R şi se poate rezolva la nivelul acelei relaŃii, ca o proiecŃie (pe atributele relaŃiei rezultat) a restricŃiei cu condiŃia impusă asupra relaŃiei date, prin expresia:

Q = Πlista_atribute

σconditie

(R)

Interogări în două sau mai multe relaŃii.În situaŃia în care atributele de proiecŃie şi atributele din condiŃia de interogare nu aparŃin unei singure relaŃii, pentru rezolvarea interogării trebuie să fie folosite toate acele relaŃiile care, împreună, conŃin aceste atribute.

Conceptual, o astfel de interogare se rezolvă construind mai întâi o relaŃie care să conŃină toate atributele necesare prin combinarea a două sau mai multe relaŃii folosind operaŃii de produs cartezian sau joncŃiuni, iar rezultatul interogării se obŃine prin restricŃia (cu condiŃia de interogare) şi proiecŃia (pe atributele de proiecŃie) a acestei relaŃii.

Cazul cel mai frecvent de interogare necesită joncŃiunea naturală a două sau mai multe relaŃii asociate, folosind perechea de atribute cheia străină - cheia primară referită pentru fiecare operaŃie de joncŃiune:

Q = Πlista_atribute

σconditie

(R >< S >< T...)

D. Exemplificarea interog ărilor folosind comenzi SQL pe o baz ă de date Oracle Fie tabelele ANGAJATI, FURNIZORI, SECTII având următoarea structură şi

conŃinut: ANGAJATI

FURNIZORI

SECTII

� Reuniunea tabelelor ANGAJATI si FURNIZORI se obtine cu:

SELECT Nume,Prenume FROM ANGAJATI WHERE Adresa = ‘B ucuresti’ UNION SELECT Nume,Prenume FROM FURNIZORI;

Rezultatul acestei instructiuni este:

NUME PRENUME

72

-------------------- -------------------- Ionescu Ion

Mihailescu Dan Pavelescu Ioana

Petrescu Ana Popescu Petre Radu Mihaela

� Intersectia tabelelor ANGAJATI si FURNIZORI se obtine cu:

SELECT Nume,Prenume FROM ANGAJATI WHERE Adresa = ‘B ucuresti’ INTERSECT SELECT Nume,Prenume FROM FURNIZORI;

Rezultatul acestei instructiuni este:

NUME PRENUME -------------------- --------------------

Popescu Petre � Diferenta pe tabelele ANGAJATI si FURNIZORI:

SELECT Nume,Prenume FROM ANGAJATI WHERE Adresa = ‘B ucuresti’ MINUS SELECT Nume,Prenume FROM FURNIZORI;

Rezultatul acestei instructiuni este:

NUME PRENUME -------------------- --------------------

Ionescu Ion Pavelescu Ioana

Radu Mihaela � Proiectia pe tabela ANGAJATI cu ajutorul clauzei DISTINCT: SELECT DISTINCT Nume,Prenume FROM ANGAJATI; elimina dublura “Ionescu Ion”

NUME PRENUME -------------------- --------------------

Ionescu Ion Pavelescu Ioana Popescu Petre Radu Mihaela

� Produsul cartezian al tabelelor SECTII si ANGAJATI se obtine cu: SELECT * FROM ANGAJATI,SECTII; Rezultatul acestei instructiuni este:

73

IDANGAJAT NUME PRENUME DATANASTERII ADRESA FUNCłIE SALARIU IDSECTIE IDSECTIE NUME ------------- ---------- ------------- ------------------ ---------- ----------- ----------- ------------ ---------------------------------------------- -------- 1 Ionescu Ion 11.12.1960 Bucureşti inginer 5000 1 1 Proiectare 2 Popescu Petre 10.04.1967 Bucureşti arhitect 4500 1 1 Proiectare 3 Pavelescu Ioana 11.03.1970 Bucureşti desenator 2500 1 1 Proiectare 4 Radu Mihaela 04.06.1966 Bucureşti contabil 3500 2 1 Proiectare 5 Ionescu Ion 08.10.1965 Bucureşti casier 2000 2 1 Proiectare 1 Ionescu Ion 11.12.1960 Bucureşti inginer 5000 1 2 Contabilitat 2 Popescu Petre 10.04.1967 BucurestI arhitect 4500 1 2 Contabilitat 3 Pavelescu Ioana 11.03.1970 BucurestI desenator 2500 1 2 Contabilitat 4 Radu Mihaela 04.06.1966 Bucureşti contabil 3500 2 2 Contabilitat 5 Ionescu Ion 08.10.1965 Bucureşti casier 2000 2 1 Proiectare

� Jonctiunea naturala a tabelelor ANGAJATII si SECTII se obtine legand coloanele IdSectie ale celor doua tabele: SELECT IdAngajat,ANGAJATI.Nume,Prenume,DataNasterii , Adresa,Salariu,SECTII.IdSectie,SECTII.Nume,Locatie FROM ANGAJATI,SECTII WHERE ANGAJATI.IdSectie = SECTII.IdSectie; Rezultatul acestei instructiuni este: IDANGAJAT NUME PRENUME DATANASTERII ADRESA SALARIU IDSECTIE NUMESECTIE LOCAłIE --------------- --------------- --------------- ---------------------- ------------- --------------- -------------- ------------------- ------------- 1 Ionescu Ion 11.12.1960 Bucureşti 5000 1 Proiectare Bucureşti 2 Popescu Petre 10.04.1967 Bucureşti 4500 1 Proiectare Bucurest 3 Pavelescu Ioana 11.03.1970 Bucureşti 2500 1 Proiectare Bucurest 4 Radu Mihaela 04.06.1966 Bucuresti 3500 2 Contabilitate Ploiest 5 Ionescu Ion 08.10.1965 Bucureşti 2000 2 Contabilitate Ploiesti

� Interogare intr-o singură relatie: Care sunt numele,prenumele si salariul angajaŃilor in sectia cu numarul 1?” Raspunsul la aceasta intrebare se obtine cu urmatoarea interogare pe tabela ANGAJATI: SELECT Nume,Prenume,Salariu FROM ANGAJATI WHERE IdS ectie = 1; Rezultatul este:

NUME PRENUME SALARIU -------------------- -------------------- ------------------------

Ionescu Ion 5000 Popescu Petre 4500 Pavelescu Ioana 2500

� Interogare în două relaŃii: Care sunt numele,prenumele si salariul angajaŃilor in sectia cu numele „Contabilitate”? Raspunsul la aceasta intrebare se obtine cu o interogare de tip jomctiune pe tabelele ANGAJATI si SECTII: SELECT ANGAJATI.Nume,Prenume,Salariu FROM ANGAJATI,SECTII WHERE ANGAJATI.IdSectie = SECTII.IdSectie and SECTI I.Nume=' Contabilitate'; Rezultatul este:

NUME PRENUME SALARIU -------------------- -------------------- -----------------------

Radu Mihaela 3500 Ionescu Ion 2000

2.4. Optimizarea interog ărilor folosind limbajul SQL În condiŃiile realizării de aplicaŃii bazate pe tehnologia client/server şi

destinate lucrului în reŃea, fie locală sau Internet, este deosebit de important fiecare aspect care determină îmbunătăŃirea performanŃelor unei baze de date. Pe lângă

74

măsurile ce pot fi luate în privinŃa creşterii capacităŃii hardware se pot căuta soluŃii pentru a optimiza aplicaŃiile astfel încât să ruleze mult mai repede. :

Următoarele patru tehnici îmbunătăŃesc performanŃele interogărilor folosind limbajul SQL pe o baza de date Oracle: � utilizarea clauzei WHERE în detrimentul clauzei HAVING.

Exemplu: Se doreşte gruparea cantităŃilor vândute pe produse şi se utilizează clauza HAVING pentru a selecta numai anumite produse (produsele cu denumirile „Chocolade” sau „Tarte au sucre”). Dacă în loc de HAVING se utilizează WHERE, se reduce numărul liniilor (înregistrărilor) care trebuie grupate, deci aceasta este maniera mai eficientă, în condiŃiile în care rezultatul este acelaşi.

SELECT Products.ProductName, Sum([Order Details].Quantity) SumOfQuantity FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID WHERE Products.ProductName=’Chocolade’ Or Products.ProductName=’Tarte au sucre’ GROUP BY Products.ProductName;

SELECT Products.ProductName, Sum([Order Details].Quantity) SumOfQuantity FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID GROUP BY Products.ProductName HAVING Products.ProductName=’Chocolade’ Or Products.ProductName=’Tarte au sucre’;

� utilizarea cuvântului cheie DISTINCT pentru a găsi o listă de înregistrări distincte, în loc de GROUP BY.

Exemplu: Dacă se doreşte selectarea numai a produselor pentru care s-au înregistrat comenzi, trebuie aplicat un SELECT asupra tabelei ORDER DETAILS din baza de date Northwind. Pentru a furniza o singură dată denumirea produsului, se poate folosi DISTINCT sau GROUP BY, cu precizarea că prima variantă este mai eficientă în cazul unei baze de date cu dimensiuni foarte mari (de ordinul milioanelor de înregistrări):

SELECT DISTINCT [Order Details].ProductID FROM [Order Details];

SELECT [Order Details].ProductID FROM [Order Details] GROUP BY [Order Details].ProductID;

� utilizarea cererilor imbricate (SELECT în SELECT) în locul tabelelor temporare

Exemplu: Fie următoarele tabele: • PRODUSE(CodProdus,DenumireProdus,PreŃUnitarRef); • COMENZI(NrComandă,DataComenzii); • DETALII_COMENZI(NrComandă,CodProdus,Cantitate,PreŃUnitar, Discount). Se doreşte realizarea unei interogări care să returneze informaŃii (cantităŃile)

despre cele mai recente 5 comenzi. Dintre următoarele două variante de rezolvare a acestei probleme se recomandă cea de-a doua care, pe lângă faptul că este mai eficientă, este şi mai scurtă decât prima.

Varianta 1: Crearea unei tabele temporare (COMENZI_TEMP) (A), introducerea datelor în ea (B), crearea de relaŃii între tabela temporară şi celelalte şi returnarea datelor (C) şi renunŃarea la tabela temporară (D). (A) CREATE TABLE

COMENZI_TEMP (NrComandă INTEGER NOT NULL, DataComenzii DATE NOT NULL);

(B) INSERT INTO COMENZI_TEMP(NrComanda,DataComenzii)

75

(SELECT c.NrComandă, c.DataComenzii FROM COMENZI c ORDER BY c.DataComenzii DESC);

(C) SELECT p.DenumireProdus,SUM(dc.Cantitate) CantitateProdus FROM COMENZI_TEMP t INNER JOIN DETALII_COMENZI dc ON t.NrComandă=dc.NrComandă INNER JOIN PRODUSE p ON dc.CodProdus=p.CodProdus GROUP BY p.DenumireProdus ORDER BY p.DenumireProdus;

(D) DROP TABLE COMENZI_TEMP; Varianta 2: Se utilizează o cerere imbricată, respectiv o cerere care este apelată

în clauza FROM. Această variantă evită operaŃiile de intrare/ieşire ale primei variante şi totodată economiseşte spaŃiu pe disc, conducând la acelaşi rezultat.

SELECT p.DenumireProdus, SUM(dc.Cantitate) CantitateProdus FROM (SELECT c.NrComandă,c.DataComenzii FROM Comenzi c ORDER BY c.DataComenzii DESC) t INNER JOIN DETALII_COMENZI dc ON t.NrComandă = dc.NrComandă INNER JOIN PRODUSE p ON dc.CodProdus = p.CodProdus GROUP BY p.DenumireProdus ORDER BY p.DenumireProdus;

� evitarea folosirii LEFT JOIN-urilor şi valorilor NULL, în situaŃiile în care acest lucru este posibil. Legăturile la stânga sunt costisitoare deoarece implică identificarea datelor cu valori NULL (non-date). O interogare care utilizează LEFT JOIN este mult mai costisitoare decât una care foloseşte INNER JOIN. O procedură simplă de evitare a încetinirilor provocate de LEFT JOIN este de a proiecta baza de date astfel încât acestea să fie ocolite.

Exemplu: Se doreşte listarea tuturor produselor şi a categoriilor lor, în situaŃia în care anumite produse nu au categorie pe linia sa şi pe coloana categoriei va apărea o valoare NULL. Pentru a elimina acest lucru se poate crea o categorie cu valoarea „lipsa categorie”. Procedând astfel, se poate utiliza un INNER JOIN pentru a selecta toate produsele şi categoriile lor. O altă modalitate care poate părea mai dificilă deoarece necesită doi paşi, dar care dă rezultate constă în crearea unei machete de tabelă în care să fie stocate datele atât din partea corespunzătoare LEFT JOIN-ului, cât şi cele din cealaltă tabelă.

3. DATAWAREHOUSE

3.1. Introducere în depozitele de date

Conceptul original de depozit de date a fost definit de IBM ca fiind un depozit de informaŃii şi prezentat ca o soluŃie pentru accesarea datelor deŃinute în sisteme non-relaŃionale. Datorită complexităŃii şi problemelor de perfomanŃă asociate cu implementarea unor asemenea soluŃii, încercările de a crea un depozit de informaŃii au fost respinse. De atunci conceptul de depozit de date a fost de mai multe ori abordat , dar numai în ultimii ani această soluŃie a fost văzută ca fiind una viabilă şi valoroasă. Ultimul şi cel mai de succes „avocat” al depozitelor de date este William H. Inmon, care a caştigat titlul de “părinte al depozitelor de date” datorită promovării active a acestui concept.

Conform definiŃiei lui William H. Inmon din 1993, un depozit de date este “a subject-oriented, integrated, time-variant and nonvolatile collection of data in support of management's decision making process.” Privind enunŃul cu ochiul unui matematician, acesta ar putea fi descompus într-o definiŃie ("un depozit de date este o colectie de date destinate fundamentării deciziei manageriale") şi o caracterizare ("o astfel de

76

colecŃie de date este (1) tematică, (2) integrată, (3) corelată cu contextul temporal şi (4) permanentă").

3.2. Evolu Ńia depozitelor de date Încă din anii ’70 organizaŃiile şi-au concentrat investiŃiile în sisteme informatice în

scopul automatizării proceselor de afaceri. Eforturile în această direcŃie au generat generaŃii de acronime care au făcut epocă şi apoi au apus: MIS (Management Information Systems), DSS (Decision Support Systems), EIS (Executive Information Systems), MSS (Management Support Systems).

Depozitele de date, sub un nume sau altul, au apărut în gândirea comunităŃii informatice la sfârşitul anilor ‘80. La începutul anilor ‘90 ideea a capătăt contur, iar IBM s-a grăbit să protejeze în nume propriu termenul Information Warehouse. Cu toate acestea, viziunea IBM se referea mai degrabă la conectivitatea globală a diverselor surse de date, fiind un fel de "middleware generalizat" bazat pe arhitectura proprie DRDA - Distributed Relational Database Architecture.

Ideea "depozitelor de date" nu este nouă. Cu toate acestea, termenul Data Warehouse a devenit un termen “la modă", reprezentând totodată o realitate tehnologică pusă în practică din ce în ce mai frecvent.

William H. Inmon, vicepresedintele firmei Prism Solutions, este părintele necontestat a noŃiunii în înŃelesul ei curent (Inmon deŃine de altfel trademark-ul termenului Data Warehouse). Viziunea sa despre depozitele de informaŃii se concentrază asupra rolului acestora de bază informaŃională a deciziei utilă oricărui factor de conducere, păstrând astfel un nivel înalt de generalitate şi permiŃând unor multiple implementări să intre în sfera acestei noŃiuni.

Un alt nume important în acest cadru este cel al lui Earl Hadden, cel care a enunŃat, a fundamentat şi a experimentat cu succes o metodologie riguroasă pentru implementarea rapidă a depozitelor de date (90 day winners). O serie de firme comerciale şi-au adus la rândul lor contribuŃia la clarificarea, dezvoltarea şi popularizarea noii tehnologii. Printre acestea se remarcă Software AG, Oracle, Red Brick Systems, Prism Solutions, MicroStrategy, etc.

În timp ce sistemele moderne de baze de date se dezvoltă în multe direcŃii, o largă familie de noi aplicaŃii au fost create şi poartă denumirea de sisteme de integrare a informaŃiei. Aceste aplicaŃii preiau date care sunt stocate în două sau mai multe baze de date (sursele informaŃiei) şi din acestea costruiesc o baza de date mare, posibil virtuală, care conŃine informaŃii din toate sursele, astfel încat datele să poată fi interogate în mod unitar.

Sursele pot fi baze de date convenŃionale sau alte tipuri de informaŃii precum colecŃii de pagini web.

Există mai multe modalităŃi prin care bazele de date sau alte surse de informaŃii, eventual distribuite, pot fi făcute să lucreze împreună. Cele mai cunoscute abordări sunt: • Bazele de date asociate (Federated databases) – sursele sunt independente, dar una dintre surse le solicită celorlalte să ii furnizeze informaŃii • Depozitul de date (Warehousing) – copii ale datelor din mai multe surse sunt stocate într-o singură bază de date, numită depozit de date. Este posibil ca înainte de stocarea în depozit, datele să fie procesate în anumite moduri, de ex. datele pot fi

77

filtrate, relaŃiile pot fi unite sau agregate. Depozitul de date se actualizează periodic, de obicei acest proces desfăşurându-se noaptea. Întrucat datele sunt copiate din surse, acestea au nevoie de anumite transformări ca să fie conforme cu schema depozitului de date. • Intermedierea (Mediation) – reprezintă o componentă software care suportă o bază de date virtuală pe care utilizatorul o poate interoga ca şi cum ar fi materializată (construită fizic, precum un depozit de date). Mediatorul nu stochează date, ci mai degrabă translatează interogarea utilizatorului în una sau mai multe interogări printre sursele sale. Mediatorul sintetizează răspunsul la interogarea utilizatorului din răspunsurile primite de la surse şi apoi returnează răspunsul utilizatorului.

3.3. Conceptul de depozit de date Aşa cum am arătat mai sus, descrierea conceptului de data warehouse propusă

de W. Inmon este compusă dintr-o definiŃie şi o caracterizare. Vom începe cu definiŃia. Astfel, genul proxim îl reprezintă o colecŃie de date.

Datele din data warehouse provin în principal din datele capturate din sistemul operaŃional, dar mai pot proveni din datele de arhivă (în perioada de constituire a depozitului) precum şi din surse externe, cum ar fi baze de date publice. Câteva exemple posibile sunt: date demografice (obŃinute în urma unui recensământ), date statistice (furnizate de institute specializate), date de prognoză economică (furnizate de instituŃii orientate pe studiul pieŃei), date obŃinute în urma unor sondaje de opinie etc. Aceste date pot fi cumpărate, pot fi preluate pe bază de abonament sau pot fi date publice gratuite.

DiferenŃa specifică se referă la faptul că depozitele sunt destinate fundamentării deciziei manageriale. Spre deosebire de colecŃiile de date utilizate de sistemul operaŃional - orientate spre optimizarea şi siguranŃa procesării datelor - datele dintr-un depozit de date sunt organizate într-o manieră care să permită analizarea lor, deci extragerea semnificaŃiei economice pe care o reprezintă. Rolul unui depozit de date este de a oferi o imagine coerentă asupra datelor relative la activitatea unei organizaŃii şi a contextului în care acesta acŃionează. Utilizarea acestei colecŃii poate consta din extragerea unor rapoarte (la cerere sau pe baza unui "abonament" cu o anumită periodicitate), extragerea unor date pentru a fi utilizate de aplicaŃiile de birotică (programe de calcul tabelar, procesoare de text, programe de prezentare etc.), dar mai ales pentru a fi utilizate de către aplicaŃii specializate de analiză. Acestea ar putea fi împărŃite în două categorii: instrumente de analiză on-line (OLAP - On Line Analytical Processing - aplicaŃii axate pe analiză multidimensională) şi instrumente pentru "minerit" în date (data mining - aplicaŃii axate pe descoperirea unor şabloane semnificative în colecŃii de date).

Dar partea cea mai consistentă a enunŃului lui W. Inmon constă în caracterizarea acestor colecŃii de date destinate analizei. În continuare le vom examina pe rând. A. Orientare tematic ă Să remarcăm pentru început că datele operaŃionale sunt orientate pe aplicaŃii, în sensul că organizarea lor este optimizată pentru a servi procesului tranzacŃional, dinamicii sistemului. În contrast, depozitul de date este orientat pe subiectele importante ale procesului economic, cum ar fi: clienŃi, furnizori, produse, activităŃi.

78

Un exemplu simplu poate fi edificator: o comandă lansată de un client va fi consemnată de sistemul operaŃional printr-un set de înregistrări care vor contine informaŃii despre client, informaŃii despre produsele sau serviciile comandate, informatii despre modul de transport şi modul de plată etc. AtenŃia sistemului tranzacŃional este orientată către consistenŃa cheilor, astfel încât operaŃia să păstreze consistenŃa. Multe dintre datele esenŃiale din perspectivă operaŃională (numărul comenzii, poziŃiile liniilor în cadrul comenzii etc.) sunt complet lipsite de relevanŃă din perspectivă informaŃională.

O consecinŃă importantă a acestei orientări este redundanŃa datelor. Dacă în sistemul operaŃional redundanŃa este minimizată (prin procesul de normalizare) pentru a evita anomaliile de actualizare, în depozitul de date redundanŃa este creată în mod intenŃionat (prin denormalizare şi sintetizare) pentru a permite un acces tematic mai facil. B. Integrare

Este cel mai important aspect al depozitului de date şi, în cele din urmă, raŃiunea pentru care acesta este creat. Datele sunt adunate aici pentru a răspunde nevoilor informaŃionale ale întregii organizaŃii, asigurând faptul că rapoartele generate pentru diverse compartimente vor conŃine aceleaşi rezultate. Sistemul operaŃional este de cele mai multe ori format din subsisteme semi-independente, create la momente diferite, de echipe diferite, în maniere diferite, rezultând o babilonie care, deşi funcŃională, este imposibil de folosit pentru analiză.

Integrarea datelor provenind din sistemul operaŃional şi din alte surse se referă la numeroase aspecte: • ConvenŃii unice privind denumirile datelor - în sistemul operaŃional acestea diferă de la aplicaŃie la aplicaŃie; • ModalităŃi unice de codificare - e suficient să ne gândim la nenumăratele variante de a codifica sexul: ('m', 'f'), (0, 1), (True, False) etc. Este evident că o aplicaŃie pentru analiză va trebui să se bazeze pe o codificare consistentă; • Sistem de unităŃi de măsură consistent - lungimi, suprafeŃe, volume, greutăŃi, temperaturi, etc, toate trebuie exprimate într-un set unic de unităŃi de măsură; • Sistem stabil de reprezentare fizică a datelor - în aplicaŃiile tranzacŃionale este posibil ca aceleaşi date să fie memorate în moduri diverse; • ConvenŃii clare privind modul de reprezentare a datelor calendarisice, a timpului etc. C. Contextul temporal

Sistemul operaŃional al unei organizaŃii tinde mereu să reflecte realitatea curentă. Astfel, el se află într-o continuă evoluŃie iar datele pe care le conŃine sunt relevante doar pentru momentul în care sunt accesate. Orizontul de timp pe care îl acoperă este de regulă de 60 până la 90 de zile, deoarece după acest interval tranzacŃiile efectuate sunt arhivate, fiind considerate deja de domeniul istoriei, deci neinteresante din perspectivă operativă.

Pentru nevoile analizei economice, dimpotrivă, informaŃiile cu caracter istoric sunt esenŃiale, deoarece ele pun în evidenŃă tendinŃe care reprezintă fundamentul unei prognoze corecte. Depozitul de date se constituie într-un istoric al sistemului operaŃional, constituit dintr-o serie de "instantanee", imagini la diverse momente în timp. Orizontul de timp pe care îl acoperă depozitul de date este de cel puŃin cinci ani,

79

ajungând uneori la zece ani, în funcŃie de dinamica evoluŃiei pieŃei şi, deci, de relevanŃa datelor cu caracter istoric pentru nevoile analizei.

Din punct de vedere tehnic, acesta implică faptul că orice înregistrare din depozitul de date poate fi plasată în timp. Orice cheie de acces cuprinde şi o variabilă temporală. D. Permanen Ńa

EsenŃa aplicaŃiilor operaŃionale este actualizarea continuă a colecŃiilor de date, actualizare realizată în general pe bază tranzacŃională. Orice tranzacŃie procesată implică inserarea unor noi înregistrări, modificarea sau eventual ştergerea altora etc. Cu totul altfel stau lucrurile în cazul depozitului de informaŃii, unde o astfel de dinamică lipseşte. Practic, singura actualizare care se realizează aici este adăugarea periodică a unor date extrase din sistemele operative. Din punctul de vedere al aplicaŃiilor care folosesc depozitul de date, accesul la date este doar pentru citire.

Din punctul de vedere al proiectării, acestă diferenŃă este extrem de importantă. În sistemul operaŃional, o tranzacŃie trebuie să ducă colecŃia de date dintr-o stare consistentă într-o altă stare consistentă, iar această implică mecanisme extrem de complexe de menŃinere a integrităŃii datelor, mai ales în situaŃia sistemelor intens concurenŃiale: mecanisme de jurnalizare, mecanisme de salvare/restaurare/refacere, mecanisme de detectare a blocărilor circulare (deadlock) etc. În cazul depozitelor de date aceste mecanisme sunt inutile, astfel că gradul de libertate câştigat poate fi utilizat pentru optimizarea accesului la date prin denormalizare, sintetizare, statistici ale accesării datelor şi reorganizare dinamică a indexării etc.

Totuşi, indiferent de definiŃia depozitului de date, scopul principal al acestuia este de a integra datele corporaŃiilor mari într-un singur depozit din care utilizatorii pot rula diverse interogări, pot produce rapoarte şi efectua analize.

3.5. Compara Ńie între sistemele OLTP şi depozitele de date Un sistem de management al bazelor de date construit pentru procesarea

tranzacŃiilor on line este privit, în general, ca fiind nepotrivit pentru depozitele de date, deoarece fiecare sistem este conceput cu seturi de cerinŃe diferite. De exemplu, sistemele online transactional processing (OLTP) sunt construite pentru a maximiza capacitatea de procesare a tranzacŃiilor, în timp ce depozitele de date sunt construite pentru a fi suport în procesarea ad-hoc a interogărilor.

Cu toate că sistemul OLTP şi depozitele de date au caracteristici diferite şi sunt construite pentru diferite scopuri, acestea sunt strâns legate între ele întrucat sistemele OLTP constituie sursele pentru depozitele de date. O problemă majoră a acestei relaŃii o constituie faptul că datele deŃinute de sistemul OLTP pot fi inconsistente, fragmentate şi constituie subiect la schimbării, conŃinând duplicate sau elemente lipsă. În asemenea situaŃii datele operaŃionale trebuie “curăŃate” înaintea introducerii în depozitul de date.

În timp ce sistemul OLTP deŃine date curente şi detaliate, depozitul de date conŃine date istorice, atât detaliate, cât şi sintetizate. De asemenea, în timp ce datele din sistemul OLTP sunt dinamice, iar procesările sunt repetitive, cele din depozitul de date sunt statice iar procesările ad hoc, nestructurate şi euristice. Sistemul OLTP este orientat pe aplicaŃii, ajută la luarea deciziilor zilnice şi serveşte un număr mare de utilizatori operaŃionali, în timp ce depozitele de date sunt orientate pe subiect, ajută la

80

luarea deciziilor strategice şi serveşte un număr relativ mic de utilizatori care sunt, în general, manageri.

3.6. Probleme asociate depozitelor de date Problemele care pot să apară în contextul dezvoltării şi administrării depozitelor

de date sunt următoarele: • subestimarea resurselor pentru încărcarea datelor – mulŃi dezvoltatori subestimează timpul necesar pentru extragerea, curăŃarea şi încărcarea datelor în depozit. Acest proces poate dura pană la 80% din timpul total al dezvoltării, deşi unelte eficiente pentru managementul şi curăŃarea datelor pot reduce timpul alocat. • probleme ascunse legate de sistemele sursă – problemele ascunse asociate cu sistemele sursă care alimentează depozitul pot fi identificate după caŃiva ani în care nu au fost detectate. Dezvoltatorul trebuie să decidă dacă să rezolve problema în depozit şi/sau în sistemele sursă. De exemplu, cand se introduc date de detaliu despre proprietăŃi noi anumite campuri permit valori nule, ceea ce poate conduce la introducerea incompletă sau incorectă a datelor, chiar şi atunci cand acestea sunt disponibile. • datele solicitate nu au fost capturate – proiectele aferente depozitelor de date adesea subliniază cerinŃe pentru date care nu au fost capturate de sistemele sursă existente. În acest caz oganizaŃia trebuie să decidă dacă să modifice sistemul OLTP sau să creeze un sistem dedicat care să captureze informaŃia care lipseşte. • cereri crescande ale utilizatorilor finali – după ce utilizatorii finali primesc uneltele de interogare şi de raportare, cererile din partea utilizatorilor finali pot să crească în loc să descrească, acest fapt fiin posibil datorită neîncrederii utilizatorilor în acurateŃea datelor din depozit. Această problemă poate fi parŃial ameliorată prin invstirea în unele unelte mai puternice şi mai uşor de utilizat sau în pregătirea, respectiv instruirea mai temeinică a utilizatorilor finali. Un alt motiv pentru creşterea cererilor utilizatorilor adresate persoanelor de la suport IT poate fi acela că din momentul în care depozitul de date este on line creşte şi complexitatea interogărilor, nu numai numărul acestora. • omogenizarea datelor – o scală largă a datelor care intră în depozit constituie un adevărat exerciŃiu pentru designer-ul depozitului de date care trebuie să redea o viziune consolidată şi integrată a datelor organizaŃiei. Designer-ul poate fi tentat să pună un accent mai mare pe similitudinile decat pe diferenŃele dintre datele utilizate în diverse aplicaŃii. • cerere mare de resurse – depozitele de date necesită spaŃii foarte mari pe suporturi informatice. Multe baze de date relaŃionale utilizate pentru suportul deciziilor folosesc scheme tip star, snowflake sau starflake (a se vedea cap.6), fiind astfel necesară crearea unor tabele foarte mari. Combinarea tabelelor şi a indecşilor agregaŃi poate necesita mai mult spaŃiu decat datele organizate pe linii. • proprietatea datelor – depozitul de date poate schimba atitudinea utilizatorilor finali cu privire la proprietatea datelor. Astfel, date sensibile utilizate de departamente precum vanzări sau marketing pot fi accesibile şi altor persoane din cadrul organizaŃiei. • mentenanŃă de un nivel calitativ înalt – depozitele de date sunt sisteem care necesită un nivel de mentenanŃă înalt. Orice reorganizare a proceselor de business sau a sistemelor sursă poate afecta datele din depozit. Pentru a rămane o resursă de

81

încredere, depozitul de date trebuie să ramană consistent pentru organizaŃia pe care o susŃine. • proiecte de lungă durată – după ce este constituit, depozitul de date reprezintă singura sursă de informare pentru organizaŃie. Crearea unui depozit de date poate dura pană la trei ani, fapt ce poate determina unele organizaŃii să îşi constituie structuri care se construiesc mai rapid, respectiv data mart–uri care să răspundă cerinŃelor unor anumite departamente ale organizaŃiei. • complexitatea integrării – cea mai importantă arie în organizarea depozitului de date este integrarea. Aceasta înseamnă că organizaŃiei îi este necesară o perioadă semnificativă de timp pentru a determina cat de bine pot fi integrate în soluŃia finală diferitele unelte ale depozitului de date. Aceasta poate fi o sarcină dificilă dat fiind numărul de unelte pentru fiecare operaŃie din depozit, care trebuie integrată corespunzător astfel încat să depozitul să lucreze în beneficiul organizaŃiei.

3.7. Douăsprezece reguli care definesc un depozit de date În anul 1994, William H. Inmon şi Chuck Kelly au creat o listă cu douăsprezece

reguli de care trebuie să se Ńină cont la definirea unui depozit de date, astfel: 1. depozitul de date şi mediul operaŃional trebuie să fie separate, 2. datele din depozitul de date trebuie să fie integrate, 3. depozitul de date conŃine date istorice referitoare la o lungă perioadă de timp, 4. datele din depozitul de date sunt date instantaneu capturate la un anumit moment în timp, 5. datele din depozitul de date sunt orientate spre obiect, 6. datele din depozitul de date sunt în principal read-only cu actualizări de batch periodice din datele operaŃionale. Nu sunt permise actualizări on-line, 7. ciclul de viaŃă al dezvoltărilor de depozite de date diferă de dezvoltarea sistemelor clasice. În timp ce dezvoltarea depozitului de date este condus de date, abordarea clasică este condusă de procese, 8. depozitul de date conŃine date cu mai multe nivele de detalii: date de detaliu curente, date de detaliu vechi, date uşor sintetizate şi date puternic sintetizate, 9. mediul depozitului de date este caracterizat prin tranzacŃii de tip read-only aplicate la seturi de date foarte mari. Mediul operaŃional este caracterizat prin numeroase tranzacŃii de actualizare aplicate la un set mic de date la un moment dat, 10. mediul depozitului de date are un sistem care trasează sursele datelor, transformările si stocarea acestora, 11. metadatele depozitului de date reprezintă o componentă critică a mediului. Astfel, metadatele identifică şi definesc toate toate elementele caracteristice ale datelor. Metadatele furnizează sursa, transformarea, integrarea, stocarea, folosirea, relaŃiile şi istoricul fiecărui element al datelor, 12. depozitul de date conŃine un mecanism charge-back pentru utilizarea resurselor, care impune utilizarea optimă a datelor de către utilizatorul final.

3.8. Arhitectura depozitelor de date

A. Componentele unui depozit de date

Componentele principale ale unui depozit de date sunt: - Datele operaŃionale – sursa datelor din depozitul de date provine din:

82

o - datele operaŃionale mainframe deŃinute în primele generaŃii de baze de date. Se estimează că majoritatea datelor operaŃionale corporate sunt deŃinute în asemenea sisteme,

o datele din departamentele organizaŃiei stocate în sisteme de fişiere precum VSAM, RMS şi baze de date relaŃionale precum Informix, Oracle etc,

o datele private deŃinute pe staŃiile de lucru şi pe serverele private, o sistemele externe precum Internet, baze de date comerciale sau bazele de date

asociate cu furnizorii şi clienŃii organizaŃiei. - Load manager-ul (denumit şi componenta front-end) – efectuează toate operaŃiile asociate cu extragerea şi încărcarea datelor în depozitul de date. Aceste operaŃiuni includ simple transformări ale datelor astfel încat să le pregătească să intre în depozit. Dimensiunea şi complexitatea acestei componente variază de la un depozit la altul şi poate fi construită folosind o combinaŃie de unelte de încărcare de date şi de programe de costumizare a construirii, procurate de la mai mulŃi furnizori de soft. - Managerul depozitului de date – această componentă efectuează toate operaŃiile asociate gestionării datelor din depozit. Această componentă este construită folosind unelte de management al datelor şi programe de customizare procurate de la furnizorul de soft. OperaŃiile efectuate de managerul depozitului de date includ: o analiza datelor pentru a se asigura consistenŃa acestora o transformarea şi fuzionarea datelor sursă din locaŃiile de stocare temporară în

tabelele depozitului de date o crearea indecşilor şi a view-urilor pe baza tabelelor o generarea denormalizărilor (dacă este necesar) o generarea agregărilor (dacă este necesar) o back-up-ul şi arhivarea datelor. În unele cazuri managerul depozitului de date generează profile de interogări astfel

încat să determine care indecşi şi agregări sunt mai potrivite. Un profil de interogări poate fi generat pentru fiecare utilizator, pentru fiecare grup de utilizatori sau pentru depozitul de date şi se bazează pe informaŃia care descrie caracteristicile interogărilor, precum frevenŃa, tabela target şi dimensiunea setului de rezultate. - Managerul interogărilor (denumit şi componenta back-end) – efectuează toate operaŃiile asociate managementului interogărilor utilizatorului. Această componentă este construită utilizand de obicei unelte ale furnizorului de soft pentru accesul utilizatorilor finali la date, unelte de monitorizare a depozitelor de date, facilităŃi ale bazelor de date şi programe de customizare a construirii. Complexitatea managerului interogărilor este determinată de facilităŃile furnizate de uneltele de acces ale utilizatorilor finali la date şi de bazele de date. OperaŃiile efectuate de această componentă includ interogări directe asupra tabelelor şi programarea execuŃiei interogărilor.

În unele cazuri managerul interogărilor generează profile de interogări care să permită managerului depozitului de date să determine care indecşi şi agregări sunt potrivite. - Uneltele de acces ale utilizatorului final – scopul principal al depozitului de date este acela de a furniza informaŃii utilizatorilor pentru a lua decizii strategice. Aceşti utilizatori interacŃionează cu depozitul de date prin intermediul uneltelor de acces. Depozitul de date trebuie să suporte eficient analize ad-hoc şi de rutină. Se pot obŃine

83

performanŃe bune prin pre-planificarea cerinŃelor de joncŃiune, sintetizare şi stabilire de rapoarte periodice către utilizatorii finali. Deşi definiiŃiile se pot suprapune pentru unele aspecte, împărŃim aceste unelte în cinci categorii: unelte de raportare şi interogare, unelte de dezvoltare a aplicaŃiilor, unelte pentru sisteme de informaŃii executive, unelte OLAP (on line analytical processing) şi unelte de data mining. - Datele detaliate – În cele mai multe cazuri, datele detaliate nu sunt stocate on line, ci sunt agregate la următorul nivel de detaliu. Totuşi datele detaliate sunt adăugate la depozit pentru a suplimenta datele agregate. - Datele sintetizate (summarized) mai mult sau mai puŃin – această zonă a depozitului stochează toate datele generate de managerul depozitului care sunt predefinite ca fiind sintetizate mai mult sau mai puŃin. Această zonă a depozitului constituie subiectul schimbării pe baze continue pentru a răspunde schimbărilor în profilelor de interogare. Scopul sintetizării informaŃiei este acela de a creşte performanŃa interogărilor. Cu toate că sintetizarea presupune costuri operaŃionale suplimentare iniŃiale, efortul merită datorită avantajelor ulterioare legate de performanŃă. Datele sintetizate sunt actualizate permanent pe măsură ce date noi sunt încărcate în depozit. - Arhivele şi back-up-ul datelor – această zonă a depozitului de date stochează date detaliate şi date sintetizate în scopul arhivării şi creării de copii. Chiar dacă datele sintetizate sunt generate din datele detalite, este necesar să se facă back-up-uri online ale datelor sintetizate dacă aceste date sunt păstrate pentru o perioadă mai lungă decat cele detaliate. Datele sunt transferate pe benzi magnetice sau pe discuri optice. - Metadatele - reprezintă poate cea mai importantă componentă a depozitului de date. Pentru a putea utiliza depozitul de date, utilizatorii trebuie să cunoască ce date se găsesc aici, iar metadatele nu sunt altceva decât date despre date, date care descriu conŃinutul depozitului şi furnizează trimiteri directe la date. Tot la nivelul metadatelor se definesc şi diverse vederi (views) asociate unor categorii specifice de utilizatori. Dar metadatele nu sunt utile doar utilizatorului final. Ele sunt intens folosite pentru administrarea depozitului de date, continând informaŃii despre provenienŃa datelor, algoritmii de agregare şi însumare, statistici privind utilizarea şi multe altele.

Când se utilizează într-un depozit de date, metadatele sunt date care definesc obiectele depozitului. Metadatele sunt create pentru numele de date şi definiŃiile din depozit. Metadatele adiŃionale sunt create pentru a asocia intervale de timp la datele extrase şi alte câmpuri care vor fi adăugate prin curăŃirea datelor sau prin procesele de integrare.

Nivelul metadatelor trebuie să conŃină: • O descriere a structurii datelor din depozit, incluzând schema depozitului, dimensiunile, ierarhiile, definiŃiile datelor derivate; • Metadatele operaŃionale, care includ date privind evoluŃia în timp (istoricul datelor şi secvenŃa de transformare aplicată asupra lor), situaŃia datelor (active, arhivate sau şterse) şi informaŃii de monitorizare (statistici privind utilizarea depozitului de date, rapoarte de erori, împrăştierea datelor etc.); • Algoritmi utilizaŃi pentru însumare, care includ măsura şi dimensiunea algoritmilor definiŃi, date despre granularitate, partiŃii, arii de subiecte, agregări, sintetizări, rapoarte şi filtre predefinite;

84

• Transformările datelor de la mediul operaŃional la depozitul de date şi care includ bazele de date, sursa şi conŃinutul lor, partiŃionarea datelor, extragerea datelor, curăŃirea datelor, regulile de întreŃinere şi securitate a datelor; • Date relative la performanŃele sistemului care includ indicatori şi profiluri care îmbunătăŃesc accesul la date şi performanŃele de căutare; • Metadate economice (business metadata), care includ termeni economici şi definiŃii, expresii şi formule de calcul ale indicatorilor.

Metadatele se aplică pentru sursele de date, pentru programele şi regulile de extragere şi transformare, pentru structura datelor şi pentru conŃinutul propriu-zis al depozitului de date. ImportanŃa metadatelor pentru depozitul de date reiese din faptul că acestea stabilesc contextul depozitului de date, uşurează procesul de analiză, menŃin şi cresc calitatea datelor, dar şi din faptul că sunt o formă de auditare a transformării datelor.

Metadatele ajută administratorii şi utilizatorii depozitului să localizeze şi să înŃeleagă secvenŃele de date atât în sistemele sursa cât şi în structura depozitului. Dacă metadatele care descriu formatul datelor din depozite sunt disponibile, atunci se elimină orice ambiguitate legată de semnificaŃia datelor.

Metadatele menŃin şi cresc calitatea datelor, fapt ce se realizează prin definirea valorilor valide pentru fiecare câmp din depozit. Înainte de a fi efectiv încarcate în depozit, datele pot fi revăzute şi erorile pot fi corectate, regulile de corecŃie a erorilor pot fi documentate tot prin metadate. Se pot deosebi mai multe tipuri de metadate: • Metadate administrative. Acestea conŃin descrieri ale bazelor de date sursă şi ale conŃinutului, ale obiectelor depozitului de date şi ale regulilor folosite pentru a transforma datele din sistemul sursă în depozit. Printre exemple de astfel de metadate menŃionăm: descrirea tuturor sursele de date folosite, trecerea câmpurilor sursă în câmpuri destinaŃie, schema depozitului de date, structura datelor din back-end, programe şi instrumente back-end, reguli şi formule de calcul, reguli de securitate şi de acces. • Metadate pentru utilizatorii finali. Aceste metadate au rolul de a ajuta pe utilizatori să-şi creeze propriile lor interogări şi să interpreteze rezultatele. Pentru aceasta, ei au nevoie să cunoască definiŃiile datelor din depozit, descrierea lor, precum şi orice ierarhie care poate exista în diferite dimensiuni. Exemple de astfel de metadate sunt următoarele: conŃinutul depozitului de date, rapoarte şi interogări predefinite, definiŃiile ierarhiilor, calitatea datelor, istoricul încărcării depozitului de date, reguli de eliminare. • Metadate pentru optimizare. Această categorie de metadate are rolul de a creşte performanŃele depozitului de date. Exemple de astfel de metadate sunt: definiŃiile agregărilor şi colecŃii de statistici.

Un depozit de date conŃine date pentru diferite perioade de timp şi de aceea este important să avem în vedere efectul pe care îl poate avea timpul asupra regulilor de trecere a câmpurilor sursă în câmpuri destinaŃie, asupra agregărilor etc. Utilizatorii trebuie să aibă acces la metadatele corecte pentru perioada de timp pe care o studiază. Echipa IT are nevoie de aceste informaŃii pentru a putea întretine depozitul de date, iar ceea ce la prima vedere ar părea să fie o eroare în transformarea datelor poate fi de fapt rezultatul schimbării regulilor de transformare a datelor. De aceea este important ca metadatele să fie corect gestionate din punct de vedere al versiunilor.

85

Deşi în mod tradiŃional metadatele reprezintă o componentă dezvoltată spre sfârsitul ciclului de dezvoltare, la ora actuală există o tendinŃă puternică de a atribui metadatelor un rol mai important. Utilizatorii instrumentelor de extragere şi transformare pot specifica modul de trecere din câmpurile sursă în câmpurile destinatie şi pot introduce toate regulile care guverzează transformarea. Tabelul sursă-destinaŃie poate servi ca bază pentru generarea codului de program folosit apoi la extragerea şi transformarea efectivă a datelor. Utilizatorii instrumentelor pentru calitatea datelor pot specifica valorile valide pentru diferite secvenŃe de date atât în sistemele sursă, cât şi în depozitul de date. Aceste instrumente pot folosi metadatele ca bază de pornire în identificarea şi corectarea erorilor. Utilizatorii specifică metadatele referitoare la schema depozitului de date (fapte, dimensiuni etc), iar aplicaŃiile pot folosi aceste specificaŃii ca intrare pentru a genera efectiv schema (tabele, indecsi, agregări etc.).

3.9. Analiza multidimensional ă

RaŃiunea pentru care există depozitul de date este de a permite realizarea unor analize economice complexe, care să folosească întreaga valoare pe care o posedă datele colectate. Altfel spus, să valorifice informaŃiile în decizii manageriale inteligente, atât la nivel strategic cât şi la nivel tactic. Iar în acest proces instrumentele de analiză au rolul principal.

Se disting două modalităŃi prin care se poate valorifica informaŃia din depozitul de date: "mineritul" în date (data mining) şi analiza multidimensională.

• Data mining este o tehnică aflată în plină dezvoltare, care vizează descoperirea unor "şabloane" (patterns) semnificative în structura datelor, care să indice în general tendinŃe ale pieŃei. Se utilizează tehnici complexe, de diverse facturi (inteligenŃă artificială, statistică matematică, etc) care vor constitui subiectul unor articole viitoare. Se spune că data mining răspunde la întrebări pe care analistul nici măcar nu şi le pune.

• Analiza multidimensională, invocată, de regulă, prin termenul OLAP (On Line Analytical Processing) este o activitate mai apropiată de realitatea zilnică, ce răspunde la întrebări pe care managerii şi le pun la modul concret. Singura trăsătură comună a acestor întrebări este caracterul lor multidimensional.

Există totuşi câteva tipuri uzuale de întrebări, care pot sugera complexitatea instrumentelor care trebuie să furnizeze răspunsuri: • Raporturi multidimensionale. Exemplu: care este contribuŃia la vânzările săptămânale totale a produselor farmaceutice vândute prin magazinele situate în regiunea Moldova între 4 şi 10 februarie? • ComparaŃii. Exemplu: care este media abaterii procentuale de la planul de vânzări în lunile acestui an comparativ cu anului trecut? • Clasificări şi profiluri statistice. Exemplu: Care este volumul vânzărilor şi media profitului pentru primii 20% dintre distribuitori şi care este contribuŃia acestora la totalul vânzărilor pe trimestrul trecut? • Agregări libere. Exemplu: care sunt veniturile realizate în ultimele patru trimestre de filialele judeŃene din regiunea Moldova? • Evaluări What-If. Exemplu: în ce măsură ar influenŃa profitul total o creştere cu 10 procente a vânzărilor în judeŃele din Banat?

86

Oricine a formulat vreodată interogări asupra bazelor de date este conştient de faptul că exprimarea unor asemenea intrebări depăşeste posibilităŃile oricărui instrument user-friendy de raportare. Devine evidentă necesitatea unor instrumente specializate OLAP.

A. Calit ăŃi

Un bun instrument OLAP trebuie: • Să poată să susŃină analize sofisticate. • Să poată fi utilizat eficient de diverse categorii de utilizatori. Managerii de pe diverse nivele ierarhice nu formează o clasă omogenă de utilizatori din perspectiva aptitudinilor şi abilităŃilor tehnice. Pentru a putea fi eficient utilizat, trebuie să furnizeze viziuni intuitive, multidimensionale asupra datelor, să permită o navigare liberă şi o prezentare cât mai naturală a rezultatelor. • Să fie scalabil la volume oricât de mari de date. Volumele depozitelor de date sunt imense, iar creşterea lor este continuă. • Să permită accesul concurent al unui mare număr de utilizatori. Depozitul de date este centrul informaŃional al întregii organizaŃii şi este de presupus că o mare parte dintre angajaŃi îl vor utiliza. • Să fie uşor de întreŃinut şi de configurat. Nevoile informaŃionale ale unei organizaŃii evoluează, iar instrumentele de analiză trebuie să se adapteze în mod continuu. • Să fie bazate pe o arhitectură deschisă. EvoluŃia tehnologică poate aduce schimbări radicale în structura sistemului informaŃional, care însă nu trebuie să afecteze instrumentaŃia utilizată pentru analiză.

B. Arhitectura

Fiind o tehnologie relativ nouă, modelul care s-a impus pentru sistemele orientate spre analiză multidimensională este unul de tip client/server pe trei niveluri. • Bazele de date formează nivelul cel mai de jos, responsabil cu stocarea şi regăsirea datelor. De regulă aplicaŃiile tranzacŃionale utilizează sisteme relaŃionale, dar pentru depozitele de date se folosesc şi sisteme multidimensionale. Dat fiind volumul mare de date, este recomandabil ca SGBD-urile folosite să ofere suport pentru prelucrări paralele (SMP şi MPP; interogări, încărcări şi indexări paralele) şi distribuite, să dispună de mecanisme performante de indexare şi de optimizare, să ofere un înalt nivel de siguranŃă. • Logica aplicaŃiei este susŃinută de un "motor analitic" (OLAP Engine), servind ca server pentru instrumentele desktop. Din perspectiva SGBD-ului care administrează depozitul de date, motorul analitic este un client. • Logica prezentării este reprezentată de instrumentele mânuite de utilizatorul final. Acestea au acces la datele din depozit prin intermediul motorului analitic. Dacă motorul analitic este un instrument foarte specializat şi care de regulă este cumpărat "de gata", aplicaŃiile front-end folosite de utilizatorii finali sunt extrem de diverse. Există aplicaŃii generale care răspund suficient de bine nevoilor unei categorii largi de utilizatori, există instrumente specializate pe domenii (cum ar fi de pildă analiza financiară) şi, în fine, există posibilitatea de a dezvolta instrumente foarte specifice unor anumite nevoi, utilizând medii de dezvoltare comerciale (de pildă Visual Basic) şi interfeŃele de programare furnizare de serverul OLAP.

87

C. Rolul motorului analitic

Principala sarcină a motorlui OLAP este de a prelua cererile exprimate de clienŃi şi, pe baza consultării metadatelor, să genereze cererile necesare pentru obŃinerea datelor ce vor fi redirectate către clienti. În plus, datelor obŃinute li se pot aplica la acest nivel o serie de prelucrări9. • Generarea de interogări - se bazează pe criteriile furnizate de clienŃi sub forma unor formule exprimate prin operatori logici. Pentru a genera interogările (SQL sau specifice SGBD-urilor multidimensionale), motorul OLAP dispune de algoritmi care aplică criteriile asupra metadatelor, obŃinând căile de acces la datele referite şi transformările necesare. Urmează apoi optimizarea cererii în vederea obŃinerii unei viteze optime. • Manipulările matematice se aplică pentru a aduce datele la forma dorită de clienŃi. Acestea constau de cele mai multe ori în calcularea unor metrici derivate pe baza unor formule date, analize statistice complexe, etc. • Sintetizarea rezultatelor (cross-tabulation) este o altă sarcină a motorului analitic. Deşi depozitul de date conŃine şi date deja agregate, adeseori utilizatorul doreşte consolidarea unor sinteze pe baza unor combinaŃii de atribute care nu au fost prevăzute. În această situaŃie, motorul analitic solicită depozitului toate datele necesare la nivel de detaliu şi realizează sintetizările necesare.

D. Rolul aplica Ńiilor

Din punctul de vedere al utilizatorului final, aplicaŃia front-end pe care o foloseşte trebuie să-i asigure două funcŃionalităŃi importante: navigarea liberă prin depozitul de date în căutarea informaŃiilor relevante şi posibilităŃi diverse de prezentare a datelor. Aceste funcŃionalităŃi sunt strâns legate între ele şi este greu de spus care operaŃie este de navigare şi care de prezentare. • Specificarea criteriilor de selecŃie este primul pas în orice analiză. Utilizatorul trebuie să poată exprima cu uşurintă criterii simple, bazate pe valori ale atributelor şi/sau pe valori ale metricilor. Aceste criterii simple trebuie să poată fi apoi combinate prin operatori logici şi trebuie să poată fi salvate în biblioteci pentru eventuale reutilizări. • Schimbarea nivelului de agregare permite găsirea nivelului de granularitate optim pentru analiză. Se poate adânci analiza spre nivele de detaliu (drill-down) pentru anumite dimensiuni, în timp ce pentru alte dimensiuni se creşte nivelul de agregare (drill-up). De regulă căile de navigare sunt date de ierarhiile de atribute corespunzătoare atributelor. • Specificarea modului de prezentare trebuie să permită analistului să găsească modalităŃile optime de valorificare vizuală a datelor extrase. În afară de posibilităŃile grafice tipice pentru prezentare, este important ca utilizatorul să poată vizualiza date multidimensionale într-o manieră tabelară. În acest sens se pot utiliza tabele complexe, care să poată grupa coloane şi linii exprimând dimensiuni diferite (de pildă timpul şi geografia) şi nivele de agregare diferite.

Cu toate că definiŃia lui W. Inmon este suficientă pentru a răspunde la întrebarea "ce este depozitul de date?", câteva aspecte legate de arhitectura tipică a unui astfel de depozit pot crea o imagine mai concretă şi mai precisă. În principiu este considerată 9 AIRINEI, D. - Depozite de date, Editura Polirom, 2003

88

comună, uzuală, o structurare a datelor pe patru nivele, în funcŃie de nivelul de agregare şi de vechimea acestora.

Dacă vom face o analogie cu un depozit adevărat, atunci este normal ca datele cele mai utilizate să se găsească la parter. Aici se vor găsi datele relativ recente, en detail. Acestea sunt în principiu livrate utilizatorilor finali, de regulă funcŃionari de execuŃie. La primul etaj se vor afla date "semipreparate" printr-o sintetizare uşoară, destinate în principal managementului mediu. La al doilea etaj se vor afla date preparate pentru nevoile managementului superior. Prepararea implică consolidare, sintetizare şi împachetare în formate accesibile instrumentelor de analiză utilizate.

În fine, la subsol se vor afla date accesate cel mai rar. Este vorba de date având o oarecare vechime (de regulă peste doi-trei ani), în formă detaliată. Datele istorice folosite de obicei de managementul superior sunt disponibile într-o formă sintetizată la "etajul al doilea" (eventual şi la primul). Această structură este dinamică, datele intră în depozitul de date, circulă pe diverse nivele, îşi schimbă forma şi poziŃia ş.a.m.d. Dar aceste aspecte dinamice vor fi analizate mai târziu.

Poate cea mai importantă componentă a depozitului de date o reprezintă nivelul metadatelor. Pentru a putea utiliza depozitul de date, utilizatorii trebuie să cunoască ce date se găsesc aici, iar metadatele, aşa cum am prezentat la capitolul 2.1. nu sunt altceva decât date despre date, date care descriu conŃinutul depozitului şi furnizează, asemenea fişelor dintr-o bibliotecă, trimiteri directe la date. Tot la nivelul metadatelor se definesc şi diverse vederi (views) asociate unor categorii specifice de utilizatori.

Dar metadatele nu sunt utile doar utilizatorului final. Ele sunt intens folosite pentru administrarea depozitului de date, conŃinând informaŃii despre provenienŃa datelor, algoritmii de sintetizare, statistici de utilizare şi multe altele.

Deoarece sursa celor mai multe date stocate în depozitul de informaŃii o constituie mediul operaŃional, am putea crede că nivelul de redundanŃă între cele două sisteme (cel operaŃional şi cel informaŃional) este foarte ridicat. De asemenea faptul că ambele sisteme se bazează pe operarea cu sisteme de gestiune a bazelor de date, că ambele sisteme implică volume mari de date etc, pot accentua această impresie. Câteva consideraŃii pe această temă pot fi edificatoare în ceea ce priveşte chiar definiŃia depozitului de informaŃii.

În primul rând trebuie să subliniem că din punct de vedere funcŃional cele două sisteme sunt disjuncte. Sistemul operaŃional procesează tranzacŃii în timp ce sistemul informaŃional este exploatat prin interogări. CerinŃele sunt diametral opuse. Orice administrator de baze de date cunoaşte faptul că optimizările vizând siguranŃa şi coerenŃa datelor, esenŃiale într-un sistem tranzacŃional, conduc inevitabil la încetinirea dramatică a interogărilor, cu deosebire a celor ad-hoc, bazate pe criterii neprevăzute (acestea sunt cele specifice analizei economice). Reciproc, aceste interogări - implicând de regulă volume mari de date şi fiind adesea lipsite de suportul unor indecşi prestabiliŃi - pot compromite performanŃele operaŃiilor tranzacŃionale până sub limitele acceptabile.

În ceea ce priveste datele propriu-zise, câteva aspecte pot fi edificatoare: • Filtrarea datelor la transferul din sistemul operaŃional în cel informaŃional face ca doar datele relevante pentru analiza economică să treacă acest prag.

89

• Orizontul temporal al celor două sisteme este diferit. Există o suprapunere foarte mică între cele două. • Depozitul de date conŃine şi date sintetizate, care nu există niciodată în sistemele operaŃionale. • La preluarea în depozitul de date, datele sunt supuse unor transformări radicale atât din punct de vedere fizic cât şi logic.

Conform aprecierii lui W. Inmon, redundanŃa datelor între cele două sisteme are de regulă o rată mai mică de 1%. Dar chiar dacă acestă rată ar fi mult mai mare, valoarea depozitului de date este imensă, deoarece oferă managementului organizaŃiei o imagine unică, coerentă şi semnificativă asupra datelor relevante din perspectiva analizei economice.

Mai mult, instrumente specializate OLAP permit utilizatorilor să exploreze efectiv această bază informaŃională, fără a avea nevoie de intermedierea unui serviciu specializat. Iar într-un context economic în care o decizie luată dimineaŃa are deja efecte sensibile la ora prânzului, "efectiv" înseamnă de fapt "vital".

3.9. Fluxul informa Ńiei în depozitul de date Nu e suficient ca depozitul de date să existe. El trebuie să furnizeze informaŃiile

potrivite către oamenii potriviŃi, în forma potrivită. DefiniŃia depozitului de date, accentuând contrastul între sistemul operaŃional şi cel

informaŃional, ne-ar putea sugera niste imagini de genul următor: sistemul operaŃional seamănă cu ringul unei burse, cu o mulŃime de brokeri agitându-se în jurul calculatoarelor, a panourilor de afisare, făcîndu-şi semne neinteligibile între ei, răcnind la telefoane, smulgând nerăbdători hârtiile din faxuri etc. Dimpotrivă, depozitul de date este un spaŃiu liniştit, respirând atmosfera serenă a unei biblioteci.

De fapt, lucrurile nu stau chiar aşa. Depozitul de date are şi el dinamica lui, chiar dacă nu atât de agitată ca cea a sistemului operaŃional. Pentru a exprima această dinamică se foloseşte adesea termenul "data warehousing", iar pentru a o descrie se recurge la cele cinci fluxuri informaŃionale identificate de Richard Hackathorn

ÎnŃelegerea modului în care aceste fluxuri acŃionează este cheia succesului construcŃiei şi utilizării unui depozit de date.

A. In-flow

Acesta este fluxul de intrare a datelor în depozit. Datele provin din sistemul operaŃional precum şi din surse externe. Actualizarea depozitului de date nu trebuie să afecteze datele existente. Nimic nu se şterge, nimic nu se suprascrie. Este vorba doar despre adăugarea unui nou "strat" de date. Actualizarea se face de regulă în loturi (batch), la intervale regulate, dar anumite cerinŃe pot impune o actualizare în flux continuu, reflectând actualizări în sistemul operaŃional. De exemplu, o bancă ar putea să dorească să păstreze un istoric al tuturor operaŃiunilor efectuate asupra unui cont sau ar putea să se mulŃumească cu balanŃe periodice (de pildă la sfârşitul fiecărei zile).

Pentru datele provenind din aplicaŃiile tranzactionale se pune în primul rând problema selectării şi extragerii. Instrumentele folosite în acest scop trebuie să fie capabile să exploateze la maximum middleware-ul disponibil pentru a accede la toate datele şi să poată să realizeze conversiile implicite la "transbordarea" între diverse platforme.

90

Dar acesta este abia începutul, deoarece datele trebuie să treacă printr-un proces complex de consolidare. Acest proces implică:

• eliminarea datelor nerelevante (a căror semnificaŃie se leagă de procesul tranzacŃional, cum ar fi de pildă numărul poziŃiei din factură);

• conversia la codificări şi reprezentări unitare (conform unor convenŃii clar stabilite de administraŃia depozitului);

• curăŃirea datelor (prin eliminarea inconsistenŃelor şi reconstituirea datelor parŃial distruse, completarea informaŃiilor lipsă cu valori implicite etc);

• reorganizarea datelor (adăugarea informaŃiilor temporale unde este cazul, denormalizare şi rearanjare după subiecte etc).

În afară de datele provenite din sistemul operaŃional, o cerinŃă tot mai actuală o reprezintă consolidarea şi integrarea în depozitul de date a datelor provenind din alte surse. Printre acestea se remarcă datele non-relaŃionale cum ar fi texte, e-mail, foi de calcul, imagini, obiecte multimedia, baze de date geografice, chiar şi reguli comerciale (business rules). De asemenea, alte surse de date externe pot fi sistemele operaŃionale ale partenerilor de afaceri, bazele de date publice sau informaŃiile furnizate pe bază de abonament (cotaŃii bursiere, buletine meteorologice etc).

B. Up-flow

Prin procesul de intrare în depozitul informaŃional, datele capătă un plus de claritate şi de semnificaŃie. Dar odată ajunse în Data Warehouse ele nu rămân în acest stadiu, ci se îmbogăŃesc în continuare printr-o serie de alte transformări. Aceste procese sunt numite în mod generic up-flow şi au rolul de a adăuga valoare informaŃională datelor colectate.

Principalele procedee utilizate în acest scop sunt: • Sintetizare. Datele intră în depozit la nivel de detaliu. Cu toate că instrumentele

de analiză pot aplica funcŃii de agregare, pentru optimizarea accesului la astfel de informaŃii de sinteză anumite astfel de operaŃii sunt realizate chiar de administraŃia depozitului de informaŃii. Sintetizarea se realizează pe baza dimensiunilor cele mai utilizate şi poate implica nu doar însumarea unor valori, ci şi medii, valori minime/maxime sau valori obŃinute prin procedee statistice complexe. Un exemplu simplu este sintetizarea pe axa timpului. Dacă granularitatea este, de pildă, la nivelul zilei, se pot precalcula totaluri săptămanale, lunare, trimestriale şi anuale. De asemenea se pot calcula medii, se pot determina zilele de minim sau maxim (pe lună, pe trimestru, etc), se pot calcula dispersii etc.

• Împachetare. Utilizarea informaŃiei din depozit nu se face doar on-line. Pentru cea mai mare parte dintre nevoile informaŃionale ale organizaŃiei se utilizează sistemul abonamentelor: un anumit beneficiar (un funcŃionar, un birou, un departament etc.) are nevoie de anumite informaŃii, la un anume nivel de sintetizare, la anumite intervale de timp. Aceste informaŃii pot fi livrate ca simple rapoarte, dar cel mai adesea ele trebuie livrate în formate care să permită beneficiarului să le utilizeze în continuare pentru analize, prezentări, raportări etc. Cel mai adesea datele se furnizează ca foi de calcul, documente text, baze de date personale, eventual grafice, animaŃii etc., toate într-un format propriu utilizatorului. De pildă datele pot fi plasate în multiple file de calcul tabelar, în diverse nivele de detaliere, continând eventual şi prezentări grafice.

91

• Distribuire. De cele mai multe ori diverse grupuri de utilizatori sunt interesate doar de anumite categorii de date, astfel încat pentru a creşte disponibilitatea informaŃiilor se realizează nişte mici depozite departamentale, conŃinând replici parŃiale (cuprinzând doar datele de interes specific) ale depozitului central. Altă situaŃie frecventă este când repartizarea teritorială a organizaŃiei impune multiplicarea depozitului de date în mai multe locuri (de pildă replicarea integrală sau parŃială la filialele din teritoriu). Pe măsură ce tehnologiile de stocare şi procesare distribuită se maturizează, arhitectura centralizată a depozitelor de informatii va fi înlocuită de o arhitectură distribuită.

C. Down-flow

Acest flux se referă la administrarea datelor şi este destinat să menŃină "vitalitatea" depozitului de date. Datorită faptului că se lucrează cu volume imense de date (de regulă peste 500 GB), se impune o ierarhizare a priorităŃii datelor în funcŃie de gradul lor de utilizare. În general, datele vechi nu se mai consultă la nivel de detaliu: foarte rare sunt cazurile în care cineva este interesat de numărul de bucăŃi dintr-un anumit produs vândute într-o anumită zi a anului 1991 la un anumit magazin. Aceste date vor fi transferate pe un suport mai lent (discuri optice, bandă magnetică, etc), păstrand la nivelele de prioritate înaltă doar anumite nivele de sintetizare.

În esentă, acest flux trebuie să asigure că nici o informaŃie importantă nu se pierde şi totodată că informaŃiile mai puŃin actuale sau mai puŃin importante nu blochează în mod inutil canalele de comunicaŃie şi mediile de stocare cu acces rapid.

D. Out-flow

Ieşirea datelor spre utilizatori reprezintă asa-numitul out-flow. Prin această deschidere, valoarea informaŃională creată prin data warehousing devine disponibilă pentru întreaga organizaŃie, oferind un substanŃial suport pentru conducerea optimă a activităŃii. Ca şi în cazul fluxul de intrare, fluxul de ieşire este posibil doar cu suportul unui middleware funcŃional. Spre deosebire de in-flow, unde legătura se făcea mai ales către bazele de date ale sistemului tranzacŃional, în acest caz middleware-ul trebuie să vizeze staŃiile de lucru ale clienŃilor. Out-flow reprezintă "tejgheaua" depozitului de date.

Există două activităŃi principale care formează acest flux: • Accesarea datelor. Această activitate se caracterizează prin faptul că iniŃiativa

aparŃine clientului, care solicită informaŃiile de care are nevoie. Cererile de acces la date pot fi de natură ocazională (interogări ad-hoc), de rutină (zilnice, săptămânale etc) sau, în unele cazuri, chiar în timp-real (continue).

• Livrarea datelor. În acest caz iniŃiativa aparŃine depozitului de date, care trimite din proprie iniŃiativă anumite date către anumiŃi clienŃi. Data Warehouse face publice diverse obiecte (business objects) care sunt actualizate periodic iar clienŃii pot să-şi aleagă seturile de obiecte care le sunt cele mai utile şi să le primească automat. Acestea sunt de regulă împachetate în formate uzuale şi pot fi reflectate automat în documente (prin legături dinamice de gen publish/subscribe sau DDE).

Deciziile luate pe baza analizei economice facilitate de informaŃia din depozitul de date se vor concretiza în operaŃii economice, consemnate prin tranzacŃii în sistemul operativ, care la rândul lui va crea viitoarele date de intrare în depozitul de date. Uneori

92

influenŃa deciziilor poate fi estimată sau măsurată tot prin instrumente de analiză. La modul teoretic măcar, putem considera că acest flux este conectat la fluxul de intrare, procesul decizional formând un cerc închis.

E. Meta-flow

Metadatele, fiind date despre date, descriu structura şi conŃinutul depozitului de date. Dar cum structura şi conŃinutul au la rândul lor o dinamică, exprimată prin cele patru fluxuri descrise până acum, rezultă că există şi o dinamică a metadatelor. În principiu, acest meta-flow descrie şi conectează cele patru fluxuri, fiind un meta-model al dinamicii depozitului de date.

Depozitul de date nu este o aplicaŃie care să poată fi cumpărată "de gata". Mai mult, ea nu este proiectată odată pentru totdeauna. Adaptabilitatea sistemului operaŃional la conditiile mereu noi ale activităŃii impune o adaptabilitate corespunzătoare a sistemului informaŃional. Dacă apar schimbări în aplicaŃiile organizaŃiei, ele trebuie să se reflecte în definiŃiile procedurilor de intrare asfel încat să nu afecteze ieşirile. De asemenea, schimbările în cerinŃele utilizatorilor trebuie să poată fi rezolvate prin adaptarea corespunzătoare a fluxurilor interne.

Există două aspecte importante legate de meta-flow: • meta-flow este instrumentul principal de administrare a depozitului de date.

Cum acest depozit este de fapt puntea dintre datele brute şi instrumentele de analiză, o bună proiectare a acestui flux trebuie să asigure imunitatea fiecărui subsistem în parte la schimbări intervenite în celelalte.

• meta-flow înseamnă de fapt modelare, atât a sistemului informatic, cât şi a activităŃii de ansamblu. Ispita perfecŃiunii ne-ar putea îndemna să începem proiectarea unui Information Warehouse cu modelarea activităŃii întregii organizaŃii şi a sistemului informatic. Probabil că dintre toate abordările posibile, aceasta este cea mai păguboasă: practic, nu există şanse de a termina vreodată (cu atât mai puŃin în timp util) o astfel de analiză. Adevărata provocare a proiectării şi administrării unui depozit de date este de a obŃine rezultate imediate şi de a permite o evoluŃie continuă a sistemului prin îmbunătăŃiri succesive. Iar cheia succesului în această direcŃie o reprezintă dinamica metadatelor.

3.10. Proiectarea depozitelor de date În proiectarea bazelor de date utilizate pentru crearea unui depozit de date este

necesar să se înŃeleagă cum vor fi folosite datele. Bazele de date trebuie construite astfel încat să permită interogări ad hoc la care să se poată primi răspuns în condiŃii de performanŃă acceptabile.

Figura 1 reprezină o schema clasică de depozit de date, în fomă de stea, în care utilizatorii finali accesează direct datele derivate din mai multe sisteme sursă, prin intermediul depozitului de date. În această schemă datele operaŃionale sunt curăŃate şi procesate înaintea introducerii în depozit.

O schemă de “joncŃiune stea“ suportă două tipuri de interogări: consultare şi joncŃiuni multiple. OperaŃia de consultare se realizează pe o singură tabelă de fapte şi nu necesită joncŃiuni. O cerere de interogare tipică apare atunci când un utilizator final solicită o listă derulantă. Interogările de tip joncŃiune multiplă apar după o serie de consultări şi implică restricŃii plasate în câteva tabele dimensiune care sunt puse în

93

legătură simultan, prin operaŃia de jonctiune, cu tabela de fapte. Scopul este de a aduce sute şi mii de înregistrări de bază într-un set de răspunsuri de dimensiune mică.

Dimensiunile în acest caz sunt denormalizate, ele având date redundante care elimină necesitatea unor legături multiple între tabele. Într-o schemă stea nu există decât o singură legatură între tabela de fapte şi dimensiuni. Optimizarea performanŃei de răspuns la interogări este principalul avantaj al acestui model.

Schema de tip “Fulg de Nea” este o variantă a modelului stea în care o parte din tabelele dimensiune sunt normalizate, iar datele sunt distribuite în tabele suplimentare (Figura 2). Rezultă o schemă reprezentată într-un grafic similar unui fulg de zăpadă. DiferenŃa între modelul stea şi modelul fulg de nea este că tabelele dimensiune din acesta pot fi păstrate în formă normalizată, ceea ce determină o redundanŃă redusă. Asemenea tabele sunt uşor de întreŃinut şi astfel se economiseşte spaŃiu de stocare. Totuşi această economie de spaŃiu este neglijabilă în comparaŃie cu volumul foarte mare de date din tabelul de fapte. Mai mult, structura fulg de nea poate reduce performanŃa extragerii de date deoarece sunt necesare mai multe joncŃiuni între tabele la o singură interogare.

Cuburi de date - Un mod mai simplu de vizualizare a datelor este reprezentarea într-un spaŃiu cartezian definit pe toate dimensiunile depozitului de date (Figurile 3 şi 4). Acesta poate fi numit cub de date, fiind un spaŃiu de date logic şi nu unul fizic.

SecŃiunile bidimensionale sunt numite tablouri. Axele cubului sunt reprezentate de dimensiuni, la intersecŃia acestora fiind variabilele sau măsurile

În analiza multidimensională cubul de date cu mai mult de trei dimensiuni poartă denumirea de cub n-dimensional sau hipercub (hypercub). Consiliul OLAP defineşte cubul n-dimensional ca fiind ”un grup de celule de date aranjate după dimensiunile datelor. O matrice tridimensională poate fi vizualizată ca un cub cu fiecare dimensiune formând o faŃă a cubului”. Tot în aceeaşi definiŃie se menŃionează că dimensiunile tipice ale datelor dintr-o întreprindere sunt timpul, măsurile, produsele, regiunile geografice, canalele de distribuŃie.

94

Figura 1

Figura 2

95

Figura 3 – cub de date cu 3 dimensiuni

Figura 4 – cub de date cu 4 dimensiuni

96

Concluzii Pentru a răspunde la întrebarea dacă este sau nu necesar un depozit de date,

trebuie să începem prin a analiza modul de obŃinere a unui raport într-un mediu IT clasic prin comparaŃie cu un mediu IT cu aplicaŃii integrate (hub and spoke) şi care beneficiază şi de o componentă de depozit de date.

Această analiză simplă ar arăta că diferenŃele de cost pentru obŃinerea şi executarea aceluiaşi tip de raport pot fi de câteva zeci de ori mai mici în favoarea mediului IT ce conŃine un depozit de date. Motivele pentru care apar aceste diferenŃe Ńin de costurile mai mari într-un mediu IT classic, de colectarea datelor prin implicarea mai multor resurse, de timpul mai mare alocat acestei sarcini şi, implicit, de dificultatea respectării unor termene.

Mai mult, mediul clasic este incapabil să ofere răspunsuri on line. O altă diferenŃă constă în calitatea datelor, care, într-un mediu classic, au o

cantitate mare de balast, de informaŃii inutile şi irelevante pentru raportul în discuŃie. Aceste date sortate cu o granularitate mai mare fac raportul mai lung, mai ineficient şi, implicit, mai scump de obŃinut. Mediile IT clasice nu au fost gândite pentru optimizarea rapoartelor, ci au fost orientate către procese, iar structura lor internă nu permite aceasta.

Pe de altă parte obiectivul dezvoltării depozitelor de date este optimizarea sistemului de raportări şi nu ar trebui să constituie o surpriză costurile mult mai mici. Nu doar executarea raportului inseamnă un cost redus pentru o arhitectura ce conŃine un depozit de date, ci şi dezvoltarea raportului ca muncă suplimentară a departamentului IT. În mediul clasic trebuie mai intâi integrate datele şi apoi executat raportul, în timp ce într-un depozit de date acestea sunt deja integrate şi disponibile imediat.

Cu un depozit de date, un analist are la dispoziŃie metadate care îi reduc timpul de căutate şi identificare a informaŃiilor relevante şi îi permit să se concentreze preponderent pe construirea raportului. Mai mult, într-un depozit de date un raport deja realizat poate fi identificat imediat şi folosit pentru generarea unuia nou, ceea ce înseamnă timp de reacŃie mai bun, lucru imposibil într-un mediu IT clasic. Un alt element ce contribuie la reducerea costurilor într-un depozit de date este identificarea simplă a istoricului datelor, a relevanŃei şi a exactităŃii acestora, activitate de asemenea dificilă şi consumatoare de timp. Mai mult, posibilităŃile de sintetizare dintr-un depozit de date contribuie la reducerea costurilor.

Ultimul aspect important este acela că într-un mediu IT clasic există riscul ca realizarea raportului să nu fie posibilă, în timp ce realizarea unui raport intr-un depozit de date este o sarcină obişnuită.

Dincolo de costurile obŃinerii şi executării raportului, există diferenŃe majore în viteza de dezvoltare. Într-un mediu clasic obŃinerea unui raport durează o perioadă mai mare de timp, în timp ce într-un depozit de date acesta se obŃine imediat sau chiar în timp real. Alte aspecte de notat ar mai fi cele legate de modificările care pot fi aduse unui raport, mai ales că aceasta implică o muncă de descoperire, fără ca finalitatea să fie întotdeauna evidentă. Depozitul de date este mediul ideal pentru un proces de descoperire şi schimbare frecventă, astfel încât un raport iniŃial este doar un punct de plecare.

97

Datorită obiectivelor impuse de utilizarea depozitelor de date, în analiză se desprind câteva caracteristici mai importante pe care acestea le deŃin:

Depozitul de date asigură accesul la datele organizaŃiei. Accesul trebuie să se realizeze într-un timp cât mai scurt, la cerere şi să fie performant. Datele într-un depozit de date pot fi separate şi combinate pentru a oferi un acces cât mai rapid şi un timp de răspuns cât mai mic sistemului. De asemenea, accesul presupune existenŃa unor utilitare care să fie foarte uşor de folosit.

Datele dintr-un depozit de date trebuie să fie consistente. ConsistenŃa presupune faptul că atunci când două persoane solicită acelaşi set de informaŃii să primească aceleaşi date, chiar dacă ele au fost cerute la momente de timp diferite. Dacă datele nu au fost complet încărcate, atunci utilizatorul va fi avertizat cu privire la acest lucru şi este sfătuit să aştepte pâna când vor fi complet încărcate.

Datele din depozite sunt utilizate direct în analize, fără alte prelucrări suplimentare. Datele nu sunt doar centralizate, integrate şi stocate ci, după ce sunt extrase dintr-o varietate de surse, sunt corectate din punct de vedere al erorilor, transformate, li se asigură calitatea necesară şi abia apoi devin utilizabile. Depozitele de date nu reprezintă doar datele, ci şi un set de utilitare pentru a interoga, analiza şi prezenta informaŃiile.

Calitatea datelor din depozitele de date este un factor determinant pentru procesul de analiză. Se întâlneşte frecvent situaŃia în care datele nu sunt de bună calitate, nu sunt extrase în întregime sau au un caracter incert din punct de vedere al conŃinutului, ceea ce face ca analiza ulterioară să conducă la rezultate eronate.

O consecinŃă importantă a acestor caracteristici este redundanŃa datelor. Dacă în modelul relaŃional redundanŃa este eliminată (prin procesul de normalizare) în scopul evitării anomaliilor de actualizare, în depozitul de date redundanŃa este creată în mod intenŃionat prin denormalizare şi agregare pentru a permite un acces mai rapid la date.

Integrarea datelor reprezintă o altă consecinŃă importantă a realizării depozitului de date si, în cele din urmă, raŃiunea pentru care acesta este creat. Datele sunt încărcate pentru a răspunde nevoilor informaŃionale ale întregii organizaŃii, asigurând faptul că rapoartele generate pentru diverse compartimente vor conŃine aceleaşi rezultate. Sistemul operaŃional este de cele mai multe ori format din subsisteme semi-independente, create la momente diferite, de echipe diferite, în maniere diferite, ceea ce face imposibilă folosirea acestui sistem pentru analiză.

Integrarea datelor provenind din sistemul operaŃional şi din alte surse se referă la diferite aspecte: modalităŃi unice de codificare, sistem de unităŃi de măsură consistent, sistem stabil de reprezentare fizică a datelor, convenŃii clare privind modul de reprezentare a datelor calendaristice, convenŃii unice privind denumirile şi conŃinutul acestora. O arhitectură complexă este structurată pe patru niveluri distincte de realizare a datelor, astfel: nivelul surselor de date, nivelul transformării datelor, nivelul depozitului de date, nivelul de prezentare şi raportare a datelor

Prin urmare, putem concluziona că un depozit de date reprezintă o modalitate de integrare şi organizare a datelor din surse omogene şi neomogene, provenite din sisteme tranzacŃionale, dar şi din fişiere externe, integrate după anumite criterii, supuse unui proces de extragere, transformare şi încărcare, stocate agregat pe niveluri ierarhice, destinate prelucrărilor şi analizelor dinamice, fiind soluŃia optimă de organizare a datelor pentru sistemele informatice suport de decizie şi executive.

98

4. BAZE DE DATE MULTIMEDIA

4.1. Generalit ăŃi Baza de date reprezintă o mulŃime structurată de date, accesibile cu ajutorul

calculatorului, care pot satisface, în timp minim şi într-o manieră selectivă, mai mulŃi utilizatori. Această mulŃime de date modelează un sistem sau un proces din lumea reală şi serveşte ca suport unei aplicaŃii informatice.

Realizarea unei baze de date presupune analiza sistemului pentru care se construieste baza de date, proiectarea structurii bazei, incarcarea datelor in baza, exploatarea si intretinerea bazei.

Realizarea efectiva a unei aplicatii presupune stabilirea temei, analiza si proiectarea aplicatiei, codificarea acestei, testarea modulelor, implementarea si apoi intretinerea aplicatiei.

Pe măsură ce volumul de date a crescut, modelele iniŃiale pentru proiectarea şi gestionarea informaŃiei nu au mai fost suficiente şi locul acestora a fost luat de modelul relaŃional, care a revoluŃionat lumea bazelor de date. UşurinŃa cu care se poate reprezenta o multitudine de aspecte ale realităŃii într-un model relaŃional şi buna sa fundamentare l-au făcut să devină cel mai popular model de baze de date.

În ultimii ani, s-a observat însă că modelul relaŃional, cu toate îmbunătăŃirile care I-au fost aduse, nu poate modela aspecte ale realităŃii contemporane, care abia acum au început sa-şi facă apariŃia. Documentele complexe, incluzând date compuse, care nu se pot reprezenta relaŃional, imaginile şi sunetele, aplicaŃiile software de mare amploare au demonstrat incapacitatea conceptului relaŃional de a modela noile aspecte ale realităŃii. Ca atare, cercetările s-au îndreptat asupra unor modele care permit, cel puŃin teoretic, studierea mai profundă a unor aspecte complexe ale realităŃii. Unul dintre acestea este modelul orientat obiect. Modelarea orientată obiect are aplicaŃii multiple în toate domeniile importante ale realităŃii datorită puterii conceptelor de: clasă, încapsulare, moştenire, etc.

Combinarea tehnologiei bazelor de date relaŃionale cu cea a programării orientate obiect a permis implementarea conceptului de model obiect-relaŃional (hibrid).

Cuvântul „multimedia” provine de la cuvintele „mulŃi” (mai multe) şi „media” (medii de transmitere şi prezentare a informaŃiilor) şi se referă la abilitatea de a achiziŃiona, manipula, combina şi reda informaŃii de la o mare varietate de medii, ce includ text, grafică, animaŃie, sunet, imagine fixă sau video. Multimedia nu este deci o tehnologie, ci mai de grabă un termen ce descrie un număr de tehnologii care lucrează împreună.

Aşadar, noŃiunea de multimedia, defineşte integrarea într-o concepŃie unitară a imaginilor, textelor şi sunetelor care formează un document.

Bază de date multimedia = o bază de date care realizează o uniune între disciplinele de regăsire a informaŃiilor şi de management al bazelor de date, care până acum erau considerate ca fiind două discipline total diferite şi disjuncte.

De aici rezultă şi numărul mare de aplicaŃii al acestora, şi anume: • în informare - multimedia este modul cel mai rapid, eficient şi ieftin în comparaŃie cu alte medii de informare a publicului, cuprinzând adevărate enciclopedii electronice. • în administrarea documentelor şi a înregistrărilor - în întreprinderi şi instituŃii comerciale, acestea având nevoie de diverse documente, în funcŃie de specificul lor.

99

• în educaŃie şi instruire - în regăsirea materialelor pentru pregătirea tuturor persoanelor. • în reclame - în mod practic nu există nici o limită în folosirea informaŃiei multimedia în astfel de aplicaŃii. • în controlul şi monitorizarea proceselor în timp real - împreună cu bazele de date active, prezentările multimedia de informaŃii au un rol efectiv în operaŃiile de monitorizare şi control în sistemele de transport, de supraveghere a pacienŃilor.

Pentru realizarea tuturor acestor aplicaŃii în condiŃii optime, bazele de date multimedia trebuie ca, pe lângă asigurarea unui timp minim de acces la date, să garanteze şi integritatea, securitatea şi independenŃa datelor.

4.2. Informa Ńiile multimedia în format digital Pentru a fi prelucrate cu ajutorul calculatorului, datele audio şi video vor fi codificate

digital, fişierele obŃinute astfel putând fi redate si prelucrate cu produse software specifice (multimedia). De exemplu, sistemele de operare Windows sunt prevãzute cu o gamã de accesorii multimedia pentru redarea si procesarea fişierelor audio-video (Sound Recorder, CD Player, MIDI, Media Player).

A. Secven Ńe de sunet

Undele audio vor fi convertite în formă numericã (digitală) de un convertor analogic-numeric specific. Procesul de transformare poate introduce erori (numite zgomote), dar acestea sunt în general minore, nedetectabile de urechea umanã. Convertirile digitale al informatiilor sonore sunt necesare pentru prelucrãrile sau transmisiile digitale ale datelor sonore: accesarea acestora prin intermediul calculatoarelor sau retelelor de calculatoare, convorbiri telefonice prin linii analogice si centrale digitale, crearea CD-urilor audio etc.

Fisierele de sunet pot fi usor prelucrate pe calculator, prin intermediul unor programe specifice, care permit utilizatorilor sã înregistreze (memoreze), să redea, să editeze sau să mixeze unde sonore provenite din surse multiple. Una din cele mai cunoscute aplicaŃii pentru prelucrări muzicale este MIDI (Musical Instrument Digital Interface), o interfată digitală standard care simulează diverse instrumente muzicale şi efecte speciale, inclusiv sunete din natură sau produse de diverse aparate si asigură gestiunea lor. MIDI este un echivalent informatic de redare/creare a unei partituri sonore şi poate fi folosit de către muzicieni ca un instrument de dezvoltare pentru secvenŃele de sunet. Mesajele audio conŃin frecvent discursuri umane; pentru transmiterea lor eficientă pe cale digitală, s-au creat sisteme de generare şi transmisie a vocii care folosesc modele de sisteme vocale ce reduc vocea la câŃiva parametri esenŃiali, cu proprietăŃi fonetice particulare.

B. Secvente video - reprezentare si afisare

Derularea imaginilor video are la bază succedarea unui anumit număr de imagini statice în fiecare secundă. Dacă se utilizează cel putin 25 de imagini pe secundă, ochiul uman nu sesizează faptul că imaginile sunt discrete, ci le percepe ca imagini continue, în miscare. Acest principiu stã la baza tuturor sistemelor de producere a filmelor, inclusiv a televiziunii - care este în esenŃă, analogică.

100

Într-un sistem digital, fiecare cadru este reprezentat sub forma unei grile dreptunghiulare de puncte luminoase numite pixeli. Pentru o imagine în alb şi negru, un bit (0 sau 1) este suficient pentru reprezentarea unui pixel. Nivelurile de gri (în număr standard de 256) se pot reprezenta dacă fiecare pixel este codificat pe 8 biti.

Sistemele color uzuale cu 256 de culori folosesc în consecinŃă tot 8 biti pentru reprezentarea unui pixel. Evident, pentru reprezentarea unui număr mai mare de culori ar trebui utilizaŃi mai mulŃi biŃi în codificarea fiecărui pixel dar introducerea unui număr mai mare de culori nu este relevantă din moment ce ochiul uman nu ar putea distinge diferenŃe infime între două nuanŃe apropiate.

O imagine reŃinută prin codificarea fiecărui pixel conŃinut este de tip BiT-Map (hartă de biŃi) şi are dimensiuni destul de mari. Un format de dimensiuni mult mai mici pentru memorarea imaginilor şi, în consecinŃă, larg utilizat, este GIF. Există programe care permit conversia unor tipuri de reprezentări ale imaginilor în alte tipuri de reprezentări – conversia fişierelor de tip imagine statică.

ConfiguraŃiile obişnuite ale monitoarelor de calculator sunt: 640×480 pixeli (VGA), 800×600 pixeli (SVGA), 1024×768 pixeli (XVGA). Această caracteristică, dată de numărul de pixeli de pe ecran, se numeşte rezoluŃie. Raportul dintre numărul de pixeli pe orizontală şi verticală, important pentru simetria figurilor, se numeşte raport de aspect şi are valoarea 4/3.

Numărul de cadre derulate pe secundă pentru imaginile animate, ca şi în cazul analogic, este de cel putin 25. Monitoarele de înaltă calitate a calculatoarelor redesenează ecranul de 75 de ori pe secundă (sau chiar mai mult) iar pentru eliminarea pâlpâirii, acelaşi cadru de reafisează de câteva ori la rând. Procesul de reafişare este simplificat de faptul că într-un calculator imaginea ecranului este memorată.

C. Compresia datelor multimedia memorate si transm ise

InformaŃiile multimedia au dimensiuni foarte mari; de aceea, pentru transmiterea lor trebuie găsită o formă comprimată echivalentă sau foarte apropiată - cu o dimensiune considerabil redusă dar păstrând în esenŃă aceleaşi informaŃii. Un factor important în buna funcŃionare a transmisiilor multimedia actuale îl constituie aplicarea tehnicilor de compresie dezvoltate în ultimele decenii.

Păstrarea într-o formă comprimată a datelor multimedia are avantaje evidente si din punctul de vedere al memoriei externe necesare pentru a le reŃine. În plus, trebuie să existe produse soft capabile să prelucreze datele în acea formă.

Compresia datelor transmise presupune aplicarea unui algoritm de codificare a datelor înainte de a le emite şi a unui algoritm de decodificare la destinaŃie (acesta din urmă va reconstrui forma iniŃială, sau o formă foarte apropiată de aceasta, din datele comprimate). Practic, este important ca algoritmul de decodificare să fie rapid şi puŃin costisitor din punctul de vedere al resurselor folosite fiindcă într-o reŃea de calculatoare, un fişier comprimat reprezentând informaŃii multimedia poate fi accesat şi transferat repetat, de mai mulŃi utilizatori. În cazul în care anumite informaŃii multimedia se transmit în timp real - de exemplu, în videoconferinŃe, codificarea rapidă a datelor este de asemenea importantă pentru a se asigura eficienŃa transmisiei.

Aceste necesităŃi, la care se adaugă faptul că diferenŃe minore în secvenŃele multimedia sunt adesea imperceptibile de către om, dau o caracteristică importantă a codificărilor / decodificărilor multimedia: procesul de codificare / decodificare nu trebuie

101

să fie perfect reversibil dar trebuie să fie rapid si să furnizeze o formă comprimată foarte apropiată de forma iniŃială, şi de dimensiuni convenabile. Acceptarea unui număr mic de informaŃii pierdute este foarte avantajoasă pentru ratele de compresie uzual necesare. Astfel, algoritmii de compresie cu pierderi mici de informaŃie în rezultat faŃă de forma originală sunt adesea preferaŃi celor care furnizează o ieşire absolut identică dar sunt mai costisitori.

Urmărind aceste principii, în 1993 au fost adoptate două standarde de codificare: pentru compresia imaginilor statice cu tonuri continue - standardul JPEG (Joint Photografic Experts Group) iar pentru filme - MPEG (Motion Picture Experts Group), care foloseşte codificarea JPEG pentru fiecare cadru separat.

Aceste tipuri de codificãri sunt foarte importante pentru eficienŃa memorării şi transmiterii imaginilor statice şi animate; de exemplu, o imagine va fi mult mai convenabil memorată în format JPEG decât în format BMP.

D. Probleme care apar în bazele de date multimedia

AplicaŃiile multimedia conŃin mii de imagini statice şi dinamice, documente, texte, segmente audio şi video; organizarea acestora depinde de modelarea structurilor şi a conŃinutului de date.

O primă problemă este generată de conflictul care apare între aplicarea tehnicilor bazelor de date şi a celor de regăsire a informaŃiilor. În sistemele de baze de date, modelarea conŃinutului de date nu este o problemă deoarece datele au o structură rigidă. Pe de altă parte, regăsirea informaŃiilor se ocupă în special cu modelarea contextului documentului (prin cuvinte cheie, indexuri, reŃele semantice, etc).

Design-ul conceptual, logic şi fizic este următoarea problemă care apare şi la care nu există încă un răspuns clar.

Stocarea datelor multimedia pe suporturi standard; această etapă prezintă probleme de reprezentare şi compresie/decompresie. TendinŃa în prezent este de arhivare a informaŃiilor astfel încât să se reducă dimensiunea zonei tampon în timpul operaŃiilor de intrare/ieşire. O modalitate de a elimina aceste probleme este folosirea standardelor ca JPEG sau MPEG; pentru bitmap-uri există BLOB (Binary Large Object) care facilitează stocarea şi regăsirea datelor. Pentru documente există deja aplicaŃii, cum sunt Encode/Uuencode (Windows), Tar (Unix), etc., care realizează compresia/ decompresia acestora (deocamdată în stadiu incipient de dezvoltare).

Regăsirea informaŃiilor este o problemă mai ales în cazul imaginilor dinamice şi al segmentelor audio şi video, deoarece de multe ori acestea conŃin informaŃiile relevante. Pentru bazele de date regăsirea se face cu ajutorul limbajului de interogare (SQL) şi a structurilor indexate. Problemele care apar se datorează în primul rând navigatoarelor (foarte diferite) cu care se lucrează, deoarece fiecare interpretează în mod diferit imaginile, în funcŃie de platforma pe care rulează. În al doilea rând, există o limitare fizică a driverelor cu care se lucrează pentru regăsirea acestor tipuri de informaŃii; în multe cazuri informaŃiile nu pot fi accesate, navigatorul anunŃând printr-un mesaj soft-ul necesar.

O altă problemă care apare este cea a performanŃei. Pentru aplicaŃiile multimedia ce conŃin simple documente şi text, constrângerile de performanŃă sunt subiectiv determinate de către utilizatori. În cazul aplicaŃiilor cu imagine video în mişcare, sau sincronizare audio-video, se poate vorbi de o limitare fizică.

102

Internetul

Un prim răspuns la aceste probleme a fost apariŃia Webului. Accesarea publicaŃiilor Web se face cu ajutorul unor soft-uri numite browsere

(Mosaic fiind primul apărut, Internet Explorer, Netscape, etc.), iar scrierea lor se realizează cu ajutorul limbajului HTML (HyperText Markup Language), bazat pe legăturile hipertext.

Pentru folosirea întregii puteri a multimediei, sistemul trebuie să aibă un model de construcŃie care să-i permită utilizatorului folosirea de legături între oricare două noduri arbitrare ale reŃelei. Legăturile hypermedia realizează acest lucru şi pot avea mai multe forme: • pot fi însoŃite de o descriere detaliată sau nu a lor • pot să pornească de la un nod dat sau de la oricare nod • pot fi direcŃionate sau nedirecŃionate

La un sistem de informare bazat pe regăsirea datelor multimedia, mecanismul de interogare trebuie să aibă acces atât la legături, cât şi la informaŃiile asociate acestora. Sistemul trebuie să fie facil atât pentru definirea imaginilor însoŃite de legături, ca şi pentru definirea legăturilor publice şi private.

Ierarhizarea informaŃiilor este procedeul folosit în prezent în bazele de date multimedia, fiind în acelaşi timp şi primul pas pe care trebuie să-l facă cel ce creează astfel de informaŃii. O legătură hipermedia generată automat nu prezintă nici o informaŃie despre nodurile intermediare care au fost conectate. Pe de altă parte, legăturile generate manual şi informaŃiile asociate lor pot fi folosite la obŃinerea mai multor informaŃii despre nodurile care se conectează. Se deprinde de aici concluzia că este neceară o prezentare ierarhizată, bazată pe legături (bidirecŃionate) însoŃite de informaŃii a unei astfel de baze de date multimedia. În prezent, documentele multimedia sunt prezentate ierarhizat prin intermediul limbajului HTML.

HTML-ul pe lângă faptul că este un limbaj simplu şi total independent de hardware, permite realizarea tuturor acestor proprietăŃi importante ale oricărui sistem ce foloseşte informaŃii multimedia. Dar editorul HTML nu este WYSIWYG. În prezent există procesoare de documente Web care pot fi utilizate atât la crearea cât şi la formatarea documentelor HTML, păstrând caracteristicile acestuia. Dintre acestea cele mai răspândite sunt: HotMetaL, HTML Assistant, Spider, WebAuthor.

Regăsirea informaŃiei prin folosirea imaginilor indexate a fost rezolvată în mai multe moduri, fără a da satisfacŃie totală.

Prima abordare foloseşte tehnica de procesare a imaginilor la identificarea automată a anumitor obiecte. O problemă care apare aici se referă la scală (mărimea imaginii). Tehnologiile permit ca în documente să se încarce imaginea, într-o primă fază, la scară redusă, ceea ce ar rezolva oarecum problema stocării acestora.

O altă metodă se bazează pe una din următoarele tehnici de indexare manuală a termenilor şi/sau expresiilor ce însoŃesc imaginea repectivă: • clasificarea imaginilor ierarhic, dintr-o anumită categorie, • folosirea cuvintelor cheie (analogă indexării documentelor), • utilizarea schemei entitate-atribut-relatie (Leung 1992).

Realizarea tuturor acestor aplicaŃii în condiŃii optime nu trebuie să elimine asigurarea cerinŃelor unei baze de date, cum sunt: timp minim de acces la date, să garanteze integritatea, securitatea şi independenŃa datelor.

103

Instrumente folosite la realizarea documentelor mul timedia

Primul instrument care s-a impus şi pe baza căruia se construiesc în prezent documentele multimedia este limbajul HTML. Acesta s-a dezvoltat treptat, deoarece îi lipseau posibilităŃile de a descrie publicaŃii electronice profesionale. Prima lui formalizare, standardul HTML 2.0, deşi mai performant, nu a reuşit să satisfacă cerinŃele unei publicaŃii electronice profesionale. Despre folosirea unor editoare WYSIWYG nici nu poate fi vorba, deoarece navigatoarele afişează acelaşi document destul de diferit, în funcŃie de platforma pe care lucrează.

Dacă într-o revistă imaginile sunt statice, într-o revistă electronică ele pot fi alternate sau dinamice(slide-show). Documentele dinamice au fost ideea Netscape, care le-a introdus pe piaŃă o dată cu versiunea Netscape 1.0. Metoda, numită Client Pull, îi permite navigatorului să încarce un document după un număr de secunde, fără nici o intervenŃie din partea utilizatorului.

Natura Web-ului se schimbă rapid, de la pagini statice, la pagini a căror formă şi conŃinut se schimbă de fiecare dată când sunt încărcate. Acest lucru este posibil datorită unor limbaje special concepute pentru a putea fi folosite la scrierea de programe care se inserează direct în documentele HTML. Ele caracterizează ceea ce numim situri Web din a doua generaŃie.

Primul dintre acestea, Java, a fost lansat de către firma Sun în 1995, părintele lui fiind James Gostling. Limbajul Java este o simplificare a limbajelor orientate pe obiecte, de fapt a C++ -ului. Java păstrează proprietăŃile acestora, fiind flexibil, simplu, puternic, independent de platforma hardware pe care rulează.

Cel mai dinamic dintre aceste limbaje pare a fi JavaScript. Deşi nu este încă în forma finală, limbajul prezintă avantajul scrierii unor programe simple direct în paginile HTML, programe care pot fi interpretate local de către navigator. JavaScript seamănă cu Java, dar spre deosebire de acesta JavaScript are funcŃii şi declaraŃii de sine stătătoare. Un alt avantaj al acestui limbaj este siguranŃa, deoarece nu poate fi utilizat pentru a accesa şi scrie pe discul clientului. Folosite împreună cu HTML, Java şi CGI, duc la creşterea performanŃelor Web-ului şi a vitezei de lucru a navigatoarelor.

CGI (Common Gateway Interface) s-a impus ca cea mai eficientă, stabilă şi uşor de înŃeles modalitate de manipulare a informaŃiei generate în mod dinamic pe Web. Este de fapt acea parte a server-ului Web care poate comunica cu alte programe care rulează pe sistem. Cu ajutorul acestei interfeŃe, serverul Web poate apela un program. Cele mai răspândite aplicaŃii ale CGI-ului sunt: • prelucrarea datelor inserate în formulare (care necesită un răspuns), • interogarea unor baze de date pentru o anumită informaŃie (se realizează cu aşa numitele browsere: Google, Altavista, Yahoo, prin interogări SQL), • realizarea unor documente virtuale (documente HTML complexe, care conŃin text, imagini, fişiere de sunet sau video).

Limbajul cel mai des utilizat pentru scrierea programelor este Perl, deoarece pare a fi cel mai uşor de utilizat pentru manipularea textelor şi a matricelor. Există şi versiunea WebForms, Q&D Software Development, a cărei interfaŃă grafică permite generarea rapidă şi automată a formularelor şi a scripturilor CGI în Perl.