Structuri de Date In Oracle8

STRUCTURI DE DATE IN ORACLE8

Cap. 1 Structuri de date relationale, notiuni introductive

Principiile modelului relational au fost pentru prima data expuse de Dr. E. F. Codd, care in iunie 1970 a publicat un articol numit ’ Un model relational de date pentru marile banci de date ’. In acest articol Dr. Codd a propus modelul ’ relational ’ pentru sistemele de baze de date.

Baza de date relationala este perceputa de utilizatorii sai ca o colectie de tabele bidimensionale care sunt usor de inteles. Sunt doar patru concepte de inteles: tabele, coloane, randuri si campuri.

Modelul relational imita procesele unei ramuri a algebrei cunoscuta sub numele de Algebra Relationala. Aceste procese implica: o colectie de obiecte cunoscute sub numele de relatii si o multime de operatori ce actioneaza asupra relatiilor pentru a produce noi relatii. Intuitiv o relatie este un tabel sau intr-o anumita masura un fisier. Totusi exista o deosebire importanta intre relatie si fisier. Cand o relatie este asimilata cu un tabel de valori, fiecare linie din tabel este o multime de date asociate care pot fi interpretate ca fapte ce descriu o entitate sau o instanta a unei legaturi. Numele tabelului ai numele coloanelor ne sunt de ajutor in interpretarea semnificatiilor valorilor in fiecare linie a tabelului. Toate valorile dintr-o coloana sunt date de acelasi tip. In terminologia bazelor relationale de date, tabelul se numeste relatie, linia se numeste tuplu, si numele coloanei se numeste atribut.

Elementele unei baze de date relationale in Oracle, se refera la obiectele, la tipurile de date si la fisierele admise, si ele sunt:

– Obiectele sunt independente de alocarea de spatiu fizic pe disc pentru date si dau structura logica a bazei de date.

Baza de date – colectia de date cu o caracteristica si un scop comun, formata din mai multe tabele. Pe un server pot exista la un moment dat mai multe baze de date, dar numai una activa(altfel ar fi necesara metabaza de date);

Tabela – unitatea de baza pentru memorarea datelor intr-o baza de date Oracle. Asigura memorarea datelor dupa modelul relational, intr-un ansamblu sub forma de randuri(inregistrari/tupluri) si coloane (campuri/atribute). Tabela sau relatia este un ansamblu format din n coloane si m randuri care respecta urmatoarele conditii minime: sa nu existe date la nivel agregat(valorile aflate la intersectia liniilor cu coloanele sa fie la nivel elementar), linile sa fie distincte unele fata de altele, sa nu existe coloane repetitive in descriere. Tabelele pot fi intr-una dintre cele cinci forme normale: FN1, FN2, FN3, FN4, FN5, date de tehnica normalizarii;

Viziunea – contine date extrase din una sau mai multe tabele dupa anumite criterii(cerinte, drepturi de acces etc), fara a ocupa spatiu fizic. Viziunea se foloseste pentru a adauga alte nivele de securitate a datelor, pentru a simplifica accesul la baza de date, pentru a oferi perspective diferite asupra bazei de date, pentru a salva rezultatul unor cereri de regasire complexe;

Clusterul – ofera o metoda optionala de memorare a datelor. El creste eficienta instructiunilor SQL de regasire, dar scade eficienta lor pentru actualizare;

Index – e structura de date asociata unei tabele sau unui cluster. Se poate indexa dupa una sau mai multe coloane ale unei tabele, care se numesc chei, ce pot fi unice sau multiple, ele pot fi primare sau externe;

Blocul de date – este egal sau multiplu al blocului fizic de pe disc;

Formatul randului – un rand dintr-o tabela poate ocupa unul sau mai multe blocuri de date Oracle

– Tipuri de date folosite de Oracle pentru descrieirea datelor in memoria interna sau externa.

CHAR si VARCHAR – pentru date sir de caractere de lungime fixa respectiv variabila;

NUMBER – pentru date numerice intregi(VF) sau reale(VM);

DATE – pentru data calendaristica;

LONG – pentru date de lungime variabila;

RAW si LONGRAW – pentru date sir caractere de lungime fixa, respectiv variabila;

ROWID – e un pseudotip de date ce permite furnizarea adresei fizice a unui rand dintr-o tabela.

– Fisierele aflate pe disc sunt utilizate pentru memorarea datelor si dau structura fizica a bazei de date.

Fisiere cu date propriu-zise(tip database) – o baza de date poate avea unul sau mai multe astfel de fisiere. Unul sau mai multe fisiere pot alcatui o unitate logica de memorare a bazei de date numita tabela spatiu;

Fisiere de control – contin informatii despre baza de date si descriu structura acesteia. Sunt fisiere binare de mici dimensiuni. O baza de date are unul sau mai multe fisiere de control, dar un fisier de control se poate asocia numai unei singure baze de date;

Fisiere jurnal(redolog) – se inregistreaza in ele, automat de catre sistem, toate modificarile facute asupra bazei de date la o sesiune curenta, pentru a se putea reface in caz de avarii.

Modificarea datelor este realizata prin operatiile relationale aplicate asupra tabelelor. Vom defini in continuare operatorii relationali prin relatie si descriere:

Restrictia – este o operatie care preia si afiseaza datele din relatie. Este posibil sa se afiseze toate randurile sau doar randurile care indeplinesc o conditie sau mai multe conditii. Aceasta este de multe ori numita ’submultime orizontala’;

Proiectia – este operatia care afiseaza anumite coloane din relatie si de aceea este numita ’submultime verticala’

Produs – este rezultatul obtinut cand randurile a doua miltimi de date sunt concatenate conform conditiilor specificate;

Join – este rezultatul obtinut cand randurile a doua multimi de date sunt concatenate conform conditiilor specificate;

Reuniunea – afiseaza toate randurile care apar in una, in cealalta sau in ambele din cele doua relatii;

Intersectia – afiseaza toate randurile care apar in ambele din cele doua relatii;

Diferenta – afiseaza randurile care apar numai intr-o singura relatie(SQL utilizeaza operatorul minus).

Vom prezenta in continuare cateva proprietati ale unei baze de date relationala:

1 O baza de date relationala apare ca o colectie de relatii(tabele), catre utilizator;

2 Formatul coloanei/randului este familiar si usor pentru vizualizarea datelor;

3 Exista o multime de operatori pentru partitionarea si combinarea relatiilor(selectia, proiectia, produsul, joinul, uniunea, intersectia, diferenta);

4 Nu sunt pointeri expliciti; conexiunile sunt facute numai pe baza datelor;

5 Limbajul utilizat pentru interogarea bazei de date este non-procedural si similar limbii engleze;

6 Utilizatorul nu specifica calea de acces si nu are nevoie sa stie cum este informatia aranjata fizic;

7 Comenzile pentru refacerea datelor si acelea pentru realizarea schimbarilor in baza de date sun incluse intr-un singur limbaj SQL;

8 Exista o independenta totala a datelor

Cap2. Introducere in Oracle8

Oracle este cel mai raspandit Sistem de Gestiune a Bazelor de Date Relationale ( Relational Database Management System – RDBMS ) din lume. Profesionistii din domeniul Tehnologiei Informatiei ( IT ) considera ca produsul Oracle satisface cerintele de baze de date din domeniile industrial, financiar, farmaceutic si ale telecomunicatiilor din intreaga lume. Bazele de date Oracle sunt instalate pe toate tipurile importante de calculatoare si ruleaza sub principalele sisteme de operare existente. Exista versiuni de Oracle pentru platforme de la calculatoarele portabile modeste pana la serverele puternice de baze de date la care sunt conectati simultan mii de utilizatori.

Bazele de date Oracle functioneaza ca motoare de procesare a tranzactiilor on-line, sisteme de suport al deciziilor, banci de date si depozitare pentru aplicatiile web.

Oracle consta dintr-un set complet de constructori de aplicatii si produse pentru utilizatori, cautand sa asigure solutii complete in tehnologia informatiei. Aplicatiile Oracle sunt portabile peste un numar mare de statii de lucru si sisteme de operare, de la calculatoarele personale la procesoare paralele. Aplicatiile Oracle trebuie rulate pe acelasi computer la fel ca si Serverul Oracle.

Alternativ, aplicatiile si utilitarele utilizate de ele pot sa fie rulate pe un sistem local pentru utilizator(sistemul client), in timp ce Oracle DBMS ruleaza pe un altul(sistemul server). In acest mediu client-server, un numar mare de resurse de calcul pot fi rulate. De exemplu, o aplictie Oracle Forms poate rula pe un calculator personal client, in timp ce accesarea datelor este condusa conventional de un Server Oracle pe un calculator central.

Printre multele dezvoltari de aplicatii si produse pentru utilizatori accesibile in familia Oracle, exista o posibilitate comuna pentru a accesa baza de date, aceasta este realizata prin Limbajul cu Structura de Interogare(SQL)

Vom vorbi in continuare despre produsele Oracle:

Pro*Oracle – o serie de precompilatoare care permit, de exemplu, accesul bazelor de date Oracle la limbajele de programare C, Cobol, PL/1;

Oracle Card – o interfata utilizator pentru producerea aplicatiilor cu baze de date usor de folosit ce incorporeaza grafice si facilitati multimedia;

Oracle TextRetrieval – o tehnologie care adauga capabilitati de refacere completa a textului intr-o baza de date Oracle;

Oracle Case – o familie de instrumente care ajuta la analiza, designul si generarea aplicatiilor Oracle;

SQL*Plus – un instrument care permite o utilizare directa si interactiva a limbajului SQL pentru a accesa serverul Oracle, utilizand comenzi ad-hoc sau prin rularea fisierelor de comanda.

Instrumentul de dezvoltare a aplicatiilor principale formeaza un set inchis integrat numit CDE(Mediul de dezvoltare auxiliar)

Oracle Forms – permite ca aplicatiile sofisticate bazate pe utilizarea ecranului sa fie construite repede si usor si permite utilizatorului sa ceara si sa manipuleze date dintr-o structura cu forma convenabila, la fel de bine ca afisarea imaginilor vizuale, si facilitati de accesare prevazute de alte produse CDE.

Oracle Reports – un puternic instrument de scriere a rapoartelor pentru constructia si executia rapoartelor sofisticate cu fonturi de text si imagini multiple.

Oracle Graphics – permite construirea si afisarea aplicatiilor vizuale, incluzand harti, grafice imagini si desene.

Oracle Book – prevede abilitatea de a crea si vizualiza documente care includ hipertexte, sunete digitale si videoclipuri.

Pentru a putea trece mai departe trebuie sa facem o scurta introducere si asupra diversitatii comenzilor SQL*Plus( precum SELECT ) pentru ca aceste vor fi folosite in continoare la exemple. Comenzile SQL sunt mijloace de acces la date prin kernelul Oracle. Aceste sunt utilizate in special pentru controlul mediului, formatarea rezultatelor interogarilor si controlul fisierelor.

Comenzile SQL sunt introduse la promptul SQL> pe o linie, ele nu vor deschide un buffer.

Forta produsului Oracle sta in promisiunea de depasire a dificultatilor generate de noua tehnologie. O astfel de tehnologie este orientarea pe obiect. Bazele de date orientate pe obiect nu sunt cu siguranta o noutate; totusi, ele nu au fost acceptate pe scara larga. Oracle8, urmatoarea versiune importanta a produsului Oracle, promite un suport transparent pentru aplicatiile vechi si extensii orientate pe obiect.

Cap. 3 Tabele

3.1 Crearea unei tabele

Tabelele sunt obiecte fundamentale in care se stocheaza informatiile unei baze de date relationale. Fara tabele, aplicatiile nu ar mai avea nevoie de produsul Oracle sau de orice alta baza de date relationala. Tabelele sunt alcatuite din una sau mai multe coloane care definesc tipul celor ce pot fi stocate, adica daca datele sunt un sir de caractere, un numar, o data, un obiect binar sau un fisier extern.

Tabelele pot fi corelate intre ele, conectate prin intermediul coloanelor comune, ceea ce defineste produsul Oracle ca baza de date relationala. In acest capitol vor fi prezentate crearea si manipularea tabelelor in Oracle. Probabil majoritatea instructiunilor DDL(Data Definition Language – Limbaj de definire a datelor ) utilizate pentrxecutia rapoartelor sofisticate cu fonturi de text si imagini multiple.

Oracle Graphics – permite construirea si afisarea aplicatiilor vizuale, incluzand harti, grafice imagini si desene.

Oracle Book – prevede abilitatea de a crea si vizualiza documente care includ hipertexte, sunete digitale si videoclipuri.

Pentru a putea trece mai departe trebuie sa facem o scurta introducere si asupra diversitatii comenzilor SQL*Plus( precum SELECT ) pentru ca aceste vor fi folosite in continoare la exemple. Comenzile SQL sunt mijloace de acces la date prin kernelul Oracle. Aceste sunt utilizate in special pentru controlul mediului, formatarea rezultatelor interogarilor si controlul fisierelor.

Comenzile SQL sunt introduse la promptul SQL> pe o linie, ele nu vor deschide un buffer.

Forta produsului Oracle sta in promisiunea de depasire a dificultatilor generate de noua tehnologie. O astfel de tehnologie este orientarea pe obiect. Bazele de date orientate pe obiect nu sunt cu siguranta o noutate; totusi, ele nu au fost acceptate pe scara larga. Oracle8, urmatoarea versiune importanta a produsului Oracle, promite un suport transparent pentru aplicatiile vechi si extensii orientate pe obiect.

Cap. 3 Tabele

3.1 Crearea unei tabele

Tabelele sunt obiecte fundamentale in care se stocheaza informatiile unei baze de date relationale. Fara tabele, aplicatiile nu ar mai avea nevoie de produsul Oracle sau de orice alta baza de date relationala. Tabelele sunt alcatuite din una sau mai multe coloane care definesc tipul celor ce pot fi stocate, adica daca datele sunt un sir de caractere, un numar, o data, un obiect binar sau un fisier extern.

Tabelele pot fi corelate intre ele, conectate prin intermediul coloanelor comune, ceea ce defineste produsul Oracle ca baza de date relationala. In acest capitol vor fi prezentate crearea si manipularea tabelelor in Oracle. Probabil majoritatea instructiunilor DDL(Data Definition Language – Limbaj de definire a datelor ) utilizate pentru producerea tabelelor bazei de date din organizatia dumneavoastra sunt intretinute si executate de administratorul bazei de date. Metodele utilizate pentru proiectarea si construirea tabelelor sunt la fel de importante atat pentru cei care dezvolta aplicatii, cat si pentru administratorii de baze de date.

Tabelele de date relationale sunt menite sa ofere metode simple si flexibile pentru stabilirea de legaturi intre informatiile stocate in tabele separate. Acest model ne permite sa-i punem bazei de date un numar nelimitat de intrebari, atata timp cat avem grija sa formulam aceste intrebari in termeni pe care baza de date ii poate intelege(cu ajutorul limbajului de interogare SQL)

Elementele cele mai importante ale unei baze de date sunt tabelele. De obicei, toate datele utilizate de aplicatii si de conturile utilizator sunt stocate in tabele. In acest paragraf sunt explicate etapele si sintaxa utilizate in crearea unei tabele obisnuite. Tabelele sunt element efundamentale ale oricarei baze de date. Ele contin coloane ce definesc modul in care sunt formate si pastrate datele.

Comanda CREATE TABLE este utilizata pentru a crea tabele. Exista numeroase optiuni pentru aceasta comanda, dar in acest paragraf ne vom axa pe crearea unei tabele obisnuite, de-sine-statatoare, urmand apoi sa descriem si cateva optiuni suplimentare pentru comanda CREATE TABLE.

Sintaxa de baza pentru crearea unei tabele este prezentata in Figura 1.1F, iar pentru a vizualiza descrierea tabelei create se va introduce comanda DESC NUME_TABEL{, unde NUME_TABEL{ este numele tabelei create.

Figura 3.1.1 Sintaxa de baza pentru crearea unei tabele.

Figura 3.1.2 Sscript-ul EX_312.sql este utilizat pentru crearea unei tabele de test.

Initial se aloca 1MB de spatiu pentru aceasta tabela, dupa cum este specificat in clauza de stocare INITIAL. Daca spatiul initial precizat de 1MB se umple cu date, Oracle va crea o extindere suplimentara de 500KB asa dupa cum este specificat in clauza de stocare NEXT. Daca si acest spatiu suplimentar se umple cu date, va fi creata o alta extindere, cu 50% mai mare, dupa cum este specificat in optiunea PCTINCREASE. In acest caz vor fi alocati 500KB * 1,5 = 750KB.

In cadrul fiecarui bloc, a carui dimensiune este determinata la crearea bazei de date, Oracle va adauga o inregistrare in alt bloc atunci cand blocul initial se umple in proportie de peste 10%. Acest lucru este determinat prin optiunea PCTFREE. De asemenea, Oracle va semnala ca exista spatiu liberin interiorul blocului pentru adaugarea de inregistrari cand blocul este mai putin de 90% umplut. Acest lucru este realizat prin optiunea PCTUSED.

Un lucru foarte important este acela de a cunoaste tehnicile de modelare a datelor pentru a putea determina ce vor contine coloanele tabelelor.

Alte optiuni avansate pentru crearea tabelelor includ: crearea tabelelor organizate pe baza de indecsi, crearea restrictiilor, optiuni pentru tabele imbricate, crearea clusterelor, optiunea de interogare paralela si LOB-uri( Large Object ) externe.

3.2 Determinarea tuturor tabelelor care apartin unui anumit utilizator

Toate tabelele disponibile pentru un cont utilizator pot fi aflate prin intermediul dictionarului de date. Vederea USER_TABLES, din dictionarul de date, contine toate tabelele detinute de contul utilizator conectat la baza de date. Vederea ALL_TABLES contine toate tabelele pentru care contul utilizator are privilegii. Interogand acesta vedere pentru un anumit proprietar, puteti determina ce tabele detine. In figura 3.2.1 sunt prezentate majoritatea coloanelor din vederea ALL_TABLES din dictionarul de date.

Figura 3.2.1 O portiune din vederea ALL_TABLES

3.3 Introducerea de comentarii despre o tabela si despre coloanele sale

Datorita numarului foarte mare de tabele folosite in aplicatii, un lucru foarte important este sa documentam tabelele si coloanele lor din baza de date. Instructiunea COMMENT este folosita in crearea comentariilor pentru tabele si coloane. In figura 1.2F este prezentata sintaxa instructiunii COMMENT. Comentariile sunt stocate in dictionarul de date si pot fi interogate din vederile ALL_TAB_COMMENTS si ALL_COL_COMMENTS din dictionarul de date. Daca contul utilizator conectat la baza de date nu este proprietarul unei tabele, trebuie sa aiba privilegiul de sistem COMMENT ANY TABLE pentru a putea introduce comentarii despre tabela.

Figura 3.3.1 Sintaxa comenzii COMMENT.

Figura 3.3.2 Script-ul EX_332.sql creaza o tabela de test pentru acest subcapitol.

Figura 3.3.3 Fisier ce creeaza tabela din figura 3.3.2

Figura 3.3.4 Fisier ce introduce comentarii despre tabela DEPT creata in figura 3.3.3

Figura 3.3.5 Script-ul ce contine o interogare pentru vizualizarea comentariului creat in etapa anterioara( figura 3.3.4 ).

Comentariile asociate tabelelor si coloanelor simplifica mentinerea bazelor de date si a aplicatiilor. Acestea pot fi utilizate in cadrul aplicatiilor prin interogarea vederilor din dictionarul de date.

3.4 Reconstruirea instructiunilor CREATE TABLE

Vederea USER_TABLES din dictionarul de date contine toate tabelele aflate in proprietatea unui cont utilizator. Vederea USER_TAB_COLUMNS din dictionarul de date contine fiecare dintre coloanele din tabela. Daca instructiunea CREATE TABLE nu necesita informatii despre spatiile-tabela si spatiul de stocare, sunt suficiente coloanele din vederea USER_TAB_COLUMNS. Aceste coloane sunt prezentate in figura 3.4.1.

Coloana TABLE_NAME contine numele tabelei careia ii apartin coloanele. Coloana COLUMN_NAME contine numele coloanei asa cum a fost definit atunci cand a fost creata tabela. Coloana DATA_TYPE contine tipul datelor din coloana.

Figura 3.4.1 Datele din vederea USER_TAB_COLUMNS din dictionarul de date.

Figura 3.4.2 Contine un script pentru a re-crea instructiunea CREATE TABLE.

3.5 Determinarea parametrilori de stocare initiali ai unei tabele

Prin utilizarea formulelor prezentate in continuare vom putea calcula valorile INITIAL, NEXT, PCTFREE, si PCTUSED inainte de crearea tabelei. Aceste valori pot fi utilizate in comanda CREATE TABLE pentru a crea o tabela cu parametrii dimensionati corect.

Figura 3.5.1 Formula generala pentru determinarea spatiului liber dintr-un bloc pentru inregistrari Oracle.

Determinarea marimii unei tabele depinde de o seama de factori. Ideal ar fi ca intreaga tabela sa incapa intr-o singura extindere( extent ). O extindere este o colectie alcatuita din blocuri ale bazei de date care contine parti dintr-o tabela intreaga. Cand se creaza o tabela, se defineste dimensiunea pentru extinderea INITIAL. Cand tabela se completeaza cu date, inregistrarile sunt create in extinderea INITIAL. Daca aceasta extindere se umple cu date, serverul Oracle aloca o alta extindere a carei dimensiune este stabilita prin clauza de stocare NEXT. Oracle insereaza inregistrarile intr-un bloc pana cand spatiul disponibil este epuizat. Spatiul liber este determinat prin scaderea din valoarea dimensiunii blocului de date a marimii spatiului antet rezervat informatiilor despre tabele si prin inmultirea rezultatului obtinut cu valoarea procentuala a parametrului PCTFREE( figura 3.5.1 ). Parametrul PCTUSED determina cat spatiu trebuie eliberat intr-un bloc prin stergerea de inregistrari inainte ca Oracle sa insereze date in el. El este utilizat pentru a determina care blocuri au spatiu liber pentru inserare.

Figura 3.5.2 Formula pentru determinarea numarului de inregistrari care incap intr-un bloc.

Figura 3.5.3 Formula pentru determinarea parametrului de stocare INITIAL.

3.6 Crearea unei tabele organizata pe baza unui index

Pentru, a se marii performantele de accesare a unei tabele care contine o cheie primara, economisirea spatiului de stocare se va crea o tabela organizata pe baza unui index.

O tabela organizata pe baza unui index este creata prin utilizarea clauzei ORANIZATION INDEX in comanda CREATE TABLE. Sintaxa de baza utilizata apare in figura 3.6.1 Toate tabelele organizate pe baza de indecsi sunt stocate in structura de arbore-B (binar) dupa cheia primara. Aceasta structura este utilizata in Oracle pentru indecsi.

Figura 3.6.1 Sintaxa de baza pentru crearea unei tabele organizate pe baza de index.

Diferenta este ca toate coloanele ce nu sunt chei sunt stocate in structura de arbore-B. Astfel performantele interogarilor si modificarilor unei tabele bazate pe cheia primara cresc prin utilizarea acestui format de obiect. Parcurgerea unei intregi tabele va returna inregistrarile in ordinea cheii primare, la fel ca in cazul tabelelor organizate in clustere.

Fiecare tabela organizata pe baza unui index trebuie sa contina o cheie primara. Cheia primara defineste unic o inregistrare; pseudo-coloanele ROWID nu sunt utilizate. Clauza PCTTHRESHOLD, daca este utilizata, trebuie sa aiba valori intre 0 si 50.

Tabelele organizate pe baza de index nu trebuie sa contina coloane cu date de tipul LONG. Nu trebuie sa aiba restrictii (cu exceptia celor pentru cheia primara) si nici indecsi suplimentari. Ele nu pot fi organizate in clustere, replicate, partitionate sau distribuite.

Cap. 4 Indecsi

Definirea indecsilor

Indecsii sunt obiecte importante utilizate pentru asigurarea integritatii datelor si a cresterii performantelor. Un index functioneaza ca un ghid pentru localizarea directa a unei inregistrari, la fel cum cineva cauta un anumit subiect in indexul unei carti. Plasati strategic indecsi pot marii simtitor performantele interogarilor, scazand durata de executie a acestora de la ordinul orelor pana la cateva secunde. De asemenea, indecsi sunt utili in impunerea unor reguli in cadrul bazei de date, in special de unicitate intr-o coloana sau grup de coloane. Un index este separat de datele pe care se bazeaza de aceea el poate fi sters sau modificat fara a afecta datele. O data creati, Oracle actualizeaza toti indecsi importanti in timpul manipularii datelor. Exista mai multe tipuri de indecsi, cum ar fi indecsi bitmap, indecsi obisnuiti, indecsii partitionati si tabele organizate pe baza de indecsi, fiecare cu propriile avantaje si dezavantaje. In acest capitol vor fi prezentate crearea si utilizarea indecsilor in Oracle si sunt descrise avantajele si dezavantajele utilizarii pentru fiecare in parte.

4.2 Determinarea tuturor indecsilor pentru o anumita tabela

Vederea DBA_INDEX din dictionarul de date contine informatii despre fiecare index din baza de date. Interogand aceasta vedere pute-ti determina ce indecsi exista si pentru ce tabela. in acest paragraf vor fi crati o tabela de test si indecsi.

Figura 4.2.1 Script ce creeaza tabela de test utilizata in acest paragraf

Figura 4.2.2 Script-ul EX_422.sql creeaza indexul SSN_INDEX.

Figura 4.2.3 Script-ul EX_423.sql contine o interogare pe vederea DBA_INDEX care va determina indecsii unei tabele.

Indecsi maresc performantele instructiunii SELECT. Ei vor incetini insa inserarile, actualizarile si stergerile, deoarece toate aceste actiuni asupra tabelei sunt realizate si asupra indexului. Daca se insereaza, de exemplu, cinci mii de inregistrari intr-o tabela, vor rezulta cinci mii de inserari de indecsi, ceea ce va avea ca efect scaderea performantelor.

Inpactul unui index asupra unei interogari va depinde de cardinalitatea coloanelor pe baza carora este creat indexul. De exeplu, o coloana ce contine ca valori numai ’ Adevarat ’ sau ’ Fals ’ (cardinalitate scazuta) va fi o alegere nefericita pentru un index obisnuit. O coloana ce contine valori foarte diferite (cardinalitate ridicata) , va fi o alegere buna pentru un index obisnuit.

Vederea DBA_INDEX din dictionarul de date contine informatii despre fiecare index din baza de date. Rezultatul figurii 4.2.3 este o lista a tuturor indecsilor. Un exemplu de rezultat al script-ului EX_423 va fi prezentat in continuare:

4.3 Determinarea parametrilor initiali de stocare pentru un index

Vom prezenta in continuare cateva formule de calcul utilizate pentru: determinarea marimii antetului de bloc figura 4.3.1 (implicit aceasta valoare este de 113 octeti), determinarea spatiului liber din fiecare bloc figura 4.3.2 (factorii luati in calcul sunt marimea blocurilor bazei de date si parametrul PCTFREE pentru index), determinarea lungimii unei intrari de index figura 4.3.3 (formatul ROWID in Oracle 8 este de 8 octeti, mai sunt adaugati 2 octeti pentru antet), determinarea numarului de inregistrari care vor incapea intr-un bloc 4.3.4, determinarea paramentrului de stocare INITIAL pentru un index figura 4.3.5.

Figura 4.3.1 Formula de calcul utilizata pentru determinarea marimii antetului de bloc.

Figura 4.3.2 Formula de calcul utilizata pentru determinarea spatiului liber din fiecare bloc.

Figura 4.3.3 Formula de calcul utilizata pentru determinarea lungimii unei intrari de index.

Figura 4.3.4 Formula de calcul utilizata pentru determinarea numarului de inregistrari care vor incapea intr-un bloc.

Figura 4.3.5 Formula de calcul utilizata pentru determinarea parametrului de stocare INITIAL pentru un index.

Daca nu anticipati ca vor exista multe operatii de inserare, actualizare sau stergere, alege-ti pentru parametrul PCTFREE o valoare mica, de exemplu 10. Daca anticipati ca datele din coloanele indicate vor creste sau se vor micsora, spre exemplu in cazul inserarii de text intr-un camp de text indexat, atribuiti parametrului PCTFREE o valoare mare, cum ar fi 30 sau 40. Astfel veti optine un index neechilibrat.

4.4 Impunerea unicitatii printr-un index

Specificul activitatii impune ca fiecare inregistrare dintr-o tabela sa aiba valori unice in anumite coloane, daca dorim sa aplicam aceasta regula prin realizarea unui index unic, atunci trebuie sa stim ca acest lucru are ca efect si cresterea performantelor la interogarea datelor. Utilizand clauza UNIQUE a comenzii CREATE INDEX vom putea crea indecsi unici. Un index unic asigura ca nu exista doua inregistrari care sa aiba aceiasi valoare in coloanele care alcatuiesc indexul. Orice incercare de adaugare a unei noi inregistrari care contine valori duplicate va fi respinsa. De asemenea, orice incercare de a modifica datele continute in coloanele indexului av fi respinsa.

Vom crea in continuare ( figura 4.4.1 )pentru tabela EMPLOAYEE un index unic pentru combinatia de coloane EMPLOAYEE_NAME si SSN.

Figura 4.4.1 Crearea unui index unic.

Cheile unice sunt create automat cand se creeaza pe tabela o restrictie cheie primara sau cheie unica.

4.5 Partitionarea unui index

Utilizand clauza PARTITION din comenzile CREATE INDEX, sau ALTER INDEX, putem crea un index partitionat. Un index partitionat ca si o tabela partitionata, permite definirea unor parametri de stocare diferiti, inclusiv spatiu-tabela, pentru fiecare partitie.Exista doua metode pentru a defini un index partitionat: LOCAL si GLOBAL. Un index LOCAL partitionat se bazeaza pe o tabela partitionata. Este metoda cel mai des folosita(sintaxa pentru crearea uni index partitionat se afla in figura 4.5.1). Un index partitionat GLOBAL permite o mai mare flexibilitate in definirea domenilor partitionare( sintaxa lui e prezentata in figura 4.5.2).

Figura 1.3.6F Sintaxa SQL pentru crearea unui index partitionat local.

Clauza PARTITION BY RANGE, specificata numai pentru un index global, defineste coloanele pe care se bazeaza domeniile de partitionare. Ca si in cazul tabelei partitionate, lista de coloane specifica pe care dintre ele este partitionat indexul. Clauza PARTITION specifica numele partitiilor individuale, parametrii lor de stocare si spatiile-tabela. Clauza VALUES LESS THAN(valori mai mici decat) specifica limitele superioare pentru partitii. Aceasta este utilizata numai pentru un index de tip GLOBAL.

Figura 1.3.7F Sintaxa SQL pentru crearea unui index partitionat global.

Un index poate avea pana la 64.000 de partitii.

Cap. 5 Vederi

5.1 Introducere, crearea unei vederi

O vedere este o tabela logica creata pe baza unei inregistrari. Vederile nu stocheaza date, dar prezinta date din alte surse, cum ar fi tabele sau alte vederi. Intr-o aplicatie, o vedere arata si se comporta ca o tabela. Datele dintr-o vedere pot fi interogate si, in multe cazuri sunt posibile inserarea, actualizarea si stergerea de date dintr-o vedere. Vederile pot fi utilizate pentru a prezenta datele in formate diferite de modul in care sunt stocate in tabelele de baza. Vederile simplifica prezentarea datelor prin ascundrea structurii reale a tabelelor de baza si ele pot asigura un inalt nivel de securitate al datelor la nivele de linie, prezentand unui utilizator un subset restrans de date din tabela de baza.

O vedere poate fi creata in diferite scopuri. Vederile pot fi utilizate pentru a reprezenta inregistrari pe tabele su pe alte vederi intr-o tabela virtuala. Vederile sunt utilizate frecvent pentru a obtine un subset de date sau pentru a reuni cateva tabele ce nu vor putea fi vizualizate de conturile utilizator. Aceasta metoda este folosita pentru marirea securitatii datelor si pentru utilizarea unui cod simplificat.

Instructiunea CREATE VIEW creeaza o vedere. Cand se executa instructiunea va prezenta erorile de compilare, daca e cazul, dar vederea va fi creata in dictionarul de date.

Figura 1.4.1F Exemplu de creare a unei vederi;

Vederile la care are acces un utilizator pot fi interogate din dictionarul de date prin vederile USER_VIEWS si ALL_VIEWS ale acestuia. Pentru ca este posibil sa existe mai multe vederi disponibile, vom prezenta in continuare o metoda de determinare a acestor vederi.

5.2 Determinarea vederilor obtinute de un anumit proprietar

Vederea USER_VIEWS din dictionarul de date contine vederile detinute de un cont utilizator conectat la baza de date. Vederea ALL_VIEWS din dictionarul de date contine vederile pe care le detine un cont utilizator sau pentru care i s-au acordat privilegii. Ambele vederi pot fi interogate pentru a obtine lista vederilor disponibile. In tabelul 5.2.1 sunt prezentate coloanele disponibile in vederea ALL_VIEWS din dictionarul de date.

Tabelul 5.2.1 Vederea ALL_VIEWS din dictionarul de date

Vederile pot fi utilizate in multe situatii, la fel ca si tabelele. Crearea unei liste cu vederile disponibile este un proces simplu. Daca se dezvolta o aplicatie care trebuie sa contina o lista cu numele vederilor, atunci putem include in aplicatie vederile din dictionarul de date.

5.3 Determinarea unei vederi actualizate

O vedere nu poate fi actualizata in anumite situatii: daca ea reuneste mai multe tabele, utilizeaza un operator DISTINCT, contine clauza GROUP BY sau functii de gup. O modalitate simpla de a determina daca vederea poate fi modificata este sa efectuati o operatie de gestionare a datelor asupra ei si apoi sa interpretati mesajul de eroare, daca apare vreunul.

Posibilitatea de a acualiza o vedere obtinuta pe baza mai multor tabele permite o mare flexibilitate in dezvoltarea aplicatiilor. Daca o tabela dintr-o vedere obtinuta prin reuniune este protejata prin cheie, ea poate fi modificata in functie de unele criterii suplimentare. Exista de asemenea posibilitatea de a actualiza vederile obiect.

Vederile obiect sunt utile cand se opereaza cu tipuri de date abstracte si date de tip obiect. De asemenea in vederile ce contin mai multe tabele se pot realiza inserari.

O vedere construita pe baza unei reuniuni poate fi actualizata daca ramane protejata prin cheie. Fiecare inregistrare trebuie sa fie unic inregistrabila si cheile nu trebuie sa contina valori NULL Vederile obiect pot fi de asemenea actualizate utilizand declansatori INSTEAD OF. In locul restrictiilor cheie primara/cheie straina se pot crea indecsi UNIQUE pentru coloanele protejateprin cheie.

5.4 Crearea unei vederi prin reuniune ce poate fi actualizata

Cand o vedere este realizata pe baza unei singure tabele sau obiect, asupra vederii pot fi executate toate instructiunile Limbajului de Manipulare a Datelor ( DML), cum ar fi inserari, actualizari si stergeri, daca contul utilizator are privilegiile necesare. Cand insa doua sau mai multe tabele sunt reunite intr-o vedere, exista limitari in ceea ce priveste modul in care se pot executa inserarile, actualizarile si stergerile in tabelele de baza.

Numai o singura tabela poate fi modificata, si cu anumite restrictii. Cea mai importanta conditie este ca tabela ce va fi modificata sa fie protejata prin cheie. O tabela protejata prin cheie are toate coloanele de identificare cheie unica continute in clauza SELECT din textul vederii. Pentru ca vederea sa fie modificabila, reuniunea obtinuta trebuie sa defineasca in mod unic fiecare inregistare din vedere. In continuare vom prezenta exemple in care este explicata aceasta conditie importanta.

In afara de a fi protejata prin cheie, vederea trebuie sa nu contina anumite clauze. Acestea sunt pseudo-coloana ROWNUM, ierarhii din tabele( START WITH; CONNECT BY), operatii de setare (UNION, MINUS si altele), functii de grupare( DISTINCT; GROUP BY; HAVING) si functii matematice( COUNT, MAX, SUM etc.).

Pentru a putea sterge inregistrari, o singura tabela din reuniunea rezultata poate sa fie protejata prin cheie. Pentru a putea insera inregistrari, toate coloanele vederii trebuie sa provina din tabele protejate prin cheie. Daca o vedere contine coloane din mai multe tabele si cel putin o coloana provine dintr-o tabela neprotejata prin cheie, atunci serverul Oracle nu va putea ss identifice in mod unic inregistrarea ce urmeaza sa fie inserata. La fel ca si in cazul inserarii de inregistrari, in cazul actualizarii, tote coloanele vederii trebuie sa provina din tabele protejate prin cheie. Daca o vedere contine mai multe coloane din mai multe tabele si cel putin o coloana provine dintr-o tabela neprotejata prin cheie, atunci serverul Oracle nu va putea sa identifice in mod unic inregistrarea ce trebuie actualizata.

Prezentam in continuare un exemplu de creeare a unei vederi pe baza reuniunii a doua tabele PART01 si FACTORY01:

Figura 5.4.1 Script ce creeaza tabelele PART01 si FACTORY01 care vor fi utilizate pentru a genera o vedere prin reuniune ce poate fi modificata.

Figura 5.4.2 Script ce creeaza date de test pentru tabelele PART01 si FACTORY01.

Figura 5.4.3 Script ce creaza vederea PART_VIEW01 pe baza reuniunii tabelelor PART01 si FACTORY01.

Mod de functionare al exeplului anterior:

in etapa 1 se creeaza tabelele PART01 si FACTORY01. Prima este unic definita prin cheia primara PART_NO, iar a doua este unic definita prin cheia primara FACTORY_NO, iar cele doua tabele sunt reunite printr-o relatie mai multi-la-unu pe baza coloanei FACTORY_NO din fiecare tabela;

in etapa 2 se insereaza date de test in ambele tabele;

in etapa 3 se creeaza vederea PART_VIEW01 pe baza reuniunii celor doua tabele.

5.5 Simularea unei interogari incrucisate( cross-tab ) utilizand o vedere.

Functia DECODE este un instrument puternic utilizat in crearea interogarilor complexe. Aceasta functie actioneaza ca o instructiune IF … THEN … ELSE. Ea poate fi utilizata pentru a vizualiza datele pe orizontala pentru ca returneaza o valoare in cazul in cazul in care linia se afla in coloana corecta.

Executati SQL*Plus si conectati-va ca utilizator WAITE. Script-ul EX_551.sql, prezentat in figura 5.5.1, construieste o tabela pe baza careia se va construi vederea CROSS_TAB si in care se vor introduce date de test.

Figura 5.5.1 Script-ul EX_551.sql creeaza obiectele de test utilizate ulterior.

Coloana VALUE reprezinta valoarea pentru luna respectiva. Vom executa urmatoarea comanda ( Figura 5.5.2 ) pentru a crea tabela si a introduce datele de test:

Figura 5.5.2

Figura 5.5.3 Contine un script SQL ce interogeaza tabela creata in etapele precedente si afiseaza datele orizontal.

In linia 1 incepe instructiunea de interogare si se selecteaza coloana YEAR. Coloana Year este singura ce nu este modificata printr-un operator GROUP BY. Liniile de la 2 la 4 returneaza valorile pentru primele trei luni. Functia DECODE returneaza VALUE daca valoarea din coloana MONTH este egala cu al doilea parametru, altfel returneaza 0. Operatorul SUM afiseaza o singura linie pentru fiecare an. Deoarece va fi returnat 0 pentru fiecare coloana in care una nu corespunde, operatorul SUM nu va afecta datele decat prin gruparea lor pe o singura linie. In linia 7 datele sunt grupate dupa coloana YEAR. Intrucat coloana YEAR selecteaza in linia 1 nu este o expresie GROUP BY, este necesara specificarea clauzei GROUP BY in linia 7.

Lansand in executie comanda START ( Figura 5.5.4 )asupra scriptului EX_1452.sql vom obtine:

Figura 5.5.4

Functia DECODE utilizeaza o proprietate importanta a produsului Oracle. Ori de cate ori este interogata o vedere, sunt interogate mult mai multe linii din tabelele de baza decat cele prezentate in vedere. Acest lucru poate cauza o scadere a performantelor.

5.6 Reconstruiri de instructiuni CREATE VIEW

Interogarea utilizata pentru a re-crea instructiunile CREATE VIEW utilizeaza vederea ALL_VIEWS din dictionarul de date. Coloana TEXT contine interogarea utilizata de vedere pentru a returna linii. Coloana TEXT contine date de tip LONG, ceea ce ingreuneaza modul de lucru cu ea in SQL*Plus. Multi operatori si functii nu pot fi utilizati cu coloane ce contin date de tip LONG.

Figura 5.6.1 Script-ul EX_561.sql interogeaza coloana TEXT din vederea ALL_VIEWS din dictionarul de date.

Executand SQL*Plus si conectandu-ne ca utilizator WHITE. Script-ul EX_561.sql, prezentat in figura 5.6.1, descrie interogarea coloanei TEXT din vederea ALL_VIEWS din dictionarul de date.

Primele trei comenzi seteaza variabilele de sistem SQL*Plus pentru a inhiba pentru afisarea antetului pentru coloane, a stabili afisarea datelor de tip LONG la 2000 de caractere si a inhiba afisarea mesajului de sfarsit al interogarii. Acestea formateaza rezultattele interogarii si inlatura afisarea antetului si a mesajului de sfarsit, care apar cand se executa o interogare. In linia 4 se suprima verificarea variabilelor de substitutie. Interogarea intoarce coloana TEXT din vederea ALL_VIEWS din dictionarul de date.

Script-ul EX_562.sql, prezentat in figura urmatoare, contine cuvintele cheie CREATE OR REPLEACE VIEW. Aceste cuvinte nu se concateneaza cu coloana TEXT pentru ca aceasta contine date de tip LONG. Coloanele cu date de tip LONG nu pot fi concatenate cu alte coloane si nu pot utiliza nici o functie de tip caracter.

Figura 5.6.2 Script-ul EX_5.6.2.sql returneaza instructiunea CREATE VIEW

Figura 5.6.3 Script ce creeaza instructiunea CREATE OR REPLEACE VIEWprin ad aogarea acestor cuvinte cheie la interogare.

Oprirea transmiterii rezultatului interogarii in fisier se poate face cu ajutorul instructiunii SPOOL OFF. Dupa ce fisierul de iesire este inchis, el poate fi executat ca un fisier de comanda SQL*Plus pentru a reconstrui instructiunea ce creeaza vedrea prin interogarea dictionarului de date.

5.7 Implementarea securitatii la nivel de inregistrare utilizand vederi

Pseudo-coloana USER contine contul utilizatorului conectat la baza de date. Majoritatea pseudo-coloanelor pot fi utilizate in vederi. Pseudo-coloana USER poate restrictiona accesul pe criterii legate de data calendaristica. Se pot utiliza subinterogari cu operatorul IN pentru a obtine liste cu valori valide.

Vederile sunt utilizate frecvent pentru a restrictiona accesul la date al utilizatorilor. Utilizatorii pot folosi diferite vederi pentru aceleasi date. Un subset al datelor din tabela de baza poate fi prezentat prin includerea in vedere a unei clauze WHERE, care restrictioneaza datele pe baza numelui contului utilizator sau a privilegiilor.

Vederile restrictionate ar trebui utilizate pentru a limita posibilitatea de vizualizare a datelor de catre un utilizator doar la acele date care ii sunt necesare in activitatea sa. Restictionarea vederilor poate avea un efect in cascada.

Mai multe lucruri detaliate le vom spune in urmatorul capitol.

Cap. 6 Securitate

Notiuni generale despre securitatea bazelor de date

Securitatea bazelor de date este o problema majora in dezvoltarea aplicatiilor Oracle multiutilizator. Pe baza tehnologiilor avansate, au fost create instrumente care pot accesa bazele de date Oracle pe cai mult mai sofisticate. Cand se folosesc instrumente de interogare rapide, mai usor de utilizat, este important sa va asigurati ca numai aplicatiile care au aprobare pot interoga si modifica datele. O problema fundamentala in dezvoltarea si intretinerea aplicatiilor multiutilizator de baze de date este gestionarea conturilor utilizator si a rolurilor. Utilizatorii nu trebuie sa utilizeze in comun aceleasi conturi si fiecare cont utilizator trebuie sa aiba o parola prin care baza de date este protejata inpotriva accesului neautorizat. Un rol este util intr-o organizare de acest tip pentru ca el include mai multe privilegii care pot fi acordate unui cont utilizatorprintr-o singura comanda. Structura rolurilor din produsul Oracle permite ca securitatea bazei de date sa fie modelata in functie de cerintele din organizatie. In acest capitol sunt parcurse problemele legate de asigurarea securitatii datelor in aplicatiile Oracle.

In Oracle se pot acorda doua tipuri de privilegii, si anume: privilegii de sistem si de obiect unui cont utilizator. Privilegiile de sistem vor fi utilizate pentru a permite utilizatorilor sa creeze obiecte si alte conturi utilizator, sa exporte baza de date si pentru alte operatii. Privilegiile de obiect vor fi folosite pentru a permite utilizatorilor sa execute instructiuni INSERT, UPDATE, SELECT, DROP si sa manipuleze obiecte, cum ar fi tabele, secvente si indecsi.

6.2 Acordarea privilegiilor de sistem si de obiect

Utilizand comanda GRANT se pot acorda ambele tipuri de privilegii, de sistem si de obiect, unui cont utilizator. Privilegiile pot fi de asemenea acordate rolurilor. Sintaxa este prezentata in figura 6.2.1.

Figura 6.2.1 Sintaxa de acordare a privilegiilor;

In Oracle 8 exista in jur de 100 de privilegii de sistem ce pot fi afisate prin interogarea vederii SYSTEM_PRIVILEGE_MAP din dictionarul de date. Cele mai utilizate privilegii sunt prezentate in tabelul 6.2.2. Majoritatea privilegiilor DROP au privilegii corespunzatoare ALTER si CREATE, cum ar fi CREATE ANY INDEX.

Tabelul 6.2.3 Privilegii de sistem

6.3 Crearea unui rol

Un rol defineste un grup de privilegii inrudite si acorda unui cont utilizator o colectie de privilegii intr-o singura etapa. In loc sa se acorde unui cont utilizator drepturi de acces pentru fiecare obiect in parte, i-se poate acorda acces la un rol care defineste un set complet de privilegii.

Rolurile bazei de date trebuie create astfel incat sa reprezinte grupuri de privilegi inrudite in baza de date. Instructiunea CREATE ROLE este utilizata pentru a crea un rol nou, iar sintaxa ei este prezentata in figura 6.3.1.

Clauza IDENTIFIED BY defineste o parola pentru rol. Cand un rol contine o parola, parola trebuie sa fie specificata cand se activeaza rolul. Cand se creeaza un rol nou, el nu are privilegii; acestea trebuie acordate rolului pentru ca rolul sa aiba efect cand este acordat unui cont utilizator. Rolurile CONNECT, RESOURCE si DBA sunt furnizate de programul Oracle pentru a asigura diferite nivele de acces. Conturile utilizator care au rolul CONNECT au acces la baza de date, dar nu-si pot crea propriile obiecte. Ele pot accesa doar propriile obiecte. Ele pot accesa doar obiectele altor conturi utilizator pentru care li s-au acordat drepturi. Conturile utilizator care au conturile RESOURCE pot crea propriile lor obiecte in baza de date. Rolul DBA acorda unui cont utilizator privilegii puternice; acces complet la baza de date si posibilitatea de a acorda privilegii altor conturi utilizator.

Daca executam SQL*Plus si ne conectam ca utilizator WHITE, cu ajutorul urmatorului script vom crea un rol:

Figura 6.3.1 Script-ul EX_631.sql creaza un rol.

Figura 6.3.2 Script-ul EX_632.sql contine o instructiune CREATE ROLE pentru a crea un rol protejat prin parola.

Figura 6.3.3 Script-ul EX_633 contine o instructiune DROP ROLE pentru a sterge un rol.

Rolurile cresc capabilitatiile de securitate ale produsului ORACLE si reduc pentru administrator volumul de timp necesar intretinerii bazei de date. Un model de securitate trebuie dezvoltat utilizand roluri care protejaza in mod eficient baza de date.

6.4 Acordarea de roluri unui utilizator

Conturile utilizator sunt asociate cu rolurile prin utilizarea comenzii GRANT. Atat privilegiile de sistem cat si cele de obiect pot fi acordate conturilor utilizator si rolurilor prin intermediul instructiunii GRANT.

In exemplil urmator vom prezenta un efect al instructiunii GRANTsi acordarea de privilegii de obiect INSERT, SELECT, UPDATE si DELETE pe tabela DEPT lui PUBLIC. Toate conturile utilizator vor putea modifica datele din aceasta tabela, indiferent ce alte privilegii mai au. Toti utilizatorii vor trebui sa refere tabela ca WAITE.DEPT, cu exceptia cazului in care a fost creat un sinonim PUBLIC pentru tabela DEPT

Figura 6.4.1 In script-ul EX_641.sql se acorda privilegii de obiect lui PUBLIC.

Inul sau mai multe privilegii sau roluri pot fi acordate unuia sau mai multor conturi utilizator printr-o singura instructiune. Clauza WITH ADMIN OPTION are urmatoarea semnificatie: cel care a primit rolul poate acorda la randul lui acest rol altui cont utilizator sau rol.

Prin asocierea de seturi unice de privilegii diverselor roluri, puteti crea cu usurinta un mediu sigur si usor de gestionat pentru aplicatiile dumneavoastra. In loc sa utilizati comenzi GRANT pentru zeci sau chiar mii de utilizatori, puteti sa le atribuiti un rol si sa folositi o singura comanda GRANT pentru acel rol. Toti utilizatorii asociati unui rol vor obtine acel privilegiu.

Exemplu de acordare rolului unui cont utilizator( SMITH )

Figura 6.4.2 Script-ul EX_642.sql creaza obiectele utilizate in acest exemplu.

Figura 6.4.3 Script-ul EX_1533.sql acorda privilegii de sistem rolului GENERAL.

Figura 6.4.4 Script-ul EX_6.4.4.sql acorda privilegii de obiect rolului GENERAL.

Figura 6.5.4 Script-ul EX_654.sql acorda rolul GENERAL contului utilizator SMITH.

Mod de functionare:

In etapa 1 se creeaza un cont utilizator, un rol si o tabela care sunt utilizate in acest exemplu;

In etapa 2 se acorda rolului GENERAL privilegiile CREATE SESSION si ALTER SESSION. Conturile utilizator carora li se acorda acest rol se pot conecta la baza de date.

In etapa 3 se acorda privilegiile SELECT, INSERT, UPDATE sI DELETE pentru tabela DEPT rolului GENERAL

In ultima etapa se acorda rolul GENERAL contului utilizator SMITH. Acest cont mosteneste toate privilegiile de sistem si de obiect care au fost acordate rolului GENERAL.

6.6 Activarea si dezactivarea rolurilor in timpul executiei aplicatiei

Instrumentele rapide de interogare creeaza o problema complicata in mediul Oracle. Este uneori necesar sa restrictionati accesul utilizatorilor la datele folosite in aplicatiile lor. Vom descrie in continuare procesul protejarii datelor confidentiale inpotriva instrumentelor de interogare rapida prin activarea si dezactivarea rolurilor in timpul executiei.

Cu ajutorul rolurilor protejate prin parola se poate restrictiona accesul instrumentelor de interogare rapida. Accesul poate fi restrictionat pe doua nivele. Acestor instrumente li se poate interzice modificarea datelor sau vizualizarea lor. Aplicatiile care utilizeaza tabele trebuie sa contina o instructiune SET ROLE sau un apel al procedurii DBMS_SESSION.SET_ROLE(Sintaxa acestei proceduri o vom vedea in figura 6.6.1) dupa ce s-au conectat la baza de date. Aceasta procedura este utilizata in aplicatii pentru a activa rolurile protejate prin parola. Parola pentru rol trebuie pastrata secreta fata de utilizatorii aplicatiei. Fara a cunoaste parola rolului si metoda de a o activa, utilizatorii vor avea acces restrictionat la date.

Figura 6.6.1 Sintaxa procedurii DBMS_SESSION.SET_ROLE utilizata pentru a activa un rol;

In etapa mai sus mentionata este momentul in care codul de program trebuie inserat in aplicatiile ce utilizeaza tabela restrictionata. Procedura DBMS_SESSION.SET_ROLE este utilizata in aplicatii pentru a activa rolurile protejate prin parola.

6.7 Determinarea drepturilor de acces ale unui utilizator la un anumit obiect.

Vederea DBA_TAB_PRIVS din dictionarul de date contine privilegiile de obiect acordate tuturor utilizatorilor si rolurilor. Desi contine particula TAB in numele ei, vederea este utilizata pentru tabele, secvente, proceduri, pachete, functii, vederi, legaturi ale bazei de date si alte obiecte.

Prin acordarea unui rol, contul uni utilizator primeste toate privilegiile asociate acelui rol. Una din multele sarcini ale unui administrator de baze de date este sa determine ce utilizatori au acces si la ce obiecte. Cu drepturi de acces adecvate, un cont utilizator poate insera, actualiza, selecta si sterge date dintr-un obiect, chiar daca obiectul este detinut de alt cont utilizator.

Figura 6.7.1 Script ce detrmina toate privilegiile de obiect acordate pentru un anumit obiect;

Exista cateva privilegii de obiect ce pot fi returnate de interogarea din scriptul 6.7.1. Privilegiile de obiect in Oracle8 sunt: ALTER, AUDIT, DELETE, COMMENT, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, EXECUTE, CREATE, READ si WRITE.

Pentru orice obiect din baza de date, fiecare cont utilizator poate avea diverse privilegii pentru a modifica obiectul sau pentru a vizualiza datele din obiect. Cu drepturi de acces adecvate, un cont utilizator poate executa urmatoarele operatii: modificare (ALTER), inserare (INSERT), actualizare (UPDATE), selectare (SELECT) sau stergere(DELETE) asupra unui obiect. De asemenea contul utilizator poate avea privilegii SELECT pentru o secventa si privilegii EXECUTE pentru un pachet, o procedura sau o functie.

Daca un privilegiu de obiect a fost acordat printr-un rol caruia i-a fost acordat un alt rol la cre este asociat contul utilizator, atunci determinarea privilegiilor de obiect este mult mai dificila. Daca se intalneste o astfel de situatie, trebuie parcursa structura rolului si o determinare daca utilizatorul este asociat unui rol care are privilegii pentru tabela. De asemenea, daca un cont utilizator detine un obiect, atunci acel cont utilizator are toate privilegiile de sistem si de obiect care au fost acordate la PUBLIC.

6.8 Activarea auditarii unei baze de date

Auditarea bazei de date este o metoda puternica de monitorizare a activitatiilor din baza de date. Audierea poate furniza informatii despre o multime de tipuri de activitati. Ea poate monitoriza modificarile, schimbarile selectiile dintr-o tabela sau acordarile de privilegii pentru o tabela si multe altele. Prin audiere se poate urmaricand si cat de des se conecteaza la sistem un anumit cont utilizator. Prin audiere se pot inregistra anumite erori care apar in baza de date, cum ar fi problemele legate de permisiuni.

Utilizand comanda AUDIT se poate urmari o gama larga de activitati din baza de date. In versiunea Oracle 8 pot fi audiate o suta cincizeci si noua de actiuni diferite. Actiunile se pot impartii in trei categorii generale: audierea obiectelor ( ori de cate ori un obiect este referit de catre un utilizator), auditarea privilegiilor ( ori de cate ori un privilegiu este utilizat sau se esueaza cand se incearca utilizarea lui) si auditarea instructiunilor ( cand se executa o instructiune).

Figura 6.8.1 Sintaxa instructiunii AUDIT;

Auditarea obiectelor poate implica audierea instructiunilor ce se executa pe obiecte, cum ar fi CREATE, INDEX, ALTER TABLE, GRANT pe obiect sau DROP PUBLIC DATABASE LINK. Auditarea privilegiilor poate implica audierea oricaror privilegii de sistem, cum ar fi privilegiul SELECT ANY SEQUENCE, privilegiul ALTER SYSTEM sau privilegiul ALTER USER. Auditarea instructiunilor poate inregistra instructiuni DDL sau DML, in functie de un obiect al bazei de date.

Toate instructiunile de auditare pot fi specificate pentru un anumit utilizator sau pentru toti utilizatorii folosind clauza BY nume_utilizator. Daca se doreste se poate specifica inregistrarea in fisierul de audit numai a instructiunilor care se executa cu succes sau esueaza, prin utilizarea clauzelor WHENEVER SUCCESSFUL sau WHENEVER NOT SUCCESSFUL. Clauzele BY ACCESS si BY SESSION arata cat de des se actualizeaza fisierul de audit intr-o sesiune, intre conectarea si deconectarea de la baza de date. De exemplu: daca un utilizator initiaza zece instructiuni SELECT pe o tabela auditata, atunci se va inregistra o inregistrare in fisierul de audit daca a fost specificata clauza By SESSION si zece inregistrari daca a fost specificata clauza BY ACCESS.

Conturile utilizator SYS si INTERNAL nu pot fi audiate. Daca un utilizator deruleaza inapoi o tranzactie, instructiunea ramane audiata. Un cont utilizator poate audita numai activitatiile din contul sau propriu daca nu are privilegiul AUDIT ANY si nu este asociat cu rolul DBA. Fisierele de audit ale sistemului de operare sunt generate automat in directorul $ORACLE_HOME/RDBMS/AUDIT si in ele se gasesc informatii despre toate pornirile instantelor, opririle instantelor si conectarile la baza de date ca utilizator DBA. Chiar daca operatia de auditare nu este activata, acest fisier se creeaza. Din cand in cand, aceste fisiere trebuie sterse pentru a evita umplerea discului.

Stergerea informatiilor de audit

Pentru a dezactiva un anumit eveniment de auditare in timp ce alte actiuni de auditare raman activate, utiliza-ti comanda NOAUDIT. Comanda NOAUDIT este opusa comenzii AUDIT. Ea dezactiveaza toate auditarile de obiecte, instructiuni si privilegii de sistem pe baza setului de optiuni de auditare selectat. Sintaxa comenzii NOAUDIT este prezentata in figura 6.9.1.

Pentru a dezactiva complet auditarea bazei de date, modificati fisierul de initializare si schimbati valoarea pentru parametrul AUDIT_TRAIL din DB sau OS in NONE. Daca vreti sa stergeti vederile din dictionarul de date referitoare la auditare, executa-ti scriptul $ORACLE/RDBMS/ADMIN/ catnoaud.sql. Pentru a sterge datele de audit, trebuie fie sa utilizati comanda TRUNCATE in tabela AUD$ din dictionarul de date, fie sa stergeti datele care nu mai sunt necesare din tabela.

Figura 6.9.1 Sintaxa comenzii NOAUDIT.

Produsul Oracle8 furnizeaza o serie de mecanisme pentru gestionarea si controlul parolelor care maresc considerabil securitatea bazei de date Oracle. Noile capabilitati asigura conformitatea parolelor cu standardele companiei. Acest lucru include optiuni de tipul: obligarea utilizatorilor de a-si schimba parolele la fiecare 30 de zile, blocarea accesului utilizatorilor la baza de date dupa un numar de incercari esuate, asigurarea ca parola are o anumita lungime si alte optiuni. Vom prezenta in continuare modul de atribuire si de schimbare a parolelor.

6.10 Gestionarea parolelor

Pentru a impune si a modifica gestionarea parolelor vom folosi comanda CREATE PROFILE. Dupa crearea unui profil asociat unui cont utilizator, vizualizarea informatiilor despre parole se face prin interogarea vederii DBA_USERS din dictionarul de date pentru ACCOUNT_STATUS ( neblocat, blocat, expirat ), LOCK_DATE ( data cand a fost blocat contul ) si EXPIRY_DATE (data si ora cand expira contul utilizator). Se poate de asemenea interoga vederea DBA_PROFILES pentru a vizualiza valorile parolele stabilite pentru fiecare profil. Exista cateva optiuni noi incluse in comenzile CREATE_PROFILE si ALTER_PROFILE, care sunt prezentate in tabelul 6.10.1.

Tabelul 6.10.1 Optiuni noi pentru profil

Se pot utiliza fie PASSWORD_REUSE_MAX fie PASSWORD_REUSE_TIME, dar nu ambele.

Optiunea PASSWORD_VERYFY_FUNCTION utilizeaza o functie implicitanumita verify_function( functie de verificare ) ce verifica daca parola fiecarui utilizator intruneste un minim de protectie impotriva intrusilor care incearca sa o ghiceasca. Acest lucru include asigurarea ca parola are mai mult de patru caractere, contine cel putin un numar, un caracter si un semn de punctuatie si are trei sau mai multe caractere diferite fata de parola anterioara. Parola nu poate fi una dintre parolele predefinite: abcd, account( cont ), computer, database, oracle, password, user ( utilizator ), sau welcome ( bine a-ti venit ).

Profilul implicit pentru toti utilizatorii are optiunile pentru parola setate la valorile prezentate in tabelul 6.10.2. De exemplu, valoarea implicita pentru PASSWORD_LOCK_TIME este 1/1440 si corespunde unui minut ( 1/1440 dintr-o zi ).

Tabela 6.10.2 Optiuni pentru parola ale profilului implicit.

Cap. 7 Restrictii

7.1 Notiuni generale, crearea unei restrictii cheie primara

In Oracle se pot defini restrictii pentru a impune cerintele aplicatiei asupra datelor din tabelele create si pentru a asigura validitatea datelor. De asemenea, restrictiile ofera posibilitatea de a defini legatura intre tabele. Este posibil sa impuneti cerintele aplicatiei dumneavoastra prin programe. Restrictiile transfera un volum mare de munca dinspre aplicatie spre baza de date. Toate datele din tabele trebuie sa respecte regulile impuse in restrictiile de baza. O restrictie de integritate impusa pe o tabela actioneaza ca un ” caine de paza ” care ne asigura ca instructiunile SQL care modifica datele din tabela, satisfac conditiile impuse de restrictie. O restrictie de integritate referentiala impune relatii principal-secundar intre tabele.

Vom incerca sa parcurgem in continuare procesul crearii cheii primare pentru o tabela. In primul rand trebuie sa mentionam ca o cheie primara este un set de coloane care identifica unic liniile dintr-o tabela. Procesul de creeare al cheii primare este esential in impunerea integritatii referentiale la nivelul bazei de date. Crearea unei chei primare stabileste un index unic pe tabela care poate duce la cresterea performantelor aplicatiei ce utilizeaza tabela.

Puteti crea o restrictie cheie primara pe o tabela ca parte a instructiunii CREATE TABLE sau utilizand instructiunea ALTER TABLE. In instructiunea CREATE TABLE clauza PRIMARY KEY este utilizata pentru definirea coloanelor ce alcatuiesc cheia primara. O cheie primara cu nume poate fi creata in instructiunea CREATE TABLE utilizand clauza CONSTRAINT…PRIMARY KEY. In insructiunea ALTER TABLE, o astfel de cheie poate fi creata cu ajutorul clauzei ADD PRIMARY KEY, sau, pentru o cheie primara cu nume, cu clauza ADD CONSTRAINT…PRIMARY KEY. Trebuie sa aveti grija sa definiti restrictiile NOT NULL pentru coloanele tabelei care necesita intotdeauna valori. Restrictia UNIQUE va asigura ca nu exista doua linii in tabela care sa aiba valori duplicate intr-o coloana specificata sau intr-un set de coloane. Restrictia UNIQUE NOT NULL va impiedica existenta valorilor NULL si pe cea a celor duplicate. Daca coloanele ce compun cheia primara nu contin explicit restrictia NOT NULL, serverul Oracle va aplica automat aceasta restrictie pentru fiecare coloana ce intra in alcatuirea cheii primare si este creat un index UNIQUE pe coloanele cheii primare. O restrictie UNIQUE NOT NULL este asociata implicit cu o cheie primara. Cand definiti o coloanautilizand instructiunea CREATE TABLE sau ALTER TABLE, puteti specifica valori implicite pentru o coloana prin utilizarea cuvantului cheie DEFAULT.

Figura 7.1.1 Scrpt ce creeaza tabela CUST01 cu o cheie primara

Linia 1 contine cuvintele cheie CREATE TABLE utilizate pentru a crea o tabela noua. Liniile de la 2 pana la 6 specifica coloanele din care este alcatuita tabela. In linia 4, desi nu este o coloana pe care s-a definit o cheie primara, se introduce restrictia NOT NULL pe coloana CUST_NAME pentru fiecare linie din tabela CUST01. Cuvantul cheie UNIQUE, continut in linia 5, impiedica duplicarea numerelor de telefon. Puteti de asemenea defini explicit o valoare DEFAULT pentru o coloana, asa cum este exemplificat in linia 6. Cand se insereaza o linie in tabela CUST01, daca nu este precizata o valoare pentru coloana LAST_UPD_DT, va fi utilizata o valoare implicita SYSDATE pentru pozitia respectiva din linia inserata. Linia 7 defineste cheia primara ca pe o cheie concatenata ce contine doua coloane.

Coloanele care alcatuiesc cheia primara nu pot avea volori NULL. Cand se creeaza o cheie primara pe o tabela, se creeaza restrictia NOT NULL pe coloanele incluse in cheie. O cheie primara poate contine cel mult 16 coloane, iar marimea totala a unei chei, in octeti, trebuie ss fie mai mica decat jumatatea marimii blocului bazei de date. Tipurile de data LONG si LONG RAW nu pot fi incluse intr-o cheie primara. Pe o tabela se poate defini o singura cheie primara, dar o tabela poate avea mai multe chei unice. Prin declararea unei chei primare, in general performantele cresc, in special daca cheia primara este alcatuita dintr-o singura coloana si, mai mult, daca datele din coloana respectiva sunt de tip intreg.

O restrictie de unicitate este introdusa implicit de o cheie primara, iar Oracle creeaza automat un index unic pe coloanele continute in cheia primara. Cu versiunea Oracle 8 puteti, de asemenea, sa utilizati indecsi non-unici pentru a impune restrictii de unicitate si de cheie primara. Indecsii non-unici nu sunt stersi cand este dezactivata restrictia, asa cum se intampla cu indecsi unici.

Daca nu se specifica un nume cand se creeaza o restrictie, Oracle va genera un nume unic pentru restrictie de forma SYS_Cn, unde n este un intreg. Un nume de restrictie dat de utilizator nu se va schimba in cursul operatiilor de import sau export ale sistemului, in timp ce un nume de restrictie generat de Oracle de obicei se schimba.Desi nu este necesara, atribuirea unor nume pentru restrictii usureaza administrarea lor pentru ca numele atribuite de utilizator sunt mai usor de tinut minte si nde inteles. O restrictie trebuie denumita ori de cate ori este nevoie sa fie referita dupa definirea ei. Acest lucru este foarte util pentru ca oridecate ori este violata o restrictie, numele restrictiei va fi inclus ca parte in mesajul de eroare si eroarea va fi mai usor de diagnosticat.

Valorile implicite trebuie utilizate ori de cate ori stiti ca o coloana ar trebui sa aiba o valoare implicitaspecifica. Tipul de data al valorii implicite trebuie sa se potriveasca cu tipul de data al coloanei. Coloana trebuie sa fie suficient de lunga pentru a contine valoarea creata. O expresie DEFAULT nu poate contine referinte catre alte coloane.

Pentru a crea restrictii pe o tabela, contul utilizator trebuie sa aiba dreptul de a crea tabele (privilegiile CREATE TABLE sau CREATE ANY TABLE) sau dreptul de a modifica tabele(privilegiile ALTER TABLE sau ALTER ANY TABLE).

7.2 Introducerea unei restrictii cheie straina

Integritaea referentiala este o trasatura a produsului Oracle care garanteaza faptul ca toate referintele la obiecte externe din cadrul fiecarui obiect al bazei de date sunt valide. Integritatea referentiala este impuss prin utilizarea unei combinatii de chei primare si chei straine. Cheia primara garanteaza faptul ca fiecare linie din tabela este unica. In unele paragrafe annterioare a fost abordata problema cheilor primare. O cheie straina defineste coloanele dintr-o tabela care trebuie sa existe ca si cheie primara a aceleasi sau a altei tabele. O cheie straina poate referi de asemenea o cheie unica a aceleasi sau a altei tabele. O cheie straina refera o cheie referita ( cheie primara sau unica ) si nu direct coloanele tabelei. O restrictie cheie straina poate fi creata in instructiunea CREATE TABLE sau adaugata mai tarziu, utilizand instructiunea ALTER TABLE.

Figura 7.2.1 Script ce sterge si recreaza cheia straina

Prima comanda este o instructiune ALTER TABLE care contine cuvintele cheie DROP CONSTRAINT pentru a sterge cheia straina din tabela EMP01. Acest lucru este necesar inainte de a crea aceiasi cheie straina pentru tabela EMP01 utilizand tabela ALTER TABLE. Clauza ADD CONSTRAINT, continuta in linia 2, este utilizata pentru adaugarea unei restrictii cu nume pe o tabela. Clauza CONSTRAINT este optionala si poate fi omisa daca nu aveti nevoie de o cheie straina cu nume specificat. Linia 3 contine clauza FOREIGN KEY pentru a crea o cheie straina pe coloana DEPT_NO din tabela EMP01. In linia 4, aceasta cheie straina refera cheia primara implicita din tabela EMP01.

Integritatea referentiala va ajuta sa mentineti datele valide. Nu este necesar ca acele coloane ale tabelelor aflate in relatie, parinte( principala ) si copil( secundara ), sa aiba acelasi nume, dar ele trebuie ss contina date de acelasi tip. Cheile straine nu pot fi alcatuite decat pe baza a maxim 16 coloane, exact ca si cheile primare. Daca lista de coloane nu este inclusa in clauza REFERENCES cand se defineste o cheie straina, va fi referita implicit cheia primara a tabelei specificate. Deoarece liniile din tabela copil refera chei din tabela parinte, nu este posibil sa actualizati cheia din tabela parinte sau sa stergeti inregistrarea din tabela parinte fara sa stergeti mai intai inregistrarile care le refera din tabela copil. Acest lucru este cunoscut si ca UPDATE RESTRICT( restrictie de actualizare ) si respectiv, DELETE RESTRICT( restrictie de stergere ). Daca se initiaza o operatie de inserare sau actualizare pe o tabela copil, cheile straine trebuie sa aiba deja o valoare pentru cheia referita din tabela parinte, sau valoarea NULL. La fel ca in cazul cheii primare, o restrictie NOT NULL nu este implicita pentru o cheie straina si, daca este necesar, trebuie definita explicit pe cheia straina.

In aplicatie trebuie sa se cunoasca restrictiile, indiferent de modul in care este impusa inregistrarea datelor in baza de date. Aplicatia trebuie sa execute tranzactiile in ordinea corecta, in concordanta cu restrictiile de integritate. Aplicatia trebuie sa trateze de asemenea exeptiile ce rezulta din incalcarea integritatii. Integritatea referentiala implementata prin aplicatii, si nu prin chei straine determina performante mai bune, tinand cont ca o restictie cheie straina necesita o citire suplimentara.

Contul utilizator trebuie sa fie proprietar al tabelei parinte sau sa aiba privilegiul REFERENCES pentru coloanele cheii primare din tabela parinte. Iar pentru a crea restrictii cheie straina in tabela copil, contul utilizator trebuie sa aiba posibilitatea sa creeze tabele( privilegiile ALTER_TABLE sau ALTER_ANY_TABL). Aceste privilegii trebuie sa fie acordate explicit celui care creeaza restrictia si nu pot fi obtinute print-un rol

7.3 Introducerea unei restrictii de validare( check ) pe coloana

Cand se creaza o tabela utilizand instructiune CREATE TABLE, se poate specifica independent o restrictie pe tabela, care nu este parte a unei definitii de coloana. In consecinta intr-o definitie a unei restrictii pe tabela pot fi incluse mai multe coloane. Restrictiile pe tabela si definitiile de coloane pot aparea in orice ordine, dupa cum urmeaza:

Figura 7.3.1

Clauza CONSTRAINT este optionala si poate fi utilizata pentru a crea o restrictie cu nume specificat. Clauza CHEK( validare ) creaza o restrictie de validare. Cuvantul conditie este o expresie de tip boolean care este evaluata utilizand valorile din linia care se insereaza sau actualizeaza. Restrictiile validate pe coloane sunt flexibile, dar exista cateva interdictii de care trebuie sa se tina seama la proiectarea lor. Interdictiile pentru o expresie dintr-o restrictie de validare pe o coloana sunt urmatoarele:

o expresie dintr-o restrictie de validare pe coloana nu poate referi alte coloane;

este interzisa utilizarea secventelor si interogarilor in expresie;

expresia nu poate apela functii de sistem: SYSDATE, UID, USER si USERENV;

expresia nu poate referi pseudo-coloane: CURRVAL, NEXTVAL, LEVEL, PRIOR si ROWNUM.

7.5 Determinarea dependentelor pentru cheia straina

Informatiile despre restrictii pot fi interogate din vederile USER_ CONSTRAINTS sau ALL_CONSTRAINTS din dictionarul de date. Coloanele asociate cu restrictiile pot fi aflate din vederiile ALL_CONS_COLUMNS. Cand este creata cheia primara este creat un index unic pe tabela cre pote fi aflata din vederea USER_INDEX. In tabelul 1.5.3F sunt prezentate coloanele din vederea USER_CONSTRAINTS si detalii despre cum trebuie interpretata valoarea din coloana CONSRAINT_TYPE pentru a gasi tipul restrictiei.

Tabela 7.5.1 Coloanele din vederea USER_CONSTRAINTS.

O exceptie este o linie dintr-o tabela care incalca o restrictie de integritate. Ca un caz special, pentru tabelele d3e indecsi in care liniile se identifica prin cheia primara si nu prin pseudo-coloana ROWID, pentru a crea tabela de exceptii este utilizata procedura BUILD EXCEPTIONS_TABLE din pachetul DBMS_IOT.

7.5 Crearea unei restrictii pe o tabela

Cand se creeaza o tabela utilizand instructiunea CREATE TABLE, se poate specifica independent o restrictie pe tabela, care nu este parte a unei definitii de coloana. In consecinta, intr-o definitie a unei restrictii pe tabela pot fi incluse mai multe coloane. Restrictiile pe tabela si definitiile de coloane pot aparea in orice ordine., dupa cum urmeaza in urmatoarea figura:

Figura 7.5.1.

Cand se defineste o restrictie pe tabela, restrictie_tabela contine urmatoarele:

Figura 7.5.2

Clauza CONSTRAINT este optionala si poate fi utilizata pentru a crea o restrictie cu nume specificat. Cuvantul conditie ce urmeaza dupa cuvantul cheie CHECK, este o expresie de tip boolean, dar nu se aseamana cu cea utilizata pentru restrictia de validare pe coloana; o expresie pentru conditia de validare pe tabela poate referi orice coloane din tabela in care este definita. Orice restrictie pe coloana poate fi definita utilizand sintaxa pentru restrictia pe tabela. O restrictie NOT NULL poate fi implementata ca o restrictie CHECK prin definirea unei restrictii pe tabela utilizand sintaxa din figura 7.5.3. Instructiunile ALTER TABLE permit crearea de restrictii de validare pentru o tabela deja existenta.

Figura 7.5.3.

In continuare vor urma cateva exemple de creare, stergere si recreeare a unei restrictii pe o tabela.

Figura 7.5.4 Script-ul EX_754 creeaza o tabela cu o restrictie pe tabela.

Figura 7.5.5 Script-ul EX_755.sql sterge si recreeaza o restrictie pe tabela.

Cheile compuse primare, straine si unice ar trebui implementate ca restrictii pe tabela. Restrictiile pe tabela pot referi coloane multiple. O tabela poate avea mai multe restrictii de tip FOREIGN KEY ( cheie straina ) si CHECK ( validare ). O coloana dintr-o tabela poate fi referita de mai multe restrictii pe tabela. Restrictiile de proiectare multiple trebuie proiectate cu grija pentru a evita aparitia conflictelor intre restrictii. Este mai bine sa fie mai multe restrictii de validare, fiecare cu cate o expresie simpla, decat o singura expresie de validare cu o expresie complexa. Daca survine o incalcare a integritatii datelor, apare un mesaj de eroare returnat de Oracle, care identifica restrictia. Acest mesaj de eroare poate fi utilizat pentru a identifica rapid regula incalcata, daca fiecare restrictie implementeaza o singura regula.

Cap. 8 Listingul programului

Programul prezentat in continuare este un program realizat in Vizual Basic 6 si Oracle8 Personal Edition. Partea grafica este realizata cu ajutorul formelor si rapoartelor din Visual Basic 6, dar si cu acela al imaginilor prelucrate cu Photoshop 6, iar bazele de date, securizarea bazelor de date si creearea anumitor scripturi: stergere, afisare, e.t.c., au fost facute cu Oracle8, mai exact cu SQL Plus 3.3 si Schema Bilder din pachetul Developer 2000.

Pentru a lansa in executie acest program se va da dublu clic pe fisierul scmssrl.exe sau pe shortcut-ul sau de pe desktop. De retinut este faptul ca acest program nu va merge decat pe un calculator care trebuie sa aiba urmatoarea configuratie minima: Pentium I, 166MHz, 1,2Gb, 64Mb RAM, si de asemenea: pachetul Oracle8 cu bazele de date specifice, Developer2000 si codurile de acces. Acesta sunt un ID si o parola care trebuie introduse inca de la inceput( figura 8.1 ). Daca cele doua coduri nu vor fi introduse bine din trei incercari atuni programul va iesi din executie. La fiecare intrare in program acesta va dechide implicit baza de date Local Database Oracle8 Personal Edition( figura 8.2 ).

Programul scmssrl.exe tine gestiunea brutariei S.C. Moara Stanca S.R.L. societate comerciala cu sediul in comuna Livezi, judetul Dolj, din data de 1 iunie 2002. In acest program se folosesc urmatoarele date de intrare: materii prime si produse finite, iar ca date de iesire sunt folosite rapoartele: fisele de magazie, si bonurile de consum pentru data ceruta de catre utilizator. Toate acestea apar in prima parte a programului, adica in meniul principal( figura 8.3 ).

Figura 8.1 Lansarea programului scmssrl.exe

Figura 8.2 Incarcarea programului scmssrl.exe si a bazelor de darte din Oracle8

Figura 8.3 Meniul Principal al programului scmssrl.exe.

Cele trei butoane din partea stanga a ferestrei Meniului Principal permit intrarea in alte trei ferestre cum sunt: Materii Prime( faina, drojdie, sare, gluten, ameliorator, zahar, ulei, rahat, mac, susan, figura 8.4 ), Produse Finite( figura 8.5 ) si Rapoarte (figura 8.6). In coltul stanga sus se gaseste o imagine ce reprezinta un semn de intrebare, iar sub aceasta un buton prin a carui apasare utilizatorul acestui program beneficiaza de un ”Ajutor”. Acesta este prezentat cu ajutorul paginilor web create in cod HTML si vizualizate cu Internet Explorer 5.0( figura 8 ). In coltul din dreapta jos se afla doua butoane ce fac posibila iesirea din program in doua feluri: Iesire cu salvare( salveaza toate modificarile executate in ziua respectiva ), Iesire fara salvare( atunci cand se introduc mai multe date gresite ).

Figura 8.4 Fereastra Materiilor Prime.

Figura 8.5 Fereastra Produselor Finite.

Fereastra Produse Finite prezentata in figura 8.5 contine urmatoarele: Paine de 400g, Paine de 500g, Cornuri cu rahat, batoane cu mac si Batoane cu susan.

Pentru introducerea materiilor prime, spre exemplu faina avem nevoie de trei date si anume de: data la care se introduce faina, cantitatea de faina care intra in ziua respectiva si pretul sau de intrare( figura 8.6 ). Aceleasi date trebuie introduse si pentru produsele finite, spre exemplu la painea de 400 de grame trebuie sa introducem data la care sa fabricat, cantitatea de paine fabricata, adica numarul de paini realizate si pretul lor de realizare.

Figura 8.6 Introducerea datelor la Faina.

Prelucrarile datelor care vor putea fi scoase si la imprimanta se realizeaza cu ajutorul rapoartelor in functie de: intrare, iesire( aceasta se calculeaza in functie de retetar si de numarul de produse efectuate in total la o anumita data ), data si pret.

Un raport, spre exemplu, Fisa Magazie Faina, ne prezinta: data si ora la care s-a scos raportul la imprimanta, in functie de data: intrarile, iesirile, stocul ramas si pretul de cumparare al fainii in acea zi( figura 8.7 ). In cadrul ferestrei Rapoarte se mai gaseste si optiunea Bon de Consum. Aceasta realizeaza un raport, la o data pe care o specificam, raport ce contine iesirile tuturor materiilor prime, preturile lor si cat au costat acele materiale, pretul neincluzand TVA( figura 8.9 ).

Figura 8.7 Model de raport pentru Fisa de magaziea a fainii.

Prima oara cand se incearca scoaterea unui astfel de raport se va intrioduce: User Name si Pasword, pentru a putea viziona si lista raportul dorit. Exemplul este dat in figura 8.8.

Figura 8.8 Conectarea prin ODBC la Oracle8

Figura 8.9 Raportul bonului de consum.