Curs 14-Integritatea si securitatea datelorEste asigurata prin: -integritatea semantica (ce presupune ca informatiile stocate sa corespunda realitatii). -sincronizarea accesului concurent la BD ceea ce presupune asigurarea ca actiunile concurente ale unor utilizatori nu aduc prejudicii altora. -siguranta in functionare, in cazul unor defectiuni fizice sau a unei pane de curent. -asigurarea securitatii in utilizare, prin controlul si limitarea accesului utilizatorilor.Integritatea semantica se realizeaza prin respectarea restrictiilor de integritate (reguli suplimentare pe care trebuie sa le respecte datele). A. Restrictii de integritate statice: A.1Restrictii de integritate specifice modelului relationalRestrictii chei primare:Campul (grupul de campuri) care indeplineste rolul de cheie primara intr-o relatie, va avea intotdeauna valori unice si nenule.Integritatea referentiala: o realizare a cheii externe se va regasi in multimea realizarilor cheii primare corespondente, sau va fi nula.Integritatea referentiala se poate implementa: a)In momentul crearii legaturilor intre tabele prin optiunea Enforce Referential Integrity din fereastra Edit Relationships. b)prin intermediul limbajului SQL c) prin intermediul metodei CreateRelation a obiectului Database

A.2 Restrictii asupra valorilor unui camp:Pot fi:-restrictii de date implementate prin prop. Validation Rule a campului, sau a controlului, prin prop Indexed, Required sau prin intermediul SQLEx: Localitate IN (Iasi, Ploiesti, Arad) -restrictii de tip (sau de format): nu vor fi acceptate valori care nu apartin tipului de data al campului.A.3 Restrictii asupra valorilor mai multor campuri ce provin din aceeasi relatie: sunt implementate prin prop. Validation RULE la nivel de tabela, sau la nivelul unui control din formular, sau prin utilizarea evenimentului Beforeupdate la nivelul unui formular.Ex: CONT (Simbol, Denumire, Tip Cont, SoldInitial debitor, SoldInitialCreditor)Daca SoldInitial debitor0 atunci SoldInitialCreditor=0

A.4.Restrictii asupra valorilor mai multor campuri provenind din mai multe relatii se implementeaz prin prop. Validation Rule a controalelor de tip TextBox,Combobox; ListBoxFie tabelele Cerere (Nrcerere, DataCererii) Contracte(Nrcontract, DataIncheierii, Valoare, Nrcerere) R.I.:DataCerere

Conceptul de tranzactie si de stare coerenta a bazei de date

Tranzacia reprezint o secven liniar de operaii executate asupra unei baze de date partajate de mai muli utilizatori. Tranzaciile se folosesc, de obicei, ntr-o procedur n care se execut mai multe operaii de actualizare a bazei de date i se dorete garantarea faptului c toate operaiile s-au executat cu succes. n cazul apariiei unei erori la una din actualizri toate cele anterioare se pot anula.

O tranzacie poate fi:Salvat (commited)-n situaia n care toate operaiile tranzaciei au fost ncheiate cu succes, iar baza de date a fost actualizat.Derulat napoi (rollback)-n situaia n care toate operaiile tranzacionate au fost anulate, iar baza de date a fost restaurat la starea dinaintea tranzaciei.

Cnd o tranzacie actualizeaz mai multe nregistrriaparinnd uneia sau mai multor tabele, o aplicaie informatictrebuie s se asigure c toate nregistrrile au fost actualizatesau c toate au rmas n starea iniial.

Sincronizarea accesului concurent consta in faptul ca intr-un mediu multiuser, datele actualizate prin intermediul unei tranzactii nu sunt vizibile altor utilizatori decat numai la salvarea tranzactiei.Access implementeaza doua metode de blocare a inregistrarilor, pentru asigurarea sincronizarii.-Blocare optimista:prin blocarea paginii cu inregistrari pe perioada salvarii, inregistrarea devenind read-only ptr. ceilalti utilizatori .-Blocare pesimista:prin blocarea paginii cu inregistrari, in timpul editarii acesteia de catre un utilizator. Nu se pot efectua modificari pana cand nu este salvata de utilizatorul curent.

Siguranta in functionare: prin tehnica jurnalizarii. La intervale regulate de timp sunt facute copii de siguranta ale bazei de date.

n cazul unei erori intervenite n funcionarea echipamentelor sau a programelor (eroare local a unei tranzacii, eroare a sistemului de exploatare, etc.) S.G.B.D-ul trebuie s asigure coerena bazei de date. n situaia n care pana survine n timpul efecturii unei tranzacii de actualizare a bazei de date, S.G.B.D.-ul trebuie: (1) s anuleze tranzacia dac aceasta nu a atins punctul de confirmare ce asigur coerena bazei de date sau (2) s refac baza de date, dac tranzacia a fost ncheiat cu succes. Tehnica jurnalizrii const n nregistrarea activitii tranzaciilor ntr-un jurnal gestionat de S.G.B.D., prin consemnarea evenimentelor principale care afecteaz baza de date:nceputul unei noi tranzacii;Sfritul tranzaciei (confirmarea actualizrii);Anularea unei tranzacii;Pentru conformitate se stocheaz: identificatorul tranzaciei ce efectueaz actualizarea, identificatorul nregistrrii modificate, vechea valoare, valoarea actualizat. n momentul n care sistemul reintr n funciune, S.G.B.D. identific tranzaciile confirmate nainte de pan i tranzaciile abandonate.

Securitatea utilizriiClase de tehnici care asigur securitatea utilizrii:Controlul accesului ce verific identitatea utilizatorilor i a drepturilor de acces acordate acestora;Controlul fluxurilor de date ce urmrete traseul datelor pentru a nu intra n posesia persoanelor ru intenionate;Controlul de inferen pentru a evita ca un utilizator s deduc informaii confideniale prin inferen (folosind datele la care are acces); Criptografia se ocup cu securizarea informaiei precum i cu autentificarea i restricionarea accesului la informatiile unei baze de date.

Asigurarea controlului accesului Limbajul SQL asigur controlul accesului prin comenzile GRANT i REVOKE.

Exemplu: USER1 este proprietarul relaiilor Angajat i Departament i dorete s acorde privilegiile de inserare i tergere utilizatorului USER2 asupra acestor relaii:

GRANT INSERT, DELETE ON ANGAJAT, DEPARTAMENT TO USER2;

n situaia n care USER1 dorete s revoce privilegiul de tergere asupra relaiei ANGAJAT pentru utilizatorul USER3 se va executa comanda:REVOKE DELETE ON ANGAJAT FROM USER3;

Exemplu de biletArhitectura client-server. Tipologii Operatorii de extensie si implementarea lor in SQL.Se da urmatorul dictionar de date:Cod material, Denumire material, Um, Nr bon consum, Cod gestiune, Data bon consum, Denumire gestiune, Nume gestionar, Cantitate consumata, Pret de consum, Valoare material consumat, Cod sectie, denumire sectie, Amplasament sectie, Valoare totala bon de consumReguli de gestiune:-Pe un bon de consum pot fi inregistrate mai multe materiale, un material se poate regasi pe mai multe bonuri.-Un bon este emis de o singura gestiune pentru o singura sectie.Sa se elaboreze modelul relational al bazei de date.4. Se da urmatoarea schema a bazei de date:Proprietari(Codproprietar, nume, prenume, adresa, telefon)Imobile (Codimobil, Codproprietar, codagent, localitate, zona, TipImobil, Nrcamere, adresa, pretsolicitat, pretvanzare, datavanzare)Agenti (Codagent, nume, prenume, adresa, telefon)

Sa se elaboreze urmatoarele interogari SQL:Sa se afiseze lista preturilor medii solicitate pentru garsoniere pe localitati si zone de interes.Sa se afiseze lista numarului de imobile nevandute inca pe fiecare agent in parte, ordonata descrescator.Sa se stearga din baza de date proprietarii care nu mai au imobile spre vanzareSa se reduca cu 5% pretul solicitat pentru imobilele nevandute inca, ale proprietarului Popescu Ion.

Reguli de gestiune suplimentare:RG3: Valoare material consumati=cantitate consumatai*pret de consumiRG4:Valoare bon consum=cantitate consumatai*pret de consumi

Se intocmeste dictionarul de date:

Nr crt.AtributExplicatie1.CodMCod material2.DenMDenumire material3.UmUnitate de masura4.NrBCNr bon consum5.DataBCData bon6.CodSCod sectie7.DenSDenumire sectie8.AmplasamentSamplasament9.CodGCod gestiune10.DenGDenumire gestiune11.Gestionargestionar12.CantCCantitate consumata13.PretCPret consum

Pe baza regulilor RG3 si RG4 se elimina atributele calculate valoare material consumat si valoare bon consum.Se construieste graful dependentelor functionale. CODM

CodS

NRBC

CodG

DenMUMNrBC+CodMDataBC

DenGGestionarDenS

Amplasament SCantCPretC

Matricea dependentelor functionale

1 2 3 4 5 6 7 8 9 10 11 12 13 14 1.codm 1 1 M 2. denm 3. um 4 NrBC M 1 1 1T 1T 1 1T 1T 5. dataBc 6.CodS 1 1 7.DenS 8.AmplasamentS 9 CodG 1 1 10.denG 11.gestionar 12. CantC 13.PretC 14. NRBC+CODM 1 1

Se stabilesc cheile primare si cu fiecare cheie si atributele dependente functional, netranzitiv,de aceasta, se elaboreaza o relatie. Pentru atributele care nu au determinant unic, se cauta grupuri de atribute care sa serveasca drept cheie ( e cazul atributelor cantC si pretc, pentru care s-a identificat drept determinant grupul NrBC+CodM). In ultima etapa se stabilesc cheile externe.

Materiale(codm, denm, um)Gestiuni(CodG, denG, gestionar)BonuriConsum(Nrbc, databc, Codg,cods)Sectii (Cods, DenS, AmplasamentS)DetaliiBon (NRBC,CodM,cantc, pretc)

REZOLVAREa) TRANSFORM AVG(PRET)SELECT LOCALITATEFROM IMOBILEGROUP BY LOCALITATEPIVOT ZONA;b) SELECT AGENTI.CODA, NUME, COUNT(Codimobil) as [Total imobile] FROM AGENTI INNER JOIN IMOBILE ON AGENTI.CODAGENT=IMOBILE.CODAGENT WHERE DATAVANZARE IS NULL GROUP BY AGENTI.CODAGENT, NUME ORDER BY COUNT(CODIMOBIL) DESC;C) DELETE * FROM PROPRIETARI Where Proprietari.codproprietar NOT IN (SELECT IMOBILE.CODPROPRIETAR FROM IMOBILE WHERE DATAV IS NULL);D) UPDATE Imobile SET [PRET SOLICITAT]=[PRET SOLICITAT]*0.95 WHERE IMOBILE.CODIMOBIL IN (SELECT IMOBILE.CODIMOBIL FROM proprietari INNER JOIN imobile on proprietari.codproprietar=imobile.codproprietar WHERE NUME=POPESCU AND PRENUME=Ion and Datav is null);