Sistemul de Gestiune a Bazelor de Date MS ACCESS

Cuprins

1. Introducere. Baze de date relationale. Sistemul Microsoft Access.

2. Teoria Relationala. Teoreme de normalizare. Proiectarea unei baze de date.

3. Tabele. Tipuri de date.

4. Relatii. Integritate referentiala. Metoda QBE.

5. Interogari simple utilizând SQL.

6. Operatori. Functii de biblioteca.

7. Interogari SQL avansate (I).

8. Interogari SQL avansate (II).

9. Interogari active.

10. Formulare. Importul si exportul datelor.

11. Rapoarte.

12. Sistemul de Macrocomenzi. VBA. Securitatea BD Access.

Cap. Baze de date relationale . sistemul Microsoft Access

1.1. Baze de date. Sisteme de gestiune a bazelor de date.

Prin termenul "Baza de date" se întelege un sistem software care gestioneaza stocarea, regasirea si prelucrarea unor date structurate, incluzând, pe lânga datele în sine, rutine de prelucrare a lor, sisteme de protectie, securitate etc.

Termenul "Sisteme de gestiune a unei baze de date" (SGBD-DBMS) desemneaza sistemele de dezvoltare care permit crearea si gestionarea bazelor de date.

Exemple de sisteme de gestiune a bazelor de date relationale considerate de nivel industrial:

. Oracle

. IBM DB2

. Informix

Exemple de sisteme de gestiune a bazelor de date relationale considerate de nivel desktop:

. Microsoft Access

. Microsoft FoxPro

. Borland dBase

1.2. Ce este sistemul Access?

Sistemul Microsoft Access este un sistem complex care permite atât dezvoltarea aplicatiilor mici cât si al sistemelor software industriale complexe. Avantajul sau însa este dat de posibilitatea asimilarii graduale si a unui start facil.

Microsoft Access este un sistem de gestiune a bazelor de date relational.

Ca sistem de gestiune, Microsoft Access cuprinde destul de multe facilitati:

. un sistem relational care suporta doua limbaje de interogare standard: Structured Query Language (SQL) si Query By Example (QBE);

. un limbaj de programare complet reprezentând în esenta un dialect al limbajului Visual Basic;

. un macro-limbaj procedural usor de utilizat;

. un mediu de dezvoltare rapida a aplicatiilor (RAD) cu interfata vizuala si unelte de creare automata a rapoartelor;

. o extensie orientata pe obiecte intuitiva;

. o mare varietata de asistenti (wizards) care usureaza faza de dezvoltare a aplicatiilor.

Pentru cei neavizati, aceasta multitudine de facilitati poate parea de o complexitate frustranta. Adevarul este ca fiecare dintre aceste fatete se bazeaza pe un set diferit de cerinte si perspective asupra aplicatiilor. Spre exemplu:

. caracterul relational implicat considera aplicatia ca un set de date;

. programare procedurala în VB sau macro comenzi considera aplicatia ca un set de instructiuni care vor fi executate secvential;

. elementele de orientare pe obiecte dau o perspectiva structurala asupra aplicatiei, care poate fi considerata ca o multime de obiecte care încapsuleaza stari si comportamente specifice, interactionând la momentul executiei prin schimb de mesaje.

Aceste fatete ale unei aplicatii însa nu sunt integrate logic, lucru care ar fi greu de realizat. în acest sens este lasata la latitudinea dezvoltatorului alegerea si utilizarea uneia sau alteia. El poate alege metoda ce se potriveste cel mai bine aplicatiei care o dezvolta.

Atâta timp cât exista o asa de mare varietate de metode puse la dispozitie, alegerea uneia sau alteia denota în final cunoasterea sistemului de catre dezvoltator. în plus, studierea acestui sistem va duce implicit la studierea diverselor concepte fara a fi necesara familiarizarea cu diferite unelte de dezvoltate, specifice fiecarei metode.

1.3. Fisier baza de date Access.

Toate informatiile continute de o baza de date Access sunt reunite într-un singur fisier. Pe lânga tabelele continând date, el mai contine diverse tipuri de obiecte, cum ar fi:

. interogarile pentru organizarea si regasirea datelor;

. formulare pentru interfata cu utilizatorii bazei de date;

. rapoarte pentru tiparirea rezultatelor;

. rutine macro si Visual Basic pentru extinderea functionalitatii aplicatiilor. Fisierul baza de date are extensia standard ".mdb" (ex. "contabilitate.mdb"). Când fisierul este deschis în Access, el va fi automat blocat, evitându-se pierderea datelor. Prin blocare se va crea un fisier cu acelasi nume dar cu extesia ".ldb", care însa nu contine date ci doar informatiile de blocare.

1.4. Rularea sistemului Access. Crearea unei baze de date.

Pentru rularea sistemului Microsoft Access se va apela la intrarea Access

meniul Windows Start sau icoana de pe desktop.

Pentru crearea unei baze de date noi se va apela la meniul File/New din Microsoft Access,

la butonul de pe bara de unelte (toolbar) sau la optiunea din fereastra New File. Suplimentar se va alege directorul în care se face salvarea si se va completa numele bazei de date nou create, de exemplu "baza1.mdb".

1.5. Deschiderea unei baze de date existente.

Deschiderea unei baze de date existente se poate face prin executia unui dublu click pe fisierul .mdb aferent sau, dupa pornirea sistemului Access, prin selectarea intrarii de meniu File/Open. În cazul utilizarii celei de a doua metode se va alege în prealabil directorul în care este salvata baza de date dorita.

☺ Observatie: Versiunile mai recente de Microsoft Access pot deschide baze de date create cu versiuni anterioare dar reciproca nu este valabila (compatibilitatea este doar în jos).

1.6. Gestionarea proiectului.

Pentru a putea gestiona diversele aspecte ale unei baze de date sistemul Access le organizeaza pe categorii în cadrul unei ferestre speciale prezentate în figura de mai jos.

Categoriile de clasificare a obiectelor sunt:

. Tables - tabelele continute în baza de date precum si legaturi spre tabele din alte baze de date;

. Queries - interogari pasive si active;

. Forms - formulare pentru interfata cu utilizatorii;

. Reports - rapoarte pentru tiparirea infromatiilor;

. Pages - pagini de Web pentru afisarea datelor din baza de date;

. Macros - rutine continând comenzi de tip macro pentru implementarea unor secvente automate de prelucrare a datelor;

. Modules - module de cod Visual Basic care permit implementarea unor algoritmi complecsi de prelucrare.

Pentru deschiderea unui obiect din fereastra de gestiune a bazei de date, se va selecta obiectul si se va apasa butonul Open.

Pentru stergerea sau redenumirea unui obiect se va selecta din meniul context al obiectului optiunea Delete respectiv Rename.

☺ Observatie: Meniul context al unui obiect poate fi accesat prin apasarea butonului din dreapta al mouse-ului deasupra acestuia.

1.7. Utilizarea optiunii Help.

Aproape toate aplicatiile dezvoltate recent pun la dispozitia utilizatorului nu doar manuale de utilizare tiparite ci si un sistem de informare interactiva numita generic "Online Help". si sistemul Access se înscrie în aceasta categorie. Pentru a accesa aceste informatii este suficient sa

se apese tasta F1 în contextul de interes sau sa se selecteze intrarea Microsoft Access Help din meniul Help.

Sistemul de informare poate fi utilizat si pentru învatarea unor secvente de lucru cu sistemul dar, principala lui menire este aceea de a servi drept referinta rapida în cadrul procesului de dezvoltare.

1.8.Dezvoltarea unei aplicatii Access.

În general, dezvoltarea unei aplicatii Access poate urma doua cai oarecum divergente:

. "in-depth systems analysis, design, and impiementation" - presupune analiza amanuntita a sistemului de modelat si crearea în prealabil a unui proiect complet înaintea începerii fazei de implementare;

. "rapid prototyping" - presupune combinarea iterativa a fazelor de analiza, proiectare si implementare.

Prima metoda este potrivita pentru bazele de date complexe si a sistemelor de mari dimensiuni. Pentru sisteme mici si mijlocii cea de a doua metoda da rezultate mai rapide si are costuri mai scazute. În plus, Microsoft Access pune la dispozitia dezvoltatorilor un mare numar de facilitati destinate acestei metode, cum ar fi: unelte grafice de proiectare, generatoare si instructiuni de nivel foarte înalt (macro-comenzi). Secventa de dezvoltare a aplicatiilor va fi urmatoarea:

1. Modelarea informatiilor de interes în entitati si relatii dintre acestea.

2. Crearea unei tabele pentru fiecare entitate, urmata de normalizarea tabelelor astfel rezultate.

3. Specificarea relatiilor între tabele.

4. Organizarea informatiilor prin intermediul interogarilor.

5. Crearea de formulare si rapoarte pentru tranzactiile de intrare si iesire.

6. Crearea de interogari active, macro si rutine Visual Basic pentru procesarea informatiilor.

Cap. 2. Teoria Relationala. Teoreme de Normalizare. Proiectarea unei baze de date.

1. Elemente de algebra relationala.

Principiile algebrei relationale au fost stabilite de dr. F. Codd în 1970. Ea reprezinta fundamentarea matematica a bazelor de date relationale. În conceptia relationala o baza de date este formata dintr-o colectie de relatii (tabele, fisiere de date) asupra carora se aplica o colectie de operatori pentru a gestiona datele continute de relatii.

Un operator relational se aplica asupra unor tabele si va avea ca si rezultat tot o tabela. Potrivit algebrei relationale nu este permis accesul direct asupra înregistrarilor dintr-o tabela.

În continuare se vor da câteva definitii a termenilor folositi de algebra relationala.

Constituantii (câmpuri, atribute, caracteristici) sunt informatiile elementare (atomice) ale unei relatii.

Domeniul (tipul) este ansamblul valorilor pe care îl poate lua un constituant. Domeniul este un set de valori atomice.

N-upletul este un ansamblu de constituanti (X1, X2, ..., XN) sau de date (a1, a2, ..., an) cu aiЄ dom(Xi). Un N-uplet de constituanti poate fi considerat ca si un constituant compus.

O relatie N-ara R(X) se defineste prin trei elemente:

. precizarea unui N-uplet de constituanti (X1, X2, ..., XN);

. definirea domeniului pentru fiecare constituant Xi;

. definirea unui predicat logic care pentru orice N-uplet de date (a1, a2, ..., an) cu aiЄ dom(Xi) cu i de la 1 la N da o propozitie adevarata sau falsa.

. Relatia R(X) este formata din ansamblul N-upletilor pentru care predicatul da propozitii adevarate.

. Gradul unei relatii este dat de numarul atributelor ce formeaza relatia.

Principalele caracteristici ale unei relatii sunt:

. N-upletii din relatie nu sunt ordonati.

. Ordinea valorilor în N-upleti este data de ordinea definirii atributelor în modelul relatiei.

. Valorile atributelor din N-upleti sunt atomice. Un atribut nu poate avea valori multiple. Sunt permise în schimb valori nule.

. Relatie poate fi privita ca o specificare a unui tip compus. Definitia tipului este data de structura relatiei.

O relatie este definita ca un set de N-upleti distincti (din acest punct de vedere corespunde tipului algebric multime).

Se numeste supercheie (SK) un grup de atribute care identifica în mod unic un N-uplet al relatiei. Exista relatii care au o singura supercheie formata din toate atributele.

Se numeste cheie a relatiei R o supercheie minima, cu proprietatea ca, înlocuind sau stergând orice atribut din ea, se obtine un grup de atribute care nu este supercheie pentru relatia data. Multimea cheilor unei relatii formeaza cheile candidat din care trebuie aleasa o cheie primara.

Cheia primara (PK - primary key) este o cheie aleasa de administratorul bazei de date pentru a identifica înregistrarile. De obicei pe acest post se alege o cheie cu un numar minim de atribute, daca este posibil chiar un singur atribut.

Se numeste cheie externa (FK - foreign key) un grup de atribute care constituie o cheie primara într-o alta relatie. O cheie externa ajuta la legarea datelor din cele doua relatii.

Pentru prelucrarea datelor din tabele teoria relationala defineste doua tipuri de operatori: operatori din teoria multimilor - UNION, INTERSECT, DIFERENCE, KHARTEZIAN PRODUCT, respectiv operatori specifici algebrei relationale: SELECT, PROJECT, UNION. Acesti operatori stau la baza limbajelor de interogare relationale, dintre care cel mai cunoscut este SQL.

2. Normalizarea unei baze de date relationale. Teoreme de normalizare.

Normalizarea unei baze de date consta în principal în descompunerea modelului bazei de date în mai multe relatii (tabele) astfel încât sa se reduca la maxim redundanta datelor si implicit sa elimine anomaliile de actualizare. Operatia de normalizare se bazeaza pe dependentele functionale care exista între datele unei aplicatii.

O dependenta functionala, notata X→Y, între doua seturi de atribute a unei relatii R, specifica o constrângere asupra N-upletilor posibili. Ea se defineste în felul urmator:

V t1, t2 Є R, t1(X) = t2(X) => t1(Y) = t2(Y)

Se spune în acest caz ca X determina functional pe Y sau ca Y este dependent functional de X..

Faptul ca X nu determina functional pe Z se va nota X | → Z.

Pentru determinarea dependentelor functionale se pot aplica urmatoarele reguli de inferenta (prin XY se noteaza concatenarea seturilor de atribute X si Y):

1. Regula reflexiva: X Y => X→Y

2. Regula de marire: => XZ→Y

3. Regula tranzitiva: => X→Z

4. Regula de decompozitie: => X→Y

5. Regula de reuniune: => X→YZ

6. Regula pseudotranzitiva: => WX→Z

Normalizarea bazei de date presupune aducerea relatiilor gradual pe diverse forme normale conform unor teoreme de normalizare. Fiecare forma normala preia constrângerile formei anterioare la care adauga noi conditii.

Forma normala 1 (1NF) cere:

. domeniul atributelor sa cuprinda valori atomice; se interzic câmpurile compuse sau "relatii în relatie"

. fiecare atribut din N-uplet trebuie sa aibe o singura valoare în domeniu.


. relatia sa fie în 1NF

. orice atribut neprim (care nu face parte din cheia primara) din R sa fie complet dependent functional de cheia primara a relatiei.

O alta varianta: se cere sa nu existe atribute care sa depinda numai de o parte a cheii primare.


. relatia sa fie în 2NF

. nu exista nici un atribut neprim care sa fie dependent tranzitiv de cheia primara a relatiei

Varianta: nu se permit atribute care nu fac parte din cheile candidat ale relatiei si care determina alte atribute.

Se poate da si o definitie generalizata pentru forma 3NF. Orice atribut al relatiei îndeplineste:

. este complet dependent functional de orice cheie din R;

. este dependent netranzitiv de orice cheie din R.

O varianta mai restrictiva este BCNF - Boyce Codd Normal Form. O relatie este în BCNF daca, pentru orice dependenta X→Y din R, X este o cheie candidat a lui R.

Aducerea unei baze de date pe o forma normala superioara presupune extragerea unor atribute din relatiile existente si crearea pe baza lor a unor noi relatii astfel încât rezultatul sa respecte forma normala în cauza. Acest lucru duce la fragmentarea bazei de date dar elimina din anomaliile de actualizare si reduce spatiul pierdut datorita redundantei datelor.

3. Proiectarea unei baze de date.

Concret, pasii care trebuie facuti la proiectarea unei baze de date relationale sunt urmatorii:

. Analiza aplicatiei: analiza circuitului informational, studierea intrarilor si iesirilor, stabilirea claselor de utilizatori;

. Analiza semanticii atributelor din entitati: identificarea atributelor si a sensului lor functional, gruparea atributelor în relatii pe entitati, stabilirea cheilor primare si externe;

. Normalizarea relatiilor obtinute la punctul anterior: micsorarea redundantei prin gruparea atributelor în relatii conform definitiilor pentru formele normale, stabilirea de constrângeri pentru eliminarea anomaliilor de actualizare;

. Scoaterea din relatiile principale a atributelor care au peste 70% valori nule.

☺Observatie: Daca se opteaza pe o metoda de tip rapid prototyping acesti pasi vor fi repetati în mod iterativ pe parcursul procesului de dezvoltare.

Cap. 3. Tabele Microsoft Access. Tipuri de date.

1. Crearea unei tabele.

Crearea unei tabele noi se face din gestionarul de proiecte de la intrarea Create table in Design view.

2. Tipuri de date si proprietatile acestora.

A. Tip TEXT (max. 255 caractere). sir de caractere. Proprietati:

. Filed Size - numar maxim de caractere

. Format - format la afisarea datelor

. Input Mask - format la citirea datelor

. Caption - text eticheta asociata câmpului

. Default Value - valoare implicita

. Validation Rule - conditie de validare intrare

. Validation Text - mesaj la citire date eronate

. Required - este obligatorie introducerea unei valori pentru acest câmp

. Alow Zero Length - permite siruri vide

. Indexed - specifica crearea unui index pentru acest câmp

. Unicode Compression - asigura codare UNICODE

. Lookup - Display Control - specifica tipul elementului de tip control utilizat la afisare

B. Tip MEMO (max. 65.535 caractere). Text lung. Proprietati:


. Caption - text eticheta asociata

. Def ault Value - valoare implicita





. Unicode Compression - asigura codare UNICODE

C. Tip Number (numere pe 1, 2, 4, 8 sau 12 biti). Numere întregi sau zecimale. Proprietati:

. Filed Size - dimensiune câmp:

a. Byte - interval [0 ... 255]

b. Decimal - interval [±1028-l]

c. Integer - interval [±32.767]

d. Long Integer - interval [± 2,147,483,648]

e. Single - interval [±3.40282338]

f. Double - interval [±1.79769313486231308]

g. Replication ID - identificator unic global


. Decimal Places - numar de zecimale acceptat








. Lookup - Display Control - specifica tipul elementului de tip control utilizat la afisare

D. Tip "Date/Time" (data - an pe 4 pozitii). Data calendaristica, moment orar sau ambele.



. Caption - denumire eticheta asociata

. Def ault Value - valuare implicita





E. Tip Currency (suma de bani în precizie 15 cifre + 4 zecimale). Proprietati:


. Decimal Places - numar de zecimale acceptat








F. Tip Auto Number (index generat automat). Proprietati:

. Filed Size - dimensiune câmp:

a. Long Integer - interval [ 0.. 4,294,967,296]

b. Replication ID - numar pe 16 octeti

. New Values - metoda de generare: incrementala sau aleatoare




G. Tip Yes/No (valoare logica). Adevarat / Fals. Proprietati:

. Format - format Yes/No, True/False, On/Off







H. Tip OLE Object (obiect OLE). Referinta spre un obiect al unei alte aplicatii (ex: document Word, imagine BMP etc). Propiretati:



I. Tip Hyperlink (adresa URL). Adresa Web. Proprietati:









J. Tip Lookup wizard (câmp de selctie relationala). Valoare asociata din alta tabela. Permite alegerea unei valori dintr-o lista de valori fixe sau obtinute din câmpurile altei tabele. Se aplica pentru tipurile Text, Number si Yes/No.

3. Formate de afisare si introducere.

Proprietatea Format poate contine un sir de caractere cu semnificatia de masca de afisare. Aceasta poate forta de exemplu afisarea textelor cu o anumita combinatie de litere mari si mici sau a numerelor cu o anumita dispunere a virgulei zecimale. sirul Format poate contine caractere de control si caractere obisnuite. Caracterele vor masca caracterele din câmp, pe când restul caracterelor vor fi afisate ca atare (se vor adauga fortat caracterelor din câmp). în acest sens se pot folosi urmatoarele caractere de control:

. Pentru tipul text si memo:

@ - caracterul este obligatoriu & - caracterul este optional < - forteaza toate caracterele la litere mici > - forteaza toate caracterele la litere mari Formatul poate contine doua siruri despartite prin ";". Primul subsir reprezinta

formatul pentru texte normale. Cel de al doilea reprezinta textul afisat pentru text vid sau valoare NULL.

Exemple:

■ @@@@-@@@@@@ - afiseaza numarul 0256444555 sub forma 0256-444555

■ @;"Date inexistente" - afiseaza sirul nemodificat daca contine cel putin un caracter; afiseaza mesajul "Date inexistente" daca sirul este vid sau nu a fost introdus.

. Pentru tipul number:

. - separator zecimal , - separator pentru mii, milioane etc. 0 - afiseaza o cifra (forteaza 0 daca cifra nu exista) # - afiseaza o cifra (nu afiseaza nimic daca cifra nu exista) % - procent. înmulteste numarul cu 100 si afiseaza marcajul de procente Formatul poate contine patru siruri despartite prin ";". Primul subsir reprezinta

formatul pentru numere pozitive. Cel de al doilea reprezinta formatul pentru numere negative. Al treilea va formata valorile zero. Ultimul formateaza valorile necompletate (NULL).

Exemple:

■ $#,##0.00[Green];($#,##0.00)[Red];"Zero";"Necompletat" va afisa numarul 100 în verde sub forma "$100.00", numarul -150 în rosu sub forma "$150.00" numarul 0 sub forma "Zero" iar un câmp necompletat sub forma "Necompletat".

. Pentru tipul Yes/No:

Formatul va contine trei siruri despartite prin ";". Primul subsir este întotdeauna vid. Cel de al doilea reprezinta textul afisat pentru valoarea YES. Cel de al treilea reprezinta textul afisat pentru valoarea NO.

Exemplu:

■ ;"Da";"Nu" va afisa "Da" pentru valoarea Yes si "Nu" pentru valoarea No.

. Pentru tipul Date/Time:

Long Date - data sub forma "Saturday, April 3,1993' Medium Date - data sub forma "3-Apr-93' o Short Date - data sub forma

"4/3/1993' Long Time - ora sub forma "5:34:23 PM Medium Time - ora sub forma "5:34 PM" Short Time - ora sub forma "17:34" Caractere de control pentru an (y), luna (m), zi (d), ora (h), minut (n) si secunda

(s).

Exemplu:

■ ddd", "mmm d", "yyyy va fi afisat sub forma: Mon, Jun 2,1997

■ "Today is "dddd va afisa Today is Tuesday (limba depinde de setarea Windows - Regional Settings).

4. Stabilirea cheii primare (PK) si salvarea tabelei.

Pentru stabilirea cheii primare se va da un click dreapta de mouse peste marcajul câmpului

dorit si se alege intrarea din meniul aferent.

Salvarea tabelei se va face de la intrarea de meniu File/Save as. La salvare se va stabili de preferinta un nume sugestiv pentru datele retinute, dar nu foarte lung.

☺Observatie: Pentru a se permite salvarea tabelei trebuie stabilita în prealabil cheia primara a acesteia.

Cap. 4. Relatii. Interogari. Metoda QBE.

1. Crearea unei relatii.

Crearea relatiilor între tabele se poate realiza apelând la intrarea de meniu Tools -Relationships. Pentru a adauga tabele în fereastra de relatii se va selecta Relationships - Show Table. (a).

a)

b)

Relatiile se vor crea prin tragerea cu mouse-ul a cheii primare din tabela secundara peste cheia externa din tabela principala. Concordanta câmpurilor se va verifica în fereastra de editare a relatiilor (b).

Pentru a asigura integritatea referentiala a bazei de date, se va marca obligatoriu, la fiecare relatie creata, optiunea Enforce Referential Integrity. De asemenea, o optiune utila în cele mai multe situatii este cea de actualizare automata a câmpurilor relationate (Cascade Update Related Fields).

2. Interogari. QBE.

Interogarile permit filtrarea si ordonarea datelor din tabele precum si reunirea datelor din mai multe tabele, respectiv calcularea unor noi informatii.

Pentru crearea unei interogari, sistemul Access pune la dispozitie doua limbaje cu proprietati diferite. Primul, numit QBE - Query by Example - este un limbaj vizual al carui principal avantaj este simplitatea. Al doilea limbaj, SQL - Structured Query Language - este un limbaj declarativ care, desi contine relativ putine cuvinte cheie, permite exprimarea unor interogari mult mai elaborate decât QBE.

Interogarile pot fi clasificate în functie de actiunea realizata în:

. Interogari de selectie

. Interogari de actualizare

. Interogari de stergere

. Interogari de creare sau insertie

În acest capitol vor fi prezentate doar interogarile de selectie. Din punct de vedere al operatiilor implicate de interogari, putem distinge între: proiectie, sortarea, filtrarea (selectia), reuniunea (JOIN), gruparea si sumarizarea.

2.1. Operatia de proiectie

Operatia de proiectie presupune excluderea din rezultat a coloanelor care nu sunt relevante în contextul interogarii. Aceasta excludere se realizeaza prin ne-includerea lor în macheta de proiectie.

2.2. Operatia de sortare

Operatia de sortare presupune ordonarea rezultatului functie de unul sau mai multe criterii. În cazul în care sunt mai multe criterii, primul criteriu va fi cel dominant, restul fiind considerate în mod ierarhic, în ordinea în care apar.

2.3. Operatia de selectie

Operatia de filtrare (selectie) presupune excluderea din rezultat a înregistrarilor care nu îndeplinesc diverse criterii exprimate prin conditii (expresii) logice.

2.4. Operatia de JOIN

Operatia de reuniune (JOIN) consta în reunirea datelor aflate în diferite tabele între care exista stabilite relatii.

2.5. Operatia de grupare si sumarizare

Operatia de grupare si sumarizare presupune împartirea înregistrarilor în grupuri si extragerea informatiilor la nivelul fiecarui grup în parte. Pentru a avea acces la linia Total se va marca optiunea Totals din meniul View.

Optiunile de sumarizare sunt: Group by, Sum, Avg, Min, Max, Count, StDev, Var, First, Last, Expression, Where.

Cap. 5. Interogari SQL.

1. Limbajul SQL.

Limbajul SQL - Structured Query Language - este un limbaj declarativ care, desi contine relativ putine cuvinte cheie, permite exprimarea unor interogari foarte complexe pe o baza de date.

În cazul limbajelor declarative, programatorul va descrie algoritmul de rezolvat în loc sa implementeze algoritmul de rezolvare. În acest caz sistemul va cauta algoritmul de rezolvare, problemele fiind legate de respectarea sintaxei limbajului la descrierea problemei.

Limbajul SQL permite atât crearea entitatilor specifice unei baze de date (ex. tabele, indecsi, utilizatori etc.) cât si editarea si regasirea înregistrarilor din aceasta, în acest capitol vor fi prezentate doar interogarile de selectie.

Din punct de vedere al operatiilor implicate de interogari, putem distinge între: proiectie, sortarea, filtrarea (selectia), reuniunea (JOIN), gruparea si sumarizarea.

1.1. Operatia de proiectie

Operatia de proiectie presupune excluderea din rezultat a coloanelor care nu sunt relevante în contextul interogarii. Aceasta excludere se realizeaza prin ne-includerea lor în lista de proiectie.

Sintaxa instructiunii SQL care realizeaza proiectia unei tabele este:

SELECT lista_proiectie

FROM tabela;

Lista de proiectie va cuprinde specificarea câmpurilor din tabela, despartite prin virgula. Ordinea de aparitie a acestora va determina ordinea câmpurilor în rezultat. Prin proiectie se reduce în general numarul de coloane dar nu este afectat numarul de înregistrari.

Proiectia 1:1 se poate realiza înlocuind lista de proiectie cu caracterul *, caz în care toate coloanele vor fi proiectate în ordinea în care ele apar în tabela.

Daca se doreste, prin proiectie pot fi redenumite si câmpurile din tabela. Sintaxa listei de proiectie va fi în acest caz:

nume_câmp1 AS nume_nou1, nume_câmp2 AS nume_nou2,...

Exemplu:

SELECT cods AS CodStudent, nume AS NumeStudent

FROM Studenti;

1.2. Operatia de sortare

Operatia de sortare presupune ordonarea rezultatului functie de unul sau mai multe criterii.

În cazul în care sunt mai multe criterii, primul criteriu va fi cel dominant, restul fiind considerate în mod ierarhic, în ordinea în care apar în lista.

Sintaxa instructiunii SQL care realizeaza ordonarea rezultatului este:


FROM tabela

ORDER BY lista_ordonare;

Lista de ordonare va cuprinde specificarea unor câmpuri din tabela sau expresii în care acestea intervin, despartite prin virgula.

Implicit sortarea se face în sens crescator (ascendent). Pentru a sorta în mod descrescator (descendent) se va utiliza marcajul DESC dupa pozitia dorita.

Exemplu:

SELECT *

FROM Studenti

ORDER BY Nume, Medie DESC, DataN

Va ordona studentii crescator dupa nume, la nume egale descrescator dupa medie, iar si la medii egale crescator dupa data nasterii.

1.3. Operatia de selectie

Operatia de filtrare (selectie) presupune excluderea din rezultat a înregistrarilor care nu îndeplinesc diverse criterii exprimate prin conditii (expresii) logice.

Sintaxa instructiunii SQL care realizeaza selectia înregistrarilor este:


FROM tabela

WHERE conditie_selectie;

În rezultat vor fi incluse doar înregistrarile pentru care conditie_selectie este adevarata. Conditia de selectie este o expresie logica care combina operatori, valori si câmpuri. Ca si elementele de legatura se pot utiliza operatorii AND (sI logic) sau OR (SAU logic).

Prin selectie se pastreaza numarul de coloane dar numarul de înregistrari din rezultat este de obicei mai mic decât în tabela originala.

Pentru a înlatura duplicatele din rezultat se poate utiliza optiunea DISTINCT:

SELECT DISTINCT lista_proiectie

FROM tabela

WHERE conditie_selectie;

Exemplu:

SELECT cods, nume FROM Student WHERE nume = "Popescu";

Cap. 6. Operatori. Functii de biblioteca.

1. Functii pe siruri de caractere

l.a. Concatenarea sirurilor. Operatorul "&".

Sintaxa: sirl & sir2 & sir3 & ...

Exemplu: [Oras] & " " & [Judet] & " - " & [CodPostal]

l.b. Extragerea prefixului unui sir. Functia "Left".

Sintaxa: Left(sir, lungime)

Exemplu: Left("Popescu", 3) = "Pop"

l.c. Extragerea sufixului unui sir. Functia "Right".

Sintaxa: Right (sir, lungime)

Exemplu: Right ("Popescu", 4) = "escu"

l.d. Extragerea mijlocului unui sir. Functia "Mid".

Sintaxa: Mid(sir, start, lungime)

Exemplu: Mid ("Popeseu", 4, 3) = "esc"

l.e. stergerea spatiilor de început si sfârsit de sir. Functia "Trim

Sintaxa: Trim (sir)

Exemplu: Trim(" Popescu ") = "Popescu"

l.f. Cautarea unui subsir într-un sir. Functia "InStr".

Sintaxa: InStr([start,] sir, subsir [, tipComparatie])

Exemplu: lnStr("Popeseu", "op") = 2

Observatii:

. daca subsirul nu se gaseste în sir se va returna 0.

. daca parametru start este prezent, cautarea va începe de pe pozitia respectiva.

. tipComparatie poate lua valorile:

■ vbBinaryCompare - comparatia se va face binar (ASCII, 'A'<>'a').

■ vbTextCompare - comparatia se va face textual ('A'='a').

. daca subsirul este gasit se va returna pozitia de început a lui în sir.

l.g. Transformarea tuturor literelor sirului în litere mici. Functia "LCase".

Sintaxa: LCase (str)

Exemplu: LCase ("PopESCU 23.") = "popescu 23."

l.h. Transformarea tuturor literelor sirului în litere mari. Functia "UCase".

Sintaxa: UCase (str)

Exemplu: UCase ("PopESCU 23.") = "POPESCU 23."

l.i. Aflarea lungimii unui sir. Functia "Len".

Sintaxa: Len (str)

Exemplu: Len ("Popescu") = 7

l.j. Obtinerea unui sir format din spatii. Functia "Space".

Sintaxa: Space (nr)

Exemplu: Space (3) = "

l.k. Compararea sirurilor. Functia "StrComp".

Sintaxa: StrComp(sirl, sir2 [, tipComparatie])

Exemplu: StrComp ("Popeseu", "Vasilescu") = -l

Observatii:

. daca sirl < sir2 se va returna -1.

. daca sirl = sir2 se va returna 0.

. daca sirl > sir2 se va returna 1.

. tipComparatie poate lua valorile:

■ vbBinaryCompare - comparatia se va face binar (ASCII, 'A'<>'a').

■ vbTextCompare - comparatia se va face textual ('A'='a').

2. Functii pentru date calendaristice si timp

2.a. Testarea unui interval. Operatorul "between".

Sintaxa: data between data1 and data2

Exemplu: (#2-06-1998* between #1-01-1990* and #l-01-2000#) = true

2.b. Obtinerea datei curente a sistemului. Functiile "Date" si "Now".

Sintaxa: Date () respectiv Now ()

Exemplu: (#2-06-1998* between #1-01-1990* and Now()) = true

2.c. Obtinerea intervalului dintre doua date. Functia "DateDiff".

Sintaxa: DateDiff(specificarelnterval, datai, data2)

Exemplu: DateDiff ("d", #ll-01-2004#, #ll-20-2004#) = 19

Observatii:

Parametrul specificarelnterval va determina modul în care se calculeaza diferenta:

"yyyy"-în ani "m" - în luni "w" - în saptamâni "d" - în zile

2.d. Extragerea anului. Functia "Year".

Sintaxa: Year (data)

Exemplu: Year(#2-0 6-1998#) = 1998

2.e. Extragerea lunii. Functia "Month".

Sintaxa: Month (data)

Exemplu: Month(#2-06-1998#) = 2

2.f. Extragerea zilei. Functia "Day".

Sintaxa: Day (data)

Exemplu: Day(#2-O6-1998#) = 6

2.g. Extragerea zilei din saptamâna. Functia "Weekday".

Sintaxa: Weekday (data)

Exemplu: Weekday(#1-01-2004#) = 5 (ziua de joi)

Observatie: Se considera ca prima zi din saptamâna este duminica.

2.h. Obtinerea orei curente a sistemului. Functia "Time".

Sintaxa: Time ()

Exemplu: Time () = 10:37:16 AM

3. Functii numerice

3.a. Valoarea absoluta a unui numar. Functia "abs".

Sintaxa: abs(nr)

Exemplu: abs (-4) = 4

3.b. Cosinusul, sinusul si tangenta unui unghi. Functiile "cos", "sin" si "tan

Sintaxa: cos(unghi), sin(unghi), tan(unghi)

Exemplu: cos (3.1415926535897932) = -l

3.c. Logaritm natural. Functia "log".

Sintaxa: log(nr)

Exemplu: log (4) = 1.38629436112

3.d. Radical de ordinul 2. Functia "sqr".

Sintaxa: sqr(nr)

Exemplu: sqr (9) =3

4. Functii de conversie

4.a. Conversie sir de caractere în data calendaristica. Functia "DateValue".

Sintaxa: DateValue(sir)

Exemplu: DateValue ("February 12, 1969") = #2-12-1969*

4.b. Conversie unui numar real la un numar întreg. Functia "Int".

Sintaxa: int(nr)

Exemplu: int(4.6869) = 4

4.c. Conversie unui numar la un sir. Functia "Str".

Sintaxa: str(nr)

Exemplu: Str (4.6869) = " 4.6869" (spatiul este rezervat pt. semnul +)

4.d. Conversie unui sir la un numar. Functia "Val".

Sintaxa: Val (sir)

Exemplu: Val ("-4.6869 de unitati") = -4.6869

4.e. Conversia conditionala. Functia "IIF".

Sintaxa: ii£(conditie, valoareAdevarata, valoareFalsa)

Exemplu: iif( [bursa] >0, "Bursier", "Nebursier")

5. Operatori logici

5.a. si logic. Operatorul "And".

Sintaxa: expl And exp2

Exemplu: true And false = false

5.b. Sau logic. Operatorul "Or".

Sintaxa: exp1 Or exp2

Exemplu: true Or false = true

5.c. Nu logic. Operatorul "Not".

Sintaxa: Not exp

Exemplu: Not false = true

6. Operatori relationali

6.a. Operatori relationali pentru testarea inegalitatii.

Sintaxa: expl Op exp2, Op poate fi <, >, <=, >=, <>

Exemplu: 5 < 9 = true

6.b. Operatorul relational pentru testarea egalitatii.

Sintaxa: expl = exp2

Exemplu: (7-5) = 2 = true

6.c. Operatori de comparatie. Operatorul "like".

Sintaxa: sir like tipar

Exemplu: "Popeseu" like "P*" = true

Observatii:

Tiparul de comparare poate cuprinde urmatoarele caractere speciale:

? - înlocuieste orice caracter singular * - înlocuieste zero sau mai multe caractere # - înlocuieste orice cifra (0-9) [charlist] - înlocuieste un singur caracter din lista [! charlist] - înlocuieste un singur caracter care nu trebuie sa fie în lista

Cap. 7. Interogari SQL avansate.

7.1. Operatia de JOIN

Operatia de reuniune (JOIN) are ca scop principal reunirea datelor aflate în diferite tabele între care exista stabilite relatii.

O operatie de JOIN va uni tot timpul o cheie externa a unei tabele cu cheia primara din tabela asociata.

Operatia de JOIN poate fi privita ca un produs cartezian peste care se aplica apoi o selectie corespunzatoare conditiei de JOIN.

Din punct de vedere al rezultatului obtinut se pot distinge urmatoarele tipuri de JOIN:

INNER JOIN - rezultatul va include doar perechi de înregistrari care au corespondenta în ambele tabele

LEFT OUTER JOIN - rezultatul va include si înregistrarile din tabela stânga care nu au corespondent in tabela din partea dreapta a relatiei. Câmpurile care lipsesc din tabela corespondenta se vor completa automat cu valoarea NULL

RIGHT OUTER JOIN - va include în rezultat si înregistrarile din tabela din dreapta care nu au corespondent în tabela din partea stânga.

1. INNER JOIN

Pentru operatia de INNER JOIN, limbajul SQL pune la dispozitie doua sintaxe diferite:

A) SELECT lista_proiectie

FROM tabelaL, tabelaR

WHERE (tabelaL.cheiePrimara = tabelaR.cheieExterna)

AND conditii_supilmentare_de_selectie

. . .

Sintaxa permite specificarea unui produs cartezian explicit urmat de o selectie bazata pe conditia de JOIN restrictionata eventual cu alte conditii suplimentare impuse de problema.

Atentie: daca se omite conditia de JOIN din clauza WHERE (sau daca aceasta este incorecta), rezultatul va fi de obicei un produs cartezian.

B) SELECT lista_proiectie

FROM tabelaL INNER JOIN tabelaR

ON tabelaL. cheiePrimara = tabelaR. cheieExterna

WHERE conditii_supilmentare_de_selectie

. . .

Sintaxa aceasta are avantajul departajarii clare a conditiei de JOIN.

2. RIGHT OUTER JOIN

RIGHT OUTER JOIN (prescurtat RIGHT JOIN) va include în rezultat înregistrarile comune plus înregistrarile din tabela din dreapta care nu au corespondent în tabela din partea stânga. Câmpurile care lipsesc din tabela corespondenta se vor completa automat cu valoarea NULL.

Sintaxa SQL pentru RIGHT OUTER JOIN este:


FROM tabelaL RIGHT OUTER JOIN tabelaR



3. LEFT OUTER JOIN

LEFT OUTER JOIN (prescurtat LEFT JOIN) va include în rezultat înregistrarile comune plus înregistrarile din tabela din stânga care nu au corespondent în tabela din partea dreapta. Câmpurile care lipsesc din tabela corespondenta se vor completa automat cu valoarea NULL.

Sintaxa SQL pentru LEFT OUTER JOIN este:


FROM tabelaL LEFT OUTER JOIN tabelaR



4. Aliasuri pentru nume de tabele.

Datorita necesitatii repetarii numelui tabelei ca prefix pentru câmpurile care apar în lista de proiectie, conditiile de selectie, listele de ordonare etc, limbajul SQL permite crearea unor aliasuri (porecle) mai scurte pentru desemnarea acestora:

SELECT listajproiectie

FROM tabelaL AS TL, tabelaR AS TR

WHERE (TL.cheiePrimara = TR.cheieExterna)

AND conditii_supilmentare_de_selectie

sau


FROM tabelaL AS TL INNER JOIN tabelaR AS TR

ON TL.cheiePrimara = TR.cheieExterna

WHEKE conditii_supilmentare_de_selectie

Exemplu:

SELECT S.Nume, F.Denumire

FROM Facultati AS F INNER JOIN Studenti AS S ON F.CodF = S.CodF

WHERE (((S.Nume)>='H')) ORDER BY S.Nume;

5. Specificarea tipului de JOIN in QBE:

Se poate realiza din meniul context JoinProperties pentru o relatie.

Cap. 8. Interogari SQL avansate (II).

1. Gruparea înregistrarilor.

Gruparea înregistrarilor se realizeaza în scopul extragerii unor informatii globale despre înregistrarile din fiecare grup.

Pentru gruparea înregistrarilor se va utiliza clauza GROUP BY urmata de criteriile de grupare, despartite prin virgula.

În cazul gruparii, lista de proiectie va putea cuprinde doar câmpurile din lista criteriilor de grupare sau functii de sumarizare. Rezultatul operatiei de grupare va contine un numar de înregistrari corespunzator cu numarul de grupuri rezultate, respectiv câte o înregistrare pentru fiecare grup.

În cazul în care lista de proiectie va cuprinde una sau mai multe functii de sumarizare în lipsa clauzei GROUP BY, implicit se considera ca toate înregistrarile apartin aceluiasi grup, sumarizarea producând o singura înregistrare în rezultat. în acest caz, lista de proiectie nu va contine decât functii de sumarizare.

2. Functii de sumarizare.

Functiile de sumarizare vor calcula o valoare corespunzatoare valorilor înregitrarilor din fiecare grup rezultat în urma gruparii. Ele sunt prezentate în lista urmatoare:

Avg - calculeaza media aritmetica a valorilor unui câmp numeric sau data; Count - numara valorile distincte din câmpul respectiv pentru grupul considerat; Min - întoarce valoarea minima a unui câmp numeric, text sau de tip data

calendaristica, pentru grupul de înregistrari respectiv; Max - întoarce valoarea maxima a unui câmp numeric, text sau de tip data

calendaristica, pentru grupul de înregistrari respectiv; Sum - calculeaza suma valorilor unui câmp numeric sau data; First - returneaza valoarea câmpului pentru prima înregistrare (considerata în

ordine cronologica) din grup; Last - returneaza valoarea câmpului pentru ultima înregistrare din grup; StDev - calculeaza deviatia standard a valorilor dintr-un câmp numeric sau data

calendaristica; Var - calculeaza variatie valorilor dintr-un câmp numeric sau data

calendaristica;

Sintaxa SQL pentru operatia de grupare si sumarizare este:


FROM lista_tabele

WHERE conditii_selectie

GROUP BY expresii_grupare

Considerând o tabela de studenti care cuprinde câmpurile Cods, Nume, Prenume, CodFacultate, An, DataNasterii si Bursa, un exemplu de interogare de grupare si sumarizare este:

SELECT CodFacultate, An, Avg(Bursa) as BursaMedie,

Sum(Bursa) As SumaBurselor, Min(DataNasterii) as DNCelMaiMare

FROM Studenti

GROUP BY CodFacultate, An;

Aceasta interogare va produce câte o înregistrare pentru fiecare pereche distincta CodFacultate-An cuprinzând informatiile de sumarizare respective.

3. Filtrarea grupurilor. Clauza HAVING.

Sunt cazuri în care nu toate grupurile rezuntate din operatia de grupare sunt necesare în rezultat. în aceste situatii ele pot fi excluse din rezultate folosind clauza HAVING.

Clauza HAVING poate fi folosita doar în conjunctie cu operatia de grupare.

Sintaxa clauzei HAVING permite specificarea unei conditii de filtrare a grupurilor care nu sunt necerare. Toate grupurile care nu indeplinesc conditia vor fi excluse din rezultat. Conditia din HAVING trebuie sa refere doar expresii existente în clauza de grupare.


FROM lista_tabele

WHERE conditii_selectie

GROUP BY expresii_grupare

HAVING conditie_includere_grup;

4. Interogari de grupare si sumarizare în QBE.

În QBE interogarile de sumarizare sunt cunoscute sub numele de interogari de tip TOTALS. Pentru a creea o astfel de interogare se va marca optiunea Totals din meniul View. Linia Total va specifica expresia de gruapre sau functia de sumarizare aplicata coloanei respective.

Cap. 9. Interogari active.

1. Interogari pentru crearea de tabele (Make-Table Queries).

În mod uzual datele rezultate dintr-o interogare sunt folosite pentru consultare imediata. Sunt însa si situatii când aceste date trebuie pastrate într-o tabela. Pentru a realiza acest lucru se va crea o interogare de tip Make-Table care va avea ca rezultat la rulare crearea unei noi tabele având câmpurile selectate în interogare.

O tabela creata printr-o astfel de interogare poate pastra atât valori din câmpuri existente în tabelele de origine cât si câmpuri calculate. Pentru valorile calculate se va crea un câmp având tipul dat de tipul expresiei de calcul.

Pentru tabela creata în acest mod trebuie setata, ulterior, cheia primara.

Pentru a crea o interogare de tip Make-Table se va crea în prealabil o interogare de selectie. Prin rularea acestei interogari se verifica rezultatul obtinut. Dupa validarea acestuia se va apela optiunea Make-Table Query din meniul Query. La transformarea unei interogari de selectie într-o interogare de tip Make-Table se va preciza numele tabelei noi ce va fi creata la rulare.

La fiecare rulare a interogarii tabela se va crea peste tabela existenta, datele vechi (de la rularea anterioara) fiind pierdute.

2. Interogari de actualizare (Update Queries).

Acest tip de interogare este folosit pentru a modifica datele din una sau mai multe înregistrari ale unei tabele.

Pentru o interogare de actualizare se vor specifica câmpurile care vor fi actualizate si respectiv conditiile de selectie a înregistrarilor care se vor actualiza. Specificarea conditiilor se va face în câmpul Criteria. Daca nu se specifica nici o conditie, se vor actualiza toate înregistrarile din tabela.

Pentru a crea o interogare de tip actualizare se va crea în prealabil o interogare de selectie. Prin rularea acestei interogari se verifica rezultatul obtinut, el indicând daca conditiile de selectie a înregistrarilor de modificat sunt corecte sau nu. Dupa validarea acestora se va apela optiunea Update Query din meniul Query. Dupa transformare va fi disponibila o noua bara în fereastra de editare a interogarii purtând eticheta Update To. Aici se vor specifica noile valori ale câmpurilor de modificat. Acestea pot fi valori constante sau pot fi expresii de calcul. O expresie poate referi prin numele câmpului valoarea anterioara actualizarii din acel câmp.

La rularea unei astfel de interogari se va cere confirmarea actualizarii cu precizarea numarului de înregistrari afectate.

3. Interogari de adaugare date (Append Queries).

Acest tip de interogare se poate utiliza pentru a adauga înregistrari preluate din alte tabele sau interogari. De asemenea se poate utiliza pentru a copia înregistrari în alta baza de date.

O interogare de tip actualizare are una sau mai multe tabele sursa (de unde se preiau datele de copiat) si o singura tabela destinatie. Tabela destinatie se specifica doar în momentul în care interogarea este transformata într-una de adaugare.

Pentru o interogare de adaugare se vor specifica câmpurile care vor fi copiate si respectiv conditiile de selectie a înregistrarilor care se vor copia. Specificarea conditiilor se va face în câmpul Criteria. Daca nu se specifica nici o conditie, se vor copia toate înregistrarile din tabela sursa. Valorile copiate în tabela destinatie pot fi identice cu cele din tabelele sursa sau pot fi calculate prin expresii.

Pentru a crea o interogare de tip adaugare se va crea în prealabil o interogare de selectie. Prin rularea acestei interogari se verifica rezultatul obtinut, el indicând daca conditiile de selectie a înregistrarilor care trebuie copiate sunt corecte sau nu. Dupa validarea acestora se va apela optiunea Append Query din meniul Query. Dupa transformare va fi disponibila o noua bara în fereastra de editare a interogarii purtând eticheta Append To. Aici se vor specifica câmpurile destinatie în care se va realiza copierea. Sistemul încearca sa completeze automat aceste coloane bazat pe denumiri identice sau foarte apropiate în cele doua tabele. Pentru atributele al caror nume nu se potrivesc, respectiv pentru câmpurile calculate prin expresii, corespondenta trebuie facuta manual.

4. Interogari de stergere date (Delete Queries).

Acest tip de interogare se poate utiliza pentru a sterge înregistrari care nu mai sunt utile.

Pentru a selecta înregistrarile care se vor sterge este necesar sa se specifice conditii de selectie. Specificarea conditiilor se va face în câmpul Criteria. Daca nu se specifica nici o conditie, se vor sterge toate înregistrarile din tabela.

Atentie: înregistrarile sterse nu mai pot fi recuperate!

Pentru a crea o interogare de tip stergere de date se va crea în prealabil o interogare de selectie. Prin rularea acestei interogari se verifica rezultatul obtinut, el indicând daca conditiile de selectie a înregistrarilor care trebuie sterse sunt corecte sau nu. Dupa validarea acestora se va apela optiunea Delete Query din meniul Query.

Daca tabela din care sunt sterse înregistrari are coloane referite din alte tabele, sistemul va detecta automat necesitatea stergerii acestora doar în cazul în care relatia dintre tabele are marcata optiunea Enforce Referential Integrity, respectiv Cascade Delete Related Records.

5. Interogari active în sintaxa SQL.

Interogare de tip Make-Table:

SELECT Studenti.Matricola, Studenti.Nume INTO StudAnl

FROM Studenti

WHERE (Studenti.An = 1);

Interogare de tip Update:

UPDATE Studenti

SET Studenti.Bursa = Studenti.Bursa+100000

WHERE (((Studenti.An)=1));

Interogare de tip Append:

INSERT INTO StudAnl

SELECT FROM Studenti

WHERE (Studenti.An = 1);

Interogare de tip Delete:

DELETE FROM Studenti

WHERE ( (Bursa>0 AND Bursa<1000000)

AND (Studenti.An =1) );

Cap. 10. Formulare. Importul si exportul datelor.

1. Formulare Access.

Formularele reprezinta o posibilitate de vizualizare a datelor apropiate de utilizatorul uman. Ele permit formatarea fina a iesirii si un control asupra intrarii de date, inclusiv o validare stricta a acesteia. Formularele pot avea ca si sursa de date fie direct tabele, fie interogari.

2. Crearea formularelor.

Pentru crearea unui formular se poate folosi fie o metoda automata (Wizard), fie o proiectare directa (Design).

3. Elementele constituente ale unui formular.

4. Uneltele din Fereastra de unelte.

a) Label - eticheta pentru afisare text fix.

b) Text Box - câmp de editare text.

c) Group Option - grup de optiuni pentru casute de marcaj, butoane radio etc.

d) Toggle Button - buton basculant pentru înregistrarea unei stari.

e) Option Button - buton tip radio pentru marcarea unor optiuni cu excludere mutuala.

f) Check Box - casuta de marcaj pentru marcarea unei stari.

g) Combo Box - lista derulanta de valori,

h) List Box - lista de valori.

i) Command Button - buton de comanda pentru executia de cod.

j) Image - control pentru afisarea unei imagini.

k) (Un)Bond Object Frame - cadru pentru afisarea unui obiect OLE.

l) Tab Control - control pentru paginarea ferestrei.

m) Subform - control pentru crearea unui sub-formular în interiorul formularului curent,

n) Line - linie de decorare,

o) Rectangle - dreptunghi de decorare.

5. Importul datelor din surse externe

In multe situatii este necesar importul datelor din alte aplicatii. Acestea pot fi:

Baze de date externe: DBase, Paradox, Ms Access sau orice alt format disponibil printr-o conexiune ODBC

Foi de calcul: Ms Excel sau Lotus 1-2-3 Aplicatii care prelucreaza texte: Ms Word, Wordpad, Notepad, Outlook etc.

Datele pot fi importate sau se pot crea legaturi catre acestea. în cazul în care se opteaza pentru legaturi, datele se pastreaza în fisierele originale si pot fi prelucrate atât din Ms Access cât si din aplicatia originala.

Importul datelor din alta baza de date Ms Access. Tabelele existente în alte baze de date Access pot fi importate sau legate. Aceste operatii pot fi realizate utilizând meniul File - Get Externai Data si optiunile Import respectiv Link Table. Observatie: Optiunea Import permite importul tabelelor dar si al interogarilor, formularelor, rapoartelor sau modulelor de cod. Optiunea Link permite doar legarea unei tabele.

Importul datelor din Ms Excel. Pentru un import eficient din Excel intr-o baza de date Access este de dorit denumirea coloanelor foii din Excel pe prima linie importata din foaie. O foaie de calcul poate fi importata într-o tabela existenta sau într-o tabela noua, care se va fi creata în timpul operatiei de import. Importul efectiv se face tot din meniul File - Get Externai Data.

Importul datelor dintr-un fisier text. Pentru un import corect al datelor stocate într-un fisier text este necesar ca acesta sa respecte o conventie clara cu privire la delimitarea câmpurilor. De asemenea este de dorit ca informatia de tip text sa fie cuprinsa între ghilimele. Delimitarea câmpurilor se poate face fie printr-un semn special (virgula, tab, spatiu etc.) fie

punând toate câmpurile de dimensiune egala. La import, numele atributelor se pot lua de pe prima linie din fisier sau se pot specifica direct în fereastra de import. De asemenea se pot modifica tipul de date (se detecteaza automat) si daca se face sau nu indexarea dupa acel câmp. în încheiere se cere stabilirea unei chei primare (Access poate adauga automat un câmp cu acest rol).

Importul datelor din Ms Outlook (Address Book). Daca este necesar sa fie importate adresele stocate în Outlook, se va apela la meniul File - Get Externai Data, optiunea Import. Importul se va face automat, fara a fi necesari completarea pasilor intermediari prezentati la celelalte tipuri de importuri

6. Exportul datelor.

Exportul datelor într-o alta baza de date Ms Access. Exportul datelor se realizeaza din meniul File - Export. Baza de date în care se face exportul trebuie sa existe în prealabil. Se poate opta pentru a exporta doar definitia unei tabele sau si datele continute de aceasta.

Exportul datelor într-o foaie de calcul Ms Excel. La exportul unei tabele în format Excel, se va crea automat un fisier nou. Prima linie din foaia exportata va contine numele atributelor (coloanelor). Procesul de export decurge complet automatizat.

Exportul în format text. La exportul în format text se va opta pentru formatarea rezultatului. în acest caz rezultatul este de genul:

CodDep Denumire

1 Productie2 Management

Exportul în format HTML. Acesta este util pentru a realiza publicarea rezultatelor pe Web. Pentru un control mai precis al aspectului paginii se poate utiliza salvarea în HTML a unui raport special creat care sa formateze datele. Atentie: în ambele situatii datele sunt statice (fixate în momentul exportului).

Cap. 11. Rapoarte.

1. Rapoarte Access.

Rapoartele reprezinta o posibilitate de obtinere a unor situatii tiparite prezentate într-o forma atractiva si accesibila celor interesati. Ele au de obicei un impact semnificativ asupra persoanelor care vin in contact cu baze de date, fara a avea însa cunostinte de specialitate în domeniul bazelor de date (manageri, directori, contabili etc).

Spre deosebire de formulare, rapoartele nu modifica niciodata datele. Ele doar parcurg secvential înregistrarile pentru a genera eventual sub-totaluri si rezumate.

Rapoartele sunt folosite atât pentru liste simple cât si pentru etichete postale tiparite automat, grafice de analiza sau rezumate si analize financiare complicate.

În toate cazurile se va avea în vedere claritatea raportului si accentuarea elementelor esentiale pe care le contine. Pentru aceasta pot fi utilizate o serie de trucuri precum: diverse fonturi, marimi si stiluri de caractere, diverse culori de afisare, diverse elemente grafice de individualizare sau grupare în genul liniilor, dreptunghiurilor etc.

De o foarte mare importanta este posibilitatea de a grupa vizual datele si de a extrage informatii sintetice referitoare la fiecare grup în parte.

2. Crearea rapoartelor.

Pentru crearea unui raport se poate folosi, ca si în cazul formularelor, fie o metoda automata (Wizard), fie o proiectare directa (Design).

În ambele situatii se va alege o tabela sau o interogare care va furniza datele utilizate în raport. Daca datele provin din mai multe tabele, se va utiliza obligatoriu o interogare.

3. Elementele constituente ale unui raport.

Un raport este compus din mai multe benzi. O banda (sectiune) are corespondenta directa cu o anumita portiune fizica din pagina tiparita. Benzile puse la dispozitie sunt:

Antetul si subsolul raportului (report header si report footer) apar doar pe prima, respectiv ultima pagina a raportului. Antetul afiseaza de obicei titlul raportului, sigla firmei etc. Subsolul cuprinde de obicei un rezumat al tuturor datelor din raport.

Antetul si subsolul de pagina (page header si pagefooter) cuprinde elementele din partea de sus si de jos al fiecarei pagini. în mod tipic, antetul de pagina cuprinde numele coloanelor afisate in sectiunea de detaliere iar subsolul de pagina afiseaza numarul paginii si eventual data tiparirii raportului.

Sectiunea de detaliere (detail) va fi repetata pentru fiecare înregistrare la rularea raportului. Ea cuprinde datele de afisat. La nevoie, un raport poate cuprinde si sub-rapoarte.

Antetul si nota de subsol al fiecarui grup {group header si group footer) vor fi afisate înaintea, respectiv imediat dupa fiecare grup. Ele vor cuprinde de obicei informatii despre grupul care va fi afisat, respectiv informatii de sumarizare despre datele respectivului grup. Pentru a sorta datele sau a include/exclude un grup se va apela la meniul View->Sorting and Grouping. Un grup va fi creat automat daca se selecteaza cel putin una din optiunile Group Header sau Group Footer.

Pentru a verifica corectitudinea si aspectul unui raport se va apela la comanda Print Preview.

4. Uneltele din Fereastra de unelte.

a) Label - eticheta pentru afisare text fix.

b) Text Box - câmp de editare text.

c) Group Option - grup de optiuni pentru casute de marcaj, butoane radio etc.

d) Toggle Button - buton basculant pentru înregistrarea unei stari.

e) Option Button - buton tip radio pentru marcarea unor optiuni cu excludere mutuala.

f) Check Box - casuta de marcaj pentru marcarea unei stari.

g) Combo Box - lista derulanta de valori,

h) List Box - lista de valori.

i) Command Button - buton de comanda pentru executia de cod.

j) Image - control pentru afisarea unei imagini.

k) (Un)Bond Object Frame - cadru pentru afisarea unui obiect OLE.

l) Tab Control - control pentru paginarea ferestrei.

m) Subform - control pentru crearea unui sub-formular în interiorul formularului curent,

n) Line - linie de decorare,

o) Rectangle - dreptunghi de decorare.

Cap. 12. Sistemul de Macrocomenzi. VBA. Securitatea BD Access.

1. Sistemul de Macrocomenzi Access. Limbajul VBA.

Macrocomenzile permit automatizarea actiunilor repetitive din Access într-o maniera simplificata fata de un limbaj de programare.

Desi nu permit realizarea operatiilor pretentioase, macrocomenzile ramân cea mai rapida cale de rezolvare a unor probleme simple.

Exemple de actiuni rezolvate prin Macrocomenzi: afisarea unui mesaj de înstiintare (MsgBox), schimbarea barei de stare (Echo), schimbarea unei proprietati a unui obiect (SetValue), pentru a tipari un raport (Open Report).

Macrodefinitia AutoExec poate fi folosita pentru a deschide un formular de început la pornirea bazei de date (Acest lucru se poate specifica si prin optiune Tools - StartUp), respectiv poate rula o procedura VBA de initializare.

Limbajul VBA (Visual Basic for Applications) permite implementarea unei game mult mai largi de actiuni. Printre acestea ar fi: tratarea erorilor, executia tranzactiilor, structuri ciclice, apel functii Windows API, crearea dinamica de obiecte. Liniile de cod VBA sunt reunite în rutine, respectiv proceduri si functii. Acestea pot face la rândul lor parte din module de cod si pot raspunde automat la evenimentele generate de interfata aplicatiei.

Subprogramele VBA pot fi definite in module globale sau pot fi legate de formulare si rapoarte - CBF (Code Behind Form). în ambele situatii ele pot fi publice sau private.

Variabilele folosite se declara cu Dim si pot fi de tip: byte, boolean, integer, long integer, single, double, currency, date, object, fixed string,, var string,, variant sau type.

În functie de modul de declarare ele pot fi publice, la nivel de modul sau locale, respectiv simple sau statice.

Structurile de control din VBA sunt urmatoarele: if ... then ... else, select ... case, do while ... loop, do ... loop while ..., for ... step ... next. Ele sunt folosite asemanator cu cele din alte limbaje de nivel înalt precum C, Pascal etc.

Pentru a declara proceduri VBA se va folosi constructia Sub... End Sub.

Sub numeProc(P1 As Type1, ...)

.

End Sub

Spre deosebire de proceduri, functiile pot returna un rezultat. Sintaxa este însa asemanatoare cu cea a procedurilor. La final, numele functiei va primi valoarea de returnat.

Function numeFunctie ( PI As Typel, ...)

.

numeFunctie = valoare

End Function

Biblioteca de functii predefinite este foarte importanta în toate sistemele de programare moderne. De la aceasta regula nu face exceptie nici sistemul Access. Exista o colectie importanta de functii VBA predefinite precum: Format, InStr, Mid, UCase, DatePart etc.

2. Utilizarea unei baze de date in mediu concurent (retea).

Problema care apare la folosirea unei aplicatii Access într-o retea este partajarea datelor între utilizatori.

Rezolvarea acestei probleme se poate face prin blocarea datelor pentru evitarea conflictelor. Partajarea se poate face la nivelul datelor sau pentru toate obiectele aplicatiei.

Exista trei tehnici de blocare: blocarea paginii, blocarea tabelului si deschiderea unei baze de date cu acces exclusiv.

Controlul global al blocarii se poate face din meniul Tools - Options -Advanced.

Blocarea înregistrarilor pentru o interogare se poate face din fereastra de proprietati a interogarii, optiunea Record Locks. Intr-o rutina VBA blocarea se poate face la prelucrarea unui set de înregistrari prin setarea proprietatii LockEdits.

3. Întretinerea bazei de date

Datorita stergerii datelor din interiorul tabelelor, fisierul bazei de date se "fragmenteaza". Pentru a remedia aceasta situatie care încetineste procesarea trebuie executata o operatie de compactare. Aceasta se realizeaza pe o baza de date prin optiunea Compact and Repair Database din meniul Tools/Database Utilities.

Repararea unei baze de date distruse se poate încerca folosind aceiasi optiune de meniu. înainte de reparare este necesara crearea unei copii de siguranta.

4. Securitatea datelor.

Principial, o baza de date Microsoft Access poate fi protejata de accese neautorizate folosind trei mecanisme: protejarea prin parola, criptarea si stabilirea unui sistem de utilizatori si drepturi asociate.

Protejarea prin parola se stabileste pentru o baza de date deschisa din meniul Tools - Security - Database Password.

Criptarea bazei de date se face din meniul Tools - Security - Encrypt / Decript Database. Prin criptare baza de date este protejata la citirea informatiilor din alte programe.

Cea mai completa (dar si complexa) metoda de securitate se bazeaza pe un sistem de drepturi acordate utilizatorilor. Pentru o administrare eficienta utilizatorii vor fi inclusi in grupuri, drepturile fiind acordate la nivel de grup.

Din motive de securitate, se va renunta la utilizatorul Admin, dupa ce în prealabil în grupul Admins a fost creat un nou utilizator.

Administrarea se face din meniul Tools - Security - Users and Groups Accounts. Sistemul de utilizatori nu apartine unei baze de date ci sistemului.

Schimbarea parolei de intrare în sistem se face prin optiunea de meniu Change Logon Password.

Sistemul de Gestiune a Bazelor de Date MS ACCESS

Documents

Transcript of Sistemul de Gestiune a Bazelor de Date MS ACCESS