1

SISTEME DE GESTIUNE A BAZELOR DE DATE

1: SISTEME DE GESTIUNE A BAZELOR DE DATE RELA IONALE

1.1. Defini ii, terminologie Apariia i dezvoltarea calculatoarelor electronice au condus la amplificarea

activitilor legate de stocarea, interogarea i administrarea coleciilor de date. Astzi, cele mai multe dintre activitile noastre zilnice necesit accesarea i actualizarea informaiei dintr-o baz de date: extragerea unei sume de bani din contul bancar, rezervarea unei camere de hotel, cumprarea unui bilet de avion, mprumutarea unei cri de la bibliotec, pltirea 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 colecie de date aflate n relaie unele cu altele i structurat astfel nct s poat servi unui anumit scop = un set de date corelate i organizate n scopul prelucrrii lor rapide i concomitente de ctre 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 expoziiile itinerante (descrise prin perioad, itinerariu, responsabil, custozi participani); 2) baza de date a unui magazin de muzic, n care sunt nregistrate albumele de muzic n funcie tipul de suport fizic (CD, caset etc.), stil, autori, soliti, anul apariiei etc. Observa ie Termenul "Database" (baz de date, n limba englez) a aprut pentru prima dat n titlul unei conferine 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 rezolvrii problemelor din cele mai variate domenii ale vieii reale. Exemple IMS, DB2 (pn 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 colecie de date este o baz de date. De exemplu, lista crilor 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 urmtoarele proprieti: 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: cumprarea unei noi casete n vederea inchirierii, modificarea diferenei permise ntre cursul de cumprare i cel de vnzare al valutei etc.); este o colecie de date coerent din punct de vedere logic i avnd un neles intrinsec (de exemplu: din baza de date asociat bibliotecii liceului nu vor face parte crile de telefon sau lista de materiale didactice din laboratorul de chimie); este proiectat, construit i populat cu date avnd permanent n vedere un anumit scop; o baz de date este destinat utilizrii de ctre un anumit grup de persoane i permite efectuarea unui anumit set de operaii.

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

Cuvntul 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: cantitile de mere obinute anual ntr-o livad de pomi fructiferi, activitile turistice propuse de ghid participanilor la o excursie; modificrile climatice suferite de o regiune a globului terestru de-a lungul unui numr de ani, cursul bancar al unei valute de-a lungul unei luni sau a unui an calendaristic etc.

Exist o diferen esenial ntre date, informaii i cunotine: 1. datele sunt informaii primare care au fost doar culese i inregistrate; 2. informa iile sunt date prelucrate, structurate (validate, corectate, organizate,

sortate, relaionate); 3. cuno tin ele sunt informaii contextualizate. Arhitectura sistemelor de gestiune a bazelor de date este puternic determinat de modelul de date al bazelor de date. Dincolo de definiiile date pn acum, ce este de fapt o baz de date? Este un obiect (asemenea numerelor, funciilor, mulimilor)? Este o metod (asemenea algoritmilor, procedurilor)?

O baz de date este n primul rnd un model al microuniversului la care se refer. Model = (n sens strict) un sistem teoretic sau material cu ajutorul cruia pot fi studiate indirect proprietile i transformrile 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), ascunznd detaliile de implementare, detalii care nu sunt necesare celor mai muli 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 numete 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 aplicaii 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 relaiilor dintre ele i a restriciilor (Constraints) pe care trebuie s le respecte); 2) un set de opera ii de baz (care s specifice modul de efectuare a extragerii i actualizrii datelor din baza de date).

1.2. Modele de date: perspectiv istoric Evoluia modelelor de date pentru bazele de date i SGBD-uri a fost sugestiv

sintetizat de R.G.G. Canttell n articolul su "What Are Next-Generation DB Systems?", publicat n revista Communications of the ACM, n octombrie 1991: "Istoria informaticii a cunoscut multe generaii de sisteme de gestiune a datelor, ncepnd cu sistemele de fiiere indexate, continund apoi cu sistemele de tip ierarhic i de tip reea, iar mai nou cu sistemele relaionale. Acum suntem pe punctul de a intra ntr-o nou generaie 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 susinut 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 "rdcinile" sale trebuie cutate aproximativ acum 40 de ani2 n obiectivul fixat de Preedintele J.F. Kennedy pentru programul Apollo: aducerea primului om pe Lun pn la sfritul anilor '60. In acel moment nu exista nici un instrument informatic care s funcioneze efectiv i care s poat administra uriaele volume de date implicate n programul 1 E.F. Codd este considerat a fi "printele" conceptului de model de date, n general, i al conceptului de model de date relaional, n particular. 2 Data apariiei primului sistem comercial de gestiune a bazelor de date

4

spaial. Ca urmare, North American Aviation (NAA) primul contractor al proiectului, a dezvoltat un software bazat pe o structur ierarhic (prile se agreg n componente din ce n ce mai ample) denumit GUAM (Generalized Update Access Method ). Spre mijlocul anilor '60, IBM s-a alturat NAA dezvoltnd n continuare GUAM i producnd unul dintre primele sisteme comerciale de gestiune a bazelor de date: IMS (Information Management System ). IBM a preluat modelul ierarhic pentru a respecta cerina de stocare a datelor pe benzi magnetice (deci n acces secvenial). Ulterior, aceast restricie a fost nlturat i IMS continu s fie principalul SGBD ieirarhic utilizat de majoritatea calculatoarelor mainframe3.

Construirea bazelor de date a cunoscut o evoluie foarte rapid, trecnd prin mai multe abordri, clasificate dup cum urmeaz: sistemele de fiiere; sistemele prerelaionale (sau "istorice"4): ierarhic i reea, sistemul relaional; sistemele postrelaionale: orientat obiect i hibrid (obiect-relaional); sistemele semantice: multi-dimensional i logic (deductiv).

1.2.1. Modelele prerela ionale Pot fi caracterizate ca modele de moment: au oferit soluii 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 colecie de programe care realizeaz fiecare cte "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 informaiilor, gradul mare de redundan i dependen program-date), sistemul de gestiune bazat pe fiiere a constituit un salt semnificativ fa de fiierele administrate manual: saltul de la abordarea informaional la cea informatic. (a) (b)

Figura 1: Modele de baze de date: (a) ierarhic; (b) reea (II.) Att n modelul ierarhic ct i n modelul re ea, datele erau reprezentate ca mulimi de inregistrri (n sensul limbajului de programare Pascal: colecii de date de diferite tipuri: Integer, Boolean, Real etc.). Relaiile 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 corporaii 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 tradiionale (legacy systems)

5

legaturi de tip pointer (adrese de locaii fizice de memorie). Inregistrrile care formau baza de date erau organizate: n modelul ierarhic: ca o mulime de arbori; n modelul reea: ca o mulime de grafuri.

Ambele modele prerelaionale permiteau accesul la date de-a lungul unor drumuri (ci) predefinite, explicit stabilite la nivelul programelor de aplicaii (de unde i numele de modele navigante). Ca urmare, orice modificare a structurii bazei de date antrena modificarea acestor ci n programele deja scrise. Exemple: pentru modelul ierarhic: IMS (amintit mai sus); pentru modelul reea: IDS II (de la Honeywell), IMAGE (de la Hewlett Packard).

Aplica ie: Modelarea activit ii didactice Intr-o facultate, cadrele didactice desfoar activiti didactice de curs sau examen; aceste activiti sunt pentru studeni i se desfoar n locaii (amfiteatre sau laboratoare). De asemenea, cadrele didactice particip la proiecte de cercetare tiinific. Figura 2 prezint modelul ierarhic al bazei de date; Figura 3 prezint modelul reea. Figura 2: Modelul ierarhic

Figura 3: Modelul reea

Proiect Cadru didactic

Predare

Facultate

Student Cadru didactic Activitate didactic Locaie

Curs Examen Proiect

Examen

Inscriere

Desfurare

Facultate

Student

Activitate didactic

Locaie

Curs

6

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

apariia modelului relaional 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 relaional pentru a rezolva problemele legate de stocarea volumelor mari de date i enuna "celebrele" 12 reguli (condiii) pe care trebuie s le ndeplineasc un SGBD pentru a fi declarat relaional.

S amintim ns existena unui precursor: modelul bazat pe teoria mulimilor, propus de D.L. Childs n articolul su: "Feasability of a Set-Theoretical Data Structure", aprut n 1968 n Proc. Fall Joint Computer Conference.

Cele mai importante prototipuri de sisteme de gestiune a bazelor de date de tip relaional au fost: System R , dezvoltat la San Jose Research Laboratory din California spre sfritul anilor '70. Acest model a condus la:

o apariia unui limbaj structurat de interogare a bazelor de date: SQL, o producerea mai multor SGBD-uri relaionale 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.

Numrul sistemelor relaionale comerciale a ajuns acum la cteva 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 att de mare nct multe sisteme nerelaionale ofer acum i o interfa cu utilizatorii de tip relaional, indiferent de modelul de date pe care se bazeaz de fapt.

Modelul relaional s-a dovedit a fi i un instrument didactic ideal de prezentare a principiilor bazelor de date, tocmai datorit fundamentrii sale riguroase pe principii logice i matematice.

Ce este de fapt un model relaional de date? Informal, l putem defini ca un model n care: datele sunt percepute de utilizatori ca nite tabele i numai ca nite tabele; operaiile disponibile pentru utilizatori (spre exemplu, pentru obinerea informaiilor) sunt operaii care genereaz noi tabele pe baza tabelelor vechi: operaia de selecie (SELECT) extrage o submulime de rnduri dintr-o tabel, operaia de proiecie (PROJECT) extrage o submulime de coloane, operaia de juxtapunere (JOIN) asociaz dou tabele pe baza valorilor identice pe care le conin n anumite coloane, de asemenea identice; ori, toate aceste submulimi rezultate pot fi privite i ele nsele ca nite tabele.

5 E.F. Codd s-a nscut la 23 august 1923 n Portland, Marea Britanie, i a murit n 18 aprilie 2003, n Florida. A fcut 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 relaional; a avut, de asemenea, contribuii n domeniul modelelor de calculabilitate prin lucrrile sale privind automatele celulare. A obinut de dou ori Premiul Turing: n 1981 i 1994.

7

(a) (b) (c)

Figura 4: Operaii cu tabele: (a) selecie; (b) proiecie, (c) asociere

Numele modelului (model rela ional ) provine de la conceptul matematic de rela ie. Aa cum o funcie f : {1, 2,,n} N R are mai multe reprezentri convenionale, dintre care cea mai comod este cea de vector, tot astfel relaia poate avea mai multe reprezentri, 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 relaional sunt (exemplificarea se face pentru baza de date asociat unui liceu): entitatea (Profesori, Elevi, PersonalAuxiliar, Clase etc.), rela ia dintre entit i (PredLa, AreDirigintePe, AreLocIn etc.), atributul (Nume, GradDidactic, DataNaterii, NrLocuri, Locaie etc.).

1.2.3. Modelele postrela ionale Chiar dac se regsete n descrierea unor situaii reale, cu o organizare

intrinsec piramidal, modelul ierarhic i-a atins rapid limitele. La fel, modelul relaional a devenit impropriu pentru rezolvarea unor probleme din realitatea nconjurtoare care presupun manipularea unor volume uriae de informaie, a unei mari varieti de tipuri de date: hri meteorologice sau geografice necesare previziunilor meteorologice sau dirijrii traficului, imagini transmise prin satelit utilizate n msurarea factorilor poluani, date neconvenionale pentru proiectarea asistat de calculator n inginerie sau arhitectur, serii dinamice implicate n tranzaciile bursiere sau bancare, stocarea obiectelor binare mari (BLOBs = Binary Large Objects) necesare n digitalizarea informaiei coninut n fiierele audio sau video. Au aprut astfel i s-au dezvoltat modelele postrelaionale, de generaia a treia: modelul orientat obiect i modelul obiect-relaional. (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 relaional const n distanarea 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 funcii C++ mai degrab dect 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 aplicaiilor software; includerea n aproape orice aplicaie 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). Dei cu o cot de pia semnificativ inferioar sistemului relaional (150 milioane dolari fa de 10 miliarde, numai n SUA n anul 1999), modelul orientat obiect este creditat cu o cretere anual extrem de rapid: 50%. n ciuda caracterului intuitiv i a altor avantaje evidente ale modelului orientat obiect, modelul relaional continu s domine piaa 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 migrrii spre un model complet diferit.

8

(II) Modelul hibrid extinde modelul relaional oferind un set de tipuri de date mai bogat, i include i orientarea obiect. Se incearc astfel combinarea avantajelor celor dou abordri: cea relaional i cea orientat obiect. Astfel, atributele i instanele entitilor pot avea tipuri complexe i pot evita unele dintre restriciile specifice modelului relaional. De exemplu, n timp ce n modelul relaional fiecare atribut trebuie s ia pentru fiecare instan a unei entiti o valoare i numai una din domeniul lui de definiie, n modelul hibrid poate lua un subset de valori (de exemplu: pentru un angajat oarecare, atributul Telefon poate lua ca valori numrul 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 relaionale 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 corespunztoare) au fost sintetizate de M. Stonebraker prin diagrama din Figura 7 (vezi [18]): modelul relaional permite realizarea chiar simultan a unor interogri variate i rapide dar complexitatea datelor stocate nu difer prea mult de complexitatea datelor memorate n baze de date de tip ierarhic sau reea; cu modelul orientat obiect se poate stoca informaie variat i complex (de la texte la sunete i imagini) dar viteza de interogare (n cazul imaginilor i mai ales al sunetelor) este foarte sczut; modelul care pare s elimine toate dezavantajele i s cumuleze toate avantajele modelelor anterioare este modelul obiect-relaional.

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

SGBD relaionale SGBD hibride

SGBD prerelaionale SGBD orientate obiect

Faciliti de interogare / asistena 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. Intruct anumite pri din baza de date sunt relevante pentru unii utilizatori dar irelevante pentru alii putem spune c o baz de date are attea nivele externe ci utilizatori o acceseaz. Mai mult, exist entiti care dei snt reprezentate n baza de date nu apar la acest nivel deoarece snt irelevante pentru anumii 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: spaiul de stocare a datelor, modul de stocare a acestora, structurile de date, organizarea fiierelor etc.);

sunt utilizate funcii ale sistemului de operare pentru plasarea datelor pe dispozitivele de stocare, pentru construirea indecilor, pentru citirea datelor etc.;

Nivelul conceptual (nivelul logic) realizeaz trecerea de la nivelul intern la nivelul extern i asigur independena acestora. Acest nivel:

grupeaz percepiile tuturor utilizatorilor bazei de date (deoarece conine fiecare viziune (view) din nivelul extern, direct sau indirect);

conine structura logic a bazei de date descris prin conceptele de entitate, atribut i relaie, constrngeri referitoare la date, informaii de securitate i integritate;

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

Nivelul extern : imaginea fiecrui utilizator asupra BD

Nivelul conceptual (structura logic a BD): ansamblul datelor stocate n BD i a relaiilor dintre ele (fr 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 relaiile dintre ele dar nu conine detalii referitoare modul de stocare a datelor pe suportul fizic (numrului de octei ocupai pe disc etc.).

Scopul arhitecturii pe cele trei nivele este acela de separare a percepiei fiecrui utilizator individual aspura datelor de modul de reprezentare fizic a acestora n baza de date. Figura 2 ilustreaz acest lucru reprezentnd nivelul intern, nivelul conceptual i dou vederi corespunztoare la nivelul extern: una aparinnd unui utilizator de PL/I, cealalt aparinnd 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 acelai model (relaional, orientat-obiect etc.), nivelul intern nu are nimic de a face cu modelul de date al bazei - el constnd din nregistrri memorate, pointeri, indeci 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 Aa cum am vzut n paragraful anterior, proiectarea unei baze de date incepe

cu analiza situaiei 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 urmtoare const n realizarea modelului conceptual al bazei de date. n cazul modelului relaional, se ncepe cu o descriere detailat a entititilor i atributelor, a relaiilor dintre entiti i a condiiilor 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 Dicionarului Explicativ al Limbii Romne: o entitate este un coninut de

sine stttor, o existen determinat (ca ntindere, importan, valoare etc.). In literatura dedicat bazelor de date exist mai multe definiii 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 naterea unei persoane, marcarea unui gol), o activitate (producia de oel a unei uzine, nchirierea unei maini) 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 nelegem mulimea tuturor elementelor de un anumit tip (care prezint aceleai caracteristici distinctive). Prin instan a unei entiti nelegem un singur element, bine individualizat, unic, din mulimea elementelor care formeaz entitatea respectiv. Observa ie Entitile dintr-o baz de date pot fi disjuncte sau nu; n al doilea caz avem de a face cu subentit i (de exemlu, entitile Piloi i MecaniciDeBord sunt disjuncte i sunt subentiti ale entitii PersonalNavigant). Intr-o baz de date pot exista entiti a cror existen este determinat de existena altora (de exemplu, entitatea PersoaneInIntretinere depinde de entitatea Salariai); primele se numesc entiti dependente , celelalte se numesc entiti principale . Defini ie Atribut = o caracteristic a unei entiti.

Un atribut posed un nume i pentru fiecare instan a entitii poate lua o valoare dintr-o mulime 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 dect simple (atributele Capital, Suprafat, Continent ale entitii 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 tratm ca un atribut compus dac prevedem necesitatea de a avea acces direct la luna sau anul de natere al unei persoane nregistrate n baza de date. Dac ns o astfel de necesitate nu este probabil i dac dorim s simplificm structura entitii (i deci a bazei de date) atunci este preferabil s l tratm 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 instanele entitii respective cte o singur valoare (de exemplu, atributele Capital, Suprafat, Continent ale entitii Tri) sau, dimpotriv, pentru unele instane pot lua cte o singur valoare, pentru altele mai multe valori iar pentru altete nici o valoare (de exemplu, atributul OrasCuMinimum2MilioaneLocuitori al entitii Tri). Cnd este cazul, se pot defini limite inferioare i/sau superioare pentru numrul de valori pe care le poate lua un astfel de atribut pentru o instan oarecare (de exemplu, putem specifica faptul c atributul NrTelefon al entitii 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 stttoare sau care pot fi calculate din valorile altor atribute. De exemplu, s considerm entitile corelate Cri, cu atributul NumrAutori i Autori, cu atributul Titlu; atributul NumrAutori este un atribut derivat: valorile pe care le ia pentru diferite instane ale entitii Cri pot fi calculate pe baza numrului de apariii ale atributului Titlu pentru diferite instane ale entitii Autori.

Utilizarea instanelor unei entiti ridic dou probleme foarte importante: modul de adresare a fiecrei instane a unei entiti; determinarea instanelor care se repet. Pentru a simplifica referirea la instanele unei entiti s-a recurs la mecanismul identificatorului unic (sau al cheii primare). Defini ie Identificatorul unic (sau cheie primar ) = un atribut sau cea mai mic mulime de atribute ale unei entiti care iau, pentru fiecare instan a entitii respective, o valoare i numai una. Atunci cnd nici un atribut sau grup de atribute ale entitii rezonabil de

13

numeros nu ia valori disticte pentru fiecare instan a acesteia se poate aduga un atribut convenional care s ndeplineasc aceast condiie. De obicei, acest atribut este denumit cu ajutorul prefixelor cod sau id .

C. Relaii Oridecteori un atribut al unei entiti se refer la alt entitate din baza de date

se stabilete, de fapt, o relaie ntre cele dou entiti. Cnd proiectm baza de date, aceste referiri nu ar trebui s fie reprezentate ca atribute ale entitilor ci ca rela ii (att n sensul real ct i n sensul matematic al cuvntului) ntre entiti. Atributele prin care se stabilete aceast relaie se numesc chei sau cmpuri de legatur . Defini ie Relaie ntr-o baz de date = o legtur logic ntre dou sau mai multe entiti.

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

Relaiile sunt caracterizate prin grad i cardinalitate (sau tip). Defini ie Gradul unei rela ii = numrul de entiti care particip la relaia respectiv. Dup grad, relaiile pot fi binare (ntre dou entiti; de exemplu: relaia JoacIn este o relaie binar ntre entitile Actori i Filme); ternare (ntre trei entiti; de exemplu: relaia LanseazMelodia este o relaie ternar ntre entitile Compozitori, Textieri i Soliti); n-are (ntre mai multe entiti; de exemplu: relaia Monteaz este o relaie de gradul cinci ntre entitile Regizori, Scenografi, DesigneriCostume, Actori i PieseDeTeatru). Defini ie Cardinalitatea (tipul) unei rela ii binare = = numrul de instane ale celor dou entiti care sunt asociate prin relaia respectiv.

S considerm dou entiti E1 i E2; dup cardinalitate (sau tip), relaiile dintre cele dou entiti pot fi 1 1 (one-to-one); 1 m (one-to-many); n m (many-to-many). Defini ie Relaie 11 = o relaie ntre dou entiti E1 i E2 n care unei instane a entitii E1 ii corespunde o singura instan din entitatea E2 i reciproc. Defini ie Relaie 1m = o relaie ntre dou entiti E1 i E2 n care unei instane a entitii E1 (numit entitate dominant) ii pot corespunde mai multe instane din entitatea E2 dar unei instane din E2 nu-i poate corespunde dect cel mult o instan din E1 . Defini ie Relaie nm = o relaie ntre dou entiti E1 i E2 n care unei instane a entitii E1 ii pot corespunde mai multe instane din entitatea E2 i, reciproc, unei instane din entitatea E2 i pot corespunde mai multe instane din entitatea E1 .

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

de relaie: Defini ie Se numete rela ie peste mulimile M1, M2, Mn orice submulime a produsului lor cartezian: R M1, x M2, x x Mn Exemplu

14

Fie mulimile Marca = {Dacia, Ford, Fiat, Audi, Opel, Volvo}, Tip = {benzin, motorin}, CapacCil = {1100, 1200, 1300, 1400, 1600}, NrLoc = {4,5}, NrUi = {2, 4, 5}. Atunci, entitatea Automobil poate fi reprezentat ca o relaie peste aceste mulimi: Automobil Marca x Tip x CapacCil x NrLoc x NrUi Iat cteva instane ale acestei entiti: (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 generalizm exemplul de mai sus obinem: E A1 x A2 x x An unde am notat cu E entitatea i cu A1, A2, , An mulimile de valori (domeniile) atributelor sale. Un element al acestei relaii (adic un tuplu al produsului cartezian) reprezint o instan ei a entitii E i const din valori particulare ale atributelor. Pentru simplitatea reprezentrii, entitile nu sunt reprezentate ca mulimi de tupluri (ca n exemplul nostru de mai sus) ci ca tabele (vezi Tabelul 4), tot aa cum, n loc s notm cu

15

Figura 8: Implementarea unei relaii 1-1 n Ms Access

E.2. Rezolvarea rela iilor 1-m n acest caz, trebuie s definim noiunea de cheie extern: Fie dou entiti U i V (de exemplu: Clase i Elevi) avnd atributele a1 (cheie

primar), a2, , an i respectiv b1 (cheie primar), b2, , bm, a1, (de exemplu: CodClas, Locaie, nrBanci, nrTable, respectiv CNP, Nume, Prenume, Adresa, CodClas). Prin cheie extern nelegem un atribut al entitii V a crui mulime de valori coincide cu mulimea 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 relaie 1-m ntre dou entiti (aici relaia InCareInva ntre entitile Clase i Elevi) procedm astfel: (1.) includem n descrirea ambelor entiti un acelai 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 bazm pe faptul c n modelul relaional nu numai entitile ci i

relaiile dintre ele sunt relaii n sens matematic i, ca urmare, pot fi reprezentate prin tabele. Am observat, de asemenea, c relaiile dintre entiti pot avea atribute (inclusiv chei primare).

Pentru a o stabili o relaie n-m ntre dou entiti (aici: relaia Comand ntre entitile Clieni i Furnizori) procedm astfel:

16

(1.) includem n descrierea relaiei (aici: Comand) pe post de chei externe dou atribute care s corespund atributelor care funcioneaz drept chei primare pentru cele dou entiti (aici: CodClient i CodFurnizor);

(2.) reducem astfel stabilirea unei relaii n-m (aici: relaia dintre Clieni i Furnizori) la stabilirea a dou relaii 1-m (aici: relaiile dintre Clieni i Comand i respectiv dintre Furnizori i Comand).

Figura 9: Implementarea unei relaii de tip n-m n Ms Access

F. Reguli de integritate pentru bazele de date Dup modelarea bazei de date la nivel structural (definirea entitilor, a atributelor

lor i a relaiilor dintre ele) urmeaz nivelul operaional al modelrii: stabilirea tipurilor de operaii care se pot efectua asupra datelor stocate (sortare,

cutare, vizualizare, adugare, tergere, modificare etc., prezentate mai jos); verificarea respectrii regulilor de integritate (ceea ce va asigura corectitudinea i

consistena datelor). Distingem urmtoarele 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 situaia 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 analizm situaia

unei valori speciale pe care o pot lua atributele entitilor: valoarea Null. Defini ie Null = valoarea pe care o ia un atribut pentru o instan a unei entiti atunci cnd 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 relaional al bazelor de date pentru a permite tratarea excepiilor 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 susinut-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 entitii.

Dac am permite ca un atribut din componena cheii primare a entitii s ia valoarea Null, am contrazice cerina 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 entitii).

F.3. Integritatea referen ial A doua regul de integritate se aplic cheilor externe. Defini ie Fie dou entiti U i V relaionate; pentru orice instan a entitii V (secundar) valoarea cheii externe trebuie s corespund valorii cheii primare a unei instane oarecare a entitii U (principal) sau s fie Null.

Figura 10: Stabilirea integritii refereniale n Ms Access

18

F.4. Restric ii contextuale In etapa de analiz a situaiei reale care trebuie modelat prin baza de date, n discuiile dintre proiectanii bazei de date i viitorii ei proprietari i utilizatori, pot aprea informaii suplimentare privind restriciile pe care trebuie s le ndeplineasc datele stocate i operaiile efectuate asupra lor. Defini ie Restric ii contextuale = reguli suplimentare privind modul de nregistrare a datelor i de efectuare a operaiilor, specifice situaiei reale sau impuse de diferitele categorii de participani 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.

Aa cum am vzut, acest proces de tip top-down ncepe cu identificarea principalelor entiti i relaii; urmeaz ca acestea s fie examinate (inclusiv la nivelul raporturilor dintre atributele care le caracterizeaz) n scopul eliminrii tuturor "defectelor" lor i transformrii ntr-un set de relaii adecvat, coerent i bine structurat. Aceast tehnic a fost iniiat tot de E.F. Codd (a se vedea [6]). El a propus iniial trei seturi de reguli pe care o relaie 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 (dnd astfel i numele tehnicii nsi). Ulterior, R. Boyce a introdus, mpreun cu E.F. Codd, o definiie 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 situaii destul de rar intalnite. S remarcm caracterul "progresiv" al acestor forme normale (ilustrat i prin Figura 1): o relaie aflat n FN3 este automat n FN2 i deci i n FN1. De fapt, cteodat din punct de vedere al performanelor n exploatare este preferabil ca baza de date s fie lsat intr-o form normal inferioar (se execut procesul invers normalizrii, denumit denormalizare a bazei de date).

Figura 11: Formele normale

Este justificat ntrebarea: cte forme normale mai ateapt s fie descoperite? Rspunsul a fost dat de R. Fagin n 1981 (a se vedea [11]). In acest articol este introdus o form normal care se bazeaz pe noiunile de domeniu de valori i cheie primar (FN/DK)i se demonstreaz c o relaie 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 puin din punctul de vedere al eliminrii anomaliilor la modificarea datelor). Defini ie

19

Normalizare = un proces prin care un set de relaii care ncalc anumite principii de proiectare este nlocuit cu un alt set de relaii adecvat, coerent i bine structurat.

Acest proces se desfoar n mai multe etape: n fiecare etap se urmrete eliminarea unui alt tip de defecte ale relaiilor astfel nct, pe msur ce relaiile trec n forme normale superioare, ele devin din ce n ce mai puin vulnerabile fa de anomaliile de actualizare a datelor.

Pentru a prezenta procesul de normalizare, este necesar s definim urmtoarele dou concepte: anomalie la actualizare, dependen funcional.

Pentru simplificare, vom utiliza acolo unde este cazul reprezentarea prin tabele a entitilor i relaiilor din baza de date.

B. Anomalii la actualizare Unul dintre principalele obiective n proiectarea bazelor de date este eliminarea redundanelor (a nregistrrii acelorai date de mai multe ori). Fie, de exemplu, entitile : Elevi {CNP, CodClas, Nume, Prenume, Adres}; Clase {CodClas, Locaie, NrBanci, NrTable}; EleviClase {CNP, Nume, Prenume, Adres, CodClas, Locaie, NrBanci, NrTable}.

Dac n baza de date includem tabelele Elevi i Clase nu vom avea redundane n date; n schimb, dac includem tabelele Clase i EleviClase (sau Elevi i EleviClase) redundana va fi evident. Tabelele care conin date redundante pot genera probleme n momentul actualizrii informaiei. Acestea se numesc anomalii la actualizare i sunt de trei tipuri: 1. anomalii la adugare; 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 relaii care pot produce anomalii de actualizare

20

(1.) Distingem dou tipuri de anomalii la adugare: (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: Adugarea 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 entitilor

Figura 15: Datele despre clasa a XIB au fost terse din tabela Clase dar datele elevilor din acea clas au rmas n tabela EleviClase

Figura 13 : Repetarea datelor despre clas la nregistrarea fiecrui 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 relaiilor dintre atributele

entitilor, oglindite prin conceptul de dependen funcional. Defini ie Dependen func ional = o restricie care apare ntre atributele unei entiti la nivelul semanticii (semnificaiei) acestora: fie a1 i a2 atributele unei entiti E; spunem c atributul a2 este dependent funcional de atributul a1 dac fiecrei valori a atributului a1 i corespunde o valoare i numai una a atributului a2 . Observa ie Observm 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 funcii n sensul matematic al cuvntului). Se pot afla n dependen funcional nu numai atribute individuale ci i grupuri de atribute. Vom ignora dependenele triviale, adic dependenele a1 a2 n care a2 depinde funcional de un subset al a1.

Notm dependena funcional a atributelor a1 i a2 prin a1 a2 i o reprezentm grafic ca n Figura 7.

Figura 17: Reprezentarea grafic a dependenei funcionale 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 reprezentri, n stnga sgeii). Observa ie Examinarea dependenelor funcionale dintre atributele unei entiti ne permite s determinm care dintre cheile candidate trebuie s fie aleas drept cheie primar: este

Figura 16: In tabela Clase s-au operat modificri n datele care descriu una dintre clase dar n tabela EleviClase au

rmas nc datele vechi despre respectiva clas

atributul a2 depinde

funcional de a1 a1 a2

22

aleas cheia candidat care apare ca determinant n toate dependenele funcionale identificate la nivelul entitii respective (a se vedea al doilea exemplu de mai sus.

D. Procesul de normalizare Normalizarea unei baze de date este un proces care se desfoar n mai muli

pai. Fiecare pas (cu excepia aducerii bazei de date la FN1) presupune: 1. identificarea dependenelor funcionale; 2. verificarea ndeplinirii unor anumite proprieti denumite generic forme normale.

Pe msur ce procesul de normalizare progreseaz, relaiile care compun baza de date (fie c acestea corespund unor entiti sau unor relaii dintre entiti) 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 relaional, singura form normal obligatorie pentru toate relaiile din baza de date este FN1; dac ns dorim s evitm toate anomaliile de actualizare (analizate mai sus) este necesar s continum procesul de normalizare cel puin pn la FN3. Observa ie Reamintim faptul c n modelul relaional, orice entitate i relaie dintre entiti este modelat matematic prin conceptul de relaie i reprezentat convenional printr-o tabel. In continuare, prin rela ie vom nelege modelul matematic al unei entiti sau al unei relaii ntre entiti, reprezentat convenional printr-o tabel, ca mai sus.

E. Prima form normal (FN1) Dup definirea structurii i a operaiilor cu baza de date, urmeaz introducerea

datelor (crearea instanelor entitilor i a relaiilor dintre ele). Acest lucru nseamn n principal transferarea datelor concrete din formularele de culegere a datelor n tabelele asociate entitilor / relaiilor. Dup parcurgerea acestui pas, se obin de obicei tabele nenormalizate , adic tabele n care unele celule conin mai multe valori ale aceluiai 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 entitile care apar intr-o astfel de baz de date enumerm: Angajai, Vehicule, Garaje, Clieni, Trasee. S presupunem c formularele de culegere date completate de eful fiecrui garaj conin (pe lng adresa garajului): tipul vehiculelor deinute (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 examinm celulele din coloana tipAuto (precum i din coloana Soferi)

constatm c tabela Garaje se afl n form nenormalizat . Ca urmare, tabela trebuie trecut n FN1. Defini ie Relaie aflat n prima forma normal (FN1) = o relaie cu proprietatea c oricare dintre celulele tabelei care o reprezint convenional conine o valoare i numai una (nu exist atribute cu valori multiple).

Se cunosc dou metode de a aduce o relaie n FN1; o vom prezenta pe cea mai eficient dintre ele (vom presupune c un singur atribut nu respect condiia din FN1): Aducerea unei rela ii la FN1 Fie E o entitate (aici: Garaje), a atributul su (aici: tipAuto) responsabil pentru forma nenormalizat a tabelei T corespunztoare entitii (aici: tabela Garaje11). Aducerea tabelei la FN1 necesit: (1.) eliminarea atributului a din entitatea E / a coloanei corespunztoare 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 entiti E' ; fie T' tabela care o reprezint (aici: pe baza atributului TipAuto crem entitatea Vehicule cu cheia primar NrInmatr i atributele: TipAuto, Marca, NrStele, NrLocuri, Culoare); (3.) stabilirea relaiei dintre cele dou entiti, relaie care este dup caz de tip 1-m sau n-m (aici: relaia dintre entitile Garaje i Vehicule este de tip 1-m). Prin urmare, cheia primar a entitii E trebuie s inclus cu rol de cheie extern printre atributele entitii 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: Relaiile dintre noile tabele Aten ie Intr-o relaie pot exista mai multe atribute cu valori multiple, deci care s fie fiecare la rndul su responsabile pentru forma nenormalizat a relaiei (tabelei). In acest caz, aducerea relaiei la FN1 se desfoare n tot atia pai cte astfel de atribute exist (aici: noua tabel Garaje22 este tot n form nenormalizat din cauza atributului Soferi). Prin urmare, vom proceda pentru acest atribut aa 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 jonciune Conduc prin care vom realiza relaia de tip nm dintre entitile 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 evideniat necesitatea inregistrrii unor informaii noi: numerele de nmatriculare ale autovehiculelor (la primul pas), datele personale ale oferilor (la al doilea pas). Pe de alt parte, n forma iniial, nenormalizat, tabela Garaje11 coninea o serie de informaii 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 entiti aprute prin normalizare se stabilesc relaiile corespunztoare (aici: la primul pas s-a stabilit o relaie 1-m ntre entitatea Garaje modificat i entitatea Vehicule nou creat, iar la pasul al doilea s-a stabilit o relaie nm ntre entitile nou create Soferi i Vehicule, deoarece n principiu un ofer poate conduce mai multe vehicule i un vehicul poate fi condus de mai muli oferi iar o restricie 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 jonciune 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 relaii rezultat dup incheierea operatiei de aducere la FN1 a entitii Garaje

F. A doua form normal (FN2) O relaie 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 cercetri care are mai multe filiale i n care salariaii sunt pltii n funcie de numrul de ore lucrate n cadrul unui proiect de cercetare sau al altuia. Dintre entitile care apar ntr-o astfel de baz de date enumerm: Filiale = {CodFil, NumeFil, LocFil}, Angajai = {CNP, CodFil, NumeAng, Adresa, SalariuPeOra}, Proiecte = {CodPrj, TitluPrj, CodFil, DataPredrii}. Pe lng acestea, am mai introdus n baza de date i entitatea AngajaiProiecte = {CNP, NumeAng, CodPrj, TitluPrj, NrOre, DataPredrii} cu instanele din Figura 16. S presupunem c data de predare a proiectului tr1 a fost devansat cu o lun; dac nu operm aceast modificare n ambele nregistrri din tabel care se refer la acest proiect atunci apare o anomalie de actualizare i baza de date ii pierde consistena.

28

Figura 26: Tabela AngajaiProiecte

Observa ie FN2 se refer numai la relaii a cror cheie primar este format din mai multe atribute deoarece se bazeaz pe conceptul de dependen funcional complet. Defini ie Dependen a func ional complet Fie a1 i a2 dou (mulimi de) atribute ale entitii E; spunem c a2 este complet dependent func ional de a1 dac i numai dac a2 este dependent funcional de a1 dar nu este dependent funcional de nicio submulime proprie a lui a1. Dac, dimpotriv, putem elimina un atribut din mulimea de atribute a1 iar a2 continu s fie dependent funcional de a1 atunci spunem c a2 este doar parial dependent func ional de a1. Contraexemplu S examinm relaia Vehicule din exemplul de mai sus: dependena funcional NrInmatr, TipAuto CodGaraj nu este complet: atributul CodGaraj este dependent funcional de un subset al {NrInmatr, TipAuto }, i anume de NrInmatr. Defini ie A doua form normal O relaie 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 funcional de cheia primar. Aducerea unei rela ii la FN2 Fie E o entitate aflat n FN1; aducerea ei la FN2 necesit: (1.) identificarea tuturor dependenelor funcionale dintre atributele entitii E; (2.) descompunerea relaiei E ntr-un numr de noi relaii astfel: o fiecare dependen funcional complet definete o nou relaie; o din fiecare dependen funcional parial se elimin acea parte a cheii

primare care este rspunztoare de incompletitudinea dependenei i apoi se definete noua relaie.

(3.) stabilirea relaiilor dintre noile entiti (n scopul recuperrii informaii de legatur, pierdute eventual prin inlocuirea entitii iniiale cu entitile normalizate). Aducerea unei rela ii la FN2

29

Ilustrm metoda de mai sus pentru entitatea AngajaiProiecte din Figura 17. La prima vedere, fiecare dintre atributele entitii: NumeAng, titluPrj, nrOre, dataPredrii depinde funcional de cheia primar a acesteia: CNP, codPrj. Aplicnd definiia dependenei funcionale complete observm c numai atributul nrOre depinde funcional complet de ambele atribute care formeaz cheia primar (a se vedea dependena funcional d.f.1 din Figura 17). Celelalte dependene sunt pariale; din ele obinem urmtoarele dependene complete: d.f.2 i d.f.3. Ca urmare, vom nlocui entitatea AngajaiProiecte cu entitile AP1, AP2, AP3 (Figura 18) i vom stabili relaiile dintre ele. d.f.2 d.f.1

CNP codPrj nrOre NumeAng titluPrj dataPredrii

d.f.3 Figura 27: Dependenele funcionale complete din entitatea AngajaiProiecte AP1 AP2

AP3

codPrj titluPrj dataPredrii

Figura 27: Normalizarea entitii AngajaiProiecte

Figura 28: Relaiile dintre noile tabele aflate n FN2

CNP NumeAng CNP codPrj nrOre

30

G. A treia form normal (FN3) O relaie 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 cercetri descris n paragraful anterior. S presupunem c am mai introdus n baza de date i entitatea AngajaiFiliale = {CNP, NumeAng, Adresa, Oras, CodFil, NumeFil, LocFil} cu instanele din Figura 20. In cazul n care una dintre filiale ii schimb sediul (de exemplu filiala CercAero se mut de la Hui la Iai), operarea modificrii numai n una dintre cele dou nregistrri care se refer la filiala respectiv determin apariia unei anomalii de actualizare i baza de date ii pierde consistena.

Figura 29: Tabela AngajaiFiliale

Observa ie FN3 se bazeaz pe conceptul de dependene funcionale tranzitive. Defini ie Dependen e func ioanle tranzitive Fie a1 , a2 i a3 trei atribute ale unei entii E cu proprietatea c: (1) a1 a2 i a2 a3 (2) a1 nu depinde funcional nici de a2 nici de a3 Atunci: a1 a3 ( a3 depinde func ional de a1 via a2 ).

Exemplu S examinm relaia 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 urmtoarele dependene funcionale: CNP CodCl i CodCl

Loc; atunci avem i CNP Loc via atributul CodCl deoarece atributul CNP nu depinde funcional nici de CodCl nici de Loc.

31

Defini ie A treia form normal O relaie 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 funcional de cheia primar. Aducerea unei rela ii la FN3 Fie E o entitate (aici: AngajaiFiliale) 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 dependenelor tranzitive dintre atributele entitii E; (2.) descompunerea relaiei E ntr-un numr de noi relaii astfel: o atributul a1 impreun cu toate atributele care depind funcional de el (deci

inclusiv a2 ) formeaz o nou relaie (aici: atributele CNP, NumeAng, Adresa, Oras, codFil formeaz entitatea Ang1);

o atributul a2 impreun cu atributul a3 i cu alte atribute care depind funcional de a1 prin tranzitivitate formeaz o nou relaie (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 entiti nou create; (4.) stabilirea relaiilor dintre noile entiti (n scopul recuperrii informaiilor de legatur, pierdute eventual prin inlocuirea entitii iniiale cu entiti normalizate).

CNP NumeAng Adresa Oras CodFil NumeFil LocFil

Figura 30: Dependenele funcionale tranzitive din entitatea AngajaiFiliale

CNP NumeAng Adresa Oras CodFil

Figura 31: Normalizarea entitii AngajaiFiliale

Observa ie Observm c atributul care asigur tranzitivitatea (atributul notat a2) nu este nici cheie primar n relaia respectiv nici mcar parte a cheii primare. Tocmai din acest motiv, dependena a2 a3 nu este dezirabil la nivelul relaiei respective.

CodFil NumeFil LocFil

32

Condi ie de verificat Solu ie (normalizare)

FN1 Toate atributele relaiei trebuie s fie atomice

Fiecare atribut neatomic se transform intr-o nou relaie

FN2

Relaia este n FN1; Cheia sa primar const din mai multe atribute; Toate atributele care nu fac parte din cheia primar sunt complet dependente funcional de cheia primar

Fiecare parte a cheii primare, mpreun cu atributele care depind funcional complet de ea formeaz o nou relaie; Se stabilesc relaiile necesare ntre noile relaii care au nlocuit-o pe cea iniial

FN3

Relaia este n FN2; Nici un atribut care nu face parte dintr-o cheie candidat nu este funcional 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 funcional dependent de cheia primar prin tranzitivitate)

Se pstreaz n relaia iniial numai cheia primar i atributele care depind funcional de ea direct (inclusiv atributul "incriminat"); Se creeaz cte o nou relaie din fiecare atribut care nu face parte din cheia primar mpreun cu toate atributele (care nu fac nici ele parte din cheia primar a relaiei iniiale) care sunt dependente funcional de acesta; Se stabilesc relaiile necesare ntre noile relaii i relaia iniial 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: entiti, atribute, identificatori unici, relaii ntre entiti.

Acest model poate fi utilizat pentru determinarea modelului fizic al oricrui 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 reine date de un anumit tip i corespunde unui atribut al entitii; numele atributului devine antetul unei coloane din tabel; un rnd din tabel corespunde unui element al entitii (instan a entitii) i se numete nregistrare. Aceasta va descrie complet proprietile unei instane; cheie primar este reprezentat de o coloan sau o combinaie de coloane ale cror valori sunt unice la nivelul tabelei i sunt completate obligatoriu. Cheile primare provin din identificatorii unici ai entitii.

Crearea unei tabele se realizeaz n dou etape: 1. n prima etap se stabilete structura tabelei, specificndu-se numele cmpurilor,

lungimile acestora, precum i tipul informaiilor care vor fi introduse n fiecare cmp.

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

cmpurile tabelei, ncheind astfel operaia de creare (ulterior, tabela va putea fi folosit n operaii de modificare a structurii sau de actualizare a datelor). Pentru aceasta: Pas 1. se afieaz tabela n modul Datasheet View Pas 2. se introduc direct datele n cmpurile corespunztoare (se plaseaz cursorul n celul, se tasteaz valoarea i se acioneaz tasta ENTER). Se folosete tasta TAB pentru a trece dintr-o celul n alta. Se folosesc tastele cu sgei pentru a parcurge tabela pe orizontal i pe vertical.

B. Exemplificare n limbajul de programare FOXPRO

A) Introducerea datelor Adugarea articolelor se face la sfritul tabelei active. Adugarea unui articol vid se realizeaz cu ajutorul comenzii APPEND BLANK. Un cmp vid are una din valorile: zero pentru cmpul numeric, spaiu pentru cmpul caracter, .F. (fals) pentru cmpurile logice, valoarea {/ /} pentru dat calendaristic. Structura tabelei se creeaz prin comanda (de exemplu:

tabela Elevi): create table elevi (numr_matricol n(5), nume c(5),

prenume c(13), data_naterii d, clasa c(2) ) Se adaug nregistrrile folosind o structur de control repetitiv cu numr cunoscut de pai (for), comenzi de afiare pe ecran de elemente de control ncepnd de la o linie i o coloan specificat (de tipul l, c say [introducei... ]) i comanda de tipul read (ce realizeaz adugarea efectiv a datelor citite n nregistrarea vid). for i=1 to 8 do append blank @4,4 say [numr matricol:] get numr_matricol @5,4 say [nume:] get nume @6,4 say [prenume:] get prenume @7,4 say [data naterii:] get data_naterii @8,4 say [clasa:] get clasa read end for Introducerea datelor se poate realiza vizual folosind oricare din editoarele Browse sau Edit, utiliznd opiunile meniului View. Salvarea informaiilor se poate realiza folosind

34

combinaia de taste Ctrl + W sau executnd clic pe butonul de nchidere al ferestrei de editare. Pentru adugarea vizuala de nregistrri vide la sfritul unei tabele se poate utiliza opiunea Append New Record din meniul Table sau opiunea 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 executrii comenzii: Forma general a comenzii REPLACE este: REPLACE WITH [, WITH ] [domeniu] [FOR ] [WHILE ] Pentru actualizarea i vizualizarea datelor poate fi utilizat i comanda BROWSE care va afia tabela activ pe linii i pe coloane (pe prima linie sunt afiate denumirile cmpurilor din structura tabelei, iar n continuare sunt afiate liniile cu date).

C. Exemplificare n limbajul de programare ORACLE

A) Introducerea datelor Comanda INSERT este utilizat pentru introducerea unei noi nregistrri ntr-o

tabel. Sintaxa general a acestei comenzi este: INSERT INTO [(nume_coloan1, [nume_coloan2, ... ])] VALUES (expresie1, expresie2, ... ) Pentru adugarea unui nou articol poate fi utilizat metoda explicit (cnd sunt

specificate explicit cmpurile ce vor fi completate cu valorile din clauza VALUES) sau metoda implicit (cnd nu se specific niciun cmp dar se cunoate structura tabelei i cmpurile sunt completate cu valorile corespunztoare din clauza VALUES).

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

UPDATE care are urmtoarea sintax general: UPDATE SET nume_coloan1 = expresie1, nume_coloan2 = expresie2, ... ... nume_coloann = expresien [WHERE condiie]

Vom reveni asupra codului SQL.

2. INTEROGAREA BAZELOR DE DATE

2.1. Generalit i Pentru utilizator, o interogare este o metod de a regsi anumite informaii

dintr-o baz de date, prin intermediul unei aplicaii de baze de date. Din punctul de vedere al programatorului aplicaiei 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 ctre SGBD n mai multe faze: analiza lexical, analiza sintactic i analiza semantic, pentru validarea interogrii, urmate de generarea codului. De asemenea, dac exist mai multe soluii pentru aceeai interogare, sistemul de gestiune selecteaz soluia optim.

Conceptual, subsistemul SGBD de prelucrare a interog rilor const din urmtoarele componente: Compilatorul de interogri, care efectueaz analiza lexical i sintactic a interogrii; acesta valideaz din punct de vedere sintactic interogarea, adic verific existena relaiilor, a vederilor, a indexurilor i a atributelor implicate n interogare i utilizarea corect a acestora. Optimizatorul de interogri, care efectueaz analiza semantic a interogrii i selecteaz alternativa optim dintre mai multe soluii posibile de execuie a interogrii. Generatorul de cod, care genereaz programul de execuie al interogrii, conform optimizrilor efectuate. Componenta de execuie (runtime), care execut programul interogrii.

Compilarea interogrii se realizeaz la fel ca orice compilare a programelor, fr aspecte specifice sistemelor de baze de date. Optimizarea interogrilor este o operaie specific sistemelor de gestiune i utilizeaz proprietile operaiilor relaionale pentru a obine performane de execuie a interogrilor ct mai bune. Optimizarea este efectuat de ctre SGBD, transparent, fr intervenia programatorului.

Interogarea (query) este operaia prin care se obin datele dorite dintr-o baz de date, selectate conform unui anumit criteriu (condiie). Dat fiind c operaia de interogare este cea mai important operaie de manevrare a datelor, de multe ori limbajele de manevrare a datelor sunt denumite limbaje de interogare.

Pentru formularea conceptual a interogrilor n bazele de date relaionale s-au dezvoltat dou limbaje abstracte de interogare: algebra relaional i calculul relaional. Algebra relaional (relational algebra) const dintr-o mulime de operaii care au ca operanzi relaii, iar rezultatul este tot o relaie. Calculul relaional (relational calculus) este bazat pe calculul predicatelor i exprim o interogare formulnd o definiie a rezultatului dorit (de regul, o relaie) printr-o expresie de calcul relaional.

Limbajele de interogare reale implementate n sistemele de baze de date relaionale sunt limbaje definite pe baza unuia sau altuia din limbajele de interogare abstracte, sau pe o combinaie a acestora. Astfel: Limbajul SQL este n cea mai mare parte bazat pe algebra relaional, dar mai conine i construcii derivate din calculul relaional. Limbajul ISBL (Information System Base Language) al firmei IBM este bazat n ntregime pe algebra relaional. Limbajul QUEL al SGBD Ingres este bazat pe calculul relaional al tuplurilor. Limbajul QBE (Query by Example), dezvoltat la firma IBM este bazat pe calculul relaional al domeniilor.

Un limbaj de interogare real este denumit relaional complet dac implementeaz toate operaiile prevzute de unul din limbajele de interogare abstracte. n general, toate limbajele relaionale 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

relaionale mai mult dect complete, coninnd i operaii care nu sunt prevzute n limbajele relaionale abstracte, ca de exemplu, efectuarea unor calcule aritmetice asupra valorilor unor atribute (sum, medie, minim, maxim), funcii de tiprire a relaiilor, etc.

Limbajul SQL este limbajul cel mai utilizat n sistemele relaionale.

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, Owners, 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 campulrezultat 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 ta