Proiectarea fizica a sistemelor informatice

Proiectarea fizica cunoscuta si sub numele de proiectare de detaliu, urmeaza proiectarii logice. Proiectarea logica intalnita si sub numele de proiectare generala, o alta varianta de definire a proiectarii logice. De fapt, printr-o astfel de referire se scoate in relief faptul ca in timpul proiectarii logice se prezinta o imagine de ansamblu (generala) a sistemului, in timp ce proiectarea fizica inseamna o abordare detaliata a sistemului. Cu alte cuvinte, in etapa de proiectare logica se acumuleaza informatiile de natura sa sintetizeze cerintele utilizatorilor noului sistem, operatiune prestata de analistii de sistem, iar in timpul proiectarii fizice se prezinta punctele de vedere ale specialistilor, cum ar fi cei din domeniul bazelor de date, securitatii sistemelor, retelelor de calculatoare, programarii, etc.

Proiectarea fizica implica parcurgerea urmatorilor pasi [1]:

1.     Proiectarea fizica a bazelor de date si a fisierelor. O astfel de activitate inseamna descrierea modului in care vor fi stocate datele si cum se va asigura controlul lor pentru a se oferi o securitate maxima;

2.     Proiectarea structurii sistemului si a programelor. Se descriu programele sau modulele acestora care sa fie in stransa concordanta cu diagramele fluxurilor de date si cu celelalte piese ale documentatiei realizate in etapele anterioare;

3.     Proiectarea strategiilor de prelucrare distribuita. Se vor prezenta modalitatile in care utilizatorul poate sa dispuna de date si facilitatile de prelucrare oferite de retele de calculatoare.

Proiectarea fizica a bazelor de date si a fisierelor

Modelul conceptual surprinde structura globala de organizare a datelor, asigurandu-se independenta totala fata de orice sistem de gestiune a bazelor de date. Modelul conceptual este prezentat prin intermediul diagramelor entitate-relatie(DER), motiv pentru care este cunoscut si sub numele de modelul entitate-relatie al datelor. El scoate in evidenta reprezentarea logica, detaliata a entitatilor, asocierilor (legaturilor) si datelor elementare ale unei organizatii sau ale unei parti din ea. Modelul se realizeaza in faza de analiza [1].

Modelul logic al datelor inseamna descrierea datelor in concordanta cu modelul de organizare a acestora de catre sistemele de gestiune a bazelor de date. In acest material s-a ales modelul relational. Conform cu acest model datele sunt reprezentate in baza de date sub forma tabelelor sau relatiilor create din diagrama entitate-relatie obtinuta in etapa anterioara.

O baza de date poate fi definita ca un ansamblu de date elementare sau structurate, accesibile unei comunitati de utilizatori. Mai concret, o baza de date este un ansamblu de fisiere intercorelate, care contine nucleul de date necesare unui sistem informatic (aplicatie informatica). Un fisier este un ansamblu de inregistrari fizice, omogene din punct de vedere al continutului si al prelucrarii. O inregistrare fizica este unitatea de transfer intre memoria interna si cea externa a calculatorului. Aceasta este formata din una sau mai multe inregistrari logice. O inregistrare logica este unitatea de prelucrare din punct de vedere al programului utilizator. Aceasta este formata dintr-un ansamblu de campuri, care descriu o anumita entitate.

Modul de stocare a datelor pe suportul fizic de memorare este functie de sistemul de gestiune a bazelor de date utilizat.

Proiectarea fizica a bazelor de date si a fisierelor isi propune sa asigure trecerea de la descrierea logica a datelor la una tehnica, de stocare a datelor. O problema de importanta majora in cadrul acestei etape o constituie alegerea Sistemului de Gestiune a Bazelor de Date adecvat solutionarii optime a problemelor formulate in etapele anterioare ale realizarii sistemului informatic.

1. Obiectivele fundamentale ale unei baze de date (BD) sunt:

Centralizarea datelor permite: suprimarea redundantei, asigurarea unicitatii inregistrarii si controlul centralizat (asupra datelor). In prelucrarea clasica in care fisierele sunt dedicate aplicatiilor, aceleasi date apar inregistrate in mai multe fisiere si in formate diferite. Acest lucru implica o utilizare ineficienta a spatiului de memorie externa, actualizarea dificila a acestor date si lizibilitate redusa ca urmare a formatelor diferite.

Independenta intre date si prelucrari. Baza de date, ca imagine a unei anumite realitati, trebuie actualizata permanent. Acest lucru nu trebuie sa afecteze programele de prelucrare. Pentru aceasta trebuie ca fiecare program sa aiba o viziune proprie asupra BD

Realizarea de legaturi intre entitatile de date, care sunt indispensabile pentru exploatarea eficienta a sistemului informatic. Spre exemplu, in cadrul gestiunii aprovizionarii, trebuie asociat un furnizor la lista de produse pe care le vinde si invers, un produs la lista de furnizori, precizand conditiile de vanzare pentru un furnizor si un produs.

Integritatea datelor asigura fiabilitatea si coerenta bazei de date (BD). Pentru aceasta trebuie definite restrictii de integritate cum ar fi:

apartenenta la o lista de valori sau interval;

apartenenta la un anumit format;

reguli de coerenta cu alte date.

Securitatea datelor. Baza de date trebuie sa fie protejata impotriva unei distrugeri logice (anomalie de actualizare) sau fizice. Pentru aceasta exista instrumente care permit:

crearea unor puncte de repriza; altfel spus, salvarea din timp in timp a unor copii coerente ale bazei de date;

gestiunea unui jurnal de tranzactii; lista operatiilor realizate asupra bazei de date dupa ultimul punct de repriza.

Confidentialitatea datelor este asigurata prin proceduri de:

identificare a utilizatorilor prin nume sau cod;

autentificarea prin parole;

autorizarea accesului diferentiat prin drepturi de creare, consultare modificare sau stergere pentru anumite segmente de date.

Partajarea datelor permite inlantuirea tranzactiilor solicitate simultan pe aceeasi inregistrare din baza de date, prin blocarea cererilor in asteptare si deservirea ulterioara a acestora.

2. Sistemul de Gestiune a Bazelor de Date (SGBD)

Sistemul de gestiune a bazelor de date referit prescurtat SGBD sau DBMS (Data Base Management System) este un sistem de programe care permite definirea, crearea siintretinerea bazei de date, precum si accesul controlat la baza de date. Un SGBD ofera urmatoarele facilitati pentru crearea si exploatarea bazelor de date:

facilitati de descriere a datelor, prin intermediul unui limbaj de descriere a datelor DDL (Data Description Language) care permite utilizatorului sa descrie structurile de date ce vor fi memorate in baza de date;

facilitati de manipulare a datelor, prin intermediul unui limbaj de manipulare a datelor DML (Data Manipulation Language) care permite utilizatorului sa insereze, actualizeze, stearga, sa prelucreze si sa extraga date din baza de date;

controlul accesului la baza de date prin intermediul unui limbaj de control DCL (Data Control Language) care asigura:

sistem de securitate, previne accesarea bazei de date de catre utilizatori neautorizati;

sistem de integritate, mentine concordanta datelor stocate in baza de date;

sistem de control al concurentei, permite accesul partajat la baza de date;

sistem de control al refacerii, permite recuperarea bazei de date in urma unor

defectiuni hard sau soft;

mecanism de vizualizare, prin care un utilizator poate vedea acea parte a bazei de

date care il intereseaza.

In majoritatea produselor comerciale de baze de date , cele trei limbaje se regasesc reunite in cadrul unui singur limbaj (spre exemplu limbajul SQL).

3. Administratorul bazei de date

Administratorul bazei de date referit prescurtat DBA (Data Base Administrator), este o persoana sau un grup de persoane care coordoneaza si raspunde de ansamblul activitatilor privind baza de date, incepand din faza de proiectare si continuand cu celelalte etape pe intreaga perioada de viata a bazei de date. Astfel, in faza de proiectare a bazei de date, administratorul stabileste SGBD-ul ce va fi utilizat, echipamentele necesare, structurile de date plecand de la necesitatile de informatie ale tuturor utilizatorilor bazei de date, drepturile de acces la date ale fiecarui utilizator. Rezultatul fazei de proiectare este concretizat prin elaborarea modelului conceptual (schema generala a bazei de date), modelului extern (subschema proprie fiecarui utilizator) si stabilirea modalitatilor de reprezentare a structurilor de date la nivel fizic pe suporturile de memorare externe utilizate. Drepturile de acces la baza de date pot fi definite [ORA92] fie pentru fiecare utilizator in parte, fie pentru grupuri de utilizatori (denumite Role), fiecare utilizator fiind apoi asignat unui grup. Dupa proiectarea bazei de date, administratorul va mentine permanent legatura cu utilizatorii acesteia pentru rezolvarea cerintelor utilizatorilor si impunerea unei discipline in vederea alinierii la standardele existente. Administratorul va realiza, ori de cate ori se impune, reorganizarea structurii fizice a datelor in vederea optimizarii parametrilor de functionare a intregului sistem si va stabili proceduri de arhivare a datelor si proceduri de recuperare a bazei de date la avarii si defecte. Pentru a preveni accesul neautorizat la date, in cadrul sistemului de securitate pot fi prevazute [12] si alte mecanisme si anume: evidenta de auditare, criptarea datelor.

Evidenta de auditare consta dintr-un fisier in care sistemul inregistreaza automat toate operatiile efectuate asupra datelor, fisier ce va putea fi consultat de catre persoane autorizate pentru a verifica efectuarea unor operatii neautorizate. O inregistrare din evidenta de auditare va contine urmatoarele informatii: textul sursa al operatiei neautorizate, terminalul de la care a fost lansata operatia, utilizatorul care a lansat operatia, data si ora operarii, obiectele bazei de date afectate, imaginile datelor afectate inainte de efectuarea operatiei, imaginile datelor afectate dupa efectuarea operatiei.

Pentru a preveni accesul unor intrusi la baza de date, care incearca sa ocoleasca sistemul, se utilizeaza criptarea datelor, mecanism ce consta in stocarea si transmiterea datelor pe caile de comunicatie sub forma cifrata. Criptarea se realizeaza cu ajutorul unor algoritmi de criptare printre care cel mai recent este standardul american de criptare avansat AES (Advanced Encryption Standard).

4. Proiectarea securitatii bazelor de date si a fisierelor

Securitatea este abordata din mai multe puncte de vedere, dar cea referitoare la baze de date si la fisiere presupune luarea unor masuri pentru reconstituirea datelor pierdute sau preluate eronat, precum si pentru accesul neautorizat sau incomodarea pana la a face imposibila citirea datelor, prin criptare, atunci cand ele sunt accesate ilegal. Asadar doua aspecte vor fi relevante: reconstituirea datelor si criptarea lor [1].

Reconstituirea datelor este des asociata cu existenta fisierelor de tip back-up, insa in practica este posibila si reconstituirea fara apelarea la acest tip de fisiere. In vederea controlarii corectitudinii datelor tranzactionate se apeleaza la fisiere cu rol special, care contin un istoric, in ordine cronologica, al schimbarilor si accesarilor efectuate asupra fisierelor sau bazelor de date. Cu ajutorul lor se pot reconstitui fisierele distruse, dar si la verificarea corectitudinii operatiunilor de actualizare [1].

Securitatea prin criptografiere se refera la asigurarea transformarii datelor de comunicat intr-o forma neinteligibila pentru toti ceilalti receptori, exceptandu-l pe cel autorizat. Criptarea a devenit una dintre cele mai puternice modalitati de asigurare a securitatii datelor. Ea poate fi realizata prin sistemul de operare sau prin SGBD, dar si prin rutine create de catre specialisti   [1].

Avand in vedere aspectele prezentate mai sus, criteriile avute in vedere in alegerea unui anumit tip de SGBD sunt [2]:

a) Portabilitatea SGBD-ului. Prin aceasta intelegem posibilitatea de a utiliza un SGBD de pe un sistem de calcul pe un altul. Portabilitatea cuprinde doua aspecte si anume: portabilitatea programelor propriu-zise si portabilitatea datelor.

Pentru realizarea unor programe portabile este necesar ca: programele sa contina cat mai putine elemente legate de echipament;

Portabilitatea sistemului de gestiune privit prin prisma portabilitatii datelor se refera la posibilitatea de a folosi o serie de date utilizate in cadrul unui sistem informatic de catre un alt sistem informatic, deci posibilitatea integrarii fisierelor deja existente in cadrul unui alt sistem.

b) Costul sistemului. Acest criteriu trebuie privit prin prisma: timpului de ocupare a unitatii centrale; costului de intretinere si dezvoltare; resurselor hard imobilizate; costului de adaptare si trecere pe un nou sistem de calcul; costul documentatiei etc.

c) Facilitatile de implementare, intretinere si exploatare a bazei de date. Acestea sunt reflectate prin: modalitatea de descriere a datelor; tehnicile de organizare si regasire a datelor, care sa permita un acces cat mai rapid la orice informatie; timpul cat mai redus pentru actualizare, cautare si raspuns la cererile de informare; editarea operativa a celor mai variate tipuri de situatii solicitate de catre utilizator; posibilitatea de insertie a unor programe de aplicatie, programe de validare de date, de actualizare, rutine statistice, rutine de sortare, rutine de prezentare grafica a iesirilor etc.

d) Posibilitatea gestionarii structurilor complexe de date.

e) Multitudinea metodelor de acces. In functie de cerintele proprii aplicatiei, sistemul va trebui sa suporte interogari sau actualizari in timp real avand proceduri de tip conversational.

f) Protectia si securitatea datelor din baza.

g) Specificul aplicatiei. Este cunoscut faptul ca programele sunt orientate pe aplicatii, cum ar fi: programarea productiei, aprovizionare-desfacere, optimizari, prognoze etc.

Toate aceste criterii de alegere pot fi corelate cu o serie de factori complementari cum ar fi: mentenanta sistemului, facilitatile ce le ofera administratorului bazei de date, calitatea documentatiei oferite de furnizori, asistenta in implementarea sistemului si in pregatirea utilizatorilor etc.

Toti acesti factori alaturi de criteriile enuntate pot sa influenteze succesul in implementarea SGBD-ului si eficienta economica pe ansamblul sistemului informatic.

In cele ce urmeaza se vor prezenta o serie de aspecte privind utilizarea limbajului SQL pentru crearea bazei de date, definirea utilizatorilor si acordarea drepturilor de acces, definirea interogarilor bazei de date, precum si exemple practice sub SGBD ORACLE.

5. Limbajul SQL - Crearea, Administrarea si Interogarea bazelor de date relationale

Limbajul SQL (Structured Query Language)– a fost realizat in cadrul firmei IBM ca limbaj de interogare al SGBD System R si ulterior a devenit unul din cele mai raspandite limbaje pentru SGBD-urile relationale. Limbajul SQL, ca limbaj de interogare a bazelor de date relationale, este construit pe baza a doua formalisme abstracte enuntate in cele ce urmeaza.

Eseu: Imobilizarile corporale - definiții, concepte, principii și clasificari privind imobilizarile corporale Eseu: Concluzii si propuneri cu privire la interfata subsistemului cu utilizatorul

1. Algebra relationala – prin care interogarile sunt exprimate prin aplicarea unor operatori unari sau binari care constituie primitive ce actioneaza asupra relatiilor, rezultatul interogarilor fiind tot relatii, ceea ce permite asocierea si imbricarea acestor operatori pentru a forma interogari complexe. Operatorii algebrei relationale se impart in doua grupe si anume:

- operatii pe multimi (Reuniunea, Intersectia, Diferenta, Produsul cartezian);

- operatori relationali speciali (Selectia, Proiectia, Cuplarea (JOIN), Diviziunea).

2. Calculul relational – prin care interogarile descriu multimea tuplelor rezultat prin specificarea unui predicat (conditie) care trebuie satisfacut de aceste tuple.

Incepand din 1986 limbajul SQL a devenit standard ANSI pentru limbajele de interogare ale bazelor de date relationale fiind utilizat atat in cadrul unor SGBD-uri complexe cum ar fi SGBD ORACLE (liderul mondial in domeniul bazelor de date), cat si in cadrul unor SGBD-uri de complexitate redusa cum ar fi cele din familia xBase (Dbase IV, FoxPro).

Standardul SQL utilizat pina la inceputul anului 2000 este cel realizat in 1992 si cunoscut sub numele de SQL’92 sau SQL2.

Noul standard SQL3 lansat in 1999 are in vedere o serie de extensii fata de SQL2 dupa cum urmeaza:

facilitati orientate obiect – posibilitatea de definire de catre utilizator a tipurilor abstracte de date care sa permita descrierea de metode, identitatea obiectelor, subtipuri si mostenire, polimorfism etc.;

structuri de control – pentru a conferi limbajului completitudine de calcul (IF, FOR, WHILE, etc.) pentru a deveni un limbaj de sine statator a carui putere de expresie sa nu mai fie limitata la nivelul limbajelor relationale;

facilitati pentru exprimarea prelucrarilor recursive;

facilitati de comunicare in retea;

facilitati de prelucrare distribuita (mecanisme pentru crearea, memorarea si executia procedurilor la nivelul serverelor de date –stored procedures);

facilitati multimedia;

facilitati pentru tratarea timpului in bazele de date.

Comenzi pentru crearea/actualizarea schemei bazei de date

Crearea unui utilizator se realizeaza cu comanda

CREATE USER <nume utilizator> IDENTIFIED BY <parola>

Adaugarea relatiilor intr-o baza de date –comanda CREATE TABLE are sintaxa:

CREATE TABLE <nume relatie>[(<nume atribut> <tip data>,…)]

Exemplu -crearea tabelei Persoane in SQL Oracle se realizeaza cu comanda:

CREATE TABLE Persoane (Nrcrt NUMBER UNIQUE NOT NULL,Nume CHAR(15),Prenume CHAR(15),Datan DATE,Sexul CHAR,Adresa VARCHAR2(50));

O noua relatie poate fi creata si ca rezultat al unei operatii de interogare astfel:

CREATE TABLE <nume relatie> (<nume atribut> <tip data>,…) AS <subinterogare>

Adaugarea/modificarea de atribute pentru o relatie existenta se realizeaza cu comanda:

ALTER TABLE <nume relatie> ADD|MODIFY (< nume atribut> <tip data>,…)

Stergerea unei relatii se realizeaza cu comanda:

DROP TABLE <nume relatie>

Comenzi pentru optimizarea interogarilor

Una din principalele cai de optimizare a timpilor de interogare a unei baze de date este indexarea. Un index poate fi privit ca o relatie cu doua atribute si anume:

primul atribut contine valorile atributelor relatiei dupa care se creaza indexul;

al doilea atribut contine un pointer (adresa) la locatia tuplelor corespunzatoare.

Crearea unui index se realizeaza cu comanda:

CREATE [UNIQUE] INDEX <nume index>

ON <nume relatie>(<nume atribut>[ASC|DESC],…)

Daca pentru atributele utilizate in clauza WHERE a unor instructiuni SQL au fost creati indecsi, atunci acestia vor fi utilizati in vederea optimizarii timpului de prelucrare. Decizia de utilizare sau nu a unui index este luata de limbajul SQL si nu de utilizator. Pentru aceasta fiecare model de limbaj SQL dispune de o componenta numita optimizator, care examineaza interogarea si decide care este modul optim de obtinere a rezultatului.

O alta tehnica de optimizare a interogarilor este tehnica “clustering” disponibila in ORACLE si care consta in gruparea tuplelor din mai multe relatii si stocarea lor in aceeasi zona pe disc.

Controlul datelor (comenzi DCL)

Vederi

O vedere este o relatie virtuala, definita plecand de la alte relatii din baza de date si care nu contine date si deci nu ocupa spatiu fizic pe disc. Vederile se definesc in doua scopuri si anume:

pentru a simplifica accesul utilizatorilor la date;

pentru a asigura protectia si securitatea datelor –fiecarui utilizator fiindu-i permis acces la o portiune a bazei de date si putand efectua doar anumite operatii (conform drepturilor de acces specificate cu comenzile GRANT/REVOKE).

Asupra unei vederi se pot efectua aceleasi operatii ca si asupra unei relatii cu deosebirea ca vederile nu contin date si ca orice modificari efectuate asupra datelor sunt reflectate si in vederi. Astfel, asupra unei vederi se pot realiza operatiile:

-creare vedere (CREATE VIEW);

creare sinonim pentru vedere (CREATE SYNONIM);

stergere vedere (DROP VIEW);

interogare vedere (SELECT);

actualizare date din vedere (UPDATE);

stergere date din vedere (DELETE);

adaugare date (INSERT).

Crearea unei vederi – se realizeaza cu comanda CREATE VIEW care are sintaxa:

CREATE VIEW <nume vedere> [<lista atribute>]

AS <fraza SELECT> [WITH CHECK OPTION]

Exemplu:

CREATE VIEW StocuriD1(Codp,Denp,Ump,Cant,Pret,Valoare)

AS SELECT Stocuri.Codp, Denp,Ump,Cant,Pret,Cant*Pret FROM Produse,Stocuri

WHERE Produse.codp=Stocuri.Codp AND CodDep = ”D1”

Interogarea vederii se va realiza cu comanda

SELECT * FROM StocuriD1

Utilizarea optiunii WITH CHECK OPTION asigura faptul ca nici o tupla nu va fi adaugata sau actualizata cu instructiunile INSERT, UPDATE, daca nu sunt respectate conditiile specificate in clauza WHERE a instructiunii SELECT din definitia vederii.

Pentru acordarea sau retragerea drepturilor de acces la baza de date prin intermediul vizualizarilor se vor folosi comenzi de forma:

GRANT [ALL|SELECT|INSERT|UPDATE|DELETE] ON <nume vedere>

TO <nume utilizator>

sau

REVOKE [ALL|SELECT|INSERT|UPDATE|DELETE] ON <nume vedere>

FROM <nume utilizator>

Asigurarea securitatii datelor presupune definirea drepturilor de acces ale utilizatorilor si protectia sistemului la accesul neautorizat. In acest sens asigurarea securitatii se realizeaza pe doua niveluri si anume:

-nivelul 1 – acordarea dreptului de acces la sistem;

nivelul 2 – acordarea dreptului de acces la nivel de relatii.

Pentru conectarea utilizatorilor la sistem in majoritatea versiunilor de SQL se utilizeaza un nume de utilizator si o parola.

Referitor la drepturile de acces la nivel de relatie in sistemele multi-user trebuie precizat utilizatorul care a creat relatia (proprietarul relatiei). Fiecare utilizator are drepturi doar asupra propriilor relatii, iar drepturi asupra unor relatii create de alti utilizatori pot fi acordate prin comanda GRANT si pot fi retrase prin comanda REVOKE.

Datele privind definirea bazei de date, utilizatorii si drepturile de acces sunt stocate in dictionarul de date si sunt gestionate de catre sistemul de gestiune a bazei de date (SGBDR).

In cele ce urmeaza se va prezenta modul de realizare a celor doua nivele de securitate in cadrul sistemului ORACLE.

Nivelul 1 de securitate a datelor se realizeaza cu comanda:

GRANT <autorizare,…> TO <nume utilizator> [IDENTIFIED BY <parola>]

unde <autorizare> poate fi:

DBA – confera utilizatorului dreptul de efectuare a oricarei operatii asupra oricarei relatii din baza de date;

CONNECT – confera utilizatorului dreptul de a a face interogari (SELECT) si actualizari (INSERT, UPDATE, DELETE) asupra relatiilor create de alti utilizatori, insa nu permite utilizatorului sa creeze relatii (CREATE) sau sa stearga relatii create de alti utilizatori (DROP);

RESOURCE – confera utilizatorului drepturile ce rezulta din autorizarea CONNECT si in plus dreptul de a crea relatii (CREATE) si de a sterge relatii ce ii apartin (DROP).

Unui utilizator ii pot fi acordate mai multe tipuri de autorizari in cadrul unei singure comenzi GRANT.

Parola stabilita pentru un utilizator poate fi modificata printr-o comanda GRANT ulterioara spre exemplu astfel:

GRANT RESOURCE TO <nume utilizator> IDENTIFIED BY <noua parola>

Unui utilizator caruia i s-a acordat un tip de autorizare ii pot fi acordate si alte tipuri de autorizare prin comenzi GRANT ulterioare.

Retragerea autorizarilor acordate unui utilizator se realizeaza cu comanda:

REVOKE <autorizare,…> FROM <nume utilizator>

Nivelul 2 de securitate a datelor

Pentru acordarea respectiv retragerea drepturilor de acces la relatii se utilizeaza comenzile GRANT respectiv REVOKE cu urmatoarea sintaxa:

GRANT ALL|<drept de acces>,… ON <nume relatie>

TO <nume utilizator>|PUBLIC [WITH GRANT OPTION]

respectiv

REVOKE ALL|<drept de acces>,… ON <nume relatie> FROM <nume utilizator>|PUBLIC

Privilegiile (drepturile de acces) pot fi acordate sau retrase de urmatoarele categorii de utilizatori:

utilizatorii cu nivel de autorizare DBA;

proprietarii relatiilor;

utilizatorii autorizati cu optiunea WITH GRANT OPTION.

Prin specificarea PUBLIC acordarea respectiv retragerea drepturilor de acces se aplica tuturor utilizatorilor.

Prin specificarea WITH GRANT OPTION, utilizatorul respectiv poate la randul sau sa acorde aceleasi drepturi sau mai putine altor utilizatori.

In ORACLE pot fi acordate urmatoarele drepturi de access asupra relatiilor:

SELECT, INSERT, DELETE, ALTER, UPDATE, CREATE,DROP pentru tabele si indecsi.

Drepturile de acces pot fi acordate asupra intregii relatii, sau doar asupra anumitor atribute ale relatiei.

Exemple:

Acordarea tuturor drepturilor de acces utilizatorilor Ionescu, Popescu, asupra relatiei Persoane care apartine utilizatorului Vasilescu se realizeaza prin comanda:

GRANT ALL ON Vasilescu.Persoane TO Ionescu,Popescu

Acordarea tuturor utilizatorilor, drepturile SLECT,INSERT,UPDATE asupra relatiei Produse apartinand utilizatorului Ionescu se realizeaza cu comanda:

GRANT SELECT,INSERT,UPDATE ON Ionescu.Produse TO PUBLIC

Acordarea privilegiilor SELECT,UPDATE numai asupra atributelor CodP, Denp din relatia Produse apartinand utilizatorului Ionescu, utilizatorului Popescu cu conditia ca acesta la randul sau sa poata acorda oricarui alt utilizator aceleasi drepturi sau mai putine, se realizeaza cu comanda:

GRANT SELECT,UPDATE ON Ionescu.Produse(CodProdus,Denumire)

TO Popescu WITH GRANT OPTION

Retragerea drepturilor de acces INSERT,DELETE asupra relatiei Persoane apartinand utilizatorului Vasilescu, utilizatorului Ionescu se realizeaza cu comanda:

REVOKE INSERT,DELETE ON Vasilescu.Persoane FROM Ionescu

Instructiuni pentru inserarea si actualizarea datelor in tabele

Inserarea datelor – comanda INSERT are urmatoarea sintaxa:

INSERT INTO <nume relatie>|<nume vedere> [(<nume atribut>…)]

[VALUES] <lista valori>|<subinterogare>

Exemple:

Fie tabela Persoane(Nrcrt,Nume,Prenume, Datan, Sexul, Adresa)

INSERT INTO Persoane VALUES (1,’Ionescu’,’Ion’,05/23/82,’M’,’Suceava’)

(adauga o inregistrare in tabela Persoane completand toate atributele)

INSERT INTO Persoane(Nrcrt,Nume,Prenume) VALUES (2,’Ionescu’,’Ana’)

(adauga o inregistrare in Persoane completand numai atributele Nrcrt,Nume, Prenume)

Pentru a insera in tabela PersF(Nrcrt,Nume,Prenume) toate inregistrarile din tabela Persoane pentru care Sexul=’F’ se scrie comanda:

INSERT INTO PersF(Nrcrt,Nume,Prenume) SELECT Nrcrt,Nume,Prenume

FROM Persoane WHERE Sexul = ‘F’

Actualizarea datelor – comanda UPDATE are sintaxa:

UPDATE <nume relatie>|<nume vedere>

SET <nume atribut> = <expresie>,…[WHERE <conditie>]

Conditia din clauza WHERE defineste tuplele care vor face obiectul actualizarii. Clauza WHERE poate contine si o subinterogare.

Exemple:

UPDATE Persoane SET Nume = ‘Popescu’, Prenume = ‘Ana Maria’

WHERE Nume = ‘Ionescu’ AND Prenume = ‘Ana’

(actualizeaza numele si prenumele persoanei Ionescu Ana cu valorile Popescu respectiv Ana Maria).

UPDATE Vanzari SET Pret = Pret*1.2 WHERE CodP IN

(SELECT CodP FROM Facturi WHERE Numar = 120 AND Vanzari.Codc=Facturi.Codc )

(realizeaza majorarea pretului cu 20% pentru produsele vandute cu factura 120).

Daca in comanda UPDATE clauza WHERE este omisa, actualizarea se va efectua asupra tuturor tuplelor relatiei.

Stergerea datelor – comanda DELETE are sintaxa:

DELETE FROM <nume relatie>|<nume vedere> [WHERE <conditie>]

unde <conditie> poate fi o conditie simpla, o expresie sau o subinterogare.

Exemple:

DELETE FROM Stocuri WHERE Cant = 0

(sterge toate inregistrarile din tabela Stocuri pentru care campul Cant are valoarea 0).

DELETE Oferte

(sterge toate inregistrarile din tabela Oferte).

Comenzi pentru gestiunea tranzactiilor

Tranzactia este o succesiune de instructiuni SQL grupate intr-un bloc de instructiuni utilizate pentru actualizarea si/sau interogarea datelor din baza de date. O tranzactie se considera incheiata dupa realizarea tuturor operatiilor pe care le contine. Operatiile continute intr-o tranzactie pot fi realizate efectiv in baza de date sau nu, fie automat de catre sistem dupa fiecare operatie, fie printr-o comanda explicita data dupa o succesiune de operatii. Astfel salvarea automata de catre sistem a modificarilor este realizata prin comanda

SET AUTOCOMMIT ON

Daca initial a fost specificata comanda SET AUTOCOMMIT OFF, salvarea modificarilor efectuate asupra datelor se realizeaza prin comanda COMMIT, iar abandonarea modificarilor se realizeaza prin comanda ROLLBACK.

Blocul de operatii ce definesc o tranzactie poate fi delimitat de instructiunile :

BEGIN TRANSACTION

END TRANSACTION

Problema rezolvata

Se lanseaza in executie SQL Plus Oracle sub utilizatorul system (figura 5.1).

In baza de date ORCL sub S.G.B.D. Oracle se creaza utilizatorul U1 identificat prin parola PW1 si i se acorda privilegiile CONNECT, RESOURCE (figura 5.2).

Se inchide sesiunea de lucru SQL Plus a utilizatorului system (cu instructiunea EXIT) si se deschide o noua sesiune de lucru SQL Plus pentru utilizatorul U1 (figura 5.3).

Se creaza tabela Produse si se insereaza doua inregistrari (figura 5.4).

Limbajul SQL - Interogarea bazelor de date - Fraza SELECT           

Interogarea bazelor de date in limbajul SQL se realizeaza cu ajutorul unei singure instructiuni si anume instructiunea SELECT avand urmatoarea sintaxa:

SELECT [DISTINCT] <lista atribute>|*

FROM <lista relatii>

[WHERE <conditie>]

[GROUP BY <lista atribute de grupare>]

[HAVING <conditie>]

[ORDER BY <atribut1 de ordonare> [ASC]|DESC,…]

[UNION <fraza SELECT>]

<lista atribute> este o lista ce contine nume de atribute (campuri) sau expresii construite utilizand atribute, separate prin caracterul “,” si care fac parte din relatiile (tabele, vederi) enumerate in <lista relatii> din clauza FROM. Numele fiecarui atribut sau expresii din <lista atribute> va fi afisat in capul de tabel ce reprezinta rezultatul interogarii, fiecare atribut sau expresie putand primi un alias folosind specificarea AS <alias>.

Caracterul ‘*’ specifica faptul ca se extrag toate atributele tabelei precizate in clauza FROM.

Clauza DISTINCT precizeaza faptul ca in relatia rezultat nu pot aparea duplicate (tuple identice).

Clauza WHERE precizeaza conditiile de interogare (conditii care trebuie sa fie satisfacute de tuplele interogate, conditii de cuplare relatii (JOIN, relatii intre tabele). In clauza WHERE pot fi utilizati operatori logici (AND, NOT, OR), predicate (IN, LIKE, BETWEEN, EXISTS, ALL, ANY), operatori aritmetici (+, -, **, /, *), operatori de comparare (=, #,<, >, <=, >=, <>), parantezele ( ) pentru schimbarea ordinii de prioritate a operatiilor, operatorilor, functii si alte subinterogari SELECT, pentru construirea de expresii pe care trebuie sa le indeplineasca tuplele ce constituie rezultatul interogarii. Predicatul IN permite specificarea unei liste pentru domeniul de cautare pentru un atribut, iar predicatul BETWEEN permite specificarea unui interval pentru domeniul de cautare a valorilor unui atribut, fiind echivalent cu o conditie de forma:

<atribut> >= <limita inf. interval> AND <atribut> <= <limita sup. interval>

Exemple:

Fie tabela Persoane(Nrcrt,Nume,Prenume, Datan, Sexul, Adresa)

Selectarea tuturor inregistrarilor din tabela Persoane pentru care primele 7 caractere din campul Adresa sunt ‘Suceava’ sau ‘Radauti’ se realizeaza cu comanda:

SELECT * FROM Persoane

WHERE SUBSTR(Adresa,1,7) IN (‘Suceava’,‘Radauti’)

Interogarea de mai sus este echivalenta cu interogarea:

SELECT * FROM Persoane

WHERE SUBSTR(Adresa,1,7) = ‘Suceava’       OR SUBSTR(Adresa,1,7) = ‘Radauti’

Selectarea tuturor inregistrarilor din tabela Persoane pentru care data nasterii este cuprinsa intre 01/01/72 si 01/01/82 se realizeaza astfel:

SELECT * FROM Persoane WHERE Datan BETWEEN AND

Interogarea de mai sus este echivalenta cu interogarea:

SELECT * FROM Persoane WHERE Datan >= AND Datan <=

Predicatul LIKE permite selectia sirurilor de caractere care contin anumite caractere specificate prin intermediul unei masti definite cu ajutorul unor caractere speciale (%, _ in dBASE IV, FoxPro, ORACLE, sau *, ? in INFORMIX)

Exemple:

SELECT * FROM Persoane WHERE Nume LIKE ‘%a’

(selecteaza toate inregistrarile din tabela Persoane pentru care valorile atributului Nume se termina cu litera ‘a’).

SELECT Nume,Prenume,Datan FROM Persoane WHERE Nume LIKE ‘A%u’

(selecteaza valorile atributelor Nume, Prenume, Datan pentru toate inregistrarile din tabela Persoane pentru care prima litera din Nume este ‘A’ iar ultima litera este ‘u’).

SELECT Nume FROM Persoane WHERE Nume LIKE ‘_o%’

(selecteaza valorile atributului Nume pentru toate inregistrarile din tabela Persoane pentru care prima litera din Nume este orice litera, a doua litera din Nume este litera ‘o’ si incepand din pozitia a treia numele poate contine orice litere.)

Predicatele ALL, ANY, EXISTS se utilizeaza pentru interogari ce contin subinterogari, in vederea verificarii anumitor conditii ce trebuie indeplinite intre rezultatele interogarii si rezultatele subinterogarii.

Clauza GROUP BY – realizeaza gruparea tuplelor unei relatii pe baza valorilor unui atribut sau grup de atribute si genereaza o singura tupla pentru fiecare grup de tuple avand aceeasi valoare pentru atributele care definesc grupul. Atributele care definesc grupul trebuie obligatoriu sa se regaseasca in lista atributelor interogate <lista atribute>.

De asemenea asupra unor atribute pot fi aplicate functii agregat:

AVG(<atribut>) – media valorilor atributului specificat ca parametru, pe grup;

SUM(<atribut>) – suma valorilor atributului specificat ca parametru, pe grup;

MAX(<atribut>) – maximum valorilor atributului specificat ca parametru, pe grup;

MIN(<atribut>) – minimum valorilor atributului specificat ca parametru, pe grup;

COUNT(<atribut>) – numarul inregistrarilor pe grupare dupa <atribut>.

Observatie. <atribut> poate fi fie un atribut, fie o expresie definita utilizand atribute ale tabelei.

Clauza HAVING, optiune a clauzei GROUP BY, este o forma speciala a clauzei WHERE intrucat se aplica unor grupuri de tuple (si nu unor tuple) definite de clauza GROUP BY.

Exemple:

Fie tabela Stocuri(CodDep,CodP,UmP,Cant,Pret)

SELECT CodDep,SUM(Cant*Pret) AS Valoare,COUNT(CodDep) AS Contor

FROM Stocuri GROUP BY CodDep

(Calculeaza suma produselor Cant*Pret pentru toate tuplele avand aceeasi valoare in campul CodDep si numarul inregistrarilor din fiecare grup definit de campul CodDep si afiseaza rezultatele sub forma de tabel avand coloanele CodDep, Valoare, Contor)

SELECT CodDep,CodP,MAX(Pret) FROM Stocuri

GROUP BY CodP HAVING MAX(Pret) < 150000

(selecteaza pentru fiecare grupa de inregistrari avand aceeasi valoare in campul CodP, inregistrarea cu pretul maxim mai mic decat 150000)

Clauza ORDER BY permite precizarea ordinii de afisare a datelor astfel:

ORDER BY <nume atribut 1> [ASC]|DESC,<nume atribut 2>[ASC]|DESC,…

Exemplu:

SELECT * FROM Persoane ORDER BY Datan DESC,Nume

(afiseaza toate inregistrarile din tabela Persoane in ordine descrescatoare dupa data nasterii si in cadrul aceleiasi date a nasterii crescator dupa Nume)

Clauza UNION – permite obtinerea rezultatului a doua sau mai multe interogari printr-o singura instructiune SELECT.

Exemplu:

SELECT CodDep,CodP,Cant FROM Stoc_Prod WHERE CodDep = ‘Dep01’

UNION

SELECT CodDep,CodP,Cant FROM Stoc_Prod WHERE Cant >= 100

selecteaza tuplele (CodDep,CodProd,Cant) din tabela Stoc_Prod pentru toate inregistrarile pentru care CodDep = ‘Dep01’, la care adauga tuplele (CodDep,CodProd,Cant) din tabela Stoc_Prod pentru toate inregistrarile pentru care Cant >= 100).

Pentru a nu se elimina tuplele duplicat trebuie specificat UNION ALL.

Pentru a schimba ordinea de afisare a tuplelor extrase se poate utiliza clauza ORDER BY aplicata doar relatiei finale si nu asupra fiecarei fraze SELECT.

Regasirea datelor din doua sau mai multe relatii

Interogarea datelor din doua sau mai multe tabele (relatii) presupune existenta unor campuri comune pentru realizarea operatiei de cuplare (operatorul JOIN). In fraza SELECT operatia de cuplare este definita in clauza WHERE sub forma:

<nume tabela1>.<cheie1> = <nume tabela2>.<cheie2>

(unde <cheie1>, <cheie2> reprezinta campurile ce identifica inregistrarile corespondente in cele doua tabele).

Pentru exemplificare pe langa tabela Stocuri mai consideram tabela Produse(CodP, DenP, DesP).

SELECT Produse.CodP,DenP,UmP,Cant,Pret FROM Produse,Stocuri

WHERE Produse.CodP = Stocuri.CodP

(extrage toate tuplele (CodP,DenP,UmP,Cant,Pret) pentru care valoarea atributului CodP din tabela Produse este egala cu valoarea atributului CodP din tabela Stocuri ).

In lipsa clauzei WHERE se vor extrage toate combinatiile posibile intre tuplele celor doua tabele (produsul cartezian).

Fiecarei tabele i se poate atribui un alias astfel incat fraza de mai sus este echivalenta cu fraza:

SELECT A.CodP,DenP,UmP,Cant,Pret FROM Produse A,Stocuri B WHERE A.CodP = B.CodP

In anumite situatii poate fi necesara corelarea (cuplarea) unei relatii (tabele) cu ea insasi. Spre exemplu daca presupunem ca in tabela Stocuri unele produse pot apare de mai multe ori cu preturi diferite si ne intereseaza pozitiile cu pretul minim, formulam urmatoarea interogare:

SELECT A.CodP,A.Cant,A.Pret FROM Stocuri A

WHERE A.Pret = (SELECT MIN(B.Pret) FROM Stocuri B WHERE A.CodP = B.CodP)

Pentru rezolvarea unor astfel de probleme s-au utilizat instructiuni SELECT imbricate care vor fi prezentate in detaliu in cele ce urmeaza.

Instructiuni SELECT imbricate

Limbajul SQL ofera posibilitatea construirii unor interogari complexe prin includerea in clauza WHERE a unei instructiuni SELECT, a altei instructiuni SELECT (numita sub-interogare sau ‘inner’) astfel:

SELECT <lista atribute> FROM <lista relatii>

WHERE <conditie> (<sub-interogare>)

La randul ei sub-interogarea poate contine in clauza WHERE o alta instructiune SELECT obtinand astfel o interogare complexa constituita din instructiuni SELECT imbricate pe un numar oarecare de nivele. Instructiunea SELECT interioara genereaza valori pentru conditia de cautare a instructiunii SELECT exterioare care o contine (numita si ‘outer’). O sub-interogare poate returna o singura valoare, sau poate returna mai multe valori.

In ce priveste ordinea de evaluare a interogarilor pot exista :

sub-interogari simple - in care interogarea interioara este evaluata prima, independent de interogarea exterioara, iar rezultatul interogarii interioare este utilizat de interogarea exterioara;

sub-interogari corelate - in care interogarea exterioara transmite repetat cate o valoare pentru interogarea interioara, care in baza valorii primite, parcurge tuplele relatiei si transmite interogarii exterioare rezultatul obtinut. Astfel de interogari realizeaza corelarea unei relatii cu ea insasi si sunt cele mai performante.

Spre exemplu daca presupunem ca in tabela Stocuri unele produse pot apare de mai multe ori cu preturi diferite si ne intereseaza pozitiile cu pretul minim, formulam urmatoarea interogare:

SELECT A.CodP,A.Cant,A.Pret FROM Stocuri A

WHERE A.Pret = (SELECT MIN(B.Pret) FROM Stocuri B WHERE A.CodP = B.CodP)

Sub-interogari simple care returneaza o singura valoare - pot fi utilizate in interogari imbricate avand sintaxa:

SELECT <lista atribute> FROM <lista relatii>

WHERE <atribut> =

<

>

<=

>=

!=

(<sub-interogare>)

[ORDER BY <atribut[ASC]|DESC,…]

Exemplu:

SELECT CodDep,CodP,Cant FROM Stocuri

WHERE Cant > (SELECT AVG(Cant) FROM Stocuri ) ORDER BY CodDep

(afiseaza produsele pentru care exista stocuri peste medie, ordonate pe depozite).

Sub-interogari simple care returneaza mai multe valori pot fi utilizate in interogari imbricata care utilizeaza in clauza WHERE coditii care genereaza o multime de valori folosind unul din predicatele: (NOT)IN, (NOT)ANY, (NOT)ALL, (NOT)EXISTS.

Exemplu:

SELECT * FROM Produse WHERE CodP IN (SELECT CodP FROM Facturi WHERE Numar IN

(SELECT Numar FROM Beneficiari,ComenziWHERE Beneficiari.Nume=’Ionescu’ AND

Beneficiari.Cod_Beneficiar=Comenzi.Cod_Beneficiar))

Predicatul ANY poate fi utilizat in combinatie cu oricare din operatorii <, >, =, <=, >=, != si permite verificarea daca valoarea unui atribut satisface conditia precizata pentru orice valoare din lista rezultata din subinterogare.

SELECT CodP FROM Stocuri WHERE Cant > ANY

(SELECT Cant FROM Stocuri WHERE CodDep = “D1”)

Predicatul ALL returneaza toate tuplele pentru care valorile atributului din clauza WHERE sunt <, >, <=, >= decat toate valorile generate de interogarea interioara (acest predicat nu poate fi utilizat cu operatorul = ce ar corespunde cazului banal in care toate interogarile din lista sunt egale).

Exemplu:

SELECT * FROM Stocuri WHERE Cant < ALL

(SELECT Cant FROM Stocuri WHERE CodDep = “D1”)

Predicatul EXISTS verifica daca pentru fiecare tupla a relatiei exista tuple care satisfac conditia din interogarea interioara (deci EXISTS permite specificarea mai multor atribute in interogarea interioara).

Astfel spre exemplu instructiunea:

SELECT * FROM Produse A WHERE NOT EXISTS

(SELECT * FROM Stocuri B WHERE B.CodP=A.CodP)

va returna o lista de produse care nu au nici o inregistrare in Stocuri.