Inspectoratul colar Judeean Bacu

PROGRAM DE FORMARE-DEZVOLTARE CONTINU

BACOV-IAGrup int: Cadre didactice din nvmntul preuniversitar

UTILIZAREA SISTEMELOR DE GESTIUNE A BAZELOR DE DATE PENTRU TEZAURIZAREA DATELOR UNUI SISTEM INFORMAIONALdr.ing.Alexandru Ctlin FILIP

Braov, 2011

Profesionalizarea carierei didactice noi competene pentru actori ai schimbrilor n educaie din judeele Bacu i Covasna

Investete n

OAMENI

CUPRINS

INTRODUCERE ............................................................................................................................. 3 TEMA NR.1 NOIUNI DE BAZ PRIVIND GESTIUNEA INFORMAIILOR CU AJUTORUL BAZELOR DE DATE ...................................................................................................................... 51.3.1. SCURT ISTORIC AL BAZELOR DE DATE ............................................................................................. 5 1.3.2. CE ESTE O BAZ DE DATE? CONCEPTE GENERALE. .......................................................................... 7 1.3.3. INTERFAA PRINCIPAL A SISTEMULUI MICROSOFT ACCESS 2007................................................13

TEMA NR.2 GESTIUNEA INFORMAIILOR N TABELELE UNEI BAZE DE DATE ................ 202.3.1. CREAREA TABELELOR N BAZELE DE DATE ACCESS. LUCRUL CU NREGISTRRILE. ......................20 2.3.2. STABILIREA PROPRIETILOR CMPURILOR N BAZELE DE DATE ACCESS. ....................................27 2.3.3. ORDONAREA I AFIAREA SELECTIV A NREGISTRRILOR. ...........................................................32

TEMA NR.3 PRELUCRAREA INFORMAIILOR PRIN INTEROGRI....................................... 373.3.1. STABILIREA RELAIILOR NTRE TABELELE UNEI BAZE DE DATE ACCESS. ......................................37 3.3.2. CREAREA UNEI INTEROGRI. FEREASTRA DE PROIECTARE. ............................................................41 3.3.3. INTEROGRI SIMPLE PENTRU SELECIE DE NREGISTRRI. ..............................................................44 3.3.4. INTEROGRI DE SELECIE DE TIP STATISTIC. ..................................................................................51

TEMA NR.4 ASPECTE PRIVIND SECURITATEA INFORMAIILOR N BAZELE DE DATE .... 534.3.1. CREAREA I UTILIZAREA FORMULARELOR N ACCESS ..................................................................54 4.3.2. POSIBILITI DE ASIGURARE A SECURITII FIIERELOR N ACCESS .............................................62

REZUMATUL DISCIPLINEI ........................................................................................................ 66 ANEXA NR.1 Tipuri de date utilizabile n bazele de date Access ................................................ 67 ANEXA NR.2 nregistrrile tabelelor din aplicaie ........................................................................ 68 ANEXA NR.3 Funcii predefinite incluse n biblioteca Access .................................................... 71 BIBLIOGRAFIE ............................................................................................................................ 73

2

INTRODUCERE

1. Locul i rolul disciplinei n formarea competenelor programului Disciplina Utilizarea sistemelor de gestiune a bazelor de date pentru tezaurizarea datelor unui sistem informaional se ncadreaz n grupul disciplinelor de tehnologia informaiei i utilizarea calculatorului. Avnd n vedere unul dintre obiectivele specifice ale proiectului dezvoltarea capacitii cadrelor didactice de a utiliza metode interactive de predare-nvare-evaluare prin intermediul TIC n implementarea curriculumului centrat pe competene disciplina asigur competenele de baz necesare gestiunii informatizate a diferitelor tipuri de informaii, prin intermediul unuia dintre sistemele informatice cele mai cunoscute, specializat pentru baze de date, Microsoft Access 2007. 2. Competene profesionale oferite Dup parcurgerea i nsuirea materialului cursantul va fi capabil: s descrie principiile de baz ale gestiunii informatizate a informaiilor, s lucreze cu conceptele de tabel, cmp, nregistrare, relaie, s realizeze diferite aciuni n interfaa principal a programului Microsoft Access2007, s creeze tabele noi pe baza unor structuri proiectate anterior, s afieze, adauge, tearg nregistrri n tabele, s lucreze cu diferitele moduri de afiare a unui tabel, s ordoneze i s afieze selectiv nregistrri n tabele, s identifice i s creeze relaii posibile i utile ntre tabelele unei baze de date, s prelucreze informaiile din baza de date prin intermediul interogrilor, s protejeze datele dintr-o baz de date cu ajutorul unei parole.

3. Obiective generale nelegerea conceptelor de baz privind sistemele de gestiune a datelor utilizate pentru tezaurizarea informaiilor cunoaterea interfeei generale a sistemului Microsoft Office Access 2007 nsuirea modului de a lucra cu tabelele de date i de a prelucra informaiile prin ordonarea i filtrarea datelor nsuirea principiilor de baz privind extragerea i prelucrarea informaiilor cu ajutorul interogrilor nelegerea aspectelor privind securitatea informaiilor tezaurizate sub forma unei baze de date3

4. Structura cursului Cursul este structurat n 4 teme principale. Tema nr.1 se refer la noiunile teoretice de baz privind gestiunea informaiilor n bazele de date. Tema nr.2 prezint organizarea datelor sub forma tabelelor n sistemul de gestiune a bazelor de date Microsoft Access. Tema nr.3 trateaz posibilitile de prelucrare a informaiilor tezaurizate n tabele cu ajutorul opiunilor de ordonare, filtrare a datelor i prin intermediul interogrilor. Tema nr.4 include aspecte care in de protecia informaiilor prin intermediul formularelor, a parolelor de acces sau prin diferite alte metode puse la dispoziie de sistemul software. n cadrul fiecrei teme sunt incluse aplicaii practice sub form de exemple pe care cursantul trebuie s le execute n vederea dobndirii deprinderilor practice necesare.

5. Evaluarea Se va face prin crearea unei aplicaii proprii de baz de date, de ctre fiecare cursant, respectnd un set de cerine minime: minim 2 tabele avnd un minim de 15 nregistrri n total. Tabelele trebuie s conin un numr minim de 3 cmpuri fiecare. Baza de date va conine cel puin 2 interogri care s afieze selectiv informaiile din tabele i 1 formular de afiare a informaiilor.

4

TEMA NR. 1 NOIUNI DE BAZ PRIVIND GESTIUNEA INFORMAIILOR CU AJUTORUL BAZELOR DE DATE

1.1. Competene profesionale oferite: a nelege principiile de baz ale gestiunii informatizate a informaiilor a lucra cu conceptele de tabel, cmp, nregistrare, relaie a crea structuri de date relaionale utilizate pentru tezaurizarea datelor a realiza diferite aciuni n interfaa principal a programului Microsoft Access2007. 1.2. Obiectivele temei nelegerea principiilor de baz privind gestiunea informatizat a informaiilor nelegerea structurii unei baze de date i a componentelor acesteia nelegerea conceptului de baz de date relaional i a terminologiei utilizate deprinderea de a lucra cu interfaa principal a programului Microsoft Access 1.3. Coninutul detaliat 1.3.1. Scurt istoric al bazelor de date n societatea contemporan gestionarea informaiei este o caracteristic definitorie a existenei acesteia. Constatm c astzi este nevoie ca un volum tot mai mare de informaii s fie gestionat (memorat, prelucrat, distribuit etc.) ntr-un timp ct mai scurt i cu costuri acceptabile. Bazele de date sunt acele instrumente prin care se poate realiza acest lucru. n societatea contemporan se vorbete destul de des de sintagmele societate bazat pe cunoatere i societate informaional. Ambele concepte presupun utilizarea calculatorului personal pentru cunoatere, tezaurizare, transmitere i prelucrare a volumului imens de informaii, specifice diferitelor domenii de activitate. n ceea ce privete evoluia istoric a gestiunii informaiilor sub form de baze de date prin intermediul calculatorului personal i a sistemelor software, se pot evidenia cteva etape istorice importante: anii 1960 perioada bazelor de date secveniale, bazate pe modelele ierarhic sau reea, n care nregistrrile erau dependente una de cealalt. Dependena era stabilit prin legturi numite pointeri. Pentru gsirea unei informaii, trebuia parcurs ntreaga baz de date, de la o nregistrare la alta, pn la nregistrarea care coninea informaia cutat. Acest mod de abordare

5

necesita timpi de cutare relative mari, pentru baze de date de volume mari. Cele mai cunoscute sisteme ale perioadei sunt CODASYL i IMS (IBM); anii 1970 apariia modelului relaional, bazat pe conceptele matematice din algebra relaional pentru a grupa datele n mulimi i a stabili relaii ntre acestea. Modelul este atribuit lui Edgar Frank Codd care prin lucrarea "A Relational Model of Data for Large Shared Data Banks, a marcat un moment important de cotitur n domeniul gestiunii informaiilor sub form de baze de date. n cadrul acestui model, informaiile sub memorate pe grupuri de obiecte, avnd anumite atribuite i se stabilesc relaii de dependen ntre grupuri. O anumit informaie se poate gsi acum rapid pe baza unei chei unice de identificare a fiecrei nregistrri, nefiind necesar parcurgerea pas cu pas a bazei de date; perioada 1970 -1980 dezvoltarea modelului relaional, prin apariia diferitelor sisteme software bazate pe acest model. Cele mai cunoscute au fost dezvoltate de ctre MIT (Massachusetts Institute of Technology), prin sistemele INGRES i QUEL i de ctre IBM, prin SYSTEM R, SEQUEL i SQL (Structured Query Language). Ultimul menionat a devenit n 1987 standard internaional n domeniu, fiind de fapt un set de instruciuni care este inclus n toate sistemele software utilizate n prezent pentru gestiunea informaiilor n baze de date; dup anii 1990 perioada modelelor OODB (Object Oriented DataBase), bazate pe modelul programrii orientate pe obiecte, cercetri n direcia creterii vitezei de procesare a informaiilor n baze de date tot mai voluminoase, n direcia bazelor de date bazate pe acces prin internet (web-based) odat cu dezvoltarea rapid a internetului i a reelelor de calculatoare. Standardul SQL este permanent dezvoltat i completat cu instruciuni noi, de exemplu prin includerea sa n limbajele XML i PHP, utilizate la gestiunea paginilor web. Odat cu dezvoltarea sistemelor personale (PC), au aprut si primele aplicaii de baze de date care foloseau un singur fiier pentru a stoca toat informaia din baza de date. Ele erau de tip XBase, un limbaj care s-a rspndit foarte repede. Sistemele care l-au folosit au fost dBase, FoxBase, FoxPro, care funcionau sub sistemul MS-DOS. Cea mai cunoscut aplicaie care folosea limbajul XBase a fost FoxPro, sistem dezvoltat de firma Fox Software. Astzi, urmaul modern al acestui system este Visual FoxPro i a fost creat de ctre Microsoft. i n zilele noastre exist utilizri care stocheaz date n baze de date FoxPro, cel mai cunoscut exemplu este cel gestiunii activitilor la Euro Tunel, cuprinznd volume de date de cteva sute de GB. La nceputul anilor 90, Microsoft Corporation a lansat aplicaia Access, care se bazeaz n mare parte pe logica de stocare a sistemului FoxPro, sistem care fusese achiziionat n 1989. Aplicaia Access a devenit, n scurt timp, cea mai folosit pentru gestiunea bazelor de date de pe sistemele personale. Ajuns acum la versiunea 13 (denumit 2010), este pregtit s fie transferat oricnd6

ctre o baz de date de tip Microsoft SQL Server, care beneficiaz de faciliti de access multiutilizator, n reelele de calculatoare i reele web. n prezent, bazele de date i sistemele software care trebuie s le gestioneze se orienteaz tot mai mult spre posibilitatea de a avea acces la informaiile stocate prin internet i reelele de calculatoare. 1.3.2. Ce este o baz de date? Concepte generale. O baz de date1 este o colecie structurat de informaii [1], organizate dup un set de reguli, nregistrate pe un suport accesibil cu ajutorul calculatorului personal, care satisface simultan cerinele unei categorii de utilizatori, n mod selectiv i n timp util. Din punct de vedere al frecvenei actualizrii i accesrii, bazele de date pot fi clasificate n dou categorii: baze de date statice (de arhivare) n care informaiile nu sunt prelucrate n vreun fel i odat introduse acestea nu se mai modific, se adaug doar date noi la cele existente; baze de date dinamice n care informaiile se modific permanent n timp i care necesit o permanent prelucrare.

Exemple Colecia crilor dintr-o bibliotec, a CD-urilor cu muzic sau a DVD-urilor cu filme Evidena elevilor, a cadrelor didactice i a disciplinelor dintr-o unitate colar Evidena candidailor la concursul de admitere la o facultate Gestiunea bunurilor unei organizaii (obiecte de inventar, mijloace fixe etc.) Gestiunea produselor comercializate de ctre o societate comercial Gestiunea produciei de bunuri ntr-o unitate de producie Evidena personalului unei organizaii sau a unei societi comerciale Colecia electronic a ediiilor unor ziare Gestiunea parcului auto al unei societi comerciale i multe altele. O baz de date modern, care se afl n pas cu cerinele lumii informatice de astzi nu conine numai informaiile propriu-zise, ci i regulile de gestionare ale acestora (cum ar fi proprieti ale datelor, reguli de validare a datelor etc.) precum i obiectele necesare acestei gestionri (cum ar fi interogri, formulare, rapoarte, macrocomenzi, acces web etc.). Pentru ca o baz de date s poat fi construit, utilizat i prelucrat, ntr-un cuvnt gestionat de ctre utilizatorul uman prin intermediul calculatorului este necesar un limbaj de nivel nalt i o1

lb.englez database, lb.francez base des donnes 7

interfa prin intermediul creia programatorul i utilizatorul obinuit s comunice cu baza de date. Acest limbaj se mai numete generic Limbajul de descriere, manipulare i prelucrare a datelor (LDD, LMD, LPD) i este un sistem de programe specializat, proiectat n acest scop, care poart numele de Sistem de Gestiune a Bazelor de Date (SGBD), n limba englez DataBases Management System (DBMS), n limba francez Systme de Gestion des Bases des Donnes. Relaiile structurale dintre baza de date fizic, sistemul de gestiune i diferiii utilizatori sunt prezentate schematic n figura 1.

Fig. 1 Relaiile dintre baza de date (BD), sistemul de gestiune (SGBD) i utilizatori Analiznd diferitele modele de baze de date dezvoltate de-a lungul istoriei, se constat c, n prezent, cel mai utilizat model este cel relaional. Un model relaional de baz de date are ca fundament [1] structuri de date numite tabele n care sunt stocate informaiile propriu-zise. ntre tabele se stabilesc relaii de asociere, a cror existen este guvernat de un set de reguli precise. Informaiile cuprinse n tabele pot fi controlate de restricii algebrice.

8

Tabelul (tabela)2 este o structur de tip matrice, n care informaiile sunt aezate pe rnduri i coloane. Coloanele definesc tipul informaiei, iar rndurile, valorile acesteia. O coloan, coninnd un anume tip de informaie, se numete cmp3. Un rnd, coninnd valorile corespunztoare tuturor coloanelor, se numete nregistrare4. Fiecare cmp al tabelului are o denumire prin intermediul creia va putea fi identificat atunci cnd se face referire la el. Lista cmpurilor tabelului formeaz structura acestuia, care este stabilit de ctre proiectantul bazei de date dup nite reguli specifice.

Exemplul 1: Evidena elevilor clasei Cod_unic 1001 1002 1003 Nume Anghelescu Dima Dumbrava Prenume Ion Paul Robert Mihaela Ioana Data_nasterii 10/04/2003 5/04/2003 22/03/2003

Tabelul prezentat mai sus se bazeaz pe o structur compus din 4 cmpuri (coloanele tabelului), avnd denumirile conform antetului su i conine 3 nregistrri (liniile tabelului). n fiecare cmp se memoreaz date de un anumit tip. De exemplu, cmpul Nume conine date de tip ir de caractere, iar cmpul Data_nasterii date calendaristice. Este important ca un cmp al tabelului s gestioneze o informaie care reprezint un singur atribut (caracteristic) a clasei de obiecte respective. n exemplu 1 exist cmpuri separate care gestioneaz numele i prenumele elevului, deoarece acestea sunt 2 atribute diferite ale acestuia. Valorile atribuite cmpurilor unui tabel al bazei de date trebuie s aib anumite proprieti care sunt stabilite la definirea structurii tabelului. Aceste proprieti se refer la tipul de dat (numeric, text etc.), la domeniul permis al valorilor, la anumite restricii privind valorile introduse etc. Posibilitile de control al acestor proprieti depind n mare msur de SGBD-ul ales pentru gestionarea bazei de date. n orice tabel al unei baze de date fiecare nregistrare trebuie s fie unic, adic valorile cmpurilor acesteia s difere de ale celorlalte nregistrri pentru cel puin unul dintre cmpuri. Dup valoarea din acel cmp, nregistrarea respectiv poate fi identificat. Acel cmp poart denumirea generic de cheie de identificare sau cheie primar5. n exemplul 1, cmpul Cod_unic este cel mai indicat s fie cheia primar, deoarece trebuie s fie un cod unic atribuit fiecrui elev. n acest caz, acesta ar putea fi codul numeric personal, numrul matricol etc.

2 3

lb.englez table, lb.francez table lb.englez field, lb.francez champ 4 lb.englez record, lb.francez enregistrement 5 lb.englez primary key 9

n cadrul modelului relaional, ntre informaiile stocate n diferitele tabele ale bazei de date se stabilesc relaii de asociere. Acestea constituie unul dintre elementele de esen conceptual a modelului relaional al bazelor de date. Pe baza acestui model s-au dezvoltat i astzi sunt n puternic dezvoltare Sistemele de Gestiune a Bazelor de Date Relaionale (SGBD-R) Relational Database Management Systems (R-DBMS), din care face parte i Microsoft Access. O relaie se stabilete ntre dou tabele ale bazei de date, prin intermediul a dou cmpuri, cte unul din fiecare tabel, adic este de tip binar (cmp cmp). Exist [1], [3] trei tipuri de relaii: Relaie unu-la-unu (one-to-one) 1:1 n care unei nregistrri din primul tabel (partea stng) i corespunde o singur nregistrare n cellalt tabel (partea dreapt) i reciproc; Relaie unu-la-mai muli (one-to-many) 1:N n care unei nregistrri din primul tabel i pot corespunde mai multe nregistrri n cellalt tabel i fiecrei nregistrri din al doilea tabel i corespunde numai una n primul tabel; primul tabel se numete tabel primar sau Parent (printe), iar cel de-al doilea, tabel corelat sau Child (copil); Relaie mai muli-la-mai muli (many-to-many) M:N n care unei nregistrri din primul tabel i pot corespunde mai multe nregistrri n cellalt tabel i reciproc.

Cmpurile prin care se stabilesc relaii ntre dou tabele ale bazei de date trebuie s conin acelai tip de dat. n figura 2 se prezint schematizat cele 3 tipuri de relaii, prin similitudine cu mulimile utilizate n matematic. Mulimea tuturor relaiilor stabilite ntre tabelele unei baze de date formeaz schema relaional6 a bazei de date care este gestionat ntr-o zon dedicat a unui SGBD.

Relaia 1-1

Relaia 1-N Fig.2 Tipuri de relaii ntre tabele

Relaia M-N

6

lb.englez relationships scheme 10

Exemplul 2: Baz de date relaional pentru evidena elevilor pe clase

Pentru gestionarea informaiilor referitoare la elevii dintr-o unitate colar se poate proiecta o structur de date relaional care s conin, pentru nceput, 2 tabele: 1. Clase (cod, nivel, litera, anul); 2. Elevi (cod_unic, cod, nume, prenume, data_nastere) Informaiile sunt separate n dou clase de obiecte: clasele i elevii. Tabelul Clase conine informaiile despre structura de clase a unitii Clase Elevi colare i are 4 cmpuri. Cmpul cod 1:N cod cod_unic reprezint un cod unic de identificare a nivel cod fiecrei clase n baza de date i este cheia litera nume primar a tabelului Clase. De obicei, anul prenume structura valorilor unui cmp de acest tip data_nastere se alege astfel nct fiecare caracter s aib o semnificaie. De exemplu, valorile propuse pentru acest cmp (vezi tabelul Fig.3. Reprezentarea relaiei ntre dou tabele de mai jos) pot avea o structur cu urmtoarele semnificaii: prima cifr s reprezinte nivelul clasei a doua cifr s reprezinte litera celelalte dou s reprezinte ultimele dou cifre din an. Tabelul Elevi conine informaiile despre elevi i are 5 cmpuri. Pentru a identifica n mod unic un elev, se utilizeaz cmpul cod_unic care poate fi CNP-ul sau numrul matricol. n mod natural, exist mai muli elevi ntr-o clas, adic unei nregistrri din tabelul Clase i corespund mai multe nregistrri n tabelul Elevi. n consecin, relaia de legtur dintre cele dou tabele va fi de tipul 1:N, prin intermediul cmpului cod, care are aceeai semnificaie n ambele tabele. Relaia se reprezint n schema conceptual a bazei de date printr-o sgeat (fig.3). n mod normal, nu trebuie s existe elevi nregistrai care s nu aparin niciunei clase adic s nu existe nregistrri n tabelul Elevi care s nu aib nregistrare corespondent n tabelul Clase. O astfel de cerin poate fi controlat automat ntr-un SGBD relaional, prin configurarea n mod corespunztor a relaiei dintre cele dou tabele. Tabelul clase cod nivel litera anul 1110 I A 2010 1210 I B 2010 2110 II A 2010 2210 II B 2010 ... ... ... ... Bineneles c structura propus mai sus poate fi dezvoltat ulterior prin adugarea altor tabele care s memoreze informaii despre alte clase de obiecte cum ar fi cadrele didactice, slile11

de clas, inventarul, disciplinele etc. sau prin adugarea de noi informaii n cele dou tabele, cum ar fi completarea tabelului Elevi cu date despre adres, telefoane, prini etc. De exemplu, n structura prezentat anterior se adaug un tabel care s gestioneze informaiile despre cadrele didactice. Acesta va putea fi legat printr-o relaie de tabelul Clase prin atribuia cadrului didactic n raport cu clasa de elevi (nvtor sau diriginte). Pentru gestiunea informaiilor referitoare la situaia colar a elevilor, baza de date se completeaz cu nc dou tabele, Discipline i Catalog, pentru a memora datele care se refer la aceste dou clase de obiecte. Schema structural a noii baze de date, completat cu cele sus-menionate este prezentat n figura 4. Clase cod nivel litera anul cod_cd Elevi cod_unic cod nume prenume data_nastere

Atributii_clasa cod_clasa cod_cd atributie

Catalog cod_elev cod_disc semestru nota data

Cadre_did cod_unic nume prenume grad_did

Discipline cod_disc denumire nivel

Fig.4. Schema structural a bazei de date Gestiune colar n schema prezentat n figura 4, pentru regsirea corect a informaiilor corelate se stabilesc relaii de legtur ntre tabele, dup cum urmeaz: fiecare elev aparine unei singure clase i, n mod natural, o clas are mai muli elevi, adic relaia dintre cele dou tabele este de tip 1:N; fiecrei clase i se asociaz un cadru didactic prin intermediul tabelului Atributii_clasa, prin precizarea atribuiei (nvtor, diriginte); n mod natural, un elev are mai multe note n catalog, iar la o disciplin sunt acordate mai multe note elevilor, adic relaiile care se stabilesc ntre tabelele Elevi i Catalog, respectiv, ntre tabelele Discipline i Catalog, sunt de tipul 1:N.

12

n cadrul modelului relaional, pe lng tabele cu structuri definite i relaii de asociere ntre acestea, trebuie s existe i un set de reguli de gestionare a informaiilor, de prelucrare a acestora. Aceste reguli sunt cuprinse n limbajul de prelucrare a datelor (LPD) care se mai numete i limbaj de cereri (n majoritatea cazurilor baza de date este utilizat pentru a solicita a cere ulterior informaii de un anumit tip). Odat cu apariia i dezvoltarea SGBD-R, au aprut i diverse tipuri de LPD, majoritatea bazate pe operatori matematici ai algebrei relaionale. Unele dintre acestea sunt specifice numai anumitor SGBD-R, altele au devenit un standard i formeaz nucleul mai multor SGBD moderne. Limbajul SQL (Structured Query Language7) este cel mai cunoscut n domeniu i reprezint nucleul multora dintre SGBD actuale [1]. El a fost introdus prima dat n 1974, de ctre IBM Research Laboratory, San Jose, California, iar n anul 1986 a fost recunoscut de ctre ANSI ca standard internaional. SQL reprezint un set de instruciuni, majoritatea direct executabile, care utilizeaz operatori din algebra relaional (produs cartezian, reuniune, intersecie, diferen), dar i operatori care definesc funcii agregat, folosite n teoria mulimilor (numrare Count, medie aritmetic Avg, suma Sum, valoare minim Min etc.). Instruciunile din SQL pot fi incluse i n alte limbaje de programare (Basic, C, Cobol, Fortran etc.), astfel nct aplicaiile scrise cu aceste limbaje pot interaciona cu bazele de date prin intermediul unui SGBD-R care include i SQL. De asemenea, prin SQL se asigur compatibilitatea ntre diversele aplicaii cu baze de date, create cu diverse SGBD-R, care trebuie s aib inclus acest limbaj n componena lor. 1.3.3. Interfaa principal a sistemului Microsoft Access 2007. Dintre sistemele de gestiune a bazelor de date relaionale existente astzi, Access este unul dintre cele mai complete i performante. El nu este un simplu SGBD, ci este un mediu complex de dezvoltare de aplicaii pentru baze de date, construit pe principiile arhitecturii deschise. Microsoft Access (pe scurt Access) este integrat n pachetul Microsoft Office, avnd faciliti corespunztoare de interaciune cu celelalte module (Word, Excel, Outlook etc). Access ncorporeaz un maximum de posibiliti de abordare a unei baze de date, avnd integrate cele mai importante modele de proiectare a acesteia. Sistemul Access, ajuns n prezent la versiunea a treisprezecea, ofer un set complet de instrumente, unele suficient de sofisticate pentru programatorii profesioniti, altele uor de folosit de ctre utilizatorii noi. Cu Access , orice utilizator i poate crea soluiile cele mai convenabile prin care accesul, organizarea i distribuia informaiilor ntr-o organizaie se poate face mai uor i mai sigur ca niciodat.7

rom. limbaj structurat de cereri 13

Sistemul Access cuprinde [2], [6], [7], [9], [14] urmtoarele componente principale (fig.5): un modul SGBD-R performant, care include dou dintre cele mai cunoscute limbaje de prelucrare a datelor, QBE (Query-by-Example) i SQL (Structured Query Language); n acest modul se creaz tabelele de date i se gestioneaz informaiile; un modul VBA care include un limbaj procedural de programare independent, VBA (Visual Basic for Applications), utilizabil pentru dezvoltarea de aplicaii; un limbaj macro procedural simplificat, cu ajutorul cruia se pot proiecta aanumitele macrocomenzi, deosebit de utile n etapele de administrare a bazei de date; un set de instrumente pentru dezvoltare rapid a interfeei baz de date utilizatori obinuii (formulare, rapoarte, panouri de comand); un set de instrumente pentru asigurarea interfeei Access alte medii (conversii de date, transfer de date n/din, securitate, acces prin Web, compatibiliti etc.); un set puternic de instrumente de asisten interactiv (wizards) pentru dezvoltarea uoar a aplicaiilor. n Access, termenul de baz de date nu se refer numai la datele propriu-zise, ci cuprinde i alte obiecte cum ar fi formularele, interogrile (cereri), rapoartele, panourile de comand, macrocomenzile i modulele de aplicaii VBA. O caracteristic specific, deosebit de alte SGBD cunoscute, este faptul c tabelele de date mpreun cu toate obiectele de gestionare sunt memorate ntr-un singur fiier. Acest lucru asigur un control mai eficient al aplicaiilor care privesc o anumit baz de date.

Instrumente de gestionareQueries, Forms, Reports Relationships QBE / SQL Language

AsistenWizards Help

MacrocomenziMacros language

Tabele de dateTables

UtilitiConversii de date Pagini de access Web Securitate date ntreinere fiiere

Module de aplicaiiVBA language

Fig.5 Elementele componente ale sistemului Microsoft Access

14

Modulul de baz, n care se definete i se gestioneaz colecia de date, cuprinde urmtoarele tipuri de obiecte principale: tabele de date (Tables), conin informaiile propriu-zise sub form de nregistrri; pentru regsirea corect a informaiilor corelate ntre tabele se stabilesc relaii de legtur (Relationships); interogri (cereri) (Queries), rezolv solicitri ale utilizatorilor privind afiarea selectiv a informaiilor i prelucrarea acestora; formulare (forme) (Forms), afieaz informaiile ntr-un format anume, constituind o interfa ntre colecia de date i utilizatorul obinuit; un tip special de formulare sunt panourile de comand (Switchboard), prevzute, de obicei, cu butoane prin intermediul crora utilizatorul obinuit utilizeaz obiectele bazei de date; rapoarte (Reports), servesc la tiprirea la imprimant a informaiilor, ntr-un format anume, constituind interfaa ntre colecia de date i imprimant; macrocomenzi (Macros), sunt formate dintr-o succesiune de aciuni printr-un buton de aciune de pe un formular. ncepnd cu versiunea 2007, Microsoft a creat o nou interfa utilizator pentru programele din pachetul Office, care permite o utilizare mai facil, intuitiv i simpl a tuturor opiunilor. n Access 2007, la lansarea aplicaiei (Start Programs Microsoft Office Microsoft Office Access) se afieaz o fereastr de nceput (Getting Started, fig.6) din care utilizatorul poate alege opiunea de ncepere a lucrului. Exist dou posibiliti principale: deschiderea unei baze de date existente sau crearea unei noi baze de date. Pentru deschiderea unei baze de date existente, n partea dreapt a ferestrei este afiat o list a ultimelor fiiere deschise anterior n care, eventual, poate fi gsit fiierul dorit. Se poate afia fereastra de acces la un fiier (Open) prin selectarea opiunii More Pentru crearea unei noi baze de date exist dou posibiliti: New Blank Database, care deschide un fiier gol n care se vor proiecta, pe rnd, componentele bazei de date; Templates, care permite alegerea unui ablon de structur a bazei de date dintre cele oferite de ctre sistem; n acest caz se va crea un fiier coninnd o serie de componente (tabele, formulare etc) fr informaii, acestea urmnd s fie adugate de ctre utilizator. efectuate asupra

obiectelor bazei de date printr-o singur comand, dat dintr-un singur loc, de obicei,

15

Baz de date goal

Baz de date din ablon Deschidere baz de date

Fig.6 Fereastra de nceput Microsoft Office Access Fereastra principal a programului Access este prezentat n fig.7. Aa dup cum se observ din figur, fereastra conine mai multe componente, dup cum urmeaz: butonul Microsoft Office, utilizat pentru diferite aciuni de gestiune a fiierelor; butonul deschide un meniu de opiuni (fig.8) New/Open/Convert/Save/Save As/Print/Manage/ Email/Publish/Close inclusiv o list a celor mai recent deschise fiiere; bara de selecie rapid a comenzilor (Quick Access Toolbar), amplasat n stnga-sus, care poate fi particularizat dup dorina utilizatorului (fig.9); panoul de afiare a obiectelor bazei de date (Navigation Pane), care permite accesul la oricare dintre acestea i totodat poate furniza informaii despre ele; panglica cu pictograme care, la versiunea 2007 a pachetului Microsoft Office, nlocuiete bara de meniuri prezent la versiunile anterioare; aceast panglic constituie cea mai important inovaie a pachetului; este compus din mai multe pagini suprapuse: patru tipuri de baz (Home/Create/External Data/Database Tools) i una/dou pagini contextuale, particularizate automat n funcie de obiectul activ la un moment dat; zona de lucru n care sunt afiate ferestrele obiectelor active (deschise) la un moment dat, n funcie de cerinele utilizatorului bazei de date; bara de stare afiat pe ultima linie a ferestrei principale, care conine n dreapta cteva butoane de acces rapid la diferite forme de afiare a ferestrelor unui obiect; un buton de ajutor (Help), amplasat pe marginea din dreapta-sus a panglicii cu pictograme, marcat cu semnul ntrebrii (?).16

Gestiune fiiere

Bar comenzi rapide

Panou obiecte

Panglica cu pictograme

Zona de lucru (afiare)

Ajutor (Help)

Bara de stare

Fig.7 Fereastra principal a programului Microsoft Office Access2007

Fig.8 Butonul de gestiune fiiere

Fig.9 Bara rapid de comenzi

Panglica cu pictograme reprezint inovaia principal a pachetului Microsoft Office 2007. n acest loc se gsesc diferitele comenzi i opiuni de configurare. n figura 10 sunt prezentate paginile principale ale acesteia, aa cum sunt apar n Access. n fiecare pagin, opiunile sunt aezate pe categorii. Pagina Home conine opiunile de editare i formatare a textului, precum i cele referitoare la ddiferite aciuni cu nregistrrile unui tabel.17

Pagina Create conine opiunile de creare i execuie a diferitelor obiecte n baza de date (tabele, formulare, rapoarte, interogri, comenzi macro). Pagina External Data include opiunile de legtur a bazei de date Access cu alte tipuri de baze de date (import/export de date), inclusiv posibiliti de legare cu programul de pot electronic Microsoft Outlook sau cu cel de calcul tabelar Microsoft Excel. Pagina Database Tools conine opiunile de gestiune global a bazei de date cum ar fi securitatea datelor, modulul Visual Basic VBA, analiz conceptual etc. Pe lng cele 4 pagini standard, pot exista i una/dou pagini contextuale, care se adapteaz automat la tipul de obiect activ i la modul su de afiare, avnd opiunile potrivite n acest sens. De exemplu, n figura 9 este prezentat pagina Table Tools (Datasheet) care este asociat deschiderii unui tabel n modul de afiare a nregistrrilor. O alt caracteristic important este posibilitatea particularizrii interfeei principale a programului Access, n funcie de cerinele sau dorinele utilizatorului. Opiunile de particularizare sunt disponibile ntr-o fereastr specific, fiind grupate pe categorii, fereastr accesibil de la butonul Office Access Options. Home

Create

External Data

Database Tools

Table Tools (Datasheet)

Fig.10 Paginile panglicii cu pictograme i controale disponibile n Access 200718

Pentru a asigura o anumit compatibilitate ntre diferitele versiuni ale programului, instalate pe diferite calculatoare, Access verific aceast caracteristic automat la deschiderea unui fiier de tip baz de date. n acelai timp, se ofer posibilitatea memorrii unei baze de date n diverse versiuni anterioare, verificndu-se automat compatibilitatea cu versiunea 2007 i informnd utilizatorul asupra eventualelor caracteristici incompatibile. Aceast opiune este disponibil la butonul Office Save As (fig.11). Deschiderea unui fiier de tip baz de date Access se poate efectua n mai multe moduri, disponibile la butonul Office Open (fig.12): Open deschiderea fiierului, avnd toate drepturile asupra obiectelor acestuia (citire/scriere), n acelai timp fiierul poate fi deschis i de ali utilizatori pentru citire/scriere; Open Read-Only deschiderea fiierului n modul citire, nu se pot face modificri ale obiectelor create, n acelai timp ali utilizatori pot deschide fiierul n modul citire/scriere; Open Exclusive deschiderea fiierului cu drepturi exclusive asupra acestuia, adic ali utilizatori nu vor avea acces la fiier ct timp este deschis exclusiv; Open Exclusive Read-Only deschiderea fiierului exclusiv n modul citire, toi utilizatorii care deschid fiierul ulterior, vor putea doar consulta baza de date, fr a putea efectua modificri ale structurii obiectelor create.

Fig.11 Fereastra Save As

Fig.12 Opiunile de deschidere Open

Pentru a obine ajutor sau explicaii diverse n legtur cu o anumit tem, este disponibil fereastra Help, care se deschide prin apsarea butonului de ajutor, marcat ?, amplasat n extrema dreapt a panglicii cu pictograme. n fereastra de ajutor se poate cuta tema dorit ntr-o caset specific alegnd dintre opiunile afiate ca rezultat al cutrii (Search).

19

TEMA NR. 2 GESTIUNEA INFORMAIILOR N TABELELE UNEI BAZE DE DATE2.1. Competene profesionale oferite a crea, deschide i memora fiiere de tip baz de date Access a crea tabele noi ntr-o baz de date a stabili proprieti principale ale cmpurilor unui tabel a afia, aduga, terge nregistrri n tabele a lucra cu diferitele moduri de afiare a unui tabel a ordona i afia selectiv nregistrrile n tabele. 2.2. Obiectivele temei nsuirea modului de creare a unui tabel i a structurii de cmpuri a acestuia deprinderea de a lucra cu tabele n diferite moduri de afiare a acestora deprinderea de a aduga, afia, terge nregistrri n tabele i de a modifica aspectul de afiare a nregistrrilor nelegerea noiunii de proprietate a cmpului i stabilirea valorilor adecvate pentru proprietile de baz ale cmpurilor deprinderea de a ordona i a afia selectiv nregistrrile unui tabel. 2.3. Coninutul detaliat 2.3.1. Crearea tabelelor n bazele de date Access. Lucrul cu nregistrrile. Tabelele unei baze de date reprezint obiectele n care sunt memorate coleciile de date propriuzise. Un tabel Access are o structur asemntoare cu o foaie de calcul Excel, coninnd coloane, denumite cmpuri i linii, denumite nregistrri (vezi tema nr.1). Tabelele coninute ntr-o baz de date Access sunt afiate pe panoul cu obiecte (vezi fig.6), n grupul Tables. Un tabel poate fi afiat sub mai multe forme: afiarea nregistrrilor, modul proiectare, modul tabel pivot sau grafic pivot (datasheet/design/pivot table/pivot chart). Crearea unui tabel nou se face prin selectarea paginii Create a panglicii cu pictograme i alegerea uneia dintre cele 4 opiuni disponibile: Table crearea tabelului n modul de afiare a nregistrrilor; cmpurile tabelului se creaz pe msur ce se introduc valorile primei nregistrri (vezi exemplul nr.4); Table Templates crearea tabelului pe baza unei structuri predefinite selectate dintr-o list de abloane oferite de ctre sistem; SharePoint Lists crearea unui tabel pe un site de tip SharePoint i a unui link ctre acest tabel n baza de date curent;20

Table Design crearea tabelului n modul de proiectare, prin definirea listei cmpurilor i, stabilirea, dup necesiti, a proprietilor acestora (vezi exemplu nr.5). Dintre cele 4 opiuni menionate anterior, probabil cel mai des se utilizeaz prima i ultima. Din acest motiv, acestea sunt prezentate mai detaliat n continuare. Crearea unui tabel prin introducerea direct a nregistrrilor (opiunea Create Table) este cea mai simpl metod. Se introduce prima nregistrare n tabel ca ntr-o foaie de lucru Excel (fig.14). Fiecare coloan primete un nume predefinit (Field1, Field2, ... etc), existnd i o coloan implicit cu numele ID, care este de fapt propus ca i cheie primar a tabelului i are un anumit tip de dat potrivit pentru aceast caracteristic. Ultima coloan din dreapta, denumit Add New Field este afiat permanent i ofer posibilitatea adugrii unui nou cmp n tabel prin simpla completare a unei valori la oricare nregistrare existent. Dup introducerea a cel puin o nregistrare, pentru a face permanent structura tabelului, se alege opiunea de memorare (Save / Ctrl+S) i se indic un nume pentru tabelul respectiv (n fig.14 Elevi). Dup crearea tabelului se pot efectua oricnd modificri ale structurii acestuia, fie prin adugarea de valori ale nregistrrilor pe coloana Add New Field, fie prin deschiderea ferestrei de proiectare (Table Design) care asigur accesul la proprietile cmpurilor.

Exemplul 4: Crearea unei baze de date pentru evidena elevilor din unitatea colar, pe baza structurii prezentate n schema din fig.3. Pentru crearea fiierului bazei de date efectuai urmtoarele aciuni:

deschidei programul Microsoft Access i creai o baz de date nou, alegnd opiunea New Blank Database i indicnd numele noului fiier Elevii scolii precum i locul n care acesta va fi creat pe calculator (fig.13), apsai butonul Create pentru a crea baza de date i a deschide interfaa principal a programului; baza de date va fi creat ntr-un singur fiier, cu numele Elevii scolii.accdb, extensia .accdb indicnd c este vorba de un fiier creat n versiunea Access2007; n fereastra de afiare a nregistrrilor unui nou tabel, cu numele implicit Table1, introducei 3 nregistrri conform fig.14, ncepnd din coloana Add New Field; deplasarea de la o coloan la alta se face cu tasta Tab; la terminarea unui rnd (nregistrare) se apas tasta Enter pentru a trece la rndul urmtor; memorai structura creat (Save, Ctrl+S) cu numele Elevi. Obs. n cazul editrii datelor calendaristice trebuie s se in seama de modul de interpretare a acestora de ctre sistemul de operare Windows: sistemul anglo-saxon: sub forma LL/ZZ/AANN, adic 12/31/2001 (31 decembrie 2001) sistemul romnesc: sub forma ZZ/LL/AANN, adic 31/12/2001.21

Fig.13 Crearea unei noi baze de date cu opiunea New Blank Database

Opiuni de afiare

Modificare cmpuri

Proprieti ale cmpurilor

Butoane de navigare

Opiuni de afiare

Fig.14 Crearea tabelului Elevi n fereastra nregistrrilor22

n figura 14, opiunile de pe panglica cu pictograme sunt specifice lucrului cu nregistrrile, care se gsesc n pagina contextual Datasheet (ultima din dreapta). n aceast fereastr se pot efectua o serie de aciuni care se refer att la structura tabelului, ct i la modul de afiare a cmpurilor i nregistrrilor. Modificrile necesare a fi efectuate n structura tabelului Elevi, creat la exemplu nr.4, sunt explicate la exemplu nr.6. Crearea unui tabel n fereastra de proiectare (Create Design View) permite definirea direct a cmpurilor numele (Field Name), tipul de dat asociat (Data Type), precum i celelalte eventuale proprieti inclusiv caracteristica de cheie primar.

Exemplul 5: Completarea bazei de date Elevii scolii cu al doilea tabel, denumit Clase,

conform structurii prezentate n schema din fig.3. Se efectueaz urmtoarele aciuni: alegei pagina Create, apoi Table Design; completai n partea superioar a ferestrei de proiectare numele (Field Name), tipul de dat (Data Type) i descrierea (Description) celor 4 cmpuri necesare, conform fig.15; deocamdat nu modificai nimic n zona de afiare a proprietilor cmpurilor (Field Properties); n cazul cmpului cod, selectai pictograma Primary Key pentru a stabili caracteristica de cheie primar i observai simbolul cheie afiat n stnga numelui acestuia; memorai tabelul cu numele Clase; observai, n dreapta jos, zona de comentarii de ajutor care se modific contextual, n funcie de componenta activ la un moment dat n fereastra de proiectare.

Fig.15 Fereastra de proiectare a tabelului Carti23

pentru a aduga nregistrri n tabelul Clase alegei fereastra nregistrrilor (Datasheet View) din lista oferit de pictograma View (stnga sus) a panglicii; adugai cele 8 nregistrri conform figurii 16 i nchidei tabelul; observai c nregistrrile se memoreaz automat dup ce au fost adugate, fr a fi necesar o aciune de memorare (Save) specific.

Fig.16 Adugarea nregistrrilor n tabelul Clase Access permite i importul de date din alte surse externe de diferite tipuri cum ar fi: alte baze de date Access, fiiere Excel, fiiere de tip dBase, Paradox, Lotus, XML, text etc. Opiunile de import sunt accesibile din pagina External Data, zona Import. Datele importate pot fi aduse ntr-un tabel existent al bazei de date curente, ntr-un nou tabel sau se creaz doar un link ctre fiierul original. n fereastra de afiare a nregistrrilor unui tabel (Datasheet View) se pot efectua o serie de aciuni care se refer la structura tabelului sau la nregistrrile acestuia. Aceste aciuni sunt: Adugarea de noi nregistrri, utiliznd butonul New Record aflat n zona butoanelor de navigare (fig.14), n partea de jos a ferestrei Datasheet View i tastele Tab / Shift+Tab pentru parcurgerea cmpurilor de la stnga la dreapta i, respectiv, invers; tergerea unei nregistrri se face prin selecia rndului care o conine i alegerea din meniul contextual (click-dreapta mouse) a opiunii Delete Record. Se cere confirmarea aciunii, deoarece dup tergere, nregistrarea nu se mai poate recupera (nu exist n acest caz opiunea Undo!); Parcurgerea nregistrrilor cu tasta Tab / Shift+Tab pentru pacurgerea cmpurilor unei nregistrri sau cu butoanele de navigare (fig.17) pentru parcurgerea nregistrrilor de la una

24

la alta succesiv sau efectuarea unui salt la prima sau la ultima; trebuie menionat c numrul curent al nregistrrii (7 of 15) este o informaie pur formal deoarece ordinea de afiare a nregistrrilor poate fi schimbat dup cerina utilizatorului; Prima Precedenta Curent Urmtoare Ultima Nou Filtru Cutare

Fig.17 Butoanele de navigare / cutare / afiare filtre Modificarea aspectului de afiare a nregistrrilor (font/fond/culori/alte opiuni) se realizeaz cu opiunile specifice din pagina Home Font; Modificarea dimensiunilor coloanelor (Column Width) sau a liniilor (Row Height) se poate face dinamic, cu ajutorul mouse-ului, din meniul contextual (click-dreapta) sau din pagina Home Records More; Ascunderea sau nghearea unui cmp Hide/Unhide / Freeze/Unfreeze, se realizeaz din pagina Home Records More sau prin click-dreapta de mouse pe numele cmpului (fig.18); Cutarea/nlocuirea unei valori a unor nregistrri se efectueaz cu opiunile din pagina HomeFind; Adugarea, tergerea sau redenumirea Fig.18 Meniu contextual la cmpuri unui cmp Insert / Delete / Rename se realizeaz prin selecia numelui cmpului i alegerea opiunii dorite din meniul contextual (click-dreapta); Aplicarea unei ordonri sau a unui filtru se face cu opiunile din pagina HomeSort&Filter (vezi subcapitolul urmtor).

Exemplul 6: Efectuarea de modificri n structura unui tabel i adugare de nregistrri. deschidei tabelul Elevi n fereastra de proiectare (Design View) i redenumii cele 3 cmpuri Field1, Field2 i Field3 cu: nume, prenume, data_nasterii; selectai cmpul ID i tergei-l (Delete), confirmnd tergerea cu Yes; adugai dou cmpuri n tabel, cod_unic i cod, (fig.19);

25

memorai modificrile (Save), afiai fereastra nregistrrilor (Datasheet View) i adugai informaiile, conform fig.20;

Fig.19 Modificri n fereastra de proiectare a tabelului Elevi

Fig.20 Completarea cmpurilor cod_unic i cod dup crearea acestora i adugarea de nregistrri n tabelul Elevi26

n anexa nr.2 se gsesc listele elevilor repartizai pe clasele codificate n figura 16, nregistrri care sunt incluse n baza de date pe care sunt exemplificate aplicaiile. Obs. Pentru mrirea spaiului de lucru se poate ascunde fereastra panoului de obiecte Access prin apsarea pe butonul specific din bara de titlu a acestuia. Afiarea obiectelor pe panoul de navigare poate fi efectuat n diverse moduri la alegerea utilizatorului prin selecia dintr-o list pus la dispozitie pe panou. De exemplu, dac se aleg opiunile Object Type i All Objects, obiectele vor fi afiate pe panou grupate pe tipuri tabele, interogri, formulare etc. i vor fi afiate toate tipurile de obiecte existente n baza de date. Pentru a gestiona mai uor i mai sigur introducerea i prelucrarea informaiilor cuprinse n baza de date, se recomand configurarea unor proprieti adecvate ale cmpurilor. Elementele de baz privind aceast configurare sunt prezentate n subcapitolul urmtor. 2.3.2. Stabilirea proprietilor cmpurilor n bazele de date Access. Cmpurile unui tabel au o serie de proprieti care controleaz modul de comportare a datelor memorate. Aceste proprieti se configureaz n fereastra de proiectare a tabelului (Design View). Fiecrui cmp al tabelului i se definete un anumit tip de dat (Data Type). n anexa nr.1 sunt prezentate tipurile de date utilizabile n Access, mpreun cu principalele caracteristici ale acestora. Aceste caracteristici fac parte din grupul proprietilor cmpurilor i se configureaz n zona Field Properties. Valorile acestor proprieti pot fi selectate din liste de opiuni predefinite sau prin scrierea valorii corespunztoare de ctre utilizator n caseta specific. n cele ce urmeaz sunt descrise cele mai importante dintre proprietile cmpurilor care pot fi configurate n Access. Proprieti principale ale cmpurilor unui tabel Access. Field Size lungimea cmpului; numrul maxim de caractere sau valoarea maxim care se poate introduce ca valoare a cmpului; este specific doar pentru tipurile de dat Text, Memo, Number i Autonumber; Format formatul de afiare a datelor; proprietate general; se poate selecta un format predefinit sau se pot genera formate personalizate pe baza unor reguli utiliznd o serie de caractere dedicate (vezi exemplul nr.7); Decimal Places numrul de cifre zecimale afiate; este specific tipurilor de date numerice; nu afecteaz valoarea memorat, ci doar cea afiat;27

Input Mask

ablon (model, masc) de control; controleaz structura valorilor introduse la nivel de caracter; este o proprietate general (cu excepia Autonumber); se configureaz de ctre utilizator cu ajutorul unor caractere dedicate, eventual utiliznd asistena interactiv Input Mask Wizard ((vezi exemplul nr.7);

Caption

etichet de cmp; proprietate general; dac are valoare, n fereastra de afiare a nregistrrilor (Datasheet View), se afieaz n locul numelui cmpului (Field Name) ca denumire a coloanei corespunztoare;

Default Value Validation Rule

valoare prestabilit; proprietate general; este valoarea afiat automat n cmp la fiecare nou nregistrare; poate fi rezultatul unui calcul matematic; regul de validare; permite verificarea datelor introduse cu ajutorul unor restricii matematice; se poate genera manual sau cu ajutorul asistenei interactive (Builder Constructorul de expresii) (vezi exemplul nr.7);

Validation Text Required

mesaj de eroare; este afiat ntr-o caset de dialog atunci cnd datele introduse nu respect regula de validare (Validation Rule); valoare obligatorie; proprietate general; dac este activ (Yes), oblig introducerea unei valori n cmpul respectiv; dac este inactiv (No) se poate lsa cmpul gol;

Indexed

cmp indexat/neindexat; proprietate general; indexarea permite o gsire mai rapid a informaiei de ctre Access la tabele cu mii de nregistrri, pe baza unor algoritmi proprii; este activat automat pentru cmpurile cheie primar; se recomand activarea pentru acele cmpuri care constituie criterii de cutare frecvente;

Text Align

aliniere text; proprietate general; permite indicarea modului de aliniere a valorii scrise n cmp, exist opiunile General/Left/Center/Right/Distribute.

Pentru nelegerea rolului fiecrei proprieti n controlul comportrii valorilor introduse n cmpurile unui tabel, n exerciiul practic urmtor sunt incluse exemple de configurare a acestor proprieti.

Exemplul 7: Configurarea unor proprieti ale cmpurilor tabelelor Elevi i Clase. deschidei tabelul Clase n fereastra de proiectare, prin click-dreapta pe numele acestuia i Design View i modificai proprietile cmpurilor, conform tabelului nr.1; deschidei tabelul Elevi n fereastra de proiectare (Design View) i modificai proprietile cmpurilor, conform tabelului nr.2;

28

memorai modificrile (Save) pentru fiecare tabel i verificai comportarea datelor introduse n fereastra nregistrrilor (Datasheet); pentru a trece din fereastra nregistrrilor n cea de proiectare i invers utilizai opiunile View ale panglicii cu pictograme. Tabelul nr.1 Configurarea unor proprieti ale cmpurilor tabelului Clase

Numele cmpului cod

Proprietatea / Valoarea Field Size: 4 Input Mask: 0000

Observaii Deoarece valorile cmpului sunt formate din 4 cifre, pentru a controla aceast caracteristic se configureaz cele dou proprieti Valorile acestui cmp fac parte dintr-o list de valori: I, II, III, IV, ... n consecin, este potrivit stabilirea caracteristicii Lookup, adic o list de valori disponibil. Valorile acestui cmp pot fi A, B, C, D, ..., adic sunt formate dintro singur liter. Pentru a controla acest aspect, se stabilesc cele dou proprieti Field Size i Input Mask. Pentru afiarea valorii cu majuscule se stabilete formatul de afiare la Format Deoarece valorile sunt numere ntregi se stabilete dimensiunea corespunztoare, iar pentru a afia automat anul curent se utilizeaz un calcul matematic cu funcii predefinite

nivel

Field Size: 4 Lookup List: vezi mai jos

litera

Field Size: 1 Format: > Input Mask: L

an

Field Size: Integer Default Value: =Year(Date())

Configurarea caracteristicii Lookup pentru cmpul nivel al tabelului Clase Pentru configurarea unei liste de valori asociate unui cmp, Access pune la dispoziie o asisten interactiv (Wizard); n acest caz, aceasta se gsete n lista tipurilor de date (Data Type) Lookup Wizard, fig.21; n prima fereastr a asistenei, fig.21, se indic tipul listei de valori dintre cele dou posibiliti disponibile: n cazul cmpului nivel se indic a doua opiune I will type in the values... pentru a edita lista valorilor respective; se apas butonul Next pentru a trece la etapa urmtoare; n a doua fereastr a asistenei, fig.21, se editeaz valorile cuprinse n list: I, II, III, IV, ..., se apas apoi butonul Next pentru a trece mai departe; n ultima fereastr se poate da un nume listei sau se las numele prestabilit (nivel) i se apas butonul Finish pentru a termina configurarea listei;29

n zona proprietilor cmpului, Field Properties, n pagina Lookup se vor gsi toate caracteristicile listei respective, fig.22; n aceast pagin, dac este necesar, pot fi modificate aceste caracteristici (de exemplu, adugare de valori n list Row Source sau limitarea valorilor posibile a fi introduse doar la cele din list Limit to List: Yes).

Fig.21 Etapele de configurare a caracteristicii Lookup, lista de valori fixe n fereastra de proiectare a unui tabel, Design View, pe lng posibilitile de configurare a proprietilor cmpurilor sunt disponibile i o serie de alte aciuni, accesibile prin selecia pictogramei corespunztoare din panglica Design: stabilirea caracteristicii de cheie primar Primary Key; editarea unei relaii matematice cu ajutorul constructorului Builder; verificarea respectrii regulii de validare de ctre valorile introduse anterior Test Validation Rules; Fig.22 Pagina Lookup a cmpului nivel30

posibilitatea de a insera noi cmpuri sau de a terge din cele existente Insert / Delete / Lookup. Tabelul nr.2 Configurarea unor proprieti ale cmpurilor tabelului Elevi Numele cmpului nume prenume Proprietatea / Valoarea Observaii Valorile introduse vor putea avea maxim 100 caractere Pentru afiarea valorilor cu majuscule se stabilete proprietatea Format Afiarea valorilor se va face sub forma 20-Apr-2010 Valorile vor fi aliniate centrat (pe mijloc) Deoarece valorile cmpului sunt formate din 4 cifre, pentru a controla aceast caracteristic se configureaz cele dou proprieti Valorile acestui cmp trebuie s fie dintre cele disponibile ale cmpului cod din tabelul Clase fiecare elev aparine unei clase. Se configureaz lista de valori de tip Lookup, indicnd cutarea acestora n cmpul cod al tabelului Clase (vezi exemplul Lookup anterior)

Field Size: 100 Format: > Format: Medium Date Text Align: Center Field Size: 4 Input Mask: 0000

data_nasterii

cod_unic

cod

Lookup List: Clase.cod

Stabilirea unei caracteristici de tip Lookup a unui cmp prin preluarea valorilor disponibile ale altui cmp exemplul de mai sus al cmpului cod din tabelul Elevi care preia valorile cmpului cod al tabelului Clase creaz i o relaie simpl ntre cele dou tabele, evideniat n fereastra nregistrrilor tabelul Clase printr-un simbol specific, fig.23, poziionat la nceputul fiecrei nregistrri, care permite afiarea nregistrrilor tabelului Elevi, corelate cu nregistrarea corespunztoare din tabelul Clase. De exemplu, n figura 23 sunt afiai elevii clasei I-B, prin apsarea acelui simbol.

Fig.23 Afiarea nregistrrilor corelate pe baza relaiei31

2.3.3. Ordonarea i afiarea selectiv a nregistrrilor. n fereastra de afiare a nregistrrilor unui tabel (Datasheet View), acestea sunt afiate n mod implicit n ordine cresctoare dup valorile cmpului cheie primar al tabelului. Totodat, n mod implicit, sunt afiate toate nregistrrile coninute la un moment dat n tabelul respectiv. Pentru gsirea i afiarea numai a anumitor nregistrri, care conin anumite valori ale unor cmpuri, Access pune la dispoziie 2 posibiliti care se pot utiliza independent sau combinat. Acestea sunt afiarea ordonat a nregistrrilor i afiarea selectiv cu ajutorul filtrelor. Alegerea uneia dintre aceste aciuni se face din pagina HomeSort&Filter, fig.24, sau din meniul contextual (click-dreapta de mouse). Afiare list opiuni ordonare / filtrare Ordonare cresctoare Filtrare prin selecie

Ordonare descresctoare tergere ordonare Fig.24 Grupul opiunilor de ordonare i filtrare

Filtrare avansat (multipl) Eliminare / aplicare filtru

Afiarea ordonat a nregistrrilor se refer la afiarea acestora ntr-o anumit ordine, dup valorile unuia sau a mai multor cmpuri. Aciunea se realizeaz n dou etape: 1. indicarea cmpului (cmpurilor) dup a crui (cror) valoare trebuie s se fac ordonarea; 2. indicarea uneia dintre cele dou opiuni de ordonare (din pagina Home a panglicii, Sort Ascending (A to Z) / Descending (Z to A), din meniul contextual sau din lista de opiuni disponibil n lista derulant deschis prin apsarea pictogramei Filter. Dac se aleg mai multe cmpuri drept criterii de ordonare, atunci acestea trebuie aezate unul lng altul, n ordinea clasei de ordonare, de la stnga la dreapta. Ordonarea aplicat la un moment dat poate fi memorat, astfel nct la deschiderea ulterioar a tabelului respectiv, nregistrrile s fie afiate n ordinea respectiv. Dac nu se memoreaz nici un criteriu de ordonare, atunci nregistrrile sunt afiate n ordine cresctoare dup valorile cheii primare a tabelului (ordonare implicit). tergerea ordonrii particularizate i revenirea la ordinea implicit, dup valorile cheii primare a tabelului, se face prin apsarea butonului specific de pe panglic Clear All Sorts.32

Afiarea selectiv a nregistrrilor nseamn afiarea doar a anumitor nregistrri, care respect criteriile de selecie precizate de ctre utilizator. Aciunea se mai numete filtrare a nregistrrilor i se realizeaz n dou etape: 1. indicarea valorii (valorilor) de cmp dup care trebuie s se fac filtrarea; 2. aplicarea opiunii de filtrare prin alegerea din pagina Home Sort&Filter, din meniul contextual sau din lista derulant deschis prin apsarea butonului Filter. Opiunile disponibile pentru filtrare sunt urmtoarele: Selection, cu mai multe posibiliti, n funcie de tipul de dat al valorii selectate (text, numeric, dat calendaristic etc.), care permite selecia dup o singur valoare a unui cmp; Advanced, avnd 2 opiuni (Filter By Form / Advanced Filter/Sort), care permite selecia simultan dup valorile mai multor cmpuri (Filter By Form) sau crearea unei interogri (Advanced Filter/Sort) n care utilizatorul are mai multe posibiliti de filtrare i/sau ordonare; Filter, care deschide o list derulant de opiuni de filtrare i, de asemenea, cele de ordonare.

n cadrul ferestrei Filter opiunile de filtrare sunt contextuale, adic se modific n funcie de tipul de dat al cmpului dup care se face filtrarea (Text/Number/Date etc.). n fig.25 sunt prezentate opiunile de filtrare a valorilor de tip Text, Date/Time i Number. Dup crearea unei opiuni de filtrare, aceasta poate fi memorat (Save) n fereastra Datasheet a nregistrrilor astfel nct s poat fi aplicat direct oricnd ulterior. Aplicarea i eliminarea filtrului se efectueaz cu opiunea Toggle Filter. tergerea tuturor filtrelor existente la moment dat se realizeaz cu opiunea Advanced Clear All Filters. Una dintre inovaiile versiunii 2007 a programului Access o reprezint posibilitatea de a obine prelucrri statistice ale informaiilor cuprinse n nregistrri direct n fereastra nregistrrilor unui tabel. Aceast opiune este disponibil n pagina Home a panglicii, cu pictograma . Pe

ultima linie a ferestrei de afiare Datasheet apare rndul Total pe care se poate apela una dintre opiunile statistice disponibile n lista derulant (Sum, Average, Count, Maximum, Minimum, ...), care va prelucra valorile cmpului pe care este activat opiunea respectiv. n figura 26 este prezentat o prelucrare statistic utiliznd opiunea Count pentru a afia numrul elevilor dintr-o clas, afiai prin aplicarea unui filtru dup codul clasei.

33

Fig.25 Opiunile disponibile pentru aplicarea filtrelor pentru cele tipuri principale de date Text, Date/Time i Number

Fig.26 Prelucrare statistic a informaiilor cu opiunea Count34

Exemplul 8: Aplicarea unor opiuni de ordonare i selecie prin filtrare n baza de date Elevii scolii. Deschidei tabelul Elevi n fereastra nregistrrilor i efectuai cteva aciuni

de ordonare i filtrare dup cum urmeaz: ordonai nregistrrile n ordine alfabetic dup numele i prenumele elevilor selectai cele dou cmpuri din dreptul numelor acestora i apsai butonul Ascending (A/Z); ordonai cronologic nregistrrile dup data naterii elevilor selectai o valoare a cmpului data_nasterii i apsai butonul Ascending (A/Z); prima nregistrare va fi a celui mai btrn elev, iar ultima a celui mai tnr; afiai doar elevii care au prenumele Mihai selectai o valoare a cmpului prenume, apsai butonul Filter, alegei opiunea Text Filters Contains, scriei textul mihai i apoi apsai Enter (OK); putei elimina/re-aplica) filtrul prin apsarea butonului Toggle Filter; observai n partea de jos a ferestrei afiarea numrului de nregistrri i butonul Filtered care este apsat putei elimina/aplica filtrul i cu acel buton; eliminai filtrul aplicat anterior (Toggle Filter) nainte de a testa urmtorul filtru; apsai pictograma Totals i, n rndul Totals afiat, alegei opiunea Count pentru cmpul cod_unic, pentru a afia numrul nregistrrilor afiate; observai c valoarea se actualizeaz dup aplicarea / eliminarea unui filtru; selectai o valoare a cmpului cod, apsai butonul Filter, care deschide lista de opiuni observai c putei alege prin bifare valorile dup care s fie efectuat filtrarea de exemplu lsai bifate doar codurile claselor I (fig.27) i validai cu butonul OK pentru a aplica filtrul; selectai o valoare a cmpului data_nasterii pentru a testa (Filter) posibilitile de filtrare dup valori de tip dat calendaristic; alegei opiunea Date Filters Between i indicai perioada anul 2002, preciznd limitele acesteia (fig.28);35

Fig.27 Selecie multipl la filtre

Fig.28 Filtrare dup dat calendaristic

pentru a afia elevii claselor I care au prenumele mihai, deoarece este necesar selecia dup dou cmpuri, cod_unic i prenume, cea mai potrivit este opiunea Advanced FilterFilter By Form, n caseta aferent cmpului nume se scrie textul *mihai*, iar n cea a cmpului cod_unic se scrie textul 1* (fig.29) i se apas Enter, apoi se apas butonul Toggle Filter; Obs. Simbolul * este utilizat ca i caracter de nlocuire (joker); n acest caz, este configurat automat funcia Like; o alt modalitate de rezolvare a problemei anterioare este aplicarea succesiv a 2 filtre de tip Selection Contains pe cele dou cmpuri; nchidei tabelele i, dac dorii, memorai ultimele filtre create pentru a le aplica direct ulterior cu butonul Toggle Filter.

Fig.29 Filtrare simultan dup mai multe cmpuri cu Filter By Form

36

TEMA NR. 3 PRELUCRAREA INFORMAIILOR PRIN INTEROGRI

1. Competene profesionale oferite a identifica i a crea relaii posibile i utile ntre tabelele unei baze de date a nelege scopul i rolul interogrilor ntr-o baz de date a proiecta i executa diferite tipuri de interogri pentru a obine informaiile dorite. 2. Obiectivele temei nelegerea rolului relaiilor ntre tabelele unei baze de date deprinderea de a crea n tabele cmpuri noi care s creeze relaii de legtur cu alte tabele nelegerea proprietilor unei relaii i a modului de funcionare a acesteia nelegerea rolului interogrilor ntr-o baz de date deprinderea de a proiecta interogri de selecie de nregistrri, cu parametri sau cu cmpuri calculate deprinderea de a proiecta interogri de sintez statistic a informaiilor. 3. Coninutul detaliat 3.3.1. Stabilirea relaiilor ntre tabelele unei baze de date Access. n cadrul temei nr.1 s-au prezentat cteva noiuni teoretice referitoare la modelul relaional de baz de date (SGBD-R). Un model relaional de baz de date cuprinde tabele cu nregistrri ntre care se stabilesc relaii de legtur prin intermediul crora pot fi regsite informaiile corelate din tabele. O relaie se stabilete ntre dou tabele ale bazei de date, prin intermediul a dou cmpuri, cte unul din fiecare tabel, adic este de tip binar (cmp cmp). De obicei, relaia se stabilete ntre cmpurile care sunt definite chei primare ale tabelelor, dar aceasta nu este o regul obligatorie. Cele dou cmpuri trebuie s aib acelai tip de dat (Data Type), cu aceeai dimensiune (Field Size). Microsoft Access are la baz modelul relaional de baz de date, permind crearea i configurarea relaiilor ntre tabelele acesteia. Relaiile dintre tabele asigur o eficien ridicat a administrrii bazei de date (introducere, afiare, modificare/actualizare a informaiilor, dimensiune mai mic a fiierelor bazelor de date prin evitarea duplicatelor, regsirea mai rapid a unei anumite informaii precum i a informaiilor corelate, memorate n tabele diferite). Configurarea relaiilor dintre tabelele unei baze de date Access se face ntr-o fereastr dedicat Relationships (fig.30) accesibil din pagina Database Tools a panglicii superioare.37

Fig.30 Fereastra relaiilor dintre tabelele bazei de date Access n fereastra relaiilor pot fi afiate simultan toate relaiile existente ntre tabelele bazei de date prin selectarea opiunii All Relationships i poate fi editat fiecare relaie n parte pentru a i se configura anumite proprieti care determin un anumit comportament al relaiei n legtur cu nregistrrile din baza de date. Editarea unei relaii se face selectnd linia care marcheaz relaia i indicnd opiunea Edit Relationships sau prin dublu-click pe linia care materializeaz relaia. n fereastra afiat (Edit Relationships) se pot stabili unele proprieti ale relaiei: Pe baza nregistrrilor existente n tabele, Access identific tipul relaiei (vezi tema nr.1, fig.1) care poate fi One-To-One, One-To-Many, Many-To-Many sau Indeterminate; Orice relaie este de tip ParentChild (printecopil), adic poziia celor 2 tabele n relaie este bine stabilit: membrul stng Parent, iar membrul drept Child; Opiunea Enforce Referential Integrity se refer la aanumita Integritate Referenial care mpiedic existena unei nregistrri n tabelul membru drept al relaiei dac nu exist o nregistrare corespondent n tabelul membru stng (de exemplu, dac exist elevi nregistrai care aparin unor clase nenregistrate, astfel de nregistrri se numesc orfane i trebuie evitate); Opiunea Cascade Update Related Fields permite actualizarea automat a valorilor cmpului de legtur n tabelul membru drept al relaiei pe baza modificrii acestora doar n tabelul membru stng;

38

Opiunea Cascade Delete Related Records permite tergerea automat a nregistrrilor din tabelul membru drept al relaiei, asociate nregistrrii din tabelul membru stng care se terge (de exemplu, dac o disciplin este eliminat din curricula i se dorete tergerea acesteia din tabelul Discipline, vor fi terse automat i nregistrrile cu notele acordate la acea disciplin din tabelul Catalog, pentru a nu exist nregistrri orfane;

Opiunea Join Type precizeaz modul de comportare a relaiei la afiarea nregistrrilor corespondente n interogri i ofer 3 posibiliti (1.Equal, 2.Left, 3.Right Join).

Exemplul 9: Crearea unor relaii n baza de date Elevii scolii, concomitent cu adugarea unor tabele, pe baza schemei structurale prezentat n figura 4.

deschidei baza de date Elevii scolii i selectai opiunea Create Table Design, pentru a crea un nou tabel, Cadre_didactice; scriei numele celor 3 cmpuri ale tabelului (Field Name), cod_unic, nume, prenume, stabilii caracteristica de cheie primar pentru cmpul cod_unic i memoraia tabelul cu numele Cadre_didactice; cele 3 cmpuri vor avea tipul de dat Text; adugai n tabel 10 nregistrri, conform figurii 31 i nchidei tabelul; creai un nou tabel cu numele Discipline, care s conin trei cmpuri, cod_disc, denumire i nivel, primele dou de tip Text i al treilea de tip Number/Byte, avnd cmpul cod_disc drept cheie primar; adugai 8 nregistrri n tabel, conform figurii 31 i nchidei tabelul; selectai opiunea CreateTable pentru a crea un nou tabel direct n fereastra nregistrrilor, fig.32; selectai opiunea Add Existing Fields, pentru a prelua n noul tabel 2 cmpuri din tabelele Clase i Cadre_didactice; observai afiarea ferestrei Field List, coninnd cmpurile disponibile din tabelele bazei de date (fig.32);

Fig.31 nregistrrile tabelelor Cadre_didactice i Discipline39

Fig.32 Crearea unui tabel prin preluarea unor cmpuri existente n alte tabele, sub form de list de valori (Lookup) n fereastra Field List, selectai cmpul cod_unic al tabelului Cadre_didactice i, prin clickdreapta de mouse, alegei opiunea Add Field To View pentru a aduga cmpul n noul tabel se afieaz o fereastr Lookup Wizard, prin care Access solicit parcurgerea a cteva etape de configurare a noului cmp ca o legtur ntre cele dou tabele; n prima fereastr Lookup Wizard se mut cmpurile nume i prenume n lista Selected Fields (fig.33,a); se apas butonul Next; n a doua fereastr se indic drept criterii de ordonare cresctoare cmpurile nume i prenume (fig.33,b), pentru a afia nregistrrile n ordine alfabetic; se apas butonul Next; n a treia fereastr se las opiunile implicite, pentru a afia n list doar numele disciplinei i semestrul (fig.33,c); eventual se redimensioneaz limile coloanelor; se apas butonul Next; n ultima fereastr se scrie numele noului cmp cod_CD i se apas butonul Finish, indicnd numele noului tabel Atributii_CD n fereastra Save As; se procedeaz n mod similar pentru cmpul cod al tabelului Clase, indicnd, n ultima fereastr a asistenei interactive, numele noului cmp cod_cls;

a. b. c. Fig.33 Etapele crerii unui nou cmp de tip Lookup prin preluarea unui cmp existent

40

deschidei fereastra de proiectare a noului tabel, Atributii_CD (Design View), tergei cmpul ID, adugai cmpul atributie, de tip Text i creai o list cu 2 valori disponibile (Lookup Value List): diriginte i invatator; modificai etichetele (Caption) celor trei cmpuri la valorile Cadru_didactic, Clasa i, respectiv, Atributia, astfel nct acestea s fie afiate ca titluri de coloane n fereastra nregistrrilor (fig.34); completai tabelul cu nregistrri, conform celor din figura 34; ntr-un mod similar putei crea tabelul Catalog, prin preluarea cmpurilor cod_unic al tabelului Elevi i cod_disc al tabelului Discipline i adugarea cmpului nota; deschidei fereastra relaiilor (fig.30) din pagina Database Tools Relationships, apsai butonul All Relationships i observai afiarea relaiilor existente ntre cele 6 tabele ale bazei de date; fiecrei relaii i se pot stabili proprietile care definesc comportamentul acesteia prin selectarea liniei care o marcheaz, apsarea butonului Edit Relationships i activarea opiunilor Enforce Referential Integrity, Cascade Update Related Fields sau Cascade Delete Related Fields, validnd modificrile cu butonul OK; la nchiderea ferestrei relaiilor, se memoreaz modificrile (Yes).

Fig.34 Tabelul Atributii_CD

3.3.2. Crearea unei interogri. Fereastra de proiectare. Interogrile sunt obiecte ale bazei de date destinate prelucrrii informaiilor din tabele. Interogrile rspund necesitilor utilizatorului de a afia selectiv informaii din baza de date, de a le prelucra pentru afiarea de sinteze statistice, de a crea noi cmpuri sau tabele sau de a modifica valorilor nregistrrilor existente. De exemplu, n baza de date Elevii scolii, interogrile pot s rspund la ntrebri cum ar fi: Care este numrul de elevi al fiecrei clase sau pe diferite nivele? Care este lista nvtorilor / diriginilor pe anumite nivele? Care sunt elevii cu data naterii ntr-o anumit perioad (luna, trimestru, an)? Care sunt mediile pe discipline ale fiecrui elev, semestrial sau anual? Dar ale claselor? Care sunt mediile generale ale elevilor sau ale claselor, semestrial sau anual? Care este numrul de discipline studiate de elevi, semestrial sau anual?41

Interogrile sunt grupate n zona Queries a panoului de obiecte Access. n funcie de scop, interogrile se clasific n dou categorii: interogri de selecie: de cmpuri, de nregistrri, cu cmp calculat, cu parametru, de tip statistic, de tip tabel sintetic; interogri de aciune: de creare tabel nou, de modificare valori n tabel existent (adugare/modificare/tergere nregistrri). Interogrile de selecie preiau nregistrrile din tabele, eventuale le prelucreaz i afieaz informaiile rezultate, fr a modifica n vreun mod valorile acestora sau sturtcura de tabele a bazei de date. Interogrile de aciune efectueaz modificri ale structurii bazei de date sau a tabelelor prin crearea unor tabele noi sau prin adugarea, tergerea sau modificarea nregistrrilor existente. Interogarea este, de fapt, o aplicaie (un program), scris ntr-un limbaj de programare specific, care execut o serie de aciuni conform cerinelor utilizatorului. n Microsoft Access, limbajul de programare este construit pe structura limbajului universal utilizat n acest scop, SQL Structured Query Language, i disponibil programatorului prin intermediul unei ferestre de proiectare grafic, QBE Query By Example (fig.35). Acest mod de programare este mai uor de folosit pentru utilizatorii cu experien minim de programare. Crearea unei interogri se face n fereastra de proiectare (CreateQuery Design) sau cu ajutorul unui asistent interactiv (CreateQuery Wizard). Afiarea unei interogri (View) se poate face n mai multe moduri, dintre care cele mai importante sunt urmtoarele: Fereastra de proiectare (Design), fig.35, n care se configureaz toate opiunile interogrii; Fereastra de execuie (Datasheet), fig.36, n care sunt afiate nregistrrile care corespund opiunilor precizate n fereastra de proiectare; Fereastra SQL (SQL) a instruciunilor SQL, fig.37, care corespund opiunilor precizate n fereastra de proiectare. Fereastra de proiectare a unei interogri (fig.35), cuprinde dou zone principale: zona tabelelor conine tabelele din care se dorete preluarea nregistrrilor, afiate sub form de ferestre cu lista cmpurilor; dac ntre tabele exist relaii, acestea sunt afiate i pot fi editate; zona de proiectare care conine o serie de rnduri specifice (Field, Table, Sort, Show etc) necesare descrierii instruciunilor interogrii; se mai numete i gril de interogare. Semnificaia rndurilor din zona de proiectare este urmtoarea: Field i Table : numele cmpurilor i a tabelului din care provin sau ale cmpurilor nou create (calculate cu relaii matematice);

42

Fig.35 Fereastra Design a unei interogri

Fig.36 Fereastra Datasheet a unei interogri

Fig.37 Fereastra SQL a unei interogri Sort : precizarea criteriu de ordonare dup valorile din cmpul respectiv;

Show : afiare sau nu a valorilor cmpului la execuia interogrii (caset de validare); Criteria / Or : precizarea criteriului / criteriilor de selecie a nregistrrilor; Total : apare la interogrile de tip statistic i precizeaz opiunile de grupare sau de prelucrare statistic a valorilor din cmpul respectiv (Group By, Min, Max, Sum etc.); Crosstab : apare la interogrile de tip tabel sintetic, pentru precizarea atributului cmpului respectiv (Row Heading, Column Heading, Value). Pe lng rndurile descrise anterior, n zona de proiectare mai pot s apar i alte rnduri specifice diferitelor tipuri de interogri de aciune. Etapele de concepie a unei interogri n Access sunt urmtoarele: Proiectarea interogrii n fereastra de proiectare prin configurarea tuturor opiunilor dorite (selecia cmpurilor, precizarea criteriilor de ordonare, crearea criteriilor de afiare selectiv, crearea de noi cmpuri calculate (dac este cazul), precizarea opiunilor de afiare statistic (dac este cazul);

43

Verificarea afirii corecte a nregistrrilor prin execuia interogrii (Run) i, eventual, aplicarea unor opiuni de formatare a valorilor afiate; Memorarea interogrii sub un anumit nume i nchiderea ferestrei de proiectare. 3.3.3. Interogri simple pentru selecie de nregistrri. Interogrile simple de selecie Select Query sunt destinate afirii selective a nregistrrilor din tabele, dup anumite criterii de selecie, precizate n rndul Criteria. Ele mai pot conine i noi cmpuri, ale cror valori sunt precizate prin relaii matematice, definite n fereastra de proiectare a interogrii. Este posibil i ordonarea dup unul sau mai multe cmpuri (rndul Sort). Prin execuia interogrii, n fereastra de afiare a rezultatelor vor apare nregistrrile ale cror valori de cmp respect criteriul de selecie. Dac exist i cmpuri calculate, valorile acestora vor fi calculate cu relaiile matematice precizate i vor fi afiate. Criteriile de selecie care se precizeaz n rndul Criteria pot fi construite i cu ajutorul operatorilor aritmetici (+ * /), relaionali (< >= ), logici (Not, True, False), de concatenare (+, &) sau cu funcii specifice de comparare (Is Null, Is Not Null, Like, In, Between). La editarea relaiei matematice se poate utiliza asistentul interactiv Builder Wizard pot utiliza n rndul Criteria al interogrii. Tabelul nr.3 Exemple de criterii de selecie a nregistrrilor Valoarea sau relaia constantin brasov ion or ioana >= 8.5 Between 8 And 9 >= 1.01.2010 Like d* Like *mihai* Is Null / Is Not Null Explicaii Valoarea cmpului trebuie s fie exact constantin. Orice valoare n afar de brasov. Valoarea cmpului poate fi ion sau ioana Orice valoare mai mare sau egal cu 8.5 Orice valoare mai mare dect 8 i mai mic dect 9, adic orice valoare cuprins ntre 8 i 9, inclusiv limitele. Orice dat calendaristic dup 1.01.2010. Orice valoare care ncepe cu litera d. Caracterul * (jokerul) nlocuiete orice numr de caractere Orice valoare care include irul mihai. Ex. Mihai Daniel; Mihaila George; Neme Mihai; Iona Mihail nregistrrile care nu conin / conin valori pe cmpul respectiv. .

n tabelul nr.3 sunt prezentate o serie de exemple de criterii de selecie a nregistrrilor, care se

44

ntr-o interogare, un cmp calculat reprezint un nou cmp, creat n fereastra de proiectare, prin precizarea relaiei matematice de calcul al valorilor acestuia, bazat pe valorile din cmpurile existente n tabelele bazei de date i pe operatorii matematici. Valorile cmpurilor calculate nu sunt memorate fizic n baza de date, ele sunt afiate doar la execuia interogrii. La construcia relaiei matematice se pot utiliza i funcii predefinite incluse ntr-o bibliotec specific (vezi anexa nr.3), a cror editare se poate face apelnd asistentul interactiv Builder Wizard, . Sintaxa unui cmp calculat este urmtoarea: nume_cmp: n care nume_cmp reprezint un nume atribuit cmpului respectiv, iar reprezint relaia matematic de calcul al valorilor cmpului. n tabelul nr.4 sunt prezentate cteva exemple de cmpuri calculate cu diferite relaii matematice sau folosind unele funcii incluse n biblioteca Access. Tabelul nr.4 Exemple de cmpuri calculate Relaia matematic de calcul al valorilor cmpului [nume] & &[prenume] [mednota]*0.75 + [teza]*0.25 Explicaii Crearea unui cmp cu numele complet al persoanei prin concatenarea celor dou cmpuri [nume] i [prenume], separate de un spaiu Calculul mediei finale a disciplinei ca medie ponderat ntre media notelor, [mednota] i nota la teza, [teza]. Cmp cu dou valori, OK sau Fail, calculat cu funcia condiional IIf. Pentru valorile cmpului [nota] mai mari sau egale cu 5, cmpul va avea valoarea OK, iar pentru celelalte valori, valoarea Fail. Calculul vrstei elevului prin scderea din anul curent a anului naterii elevului. Se utilizeaz funcia Year( ) Calculul unei valori in lei, pe baza valorii n EUR i a cursului valutar lei/eur. Calculul mediei aritmetice a valorilor cmpului [nota], utiliznd funcia statistic Avg( )

IIf ([nota] >=5; OK; Fail) 2011-Year[data_nasterii] [val_eur]*4.3 Avg([nota])

O interogare simpl poate conine i parametri. Parametrul reprezint o valoare variabil, dat de ctre utilizator, ca dat de intrare ntr-o fereastr de dialog specific, n momentul execuiei interogrii. Parametrul se poate utiliza drept criteriu de selecie de nregistrri sau n relaii matematice care definesc criterii de selecie sau cmpuri calculate.45

Definirea unui parametru se face prin editarea unui mesaj specific scris ntre paranteze drepte, n locul n care trebuie introdus valoarea acestuia. Mesajul care constituie definiia parametrului va fi afiat n fereastra de dialog n care se solicit valoarea acestuia. n tabelul nr.5 sunt prezentate cteva exemple de utilizare a parametrilor n conintul criteriilor de selecie a nregistrrilor sau n relaii matematice. Tabelul nr.5 Exemple de parametri n interogri Like *&[Introdu codul clasei]&* Parametrul definete valoarea de selecie aplicat n rndul Criteria. Utilizarea caracterului special * face selecia mai larg Not [Introdu valoarea care se exclude] Parametrul definete valoarea care trebuie exclus la selecie. >= [Introdu valoarea minima] Parametrul definete valoarea din relaia de comparare. De exemplu, dac se d parametrului valoarea 8.5, se vor afia toate nregistrrile care conin o valoare mai mare sau egal cu 8.5. [nota]*(1-[Introdu ponderea tezei])+[teza]*[Introdu ponderea tezei] Calculul mediei finale ca medie ponderat dintre dou componente. Parametrul definete ponderea tezei n valoarea mediei finale calculat cu relaia matematic. Ponderea celeilalte componente, [nota], se calculeaz prin scdere din 1, ntruct suma ponderilor trebuie s fie egal cu unitatea. [val_eur]*[Introdu cursul de schimb lei/euro] Calculul preului n lei pe baza preului n eur i a valorii cursului lei/eur. Parametrul definete valoarea cursului de schimb care se introduce la execuia interogrii.

Exemplul 10: Crearea unor interogri simple n baza de date Elevii scolii.

Interogri simple cu diverse criterii de selecie deschidei baza de date Elevii scolii i selectai opiunea CreateQuery Design, pentru a deschide fereastra de proiectare a unei noi interogri; n fereastra Show Table afiat, selectai, pe rnd tabelele Clase i Elevi i apsai butonul Add pentru a le aduce n fereastra interogrii, nchidei apoi fereastra Show Table; putei oricnd s o reafiai prin apsarea butonului Show Table din pagina Design; selectai, pe rnd, prin dublu-click, cmpurile cod din tabelul Clase, nume, prenume din tabelul Elevi, observai c aceste cmpuri au aprut n rndul Field al interogrii (fig.38); alegei opiuni de ordonare, n rndul Sort, pentru cmpurile nume i prenume pentru a afia elevii ordonai alfabetic (fig.38);

46

Fig.38 Fereastra de proiectare a unei interogri simple de selecie indicai n rndul Criteria al cmpului clasa (fig.38) un criteriu de selecie pentru afiarea elevilor din clasa I-A, al crei cod este 1110; execuia interogrii, prin apsarea butonului Run, va afia lista alfabetic a elevilor clasei I-A; putei reveni n fereastra de proiectare cu butonul ViewDesign View; memorai interogarea (Save) cu numele Lista elevilor clasei; tergei selecia efectuat n rndul Criteria al cmpului cod i precizai o alt selecie pentru a afia toi elevii claselor a II-a, fig.39; n acest caz se utilizeaz funcia Like i caracterul de nlocuire (jokerul) *; indicai i ordonare cresctoare dup valorile cmpului cod pentru a afia elevii ordonai pe clase; executai interogarea pentru a vedea nregistrrile i memorai-o (Save As Save Object As) cu numele Lista elevilor pe nivel; tergei selecia efectuat n rndul Criteria al cmpului cod i editai o nou selecie pentru a afia elevii cu prenumele ion, ioan sau ioana (fig.40); n acest caz sunt necesare dou criterii de selecie cu funcia Like i joker care s funcioneze alternativ (Or); memorai interogarea (Save As Save Object As) sub numele Selectie elevi dupa prenume;

Fig.39 Selecie simpl cu funcia Like

Fig.40 Selecie multipl cu funcia Like47

tergei seleciile efectuate anterior, aducei i cmpul data_nasterii pe grila de proiectare i efectuai o selecie dup un interval de timp de exemplu elevii nscui n anul 2001, fig.41; n acest caz se poate utiliza funcia Between care permite precizarea unui interval de valori ntre dou limite i funcioneaz pentru date numerice, texte sau date calendaristice; precizai i ordonare cresctoare dup valorile cmpului data_nasterii; executai interogarea pentru a-i testa funcionarea i memorai-o cu numele Selectie elevi dupa data nasterii; n acest moment, pe panoul principal, n grupul Queries trebuie s avei 4 obiecte de tip interogare.

Fig.41 Selecie cu funcia Between i valori de tip dat calendaristic

Interogri simple cu cmpuri calculate creai o nou interogare (Create Query Design) i aducei tabelele Clase, Cadre_didactice i Atributii_CD n fereastra interogrii; observai afiarea relaiilor existente ntre tabele; creai un nou cmp (calculat), editnd relaia matematic a acestuia n rndul Field, pe prima coloan liber din stnga (fig.42), editarea se poate face i n fereastra Builder Wizard, ; noul cmp trebuie s afieze numele complet al cadrelor didactice, obinut prin concatenarea (adunarea) valorilor din dou cmpuri, nume i prenume, separate de un spaiu; sintaxa relaiei matematice este nume_CD: [nume] + +[prenume] dup editarea relaiei matematice n fereastra Expression Builder, se valideaz aciunea apsnd butonul OK;

48

Fig.42 Crearea unui cmp calculat prin editarea unei relaii matematice n fereastra Expression Bulider procedai n mod similar pentru crearea unui cmp pentru afiarea numelui complet al clasei, cu relaia matematic: clasa: [nivel]+-+[litera] aducei i cmpul atributie pe grila de proiectare i creai o selecie pentru a afia doar atribuiile de nvtor; executai interogarea vei avea o list a cadrelor didactice cu atribuiile de nvtor; dac baza de date conine date ale mai multor ani, atunci trebuie eventual precizat i o selecie dup valorile cmpului an pentru a afia doar datele dintr-un anumit an; dac dorii afiarea doar a nvtorilor pe un nivel (de exemplu clasa I), atunci trebuie precizat i criteriul respectiv de selecie pentru cmpul nou calculat clasa; memorai interogarea (Save) cu numele Lista nvatatorilor claselor; creai o nou interogare (CreateQuery Design) i aducei tabelul Elevi n fereastra de proiectare; aducei pe gril cmpurile nume, prenume, data_nasterii i creai apoi un cmp calculat, cu numele varsta, care s afieze varsta elevilor, calculat cu relaia matematic varsta: 2011-Year([data_nasterii])

49

la calculul valorilor noului cmp se utilizeaz funcia Year ( ), care este inclus n biblioteca de funcii Access (fig.43); executai interogarea pentru a vedea nregistrrile i memorai-o (Save) cu numele Varsta elevilor;

Fig.43 Cmp calculat, utiliznd funcia Year( ) Interogri simple cu parametri

Fig.44 Parametru definit ca i criteriu de selecie al nregistrrilor

deschidei interogarea Lista elevilor clasei n fereastra de proiectare i modificai criteriul de selecie al clasei, nlocuindu-l cu un parametru [Introdu codul clasei] (fig.44); executai interogarea de mai multe ori i dai, pe rnd, diverse valori parametrului, n fereastra de dialog afiat, de ex. 1110, 1210 etc; de fiecare dat execuia se va realiza cu valoarea dat a parametrului i rezultatul va fi altul; memorai interogarea cu numele Lista elevilor clasei cu parametru (Save As) i nchidei fereastra; deschidei interogarea Selectie elevi dupa prenume n fereastra de proiectare i modificai criteriul de selecie astfel nct acesta s fie parametrizat (fig.45) se creaz n acest scop o relaie matematic, utiliznd i jokerul drept caracter de nlocuire; executai interogarea, indicnd valoarea parametrului n fereastra de dialog specific; memorai interogarea cu numele Lista elevilor cu prenume Fig.45 Parametru utilizat la funcia Like parametru (Save As) i nchidei-o.

50

3.3.4. Interogri de selecie de tip statistic. Interogrile de tip statistic prelucreaz informaiile din tabelele bazei de date cu ajutorul unor opiuni de prelucrare statistic a datelor. nregistrrile sunt grupate prin selecie, pe grupuri, dup valoarea comun a unor cmpuri (Group By) i apoi se efectueaz prelucrarea grupurilor create, dup valorile din alte cmpuri, cu funciile de tip statistic (Count, Sum, Min, Max, Avg, Expression). O interogare de tip statistic se creaz dintr-o interogare de selecie simpl, apsnd pictograma Totals; pe gril apare rndul Total n care se aleg opiunile de prelucrare pentru fiecare cmp. n tabelul nr.6 sunt prezentate opiunile de prelucrare statistic disponibile. Aceste opiuni exist i sub form de funcii n biblioteca Access, sub numele de SQL Aggregate Functions. Tabelul nr.6 Opiunile de prelucrare statistic Numele opiunii Group By Sum Avg Min Max Count StDev Var First Last Expression Descriere Definete cmpul ca i criteriu de grupare a nregistrrilor Calculeaz suma valorilor cmpului din fiecare grup Calculeaz media aritmetic a valorilor din fiecare grup Afieaz valoarea minim din setul de valori al fiecrui grup Afieaz valoarea maxim din setul de valori al fiecrui grup Afieaz numrul de nregistrri din fiecare grup Calculeaz abaterea standard a setului de valori din fiecare grup Calculeaz variana setului de valori al fiecrui grup Afieaz prima valoare din fiecare grup Afieaz ultima valoare din fiecare grup Definete un cmp calculat cu o relaie matematic care include o funcie de tip SQL Aggregate

Opiunile de prelucrare statistic Sum, Avg, Min, Max, StDev i Var pot fi utilizate pentru cmpuri cu tip de dat numeric, iar celelalte Count, First, Last i Expression pot fi utilizate i pe celelalte tipuri de dat disponibile ntr-o baz de date. Interogrile de tip statistic pot fi obinuite sau pot afia informaiile sub form de tabel sintetic. n acest caz, Access pune la dispoziie o opiune specific Crosstab.

51

Exemplul 11: Crearea unor interogri de tip statistic n baza de date Situatie_scolara.

Interogri de tip statistic obinuite deschidei baza de date Elevii scolii i creai o nou interogare (CreateQuery Design), selectnd tabelele Clase i Elevi; creai cmpul calculat clasa, cu relaia matematica clasa: [nivel]+-+[litera] i aducei apoi cmpul nume pe gril (fig.46); apsai pictograma Totals i alegei, pentru cmpul nume, n rndul Total, opiunea Count, pentru a afia numrul de elevi din fiecare clas; executai