Ioan Doroș
Baze de date
Editura Cibernetica MC București 2010
Descrierea CIP a Bibliotecii Naţionale a României DOROS, IOAN Baze de date / Ioan Doros. - Bucureşti : Cibernetica, 2010 ISBN 978-973-88451-6-9 004
Editură acreditată CNCSIS cu codul 73
Tehnoredactare și corectură: Arabela Andor Coperta: Adelina Muntean
2
CAPITOLUL I. DELIMITĂRI CONCEPTUALE ALE BAZELOR DE DATE .................... 6 1.1. Termeni specifici în lucrul cu bazele de date ........................................................... 6 1.2. Sisteme de gestiune a bazelor de date ...................................................................... 9 1.2.1. Noţiuni generale privind sistemele de gestiune a bazelor de date ....................... 9 1.2.2. Funcţiuni ale sistemelor de gestiune a bazelor de date ...................................... 10 1.2.3. Caracteristicile sistemelor de gestiune a bazelor de date pentru o bază de date relaţională ................................................................................................................ 11 1.2.4. Sistemul de gestiune a bazelor de date ACCESS .............................................. 13 1.3. Scopul unei baze de date ........................................................................................ 14 1.4. Componentele aplicaţiei ACCESS .......................................................................... 15 1.5. Proiectarea bazelor de date..................................................................................... 16 1.5.1. Principii ale proiectării bazelor de date ............................................................ 16 1.5.2. Normalizarea unei baze de date relaţionale ...................................................... 18 1.5.3. Integritatea bazelor de date .............................................................................. 22 1.6. Operarea în mediul ACCESS .................................................................................. 24 1.6.1. Deschiderea aplicaţiei. Crearea unei noi baze de date ....................................... 24 1.6.2. Moduri de vizualizare ..................................................................................... 26 1.6.3. Deschiderea, modificarea, salvarea şi închiderea unei baze de date existente ... 27 1.6.4. Folosirea funcţiei ajutor .................................................................................. 29 1.6.5. Închiderea aplicaţiei ........................................................................................ 31 CAPITOLUL II. CREAREA ȘI UTILIZAREA TABELELOR ............................................ 32 2.1. Crearea unei tabele ................................................................................................ 32 2.1.1. Proprietăţile câmpurilor................................................................................... 33 2.1.2. Definirea unei chei primare ............................................................................. 37 2.1.3. Stabilirea unui index ....................................................................................... 37 2.1.4. Modificarea proprietăţilor unui câmp ............................................................... 38 2.2. Editarea datelor într-o tabelă .................................................................................. 39 2.2.1. Introducerea de date în tabelă .......................................................................... 39 2.2.2. Vizualizarea informaţiilor dintr-o tabelă .......................................................... 40 2.2.3. Modificarea datelor într-o tabelă ..................................................................... 40 2.2.4. Adăugarea de înregistrări într-o tabelă ............................................................. 41 2.2.5. Ştergerea de date într-o tabelă ......................................................................... 41 2.3. Crearea relaţiilor între tabele ................................................................................. 43 CAPITOLUL III. UTILIZAREA INFORMAŢIILOR DINTR-O BAZĂ DE DATE .............. 45 3.1. Conectarea la o bază de date existentă .................................................................... 45 3.2. Căutarea datelor cu ajutorul instrumentelor mediului Access .................................. 45 3.3. Crearea unei interogări simple ............................................................................... 46 3.4. Crearea interogărilor peste mai multe tabele ........................................................... 51 3.5. Selecţia şi sortarea datelor ..................................................................................... 56 3.6. Salvarea unei interogări ......................................................................................... 57 3.7. Adăugarea şi eliminarea filtrelor ............................................................................ 57
3
3.8. Limbaj Structurat de Interogare SQL (Structured Query Language) ......................... 59 3.8.1. Limbajul SQL ................................................................................................. 59 3.8.2. Comenzi destinate tabelelor ............................................................................. 62 3.8.3. Instrucţiunile de selecţie a datelor ................................................................... 63 3.8.4. Instrucţiunile pentru manipularea datelor ......................................................... 74 CAPITOLUL IV. CREAREA ŞI UTILIZAREA FORMULARELOR .................................. 77 4.1. Noţiuni generale ................................................................................................... 77 4.2. Controale ............................................................................................................... 80 4.3. Proiectarea unui formular legat de o sursă de înregistrări ........................................ 82 4.3.1. Proprietăţile esenţiale ......................................................................................... 82 4.3.2. Obiectele simple ............................................................................................. 84 4.3.3. Culorile obiectelor .......................................................................................... 85 4.3.4. Aranjarea obiectelor ........................................................................................ 86 4.3.5. Alte facilităţi .................................................................................................. 87 CAPITOLUL V. CREAREA ŞI UTILIZAREA RAPOARTELOR ...................................... 91 5.1. Crearea unui raport ................................................................................................ 91 5.2. Particularizarea antetului şi subsolului .................................................................. 94 5.3. Gruparea datelor într-un raport. Realizarea de totaluri şi subtotaluri ....................... 97 CAPITOLUL VI. LIMBAJUL VISUAL BASIC .............................................................. 103 6.1 Editarea modulelor Visual Basic for Application ................................................... 103 6.2 Elemente de sintaxă .............................................................................................. 105 6.3 Casete de dialog predefinite .................................................................................. 106 6.4 Variabilele în VBA ............................................................................................... 109 6.4.1. Declararea variabilelor .................................................................................. 109 6.4.2. Tipurile variabilelor în VBA ......................................................................... 111 6.4.3. Iniţializarea variabilelor ................................................................................ 113 6.5. Tablouri .............................................................................................................. 113 6.6 Funcţii şi proceduri definite de utilizator ............................................................... 114 6.7 Structura alternativă ............................................................................................. 119 6.8 Structura repetitivă ............................................................................................... 120 6.8.1. Structura repetitivă cu numărător ................................................................... 120 6.8.2. Structurile repetitive de tip Do … Loop ......................................................... 122 6.8.3. Structura repetitivă „pentru fiecare‖ .............................................................. 124 6.9 Programarea recursivă .......................................................................................... 125 6.10 Obiecte ACCESS 2000 ........................................................................................ 126 6.11 Programarea dirijată de evenimente în ACCESS 2000 .......................................... 128 6.12 Accesul cu programele la obiectele bazei de date ................................................. 131 CAPITOLUL VII. ÎNDRUMAR PENTRU REALIZAREA PROIECT ULUI LA DISCIPLINA „BAZE DE DATE‖ ........................................................................................................ 140 7.1. Cerinţe minimale ................................................................................................. 140 7.2. Baza de date ........................................................................................................ 141 7.3. Documentaţia ...................................................................................................... 144
4
BIBLIOGRAFIE ............................................................................................................ 149
5
CAPITOLUL I. DELIMITĂRI BAZELOR DE DATE
CONCEPTUALE
ALE
1.1. Termeni specifici în lucrul cu bazele de date Gestiunea colecţii1or de date este una din cele mai importante probleme care se rezolvă astăzi cu ajutorul calculatoarelor existente, de la cele mai simple PC-uri (Personal Computers), până la calculatoarele foarte puternice. Aceste colecţii de date pot fi memorate în diferite fişiere, care sunt apoi gestionate cu ajutorul limbajelor de programare, sau în baze de date, care sunt gestionate de Sistemele de Gestiune a Bazelor de date (SGBD). Lucrul cu voume tot mai mari de date i-a făcut pe oameni să sporească din ce în ce mai mult numărul calculatoarelor şi să le îmbunătăţească caracteristicile p e zi ce trece, pentru a-şi uşura astfel munca. O bază de date poate fi definită ca fiind o colecţie de tabele în care sunt stocate clase de entităţi diferite. Elementele generice ale unei baze de date sunt: ► Coloanele, care se numesc câmpuri (în engleză FIELDS); Nume ► Rândurile, care se numite înregistrări, tabelă articole sau tupluri (în engleză
Înregistări
Câmpuri
RECORDS). 6
Un tabel este de fapt o colecţie a tuturor entităţilor de date dintr -un singur tip de date (tip de entităţi sau clasă de date). Înregistrările dintr-un tabel formează cardinalul, în vreme ce câmpurile definesc structura tabelului, numărul de câmpuri, tipul lor şi dimensiunea lor. Câmpurile unui tabel pot fi de tipul: 1. Logic, putând să suporte valorile Y, N, T, F; 2. Numeric, putând fi definite cu ajutorul numerelor întregi sau cu ajutorul zecimalelor; 3. Dată calendaristică, câmpurile putând fi definite cu ajutorul unei date valabile; 4. Şir de caractere, putând fi de diferite lungimi; 5. Memo, câmpurile putând conţine articole de lungimi variabile. O bază de date are o structură care poate fi reprezentată sub forma unei colecţii de descrieri statice ale diferitelor tipurilor de entităţi, dar si a relaţiilor logice dintre acestea. Aceste relaţii logice reprezintă de fapt, asociaţiile dintre mai multe entităţi. Astfel, o entitate poate fi definită ca o reprezentarea unică a unui obiect real. Aceasta corespunde unei înregistrări şi este reprezentată prin luarea de valori pentru diferite atribute ale sale. Pentru a diferenţia două entităţi este necesar ca măcar un atribut să aibă valori diferite. Atributul este o proprietate importantă a unui obiect din lumea reală, fiind necesar pentru a distinge entităţile între ele. Atributul mai poate fi definit ca fiind o celulă sau o căsuţă dintr-un tabel. La fel ca şi câmpurile, atributele pot fi de tip logic, numeric, dată calendaristică, şir de caractere sau memo. Domeniul de valori al atributului poate fi precizat la creearea bazei de date, când se vor specifica şi intervalele de valori pentru fiecare câmp. Pentru identificarea univocă a unei entităţi este necesară crearea unui atribut sau a unei combinaţii de atribute care poartă denumirea de identificator sau cheie. În ACCESS identificatorii sunt câmpuri cheie principale, aceştia fiind folosiţi pentru crearea indexului principal al entităţii. O bază de date este un ansamblu structurat de date evolutive, organizate pentru a fi prelucrate de programe informatice. Bazele de date sunt fişiere stocate pe suporturi magnetice, împreună cu aplicaţiile care le exploatează (utilizează). Cele mai multe baze de date sunt relaţionale (în proporţie de 80%-90%). 7
Bazele de date relaţionale sunt de fapt nişte tabele cu relaţii între ele. Aceste baze de date relaţionale au redundanţă minimă şi controlată. Primul care a introdus noţiunea de bază de date relaţională a fost E. F. Codd 1, în anul 1970. Un exemplu de bază de date relaţională ar putea fi prezentat cu ajutorul celor două tabele, a căror structură este redată mai jos: Marcă
Marcă
Nume
Nume
Funcţii
Adresă
Salar nominal
Salar nominal
Deduceri
Impozit
Faptul că următoarele coloane: marcă, nume şi salar nominal , apar de două ori, înseamnă că aceste coloane sunt redundante. Pentru a putea stabili relaţii între tabele, câmpul care conţine marca trebuie să fie inclus în ambele tabele. Nu este nevoie să fie denumit identic în ambele tabele, în schimb trebuie să aibă acelaşi conţinut. Relaţiile între tabele pot fi stabilite şi pe combinaţii de câmpuri (unele SGBD-uri permiţând şi descrierea de astfel de combinaţii prin expresii aritmetice). Astfel, se pot stabili mai multe tipuri de relaţii între tabele, după cum urmează: ► Relaţii de unu-la-unu; ► Relaţii de unu-la-mai mulţi; ► Relaţii de mulţi-la-unu; ► Relaţii de mulţi-la-mulţi. Două tabele aflate în relaţie de unu-la-unu, sunt interclasabile şi pot fi privite ca unul singur, articolele rezultate cuprind reuniunea câmpurilor celor două tabele. Relaţiile de unu-la-mulţi şi mulţi-la-unu, se referă la acelaşi lucru, dar privit din părţi diferite. În Access se utilizează relaţiile de unu-la-mai-mulţi. Relaţiile de tipul mulţila-mulţi, nu pot fi incluse în bazele de date relaţionale. Exemplificăm, în figura de mai jos, 4 tabele cu relaţii de unu-la- mai mulţi (scris 1-∞), definite în ACCESS:
1
Edgar Frank Codd (23 august 1923, Insula Portland, Anglia – 18 aprilie 2003, Williams Island,
Florida, SUA) a fost un informatician american de origine engleză care, lucrând pentru IBM, a inventat modelul relaţional pentru gestiunea bazelor de date, model care constituie baza teoretică a bazelor de date relaţionale. (conform wikipedia.com)
8
1.2. Sisteme de gestiune a bazelor de date 1.2.1. Noţiuni generale privind sistemele de gestiune a bazelor de date Aplicaţiile care exploatează aceste date sunt cunoscute sub denumirea de sisteme de gestiune a bazelor de date sau S.G.B.D.. Câteva astfel de sisteme de gestiune a bazelor de date ar fi: ► ORACLE. Compania americană cu acest nume, a oferit, încă de la începutul anilor ’80, cele mai robuste, rapide şi eficiente variante de sisteme de gestiune a bazelor de date, pentru calculato are de toate tipurile (mari, mini, micro), de tip client-server, pentru reţelele locale sau pe Internet. Preţul ridicat, datorat standardelor de calitate ale produselor şi serviciilor, fac ca în ţara noastră, recurgerea la soluţiile Oracle să se facă doar pentru proiectele de sisteme informatice mari; ► FoxPro. Cu această denumire Compania Microsoft a promovat un model de organizare a datelor, numit dBASE, existent din anul 1990. Acest program este foarte popular în ţara noastră, deoarece crearea şi evoluţia lui este sincronă fenomenului exploziv din anii 1990, prin care firmele şi-au dezvoltat proiecte proprii de informatizare. ► SQL Server. Cu acest nume, compania Microsoft promovează sistemele de gestiunea a bazelor de date, de tip client-server, pentru problemele care depăşesc capacităţile ACCESS sau FoxPro; 9
► MySQL. Este un sistem de gestiune a bazelor de date foarte răspândit datorită faptului că este gratuit (de tip Open Source); ► Aplicaţia Microsoft ACCESS, care este un sistem de gestiune a bazelor de date relativ nou, aceasă aplicaţie făcând parte din suita de programe de birou Microsoft Office. Toate fişierele sunt în ultimă instanţă un lung şir de biţi, ceea ce face până la urmă diferenţa între ele, redarea sunetelor din fişierele tip .wav sau a imaginilor din fişierele .bmp sau .jpg, sunt programele dedicate acestor meniri. Astfel, şi un sistem de gestiune a bazelor de date are menirea de a conferi unor fişiere forma de tabele pentru ca informaţiile conţinute să fie accesibile utilizatori lor, care să poată efectua cele patru operaţii (adăugare, regăsire, şt ergere, modificare). Prin similitudine se poate spune că rolul unui sistem de gestiune a bazelor de date pentru o bază de date este asemănător rolului unui Sistem de Operare pentru un calculator. Principalul scop al unui sistem de gestiune a bazelor de dat e este acela de a pune la dispoziţia utilizatorilor mijloacele necesare pentru crearea bazelor de date, dar şi pentru crearea unor programe care să folosească aceste date.
1.2.2. Funcţiuni ale sistemelor de gestiune a bazelor de date Un sistem de gestiune a bazelor de date are următoarele funcţiuni: 1. Definirea bazei de date, în principal a structurii tabelelor, vederilor, interogărilor, a relaţiilor sau a altor obiecte specifice SGBD-urilor. Partea din limbaj specializată pentru aceste sarcini se constituie într-un Limbaj de Definire a Datelor (abreviat în mod uzual: LDD), alcătuit din diferite comenzi (dacă un astfel de limbaj nu există), şi/sau dintr-o interfaţă cu utilizatorul (variantă mult utilizată). 2. Manipularea informaţiilor din baza de date, este principalul scop al unui SGBD perceput de utilizatorii obişnuiţi. Aceştia îşi doresc să poată introduce, regăsi şi actualiza (modifica sau şterge), date prin aplicaţiile lor. Programatorii au la dispoziţie, pentru a realiza acest lucru, un Limbaj de Manipulare a Datelor (abreviat în mod uzual: LMD), şi/sau autorul unor comenzi (dacă nu există un astfel de limbaj). Este recomandabil ca în aplicaţii, părţile care implementează aceste prime două funcţiuni să fie pe cât posibil distinct conturate, iar modificările aduse uneia să nu afecteze bunul mers al celeilalte. Datele trebuie să aibă o stabilitate mai mare decât programele. În plus, datele trebuie construite astfel încât să poată fi accesibile mai multor programe, care sunt scrise în limbaje diferite şi care rulează pe mai multe calculatoare. Aceste programe, scrise cu ajutorul LMD-ului, folosesc structurile de 10
date luate din tabelele (dicţionarele), unde se află descrise structurile virtuale ale bazei de date, precum şi structura logică la care va avea acces. 3. Administrarea bazei de date. O sarcină importantă a gestiunii bazei de date se referă la supravegherea datelor din baza de date şi supervizarea programelor de acces la aceste date. Persoana (sau grupul de persoane) care are aceste sarcini, este numită administratorul bazei de date, iar atribuţiile îndeplinite se constituie în funcţia de administrare a bazei de date. Un SGBD trebuie să includă mijloace le prin care un administrator de baze de date să îşi poată îndeplini sarcinile (funcţiile) de bază, cum ar fi: a). Organizarea bazei de date prin stabilirea structurii şi încărcarea ei cu date iniţiale; b). Urmărirea funcţionării bazei de date prin: ► Păstrarea în baza de date a datelor corecte şi la zi; ► Asigurarea integrităţii şi confidenţialităţii datelor; ► Urmărirea performanţelor bazei de date, cu ajutorul unor programe utilitare; ► Punerea la dispoziţia utilizatorilor a unor programe sau proceduri; ► Informarea continuă cu privire la observaţiile utilizatorilor bazei de date. c). Reorganizarea bazei de date prin actualizări ale structurilor sau ale programelor de acces. 4. Protecţia informaţiilor din baza de date este o problemă la ordinea zilei datorită generalizării bazelor de date cu acces partajat al mai multor utilizatori. Un SGBD asigură protecţia datelor prin asigurarea: ► Confidenţia1ităţii, realizând protecţia împotriva accesului neautorizat la date; ► Integrităţii, realizând protecţia împotriva alterării conţinutului bazei de date, datorită unor erori de programare, defecte de echipament, distrugeri rău intenţionate, etc.
1.2.3. Caracteristicile sistemelor de gestiune a bazelor de date pentru o bază de date relaţională De-a lungul timpului au fost concepute mai multe tipuri de sisteme de gestiune a bazelor de date care s-au pretat diferitelor momente de evoluţie fizică a tehnicii de calcul. În momentul de faţă sunt folosite tot mai des sistemele de gestiune a bazelor de date relaţionale şi, mai nou, încearcă să se impună pe piaţă şi sistemele de gestiune a bazelor de date distribuite. Acelaşi E. F. Codd a detaliat, în anul 1985, caracteristicile care trebuie să fie îndeplinite de un sistem de gestiune a bazelor de 11
date pentru ca acesta să fie considerat relaţional, sub forma a 13 reguli. Aceste reguli pot fi sintetizate astfel: Regula 0. Principiul de bază, conform căruia orice sistem de gestiune a bazelor de date relaţional trebuie să gestioneze toată baza de date numai prin posibilităţile modelului relaţional. Regula 1. Stocarea informaţiei, potrivit căreia toate datele trebuie să fie cuprinse în tabele de date, şi deci datele trebuie să fie memorate şi prelucrate în acelaşi mod. În ACCESS de exemplu, acest lucru este posibil cu ajutorul aplicaţiei Microsoft Jet. Regula 2. Garantarea accesului la date, potrivit căreia fiecare element de dată trebuie să fie accesibil logic, printr-o combinaţie de genul: cheie primară, nume tabel şi nume câmp. În ACCESS spre exemplu, se pot defini chei primare. Regula 3. Lipsa informaţiei, adică în tabele trebuie să poată exista valori nule (vide), astfel de valori trebuie să specifice faptul că într-o celulă o valoare nu a fost completată şi acest lucru nu trebuie să fie confundat cu valori 0 pentru tipul numeric sau spaţiu pentru tipul text. În aplicaţia ACCESS există astfel de valori care poartă denumirea de Null. Regula 4. Catalogul de sistem, acest lucru însemnând că descrierea bazei de date trebuie să se facă tot în tabele. În ACCESS acest lucru e posibil cu ajutorul motorului Microsoft Jet. Regula 5. Limbajul cuprinzător, un sistem de gestiune a bazelor de date trebuie să conţină un limbaj cuprinzător pentru definirea datelor, descrierea tabelelor, realizarea unor adăugări, actualizări sau ştergeri de câmpuri. ACCESS-ul de exemplu, suportă limbajul SQL. Regula 6. Actualizarea vederilor, însemnând că toate vederile trebuie să fie actualizate de sisteme. În acest sens ACCESS-ul a fost primul sistem de gestiune a bazelor de date pentru PC-uri, care a permis realizarea interogărilor pentru actualizare. Regula 7. Actualizări la nivel de mulţime. În tabele este necesar ca modificările şi actualizările să se facă la nivel de mulţime de articole. În cadrul aplicaţiei ACCESS acest lucru este posibil. Regula 8. Independenţa fizică a datelor, altfel spus datele trebuie să fie fizic independente faţă de programul aplicaţiei. O aplicaţie nu trebuie să se modifice dacă , spre exemplu, se adaugă sau se şterge un index. În ACCESS acest lucru se realizează cu ajutorul aplicaţiei Microsoft Jet, care are motor logic de stocare. Regula 9. Independenţa logică a datelor. Conform acestei reguli, schimbările efectuate asupra relaţiilor dintr-o bază de date nu trebuie să afecteze programul de aplicaţie. 12
Regula 10. Independenţa integrităţii datelor, sau altfel spus restricţiile de integritate trebuie să poată fi definite într-un limbaj şi să poată fi stocate într-un catalog. În ACCESS se pot crea reguli de integritate cu ajutorul limbajului SQL. Regula 11. Independenţa distribuţiei. Potrivit acestei reguli, capacităţile sistemelor de gestiune a bazelor de date nu au voie să fie limitate datorită distribuţiei unor componente ale acestora în baze de date separate. Regula 12. Inexistenţa subminărilor, însemnând că dacă un sistem de gestiune a bazelor de date relaţional are un limbaj de manipulare al unei singure înregist rări, acest limbaj nu va putea fi folosit la ocolirea regulilor de integritate sau a constrângerilor modelului relaţional. În ACCESS acest lucru înseamnă că nu se pot încălca regulile de integritate. Folosindu-se de lucrările lui E. F. Codd referitoare la modelul relaţional şi la limbajele bazate pe algebra relaţională sau calculul relaţional, din ce în ce mai mulţi oameni de ştiinţă au încercat să redefininească şi să îmbunătăţească aceste concepte. S-a ajuns astfel la dezvoltarea şi perfecţionarea unor limbaje relaţionale precum SQL (Structured Query Language), QBE (Query-By-Example) sau QUEL(Query Language). Limbajul SQL se bazează pe calculul relaţional, având în vedere utilizarea de variabile constituite din tupluri. Pentru a ajuta la extinderea acestui limbaj în întreaga comunitate a producătorilor de baze de date, Institutul Naţional American pentru Standarde (ANSI) a elaborat şi lansat standardele SQL, în 1986, potrivit cărora acest limbaj foloseşte acelaşi set de comenzi şi structuri de bază standa rdizate, indiferent de varianta de limbaj folosită.
1.2.4. Sistemul de gestiune a bazelor de date ACCESS Unul dintre cele mai actuale sisteme de gestiune a bazelor de date este aplicaţia Microsoft ACCESS, prima variantă a acestei aplicaţii fiind lansată pe piaţă de compania Microsoft în anul 1992, în cadrul suitei de programe de birou Microsof Office. Din cadrul programelor Microsoft Office mai fac parte şi aplicaţiile : Microsoft Word, Microsoft Excel sau Microsoft PowerPoint. Principalele caracteristici ale sistemului de gestiune a bazelor de date ACCESS sunt: ► Este componenta dedicată bazelor de date relaţionale din Microsoft Office; ► Este deschis comunicării cu alte sisteme de gestiune a bazelor de date cum ar fi FoxPro, Paradox sau SQL Server (şi MSDE), un alt SGDBR creat de Microsoft, obiectele ACCESS putând fi folosite în aceste SGBD-uri dar şi în alte aplicaţii windows, sau invers, obiecte din aceste SGBD -uri pot fi folosite în ACCESS; 13
► Beneficiază de tehnologii avansate, deoarece este creat de firma Microsoft. Aceste tehnologii sunt: Tehnologia ActiveX, care permite realizarea aplicaţiilor client/server; Permite accesul la baze de date din reţeaua Internet, fiind un instrument util pentru publicarea informaţiilor în paginile Web; Este autodocumentat prin ajutor (help), scris în HTML compilat, ajutorul fiind apelabil contextual sau la cerere; Permite crearea foarte simplă a obiectelor bazei de date cu instrumentele expert (wizard); Bazele de date pot fi personalizate. ► Permite protecţia accesului la obiecte prin administrarea de drepturi de acces pentru utilizatorii ordonaţi în grupuri; ► Permite crearea de grupuri de obiecte definite de utilizator în cadrul bazei de date; ► Limbajul propriu al SGBD-ului relaţional ACCESS este Visual Basic; ► E foarte răspândit, utilizarea lui este intuitivă şi este relativ uşor de învăţat . Pune la dispoziţia utilizatorilor un mediu prietenos pentru crearea, proiectarea, testarea şi administrare obiectelor bazelor de date proprii. Aceste caracteristici, ne-au determinat să alegem pentru exemplificarea noţiunilor despre baze de date aplicaţiile realizate cu ajutorul mediului ACCESS, în capitolele următoare. Pe de altă parte este adevărat că SGBD Access nu poate concura cu Oracle, Informix, Microsoft SQL Server ca viteză sau concurenţă partajată de lucru. Din acest motiv pentru proiectele mari trebuiesc alese aceste sisteme de gestiune a bazelor de date, dar pentru proiectele mai mici avantajele de mai sus constituie argumente serioase pentru alegerea aplicaţiei ACCESS.
1.3. Scopul unei baze de date În mod intuitiv, atunci când avem de lucru cu colecţii mari de date avem tendinţa de a realiza tabele care să ne ajute să gestionăm aceste date. Atunci când aceste colecţii sunt prea mari pentru a le gestiona manual avem nevoie de calculator, cu ajutorul căruia să construim aplicaţii care să gestioneze aceste baze de date. Aceste aplicaţii trebuie să gestioneze bazele de date într -un mod mai eficient decât în cazul în care am gestiona aceste date manual. Printre cele mai importante avantaje în lucrul cu o bază de date electronică se numără: ► Viteza mărită şi posibilitatea de a stoca un volum mare de date; ► Introducerea şi editarea comodă a datelor, precum şi stocarea şi căutarea uşoară a acestora; 14
► Posibilitatea de pregătire a unor rapoarte precise, în forma dorită şi utilizând datele de la un moment dat; ► Asigurarea partajării datelor cu ajutorul altor aplicaţii sau a altor calculatoare; ► Asigurarea securităţii datelor împotriva accidentelor. În cazul utilizării aplicaţiei ACCESS, principalele avantaje ar fi: ► Asigurarea unor posibilităţi avansate de manipulare, stocare şi selecţie a datelor; ► Stabilitatea aplicaţiei în contextul sistemului de operare; ► Aplicaţia este uşor de învăţat; ► Aplicaţia este foarte directă în ceea ce priveşte posibilităţile de modificare a câmpurilor şi a tabelelor; ► Aplicaţia este foarte răspândită în întreaga lume.
1.4. Componentele aplicaţiei ACCESS Aplicaţiile în ACCESS se constituie în jurul unei baze de date dată. Acestea îşi stochează toate elementele de care are nevoie în containerul bazei de date. Astfel, o bază de date ACCESS poate fi definită ca fiind o colecţie de obiecte: tabele (tables), cereri de interogare (queries), formulare (forms), rapoarte (reports), pagini Web (pages), comenzi macro (macros) şi module (modules). Tabelele sunt obiecte definite de utilizator, în care sunt stocate datele primare. Formularele sunt obiecte care permit introducerea datelor, editarea sau afişarea acestora, sau controlul întregii aplicaţii. Un formular poate conţine:
15
Rapoartele sunt obiecte ale bazelor de date prin care sunt vizualizate sau tipărite informaţii cu conţinut şi structură accesibilă şi conforme cerinţelor utilizatorilor. Interogările sunt obiecte care permit vizualizarea informaţiilor obţinute p rin prelucrarea datelor din una sau mai multe tabele, şi/sau a altor cereri de interogare. Paginile Web de accesare a datelor reprezintă obiecte care includ fişiere HTML şi alte fişiere suport, în vederea furnizării accesului la date prin intermediul browser-elor de Internet. Comenzile Macro reprezintă obiecte care conţin o definiţie structurată a uneia sau mai multor acţiuni pe care ACCESS-ul le realizează ca răspuns la un anumit eveniment. Modulele reprezintă obiecte care conţin proceduri definite de utilizator şi sunt scrise în limbajul de programare Visual Basic for Applications (V.B.A.). În cadrul aplicaţiei ACCESS utilizatorul poate apela la Vrăjitori (Wizards) pentru a creea orice obiect (tabele, formulare, macro -uri etc). Aceste obiecte pot fi însă create şi de către utilizator.
1.5. Proiectarea bazelor de date 1.5.1. Principii ale proiectării bazelor de date Metoda clasică, şi de altfel singura metodă de proiectare pentru persoanele care nu au experienţă în ceea ce priveşte utilizarea programelor informatice, este recurgerea la hârtie şi creion. Proiectarea unei baze de date presupune răspunderea la o serie de întrebări, cum ar fi: 16
► De ce se doreşte realizarea bazei de date? (pentru a putea răspunde la această întrebare se va porni mereu de la rapoartele care ar trebui listate); ► Ce resurse avem la dispoziţie? (cele mai importante resurse fiind în acest caz calculatoarele, oamenii, informaţiile etc); ► Ce anume va fi de făcut? Abia apoi se va trece la proiectarea tabelelor, ceea ce presupune def inirea câmpurilor, a conţinutului lor şi a dimensiunilor acestora, dar şi determinarea relaţiilor dintre acestea. În demersul realizării unei baze de date, există trei niveluri de percepţie a acesteia: ► nivelul extern, este nivelul în care sunt percepute bazele de date de utilizatori. Ei îşi exprimă cerinţele informaţionale prin aşa -numitele scheme externe. Pot exista o mulţime de sub-scheme, fiecare sub-schemă corespunzând viziunilor unui utilizator al bazei de date; ► nivelul conceptual, este nivelul aferent administratorului bazei de date, în viziunea căruia baza de date este o abstractizare a unei p ărţi din lumea reală. Această viziune se concretizează într-o schemă conceptuală; ► nivelul intern, corespunzător programatorului, care realizează modul de reprezentare a datelor pe suportul fizic. Determinarea structurii unei baze de date se poate aborda ascendent, realizându-se descrierea schemelor externe, urmată de elaborarea schemei conceptuale, sau descendent, definind mai întâi schema conceptuală şi deduc ând ulterior schemele externe posibil de obţinut. În acest scop îşi vor da concursul un grup mai larg de persoane a căror activitate va fi influenţată de modificarea acestei zone a sistemului informatic al instituţiei. În momentul proiectării unei baze de date se vor urmării în primul rând entităţile, atributele şi relaţiile, indecşii, machetele de introducere sau d e ieşire, procedurile de calcul, precum şi schemele logice sau interogările, şi abia apoi se va trece la încercarea de închegare a tabelelor, la normalizarea lor şi stabilirea relaţiilor dintre ele. Datele vor fi memorate în tabele şi fiecare tabel va conţine date doar despre un singur subiect (ex: elevi, discipline, etc). Redundanţa, adică apariţia unei informaţii de două ori în baza de date, trebuie să fie minimă şi controlată. Proiectarea formularelor, a rapoartelor, a macrocomenzilor şi a modulelor , este influenţată în primul rând de calitatea structurii datelor. Însă trebuie să se ţină cont de faptul că, de cele mai multe ori, aceste forme de reprezentare externă a datelor se bazează pe interogări, nu direct pe tabele. O bază de date bine proiectată conţine, de obicei, diferite tipuri de interogări care prezintă informaţiile necesare. 17
În ajutorul aplicaţiei Microsoft Access (Help) sunt prezentaţi paşii care trebuie parcurşi pentru proiectarea unei baze de date. Aceştia sunt: ► Stabilirea scopului bazei de date, a utilizatorilor, a cerinţelor lor . Pornind de la listele necesare (rapoarte), se schiţează formulare le de introducere a datelor. Se deduc apoi faptele ce trebuie memorate în baza de date şi cărui subiect îi va aparţine fiecare. Aceste fapte corespund câmpurilor (coloanelor) din baza de date, iar subiectele cărora le aparţin corespund tabelelor. ► Stabilirea câmpurilor necesare în baza de date. Fiecare câmp este un fapt despre un anumit subiect. Nu trebuie să rămână vreun fapt fără un câmp corespondent, iar câmpurile trebuie să fie detaliate în cele mai mici părţi logice. De exemplu, o adresă trebuie să fie descompusă în stradă, număr, bloc, scară, etaj, apartament. De asemenea în câmpuri nu se înscriu liste sau valori calculate. ► Stabilirea tabelelor necesare în baza de date. Fiecare tabel trebuie să conţină informaţii despre un subiect. ► Stabilirea apartenenţei fiecărui câmp la tabelul corespunzător. ► Identificare câmpurilor cu valori unice în fiecare înregistrare . ► Stabilirea relaţiilor dintre tabele. ► Îmbunătăţirea proiectului. ► Introducerea datelor şi crearea altor obiecte ale bazei de date . ► Utilizarea instrumentelor de analiză ale Microsoft ACCESS. Microsoft ACCESS conţine două instrumente cu care se poate îmbunătăţiţi forma unei baze de date: 1. Analizatorul de Tabel, care poate analiza forma unui tabel la un moment dat şi, dacă este corespunzător, poate propune noi structuri de tabel şi relaţii, şi poate scinda un tabel în noi tabele corelate, dacă o astfel de corelaţie are sens. 2. Analizatorul de Performanţă, care poate analiza întreaga bază de date şi poate efectua recomandări şi sugestii pentru îmbunătăţirea acesteia. Expertul poate implementa de asemenea aceste recomandări şi sugestii.
1.5.2. Normalizarea unei baze de date relaţionale Normalizarea unei baze de date este o tehnică de transformare a a unei baze de date folosită pentru a elimina anomaliile de actualizare. În aplicare a acestei tehnici se ţine cont de dependenţela funcţională dintre câmpuri. Prin normalizare datele sunt cuprinse în mai multe tabele, cu relaţie între ele, iar redundanţa lor este minimă şi controlată, astfel încât să conserve informaţiile şi dependenţele funcţionale din relaţia iniţială (descompunerea fără pierderi). 18
E. F. Codd a demonstrat că într-o anumită formă relaţiile posedă proprietăţi nedorite, pe care le-a numit anomalii de actualizare. Aceste anomalii sunt: ► Anomalia de ştergere, care constă în faptul că anumite date care urmează să fie şterse fac parte din tupluri în care se găsesc şi alte date care sunt necesare şi în continuare, ori ştergerea făcându-se la nivelul tuplului, acestea se pierd; ► Anomalia de adăugare, constă în faptul că anumite date care urmează să fie adăugate fac parte din tupluri incomplete (pentru care nu se cunosc toate datele), ceea ce face ca acestea să nu poată fi adăugate; ► Anomalia de modificare, care rezultă din faptul că este dificil de modificat o valoare a unui atribut atunci când ea apare în mai multe tupluri ale relaţiei. Pentru a înlătura aceste anomalii, E. F. Codd a stabilit trei forme normale pentru relaţii şi a introdus procesul de normalizare care se bazează pe noţiunea de dependenţă funcţională (FD) ca relaţie între atributele unei entităţi cu caracter invariant. Procesul de normalizare a relaţiilor se realizează în mai mulţi paşi, începând cu forma normală unu (1NF) şi ajungând (după ultimele cercet ări) la forma normală cinci (5NF). Aceasta constă în descompunerea unei relaţii în conformitate cu mulţimea dependenţelor funcţionale F, într-o colecţie de relaţii care să conserve informaţiile şi dependenţele funcţionale din relaţia iniţială (descompunerea fără pierderi). O bază de date este în forma normală 1 dacă şi numai dacă toate câmpurile din tabelele ei nu sunt repetate şi conţin numai valori atomice. O bază de date este în forma normală 2 dacă şi numai dacă a trecut de forma normală 1 şi în tabelele ei orice câmp non-cheie este complet dependent funcţional de cheia primară. O bază de date este în forma normală 3 dacă şi numai dacă a trecut de forma normală 2 şi în tabelele ei fiecare câmp non-cheie este dependent numai de cheia primară. Fie următorul set de date, referitoare la vânzări: Dencli SC Secundul SRL SC Secundul SRL SC Secundul SRL SC Terţa Parte SRL SC Terţa Parte SRL SC Terţa Parte SRL SC Secundul SRL SC First SRL SC First SRL SC First SRL
Adrcli Str.Verii nr 2 Str.Verii nr 2 Str.Verii nr 2 Str.Toamnei nr 3 Str.Toamnei nr 3 Str.Toamnei nr 3 Str.Verii nr 2 Str Primaverii nr 1 Str Primaverii nr 1 Str Primaverii nr 1
Telcli Nrfact Datafact Delegat DenProd 0261222222 1 21.10.2009 Popescu Zahar 0261222222 1 21.10.2009 Popescu Ulei 0261222222 2 22.10.2009 Dragoș Lapte 0261333333 5 24.10.2009 Trandafir Zahar 0261333333 5 24.10.2009 Trandafir Ulei 0261333333 5 24.10.2009 Trandafir Lapte 0261222222 6 25.10.2009 Popescu Lapte 0261111111 3 22.10.2009 Anton Lapte 0261111111 4 23.10.2009 Anton Ulei 0261111111 4 23.10.2009 Anton Lapte
19
UM Cant Pret Discount kg 15 3,1 Fl 20 3,27 l 30 2,9 5 kg 25 3,1 Fl 11 3,27 l 20 2,9 5 l 15 2,9 5 l 18 2,9 Fl 12 3,27 l 15 2,9
1. Prima formă normală, spune că toate coloanele unui tabel trebuie să conţină valori atomice (indivizibile). Aşadar, nu trebuie să existe câmpuri ce conţin liste de valori sau grupuri de date. Tabelul respectă condiţiile cerute pentru această formă. Câmpul Adrcli ar putea fi considerat un câmp complex şi divizat în stradă şi număr, dar deoarece valorile incluse sunt manipulate, de obicei, în mod agregat, foarte mul ţi practicieni stochează în acest mod informaţiile despre adrese. 2. A doua formă normală, conform căreia un tabel este în această formă dacă a trecut de prima formă normală şi dacă toate câmpurile care nu sunt chei sunt dependente de cheia primară. Pentru transformarea în 1NF a tabelelor se încearcă găsirea unui identificator (cheie primară) şi câmpurile care sunt dependente de identificator. S-a găsit Dencli de care sunt dependente câmpurile Adrcli, Telcli. Pentru legătură introducem câmpul Cocli. Acestea alcătuiesc un tabel pe care îl putem numi Clienţi. În mod similar se procedează şi cu grupul de câmpuri DenProd, UM, Pret, pentru care s-a introdus Codprod şi le vom grupa în tabelul Produse. Restul câmpurilor, la care adăugăm şi cele două coduri pentru relaţii, constituie împreună un tabel pe care îl putem numi Facturi, în care cheia o constituie combinaţia de câmpuri: Nrfact, Datafact şi Codprod. Codcli #
Dencli
Adrcli
Nrfact
Telcli
Datafact
Delegat CodProd CodCli
Cant Discount
111111 SC First SRL
Str Primaverii nr 1 0261111111
1 21.10.2009 Popescu 1075
222222
15
222222 SC Secundul SRL
Str.Verii nr 2
0261222222
1 21.10.2009 Popescu 1009
222222
20
0261333333
2 22.10.2009 Dragos
7023
222222
30
5 24.10.2009 Trandafir 1075
333333
25
5 24.10.2009 Trandafir 1009
333333
11
5 24.10.2009 Trandafir 7023
333333
20
5
6 25.10.2009 Popescu 7023
222222
15
5
333333 SC Terţa Parte SRL Str.Toamnei nr 3
Codprod #
DenProd
UM
Pret
1075 Zahar
Kg
3,1
3 22.10.2009 Anton
7023
111111
18
1009 Ulei
Fl
3,27
4 23.10.2009 Anton
1009
111111
12
7023 Lapte
L
2,9
4 23.10.2009 Anton
7023
111111
15
5
La rândul lui, datorită nedependenţei câmpurilor Delegat şi CodCli de cheia compusă, tabelul facturi se descompune în două tabele: în primul pot fi trecute informaţiile generale despre facturi cu cheia compusă din: Nrfact# 1 2 5 6 3 4
Datafact# Delegat 21.10.2009 Popescu 22.10.2009 Dragoș 24.10.2009 Trandafir 25.10.2009 Popescu 22.10.2009 Anton 23.10.2009 Anton
CodCli 222222 222222 333333 222222 111111 111111
în al doilea pot fi trecute informaţiile cu rândurile din facturi cu informaţii despre produsele vândute: 20
Nrfact#
Datafact# CodProd# 21.10.2009 1075 21.10.2009 1009 22.10.2009 7023 24.10.2009 1075 24.10.2009 1009 24.10.2009 7023 25.10.2009 7023 22.10.2009 7023 23.10.2009 1009 23.10.2009 7023
1 1 2 5 5 5 6 3 4 4
CodCli Cant Discount 222222 15 222222 20 222222 30 5 333333 25 333333 11 333333 20 5 222222 15 5 111111 18 111111 12 111111 15
3. A treia formă normală, presupune ca tabelele să treacă de a doua formă şi câmpurile ne-cheie să fie mutual independente pentru ca acestea să fie în această formă normală. În bazele de date nu se pun câmpuri calculate. Primele trei tabele respectă criteriile pentru 3NF: Clineti Codcli #
Dencli
Adrcli
Telcli
111111 SC First SRL 222222 SC Secundul SRL
Str Primaverii nr 1
0261111111
Str.Verii nr 2
0261222222
333333 SC Terţa Parte SRL
Str.Toamnei nr 3
0261333333
Produse Codprod #
DenProd
UM
Pret
1075 Zahar
Kg
3,1
1009 Ulei 7023 Lapte
Fl
3,27
L
2,9
FactGen
Nrfact# 1 2 5
Datafact# Delegat 21.10.2009 Popescu 22.10.2009 Popescu 24.10.2009 Trandafir
CodCli 222222 222222 333333
În cel de-al patrulea tabel, observăm că există câmpul Discount, care este dependent în mod tranzitiv de cheia primară. Drept urmare, din acest tabel, vor fi create două tabele care vor fi în 3NF. FactProd
Nrfact# 1 1 2 5 5 5 6 3 4 4
Datafact# CodProd# Cant 21.10.2009 1075 15 21.10.2009 1009 20 22.10.2009 7023 30 24.10.2009 1075 25 24.10.2009 1009 11 24.10.2009 7023 20 25.10.2009 7023 15 22.10.2009 7023 18 23.10.2009 1009 12 23.10.2009 7023 15
21
Discount Codprod #
7023 333333
CodCli#
Discount 5
7023 222222
5
1.5.3. Integritatea bazelor de date Integritatea bazelor de date se referă la anumite restricţii care asigură corectitudinea datelor stocate. Astfel, la proiectarea bazelor de date se pot impune anumite restricţii pentru a se realiza şi conserva integritatea bazelor de date, care are două aspecte: 1. Integritatea în interiorul fiecărui tabel. Acest aspect se referă la impunerea unor restricţii de validare a datelor, specifice domeniului aplicaţiei. De exemplu: se va urmări ca datele calendaristice să fie stocate în câmpuri corespunzătoare, domeniul de valori să fie respectat. Pe de altă parte, se urmăreşte şi asigurarea integrităţii entităţii, care presupune că orice componentă a cheii primare trebuie să ia o valoare unică , diferită de valoarea NULL, pentru fiecare înregistrare introdusă în tabel. 2. Integritatea referenţială. Conform integrităţii referenţiale, pentru fiecare valoare a cheii externe din baza de date relaţională, diferită de valoarea NULL, trebuie să existe o valoare corespunzătoare din acelaşi domeniu de valori şi de acelaşi tip, aceasta fiind cheia primară.
22
Relaţiile dintre tabele se realizează prin referinţe 2, stabilite între înregistrările tabelelor şi cheile tabelelor. Viabilitatea acestor referinţe poate fi înfăptuită prin integritatea referenţială, a cărei scop este de a împiedica apariţia înregist rărilor solitare şi de a păstra sincronizate referinţele, astfel încât să nu apară înregistrări care se referă la alte înregistrări care nu mai există. În ACCESS, integritatea referenţială poate fi impusă atunci când se definesc relaţiile dintre tabele, pr in precizarea actualizărilor şi ştergerilor în cascadă a câmpurilor corelate, precum este redat în figura anterioară. După activarea acestei impuneri pentru o relaţie dintre două tabele, ACCESS nu va mai permite niciunei aplicaţii care utilizează baza de date încălcarea integrităţii referenţiale pentru acea relaţie, adică nu sunt posibile actualizări care modifică ţinta unei referinţe şi nici ştergeri care elimină ţinta unei referinţe. Mai mult chiar, orice modificare a cheii primare se va propaga, în cascadă, şi în cheile străine, sau ştergerea unui articol dintr-un tabelul de iniţiere a referinţei, va provoca ştergerea tuturor articolelor din tabelul ţintă al referinţei, pentru care cheia străină are valoarea pe care o are cheia primară a articolului şters. ACCESS permite chiar şi afişarea în cascadă a relaţiilor dintre tabele, precum în figura de mai jos: De exemplu, în datele pe care le utilizăm, modificarea în tabelul Clienti, a codului client (Codcli) pentru „SC Secundul SRL‖ la valoarea 999999, va determina modificarea valorii câmpului Codcli, cu valoarea 999999, pentru articolele corelate din tabelul Factgen (3 articole care au valoarea Codcli=222222). Dacă se şterge din
tabelul Clienţi, articolul care are valoarea pentru Codcli egală cu 222222, se va
2
de la verbul a referi
23
produce (bineînţeles după o atenţionare şi acceptul utilizatorului) ştergerea în cascadă a celor 3 articole corelate din Factgen. Folosirea sintagmei în cascadă este îndreptăţită în acest caz, deoarece cele 3 articole din tabelul Factgen, sunt corelate, la rândul lor, cu articole din tabelul Factprod, după cheia compusă din câmpurile Nrfact şi Datafact (4 articole: două corespunzătoare facturii 1, unul corespunzător facturii 2 şi unul corespunzător facturii 6). Rezumând, în structura de cascadă di n figură, ştergerea unui articol din tabelul Clienţi provoacă ştergerea a trei articole în tabelul Factgen, provocând mai departe ştergerea a 4 articole în tabelul Factprod.
1.6. Operarea în mediul ACCESS 1.6.1. Deschiderea aplicaţiei. Crearea unei noi baze de date SGBD ACCESS face parte din pachetul de programe Microsoft Office, pachet care este proiectat să ruleze sub diverse versiuni ale sistemului de operare Windows. Desigur că într-un sistem friendly user, orice se poate face în câteva moduri, pentru ca utilizatorul să poată folosi metoda care i se pare cea mai naturală. O posibilitate de a lansa SGBD-ul ACCESS este cea de la butonul Start pe calea programelor instalate: Start>All programs> Microsoft Office > Microsoft Access. La pornire, SGBD-ul ACCESS afişează fesreastra din figură (numită sugestiv Pagina Introducere în Microsoft Office Access), şi utilizatorului îi sunt oferite mai multe posibilităţi. Pentru crearea unei noi baze de date trebuie aleasă opţiunea: Bază de date necompletată (Blank Access database) din partea a doua a panoului
24
După aceasta este afişată o fereastră cu titlul Fişier nou bază de date în care utilizatorul are de stabilit: ► directorul în care va fi salvat fişierul; ► numele fişierului în care va fi stocată baza de date.
Creare director nou Listă cu foldere şi Instrumente pentru fisiere
Alegerea locului de stocare
fişiere Scrierea numelui
Tipul fișierului
fişierului
În fereastra Fişier nou bază de date se procedează în felul următor: ► În caseta pentru alegerea tipului fişierului se specifică extensia fişierului sau se alege un tip din lista ataşată; ► Pentru a ajunge la directorul unde fişierul va fi stocat, ut ilizatorul se foloseşte de lista ascunsă, din primul rând al fesrestrei, unde alege unitatea de stocare şi apoi alege directorul sau fişierul dorit din lista cu directoare şi fişiere. Aceste operaţii se repetă până când se ajunge la locaţia dorită; 25
► În caseta Nume fişier se va trece numele fişierului. Dacă numele fişierului dorit apare în listă, se poate face un clic pe fişierul dorit şi numele lui va fi trecut automat în caseta de scriere a numelui fişierului (Atenţie, în acest caz se va suprascrie fişierul ales). Operaţia se termină când se apasă butonul OK. Dacă se doreşte, se poate crea un director nou sau se poate apela la instrumentele pentru fişiere pentru căutare a, ordonarea, modul de afişare, adăugarea în lista de preferinţe sau afişarea proprietăţilor. Intrarea în noua bază de date se face atunci când este afişată fereastra bază de date.
1.6.2. Moduri de vizualizare Pentru a se lucra cu un obiect, acesta se alege din partea dreaptă a ferestrei Bază de date, sau se realizează în succesiunea operaţiilor pe care le execută aplicaţia. Obiectele sunt afişate în diferite moduri de vizualizare prezentate în figur a de mai jos. Se observă că multe moduri sunt comune pentru majoritatea obiectelor. Modurile de vizualizare se selectează apăsând butonul Vizualizare din panglică.
Vizualizarea în modul proiectare (Design view) este posibilă pentru toate obiectele. În acest mod se configurează conţinutul şi forma obiectelor. În modul Foaie de date (Data view) sunt afişate, pentru a putea fi modificate, datele legate de obiectul curent (tabele sau integogări). În modul Vizualizare raport (Report view) sunt afişate, prin intermediul 26
formatărilor prevăzute într-un raport, datele legate de raportul curent. În modul Vizualizare formular (Form view) sunt afişate, prin intermediul formatărilor prevăzute într-un formular, datele legate de formularul curent, pentru a putea fi modificate. În modul Vizualizare aspect sunt afişate datele, prin intermediul formatărilor prevăzute într-un formular sau raport şi se pot face unele modificări intuitive aspra machetei obiectului prin care se expun datele. Vizualizare SQL este un mod specific interogărilor. Utilizatorii bazelor de date trebuie să înţeleagă că o interogare este în primul rând o comandă SQL (Structured Query Language). Tabelele văzute în modul Foaie de date a interogărilor, conţin datele rezultate în urma execuţiei comenzilor SQL. O interogare, vizualizată în modul proiectare, mai este cunoscută şi sub numele de grilă QBE (acronim de la sintagma din engleză Query by Example) şi vizualizează grafic comanda SQL. Examinarea înaintea imprimării este o redare pe ecran a modului cum va fi tipărit documentul (raport) pe hârtia de imprimantă. Vizualizare PivotTable: e un mod de vizualizare care sintetizează şi analizează datele dintr-o foaie de date sau dintr-un formular. Se utilizează diferite niveluri de detaliere sau se organizează datele prin glisarea de câmpuri şi elemente, prin afişarea sau ascunderea elementelor din listele verticale pentru câm puri. Vizualizare PivotChart: este un mod de vizualizare care arată o analiză vizuală a datelor dintr-o foaie de date sau dintr-un formular. Sunt vizibile diferite niveluri de detaliere sau se indică aspectul prin glisarea de câmpuri şi elemente sau prin expunerea şi ascunderea de elemente în listele verticale pentru câmpuri.
1.6.3. Deschiderea, modificarea, salvarea şi închiderea unei baze de date existente Pentru deschiderea unei baze de date existente se poate proceda în mai multe moduri. Dacă se doreşte modificarea sau consultarea unei baze de date creată anterior, atunci când se lansează rularea aplicaţiei ACCESS, în fereastra principală se acţionează în partea dreaptă, în caseta Deschidere bază de date recentă a ferestrei de start a aplicaţiei, de obicei pe opţiunea Mai multe … şi se va deschide o fereastră de dialog, cu titlul Deschidere, asemănătoare cu fereastra de dialog Fişier nou bază de date, în care se selectează baza de date care se doreşte să fie modificată. Spre deosebire de alte aplicaţii din Office, în ACCESS se poate lucra, la un moment dat, doar cu o singură bază de date. 27
Dacă aplicaţia ACCESS este deja deschisă, se poate acţiona pe pictograma din meniul ascuns legat de Butonul Office sau se alege opţiunea > Deschidere, sau se apasă concomitent Ctrl + O. Şi în aceste trei ultime cazuri, se deschide automat fereastra cu titlu Deschidere, cu a cărei elemente componente utilizatorul îşi alege fişierul cu care doreşte să lucreze. În fine, este posibil ca documentul care se doreşte a fi deschis să fi fost editat recent şi să apară într-un istoric, conţinut în partea dreaptă a meniului ascuns legat de Butonul Office , sau în prima parte a panoului de activitate Fişier nou, de unde poate fi deschis. Dar o bază de date ACCESS, concomitent cu aplicaţia Microsoft ACCESS, pot fi deschise dintr-un File Manager, printr-un dublu clic pe orice document de tip bază de date ACCESS cu extensia .mdb (versiuni anterioare anului 2007) sau .accdb (versiuni ulterioare anului 2007), dacă aplicaţia ACCESS este instalată pe calculator. Asupra unei baze de date deschise se pot face modificări ale obiectelor încapsulate: se pot crea noi tabele, se pot modifica structurile sau conţinutul datelor, se pot crea noi interogări, formulare, rapoarte, pagini Web, sau se pot face modificări asupra celor existente. ACCESS are opţiuni de salvare a principalelor tipuri de obiecte compuse care apar în fereastra Bază de date. Salvarea se referă la obiectul curent, atunci când el este vizualizat în modul proiectare (pentru interogări şi când sunt vizualizate în modul SQL). În restul modurilor de vizualizare, pentru toate obiectele, salvarea, dacă are sens, se referă la datele pentru care obiectul este interfaţă. Pentru obiectele curente, se pot face salvări sub alt format: html, xml, pdf, xls, doc etc. Pentru prima salvare a unor obiecte nou create, se foloseşte una dintre comenzile: >Salvare ca… sau > Salvare. Se ajunge la o fereastră de salvare unde se precizează numele sub care va fi salvat obiectul. Dacă se apelează comanda >Salvare ca… mai este afişată şi o casetă de editare text combinată cu o listă ascunsă din care se poate alege tipul obiectului, pe când, dacă se apelează la coma nda > Salvare, această casetă combinată nu mai apare. Această salvare se poate face şi cu combinaţia de taste Ctrl + S sau apăsând butonul al treilea din bara de instrumente Standard cu pictograma:. Dacă există deja o versiune a obiectului, salvată în baza de date, cu comanda Salvare, versiunea veche se suprascrie. Dar, se poate salva noua versiune, cu un nou nume sau ca alt tip de obiect bazat pe obiectul iniţial prin comanda > Salvare ca…. În modurile de vizualizare interactive, valorile stabilite pentru controalele diferitelor obiecte se salvează automat după părăsirea controalelor, de aceea când iese dintr-un obiect, dacă nu s-au făcut modificări în modul proiectare, utilizatorul nu mai este atenţionat să facă salvare. Dacă utilizatorul a efectuat modificări asupra unu i 28
obiect în modul proiectare, chiar dacă trece într-un mod interactiv, utilizatorul este atenţionat când părăseşte obiectul să-şi salveze modificările efectuate în modul proiectare. Pentru închiderea unei baze de date se alege opţiunea
>Închidere.
1.6.4. Folosirea funcţiei ajutor Utilizatorii aplicaţiilor Microsoft sunt obişnuiţi să beneficieze de un sistem complex de ajutor în lucru. De aceste facilităţi beneficiază şi utilizatorii ACCESS. Probabil cel mai bun ajutor pentru utilizatori este realizarea interfeţei în diverse limbi, altele decât limbile de circulaţie internaţională. Aşa este de exemplu şi versiunea în româneşte a Microsoft Office, cu toate că încă se utilizează foarte multe neologisme forţate. Aplicaţiile Office au toate elementele traduse: de la opţiunile de meniu, mesajele din diferite ferestre sau casete, până la sistemul de ajutor (help) în amănun, sau chiar mesajele care apar atunci când poziţionăm cursorul mouse-ului pe unul din butoanele barelor de instrumente (text info control sau pe engleză: „Tool Tip‖). În Ajutorul din ACCESS, sunt traduse chiar şi explicaţiile pentru partea de programare in limbajele VBA sau SQL. Totuşi cea mai mare parte a funcţiunilor de ajutor pentru utilizatori este concentrată în meniul „Ajutor‖. Ajutorul este sub formă de documente hypertext. Opţiunile componentei de Ajutor a aplicaţiei ACCESS sunt următoarele: Răsfoire Ajutor Access Activarea Access
Colectarea datelor
Noutăţi
Pornirea
Accesibilitate
Obţinerea Ajutorului
Pagini de date
Ataşări
Macrocomenzi şi programabilitate
Implementarea aplicaţiei
Access Developer Extensions
Conversie
Particularizare
Proiectarea bazelor de date
Expresii
Date externe
Filtrarea şi sortarea
Formulare şi rapoarte
Interogări
Salvare şi imprimare
Securitate şi confidenţialitate
Tabele
Lucrul cu site-uri SharePoint
Adăugarea de diagrame, nomograme sau tabele
Gestionarea fişierelor şi datelor
Lucrul în altă limbă
29
Get help? este o facilitate a Microsoft Office de a explica elementele de interfaţă din aplicaţiile sale. Alegând butonul care e plasat în colţul din dreapta sus, se deschide o fereastră cu explicaţii dependente de context. Ajutor pentru Microsoft ACCESS este funcţiunea care poate fi lansată şi apăsând tasta F1 sau cu clic pe butonul . Se afişează o fereastră de ajutor asemănătoare celei de mai jos cu posibilitatea de a vedea o listă complexă a termenilor de ajutor. În partea de sus sunt câteva butoane folosite pentru răsfoirea convenabilă a acestor pagini de ajutor. În partea de jos sunt două cadrane de pagini. Prima are ca titlu Contents (contents înseamnă cuprins), şi conţine lista de teme a documentaţie i numită Ajutor Microsoft Access şi este structurată arborescent. Temele explicate pot fi simple sau
compuse. O temă simplă are la începutul rândului o pictogramă cu semnul atunci când este selectată, se afişează explicaţia ei în partea dreaptă.
30
şi,
Temele care sunt compuse din alte subteme, pot avea la începutul rândului unul dintre semnele sau . Dacă au semnul , atunci înseamnă că apar în lista din stânga fără detalierea subtemelor incluse. Aceste teme incluse, pentru fiecare temă complexă, pot fi afişate printr-un clic pe semnul de la începutul rândului. Când tema apare într-o listă, expandată cu subtemele incluse, are la începutul rândului semnul . Deasupra cadranului din stânga are ca titlu Căutare (în limba engleză Search) un instruent folosit pentru căutarea unor termeni în manualul aplicaţiei. După căutare, sunt afişate subiectele unde apar termenii căutaţi, iar efectuarea unui clic pe un subiect are ca rezultat afişarea acestuia în partea dreaptă a ferestrei de ajutor.
1.6.5. Închiderea aplicaţiei Încheierea lucrului cu aplicaţia ACCESS este similară cu închiderea oricărei aplicaţii Microsoft Office: din meniu se alege opţiunea > Închidere bază de date. Ieşirea din aplicaţie provoacă închiderea tuturor obiectelor deschise în acel moment. Sistemul, înainte de închiderea fiecărui obiect, testează dacă s -a făcut salvarea ultimelor modificări în modul proiectare, pentru fiecare obiect în parte, iar dacă nu sa realizat acest lucru se afişează mesajul de atenţionare, urmând ca alegerea opţiunilor de salvare fişier, închidere fără salvare sau revocarea închiderii aplicaţiei să se facă de utilizator. Combinaţia de taste pentru închiderea aplicaţiilor Windows, deci şi a aplicaţiei Microsoft ACCESS, este Alt + F4. Totodată, având o fereastră a sistemului şi ACCESS-ul poate fi închis cu un clic pe butonul cu semnul X din colţul din dreapta sus.
31
CAPITOLUL II. CREAREA ȘI UTILIZAREA TABELELOR 2.1. Crearea unei tabele Începând cu versiunile ulterioare anului 2007 ale programelor Microsoft Office, meniurile şi grupurile de instrumente care erau plasate sub meniu în fereastra aplicaţiilor, au fost înlocuite de aşanumita panglică. Această noutate a ferestrei aplicaţiei este proiectată cu scopul de a ajuta utilizatorii în a găsi rapid comenzile de care au nevoie pentru a realiza o activitate. Comenzile sunt organizate în grupuri logice care sunt grupate în file. Fiecare filă are legătură cu un anumit tip de activitate, cum ar fi scrierea sau asamblarea unei pagini. Pentru a reduce aglomerarea de pe ecran, unele file sunt dependente de context, fiind afişate doar când sunt necesare. Este cazul, de obicei a ultimei file, care conţine comenzi pentru obiectul curent în lucru, accesat sau selectat. Crearea unui tabel presupune crearea structurii sale. Acest lucru se poate realiza în fila Creare, în grupul de instrumente pentru tabele, recomandabil prin instrumentul Proiectare tabel:
Se mai poate apela la instrumentul Şabloane tabel. În ultimul timp se obişnuieşte ca să se promoveze nişte structuri predefinite pentru a crea tabele sau chiar baze de date, pentru diferite probleme. Neajunsul constă în faptul că structurile predefinite sunt greu pliabile pe o problemă reală. Probabil lipsesc câmpuri, sau sunt de tip necorespunzător şi nu în ultimul rând au denumiri greu de accep tat. Crearea tabelelor se poate face şi cu instrumentul Tabel, prin introducerea de date. Într-un format asemănător cu Excel se pot introduce date şi apoi în modul proiectare (în limba engleză: Design) se pot reboteza şi restructura câmpurile. Structuri de tabele pot fi realizate şi prin subprograme VBA, create de utilizatori.
32
Modul cel mai elegant, cel mai utilizat şi convenabil de creare şi apoi de modificare a structurilor tabelelor rămâne utilizarea ferestrei de proiectare, precum în figura de mai jos, în care este vizibilă automat ultima filă cu instrumente de prelucrare a tabelelor, fila Proiectare.
2.1.1. Proprietăţile câmpurilor În cadrul acestei ferestre se definesc numele câmpului (Field Name), tipul de date (Data Type) şi, opţional, o descriere a câmpului respectiv (Description). În panoul de jos al acestei ferestre se introduc celelalte proprietăţ i ale câmpului: ► Numele câmpului, poate fi format, precum orice nume de obiecte ale unei baze de date ACCESS 3, din maximum 64 de caractere (chiar şi diacritice!), poate să conţină spaţiu, dar nu poate să conţină unele caractere, printre care: „ [‖, „]‖,„.‖, „!‖. Atunci când în Microsoft ACCESS un nume de câmp este format din mai multe cuvinte, trebuie folosite parantezele drepte, de exemplu: [Cod numeric personal]. În majoritatea limbajelor de programare sau a SGBD-urilor, pentru a forma nume de câmpuri complexe, în sintaxă se recurge la utilizarea caracterului „_‖ (denumit în engleză: underscore), scriindu-se Cod_numeric_personal.
3
vezi Specificaţii pentru baze de date, http://office.microsoft.com/ro -
ro/access/HA100307391048.aspx?pid=CH100621861048
33
Microsoft ACCESS identifică un câmp prin numele său de câmp. După specificarea numelui de câmp, în Vizualizare proiect pentru tabel, numele poate fi utilizat în expresii, proceduri Visual Basic şi instrucţiuni SQL. Setarea acestei proprietăţi se efectuează în partea de sus a ferestrei deschise prin apelarea instrumentului Proiectare tabel din fila Creare, sau prin redenumirea unei coloane în fereastra deschisă prin apelarea instrumentului Tabel din fila Creare, sau utilizând un limbaj de programare (de obicei limbajul Visual Basic). ► Ca tipuri sau subtipuri de date se pot folosi: text, memo, număr, dată/oră, monedă, autonumerotare, da/nu, obiect OLE, etc. Proprietatea Tip de date se utilizează pentru a preciza tipul de date stocat în câmpul unui tabel. Fiecare câmp stochează date constând dintr-un singur tip de date. Proprietatea Tip de date utilizează următoarele setări: Setare Tip de date Dimensiune (Implicit) Text sau combinaţie de text şi Până la 255 de caractere sau o lungime Text numere, precum şi numere care nu mai mică setată prin proprietatea necesită calcule, cum ar fi numerele de Dimensiune câmp. Microsoft ACCESS telefon. nu rezervă spaţiu pentru porţiuni neutilizate din câmpul text. Text lung sau combinaţii de text şi Până la 65.535 de caractere. Memo numere. Noile versiuni ale ACCESS-ului înregistrează istoricul revizuirilor. Date numerice utilizate în calcule 1, 2, 4 sau 8 octeţi (16 octeţi dacă Număr matematice. proprietatea Dimensiune câmp este setată la ID reproducere). Valori de date calendaristice şi ore pentru 8 octeţi. Dată/Oră anii cuprinşi între 100 şi 9999. Noile versiuni ale ACCESS-ului oferă asistenţă pentru alegerea datei într-un calendar interactiv încorporat. Monedă Valori monetare şi date numerice 8 octeţi. utilizate în calcule matematice care implică date având între unul şi patru poziţii zecimale. Cu precizie de 15 cifre în stânga separatorului zecimal şi de 4 cifre în dreapta lui. AutoNu- Un număr secvenţial unic (incrementat 4 octeţi (16 octeţi dacă proprietatea Dimensiune câmp este setată la ID merotare cu 1). reproducere). Valori şi câmpuri Da şi Nu care conţin 1 bit. Da/Nu numai una din cele două valori (Da/Nu, Adevărat/Fals sau Activat/Dezactivat). Un obiect (cum ar fi o foaie de date Până la 1 gigaoctet (limitat la spaţiul-disc Obiect Microsoft Excel, un document Microsoft disponibil). OLE Word, grafică, sunete sau alte date binare) legate sau încorporate într-un tabel Microsoft ACCESS. 34
Hyperlink Text sau combinaţie de text şi numere stocate ca text şi utilizate ca adresă hyperlink. Permite stocarea de diferite tipuri de Atach documente şi fişierele binare în baza de date fără a creşte inutil dimensiunea bazei de date. Microsoft Access 2007 comprimă automat ataşările, când este posibil, pentru a minimiza utilizarea spaţiului.
Fiecare din aceste trei părţi ale tipului de date Hyperlink conţine până la 2048 de caractere. Limitat la spaţiul de disc disponibil.
Câmpurile Memo, Hyperlink şi Obiect OLE nu se indexează. Este bine să se utilizeze tipul de date Monedă pentru un câmp care necesită multe calcule implicând date cu una până la patru zecimale. Câmpurile cu tipul de date Simplă precizie şi Dublă precizie necesită calcule în virgulă mobilă. Tipul de date Monedă utilizează un calcul în virgulă fixă, mai rapid. ► Proprietatea Dimensiune câmp se utilizează pentru a seta dimensiunea maximă pentru datele stocate într-un câmp având tipul de date Text, Număr sau AutoNumerotare. Setarea acestei proprietăţi se face în strânsă legătură cu setarea proprietăţii tip de dată a câmpului (data type). Pentru proprietatea Tip date setată la Text, dimensiunea poate fi un număr de la 0 la 255. Setarea implicită este 50. Pentru AutoNumerotare, proprietatea Dimensiune câmp se setează la Întreg lung sau ID reproducere. Pentru proprietatea Tip date setată la Număr, setările proprietăţii Dimensiune câmp şi valorile lor sunt corelate ca în tabelul de mai jos: Setare Descriere Precizie Dimensiune zecimală de stocare Numere de la 0 la 255 (fără fracţiuni). Fără 1 octet Octet 38 38 Numere între -10 –1 şi 10 –1. 28 12octeţi Zecimal Numere între -32.768 şi 32.767 (fără fracţiuni). Fără 2 octeţi Întreg Numere de la –2.147.483.648 la 2.147.483.647 Fără 4 octeţi Întreg lung (fără fracţiuni). 7 4 octeţi Simplă precizie Numere de la –3,402823E38 la –1,401298E-45 pentru valori negative şi de la 1,401298E–45 la 3,402823E38 pentru valori pozitive. 15 8 octeţi Dublă precizie Stochează numere de la –1,79769313486231E308 la –4,94065645841247E–324 pentru valori negative şi de la 4,94065645841247E–324 la 1,79769313486231E308 pentru valori pozitive. Identificator unic global (GUID). N/A 16 octeţi ID reproducere Setarea acestei proprietăţi se efectuează numai din foaia de proprietăţi a tabelului. 35
Valorile implicite ale dimensiunilor de câmp pentru câmpurile Text şi Număr se pot seta prin modificarea valorilor de la opţiunea Designeri de obiecte, din fereastra afişată la selecţia Opţiuni Access, după ce se face clic pe Butonul Microsoft Office
. Este bine să se utilizeze cea mai mică posibil setare pentru proprietatea Dimensiune câmp deoarece dimensiunile mai mici de date sunt prelucrate mai rapid şi solicită mai puţină memorie. ► Numărul de zecimale (în limba engleză: Decimal places): În cadrul acestei proprietăţi se stabileşte numărul cifrelor afişate la dreapta separatorului zecimal, pentru valorile câmpului. Această setare are sens doar la tipurile de date Număr şi Monedă şi poate stabili între 0 şi 15 cifre s au Auto pentru determinarea automată a numărului de cifre pentru partea fracţionară. ► Format: formatul în care sunt afişate informaţiile. Important este de reţinut că proprietatea Format se utilizează pentru stabilirea modului în care numere, date calendaristice, timp şi text vor fi afişate şi tipărite şi nu afectează introducerea datelor. Această sarcină revine proprietăţii Input mask. ► Formatul de introducere, masca de intrare (în limba engleză: Input Mask) este şablonul de introducere a datelor Setarea formatului de afişare şi al celui de intrare poate fi o operaţie complexă şi anevoioasă. Este recomandabil ca de ea să facă uz programatorii experimentaţi. Neînţelegerea conţinutului informaţiei datorită unui format ambiguu poate duce la deteriorarea unor date. ► Legenda (în limba engleză: Caption), cu sensul pe care îl are în general în programarea orientată pe obiecte, aceasta permite specificarea unui al doilea nume în situaţiile în care primul nu este destul de semnificativ. El apare în antetul cererilor d e interogare, a formularelor şi a rapoartelor. ► Valoarea implicită (în limba engleză: Default Value): este o valoare care este atribuită automat de sistem, în momentul introducerii datelor, dacă utilizatorul nu completează acel câmp. ► Regulă de validare (în limba engleză: Validation Rule): este o restricţie de introducere a datelor care obligă datele să respecte expresia introdusă la această proprietate. În expresie practic se folosesc regulile de sintaxă specifice A CCESSului. ► Textul de validare (în limba engleză: Validation Text ): reprezintă textul care va apărea pe bara de mesaje în cazul în care valoarea introdusă nu respectă criteriul impus de regulă de validare. ► Obligatoriu (în limba engleză: Required): obligă sau nu completarea respectivului câmp în fiecare înregistrare. ► Indexat (în limba engleză: Indexed): poate suporta trei posibilităţi: a) Nu; 36
b) Da (Cu dubluri); c) Da (Fără dubluri).
2.1.2. Definirea unei chei primare După descrierea câmpurilor componente ale unei tabele este timpul să se stabilească cheia primară a acesteia. Dacă se uită, ACCESS avertizează utilizatorul de acest lucru şi automat creează un nou câmp şi-l propune ca şi cheie primară. Un tabel care nu are cheie primară nu poate stabili relaţii de tipul unu la mai mulţi cu alte tabele. O cheie primară poate fi formată din unul sau mai multe câmpuri. Dacă cheia primară este formată dintr-un singur câmp, se selectează acesta, se face clic dreapta şi apoi se activează butonul cheie primară. Dacă cheia primară este formată din mai multe câmpuri (cheie compusă)se selectează primul, iar celelalte se selectează în combinaţie cu apăsarea tastei CTRL, după care se face clic dreapta pe unul dintre ele şi apoi se activează butonul cheie primară. În Microsoft ACCESS cheia primară a unei tabelei trebuie să fie indexată şi fără dubluri. Într-un tabel poate exista o singură cheie primară. Dacă mai există şi alte câmpuri, indexate, fără dubluri, acestea sunt numite chei candidate.
2.1.3. Stabilirea unui index Indecşii sunt utilizaţi pentru a mări viteza de găsire a informaţiilor. Un index este o structură în funcţie de a cărei valori sunt ordonate înregistrările unui tabel. Un sistem de gestiune de baze de date se foloseşte de indecşii existenţi în diverse operaţii, de multe ori fără ca acest lucru să îi fie evident utilizatorului. Este recomandabil să se facă indecşi pe câmpurile după care se sortează rapoarte, formulare, interogări sau foi de date sau pe câmpurile care apar în expresii pentru realizarea relaţiilor dintre tabele. Un index pentru un singur câmp se poate face prin stabilirea proprietăţii indexat la Da (Cu dubluri) sau Da (Fără dubluri). Pentru crearea unui index bazat pe mai multe câmpuri se foloseşte instrumentul Indexuri, fila Instrumente tabel/ Proiectare. Această filă este accesibilă, atunci când un tabel este accesat pentru prelucrare, în modul vizulaizare proiect. În fereastra cu titlul Indexuri sunt cuprinşi toţi indecşii tabelului curent. Fiecare index are un nume şi poate utiliza până la 10 câmpuri. Expresia de sortare este formată prin concatenarea câmpurilor în ordinea în care apar în fereastră. De exemplu: indexul DenAdrTel este format din concatenarea câmpurilor [Dencli] ordonat ascendent, [Adresa] ordonat ascendent şi [Telcli] 37
ordonat descendent, indexul Codcli este format din câmpul [Codcli] ordonat ascendent, iar indexul PrimaryKey este format doar din câmpul [Codcli] în ordine ascendentă.
2.1.4. Modificarea proprietăţilor unui câmp Proprietăţile unui câmp se pot modifica uşor în modul proiectare. Modificarea proprietăţilor unui câmp nu afectează buna funcţionare a obiectelor în care apar date din câmpul modificat. De exemplu, dacă în tabelul „Clienţi‖, numele câmpului Adresa se modifică în Adrcli, atunci formularele, rapoartele sau interogările care folosesc date din acest câmp sunt în mod automat modificate, astfel încât, vor prelua datele din câmpul cu noul nume. Mărirea dimensiunii unui câmp nu duce neapărat la mărirea dimensiunii bazei de date, dar prelucrarea devine mai greoaie. Dacă la un tabel, care deja conţine date, se micşorează dimensiunea unui câmp, este posibilă pierderea unor date. Odată cu afişarea unei casete de atenţionare, asemănătoare celei de mai jos, utilizatorul poate să -şi revoce acţiunea. Însă, dacă utilizatorul selectează butonul Da în fereastra de mai jos, pierderea de informaţii devine ireversibilă. De exemplu, dacă se modifică setarea Dimensiune câmp a unui câmp de tip Text de la 100 la 50, partea de date care depăşeşte noua setare de 50 caractere se pierde. Dacă într-un câmp de tip Număr, datele nu încap în dimensiunea câmpului, numerele de după virgulă sunt rotunjite sau primesc valoarea Null. De exemplu, dacă se modifică o dimensiune de câmp de la Simplă precizie la Întreg, valorile fracţionare vor fi rotunjite la cel mai apropiat număr întreg, iar valorile mai mari decât 32.767 sau mai mici decât -32.768 vor deveni valori nule. Aceste modificări de date sunt ireversibile.
Problema se complică atunci când se doreşte schimbarea tipului unui câmp. Trebuie avut în vedere dacă sunt posibile conversiile datelor din vechiul tip în noul tip. Conversia din tipul număr în tipul monedă, sau invers, se face fără 38
probleme. Şi conversiile din numere în date/oră şi invers se fac fără probleme. Partea întreagă a numerelor se transformă în dată iar p artea fracţionară se transformă în oră. Numărul 0 se transformă în #1.1.1900#. Pentru numerele negative, se scade din 1 ianuarie 1900, un număr de zile egal cu valoarea numărului. Pentru ore se face ora partea 24
fracţionară . De exemplu: ora 12 se transformă în corespondenţa 0,5=12/24, ora 1 se transformă în 0,04167=1/24, ora 14h45’ devine 0,614583333=14 ¾ : 24, etc. Dacă se doreşte transformarea din text în numere cu format obişnuit, este necesar ca în toate celulele să existe doar caracterele necesare pentru a scrie numere: cifre, marcatorul zecimal şi semnele „+‖ şi „-‖.
2.2. Editarea datelor într-o tabelă 2.2.1. Introducerea de date în tabelă În cadrul aplicaţiilor ACCESS datele pot fi introduse în tabele printr -o multitudine de metode, user-friendly, asigurate de însăşi SGBD. Cea mai directă este printr-o fereastră Foaie de date (în limba engleză: Datasheet). În cadrul acestei ferestre se pot vizualiza, şi/sau modifica datele deja introduse sau se pot introduce date noi. Stilul de editare urmează de obicei convenţiile din aplicaţiile pachetului Office. Un exemplu de introducere de date în modul foaie de date este redat în exemplul figură.
Poziţionarea în cadrul tabelului se poate face cu mouse -ul sau cu tastele pentru deplasare în cadrul foii (Tastele cu săgeţi, Insert, Home, PageUp, PageDown). În plus, trecerea la câmpul următor se face cu tasta TAB sau apăsând ENTER după ce sau introdus date în celulă. Trecerea la câmpul anterior se face cu ajutorul combinaţiei de taste Shift + TAB.
39
Articolul pe care este poziţionat cursorul este evidenţiat prin accent de culoare. La editare se ţine cont de proprietăţile câmpurilor stabilite în modul proiectare, nepermiţându-se violarea regulilor de integritate. În tabelă se pot introduce date din alte zone ale tabel ului, din alte tabele sau chiar alte aplicaţii (Word, Excel) prin intermediul ferestrei Clipboard, prin operaţiile Decupare sau Copiere, cuplate cu operaţia Lipire. Ca o particularitate generată de lucrul cu înregistrările, în A CCESS este aproape omniprezent grupul de butoane de navigare cu semnificaţie similară butoanelor de pe aparatele audio sau video.
Pentru introducere/editare de informaţii totuşi nu se obişnuieşte utilizarea ferestrei Datasheet. De obicei programatorii proiectează formulare, cu po sibilităţile oferite de SGBD, pentru introducerea de date în tabele. Totuşi, pentru tabele mici, nu se justifică efortul pentru a se crea formulare.
2.2.2. Vizualizarea informaţiilor dintr-o tabelă Vizualizarea informaţiilor dintr-o tabelă se face selectând din meniul generat prin acţiunea asupra butonului Office , opţiunea Imprimare > Examinare înaintea imprimării. ACCESS desenează automat un tabel şi îl vizualizează în pagini, în funcţie de setările imprimantei. Dar, în mod frecvent, vizualizarea informaţiilor dintr -o tabelă se face prin modul Foaie de date. În acest mod vizualizarea este însoţită de posibilitatea de modificare a informaţiilor. Tot cu actualizare, se pot vizualiza informaţiile din tabele cu ajutorul unor formulare sau interogări create pe baza tabelului.
2.2.3. Modificarea datelor într-o tabelă Datele în tabele se modifică la în modul Foaie de date la nivel de celule. Pentru a modifica date, întâi trebuie să se ajungă la celula în cauză. Nu este posibilă editarea câmpurilor de grup şi a câmpurilor calculate. Datele pot fi modificate numai în câmpurile unde există posibilitatea să se insereze un cursor de introducere de la tastatură; existenţa unui astfel de cursor semnifică acceptarea editării în coloana respectivă. Deplasarea în cadrul celulelor tabelului se face cu tastele săgeţi. Trecerea de la o celulă, la celula vecină din dreapta sau la prima din rândul imedia t următor se face cu tasta TAB. Deplasarea în sens invers se face apăsând concomitent Shift + TAB. 40
Datele modificate dintr-o celulă sunt salvate imediat după părăsirea ei. Revocarea modificărilor făcute în celula curentă se face apăsând ESC. Pentru a revoca modificările din întreaga înregistrare, se face clic pe Anulare din bara de instrumente de navigare în înregistrări sau opţiunea cu acelaşi nume din meniul Editare. Această anulare nu mai este posibilă dacă se mai fac alte modificări într-o altă înregistrare.
2.2.4. Adăugarea de înregistrări într-o tabelă În modul Foaie de date, de obicei, este afişat în plus un articol gol evidenţiat cu semnul „*‖. Acest articol nu este socotit în totalul articolelor. Dacă se poziţionează cursorul pe acest articol, semnul * dispare până când se începe editarea în articolul respectiv. În acel moment se adaugă imediat un nou articol gol marcat cu „*‖. De asemenea, un articol se adaugă imediat în plus în tabelă, când cursorul se găseşte pe ultima celulă din ultimul rând şi se apasă tasta TAB sau Săgeată Dreapta. Din panglica ferestrei ACCESS, pentru a se adăuga unui tabel o înregistrare nouă, se poate utiliza instrumentul Nou, din grupul Înregistrări, fila Pornire. Combinaţia de taste pentru realizarea acestei operaţii este Ctrl + +. Într-o tabelă se pot adăuga mai multe articole din alte zone ale tabelului, din alte tabele sau chiar alte aplicaţii (Word, Excel), prin intermediul ferestrei Clipboard, prin operaţiile Decupare sau Copiere, cuplate cu operaţia Lipire. Selectarea mai multor înregistrări dintr-un tabel se face glisând cu mouse-ul pe zona de selecţie situată la începutul rândurilor. Odată selectate articolele dorite de a fi adăugate, se selectează fila Pornire a panglicii ACCESS, opţiunea Decupare sau Copiere, se selectează rândul din tabel marcat cu „*‖ şi apoi cu opţiunea Lipire (din acelaşi grup de instrumente) se adaugă articolele, nu înainte de a se cere confirmarea de la utilizator pentru a se realiza adăugarea acestor articole.
2.2.5. Ştergerea de date într-o tabelă Pentru a şetrge înregistrări într-o bază de date, mediul de dezvoltare ACCESS are prevăzute instrumentul Ştergere în fila Pornire din Panglica Office a aplicaţiei ACCESS. Instrumentul are trei funcţiuni, putând fi folosit atât pentru şte rgerea conţinutului celulei curente, a înregistrării curente sau a câmpului curent. Ştergerea înregistrării curente dintr-o tabelă se poate face şi alegând din meniul generat de sistem, după ce s-a acţionat printr-un clic dreapta pe una sau mai multe înregistrări selectate opţiunea Ştergere înregistrare.
41
Dacă se selectează această opţiune din meniu, după ce sunt selectate mai multe înregistrări, atunci aceste înregistrări vor fi şterse, împreună cu înregistrările în cascadă, dacă sunt definite relații între tabele în bazele de adte. Pentru protecţia datelor împotriva ştergerilor accidentale, operaţia de ştergere se revocă, dacă operatorul nu confirmă ştergerea, într -o casetă de forma celei de mai jos.
În mod similar, dacă se selectează una sau mai multe coloane, şi se face clic drepata pe selecţie, se pot şterge câmpuri din tabelă. şi această ştergere necesită confirmarea operatorului. Câmpurile pot fi şterse şi cu instrumentul Ştergere din grupul Câmpuri şi coloane, fila Instrumente tabel/Foaie de date.
Exerciţii: Concepeţi o structură pentru următorul set de date şi realizaţi un tabel pe baza acesteia, în mediul Access, a cărui denumire va fi ANG şi în care se adaugă, ca înregistrări, datele. NrAng NumeA 2000 CHIVU
Fnc SOFER
ANG Csf DataAnga Sal Rate NrDept 2956 23.02.1999 950
30
2555 CONTRA VANZATOR 2956 19.12.2000 1250 1400
30
2049 DOROS
INGINER
2553 31.10.2000 2975
20
2731 MARCU ANALIST
2049 19.03.1999 3000
20
2956 NEGRU
INGINER
2553 11.01.1999 2850
30
2084 OSAN
ANALIST
2049 19.12.2000 3000
2789 POPA
VANZATOR 2956 26.03.1999 1250
2396 POPAN
SOFER
2553 RUSU
DIRECTOR
2024 TULIAN
VANZATOR 2956 23.01.1999 1500
2987 VUSCA
VANZATOR 2956 15.09.1999 1600
2643 ZIMAN
INGINER
20 500
30
2084 13.01.1999 800
20
19.02.1999 5000
10
2553 14.10.1999 2450
30 300
30 10
Concepeţi structuri pentru tabelele din figura de ilustrare a relaţiilor de la 1.2.
42
2.3. Crearea relaţiilor între tabele Relaţiile dintre tabele pot fi percepute logic în cadrul schemei conceptuale 4 sau pot fi specificate şi salvate în baza de date. Specificarea unei relaţii dintre tabele în baza de date, se realizează cu instrumentul din fila Instrumente bază de date din panglica Access. Pentru a putea stabili o relaţie între două tabele sunt necesare două condiţii: ► tabelul sursă a relaţiei să aibă o cheie primară (un câmp de identificare cu valori unice şi diferite de Null); ► tabelul destinaţie trebuie să aibă un câmp cheie străină, de acelaşi tip de dată şi dimensiune cu cheia primară. Atunci când se acţionează asupra instrumentului se deschide o fereastră de alegere a tabelelor între care se stabilesc relaţiile. Se aleg tabelele de lucru cu dublu clic al mouse-ului iar apoi se închide fereastra acţionându-se asupra butonului Închidere. Exemplificăm acest lucru cu două tabele Prod şi FactProd. Stabilirea relaţiei se poate face glisând, în fereastra relaţii, câmpul cheie primară peste câmpul cheie străină. Automat este afişată fesreastra Editare relaţii, în care se precizează câmpurile prin care se realizează relaţia şi tipul de integritate referenţială impusă.
După apăsarea butonului Creare, relaţia este stabilită şi afişată. 4
Schema conceptuală reprezintă descrierea fenomenelor din realitatea înconjurătoare cu ajutorul
entităţilor şi atributelor şi a legăt urilor dintre acestea.
43
O relaţie între tabele este reprezentată printr-o linie de relaţie trasată între tabelele din fereastra Relaţii. O relaţie care nu are impusă integritatea referenţială apare ca o linie subţire între câmpurile comune care acceptă relaţia. Când relaţia este selectată, făcând clic pe linia sa, linia este afişată îngroşat. Cu dublu clic se pot reedita condiţiile ei. Dacă este impusă integritatea referenţială pentru o relaţie, linia apare îngroşată la fiecare capăt. În plus, numărul 1 apare deasupra porţiunii îngroşate a liniei în partea unu a relaţiei, iar simbolul infinit (∞) apare deasupra liniei în cealaltă parte.
Exerciţiu: Creaţi relaţiile între tabelele create în ultimul exerciţiu al subcapitolului precedent.
44
CAPITOLUL III. UTILIZAREA INFORMAŢIILOR DINTR-O BAZĂ DE DATE 3.1. Conectarea la o bază de date existentă Structurarea informaţiilor în tabelele bazelor de date şi modalitatea managementului acestora e diferită în funcţie de tipul bazelor de date, păstrând particularitatea SGBD-ului în care a fost definită şi creată colecţia de date. În scopul utilizării selective, raţionale, distribuite, eficiente şi securizate a datelor, interogările asigură conectarea la informaţiile din tabelele bazelor de date. Prin intermediul interogărilor, informaţiile din tabelele bazelor de date pot fi utilizate ca sursă de înregistrări pentru formulare şi rapoarte. Obiectele din ACCESS pot prelua informaţii din baze de date de tip Access, dar şi de tip SQL-Server, MSDE, sau chiar MySQL şi FoxPro. Preluarea informaţiilor din tabele acestor baze de date presupune conectarea aplicaţiei realizată de utilizator la aceste baze de date existente. O aplicaţie Access poate include tabele proprii, iar conectarea la acestea este automată. Conectarea la tabele din alte baze de date, chiar de tipul Access, neincluse în aplicaţia curentă, este o operaţie dificilă şi se poate realiza cu ajutorul obiectelor de tip module Visual Basic for Application, care recurg, la rândul lor, la interogări.
3.2. Căutarea datelor cu ajutorul instrumentelor mediului Access Căutarea informaţiilor în bazele de date presupune localizarea câmpurilor şi înregistrărilor în care sunt stocate. Pentru căutarea sau modifcarea unei înregistrări, fie că se lucrează într-un tabel, într-un formular sau interogare, se alege din meniu opţiunea din fila Pornire a Panglicii Microsoft Office, unul dintre instrumentele de Căutare, Găsire şi/sau Înlocuire, sau Salt la .
45
Se afişează fereastra cu titlul Căutare şi înlocuire, existentă în toate aplicaţiile pachetului Microsoft Office, cu câteva particularităţi specifice pentru lucrul cu bazele de date. În caseta de text De căutat, se scrie textul care trebuie căutat. Ultimele şiruri căutate, rămân în istoricul din lista ascunsă ataşată casetei, şi la nevoie pot fi readuse. Cătarea se face doar în câmpul curent sau în întreg tabelul, în funcţie de opţiunea aleasă din lista Privire în:. Şirul căutat trebuie să se potrivească cu începutul câmpului, cu întreg câmpul sau cu o parte oarecare din câmp, după cum se alege din lista Potrivire:. De exemplu, dacă se caută textul os într-un catalog de produse, dacă se alege prima opţiune, se pot găsi articole în care există câmpuri care încep cu textul os (osânză, os, ospen, etc); dacă se alege a doua opţiune, se pot găsi doar acele articole în care există câmpuri care conţin cuvântul os şi nimic altceva; dacă se alege a treia opţiune, se pot găsi articole în care există câmpuri care conţin textul os (osânză, costume, costiţe, os, ospen, etc). Articolele în care se caută pot fi: de la articolul curent în sus, în jos sau printre toate articolele din tabel. În fine, se poate bifa caseta de selecţie Potrivire litere mari şi mici dacă se doreşte să se facă căutări insensibile la litere mari sau mici sau se poate bifa caseta de selecţie Căutare în câmpuri după format pentru a se ţine cont de formatul de afişare a datelor din câmpuri.
3.3. Crearea unei interogări simple Specifică bazelor de date relaţionale, cum sunt şi bazele de date Access, este uşurinţa de a crea interogări. Prin deschiderea pe care o au pentru a răspunde acestor interogări, bazele de date pot constitui obiectul aplicaţiilor scrise în oricare limbaj de programare modern. Interogarea bazei de date permite, în principal, extragerea şi afişarea unor informaţii din înregistrările din una sau mai multe tabele; înregistrări care verifică anumite condiţii, care sunt grupate în funcţie de anumite criterii sau sunt ordonate crescător sau descrescător. Termenul "interogare" este sensul principal al cuvântului englezesc "Query". În bazele de date relaţionale acest termen are un sens mai larg. Acest termen se referă, pe lângă interogările de extragere (selecţie) şi la aşa -zisele interogări de acţiune (de creare, de ştergere sau actualizare) pentru tabele le şi elementele lor constitutive. Lucrul cu interogările este posibil prin implementarea limbajului SQL într-un SGBD. Interogările sunt în ultimă instanţă, comenzi ale unui limbaj simplu, structurat de manevrare a informaţiilor din tabelele unei baze de date relaţionale. Acest limbaj 46
se numeşte SQL (abreviere pentru sintagma din limba engleză: structured query language, care se traduce în limba română: limbaj structurat de interogare). Rezultatul execuţiei unei cereri, în majoritatea limbajelor, se numeşte vedere, deoarece este un cadru în care sunt accesibile o anumită parte a informaţiilor din baza de date. Cele mai importante moduri de vizualizare a interogărilor sunt: mo dul proiectare, foaie de date şi vizualizare SQL. Important de reţinut este faptul că e vorba despre acelaşi conţinut, etalat în trei moduri diferite de vizualizare. Dintre aceste posibilităţi, modul proiectare este cel mai recomandat pentru a crea interogări în mod eficient (interogări simple). Pentru a crea o cerere de interogare în modul proiectare se parcurg paşii: Din fila Creare, din ultimul grup de instrumente se face clic pe butonul Proiectare interogare.
Sistemul deschide automat fereastra de creare asistată a interogărilor. Se afişează o casetă de dialog cu titlul Afişare tabel care conţine cadrele de pagini Tabele, Interogări şi Ambele. În aceste cadre de pagină sunt afişate toate tabelele şi interogările din baza de date curentă. Interogarea care va fi creată va conţine rânduri şi coloane derivate din tabelele sau interogările pe care utilizatorul le va extrage din caseta Afişare tabel. Pentru a extrage obiecte, se face dublu clic pe numele fiecărui obiect care se doreşte a fi folosit. Ace astă casetă de afişare a tabelelor se ascunde printr-un clic pe butonul Închidere.
47
Automat apare vizibililă ultima filă cu instrumente de lucru pentru interogări, şi anume fila Instrumente interogări/Proiectare. Fereastra de lucru are două părţi: În partea de sus sunt afişate casetele cu structuri de înregistrări ale tabelelor sau interogărilor selectate din caseta afişare tabel. Dacă nu e vizibilă caseta Afişare tabel, acest lucru se poate face selectând din panglică instrumentul Afişare tabel sau făcând clic dreapta şi apoi selectare Afişare tabel. Partea de jos a ferestrei se numeşte grilă de proiectare ( design grid), şi conţine elemente cu ajutorul cărora se pot construi interogări . Aceasta mai este cunoscută şi sub numele de grilă QBE (Prescurtare de la sintagma din limba engleză: Query By Exemples care poate fi tradusă în româneşte Interogare prin exemple). Câmpurile se pot prelua din structurile sursă prin glisarea lor din casetele de structuri în primul rând al grilei QBE (rândul Câmp), unde se înscriu câmpurile interogării rezultate. Câmpurile pot fi înscrise şi efectiv în această linie sau alese din lista ascunsă ataşată liniei câmp din grilă. Dacă se doreşte aducerea tuturor câmpurilor dintr-o tabelă, se scrie semnul „*‖ în rândul câmpurilor, iar în rândul tabel se înscrie tabelul în cauză. Pe lângă câmpurile preluate din tabelele sau interogările disponibile, interogările pot conţine şi câmpuri calculate. Calculele se pot face linie cu linie sau pe grupuri de înregistrări. Pentru a se obţine o coloană în primul caz, se înscrie în linia Câmp formula de calcul care are forma generală: Nume-rezultat: expresie_aritmetică_sau_logică De exemplu: Valoarea: cantitate*[Pret unitar] vârsta: (Date() - [data naşterii])/365 NB: Expresia Date() se referă reprezintă o funcţie care returnează data sistemului.
Exerciţii: 1. Să se extragă din tabelul PROD numele, unitatea de măsură şi pretul cu TVA. 2. În modul Vizualizare SQL observaţi comanda SQL construită.
48
Rezolvare: 1. În fila Creare a panglicii, se alege penultima opţiune Proiectare interogare. În caseta Afişare tabel se selectează „Prod‖, se apasă Adăugare şi apoi Închidere. Se glisează apoi câmpurile DenProd şi UM din caseta cu structura tabelului „Clienti‖ în rândul Câmp din grila QBE, iar pentru al treilea câmp se scrie expresia "pret cu TVA": [pret]*1,19. Lansarea în execuţie a interogării se face apăsând butonul cu semnul „!‖ din fila specială de instrumente de proiectare interogări din panglică. Pentru eventualele modificări se alege din nou modul proiectare din meniul primei op ţiuni, Vizualizare, din fila mai sus amintită. 2. Se alege din meniul Vizualizare din panglică, opţiunea Vizualizare SQL şi se obţine comanda: SELECT CLIENTI.Dencli, CLIENTI.Adresa, CLIENTI.Telcli FROM CLIENTI; Plasăm în figură interogarea în modurile foaie de date şi SQL. Pentru a face calcule pe grupuri de înregistrări, trebuie adăugată o linie, cu numele Total, în grila QBE, acţionând în panglică asupra butonului Σ Totaluri. Celulele din această linie trebuie neapărat să conţină ceva dacă este bifată caseta din rândul afişare a coloanei la care se referă. În acest caz, pe rândul total trebuie să existe, fie expresia Grupare după, fie o funcţie de grup (sumă, medie, contor, min, max, etc). 49
Exerciţiu: 1. Să se extragă din tabela „ANG”, media vechimii tuturor angajaţilor. 2. Să se extragă din tabela „ANG”, suma salariului angajaţilor pe departamente. 3. În modul Vizualizare SQL observaţi comanda SQL construită la punctul 2. Rezolvare: 1. Se creează o nouă interogare, în modul proiect alegându-se ca sursă tabelul „ANG‖. În grila QBE, prima coloană, în prima celulă se face clic şi apoi se înscrie: vechimea:(Date() - DataAnga)/365 În panglică se acţionează asupra butonului Σ Totaluri pentru a se adăuga rândul Total la grilă. În acest rând, la coloana vechimea se alege din lista ataşată, funcţia Medie. Se lansează în execuţie interogarea. Comanda SQL corespunzătoare este afișată în medalion în figură. Și rezultatul execuției interogării este suprapus într -un medalion peste figură.
Dacă se dorește ca să se partcilularizeze rezultatul, este accesibilă modificarea proprietăților, în Foaia de proprietăți.
50
2. Se creează o nouă interogare, în modul proiect, alegându-se ca sursă tabelul „ANG‖. În grila QBE, prima coloană se alege câmpul „NrDept‖, iar în a doua se alege câmpul „Sal‖. Se adaugă linia de total, în care se înscrie pentru prima coloană Grupare după iar la a doua coloană se alege funcţia Sum. Se lansează în execuţie interogarea, iar rezultatul şi comanda SQL corespunzătoare sunt: SELECT ANG.NrDept, Sum(ANG.Sal) AS [Suma salariu pe departamente] FROM ANG GROUP BY ANG.NrDept; NrDept
Suma salariu pe departamente 10
7450
20
9775
30
9400
3.4. Crearea interogărilor peste mai multe tabele Pentru formularea unor cereri de interogare bazate pe mai multe tabele sau interogări, este necesar ca între acestea să existe legături. Dacă relaţiile au fost create prin funcţiunea Relaţii din fila Instrumente bază de date, precum s-a explicat în capitolul anteriror, relaţiile pot fi considerate permanente. În afara acestora, există unele relaţii stabilite în timpul executării interogărilor. Primul tip de relaţii este vizibil, în mod automat, şi în partea de sus a ferestrei pentru realizarea înregistrărilor. Atunci când se încearcă o interogare din mai multe tabele şi/sau cereri de interogare, trebuie verificată natura reală a relaţiilor dintre acestea pentru ca datele din noua interogare să fie corespunzătoare cerinţelor problemei. O relaţie care nu a fost definită corect duce la rezultate inexplicabile. Dacă se face o cerere de interogare din două tabele cu un număr de n şi respectiv m înregistrări, şi nu se stabileşte nici o relaţie între tabelele sursă, atunci rezultatul va conţine m x n înregistrări. 51
Explicarea stabilirii relaţiilor în interogări, o vom exemplifica cu ajutorul a două tabele: „Prod” şi „FactProd”, care au structurile ce pot fi deduse din conţinutul prezentat în figură:
Între aceste două tabele există o relaţie de 1 la n prin câmpul cod, de la „Prod” spre „FactProd‖. Acest lucru trebuie înţeles prin faptul că fiecare produs poate fi conţinut în mai multe facturi. Dacă nu există legăturile necesare între tabele, acestea se pot stabili doar pentru interegarea curentă, printr-o procedură similară stabilirii relaţiilor între tabelele bazelor de date, prin glisarea mouse-ului de pe câmpul de legătură din tabela principală spre câmpul de legătură din tabela secundară. Cele două câmpuri trebuie să fie de acelaşi tip şi dimensiune şi pot fi chei primare sau externe. Dacă deja există o legătură, permanentă sau propusă de sistem, caracteristicile acesteia pot fi modificate sau pur şi simplu legătura poate fi ştearsă. În stabilirea relaţiilor în interogări, există trei posibilităţi (vez i figura cu proprietăţi de asociere):
52
Pentru prima posibilitate, relaţia este definită astfel încât, în asocierea celor două tabele se iau înregistrările pentru care câmpurile de relaţie au valori egale. Este cel mai întâlnit tip de relaţie, se numeşte echicompunere şi, în exemplul nostru, se descrie în comanda SQL, astfel: FROM [Prod] INNER JOIN FactProd ON Prod.Codpro = FactProd.Codprod Pentru cea de a doua posibilitate, relaţia este definită astfel încât, în asocierea celor două tabele se iau toate înregistrările din catalog, şi înregistrările din rulaj pentru care câmpurile de relaţie au valori egale. Relaţia se numeşte compunere externă stânga. În exemplul nostru, în comanda SQL, relaţia este descrisă astfel: FROM [Prod] LEFT JOIN FactProd ON Prod.Codpro = FactProd.Codprod În cea de a treia posibilitate, relaţia este definită astfel încât, în asocierea celor două tabele, se iau toate înregistrările din rulaj, şi înregistrările din catalog pentru care câmpurile de relaţie au valori egale. Pentru exemplul nostru, în comanda SQL, relaţia este descrisă astfel: FROM [Prod] RIGHT JOIN FactProd ON Prod.Codpro = FactProd.Codprod
Exerciţiu: Să se extragă din tabelele „Prod” şi „FactProd‖ valoarea produselor pe facturi. Să se verifice rezultatul obţinut prin cele 4 posibilităţi din punct de vedere al asocierii: cele 3 din figura de mai sus şi ruperea legăturii.
53
Rezolvare:
DenProd
Datafact
49,05
1
21.10.2009 Ulei
65,40
1
21.10.2009 Ulei
65,40
1
21.10.2009 Ulei
65,40
1
21.10.2009 Zahar 46,50
5
24.10.2009 Ulei
35,97
5
24.10.2009 Ulei
35,97
5
24.10.2009 Ulei
35,97
1
21.10.2009 Lapte 43,50
4
23.10.2009 Ulei
39,24
4
23.10.2009 Ulei
39,24
4
23.10.2009 Ulei
39,24
1
21.10.2009 Malai 33,75
1
21.10.2009 Zahar
46,50
1
21.10.2009 Zahar 46,50
1
21.10.2009 Zahar 46,50
1
21.10.2009 Ulei
65,40
5
24.10.2009 Zahar
77,50
5
24.10.2009 Zahar 77,50
5
24.10.2009 Zahar 77,50
1
21.10.2009 Zahar 62,00
2
22.10.2009 Lapte
87,00
2
22.10.2009 Lapte
87,00
2
22.10.2009 Lapte 87,00
1
21.10.2009 Lapte 58,00
5
24.10.2009 Lapte
58,00
5
24.10.2009 Lapte
58,00
5
24.10.2009 Lapte 58,00
1
21.10.2009 Malai 45,00
6
25.10.2009 Lapte
43,50
6
25.10.2009 Lapte
43,50
6
25.10.2009 Lapte 43,50
2
22.10.2009 Ulei
98,10
3
22.10.2009 Lapte
52,20
3
22.10.2009 Lapte
52,20
3
22.10.2009 Lapte 52,20
2
22.10.2009 Zahar 93,00
4
23.10.2009 Lapte
43,50
4
23.10.2009 Lapte
43,50
4
23.10.2009 Lapte 43,50
2
22.10.2009 Lapte 87,00
2
22.10.2009 Malai 67,50
5
24.10.2009 Ulei
5
24.10.2009 Zahar 77,50
5
24.10.2009 Lapte 72,50
5
24.10.2009 Malai 56,25
5
24.10.2009 Ulei
5
24.10.2009 Zahar 34,10
5
24.10.2009 Lapte 31,90
5
24.10.2009 Malai 24,75
5
24.10.2009 Ulei
5
24.10.2009 Zahar 62,00
5
24.10.2009 Lapte 58,00
5
24.10.2009 Malai 45,00
6
25.10.2009 Ulei
6
25.10.2009 Zahar 46,50
6
25.10.2009 Lapte 43,50
6
25.10.2009 Malai 33,75
81,75
35,97
65,40
49,05
54
Val
21.10.2009 Ulei
Val
1
Malai
Nrfact
DenProd
Nrfact
Datafact
Val
DenProd
Nrfact
Datafact
Val
DenProd
Datafact
Nrfact
Se creează o nouă interogare, în modul proiect alegându-se ca sursă tabelele „Prod” şi „FactProd‖. Se glisează apoi câmpurile NrFact, DataFact, DenProd casetele cu structurile tabelelor în rândul Câmp din grila QBE, iar pentru al patrulea câmp se scrie expresia Pret*Cant. În primul caz vom verifica rezultatul dacă nu există nici o legătură între tabele, apoi se verifică rezultatul în celelalte 3 posibilităţi de asociere. Rezultatele obţinute sunt prezentate în tabelul următor:
22.10.2009 Ulei
3
22.10.2009 Zahar 55,80
3
22.10.2009 Lapte 52,20
3
22.10.2009 Malai 40,50
4
23.10.2009 Ulei
4
23.10.2009 Zahar 37,20
4
23.10.2009 Lapte 34,80
4
23.10.2009 Malai 27,00
4
23.10.2009 Ulei
4
23.10.2009 Zahar 46,50
4
23.10.2009 Lapte 43,50
4
23.10.2009 Malai 33,75
Val
DenProd
Datafact
Nrfact
Val
DenProd
Datafact
Nrfact
Val
DenProd
Datafact
Nrfact
Val
DenProd
Datafact
Nrfact 3
58,86
39,24
49,05
În primul caz, atunci când nu au fost definite legături între tabele, s -au făcut asocieri pentru fiecare dintre cele 4 tupluri din primul tabel (cu structura DenProd – câmp individual şi Pret – ca parte componentă pentru Val) cu fiecare dintre cele 10 tupluri din al doilea tabel (cu structura NrFact şi DataFact – câmpuri individuale şi Cant – ca parte componentă pentru Val). Au rezultat 40 de articole. În cel de-al doilea caz s-au făcut asocieri doar petru tuplurile care provin din articole în care Prod.Codpro = FactProd.Codprod, rezultând 10 articole. Pentru cel de-al treilea caz s-a făcut adăugare la rezultatul cazului doi, tuplurile din tabelul Prod care nu au corespondent în tabelul FactProd (e vorba de produsul Malai). În cel de-al patrulea caz, s-ar fi făcut adăugiri la rezultatul cazului al doilea, tupluri din tabelul FactProd care nu au corespondent în tabelul Prod, dar nu au fost găsite.
Exerciţiu: Să se extragă din tabelele „Clienti”, „FactGen”, „Prod” şi „FactProd‖ valorile din toate câmpurile şi din toate înregistrările, respectând relaţiile dintre tabele. Rezolvare: Se creează o nouă interogare, în modul proiect alegându-se ca sursă tabelele „Clienti”, „FactGen”, „Prod” şi „FactProd‖. Se glisează apoi câmpurile celor patru tabele, avându-se grijă ca dintre câmpurile de relaţie să fie incluse doar o parte, 55
salvăm interogarea cu numele totul si toate. totul si toate Codcli
Dencli
Adresa
Telcli
Nrfact
Datafact
Delegat
222222
SC Secundul SRL
Str.Verii nr 2
0261222222
1
21.10.2009
Popescu
1075
Zahar
kg
15
3,1
222222
SC Secundul SRL
Str.Verii nr 2
0261222222
1
21.10.2009
Popescu
1009
Ulei
Fl
20
3,27
222222
SC Secundul SRL
Str.Verii nr 2
0261222222
2
22.10.2009
Popescu
7023
Lapte
l
30
2,9
333333 SC Terţa Parte SRL
Str.Toamnei nr 3 0261333333
5
24.10.2009
Trandafir
1075
Zahar
kg
25
3,1
333333 SC Terţa Parte SRL
Str.Toamnei nr 3 0261333333
5
24.10.2009
Trandafir
1009
Ulei
Fl
11
3,27
333333 SC Terţa Parte SRL
Str.Toamnei nr 3 0261333333
5
24.10.2009
Trandafir
7023
Lapte
l
20
2,9
0261222222
6
25.10.2009
Popescu
7023
Lapte
l
15
2,9
Str.Verii nr 2
Codprod DenProd UM Cant
Pret
222222
SC Secundul SRL
111111
SC First SRL
Str Primaverii nr 1 0261111111
3
22.10.2009
Anton
7023
Lapte
l
18
2,9
111111
SC First SRL
Str Primaverii nr 1 0261111111
4
23.10.2009
Anton
1009
Ulei
Fl
12
3,27
111111
SC First SRL
Str Primaverii nr 1 0261111111
4
23.10.2009
Anton
7023
Lapte
l
15
2,9
3.5. Selecţia şi sortarea datelor Datele rezultate pot fi ordonate crescător sau descrescător, după unul sau mai multe câmpuri. Pentru aceasta se realizează clic în celula de la intersecţia coloanei câmpului cu caseta Sortare şi apoi se alege între Ascendentă sau Descendentă. În cazul în care se specifică mai multe câmpuri de ordonare (chei de sortare), operaţia se execută începând cu primul câmp din stânga introdus în expresia de sortare şi continuând cu celelalte, spre dreapta. Dacă succesiunea câmpurilor de sortare este alta decât la afişare, trebuie introduse câmpuri care nu se afişează dar sunt intercalate în succesiunea de ordonare dorită. Succesiunea câmpurilor de sortare influenţează rezultatul operaţiei de sortare a articolelor. Operaţia de selecţie se referă atât la selecţia informaţiilor din coloane, dar şi la selecţia informaţiilor din rânduri. Pentru realizarea acestui aspect există linia Criterii, unde se introduc criteriile de selecţie. Criteriile se înscriu în formatul: Operator_relaţional expresie Operator_relaţional poate fi =, >, <, >=, <=, !=, between, in etc. Dacă nu se specifică niciun operator se consideră a fi semnul „=‖. Criteriile se pot compune cu ajutorul operatorilor logici AND/OR şi o serie de cuvinte rezervate şi expresii definite de utilizatori.
Exerciţiu: Să se extragă, din tabelul „ANG”, numele şi funcţia angajaţilor care au salariu între 1500 şi 3000, ordonaţi descrescător după vechime. 56
Rezolvare: Se creează o nouă interogare bazată pe tabelul „ANG”. Se stabilesc ca şi câmpuri ale interogării: [NumeA], [Fnc], [DataAnga] şi [Sal]. Câmpul DataAnga şi Sal nu trebuie bifate la rândul Afişare şi la Criterii trebuie înscris >10, iar în rândul Sortare pentru [DataAnga] se selectează Ascendentă. Grila QBE este redată în figura de mai jos:
3.6. Salvarea unei interogări Salvarea unei interogări se face la părăsirea ei, sau prin alegerea opţiunii de meniu Salvare sau Salvare ca…, din meniul Butonului Office . O interogare salvată poate fi folosită în obiecte ca şi cum ar fi un tabel, în special ca sursă de înregistrări pentru formulare sau rapoarte. De asemenea, interesant es te faptul că o interogare poate fi salvată sub formă de raport sau interogare.
3.7. Adăugarea şi eliminarea filtrelor Filtrele au o menire asemănătoare interogărilor de selecţie deoarece ambele selectează o anumită parte din date, pot ordona aceste date , pot furniza surse de date pentru formulare sau rapoarte, permit modificarea informaţiilor din cadrul rezultat. Chiar şi formatul ecranului pentru construcţie asistată a filtrului sau înregistrărilor sunt foarte asemănătoare.
57
Alegerea filtrului în locul înregistrărilor este motivată atunci când operaţia de extragere de informaţii este mai simplă. Dacă extragerea de informaţii este mai laborioasă, atunci este de preferat să se aleagă interogările. Există mai multe categorii de filtre. Probabil, cel mai simplu este filtrul de selecţie, stabilit prin acţionarea instrumentului cu acelaşii nume, din fila Pornire a panglicii Accesss. Dacă un tabel sau o interogare este vizualizată în modul Foaie date, atunci când cursorul este într-o anumită celulă şi se face clic pe butonul Selecţie din grupu Sortare şi selecţie, se selectează din foaia de date toate înregistrările care au în câmpul curent o valoare care faţă de valoarea aleasă poate fi: aceeaşi; diferită; mai mică; mai mare; sau într-un interval. Filtrarea se mai poate efectua cu un clic dreapta aplicat unui câmp, într -un tabel se deschide un meniu care are ca prime opţiuni operaţii de filtrare: Filtrare prin selecţie; Filtrare cu excluderea selecţiei; Filtrare pentru…; Eliminare Filtrare/sortare. Filtrarea prin selecţie e descrisă în alineatul precedent şi redată în figura următoare.
58
Filtrarea cu excluderea selecţiei extrage articolele care nu apar în cazul anterior. La Filtrare pentru se poate da un criteriu de căutare în câmpul curent. Dacă pentru criteriu de selecţie se dă un text simplu, practic funcţionarea este identică cu Filtrarea prin selecţie. Dar acest filtru este mai complex deoarece în caseta pentru se pot introduce expresii, ca de exemplu: Mai mic „<‖ sau mai mare „<‖ decât anumite valori numerice sau literare; caracterul joker „*‖ pentru realizarea de expresii de forma „contine text‖, „începe cu textul‖, „se termină cu textul‖, sau negarea acestor expresii etc. Filtrele se pot aplica prin suprapunere, mereu reducându-se numărul înregistrărilor. Revenirea la situaţia iniţială se face selectând din meniu opţiunea Comutare filtrare. Filtrele mai complexe se pot executa cu opţiunea Filtrare complexă, prin care utilizatorul este ajutat să efectueze filtrări ale datelor cu ajutorul unei grile asemănătoare grilei QBE (Vezi figura):
3.8. Limbaj Structurat de Interogare SQL (Structured Query Language) 3.8.1. Limbajul SQL Probabil că limbajul SQL este astăzi cel mai utilizat dintre limbajele structurate pentru interogarea bazelor de date relaţi onale. SQL a devenit chiar un standard pentru o gamă din ce în ce mai largă de sisteme de gestiune a bazelor de date. Mai mult, toate limbajele de programare care vor să asigure utilizatorilor posibilităţi de comunicare complexă şi rapidă cu bazele de date , au încorporată o 59
formă mai mult sau mai puţin dezvoltată de SQL. Pe lângă manipularea şi regăsirea datelor, se efectuează şi operaţii complexe privind actualizarea şi administrarea bazei de date. SQL eare un set de comenzi în limba engleză, simple şi clare, care poate fi utilizat atât de specialişti cât şi de nespecialişti. Nu este un limbaj procedural, nu are proceduri de control, fiecare SGBD având un motor care are printre func ţiuni sarcina optimizării cererilor. Limbajul SQL foloseşte cuvintele select, insert, delete ca părţi ale setului de comenzi. SQL pune la dispoziţia programatorilor comenzi pentru rezolvarea unor probleme ca: date interogate; inserarea, extragerea şi ştergerea rândurilor intr-un tabel; crearea, modificarea şi ştergerea obiectelor de tip baza de date; controlul accesului la baza de date şi la obiectele de tip baza de date; garantarea consistentei bazei de date. Limbajul SQL a fost dezvoltat într-un prototip de sistem de management a bazelor de date relaţionale, System R, de către compania IBM, la mijlocul anilor 1970. În 1979, Corporaţia Oracle introduce prima implementare a SQL în varianta comercială. Există un anumit grad de standardizare a limbajului SQL, mai multe sisteme de gestiune a bazelor de date recunoscând principalele instrucţiuni ale acestuia (de exemplu: Oracle, Access, Sysbase etc.). Pe plan mondial, standardul în domeniu este considerat ANSI (American National Standards Institute). SQL are în vedere atât aspectele de definire, interogare, manipulare a dat elor, procesare a tranzacţiilor, cât şi caracteristicile complexe privind integritatea informaţiilor, cursoarele derulante sau joncţiunile externe. Mulţi producători de sisteme de gestiune a bazelor de date furnizează propriile extensii ale limbajului SQL, asigurându-şi astfel exclusivitatea. ACCESS utilizează versiuni standardizate ale SQL, prin setarea mediului la SQL ANSI-89 sau SQL ANSI-92. Mediul ACCESS încurajează utilizarea limbajului SQL în scopul optimizării şi eficientizării lucrului cu bazele de date. SGBD-urile manipulează, prin prisma instrucţiunilor SQL, structuri de date, instrucţiunile SQL reprezentând unul dintre modurile de vizualizare al diferitelor colecţii şi structuri de date. Astfel comenzile SQL pot fi plasate în multe locuri din ACCESS, acolo unde se introduce numele unui tabel, al unei interogări sau al unui câmp. 60
În unele cazuri, ACCESS completează automat instrucţiunea SQL. De exemplu, atunci când se utilizează un expert pentru a crea un formular sau raport care preia date din mai multe tabele, ACCESS creează automat o instrucţiune SQL pe care o utilizează ca setare pentru proprietatea Sursă înregistrări a formularului sau raportului. Atunci când trebuie creată o casetă listă sau combo cu un expert, A CCESS creează o instrucţiune SQL şi o utilizează ca setare pentru proprietatea Sursă înregistrări a casetei listă sau a casetei combo. De asemenea, SQL poate fi utilizat în: ► Argumentul Instrucţiune SQL al acţiunii de macrocomandă ExecuţieSQL. Această facilitate este uzitată şi de limbajele clasice de programare pentru acces la bazele de date. ► În cod, ca şir de caractere literale sau ca instrucţiune SQL care conţine variabile şi controale. ► Proprietatea SQL a unui obiect QueryDef pentru a modifica instrucţiunea SQL subordonată unei interogări. În paragrafele anterioare ale acestui capitol au fost prezentate modalită ţi de concepere automată a interogărilor folosind tehnica grafică a grilei QBE, prin care informaţia definită şi apoi modificată pe grila QBE este automat transformată într-o instrucţiune SQL care este posibil de vizualizat sau actualizat în modul Vizualizare SQL. Grila QBE are limite în realizarea de interogări complexe, motiv pentru care însuşirea temeinică a clauzelor comenzilor limbajului SQL este indispensabilă în realizarea unei activităţi de proiectare, administrare şi utilizare a bazelor de date. Implementările în diferite limbaje SQL diferă câte puţin, fiecare prin sintaxă. În ACCESS principalele reguli sunt redate mai jos: ► o comandă se încheie cu ";" ; ► comenzile SQL pot fi pe una sau mai multe linii; ► clauzele sunt uzual plasate pe linii separate; ► comenzile SQL nu sunt 'case sensitive'; ► într-o interogare unde se folosesc câmpuri din mai multe tabele, pentru a separa numele tabelului de numele câmpului, se va utiliza modelul: tabel.câmp; ► dacă se folosesc spaţii sau diacritice în numele câmpurilor sau tabelelor, aceste nume trebuie să fie încadrate între paranteze drepte; ► elementele din listele diferitelor clauze sunt separate prin virgulă; ► valorile de tip şir de caractere sunt încadrate între apostrof sau ghilimele; ► în expresiile din cadrul clauzelor se folosesc operatorii aritmetici, relaţionali şi logici obişnuiţi din informatică;
61
► simbolurile „?‖ şi „*‖ sunt folosite pentru a desemna unul sau mai multe caractere de înlocuire 5; ► valorile de tip dată/timp sunt încadrate cu caracterul „#‖. Pentru explicarea entităţilor şi structurii unui limbaj de programare se utilizează metalimbajul. Când în descrierea unei instrucţiuni anumite părţi constitutive por să lipsească, acestea sunt intercalate între paranteze 6 drepte [ ]. Dacă dintre câteva elemente este necesară optarea penntru unul dintre ele, elementele sunt intercalate între acolade { }, separate de caracterul pipeline |. Dacă un anumit tip de componentă se poate repeta, acest fapt se notează cu trei puncte „…‖.
3.8.2. Comenzi destinate tabelelor Principalele comenzi SQL pentru definirea datelor sunt următoarele: CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX. Pentru crearea unei tabele se utilizează comanda: CREATE TABLE table (field1 type [(size)] [NOT NULL] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]]) . Printre cele mai importante tipuri de date folosite amintim: Character, Memo, Number, Integer, Decimal, Logical, Date, OLE Object etc. Numele tabelei trebuie să fie unic în cadrul bazei de date şi să respecte cerinţele identificatorilor. Aceleaşi cerinţe apar şi pentru numele câmpurilor, în plus există posibilitatea duplicării lor în cadrul bazei de date, dar se păstrează unicitatea în tabelă. Clauza NOT NULL arată că în câmpul respectiv nu se pot stoca valori de tip NULL. Un alt exemplu pentru crearea unei tabele ANG pe care o vom folosi în capitolele următoare, este comanda: CREATE TABLE ANG (NrAng integer NOT NULL, NumeA text(10 ), Fnc text(10), CSf integer, DataAnga DATE, Sal currency, Rate currency, NrDept integer NOT NULL);
5
Caractere jocker
6
A nu se confunda cu utilizarea acestor paranteze în metalimbaj cu utilizarea lor în VBA pentru
delimitarea numelor de identificatori compuse din mai multe cuvinte
62
Pentru modificarea structurii unui tabel se utilizează comanda ALTER TABLE cu următoarea sintaxă (simplificată): ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] | ALTER COLUMN tip de câmp[(dimensiune)] | CONSTRAINT multifieldindex} | DROP {COLUMN field I CONSTRAINT indexname} } Exemplu: Se modifică în tabela ANG pentru ultimul câmp, tipul în Byte: ALTER TABLE ANG ADD ALTER COLUMN NrDept Byte. Comanda DROP TABLE nume_tabelă este folosită pentru a şterge complet o tabelă dintr-o bază de date (structura şi valorile asociate).
3.8.3. Instrucţiunile de selecţie a datelor Pentru selecţia datelor SQL are prevăzută instrucţiunea SELECT. Această instrucţiune conţine mai multe clauze, dintre care doar primele două sunt obligatorii. Pentru definirea interogărilor de selecţie simple se utilizează următoarea sintaxă 7: SELECT [predicat] { * | tabel.* | [tabel.]câmp1 [AS alias1] [, [tabel.]câmp2 [AS alias2] [, ...]]} FROM expresie pentru tabele [IN bază de date externă] [WHERE... ] [GROUP BY... ] [HAVING... ] [ORDER BY... ] [WITH OWNERACCESS OPTION] Predicat permite stabilirea modalităţii de manipulare a înregistrărilor din baza de date asupra căreia se efectuează selecţia şi poate fi: ALL: permite includerea tuturor înregistrărilor ce îndeplinesc condiţiile impuse. Cum frazele SELECT tabelă şi SELECT ALL tabelă au practic acelaşi rezultat, calificativul ALL este destul de rar utilizat; DISTINCT: are ca efect eliminarea înregistrărilor care conţin duplicate în câmpurile selectate, astfel se va afişa doar o apariţie a datei multiple; 7
Conform ajutorului Access
63
DISTINCTROW: are în vedere înregistrările duplicate în ansamblul lor, nu numai pe cele care au câmpuri duplicate; TOP: are ca efect prezentarea primelor articole rezultate, ca procent din total sau în număr absolut. Ceea ce urmează după predicat în prima clauză formează o listă de selec ţie în care sunt cuprinse toate câmpurile care vor apărea în tabelă cu rezultatele interogării. Câmpurile adăugate în rândul Field din grila Query a machetei grafice QBE, care au marcată caseta de validare Show, sunt aceleaşi cu cele menţionate în lista de selecţie. Simbolul „*‖ are rol de caracter jocker. De fapt în această listă pot fi incluse şi expresii care pot sau nu să fie sensibile la articolele din tabelele sursă. De asemenea, în scrierea interogărilor de selecţie simple SQL ACCESS este posibilă şi folosirea funcţiilor totalizatoare. Cele mai importante funcţii din această categorie sunt: COUNT: returnează numărul de înregistrări care respectă condiţii le stabilite prin clauza WHERE; SUM: redă suma tuturor valorilor dintr-un câmp, operează numai cu valori numerice; AVG: calculează valoarea medie a unui câmp numeric; MAX: permite determinarea celei mai mari valori dintr-un câmp; MIN: duce la obţinerea celei mai mici valori a unui câmp rămâne valabilă şi aici restricţia privind clauza WHERE. Aceste funcţii se mai numesc şi funcţii de grupare, iar mulţimea şi valorile rezultatelor obţinute sunt direct condiţionate de clauza GROUP BY. În cadrul listei de selecţie se pot defini şi alias-uri. Acestea reprezintă un pseudonim (nume) asociat unui câmp astfel: câmp AS alias al funcţiei agregat. Clauza FROM specifică numele tabelei sau tabelelor care vor forma suportul interogării. Dacă în lista_selecţie se includ câmpuri din mai multe tabele, în faţa numelui acestora trebuie precizată tabela din care fac parte. Aşa cum arătam la regulile de sintaxă, pentru separarea numelor de tabele se utilizează semnul „,‖ (virgulă). Trebuie să precizăm faptul că în cadrul acestei clauze se pot menţiona pe lângă tabele, ca surse de informaţii pentru interogările SQL, şi interogări care au fost deja create. În continuare redăm câteva exemplificări ale acestor precizări: Pentru a selecta toate coloanele dintr-o tabelă se utilizează caracterul asterix ('*') după cuvântul SELECT. 64
SELECT * FROM ANG; NrAng NumeA Fnc Csf DataAnga Sal Rate NrDept 2000
CHIVU
SOFER
2956 23.02.1999 950
30
2555 CONTRA VANZATOR 2956 19.12.2000 1250 400
30
2049
DOROS
INGINER
2553 31.10.2000 2975
20
2731
MARCU
ANALIST
2049 19.03.1999 3000
20
2956
NEGRU
INGINER
2553 11.01.1999 2850
30
2084
OSAN
ANALIST
2049 19.12.2000 3000
20
2789
POPA
2396
POPAN
SOFER
2553
RUSU
DIRECTOR
VANZATOR 2956 26.03.1999 1250 500
30
2084 13.01.1999 800
20
19.02.1999 5000
10
2024
TULIAN VANZATOR 2956 23.01.1999 1500
30
2987
VUSCA VANZATOR 2956 15.09.1999 1600 300
30
2643
ZIMAN
10
INGINER
2553 14.10.1999 2450
Pentru a lista salariile angajaţilor este necesară comanda: SELECT NUMEA, SAL FROM ANG; NUMEA
SAL
CHIVU
950
CONTRA
1250
DOROS
2975
MARCU
3000
NEGRU
2850
OSAN
3000
POPA
1250
POPAN
800
RUSU
5000
TULIAN
1500
VUSCA
1600
ZIMAN
2450
Uneori dorim să creăm noi coloane prin expresii. Regulile de sintaxă şi de precedenţă sunt cele obişnuite, funcţie de tipurile de date ale operanzilor sau câmpurilor ce concură la realizarea expresiilor. În următorul exemplu se evaluează prima dată înmulţirile (Sal * 12 şi Comp * 12); apoi cele două valori produs sunt adunate. 65
SELECT NumeA, Sal *12 - Rate * 12 FROM ANG; NumeA
Expr1001
CHIVU CONTRA
10200
DOROS MARCU NEGRU OSAN POPA
9000
POPAN RUSU TULIAN VUSCA
15600
ZIMAN
Câmpul se va numi Expr1001. Pentru ca sistemul să nu îi atribuie o denumire implicită trebuie să se specifice un alias asemeni celui din exemplul de mai jos: SELECT NumeA, Sal*12-Rate*12 as VenitAnual FROM ANG; SQL tratează corect valorile nule. Dacă apar în expresii, valorile nule provoacă rezultatul Null, indiferent de tipul celorlaţi operatori implicaţi. Din păcate, acest lucru se poate perpetua în obiectele în care aceste interogări vor fi folosite mai departe. Pentru aceasta există funcţia Nz, care înlocuieşte expresiile Null cu o anumită valoare, de obicei 0 pentru expresii care în mod firesc ar trebui să fie numerice, "" pentru string, etc. Deci, pentru a face ca interogarea să funcţioneze corect ea trebuie transformată sub următoarea formă: SELECT NumeA, Sal*12 -Nz(Rate,0)*12 as VenitAnual FROM
ANG;I
NumeA
VenitAnual
CHIVU
11400
CONTRA
10200
DOROS
35700
MARCU
36000
NEGRU
34200
OSAN
36000
POPA
9000
66
SELECT NumeA, Sal*12 -Nz(Rate,0)*12 as VenitAnual FROM
ANG;I
NumeA
VenitAnual
POPAN
9600
RUSU
60000
TULIAN
18000
VUSCA
15600
ZIMAN
29400
Prin clauza WHERE se pot selecta numai anumite articole, şi anume acele a care îndeplinesc criteriul descris. Parametrul criteriul ui de selecţie este o expresie care are rezultatul obligatoriu logic. În expresia clauzei WHERE obligatoriu trebuie să apară cel puţin un câmp. Clauza WHERE este opţională şi nu operează cu funcţii totalizatoare. În cadrul condiţiei din clauza WHERE pot fi utilizaţi operatorii relaţionali: (>, >=, <, <=, =, !=), logici: (AND, OR, NOT) şi SQL: (IN, BETWEEN, LIKE, IS NULL). Apelând la aceştia, este posibilă construirea unor condiţii mai complexe. Redăm în continuare câteva exemple de utilizare a acestei clauze: Pentru a afişa angajaţii anterior datei 01.01.2000, se dă comanda: SELECT ANG.NumeA, ANG.DataAnga FROM ANG WHERE [DATAANGA]<=#1/1/2000#; NumeA
DataAnga
POPAN
13.01.1999
VUSCA
15.09.1999
POPA
26.03.1999
NEGRU
11.01.1999
ZIMAN
14.10.1999
MARCU
19.03.1999
RUSU
19.02.1999
TULIAN
23.01.1999
CHIVU
23.02.1999
Dacă se doreşte să se extragă angajaţii ai căror salariu este între 1500 şi 3000:
67
SELECT ANG.NumeA, ANG.Fnc FROM ANG WHERE (((ANG.Sal)>1500 And (ANG.Sal)<3000)) ORDER BY ANG.DataAnga; NumeA
Fnc
NEGRU
INGINER
VUSCA
VANZATOR
ZIMAN
INGINER
DOROS
INGINER
Presupunem că dorim să găsim angajaţii care sunt şefi, au unul din numerele de marcă aparţinând (CSf): SELECT
NrAng, NumeA, Sal, CSf FROM
WHERE
ANG
NrAng IN (2553, 2049, 2956, 2084);
NrAng
NumeA
Sal
CSf
2049
DOROS
2975
2553
2956
NEGRU
2850
2553
2553
RUSU
5000
2084
OSAN
3000
2049
Pentru a selecta toţi INGINERii cu salarii peste 1500 şi toţi vânzătorii se foloseşte: SELECT NrAng,NumeA,Fnc,Sal,NrDept FROM
ANG
WHERE Sal > 1500 AND OR NrAng 2987 2789 2049 2555 2956 2643 2024
NumeA VUSCA POPA DOROS CONTRA NEGRU ZIMAN TULIAN
Fnc = 'INGINER' Fnc = 'VANZATOR'; Fnc VANZATOR VANZATOR INGINER VANZATOR INGINER INGINER VANZATOR
Sal
NrDept 1600 1250 2975 1250 2850 2450 1500
30 30 20 30 30 10 30
Dacă se doreşte selectarea tuturor INGINERilor şi a vânzătorilor cu salarii peste 1500 se introduce:
68
SELECT FROM WHERE AND OR NrAng 2987 2049 2956 2643
NumeA VUSCA DOROS NEGRU ZIMAN
NrAng,NumeA,Fnc,Sal,NrDept ANG Sal 1500 (Fnc = 'INGINER'
Fnc = 'VANZATOR'); Fnc VANZATOR INGINER INGINER INGINER
Sal 1600 2975 2850 2450
NrDept 30 20 30 10
Parantezele specifică ordinea în care operatorii vor fi evaluaţi. În al doilea exemplu operatorul OR este evaluat înaintea operatorului AND. În astfel de operaţii complexe trebuie avută mare atenţie la precedenţa operatorilor de toate tipurile, pentru că altfel rezultatul poate fi imprevizibil. Sumar, regulile de precedenţă sunt redate în ceea ce urmează: Toţi operatorii sunt aranjaţi într-o ierarhie, ceea ce le determină precedenţa. Într-o expresie operaţiile sunt executate în ordinea precedenţei lor de la mare la mică. Când operatorii au precedenţa egală atunci ei se evaluează de la stânga l a dreapta. 1. Toţi operatorii de comparaţie şi SQL au precedenţa egală: =,!=,,<=,>=,BETWEEN...AND,IN,LIKE,IS NULL. 2. NOT(pentru a inversa rezultatul unei expresii logice. De ex: WHERE not(sal=2000)) 3. AND 4. OR. De fiecare dată când există dubiu despre care dintre două operaţii vor fi executate mai întâi când o expresie este evaluată, se recomandă utilizarea parantezelor pentru clarificarea semnificaţiei dorite şi obţinerii rezultatului scontat. Clauza ORDER BY se utilizează atunci când se doreşte ca rezultatele interogării să fie ordonate după unul sau mai multe câmpuri_criteriu (definite drept chei de sortare). Sortarea este opţională şi se poate realiza în mod crescător (ASC) sau descrescător (DESC), separat pentru fiecare câmp din listă. Exemplificare a folosirii acestei clauze este comanda prin care se extrag în ordinea crescătoare a vechimii (inversă a valorilor coloanei DataAnga) date din tabelul ANG:
69
SELECT FROM
NumeA,Fnc,DataAnga ANG
ORDER BY DataAnga DESC; NumeA OSAN CONTRA DOROS ZIMAN VUSCA POPA MARCU CHIVU RUSU TULIAN POPAN NEGRU
Fnc
DataAnga
ANALIST VANZATOR INGINER INGINER VANZATOR VANZATOR ANALIST SOFER DIRECTOR VANZATOR SOFER INGINER
19.12.2000 19.12.2000 31.10.2000 14.10.1999 15.09.1999 26.03.1999 19.03.1999 23.02.1999 19.02.1999 23.01.1999 13.01.1999 11.01.1999
Clauza GROUP BY precizează câmpul sau câmpurile pe baza cărora se va efectua gruparea înregistrărilor. În acelaşi timp, prin intermediul acestei clauze, se pot executa funcţiile agregate descrise în lista de selecţie pentru fiecare dintre grupări (constituite pe baza câmpurilor de grupare). Echivalentul acestei clauze în macheta grafică QBE de construcţie a interogării îl reprezintă rândul Total. Pentru a calcula totalul salariilor pe departamente, se utilizează comanda: SELECT SUM(SAL) AS [TOTAL DEPARTAMENT], NRDEPT FROM ANG GROUP BY NRDEPT; TOTAL DEPARTAMENT NRDEPT 7450 9775 9400
10 20 30
Pentru a calcula suma salariulu și salariul mediu distribuite de către persoanele cu funcții de conducere, se utilizează comanda: SELECT Sum(ANG.SAL) AS Sumă, Avg(ANG.SAL) AS Medie, ANG.CSF AS Responsabil FROM ANG GROUP BY ANG.CSF; Sumă Medie Responsabil 5000 6000 800 8275 6550
5000,00 3000,00 800,00 2758,33 1310,00
70
2049 2084 2553 2956
La prima vedere s-ar părea că clauza HAVING ar fi redundantă. Din contră, ea este foarte necesară. Spre deosebire de WHERE, care acţionează înainte de a se efectua gruparea înregistrărilor, HAVING va opera după definirea acesteia. Pentru HAVING criteriul va fi aplicat câmpului definit ca argument al funcţiei agregat. Altfel spus, când se foloseşte clauza GROUP BY şi este necesară şi o condiţie, se va utiliza clauza HAVING. Un exemplu edificator ar fi extragerea funcţiilor pentru care toţi angajaţii au salariu mai mare decât 1500: SELECT Fnc, MIN(Sal) as [salariu minim] FROM ANG GROUP BY Fnc HAVING MIN(Sal) >= 1500 ORDER BY MIN(Sal); Fnc salariu minim INGINER ANALIST DIRECTOR
2450 3000 5000
Rezultatul este diferit de cel care de obține prin mutarea condiției la la clauza WHERE. Interpretarea unei astfel de înregistrări ar fi: extragerea pe funcții a salariului minim, pentru toți angajații cu salarii mai mari sau egale decât 1500: SELECT Fnc, MIN(Sal) as [salariu minim] FROM ANG where Sal >= 1500 GROUP BY Fnc ORDER BY MIN(Sal); Fnc salariu minim VANZATOR INGINER ANALIST DIRECTOR
1500 2450 3000 5000
De remarcat faptul că se admite utilizarea unei funcţii agregat care nu apare în lista de selecţie, precum şi apelarea la mai multe criterii de grupare. Puterea limbajului SQL este pusă în evidenţă atunci când se lucrează cu mai multe tabele, când se exploatează relaţiile sau chiar asocierea dintre ele. Operaţiile de asociere induse de clauza JOIN au ca rezultat producerea tuturor combinaţiilor posibile, pentru conţinutul informaţional al fiecărei tabele. Noile înregistrări care rezultă în urma joncţiunii vor deveni disponibile pentru selecţiile ulterioare. La o asociere pot participa mai mult de două tabele. Principala modalitate de realizare a joncţiunii este sintetizată de următoarea sintaxă, a celei de-a doua clauze a comenzii SELECT: FROM nume_tabela1 {INNER|LEFT OUTER|RIGHT OUTER} JOIN nume_tabela2 ON criteriul_de_asociere 71
[{INNER|LEFT OUTER|RIGHT OUTER} JOIN nume_tabela3 ON criteriul_de_asociere]... Semnificaţia elementelor de sintaxă descrise mai sus este următoarea: INNER, LEFT OUTER, RIGHT OUTER se referă la tipurile de joncţiuni (INNER JOIN, internă de tip echivalent, LEFT OUTER JOIN, externă de stânga, RIGHT OUTER JOIN, externă de dreapta). De remarcat faptul că SQL ACCESS acceptă scrierea interogărilor externe fără specificarea explicită a lui OUTER. JOIN specifică tabela care va fi asociată (nume_tabela2, nume_tabela3...) tabelei precizată în clauza FROM,. ON este criteriul de asociere şi arată relaţia dintre câmpurile pe care se bazează joncţiunea. Unul se află în tabela asociată, iar cel ălalt există într-o altă tabelă din lista cu numele tabelelor. Expresia criteriul_de_asociere conţine un operator de comparaţie (=,<,>,<>,<=,>=) şi va returna valorile logice TRUE sau FALSE. Joncţiunile tip INNER JOIN determină o asociere a înregistrărilor din tabele, astfel încât să rezulte doar informaţii din înregistrările din fiecare tabelă care respectă criteriul de asociere. Joncţiunile externe (OUTER) sunt de două tipuri: de stânga (LEFT OUTER JOIN) şi de dreapta (RIGHT OUTER JOIN), fiind destul de puţin utilizate. Echivalentul QBE al acestor categorii de joncţiuni este alegerea opţiunilor 1, 2 sau 3, din caseta Join Properties, care au fost explicate într-un capitol precedent (3.4). Ca exemplu, fie următoarea interogare totul si toate: SELECT CLIENTI.Codcli, CLIENTI.Dencli, CLIENTI.Adresa, CLIENTI.Telcli, FactGen.Nrfact, FactGen.Datafact, FactGen.Delegat, FactProd.Codprod, Prod.DenProd, Prod.UM, FactProd.Cant, Prod.Pret FROM Prod INNER JOIN ((CLIENTI INNER JOIN FactGen ON CLIENTI.Codcli=FactGen.Codcli) INNER JOIN FactProd ON (FactGen.Nrfact=FactProd.Nrfact) AND (FactGen.Datafact=FactProd.Datafact)) ON Prod.Codpro=FactProd.Codprod; De observat că este este recomandabilă scrierea completă din punct de vedere sintactic a numelui câmpurilor. Precedarea sintactică a numelui câmpurilor de numele tabelului și semnul ‖.‖ e obligatorie, în multimea câmpurilor tabelelor la care se face referire în clauza FROM, dacă o denumire de câmpuri apare de cel puțin două ori. Același rezultat poate fi obținut fără a se recurgere la opțiunile JOIN în clauza FROM, ci specificare condițiilor de legătură în clauza WHERE, precum în comanda de mai jos: 72
SELECT CLIENTI.Codcli, CLIENTI.Dencli, CLIENTI.Adresa, CLIENTI.Telcli, FactGen.Nrfact, FactGen.Datafact, FactGen.Delegat, FactProd.Codprod, Prod.DenProd, Prod.UM, FactProd.Cant, Prod.Pret FROM Prod, Clienti, FactGen, FactProd WHERE (CLIENTI.Codcli=FactGen.Codcli) AND (FactGen.Nrfact=FactProd.Nrfact) AND (FactGen.Datafact=FactProd.Datafact)AND (Prod.Codpro=FactProd.Codprod) ; Opțiunea JOIN a fost introdusă în standardul SQL din 1992, până atunci, specificarea relațiilor s-a făcut prin clauza WHERE. Prin parametrii RIGHT sau LEFT opțiunea JOIN a adus și o simplificare a unor comenzi. Fie, de exemplu, interogarea de extragere a totalului valorii și cantității pe produse: SELECT Prod.DenProd, Prod.UM, Sum(FactProd.Cant) AS SumăalCant, Prod.Pret, Sum([Cant]*[PRET]) AS SumăValoare FROM Prod LEFT JOIN FactProd ON Prod.Codpro = FactProd.Codprod GROUP BY Prod.DenProd, Prod.UM, Prod.Pret; TOTAL VALOARE SI CANT DenProd UM SumăalCant Pret SumăValoare Lapte Malai Ulei Zahar
l kg Kg kg
98,00 43,00 40,00
2,9 2,25 3,27 3,1
284,20 140,61 124,00
Fără opțiunea JOIN, trebuie să se recurgă pentru relații la clauza WHERE și, pentru adăgarea produselor pentru care nu s-a facturat nimic, la opțiunea UNION: SELECT Prod.DenProd, Prod.UM, Sum(FactProd.Cant) AS SumăalCant, Prod.Pret, Sum([Cant]*[PRET]) AS SumăValoare FROM Prod, FactProd WHERE Prod.Codpro=FactProd.Codprod GROUP BY Prod.DenProd, Prod.UM, Prod.Pret; UNION SELECT Prod.DenProd, Prod.UM, 0.00 AS SumăalCant, Prod.Pret, 0.00 AS SumăValoare FROM Prod, FactProd WHERE Prod.Codpro NOT IN (SELECT DISTINCT CodProd FROM FactProd) GROUP BY Prod.DenProd, Prod.UM, Prod.Pret;
73
Scrierea unei interogări în cadrul alteia duce la apariţia unei subinterogări; setul de rezultate obţinut de la o interogare va constitui argument pentru o alta. Utilizatorul poate astfel să creeze legături între mai multe interogări SQL ACCESS, pe baza unor câmpuri unice, cu rol de căutare în structura tabelelor. Subinterog ările înlocuiesc interogările imbricate din versiunile precedente, cu performan ţe mult îmbunătăţite. Pot fi construite şi prin varianta de lucru a machetei grafice QBE ACCESS. Cea mai simplă subinterogare are sintaxa următoare: SELECT * FROM Tabela1 WHERE Tabela1.nume_ câmp operator (SELECT nume_câmp FROM Tabela2 WHERE criteriul_de_selecţie); Tabela1 şi Tabela2 vor avea un câmp comun (nume_câmp) care va reprezenta de fapt câmpul de legătură ce stă la baza construirii subinterogării. Clauza SELECT din subinterogare va avea acelaşi număr de câmpuri şi de natură similară cu cele din clauza WHERE a interogării externe. Operatorul poate fi unul relaţional sau unul dintre BETWEEN...AND, IN, LIKE, IS NULL. Ca exemplu redăm următoarea comandă: SELECT DenProd FROM Prod WHERE Pret= (SELECT MAX(Pret) from Prod).
3.8.4. Instrucţiunile pentru manipularea datelor Cele mai importante instrucţiuni sunt: INSER; UPDATE; DELETE. Comanda INSERT se foloseşte pentru adăugarea de înregistrări la o tabelă. Ca sursă a datelor pot fi o alte tabele, date fixe sau parametri. Sintaxa comenzii este următoarea: pentru un articol: INSERT INTO nume_tabelă [(câmp1[, câmp2[, ...]])] VALUES (valoare1,valoare2...) pentru mai multe articole: 74
INSERT INTO nume_tabelă [IN baza_de_date_externă] [(câmp1[, câmp2[, ...]])] SELECT [sursă.]câmp1[, câmp2[, ...] FROM listătabele În acest caz se adaugă înregistrări într-o tabelă, menţionându-se câmpurile şi valorile asociate acestora. După lansarea în execuţie a interogării apare un mesaj de avertizare privind adăugarea de noi înregistrări în baza de date şi caracterul ireversibil al acestei operaţii. În cadrul acestui tip de inserare a datelor trebuie să se respecte următoarele reguli: ► valorile menţionate în clauza VALUES vor avea aceeaşi natură cu câmpurile specificate în clauza INTO; ► mărimea valorii corespunzătoare fiecărui câmp va fi mai mică decât dimensiunea câmpului; ► nu va fi obligatorie specificarea denumirii câmpurilor, deoarece SQL ACCESS va asocia listei de valori câmpurile în ordinea din structura înregistrării (prima valoare se va introduce în primul câmp, a doua valoa re, în al doilea câmp s.a.m.d.); ► dacă un câmp are definiţia NOT NULL, va fi obligatorie introducerea unei valori pentru acesta. Exemplu: Să se adauge un articol pentru un nou angajat, Paznic: INSERT INTO Ang values (8888, "VALCU" , "PAZNIC", 2553, #3/3/2009#,2000,null ,10) Comanda DELETE are următoarea sintaxă: DELETE FROM nume_tabelă [WHERE criteriul_de_ştergere] se materializează în interogarea acţiune de ştergere parţială sau totală a înregistrărilor din tabele. În acelaşi timp se va şterge doar conţinutul tabelei nu şi aceas ta (pentru eliminarea tabelei se va apela la instrucţiunea DROP TABLE). Exemplu: DELETE FROM ANG WHERE NrAng = 8888 Comanda UPDATE are următoarea sintaxă: UPDATE nume_tabelă SET nume_câmp1 = valoare1 [,nume_câmp2 = valoare2]... [WHERE criteriul_de_actualizare] 75
Această comandă are rolul şi de a modifica valorile câmpurilor din înregistrările existente. Ca şi în cazul instrucţiunii INSERT, se va urmări dacă în câmpul cu valori de actualizat sunt permise numai valori unice. Atunci când se doreşte actualizarea datelor din mai multe câmpuri se foloseşte virgula ca separator între câmpuri şi valorile acestora. Se pot utiliza mai multe condiţii, WHERE apelând la operatorul logic AND pentru a limita actualizarea la înregistrări mai bine specificate. Exemplu: Să se mărească salariile VANZATORilor cu 15%: UPDATE Ang SET Sal= Sal * 1,15 where Fnc=”VANZATOR”;
76
CAPITOLUL IV. FORMULARELOR
CREAREA
ŞI
UTILIZAREA
4.1. Noţiuni generale Pentru introducerea în siguranţă a informaţiilor în tabelele bazelor de da te se recurge la utilizarea formularelor (în limba engleză: forms). Deoarece bazele de date trebuie să poată gestiona un număr de articole de ordinul miilor sau chiar milioanelor, la proiectarea formularelor trebuie să se găsească soluţii pentru întreţinerea datelor în siguranţă, introducerea eficientă de date, posibilitatea de regăsire a articolelor, în scopul modificării, asigurarea concordanţei datelor introduse cu datele existente, verificarea şi partajarea accesului utilizatorilor la bazele de date, vizualizarea rapidă a valorilor introduse şi apoi tipărirea lor. Crearea formularelor necesită activităţi complexe de analiză, proiectare şi programare a sistemelor, care consumă cea mai mare parte din bugetul de timp alocat creării şi implementării aplicaţiei de gestionare a bazei de date. Majoritatea formularelor sunt legate de unul sau mai multe tabele şi interogări dintr-o bază de date. Datele cu care lucrează formularul sau raportul sunt constituite în sursa de înregistrări a formularului în câmpurile din tabelele şi interogările pe care acesta se bazează. Un formular nu e obligatoriu să conţină toate câmpurile din fiecare tabel sau interogare pe care se bazează. Un formular legat de date memorează sau regăseşte informaţii din sursa de înregistrări de bază. Pe lângă acestea formularele mai pot conţine şi alte informaţii, cum ar fi: titlu, data şi numărul paginii (vezi figura).
Elementele grafice, cum sunt liniile şi dreptunghiurile, sunt memorate în forma formularului. 77
Datele provin din câmpurile provenite din sursa de înregistrări de bază. Un calcul provine dintr-o expresie, care este memorată în forma formularului. Textul descriptiv este şi el memorat în forma formularului. Aceste elemente se numesc controale. Un control este un o biect grafic care poate fi poziţionat pe un formular sau raport, putând afişa date, putând fi legat de efectuarea unor acţiuni declanşate de evenimente intervenite în rularea aplicaţiei sau pur şi simplu sunt create pentru mai buna lizibilitate sau pentru scop estetic. Asupra controalelor acţionează utilizatorii cu mouse-ul sau tastatura. Acţiunea se poate face la un moment dat doar asupra unui singur obiect, aspect numit focalizare. Obiectul care a primit focalizarea este, în mod normal, indicat prin evidenţiere grafică. Focalizarea poate fi setată de utilizator sau de aplicaţie cu ajutorul metodei SetFocus. Mediile de proiectare a bazelor de date pun la dispoziţia utilizatorilor diverse instrumente pentru crearea de formulare. În versiunea 2007 a Microsoft ACCESS au fost introduse instrumentele de creare formulare, grupate într-o zonă cu acelaşi nume, în fila Creare a panglicii Access, prezentate în figura alăturată. Crearea formularelor se poate face: prin salvarea unui tabel sau interogări ca formular, creindu-se un formular care afişează toate câmpurile şi înregistrările din sursa de date. În ACCESS 2007 a fost introdus un instrument nou în fila creare în acest scop; utilizând expertul, caz în care utilizatorii sunt asistaţi de un expert care pune întrebări şi creează un formular bazat pe răspunsurile primite; prin apelarea la unul dintre noile instrumente introduse în mediul de proiectare şi întreţinere: Scindare formulare, Elemente multiple sau Dialog Modal. Formularele astfel create pot fi particularizate în modul dorit, în modul Vizualizare proiect. În Formularul scindat sunt generate automat, în partea de sus, toate câmpurile unui tabel, iar în partea de jos, înregistrările în modul de vizualizare Foaie de date. Un Formular cu elemente multiple, denumit şi formular continuu, permite afişarea simultană a informaţiilor mai multor înregistrări. Un formular cu elemente multiple într-o versiune iniţială poate fi confundat cu un tabel în modul Foaie de date, dar fiind formular, poate fi particularizat. Un formular este de tip modal dacă are setată proprietatea modal. Această proprietate face ca atunci când e terminată o sesiune de editare într-un astfel de formular, fereastra să trebuiască închisă neapărat înainte de a fi posibilă mutarea focalizării la alt obiect. Aici programatorii 78
beneficiază de tehnologii bazate pe obiecte şi - pentru sarcinile mai dificile - de ajutorul limbajului Visual Basic. Ca şi în cazul tabelelor, odată cu creşterea experienţei proiectanţilor, munca în modul Vizualizare proiect va fi preponderentă în crearea sau particularizarea formularelor. Spre exemplu formularul Produse este prezentat mai jos, în modul Proiectare (în limba engleză: Design View).
Fereastra pentru proiectarea formularelor
Panglica Access
Etichetă
Instrumente pentru crearea controalelor formularelor Casetă combinată
Casetă text Panou de navigare
Subformular Foaia de proprietăți
Dreptunghi Buton
Linia de stare
În modul Vizualizare proiect, într-un formular, se pot particulariza următoarele elemente: Pentru controlul şi asistarea utilizatorului se pot seta proprietăţile formularului pentru a permite sau preveni utilizatorii de la adăugarea, ştergerea sau editarea înregistrărilor afişate într-un formular. De asemenea se poate adăuga Ajutor particularizat unui formular pentru a asista utilizatorii la folosirea formularului. În fereastra Formular se pot adăuga sau elimina butoanele Maximizare şi Minimizare, butoanele de navigare, meniuri rapide şi alte elemente ale ferestrei Formular. În secţiuni se poate adăuga, elimina, ascunde sau redimensiona antetul, subsolul şi secţiunea detalii ale unui formular. De asemenea, se pot seta proprietăţile secţiunii pentru a controla aspectul şi imprimarea unui raport. Controale. Se pot muta, redimensiona sau seta proprietăţile fonturilor unui control. De asemenea, se pot adăuga controale pentru a afişa valori calculate, 79
totaluri, data şi ora curentă şi alte informaţii folositoare într -un formular. Se pot crea proceduri particularizate de tratare a evenimentelor ocazionate de lucrul cu controalele.
4.2. Controale
Tip linie Efect special
Culoare linie Selectare totală Stabilire proprietăţi implicite control
corespunzătoare în rubricile din Fila proprietăţilor 80
Instrument selecţie
Grosime linie
Inserare sfârşit pagină
Ataşament
Inserare hyperlink
Cadru independent
Inserare diagramă Inserare pagină
Imagine
Buton de comutare
Grup de opţiuni
Control filă
Buton opţiune
Caseta de selectare
Casetă listă
Cadru obiect legat
Subformular/ subraport
Casetă combo Dreptunghi Linie
Sistemul de gestiune a bazelor de date ACCESS oferă utilizatorilor un mediu de proiectare orientat pe obiecte, dezvoltat în special pentru realizarea elementelor de interfaţă. Obiectele au diverse proprietăţi prin care se formatează caracteristicile de stare, legătura cu datele sau modul de reacţie la evenimente. Pentru a putea fi incluse în formulare, există instrumente de creare a acestora, grupate într-o zonă a Controalelor, în fila Instrumente proiectare formular, din panglica Access. Procedura de amplasare a acestora în formulare este următoarea: se face clic pe tipul de control dorit; se selectează o zonă pe formular unde acest control va fi amplasat; când mouse-ul este lăsat, controlul este ataşat; configurarea parametrilor controlelor prin atribuirea de valori
Toate aceste operaţii se fac în modul vizualizare proiectare (DesignView), şi constituie activitatea preponderentă a proiectanţilor de aplicaţii de baze de date, munca programatorilor de interfeţe de lucru cu informaţiile din baza de date. Măiestria utilizării controalelor se deprinde odată cu experienţa, cu asimilarea accepţiunii proprietăţilor şi reacţiei controalelor la evenimente. Instrumentele din fila Instrumente proiectare formular, din panglica Access destinate proiectării controalelor pentru formulare, care nu sunt prezentate împreună cu numele lor, le am prezentat în figura anterioară: Proprietăţile unui obiect sunt disponibile pentru modificare dacă numele acelui obiect se găseşte în titlul ferestrei de proprietăţi. Fereastra de proprietăţi este disponibilă prin aplicarea unui clic pe instrumentul Foaie de Proprietăţi din panglica Access sau făcând clic dreapta pe un obiect şi alegând ultima opţiune (Proprietăţi) din meniul contextual. Poate pentru începători găsirea proprietăţilor formularului este mai dificilă, dar clic-ul dreapta trebuie făcut în afara zonei unde se aşează obiectele. Există controale simple, nelegate de date, care nu au proprietatea Control source. Dintre acestea amintim: etichetele, liniile, dreptunghiurile sau unele imagini. Dar există controale cu mult mai complexe decât cele de mai sus, chiar mai complexe decât casetele de text. Cele mai multe dintre ele, cânt sunt create în modul design, apelează automat la un expert (wizard). Aşa sunt, de exemplu controalele tip: Casetă combinată, Buton de comandă, Casetă cu listă sau Control de pagini. Un regim oarecum diferit de cel al controalelor îl au subformularele. Un subformular este un formular inclus într-un alt formular, pentru a permite afişarea datelor din mai multe tabele sau cereri de interogare, aflate în general în relaţii de tip unu la unu sau unu la mai mulţi. Astfel, în formularul principal vor fi afişate datele din partea unu a relaţiei, iar în subformular cele din partea mai mulţi. În mod implicit, legătura dintre un formular şi un subformular reflectă legătura dintre tabelele pe care se bazează. Prin urmare, la un moment dat în formular va fi afişată o înregistrare aflată de partea unu a relaţiei iar în subformular înregistrările corespondente din tabela aflată de partea mai mulţi a acesteia. Într-un formular care conţine un subformular se pot specifica criterii de filtrare numai asupra câmpurilor din formularul principal. Ca exemplu putem prezenta o relaţie 1 – n, dintre un catalog de materiale şi documente de mişcare a acestor materiale astfel:
81
Un subformular poate fi înglobat într-un formular în două moduri: foaie de date (în limba engleză: Datasheet view) sau formular (în limba engleză: Form view). Atunci când se include un subformular într-un formular principal trebuie realizate următoarele operaţii: se creează formularul principal; se creează subformularul; se adaugă obiectul subformular din ToolBox într-o manieră similară cu adăugarea oricărui control. Dacă tabelele pe baza cărora sunt create formularele sunt într-o anumită relaţie, ACCESS-ul propune stabilirea acestei relaţii şi între cele două formulare. Odată înglobat un subformular într-un formular, se poate verifica rezultatul operaţiunii în modul Vizualizare machetă. Eventualele retuşuri se pot face în modul Proiectare.
4.3. Proiectarea unui formular legat de o sursă de înregistrări 4.3.1. Proprietăţile esenţiale Un formular este o fereastră de tip Windows. Acesta are obiecte întâlnite în ferestrele aplicaţiilor Windows. Pentru introducerea datelor de la tastatură se folosesc de obicei casete de editare text. În aceste casete se folosesc foarte multe dintre regulile de scriere de text din editoarele Microsoft referitoare la deplasare, selectare, corectare, căutare, etc. Deoarece, cea mai importantă menire a formularelor este de introducere de date într-un formular, cele mai importante proprietăţi ale obiectelor sunt cele legate de date. Pentru formular această proprietate este Sursa înregistrări (în limba engleză: Record Source) şi reprezintă tabelul sau interogarea de unde formularul îşi extrage datele. 82
Pentru casete de text, ca de altfel pentru majoritatea controalelor, legătura cu datele este realizată de proprietatea Sursă Control (în limba engleză: Control Source) şi reprezintă de obicei câmpurile tabelului sau interogării de unde sunt luate valorile pentru o înregistrare. Pentru formular "Record Source" se poate stabili proprietatea mai târziu, în modul Design view. Sunt disponibile pentru modificare proprietăţile unui obiect dacă numele acelui obiect se găseşte în titlul ferestrei de proprietăţi. Fie, de exemplu, tabelul CLIENTI, care are următoarea structură:
Cum se construieşte un formular pentru introducerea acestor date? Tabelul trebuie să existe în baza de date, măcar structura lui. Recomandăm utilizarea instrumentului . Se deschide apoi o fereastră goală pentru crearea formularului, şi este util să fie activată Foaie de proprietăţi, prin instrumentul comutator , plasat ultimul în fila Instrumente proiectare formular a panglicii. Penultimul buton din aceeaşi filă este instrumentul comutator care face activă/inactivă Lista de câmpuri a bazei de date curente, pe locul Foii de proprietăţi.
83
4.3.2. Obiectele simple În continuare, trebuie ataşate controalele. Casetele de text pot fi ataşate prin mai multe posibilităţi. O posibilitate este de a trage din fereastra cu lista de câmpuri (pe rând), câmpurile în locul unde se doreşte a fi ataşate. O altă posibilitate este de a selecta din bara de instrumente, butonul ab|, pentru casete text. Cursorul mouse-ului, dacă este plasat peste fereastra de creare a formularului, va avea forma unui semn + şi ab|. Se face clic în aria formularului, în locul unde se doreşte să fie amplasat cursorul şi se trage cursorul mouse-ului peste zona care va fi acoperită de caseta text. Când butonul mouse-ului este eliberat, caseta este amplasată şi este însoţită de o etichetă. În caseta text apare scris Nelegat(Unbound). Se modifică textul etichetelor ataşate (în cazul nostru Text0, Text2, Text4, Text6) cu mesajele logice (în cazul de faţă Codul, Denumirea, Adresa, Telefonul).
Această modificare se poate realiza după două clicuri pe etichetă, atunci când cursorul mouse-ului devine |, sau după selecţia controlului (a etichetei, în cazul de faţă), prin modificarea proprietăţii Legendă a filei Format, din Foaia de proprietăţi. De altfel, prin acest procedeu se recomandă să fie particularizate toate proprietăţile (caracteristicile) obiectelor plasate în formulare (inclusiv a formularului însuşi): 84
culoare, dimensiune, plasament, font (tip, dimensiune, stil, culoare), efecte speciale, legendă, nume etc. Cu ajutorul instrumentelor din fila Aranjare pot fi aranjate obiectele.
4.3.3. Culorile obiectelor Majoritatea obiectelor au prevăzută proprietatea prin care se poate stabili o nuanţă pentru fundal, care este Culoare fundal, sau proprietatea pentru fonturi numită Culoare prim plan sau proprietatea pentru contur numită Culoare chenar. Realizarea unei nuanţe de culoare pentru aceste propeietăţi se poate obţine prin amestecul, în ponderi diferite, a celor trei culori fundamentale: roşu, verde şi albastru. Practic se pot obţine 2563=16777216 nuanţe. Dacă se cunoaşte codul culorii acesta se poate completa. Pentru a facilita intuiţia codului, sistemul de redare a lui poate fi înscris printr -o tripletă de perechi de cifre hexazecimale, precedate de semnul # (diez). Pentru codificarea în sistem intern, se formează o mască în care tripleta RGB se pune invers. Acest cod se obţine prin însumarea codificărilor cantităţilor pentru cele trei culori (valori 0255) corectate prin coeficienţi, în funcţie de locul lor în mască: cantităţii de roşu, fiind în dreapta i se aplică coeficientul 20=1, cantităţii de verde i se aplică coeficientul 28=256 iar cantităţii de albastru i se aplică coeficientul 216=65536. Dacă există dificultăţi în selectarea nuanţei, atunci se apasă pe „…‖, de la sfârşitul liniei proprietăţii culorii, şi se alege o culoare dintr-o paletă. Dacă tot nu se găseşte nuanţa dorită, se apasă pe butonul Definire culori particularizate şi se pot amesteca culori pe sistemul RGB (Red, Green, Blue). În funcţie de amestecul realizat se obţine un cod pentru nuanţă. Pentru culoarea negru, care este amestecul celor 3 culori RGB în cantitatea 0, scris #000000 în sistemul hexazecimal (se obţine valoarea codului 0). Culoarea alb se obţine prin amestecul celor 3 culori RGB în cantitate saturată, 256 -1, scris #FFFFFF în sistemul hexazecimal (se obţine valoarea codului 2563-1=16777216-1). Pentru roşu se scrie #FF0000 în sistemul hexazecimal (codul este 255). Galbenul este amestec saturat de roşu şi verde, scris #FFFF00 în sistemul hexazecimal (se obţine valoarea codului 256*256-1=65536-1). Verde se scrie #00FF00 în sistemul hexazecimal (codul este este obţinut înmulţind 255*256 sau scoţând roşul din galben: galben –rosu =65535-255=65280). Pentru a se obţine albastru se scrie #00FF00 în sistemul hexazecimal (codul este este obţinut înmulţind 255*65536, sau se scoate roşu şi verde din alb: 16711680=16777215-65280-255).
85
4.3.4. Aranjarea obiectelor Aspectul formularului depinde în mare măsură de aranjarea obiectelor în formulare, de poziţia pe care o au unele faţă de altele, de proporţiile pe care le au, de ordinea de parcurgere a lor (momentul când primesc focusul). Atunci când se doreşte ca unui grup de obiecte să i se aplice în mod unitar anumite proprietăţi, trebuie să se selecteze obiectele apăsând Shift + clic pe fiecare dintre ele sau trecând cursorul mouse-ului peste ele şi apoi se aplică modificarea modului de aranjare a obiectelor în formular. Operaţiile care pot fi efectuate sunt legate de instrumetele grupate în fila Instrumente proi ectare formular/Aranjare din panglică:
Unul sau mai multe obiecte selectate se pot muta cu ajutorul tastelor cu săgeţi sau prin tragere cu mouse-ul atunci când cursorul este sub forma unei cruci de săgeţi. În mod similar, unul sau mai multe obiecte selectate se pot redimensiona cu ajutorul tastelor cu săgeţi apăsate concomitent cu tasta Shift sau prin tragere cu mouse-ul de unul dintre cele opt puncte negre din colţuri sau mijloace de laturi, atunci când cursorul este sub forma unei săgeţi cu vârfuri l a ambele capete. Atunci când sunt suprapuse obiecte, se poate uzita de opţiunile: Aducere în prim plan sau Trimitere în ultimul plan pentru a stratifica corect obiectele. În fine, ordinea de parcurgere a controalelor se poate stabili cu opţiunea Ordine de tabulare unde, în fereastra Ordine de tabulare se poate modifica, prin glisare cu mouse-ul, ordinea de parcurgere, existentă la un moment dat. A mai rămas de legat formularul şi controalele de date. După ce este selectat formularul (clic în zona din exteriorul grilei din fereastra formularului) se stabileşte valoare Clienti prin modificarea proprietăţii Sursă înregistrări (sau se alege din lista ascunsă) a filei Date din Foaia de proprietăţi. Apoi, pe rând, pentru fiecare casetă de text în parte, după ce se selectează, se alege câmpul corespunzător din lista ascunsă a proprietăţii Sursă control a filei Date din Foaia de proprietăţi. După efectuarea acestor operaţii, formularul este funcţional, precum este redat în modul 86
Vizualizare formular, în figura alăturată:
4.3.5. Alte facilităţi Într-un formular se pot plasa câmpuri calculate pe baza unor expresii. Plasarea expresiilor în controalele aflate pe un formular sau subformular se realizează prin scrierea acestora în proprietatea Control Source a unui control. Scrierea unei expresii se poate face cu asistenţa generatorului de expresii (Expression Builder) sau prin scriere directă. Dacă se utilizează scrierea unei expresii în modul neasistat, aceasta trebuie precedată de semnul egal (=expresie). Expresiile pot conţine orice funcţie Access sau funcţii definite de utilizator, referiri către controale aflate pe alte formulare (deschise), câmpuri din tabela sau interogarea stabilită, a proprietăţii Record Source a formularului, etc. Utilizarea în expresii a controalelor din acelaşi formular se realizează prin referirea numelui controalelor implicate în expresie. Astfel, se consideră implicit referirea proprietăţii Value a controlului respectiv.
87
În exemplul de mai jos caseta Text25, care va afişa valoarea, va avea la proprietatea Control Source formula =[prettt]*[1.19] unde 'prettt' este numele unui control. În loc de 'prettt' se putea scrie 'prettt.value' ca referire completă a proprietăţii sau 'pret' ca referire a câmpului 'pret' dintr-o tabelă 'produs' care este stabilită la proprietatea 'Record Source' a formularului. Există controale mai complexe pentru plasarea cărora în formulare mediul Access are prevăzute instrumente expert şi indică posibilităţile lor cele mai frecvente de utilizare. Butoanele de comandă , sunt frecvent utilizate în formulare, instrumentele mediului Access propunând utilizarea acestora pentru acţiuni pe categorii, redate în figură: De exemplu, pentru a se crea un buton care face salt la înregistrarea anterioară celei curente a formularului, se stabileşte acţiunea Salt la înregistrarea anterioară din categoria Navigare înregistrări. Controalele de tip Casetă combo sau Casetă listă oferă utilizatorilor posibilitatea de a selecta dintr-o listă valori care pot fi introduse într-o bază de date. Diferenţa dintre cele două tipuri de controale este dată de faptul că, prin
casetele combo valorile introduse pot fi preluate fie din lista controlului, fie prin tastarea lor într-o casetă text, componentă a controlului. Sintagma Casetă combo defineşte combinarea a două tipuri de controale: Caseta text şi Caseta listă.
88
Valorile din liste pot fi preluate din câmpuri de tabele s au interogări şi pot fi utilizate mai departe pentru a fi stocate sau pentru a căuta valori în tabele, precum se vede în figura de mai jos.
De exemplu, într-un formular al produselor, pentru unitatea de măsură se pot introduce valori care pot fi, la rândul lor, preluate dintr-o listă. Valorire acestei liste pot fi preluate din interogarea pe care o putem salva cu denumirea UM, bazată pe comanda SQL: Select distict UM from Produs order by UM
. Alegerea interogării sau a tabelului care furnizează informaţiile, alegerea câmpurilor, a modului de sortare, a lăţimii de afişare, a modului de stocare şi utilizare a valorii selectate şi Proprietatea Legendă a etichetei cuplate de listă, pot fi precizate în ferestre ale expertului Access, prin care utilizatorul e ghidat pentru a personaliza controalele de listă. Opţiunile alese de utilizator sunt înregistrate ca parametrii pentru proprietăţile casetei listă, precum se vede în figura de mai jos:
89
Aceşti parametrii pot fi modificaţi de utilizatori. De exemplu, dacă se consideră că ar fi mai portrivită pentru acest control o listă de valori predefinită, nu o interogare, se poate modifica proprietatea Tip sursă rânduri la valoarea Listă valori şi se poate completa proprietatea Sursă rânduri cu expresia ‖Fl‖; ‖Kg‖; ‖L‖ . Într-un formular pot fi folosite date şi din diverse tabele, sau interog ări, altele decât cele precizate la proprietatea Sursă înregistrări a formularului, prin folosirea unor proceduri de tratare a evenimentelor. Utilizarea în controale a expresiilor din alte formulare se face prin sintaxa: Forms![Nume formular]![Nume control]. Pentru a putea fi evaluate astfel de expresii, trebuie ca acele formulare de pe care sunt referite controale să fie deschise în acel moment. Utilizarea într-o expresie, de pe un formular, a unor controale din subformular, se realizează prin: [Nume subformular].Form![Nume control].
90
CAPITOLUL V. CREAREA ŞI UTILIZAREA RAPOARTELOR 5.1. Crearea unui raport Atunci când se proiectează o aplicaţie de baze de date, toţi parametrii acesteia, toate tabele şi obiectele componente, sunt funcţie de listele finale necesare, care constituie finalitatea aplicaţiei. Rapoartele sau situaţiile finale reprezintă forme mai accesibile de prezentare a informaţiilor din bazele de date, conţinutul lor putând fi vizualizat pe ecran, listat la imprimantă sau creat un fişier raport, de exemplu .RTF, ce poate fi prelucrat în continuare în Word. Mediile de proiectare a bazelor de date pun la dispoziţia utilizatorilor diverse instrumente pentru crearea rapoartelor. În versiunea 2007 a Microsoft ACCESS, au fost introduse instrumentele de creare rapoarte, grupate într-o zonă cu acelaşi nume, în fila Creare a panglicii Access, prezentate în figura alăturată. Şi în cazul rapoartelor, ca şi pentru celelalte tipuri de obiecte Access, dintre posibilitatea de a le crea cu ajutorul expertului sau cea în modul proiectare , e recomandabilă a doua. Crearea rapoartelor este foarte asemănătoare cu crearea formularelor, fiind chiar puţin mai simplă deoarece nu se folosesc controale prin care se introduc date. Utilizând instrumentul , se deschide o fereastră goală pentru crearea raportului, şi este util să fie activată Foaie de proprietăţi, prin instrumentul comutator
, plasat ultimul în fila Instrumente proiectare formular a panglicii.
Penultimul buton din aceiaşi filă este instrumentul comutator care face activă/inactivă Lista de câmpuri a bazei de date curente, pe locul Foii de proprietăţi.
91
În continuare, trebuie ataşate controalele. Procedura de ataşare a controalelor este similară cu procedura plasare a acestora lor în formulare. Raportele constituie unul din tipurile de obiecte fundamentale ale ACCESS ului. Deoarece menirea lor de bază este de prezentare a informaţiilor di n bazele de date, cele mai importante proprietăţi ale unui raport sunt proprietăţile referitoare la date. Stabilirea tabelului sau cererii de interogare de unde raportul îşi va extrage informaţiile se face prin proprietatea sursă de înregistrări (în limba engleză: Record Source). Precum la formulare, şi la rapoarte, stabilirea câmpului de unde provin valorile afişate în controale, se face cu ajutorul proprietăţii sursă control (în engleză: Control source). Informaţiile provenite din celulele tabelelor vor fi afişate în secţiunea de detaliu. Controalele inserate în această secţiune vor fi afişate în raport, în mod repetat, pentru fiecare articol al sursei de înregistrări. În rapoarte mai pot fi create anteturi şi subsoluri pe diferite niveluri: antet şi subsol de raport, antet şi subsol de pagină, antet şi subsol de grup.
Exerciţiu: Pe baza informaţiilor din interogarea totul şi toate creaţi un raport cu produsele vândute clienţilor. Rezolvare Utilizând instrumentul
,
se deschide o fereastră goală pentru crearea 92
raportului. Vom începe prin a lega raportul şi controalele de date. După ce este selectat raportul (clic în zona din exteriorul grilei din fereastra raportului) , se stabileşte valoarea totul şi toate prin modificarea proprietăţii Sursă înregistrări (sau se alege din lista ascunsă) a filei Date din Foaia de proprietăţi. Apoi, pe rând, pentru fiecare valoare de expus în rândurile raportului, se plasează câte o casetă text. Pentru fiecare casetă text în parte, trebuie elimitată eticheta care e agăţată de mediul Access, automat de casetă (se face clic pe etichetă şi se apasă tasta Delete). Legarea casetelor de date se face după ce se selectează prin alegerea câmpului corespunzător din lista ascunsă a proprietăţii Sursă control a filei Date din Foaia de proprietăţi. După efectuarea acestor operaţii, raportul este funcţional, precum este redat în modul Vizualizare proiect în figura de mai jos.
Raportul în modul Vizualizare raport este redat în figura de mai jos.
După ce s-au stabilit sursele de date pentru întreg raportul, trebuie stabilite controalele care vor fi conţinute în raport, proprietăţile lor şi, în mod special, pentru 93
fiecare control în parte, sursa datelor. Această activitate constituie particularizarea unui raport. Într-un raport se pot particulariza următoarele elemente: Sursa de înregistrări. Se modifică tabelele şi interogările pe care se bazează un raport. Sortarea şi gruparea datelor. Se pot sorta date în ordine ascendentă sau descendentă. De asemenea, se pot grupa înregistrări în unul sau mai multe câmpuri şi afişa subtotaluri şi totaluri generale într -un raport. Fereastra Raport. Se pot adăuga sau elimina butoanele Maximizare şi Minimizare, modifica textul din bara de titlu şi alte elemente ale ferestrei Raport. Secţiuni. Se pot adăuga, elimina, ascunde sau redimensiona secţiunile antet, subsol şi detaliu ale unui raport. De asemenea, se pot seta proprietăţile secţiunilor pentru a controla aspectul şi imprimarea unui raport. Controale. Se pot muta, redimensiona sau seta proprietăţile fonturilor unui control. De asemenea, se pot adăuga controale pentru a afişa valori calculate, totaluri, data şi ora curente şi alte informaţii utile într -un raport.
5.2. Particularizarea antetului şi subsolului La fel ca şi în cazul formularelor şi rapoartele conţin antet, detaliu şi subsol. Dar, suplimentar faţă de formulare, rapoartele pot conţine această împărţire şi la nivel de pagină. Şi, mai mult decât atât, rapoartele sunt făcute să treacă peste mai multe înregistrări pentru a afişa rezultate. Valorile afişate pot reda situaţia dintr-o înregistrare sau din grupuri de înregistrări. De exemplu, pentru casetele care includ valori numerice, se poate seta pentru proprietatea "Executare sumă " (în engleză Running Sum) valoarea "Over All". Gruparea induce existenţa până la nivel de grup a secţiunilor: antet, detaliu şi subsol. Antetul raportului este secţiunea dintr-un raport utilizată pentru plasarea de informaţii (cum ar fi titlul, data sau o prefaţa) la începutul unui raport. Subsolul de raport este acea secţiune dintr-un raport care este utilizată pentru a plasa informaţii ce în mod normal apar în partea finală a unui raport, cum ar fi numărul total de pagini, semnături, data creării şi sumele de raport. Antetul şi subsolul unui raport apar la începutul şi sfârşitul unui raport imprimat.
94
Antetul de pagină este utilizat pentru a afişa titlul, titluri de coloane, date calendaristice sau numere de pagină în partea de sus a fiecărei pagini a unui formular sau raport. Subsolul de pagină este utilizat pentru a afişa rezumate de pagină, date calendaristice sau numere de pagină în partea de jos a fiecărei pagini dintr -un formular sau raport Antetul şi subsolul de pagină apar în partea superioară şi cea inferioară a fiecărei pagini. Antet de grup este utilizat pentru a plasa informaţii, cum ar fi numele de grup sau totalul de grup, la începutul unui grup de înregistrări. Subsol grup se utilizează pentru a plasa informaţii, cum ar fi numele de grup sau totalul de grup, la sfârşitul unui grup de înregistrări. Antetul şi subsolul de grup apar în partea superioară şi cea inferioară a grupărilor articolelor. Adăugarea sau eliminarea acestor elemente se face cu ajutorul instrumentelor din grupul Afişare/Ascundere din fila Instrumente proiectare rapoarte/Aranjare a panglicii. Antet/Subsol raport Antet/Subsol pagina
Cu excepţia antetului şi subsolului de grup, celelalte niveluri de anteturi şi subsoluri sunt perechi. Ele pot fi disponibile doar împreună. Dacă este necesară doar o componentă, pentru cealaltă se setează înălţimea la dimensiunea 0. Eliminarea anteturilor şi subsolurilor atrage după sine si ştergerea tuturor controalelor plasate pe suprafaţa lor.
Exerciţiu Adăugaţi antet şi subsol raportului de la exerciţiul precedent. În antetul raportului să se înscrie un antet de firmă, şi numele de coloane, iar la subsol să se scrie data listării şi semnătura persoanei care a făcut listarea. Rezolvare: se deschide baza de date Facturi; se alege din panoul de navigare raportul Raport TEST; se comută în Vizualizare în mod proiectare; se urmăreşte să existe setat Antet/subsol raport; 95
se glisează în antetul raportului, din bara de instrumente, etichet a pentru a se scrie firma, în partea stângă sus şi ca titlu: Lista produselor vândute pe clienţi. Titlul se poate face cu un font mai mare şi aldin; se măreşte înălţimea antetului şi se adaugă în antet etichete pentru antetul coloanelor, care se aliniază în linie cu cea mai de sus; se trasează un dreptunghi, care să înconjoare etichetele, şi pentru ca să nu acopere etichetele se setează proprietatea Stil fundal cu culoarea Roşu şi se plasează în spatele etichetelor realizate cu instrumentul Trimitere în ultim plan din fila Aranjare; la subsolul raportului se adaugă etichetă cu conţinutul (legenda) Listat în data şi o etichetă Întocmit / Semnătura; se acţioneză pe instrumentul Data şi ora din panglică, şi pentru că se adaugă etichete cu data şi ora sistemului în partea stângă a antetului, data trebuie glisată în zona de subsol, lângă Listat în data, iar eticheta cu ora se poate şterge; se trasează o linie în partea de subsol, pentru a delimita elementele acestuia de articolele listate.
În modul Vizualizare raport rezultatul este următorul:
96
5.3. Gruparea datelor într-un raport. Realizarea de totaluri şi subtotaluri Atunci când informaţiile prezentate în rapoarte trebuie structurate grupat, pe anumite grade de centralizare, fiecare grup putând să aibă antet şi subsol (partea de detaliu este una singură pentru întreg raportul), pentru a adăuga sau elimina aceste elemente de grup într-un raport se foloseşte instrumentul Sortare şi Grupare
(simbolul
) din fila Instrumente proiectare rapoarte din panglică.
În subsolul ferestrei mediului ACCESS apare caseta de dialog Grupare, sortare si totaluri, şi se face clic pe butonul Adăgare grup şi automat este afişat un rând în casetă pentru a ajuta la precizarea Grupării după selectare Câmp sau Expresie.
Pentru particularizare se aleag parametrii doriţi: modul de sortare, dacă exită totaluri sau titluri, dacă se afişează sau ascunde secţiunea de antet sau subsol la nivel de grup. Grupările se înscriu în această casetă în ordinea ariei lor de cuprindere. Pentru adăugarea de controale care vor reda totalul tuturor valorilor unui câmp, la nivel de raport, se procedează în felul următor: 1. Se adaugă o casetă text în zona de subsol a raportului. Este de preferat ca această casetă să fie o copie a unei casete pe care o totalizează (cu operaţiile obişnuite de copiere şi lipire); 2. La proprietatea Sursă control se setează: =Sum([expresie]) unde expresie poate lua valorile variabile, funcţie de înregistrări, care vor fi totalizate; 3. La proprietatea Execuţie sumă se setează valoarea Nu. Totalizarea unor valori pe grupuri (subtotaluri de raport) se face în mod similar cu totalurile generale pe raport cu diferenţa că ele se plasează în subsolurile de grup. Problema se pune în mod similar şi pentru totalurile pe pagini.
Exerciţii 1. Să se adauge o grupare după denumirea produselor în raportul construit 97
în exerciţiu precedent. 2. Să se gliseze în antetul grupării produsului, din zona de detaliu, informaţiile generale despre produse. 3. Să se realizeze în zona de detaliu, o casetă pentru valoarea fiecărei livrări şi să se rectifice antetul coloanelor. 4. Să se totalizeze valorile facturilor pe produse şi pe întreg raportul. Pentru lizibiliate, totalurile produselor sunt precedate de o etichetă „Total pe produsul:‖ şi de o casetă text ce va reda codul şi numele produslui. Pentru raport, valoarea va fi precedată doar de o etichetă „Total general‖. Totalul pe produse şi informaţiile din antetul acestei grupări să fie incadrate într-un dreptunghi cu fond galben, iar cele de antet/suport - pe întreg raportul - într-un dreptunghi roşu. Rezolvare: 1. Pentru adăugarea grupării după câmpul furnizori se face clic Sortare şi
Grupare (simbolul panglică.
) din fila Instrumente proiectare rapoarte din
2. În subsolul ferestrei mediului ACCESS apare caseta de dialog Grupare, sortare şi totaluri, şi se face clic pe butonul Adăgare grup şi automat este afişat un rând în casetă, cu o listă din care se alege câmpul DenProd. Acţionând pe butonul Mai multe , se stabileşte să fie vizibile secţiunea antet şi secţiunea subsol. Se glisează din zona detaliu casetele text corespunzătoare câmpurilor: Codprod, Denprod, Um şi Pret. 3. Se face o copie a casetei text corespunzătoare câmpului Cant. (Prin intermediul memoriei clipboard cu operaţiile Copy (Ctrl + C) şi Paste (Ctrl + V). Acest control se mută în partea dreaptă a benzii Detaliu. La proprietatea Sursă control se scrie: =Cant * Pret, pentru a se specifica faptul că e vorba de produsul a două valori (Semnul „=‖ este indispensabil). La proprietatea Format se alege Fix, iar la Zecimale se precizează 2. Redăm mai jos, în modul Vizualizare aspect, configurările făcute. 98
4. În modul Vizualizare proiect, se selectează, cu un clic, caseta de text corespunzătoare expresiei valoare, se copiază în clipboard, cu Ctrl + C şi se lipeşte cu Ctrl + V. Cu mouse-ul se glisează caseta copiată, în secţiunea Subsol DenProd. Se face o nouă lipire şi se glisează caseta în secţiunea Subsol raport. Se reajustează înălţimea secţiunii de detaliu. Pentru cele două casete copiate se setează următoarele proprietăţi de dată: Sursă control : =Sumă (Cant * Pret); Execuţie sumă : Nu. Se trasează un dreptunghi cu ajutorul instrumentului din fila Instrumente raport/ Proiectare din panglică, se colorează cu galben prin intermediul listei ascunse de culori, legată de instrumentu din panglică şi se trimite, pentru a nu acoperi obiectele, în ultimul plan cu
ajutorul butonului , din fila Instrumente raport/ Aranjare. În mod similar se trasează dreptunghi în subsolul raportului şi antetul grupului.
99
Rezultatul este prezentat în modul Vizualizare proiectare, în figură:
Rezultatul este prezentat în modul Examinare înaintea imprimării, în figură:
Exerciţii 1. Să se adauge o grupare după denumirea clienţilor în raportul construit în exerciţiu precedent. 2. Să se gliseze în antetul grupării Clienţilor, din zona de detaliu, 100
informaţiile generale referitoare la Clienţi şi să se rectifice antetul coloanelor. 3. Să se totalizeze valorile facturilor pe Clienţi. Pentru lizibiliate, totalurile produselor sunt precedate de o etichetă „Facturat clientului:‖ , şi de o casetă text ce va reda codul şi numele clientului. Totalul pe client şi informaţiile din antetul acestei grupări să fie încadrate într-un chenar albastru deschis, cu fond alb. Să se elimine fondul controalelor din detaliu. 4. Să se pună o casetă care să redea numărul curent al ieşirilor pe clienţi. 5. Să se salveze raportul într-un document Word. Rezolvare: 1. Operaţiile sunt evidente, şi se rezolvă în mod similar exerciţiului anterior. 2. Idem. 3. Idem. 4. O casetă cu număr curent al articolelor se realizează prin următorii paşi: din caseta cu instrumente se selectează caseta text şi se glisează în partea stângă a zonei de detaliu; se şterge eticheta lipită de caseta de text; pentru proprietatea Sursă control se setează: =1; pentru proprietate Execuţie sumă se setează Peste tot;. Rezultatul este prezentat în modul Vizualizare proiectare, în figură:
101
5. Pentru a salva lista rezultată într-un document Word, în prealabil, documentul trebuie vizualizat în modul Examinare aspect şi din linia cu instrumente proprie acestui mod de vizualizare se alege pictograma aplicaţiei Microsoft Word şi se produce deschiderea raportului cu procesorul de texte. Nu sunt importate elementele grafice. Rezultatul este redat mai jos: SC DEPOZITUL SRL LISTA PRODUSELOR VANDUTE PE CLIENTI Cod Produs
Denumire Produs
Nr. Client
Adresa
Telefon
factură
Data factură
Preţ/UM Delegat
UM Cantitate Val
7023 Lapte
2,9 /l 111111 SC First SRL
Str Primaverii nr 1
26111111
1
4
23.10.2009 Antonescu
15
43,50
2
3
22.10.2009 Antonescu
18
52,20
Facturat clientului: 222222 SC Secundul SRL
Str.Verii nr 2
1
111111SC First SRL 6
2
2 Facturat clientului:
333333 SC Terţa Parte SRL
Str.Toamnei nr 3
1
25.10.2009 Popescu
15
22.10.2009 Popescu
30
222222SC Secundul SRL
43,50 87,00 130,50
26133333 5
Facturat clientului: Total pe produs:
95,70
26122222
24.10.2009 Trandafir
20
333333SC Terța Parte SRL
58,00 58,00
7023Lapte
284,20
1009 Ulei
3,27/Kg 111111 SC First SRL
Str Primaverii nr 1
1
26111111 4
Facturat clientului: 222222 SC Secundul SRL
Str.Verii nr 2
1
1 Str.Toamnei nr 3
1
39,24 39,24
21.10.2009 Popescu
20
222222SC Secundul SRL
65,40 65,40
26133333 5
Facturat clientului: Total pe produs:
12
26122222
Facturat clientului: 333333 SC Terța Parte SRL
23.10.2009 Antonescu 111111SC First SRL
24.10.2009 Trandafir
11
333333SC Terța Parte SRL
35,97 35,97
1009Ulei
140,61
1075 Zahar
3,1 /kg 222222 SC Secundul SRL
Str.Verii nr 2
1
26122222 1
Facturat clientului: 333333 SC Terţa Parte SRL
Str.Toamnei nr 3
1 Total pe produs:
15
24.10.2009 Trandafir
1075Zahar
102
77,50 77,50 124,00 548,81
Listat în data: 17:56:03
46,50 25
333333SC Terța Parte SRL
Total pe produsul: 8 mai 2010
46,50
26133333 5
Facturat clientului:
21.10.2009 Popescu 222222SC Secundul SRL
Întocmit
Semnătura
CAPITOLUL VI. LIMBAJUL VISUAL BASIC 6.1 Editarea modulelor Visual Basic for Application Visual Basic for Application (abreviat VBA) este limbajul de programare intern al pachetului software pentru birouri Microsoft Office. Cu ajutorul VBA pot fi create modulele Access, obiecte ale baze de date care
Instrumente
Exploratorul proiectelor
Instrumente pentru depanare
Ferestra de editare cod
Ferestra proprietăţilor obiectelor
Ferestra afișare
Ferestra urmărire variabile
permit scrierea de rutine (subprograme, proceduri 8, funcţii 9) pentru accesa şi face particularizări în mod intim tabelele, formularele, rapoartele şi interogările din baza de date şi funcţionarea conjugată a acestora. VBA este un dialect al limbajului Visual Basic. În VBA nu pot fi create programe de sine stătătoare ci proceduri a căror execuţie poate fi iscată de interacţiunea utilizatorului cu obiectele incluse în fişierele Office (documente de tip Word, registre de tip Excel, prezentări de tip Power Point şi baze de date Access). Aceste proceduri constituie o perfecţionare a unui limbaj de macrocomenzi a 8
metodă de rezolvare a unei probleme, defalcată în etape succesive; func ţie executată de un
subprogram, parte din sintaxa limbajelor evoluate, conform www.dexonline.ro preluat din Marele dicţionar de neologisme, Florin Marcu, Editura Saeculum, 2000 9
ib idem
103
pachetului Office. Produsele pachetului Office, ele însele, crează în unele situa ţii, module scrise în VBA, a căror prezenţă şi rulare pot fi sesizate sau nu de utilizatori. Unitatea elemetară prin care programatorii scriu un algorit m într-un limbaj de programare este instrucţiunea. Un grup de instrucţiuni realizează o unitate de program; în cazul VBA, o procedură. Există mai multe tipuri de proceduri: proceduri eveniment: O procedură executată automat ca răspuns la un eveniment iniţiat de utilizator sau de codul program, sau activat de sistem prin adăugarea de cod la un eveniment dintr-un formular sau raport. proceduri Function : Procedură care returnează o valoare şi care poate fi utilizată într-o expresie. Funcţia se declară cu instrucţiunea Function şi se sfârşeşte cu instrucţiunea End Function. proceduri Sub : Procedură care îndeplineşte o operaţiune. Spre deosebire de o procedură Function, o procedură Sub nu returnează valori. O procedură Sub se declară cu instrucţiunea Sub şi se încheie cu o instrucţiune End Sub. Procedurile sunt amplasate în module. Pentru crearea, testarea, depanarea şi/sau rularea procedurilor utilizator, se recurge la instrumentele speciale asigurate de mediul Visual Basic, care se iniţiază prin butonul , care este aşezat în capătul din stânga al filei Instrumente baza de date a panglicii. Procedurile sunt definite prin tastarea instrucţiunii Function sau Sub, eventual precedate de specificatorul de domeniu Public sau Private. Pe acelaşi prim rând se scrie efectiv un nume de procedură (funcţie sau subrutină) imediat urmat de toate argumentele între paranteze iar la funcţie şi de comutator funcţiei. De exemplu, următoarea declaraţie pentru funcţia Încărcat specifică şirNumeForm ca argument: Function Încărcat (sirNumeForm As String) As Boolean Sau, următoarea declaraţie pentru procedura Sub: AfişEveniment indică NumeEveniment ca argument: Sub AfişEveniment (NumeEveniment as String) Corpul definirii procedurilor conţine liniile de cod în limbaj Microsoft Visual Basic care vor efectua operaţiunile sau calculele necesare pentru realizarea algoritmilor şi se termină cu linia de final End Sub sau End Function scrisă automat de editor când s-a declarat procedura. Pentru a facilita gestionarea obiectelor, a procedurilor în module, în editor există pentru programatori fereastre de afişare a proprietăţilor şi de exploarare a proiectelor, permiţându-se crearea de proceduri şi module, cătarea şi actualizarea sau stergerea lor. Editorul mediului are facilităţi de colorare a cuvintelor cheie, declaraţiilor utilizator, frazelor eronate, comentariilor etc. Cea mai puternică facilitate a editorului 104
VBA este posibilitatea afişării contextuale în momentul scrierii instruc ţiunilor în limbajul VBA, a elementelor care caracterizează un anumit context: În fine, dar nu mai puţin important, este depanatorul, nelipsit din mediile de programare deja de aproape 20 de ani. În secţiunile următoare ale acestui capitol vom prezenta elemente de limbaj VBA în scopul evidenţierii unor metode de access la obiectele bazei de date.
6.2 Elemente de sintaxă Modulele VBA se bazează pe un set de instrucţiuni care conţin cuvinte preluate din limba engleză. În afacra acestora se utilizează identificatorii. Un identificator este numele unui element dintr-un program, fie el variabilă, constantă, tip definit de utilizator, enumerare, procedură, funcţie, obiect, metodă, proprietate, control, formă modul sau chiar proiectul însuşi. Specific VBA este faptul că numele şi conţinutul acestor identificatori pot fi alcătuite atât din prima parte a codului ASCII (primele 128 caractere) cât şi din codul ASCII extins (caractere speciale, care în funcţie de modul de redare pot căpăta diferite forme, de exemplu diacritice). Sintaxa a împrumutat multe reguli care constituie esenţialul regulilor limbajului natural: între cuvinte se intercalează spaţiu, după rânduri se tastează returul de car (enter), elementele de acelaşi tip sunt despărţite de virgulă, argumentele funcţiilor sunt delimitate de parantezele rotunde, şirurile de caractere care se manipulează intacte sunt delimitate de ghilimele, numele variabilelor trebuie să înceapă cu o literă, etc. În plus mai există câteva reguli specifice. Caracterele interzise în denumiri sunt %!$&#@. Nu e permis să existe doi identificatori identici cu acelaşi scop şi există o serie de identificatori standard, definiţi de Visual Basic. Nu este permis să fie numiţi identificatorii prin cuvintele cheie ale limbajului, precum Dim, Private, Public, Array, For, Next, While, with, If, Then, Else, End, etc. Pentru a face codul cât mai lizibil mulţi programatori folosesc prefixe în definirea identificatorilor care indică tipul de date referit şi scopul lor. Acesta regulă nescrisă se numeşte scrierea ungară după un angajat ungur al Microsof t care a promovat-o. Exemplu: iCod pentru un cod de tip integer; astrLuni pentru un masiv de şiruri de caracter cu lunile anului; dbPersonal pentru o bază de date personal, etc. Comentariile sunt şiruri de caractere care au în faţă caracterul (') şi au rolul de a face textul programului mai lizibil, nefiind compilate sau interpretate.
105
În general în Visual Basic dacă se doreşte să se scrie mai multe instruc ţiuni pe o linie, aceasta sunt separate prin caracterul (:) . Invers, dacă o declaraţie este prea mare se poate scrie pe mai multe linii, pentru continuare se foloseşte caracterul continuator de linie ( _)(un spaţiu urmat de o linie jos). Algoritmii care sunt transformaţi în programe pot fi concepuţi iniţial prin scheme logice. Schemele logice pot să fie sub forma unui pseudocod - care este un limbaj mai puţin riguros decât un limbaj de programare, posibil şi cu termeni din limba română - sau sub formă grafică. Cele mai utilizate dintre aceste simboluri sunt: start şi final de algorim blocuri secvenţiale operaţii de intrare-ieşire bloc de decizie apel de subprograme conectori
6.3 Casete de dialog predefinite În aproape toate limbajele care au formă Visual se pot folosi două funcţii pentru a se putea realiza casete de dialog predefinite. Ev ident, formularele şi rapoartele reprezintă o formă elegantă de introducere şi respectiv afişare a datelor care se bazează în principal pe structura bazei de date. În afară de acestea, există în VBA instrucţiuni pentru introducerea unor date izolate şi afi şarea de mesaje. Această necesitate se simte mai ales în faza de test a unei proceduri sau funcţii, sau pentru afişarea de mesaje către utilizatorul bazei de date. Şi mai mult chiar nici posibilitatea Debug.Print nu este de înlăturată în această fază. Pentru aceasta Visual Basic pune la dispoziţia programatorilor două funcţii: InputBox care afişează o casetă de dialog ce permite preluarea datelor de la utilizator şi MsgBox ce permite afişarea diferitelor informaţii. Funcţia InputBox afişează o casetă de dialog modală care cere utilizatorului să introducă date. Funcţia returnează o valoare de tip String formată din caracterele introduse de utilizator în caseta cu text din fereastra de dialog dacă utilizatorul apasă
106
butonul OK; dacă se apasă butonul Cancel şirul returnat este şirul vid "". Sintaxa este: InputBox(prompt[,title] [,default] [,xpos] [,ypos] [,helpfile, context]) Dacă nu se pun parantezele rotunde InputBox se transformă în procedură. Elementele din sintaxă reprezintă: prompt reprezintă un şir de caractere afişat ca mesaj în caseta de dialog. Numărul maxim de caractere este de 1024; title, este un şir de caractere ce va fi afişat drept titlu în bara de titlu a casetei de dialog; default, reprezintă un şir de caractere ce va fi afişat în caseta cu text şi care va fi returnat de funcţie în caz că utilizatorul nu introduce alte date; xpos, ypos, sunt expresii numerice care specifică coordonatele colţului stânga sus al casetei de dialog faţă de colţul stânga sus al ecranului; helpfile, este un şir de caractere ce specifică fişierul Help ce va fi folosit pentru a oferi asistenţă în lucrul cu caseta de dialog; Exemplu: InputBox "Introduceţi forma de învăţământ:", _ "Formă de dialog predefinită", "Zi", 1000, 1000 Funcţia MsgBox se utilizează pentru a obţine răspunsuri de tip Da / Nu de la utilizatori sau pentru a afişa diferite mesaje de avertizare, erori, atenţionări. După citirea mesajului utilizatorul va apăsa un buton pentru a închide fereastra. Funcţia returnează un Integer specificând ce buton a fost apăsat. Sintaxa este: MsgBox(prompt[, buttons] [, title] [, helpfile, context]) Unde: prompt - un şir de caractere afişat ca mesaj în caseta de dialog. Numărul maxim de caractere este de 1024; buttons – o expresie numerică ce reprezintă suma valorilor care specifică numărul şi tipul butoanelor afişate sau pictograma; title – un şir de caractere ce va fi afişat drept titlu în bara de titlu a casetei de dialog; helpfile - un şir de caractere ce specifică fişierul Help ce va fi folosit pentru a oferi asistenţă în lucrul cu caseta de dialog. Pentru setarea butoanelor există predefinite următoarele constante specifice VisualBasic: 107
Constante vbOKOnly VbOKCancel VbAbortRetryIgnore VbYesNoCancel VbYesNo VbRetryCancel VbCritical VbQuestion VbExclamation VbInformation VbDefaultButton1 VbDefaultButton2 VbDefaultButton3 VbDefaultButton4 VbApplicationModal
Valoare 0 1 2 3 4 5 16 32 48 64 0 256 512 768 0
VbSystemModal
4096
Descriere afişare Numai buton OK Butoanele OK şi Cancel Butoanele Abort, Retry şi Ignore Butoanele Yes, No şi Cancel Butoanele Yes şi No Butoanele Retry şi Cancel Pictograma Critical Message Pictograma Warning Query Pictograma Warning Message Pictograma Information Message Primul buton este implicit Al doilea buton este implicit Al treilea buton este implicit Al patrulea buton este implicit Modul de aplicaţie; Aplicaţia aşteaptă şi utilizatorul trebuie să răspundă la caseta de dialog Modul de sistem; Toate aplicaţiile sunt suspendate până la răspunsul la caseta de dialog
Primele constante cu valori (0–5) numărul şi tipul de butoane afişate; grupul următor (16, 32, 48, 64) descrie stilul pictogramelor; apoi grupul următor (0, 256, 512, 768) determină care buton este implicit; în fine ultimul grup (0, 4096) dă tipul modulului. Prin adunarea numerelor se obţin efecte compuse, dar se adună exclusiv o singură valoare dintr-un grup. Valorile returnate de funcţia MsgBox sunt: Constante vbOK vbCancel vbAbort vbRetry vbIgnore vbYes vbNo
Valoare 1 2 3 4 5 6 7
108
Descriere OK Cancel Abort Retry Ignore Yes No
Exemplu: MsgBox "Mesaj de atenţionare !", vbExclamation _ + vbOKCancel + vbDefaultButton1+vbsystemmodal, "Sistemul stă!"
6.4 Variabilele în VBA 6.4.1. Declararea variabilelor O variabilă se caracterizează prin două elemente: numele variabilei, care este un identificator cu ajutorul căruia putem să referim variabila pe parcursul programului şi tipul variabilei, care determină ce tip de dată poate stoca variabila. În funcţie de tipul variabilei compilatorul alocă o zonă mai mică sau mai mare de memorie pentru variabila respectivă. Ieftinirea memoriilor face ca rigoarea gestionării tipurilor de variabile să nu mai fie de actualitate, chiar declarările devin desuete în limbajele de programare moderne. VBA permite utilizarea declarării implicite a variabilelor la prima utilizare şi utilizarea tipului implicit Variant, prin care este posibil ca o variabilă să stocheze valori diferite în momente diferite. În schimb capătă importanţă crescută locul unde se declară şi spaţiul unde este recunoscută o variabilă şi durata de existenţă. Declararea unei variabile se face astfel: {Public| Private| Dim} NumeVariabilă [as TipVaribilă] Unde: NumeVaribilă este identificatorul variabilei; TipVariabilă este numele unui tip de date din cele pe care le acceptă Visual Basic. Dacă acesta lipseşte se consideră că variabila este de tipul Variant. Exemple: Dim iCant as Integer Dim dtZiSal as Date
´defineşte variabila iCant de tip Integer ´defineşte variabila dtZiSal de tip date 109
Private sNume as String Public lstListaPret as ListBox
´defineşte variabila sNume de tip String ´defineşte variabila lstListaPret ce va conţine o referinţă către un obiect de tip ListBox
O caracteristică deosebit de importantă a variabilelor este do meniul de valabilitate şi durata de existenţă. Astfel există variabile locale la nivelul unei proceduri. Aceste variabile se declară în interiorul procedurii cu ajutorul cuvântului cheie Dim. Ele există atâta timp cât se execută procedura în care au fost declarate. De asemenea, ele pot fi utilizate numai în cadrul acelei proceduri neavând nici o semnificaţie în altă parte a programului. Au un domeniu de valabilitate doar la nivelul proceduri şi au o durată de viaţă temporară. Este recomandat să se folosea scă Dim doar pentru declararea acestui tip de variabile chiar dacă Visual Basic permite folosirea lui şi în alte contexte. Un alt tip de variabile sunt cele cu domeniu de vizibilitate la nivelul unui modul. Aceste variabile pot fi folosite oriunde în interiorul modulului în care au fost declarate şi există atâta timp cât este folosit modulul respectiv. Este recomandabil ca declararea lor să se facă cu ajutorul cuvântului cheie Private în loc de Dim. Un al treilea tip de variabile sunt cele globale la nivelul întregului program. Acestea sunt pot fi folosite oriunde în program, au un domeniu de valabilitate global şi există atâta timp cât se execută programul. Declararea acestor variabile se face folosind cuvântul cheie Public. Un alt tip de variabile sunt cele statice. Acestea pot avea un domeniu de valabilitate locală sau la nivelul unei proceduri, dar o durată de existenţă permanentă. Declararea se face cu ajutorul cuvântului cheie Static. Chiar dacă sunt definite la nivelul procedurilor, variabilele Static îşi păstrează valoarea şi după ce procedurile gazdă îşi termină execuţia. Dacă procedura va fi din nou apelată variabila statică nu va fi iniţializată automat de Visual Basic, lucru ce se întâmplă cu variabilele care nu sunt statice, ci va avea valoare pe care a avut-o la ultima ei folosire. Variabilele locale la nivelul unui modul sau publice se declară în secţiunea de declaraţii a modulului (Declarations). În unele versiuni de Visual Basic se permite declararea variabilelor şi implicit, adică o variabilă este considerată declarată la prima ei folosire. Nu este necesară o declarare prealabilă cu ajutorul cuvintelor cheie Dim, Private, Public. În mod implicit Visual Basic le consideră de tip Variant pe toate. În VBA acest lucru nu este permis. Una dintre cele mai periculoase erori întâlnite la declararea unei variabile este următoarea: 110
Dim a, b, c as Integer, care la prima impresie pare că sunt definite trei variabile a,b,c de tip Integer dar de fapt numai c este de tip Integer, celelalte fiind în mod implicit de tip Variant. Corect este Dim a as Integer, b as Integer, c as Integer
6.4.2. Tipurile variabilelor în VBA În paragrafele precedente ne-am folosit de câteva tipuri de date folosite în Basic. Important în alegerea tipului de dată este natura d atelor şi intervalul în care poate lua valori variabila. VBA acceptă două categorii de tipuri de date: standard (predefinite); utilizator. Tipurile de date standard sunt: Tipul de Tipul informaţiei dată Integer Numere întregi Long Numere întregi Single Numere reale
Double
Currency
String
Byte Boolean Date Object Variant
Memoria Intervalul de valori necesară 2 bytes -32,768 la 32,767 4 bytes Aproximativ – 2.1E9 la 2.1E9 4 bytes -3.402823E38 la –1.401298E-45 pentru valori negative şi1.401298E-45 la 3.402823E38 pentru valori pozitive Numere reale 8 bytes -1.79769313486232E308 la – (dublă precizie) 4.94065645841247E-324 pentru valori negative şi 4.94065645841247E-324 la 1.79769313486232E308 pentru valori pozitive Numere cu până 8 bytes -922,337,203,685,477.5808 la la 15 cifre întregi 922,337,203,685,477.5807 şi 4 zecimale Şir de caractere 1 byte / Până la 65000 de caractere pentru şirurile cu caracter lungime fixă şi până la 2 miliarde de caractere pentru şirurile dinamice Numere întregi 1 byte 0 la 255 Valori logice 2 bytes True sau False Dată şi timp 8 bytes 01.01.100 la 31.12.9999 Referinţe către 4 bytes N/A obiecte Oricare din 16 bytes N/A 111
tipurile precedente
+ 1 byte per caracter
Cu fiecare tip de dată sunt permise o serie de operaţii. Ast fel: operaţiile care se pot face cu valorile unui tip întreg sunt: adunarea (+); scăderea(-); înmulţirea(*); împărţirea întreagă (\) a \ b returnează câtul împărţirii lui a cu b; împărţirea reală(/); restul împărţirii întregi (mod); ridicarea la putere (^) de asemenea sunt permise operaţiile relaţionale: mai mic sau egal (<=); mai mic (<); egal (=); mai mare(>); mai mare sau egal (>=);Rezultatul unor astfel de operaţii este de tip boolean, având valorile True sau False; operaţiile care se pot face cu valorile unui tip real sunt: adunarea (+); scăderea(-); înmulţirea(*); împărţirea(/); ridicarea la putere (^); sunt permise operaţiile relaţionale prezentate la tipurile întregi; şirurile de caractere suportă operaţia de concatenarea (& sau uneori +) şi operaţiile de comparaţie (<,<=,=,>=,>). Prin concatenare două sau mai multe şiruri de caractere pot fi lipite formând un singur şir de caractere. Cu ajutorul operaţiilor de comparaţie se stabileşte poziţia alfabetică a şirurilor de caractere; valorile logice, booleene (True şi False) suportă următoarele operaţii logice: negarea logică (Not); şi logic (And); sau logic (Or); sau exclusiv logic (Xor); echivalenţa logică (Eqv); implicaţia logică (Imp); tipurile de dată de tip obiect suportă o singură operaţie – cea de comparaţie (Is). Cu ajutorul acestei operaţii se verifică dacă două variabile de tip obiect referă acelaşi obiect sau nu. Un tip particular de date sunt constantele. În expresii, constantele au regim obişnuit ca al oricăror alţi operanzi. O definiţie de constantă introduce un identificator ca sinonim al unei valori constante. În Visual Basic există două tipuri de constante: intrinseci sau definite de sistem. Visual Basic pune la dispoziţie o serie întreagă de constante pentru culori, taste, figuri etc; simbolice sau definite de utilizator. Puteţi defini propriile dumneavoastră constante cu ajutorul cuvântului cheie Const astfel: [Public|Private] Const NumeConstanta[As type] = expresie Exemple: Const PI = 3.14159265358979 Const NR_CARACTERE = 256 Const ZI_DE_NASTERE = #02.04.1981# Const NUME_DE_COD = "Şacalul" 112
6.4.3. Iniţializarea variabilelor Variabilele declarate sunt iniţializate automat de compilator: cele numerice cu 0, text cu "", etc. Modificarea informaţiilor stocate în variabile se face cu ajutorul instrucţiuni de atribuire. Sintaxa: variabilă = expresie sau Set variabilă = expresie Exemple: iOreLunar = 170 sNume ="Popescu"&" Ioan" dtDataAng=#13.06.2000# s=s+1
– variabila a va stoca valoarea 170; – variabila va stoca şirul de caractere "Popescu Ioan" – variabila va stoca data 13.06.2000 – reprezintă primul şoc întâlnit de novicii în informatică, care nu sesizează că e vorba de operatorul de atribuire nu de comparare, în cazul de faţă, în locaţia de memorie s; va fi suprascris conţinutul anterior incrementat cu 1
Unei variabile de tip obiect (tipul generic Object sau de tipul celorl alte obiecte Access 2000) nu i se poate atribui un obiect în varianta clasică: variabila=expresie sau variabila1=variabila2, ci utilizându-se instrucţiunea Set: Set variabila_obiect = expresie_obiect. În urma apelului acestei instrucţiuni, variabila_obiect va referi obiectul returnat de expresia expresie_obiect. Şi numele de proceduri pot fi considerate un caz particular de variabile.
6.5. Tablouri În limbajele de programare se încearcă crearea de structuri de date complexe. În aproape toate există posibilitatea de lucru cu tablouri sau masive. Un tablou este o structură de date percepute ca fiind adiacente, care poartă un singur nume şi au acelaşi tip, care poate să păstreze mai multe valori de acelaşi tip. Tabloul poate fi asimilat unui şir finit din matematică, Ca şi şirurile, tablourile pot fi uni-, bi-, tri- sau multi-dimensionale. Distincţia între elemente se face cu ajutorul 113
indecşilor care redau un număr de ordine al elementelor pe fiecare dimensiune. Bineînţeles, dacă tabloul este de tip Variant atunci elementele acestuia pot să conţină diferite tipuri de date (numerice, şiruri de caractere, date calendaristice, obiecte). De obicei un tablou unidimensional este numit vector iar unul bidimensional matrice. Tablourile sunt intuitiv create în programare implicaţie a scopului declarat al calculatoarelor programabile, acela de a efectua cu viteză operaţii de rutină, omogene şi repetivive. Pentru gestionarea cât mai eficientă a elementelor tablourilor sunt de mare folos structurile repetitive. Declararea unui tablou cu dimensiune fixă se poate face astfel: Dim|Public|Private _ NumeTablou([[NrPrimElement to] NrUltimElement], _ [[NrPrimElement to] NrUltimElement], ...) [As TipDată] Dacă nu se specifică NrPrimElement to se consideră a fi 0. Dacă se specifică NrUltimElement pentru toate dimensiunile, tabloul este considerat a fi de dimensiuni fixă. Dacă nu se specifică NrUltimElement pentru o dimensiune, tabloul este considerat a fi dinamic şi în interiorul procedurilor aceasta poate fi precizată cu instrucţiunea Redim, care are aceiaşi sintaxă ca instrucţiunea Redim. Ca de exemplu prin următoarea instrucţiune se declară un vector cu 7 elemente: Dim zi(6) As string Alte exemple ar fi: Dim PuncteÎnPlan(10,10) As Byte ’ matrice cu 121 elemente Private PuncteÎnSpaţiu( 1 To 30,1 To 30, 1 To 30) As Byte ’ masiv cu 30 3 elemente După declaraţie, elementele tabloului pot fi folosite în expresii sau în orice loc unde poate fi utilizată o variabilă obişnuită. Deci: zi(0)=”Luni” ‟ încarcă primul element al vectorului zi ‟ cu şirul”Luni”
6.6 Funcţii şi proceduri definite de utilizator O funcţie/procedură reprezintă un bloc de instrucţiuni care realizează o prelucrare. Funcţiile şi procedurile se aseamănă întrucâtva şi de aceia au o denumire comună: subprograme. Acestea pot fi apelate ori de câte ori este nevoie, fără a mai fi necesară rescrierea lor. În felul acesta se reduce considerabil efortul de programare. Aplicaţiile mai complexe nu pot fi concepute fără a apela la proceduri. În plus programarea pe obiecte se poate realiza doar făcându -se ca fiecărei posibilităţi de 114
modificare a proprietăţilor obiectelor sau răspunsului acestora la diferite evenimente să-i corespundă câte un subprogram. Pentru a da un grad de generalitate cât mai mare, funcţiile şi/sau procedurile conţin în definirea acestora o listă de parametri formali. În momentul apelării unei funcţii şi/sau proceduri se transmit către acestea valori pentru fiecare parametru al procedurii sau funcţiei (sau nimic dacă nu există parametri). Sintaxa pentru definirea unei proceduri este următoarea: [{Private|Public}]Sub nume_procedură[([{ByRef|ByVal}] param_1 [as tip_date],…)] [instrucţiuni] .... [Exit Sub] ... [instrucţiuni] End Sub Unde: Private şi Public au semnificaţie similară cazului declarării variabilelor. O procedură eveniment poate fi definită doar Private; Sub permite ieşirea forţată dintr-o procedură; instrucţiunile care încep cu Exit provoacă ieşirea forţată din blocurile de instrucţiuni ale anumitor structuri; Parametrii din definirea procedurii se numesc parametrii formali, ei precizează (implicit sau explicit) doar tipul parametrilor; {ByRef|ByVal} precizează modul de transmitere a parametrilor, care poate fi prin valoare sau referinţă. Implicit se consideră a fi ByVal. Dacă se utilizează ByVal modificările făcute argumentelor transmise procedurii se pierd după ieşirea din procedură, pe când, dacă se utilizează ByRef modificările făcute argumentelor transmise procedurii rămân şi după ieşirea din procedură. Apelul unei proceduri se poate face astfel: [Call]nume_procedura [(valoare_param_1,valoare_param_2,....)] Parametrii din apelul procedurii se numesc parametrii reali, ei sunt purtători de valoare în momentul apelului, iar dacă nu corespund ca tip cu parametrii formali, parametrii reali sunt convertiţi. Referitor la definirea şi apelul procedurilor să considerăm următorul exemplu: Sub test() v=7 transmit v 115
MsgBox v End Sub Sub transmit(ByVal a) a=a+3 End Sub Procedura test de tip Sub apelează procedura transmit de tip Sub. Modul de transmitere a parametrului este prin valoare. Prin rularea subrutinei test se afişează valoarea 7, cu toate că în interiorul subrutinei transmit parametrul este modificat, aceste transformări nu rămân la ieşirea din subrutină. Dacă se schmbă modul de transmitere a parametrului prin ByRef, linia de definirea a subrutinei apelate fiind Sub transmit(ByVal a), atunci rezultatul execuţiei subrutinei test va fi afişarea valorii 10. Sintaxa unei funcţii este următoarea: [{Private|Public}]Function nume_funcţie[([{ByRef|ByVal}]param_1 tip_date],…)] _ [as tip_date] [instrucţiuni] [nume_funcţie = expresie] ... [Exit Function] [instrucţiuni] [nume_funcţie = expresie] End Function
[as
Unde precizările de la explicarea sintaxei procedurilor de tip Sub, rămân valabile şi pentru acest tip de proceduri. Diferenţele apar de la de la specificul capacităţii funcţiilor de a returna o valoare. Tipul valorii returnate este specificat în linia de antet a definirii funcţiilor, după lista de parametrii. Valoarea se returnează prin linia descrisă de sintaxa: nume_funcţie = expresie Acest rezultat va fi returnat în momentul terminării execuţiei funcţiei. Apelul unei funcţii se poate face astfel: Variabila=nume_funcţie[(valoare_param_1,valoare_param_2,...)] , variabila preia rezultatul returnat de funcţie. Dar, prin apelarea u nei funcţii se returnează o valoare, deci funcţia apelată poate fi folosită ca un operand în orice expresie unde este permis tipul returnat. Referitor la definirea şi apelul funcţiilor să considerăm următorul exemplu: 116
Function test1() As Integer Dim v As Integer MsgBox increment(7) End Function Function increment(a As Integer) As Integer increment = a + 1 End Function Execuţia funcţiei afişează valoarea 8. Pentru a testa un subprogram fără parametri direct dintr -un modul, se poziţionează cursorul în corpul subprogramului, apoi se selectează comanda: Run, Go/Continue F5. Dacă se doreşte executarea subprogramului în modul Trace, se alege din meniul Debug, Step into F8. Limbajul VBA cuprinde în plus o serie de funcţii predefinite care facilitează scrierea procedurilor şi funcţiilor utilizator. Unele mai des folosite dintre acestea sunt: Abs(expresie_numerică): returnează valoarea absolută a unei expresii numerice, sau a unui număr; Asc(sir_caractere): returnează codul primului caracter din şirul de caract ere specificat; CDate(expresie): face conversia la tipul Date; CDbl(expresie): face conversia la tipul Double; Dec(expresie): face conversia la tipul Decimal; CInt(expresie): face conversia la tipul Integer; CLng(expresie): face conversia la tipul Long; CSng(expresie): face conversia la tipul Single; CStr(expresie): face conversia la tipul String; Cos(expresie_numerică): returnează cosinus dintr-o expresie numerică sau dintr-un număr. Valoarea returnată este de tip Double; Chr(COD_CARACTER): returnează caracterul ASCII cu codul specificat; Date(): Returnează data calendaristică; Day(data_calendaristică): returnează numărul zilei din luna; Exp(expresie_numerică): returnează valoarea constantei e ridicată la o puter e (expresie numerică sau număr); Log(expresie_numerică): returnează logaritmul natural dintr-un număr sau dintr-o expresie numerică; InStr ([intStart, ]strŞir, strCaută[, intCompară]): verifică dacă un caracter sau un şir( strCaută) se găseşte în interiorul altui şir (strŞir). În caz afirmativ funcţia returnează poziţia primului caracter căutat în şirul strŞir; 117
IsDate(expresie): returnează valoarea adevărat (TRUE) dacă expresia dintre paranteze este compatibilă cu o dată calendaristică; IsEmpty(expresie): returnează valoarea adevărat (TRUE) dacă expresia dintre paranteze nu conţine o valoare. Null este considerat valoare; IsNumeric(expresie): returnează valoarea adevărat (TRUE) dacă expresia dintre paranteze poate fi evaluată ca număr; IsObject(expresie): returnează valoarea adevărat (TRUE) dacă identificatorul dintre paranteze este de tip obiect; IsError(expresie): returnează valoarea adevărat (TRUE) dacă expresia dintre paranteze conţine o eroare; Lcase(şir_de_caractere): transformă literele mari dintr-un şir în litere mici; Left(strŞir,intNrCaractere): extrage dintr-un şir de caractere primele intNrCaractere din partea stângă a şirului; Len(şir_caractere/variabilă): returnează numărul de caractere ale şirului de caractere specificat sau numărul de octeţi necesari pentru a stoca conţinutul unei variabile; Mid((şir_caractere, poziţie_start[, lungimea])): extrage un şir de caractere dintr-un alt şir de caractere; Month(data_calendaristică): returnează numărul lunii din an; Right(strŞir,intNrCaractere): extrage dintr-un şir de caractere primele intNrCaractere din partea dreaptă a şirului; Space(număr): returnează numărul de spaţii specificate; Str(expresie_numerică): converteşte rezultatul evaluării expresiei numerice dintre paranteze într-un şir de caractere; Ucase(şir_de_caractere): transformă literele mici dintr-un şir în litere mari; Val(şir_caractere): returnează conversia şirului de caractere specificat, într un număr; WeekDate(dată_calendaristică,prima_zi): returnează numărul zilei din saptămâna datei calendaristice; Year(dată_calendaristică): returnează anul. Pentru scrierea programelor fiecare limbaj dispune de implementarea aşaziselor structuri de control al execuţiei programelor. S -a demonstrat că orice algoritm, fie el cât de complex, poate fi scris cu doar 3 sau 4 astfel de structu ri. Principalele structuri de control implementate în Visual Basic for Aplication sunt:
118
6.7 Structura alternativă În Visual Basic for Aplication pentru implementarea structurii alternative se poate utiliza instrucţiunile IF şi SELECT CASE. Sintaxa variantei principale a instrucţiunii IF este următoarea: If condiţie Then [secvenţa instrucţiuni A] [Else [secvenţa instrucţiuni B ]] End If Condiţia unei structuri alternative poate fi o expresie numerică sau o expresie logică, care poate fi evaluată la adevărat (True) sau fals (False). Compilatorul evaluează condiţia şi dacă ea este "adevărată", se execută secvenţa instrucţiuni A. Dacă expresia este "falsă", instrucţiunea "If" are şi parte de "Else" atunci se execută secvenţa instrucţiuni B. Dacă condiţia este o valoare numerică atunci dacă este diferită de 0 este interpretată ca True iar valoarea 0 este interpretată ca False. Una şi numai una dintre cele două instrucţiuni se execută. Grafic, instrucţiunea este reprezentată astfel:
Da
Condiţie
Nu
secvenţa
secvenţa
instrucţiuni A
instrucţiuni A
Un exemplu de utilizare a acestei instrucţiuni, este următorul subprogram, care calculează soluţiile ecuaţiei de gradul al doilea, pentru care prezentăm schema logică şi codul sursă, scris în Visual Basic. '-----------------------Private Sub ec2(double a, _ double b, double c) Dim delta As Double Dim x As Double Dim x1 As Double Dim x2 As Double If a <> 0 Then delta = b ^ 2 - 4 * a * c If delta >= 0 Then x1=(-b+Sqr(delta))/(2*a) 119
x2=(-b-Sqr(delta))/(2*a) Sub ec2(a, b, c) MsgBox "x1 =" & x1 MsgBox "x2 =" & x2 Da Nu Else a≠0 MsgBox _ "ec. nu are solutii reale" Da 2 delta=b -4ac End If Else Da x=-c/b Nu delta>=0 If b <> 0 Then x = -c/b Scrie x MsgBox "x =" & x x 1 =(-b+ )/2a delta Else x 2 =(-b)/2a Nu există delta MsgBox_ soluţii "ecuatia n- are sens" reale Scrie x 1 , x 2 End If End If End Sub
Nu
b≠0
Ecuaţia nu are sens
Terminare
Schema logică a acestei probleme este redată alăturat codului.
6.8 Structura repetitivă Structura repetitivă, la modul general, presupune repetarea unei secvenţe de instrucţiuni de un anumit număr de ori, în funcţie de o condiţie. Fiecare limbaj implementează mai multe instrucţiuni pentru realizarea structurii repetitive. Structurile repetitive întâlnite pot fi: cu numărător, cu testarea condiţiei înaintea executării unui ciclu, cu testarea condiţiei după executarea unui ciclu.
6.8.1. numărător
Structura
repetitivă
cu
val c = val i Operaţii care
Cele mai multe limbaje folosesc pentru instrucţiunea repetitivă cu numărător o formă a instrucţiunii FOR. În Visual Basic aceasta este numită în mod obişnuit instrucţiunea FOR...NEXT.
se repetă
val c = val c + val p
val c <= val f
Sintaxa instrucţiunii FOR...NEXT este : 120
Da
For valc=vali To valf [Step valp] [secvenţă instrucţiuni] [Exit For] [secvenţă instrucţiuni] Next [valc ] Schema logică de reprezentare a acestei structuri este redată alăturat sintaxei . În descrierea sintaxei instrucţiunii avem : val i , val f - reprezintă valoarea iniţială, respectiv valoarea finală pentru o variabilă contor val c ; val p - reprezintă valoarea pasului de incrementare/decrementare pentru variabila contor, implicit are valoarea =1; val i , val f , val p - pot fi şi rezultatul evaluării unor expresii. La întâlnirea acestei structuri, se repetă secvenţa de instrucţiuni de un număr de ori, plecându-se de la valoarea iniţială a variabilei contor, până la valoarea finală a acesteia, incrementându-se întotdeauna variabila contor cu val p . Dacă partea de [Step ...] lipseşte, compilatorul consideră val p egal +1. Pentru ieşirea forţată din structura repetitivă se poate uza de partea Exit For a instrucţiunii care de obicei trebuie intercalată într -o structură alternativă. Prin execuţia rândului Exit For, controlul instrucţiunii este transferat la prima instrucţiune după Next. Redăm mai jos, codul sursă pentru algoritmul deordonare al unui vector cu n elemente.
121
6.8.2. Structurile repetitive de tip Do … Loop Caracteristic limbajului Visual Basic este existenţa aşanumitei instrucţiuni DO...LOOP. Această instrucţiune repetă un bloc de instrucţiuni cât timp o condiţie este adevărată sau până când o condiţie devine adevărată. Mai clar spus, această structură suportă mai multe forme, echivalente fie cu structura repetitivă cu condiţie anterioară fie cu structura repetitivă cu condiţie posterioară. Aceste structuri repetă un bloc de instrucţiuni în funcţie de valoarea de adevăr a unei condiţii. Structurile pot fi implementate prin mai multe forme, echivalente fie cu structura repetitivă cu condiţie anterioară fie cu structura repetitivă cu condiţie posterioară. Unele forme se repetă atâta timp cât condiţia este adevărată, altele, din contră, un timp atât cât îi e necesar condiţiei să devină adevărată. Limbajul Visual Basic, prin instrucţiunea Do...Loop are implemenate toate aceste 4 posibilităţi. Sintaxa poate fi redată astfel: DO [{While | Until} condiţie] [instrucţiuni] [Exit Do] [instrucţiuni] Loop sau este valabilă şi sintaxa în forma următoare: Do [instrucţiuni] [Exit Do] [instrucţiuni] Loop [{While|Until} condiţie] Condiţia este opţională. Ea poate fi o expresie numerică sau o expresie logică, care poate fi evaluată la adevărat (True) sau fals (False). Compilatorul evaluează condiţia şi în funcţie de rezultatul obţinut, mai execută sau nu instrucţiunile din corpul structurii. Dacă condiţia este o valoare numerică, atunci dacă este diferită de 0 este interpretată ca True iar valoarea 0 este interpretată ca False. După evaluarea condiţiei, controlul execuţiei va fi transmis la una şi numai una dintre cele două ramuri cu cu ieşire din ea. Atunci când se foloseşte While ciclarea continuă dacă condiţia este adevărată, în schimb când se foloseşte Until ciclarea continuă dacă condiţia este falsă. În plus dacă condiţia este pe linia cu Do atunci structura este repetitivă cu condiţie anterioară, deci e posibil ca instrucţiunile din corpul structurii să nu fie executate niciodată.
122
În schimb dacă condiţia este pe o linie cu Loop atunci structura este repetitivă cu condiţie posterioară, deci instrucţiunile din corpul structurii vor fi executate ce l puţin odată, indiferent de valoarea de adevăr a condiţiei. Existenţa acestei instrucţiuni pluriforme este firească deoarece în algoritmi sunt poate, întotdeauna substituibile instrucţiunile repetitive Dacă este întâlnit rândul cu Exit Do, atunci controlul programului este transmis forţat în afara buclei, la prima instrucţiune care se găseşte după linia Loop. Redăm mai jos, schema logică şi codul sursă pentru algoritmul de calcul al sumei primelor n numere naturale, folosind cele 4 variante ale instruc ţiunii. i) Cazul Do While cu condiţie anterioară: '-----------------------Function sum(n As Integer)_ Function Sum (n) As Integer sum = 0 Dim i As Integer sum = 0 i=1 i=1 Da Do While i <= n i <= n sum = sum + i sum = sum + i Return i=i+1 Loop i=i+1 End Function
Function Sum (n) sum = 0 i=1 i>n Return
Nu
sum = sum + i i=i+1
ii) Cazul Do Until cu condiţie anterioară: '-----------------------Function sum(n As Integer)_ As Integer Dim i As Integer sum = 0 i=1 Do Until i > n sum = sum + i i=i+1 Loop End Function
iii) Cazul Do While cu condiţie posterioară: '-----------------------123
Function sum(n As Integer)_ As Integer Dim i As Integer sum = 0 i=1 Do sum = sum + i i=i+1 Loop While i <= n End Function Function Sum (n) sum = 0 i=1 sum = sum + i i=i+1 i>n Ieşire
Nu
Function Sum (n) sum = 0 i=1 sum = sum + i i=i+1 i <= n
Da
Ieşire
iv) Cazul Do Until cu condiţie posterioară: '-----------------------Function sum(n As Integer)_ As Integer Dim i As Integer sum = 0 i=1 Do sum = sum + i i=i+1 Loop Until i > n End Function
6.8.3. Structura repetitivă „pentru fiecare” Visual Basic mai are o instrucţiune specifică, derivată din instrucţiunea FOR...NEXT numită instrucţiunea For...Each...Next. Această instrucţiune repetă un grup de instrucţiuni pentru fiecare element al unui masiv sau al unei colecţii de obiecte. Sintaxa este redată mai jos: For Each element In grup [instrucţiuni] [Exit For] [instrucţiuni] Next [element] Element poate fi o variabilă utilizată în instrucţiune pentru a itera toate elementele unui masiv sau a unei colecţii referit prin variabila grup. Pentru colecţii, element poate fi variabilă de tipul variant, un obiect generic de variabilă, sau un 124
obiect specific de variabilă. Pentru masive element poate fi doar de tipul variant. grup este un nume de colecţie sau masiv. Exemplul următor listează numele tuturor controalelor incluse în formularul Produse. Pentru a fi accesate controalele, formularul trebuie să fi e deschis. '-----------------------------------Sub Form() Dim f As Form Dim c As Control Set f = Forms("Produse") For Each c In f Debug.Print c.Name Next End Sub Redăm în figura de mai jos, rularea în mediul VBA alături de mediul Access cu formularul deschis.
6.9 Programarea recursivă Sintagma Programare recursivă se referă la apelarea unei proceduri sau funcţii de către ea însăşi. Orice algoritm recursiv se poate transforma într -unul iterativ. 125
Un exemplu de funcţie recursivă, scris în VBA, cal culează suma primelor n numere naturale: „------------------------Function sum_rec(n As Integer) If n > 0 Then sum_rec = n+sum_rec(n –1) End If End Function Apelul se poate face chiar din “Debug Window” prin linia: sum_rec(100). Pentru a nu se apela la infinit, în subprogramele recursive trebuie plasat un bloc de instrucţiuni care să conţină o secvenţă de oprire, oarecum similară algoritmilor iterativi. În programare, recursivitatea este inspirată din recursivitatea sau recurenţa din matematică. Dacă efectiv subprogramul se apelează direct pe el însuşi atunci avem recursivitate directă, pe când dacă un subprogram SP1 apelează un altul SP2 care apelează la rândul lui pe SP1 atunci avem recursivitate indirectă. Prin aceasta se ajunge la realizarea repetabilităţii unor blocuri de instrucţiuni. În general însă recursivitatea nu face economie de memorie, deoarece trebuie menţinută o stivă cu valorile de prelucrat, putîndu-se supraîncărca stiva şi memoria. În schimb pot subprogramele recursive sunt lizibile şi uşor de depanat.
6.10 Obiecte ACCESS 2000 O baza de date Access este formată dintr-o mulţime de obiecte, care pot fi accesate din VBA. Fiecare obiect are o serie de proprietăţi şi metode ataşate. Reamintim că termenul de proprietate se referă la o dată încapsulată într-un obiect, iar termenul de metodă se referă la un subprogram încapsulat într -un obiect. Invocarea unei metode sau proprietăţi a unui obiect se face prin prefixarea acesteia cu numele obiectului astfel: nume_obiect.nume_metodă; nume_obiect.nume_proprietate. Specificarea unui obiect aparţinând unei colecţii se poate face conform uneia din următoarele variante: 1. nume_obiect_colecţie![nume_obiect] – este nevoie de paranteze drepte numai dacă numele obiectului conţine spatii, 2. nume_obiect_colecţie("nume_obiect"),
126
3. nume_obiect_colecţie(index_obiect) – număr care indică poziţia în colecţie a unui obiect. Aceste numere de ordine încep de la zero. Invocarea formularului sau raportului curent se poate face prin apelativul Me. Printre numeroasele obiectele care pot fi utilizate de programatori sunt şi obiectele Applicaton şi Form. Obiectul Application referă aplicaţia Microsoft Access 2000. Principalele metode ale obiectului Application sunt: CurrentDb returnează un obiect baza de date (DataBase) care refera baza de date curentă; CurrentUser returnează utilizatorul curent, conectat la baza de date curent ă; SysCmd se poate utiliza în principal pentru a afişa un text în bara de stare (Status Bar) sau pentru gestionarea unui Progress Bar; SetOption stabileşte valori pentru parametrii care se găsesc în meniul Tools, opţiunea Options:; obiect_Application.SetOption nume_opţiune, setări; GetOption returnează valorile unor parametri care se găsesc în meniul Tools, opţiunea Options:; obiect_Application.GetOption(nume_opţiune). Principalele proprietăţi ale obiectului Application sunt: DoCmd apelează obiectul DoCmd, descris în paragraful precedent; Forms permite accesul la colecţia de formulare deschise dintr -o baza de date; Reports permite accesul la colecţia de rapoarte deschise dintr-o baza de date; Screen permite accesul la obiectul Screen (va fi descris în paragrafele următoare). Obiectul Form referă un obiect de tip formular. Toate formularele deschise din baza de date se regăsesc în colecţia Forms, fiind identificate printr-un număr, alocat în ordinea deschiderii acestora. Primul formular deschis are numărul de ordine zero. Pentru a afla numărul de formulare deschise la un moment dat, trebuie citită proprietatea Count a colecţiei Forms. Metodele obiectului Form sunt: Repaint redesenează pe ecran formularul pentru care a fost apelată această metodă; Requery reactualizează înregistrările din formular (dacă formularul are ca sursă o interogare se reface aceasta); 127
SetFocus se cedează controlul formularului specificat împreună cu metoda; Undo - toate modificările făcute datelor afişate în formular se pierd. Dintre proprietăţile acestui obiect amintim: OpenArgs - şir de caractere care poate fi transmis ca parametru la deschiderea unui formular; RecordSource - numele unei tabele, interogări sau o fraza SQL ce are ca rezultat un set de înregistrări de care este ataşat formularul; RecordSelectors stabileşte apariţia marcatorului de înregistrare pe formular (TRUE) sau dezactivarea acestuia (FALSE); DataEntry - formularul este deschis numai pentru introducerea de înregistrări noi; AutoCenter - formularul este afişat pe centrul ecranului; AutoResize - formularul se redimensionează pe ecran automat, astfel încât sa se vadă tot conţinutul său; Caption - şir de caractere afişat în bara de titlu a ferestrei formularului; ControlBox setează activarea (TRUE) sau dezactivarea (FALSE) butoanelor de control ale ferestrei formularului; NavigationButtons - setează activarea (TRUE) sau dezactivarea (FALSE) controlului pentru deplasarea între înregistrările unui formular; Toolbar - indică bara de instrumente (Toolbar) afişată o dată cu formularul; Controls - reprezintă colecţia de controale de pe un formular.
6.11 Programarea dirijată de evenimente în ACCESS 2000 Prin generalizarea utilizării interfeţelor user friend, puternic interactive, se poate vorbi de programare dirijată de evenimente în Access în contextul formularelor, rapoartelor şi controalelor de pe un formular/raport sau secţiunile acestora. Exemple de evenimente în Access 2000 pot fi: deschiderea unui formular/raport, închiderea unui formular/raport, scrierea unei înregistrări într -o tabela, ştergerea unei înregistrări dintr-o tabela, dublu clic pe mouse, activarea unui buton de comanda, o eroare etc. Pentru obiecte, pentru toate evenimentele din Access, sistemul prevede proceduri sau funcţii. Ele pot fi particularizate de către programatorii de ineterfeţe prin inserare de cod. Astfel de exemplu, la evenimentul de deschidere a unui formular se poate ataşa o procedură/funcţie care sa testeze dacă un anumit utilizator are drepturi de utilizare a acestuia sau înainte de salvarea unei înregistrări într-o tabelă se poate ataşa o procedura care sa verifice respectarea anumitor corelaţii dintre date etc. 128
Pentru a particulariza o procedură a unui eveniment legat de un obiect trebuie parcurse următoarele etape: se afişază Foaia de proprietăţi; se selectează secţiunea Event; se selectează evenimentul la care se doreşte ataşarea procedurii; click pe butonul … pentru editarea procedurii respective. Sistemul deschide în editorul VBA o procedură de tip Sub sau Function cu numele compus din numele obiectului legat de numele evenimentului prin semnul _ . Pentru un obiect formular (Form) principalele evenimente sunt: OnClose : se produce la închiderea şi ştergerea de pe ecran a formularului, OnLoad: se produce la deschiderea formularului, în momentul în care o înregistrare din tabela sau interogarea ataşata formularului (în proprietatea Record Source) este afişată pe ecran, OnCurrent: se declanşează atunci când o înregistrare din tabela sau interogarea ataşată formularului devine înregistrare curentă. Pentru controale dintre evenimentele caracteristice acestora se pot enumera: OnClick:se declanşează în momentul în care se activează butonul mouse ului, OnDblClick: se declanşează în momentul în care se activează de două ori butonul mouse-ului (la dublu clic), OnMouseMove: se declanşează în momentul în care utilizatorul mută mouse ul pe deasupra controlului unde se tratează acest eveniment, OnChange: se declanşează atunci când conţinutul unui control casetă de text (Text Box) sau listă derulantă (Combo Box) se modifică.
Exerciţiu: Redăm un exemplu în care, într-un formular sunt plasate două dreptunghiuri: casetă1 şi casetă2. Pentru început colorăm fondul pentru casetă1 cu rosu, iar pentru casetă2 cu albastru. Particularizăm procedurile de evenimente astfel: ► la clic pe casetă1 formularul să devină roşu; ► la clic pe casetă2 formularul să devină albastru; ► la clic pe formular, acesta să devină gri;
129
► la dublu-clic pe casetă1, caseta să devină lila.
O altă grupare a principalelor evenimente ar putea fi redată in tabelul de mai jos: Tip de eveniment
Nume
Apare când Utilizatorul
de fereastră Close, Load, Open, Resize, Unload de focalizare
Activate,
Deactivate,
Enter,
realizează
o
acţiune
cu
formularul sau raportul Exit, Obiectul primeşte sau pierde focalizarea
GotFocus, LostFocus
sau devine activ respectiv inactiv
AfterDelConfirm,AfterInsert, AfterUpdate,BeforeDelConfirm, de date
BeforeInsert,BeforeUpdate, Change,Current,Delete,
NotInList,
Se
fac
modificări
ale
datelor
sau
înregistrărilor
Update de mouse
Click,
DblClick,
MouseDown,
MouseMove, MouseUp
Apare o acţiune cu mouse-ul Utilizatorul
de tastatură KeyDown, KeyPressed, KeyUp
apasă
taste
sau
foloseşte
funcţia SendKeys pentru transmiterea unei acţiuni
de tipărire de ceas
Un raport este tipărit sau este formatat în
Format, Print, Retreat
vederea tipăririi Creat de Access la intervale de timp
Timer
definite de utilizator
130
de eroare
Apare odată cu o eroare şi dă posibilitatea
Error
utilizatorului să o trateze
Atunci când utilizatorul derulează o operaţie, Access -ul lansează o secvenţă de evenimente. Aşa se întâmplă pentru diversele operaţii ca re pot fi executate de operatori. Programatorii pot să modifice sau nu aceste evenimente, VBA le oferă ca potenţial de particularizare. În tabelul următor redăm succesiunile de evenimente ocazionate de diferite operaţii: Operaţie Succesiune de evenimente Deschiderea unui formular cu controale active
Open, Load, Resize, Activate, Current
Deschiderea unui formular fără controale active
Open,
Load,
Resize,
Activate,
GotFocus,
Current Închiderea unui formular cu controale active
Unload, Deactivate, Close
Închiderea unui formular fără controale active
Unload, LostFocus, Deactivate, Close
Intrarea într-un control
Enter, GotFocus
Ieşirea dintr-un control
Exit, LostFocus
Cumutarea între două formulare F1 şi F2 cu Deactivate(F1), Activate(F2) controale active Cumutarea între două formulare F1 şi F2 fără LostFocus(F1), Deactivate(F1), GotFocus(F2), controale active Modificarea
unui
Activate(F2) text
într-o
casetă
text sau KeyDown, KeyPressed, Change, KeyUp
combinată, pentru fiecare caracter introdus: Actualizarea datelor într-un control sau înregistrare KeyDown,
KeyPressed,
Change,
KeyUp,
BeforeUpdate, AfterUpdate, Exit Înserarea unei înregistrări, la intrarea în primul KeyDown, KeyPressed, BeforeInsert, Change, control
KeyUp
Înserarea unei înregistrări, la ieşirea din ultimul BeforeUpdate, AfterUpdate, AfterInsert control Stergerea uneiÎnregistrări
Delete, Current, BeforeDelConfirm şi dacă se apasă Yes se mai face şi AfterDelConfirm
6.12 Accesul cu programele la obiectele bazei de date Obiectele bazelor de date pot fi accesate cu ajutorul extensiilor API ale limbajelor de programare. În continuare vom prezenta cateva aspecte ale posibilităţilor prin care se rezolvă această problemă cu VBA. 131
Deja am dat câteva exemple în acest sens în subcapitolele ante rioare. Obiectele bazelor de date Access sunt structurate în ierarhii: ADODB (ActiveX Data Objects), ADOX (Microsoft ADO Extensions for DLL and Security) şi JRO (Microsoft Jet and replication Objects). Pentru exploatarea datelor se folosesc obiectele ADODB iar pentru definiţia datelor se folosesc obiectele ADOX. ADODB este un model ierarhic de obiecte, Catalog redat în figura alăturată. Tables Table Casetele gri reprezintă obiecte, iar Columns Column Properties Propert casetele albe colecţii de obiecte. y Indexes Index Specificarea unui obiect aparţinând unei Columns Column colecţii se poate face conform uneia dintre Properties Propert următoarele variante: Keys Key y ► nume_colecţie![nume_obiect] –fiind nevoie Columns Column Groups Group Properties Propert de paranteze drepte numai dacă numele y Users User obiectului conţine spaţii; User Users ► nume_colecţie("nume_obiect"); Groups Group ► nume_colecţie(index_obiect) – index care Procedure Procedur s e Commmand indică poziţia în colecţie a unui obiect. Views View Aceste numere de ordine încep de la zero. Commmand ADOX este o colecţie de obiecte produse de Microsoft pentru definirea şi crearea structurilor bazelor de date şi a securităţii acestora, redată în figură. Specificarea unui obiect aparţinând unei colecţii se poate face în mod similar specificării obiectelor în cazul ierarhiei ADODB. Cel mai important obiect din ADODB este obiectul Connection care defineşte o sesiune de lucru pentru un utilizator al unei date particulare. Cum am mai spus, se pot folosi în principal, informaţii din baze de date Access, SQL Server sau MSDE. Pentru a ne conecta la baza de date, Data Source=C: \My Documents\bdNelu.accdb, trebuie scris următorul cod: '--------------------------------------Public Sub conexiune() Dim con As ADODB.Connection ' realizează o conectare con.ConnectionString = "Provider=Microsoft.Access.OLEDB.10.0;" _ & "Data Provider=Microsoft.ACE.OLEDB.12.0"; & _ "Data Source=C:\My Documents\bdNelu.accdb " con.Open MsgBox con.ConnectionString set con= Nothing End Sub 132
Deci trebuie declarată o variabilă pentru obiectul conexiune. Apoi trebuie precizat conţinutul iniţial al şirului conexiunii, care conţine valorile pentru proprietăţile conexiunii. Pentru a realiza conexiunea sunt obligatorii specificarea proprietăţii Provider, Data Source şi User. Însă de cele mai multe ori se lucrează cu datele din baza de date curentă. În consecinţă codul se simplifică astfel: '--------------------------------------Public Sub conexiune() Dim con As ADODB.Connection ' realizează o conectare Set con = CurrentProject.Connection MsgBox con.ConnectionString set con= Nothing End Sub Se observă că nici măcar nu trebuie dată comanda Open pentru conexiunile la datele proiectului curent. Până aici s-a prezentat doar prima chestiune care trebuie rezolvată în lucrul cu datele. Obiectul care conţine informaţiile care vor fi procesate de rutine este mulţimea de înregistrări (în limba engleză: RecordSet). Pentru iniţializarea lucrului cu o mulţime de înregistrări se utilizează următoarea secvenţă de instrucţiuni: Dim mî as RecordSet set mî = New ADODB.RecordSet mî.Open Sursă, Conexiune, TipCursor, TipBlocare, Opţiuni "mî" este numele variabilei prin care se face referire la mulţimea de î nregistrări, iar ultima linie, bineînţeles că este prezentată ca ca sintaxă pe care o vom explica. Parametrul "sursă" poate fi numele unui tabel sau numele unei interogări, comenzi, intucţiuni SQL sau procedură ce are rezultat o mulţime de înregistrări. Despre "conexiune" am scris puţin înainte. Parametrul "tipcursor" poate fi specificat cu următoarele constante predefinite: adOpenDynamic, adOpenKeyset, adOpenStatic sau adOpenForwardonly. Diferenţele între aceste tipuri de cursoare apar de la modul cum e tr atată înregistrarea referită la un moment dat, deplasarea prin înregistrări sau posibilitatea de a se seziza modificările făcute de alţi utilizatori. Numele lor sugerează oarecum despre ce e vorba. 133
Parametru "tipblocare" se referă la modul de tratare a î nregistrărilor pe timpul editării lor, posibilitatea de acces la ele a altor utilizatori. Constantele predefinite care stabilesc acest parametru sunt: adLockReadOnly, adLockPessimistic, adLockOptimistic şi adLockBatchOptimistic. Implicit ultimii doi parametrii sunt daţi de constantele: adOpenForwardonly şi respectiv adLockReadOnly. Pentru parametrul opţiuni se pot folosi următoarele constante VB: adCmdText, adCmdTable, adCmdTableDirect, adCmdStoredProc, adCmdUnknown şi adCmdFile care precizează modul cum este evaluat parametrul "sursă". Acestea fiind precizate putem să trecem la prezentarea unor exemple mai edificatoare. Pentru trecerea de la o înregistrare la alta într-o mulţime de înregistrări (navigare) se folosesc metodele Move, MoveNext, MovePrevious, MoveFirst şi MoveLast. '--------------------------------------Public Sub Navigare(tabel as string) Dim rs As ADODB.Recordset Dim con As ADODB.Connection Set con = CurrentProject.Connection Set rs = New ADODB.Recordset with rs .CursorType = adOpenStatic .CursorLocation = adUseClient .Open tabel, con, , , adCmdTable If .RecordCount > 0 Then .MoveFirst Do For i = 0 To .Fields.Count - 1 Debug.Print .Fields(i).Value; Next Debug.Print .MoveNext Loop Until .EOF End If End With rs.close con.close set rs=nothing set con=nothing 134
End Sub Acest exemplu afişează valorile câmpurilor înregistrărilor unui tabel (dacă există câmpuri de tip obiect OLE rezultatul e imprevizibil). Apelul poate fi făcut din fereastra Immediate, acolo unde va afişa: S-a arătat doar navigarea printre înregistrări, un proces ReadOnly. Pentru a se putea interveni cu actualizări trebuie să se facă apel la metodele .AddNew, .Update sau .Delete. Actualizările practic sunt făcute după o căutare prealabilă a articolului în cauză. Fie un tabel "persoane" cu campurile marca şi nume. Scriem o procedură care va căuta articolul cu marca 50. Dacă tabelul este indexat cătarea se poate face cu metoda .Seek altfel cu metoda .Find. O vom folosi pe aceasta din urmă, dar atenţie este mai lentă şi folosirea ei repetată încetineşte lucrul. Metoda .Find are următoarea sintaxă: Recordset.Find condiţie, articolesarite, sens, articolstart Parametrul "condiţie" e evident. "articolesarite" specifică un număr de articole din vecinătatea articolului de unde se începe căutarea care nu sunt verificate (implicit 0). "sens" poate fi adSearchForward sau adSearchBackWard (implicit în faţă). Început poate fi primul articol, ultimul sau articolul curent (implicit). Procedura este următoarea: '--------------------------------------Public Sub Modificare_Stergere() Dim rs As ADODB.Recordset Dim con As ADODB.Connection dim sirdate as String Set con = CurrentProject.Connection Set rs = New ADODB.Recordset with rs 135
.CursorType = adOpenStatic .CursorLocation = adUseClient .Open "persoane", con, adOpenStatic, _ adLockOptimistic, adCmdTable .Find "marca=50" If Not .Eof Then sirdate="" For i = 0 To .Fields.Count - 1 sirdate =sirdate & .Fields(i).Value Next Dim rasp rasp = InputBox("articolul cautat este" + vbCr _ +sirdate + vbCr + "m- modificare, d -stergere", "m") If lCase(rasp)="d" Then .Delete Else .Fields("Marca")=InputBox( _ "Altă valoare pentru Marca") .Fields("Nume")=InputBox( _ "Altă valoare pentru Nume") .Update End If End If End With rs.close con.close set rs=nothing set con=nothing End Sub Ce operaţii se realizează? După iniţializări se caută articolul cu marca = 50 si se afişează conţinutul lui. Apoi operatorul e întrebat dacă doreşte să modifice sau să şteargă articolul. Pentru ştergere se utilizează metoda .Delete. Pentru modi ficare se citeşte valori noi pentru cele două c\mpuri şi apoi se utilizează metoda .Update. Pentru adăugarea unei noi înregistrări se poate folosi procedura: '--------------------------------------Public Sub adăugare() Dim rs As ADODB.Recordset Dim con As ADODB.Connection 136
Set con = CurrentProject.Connection Set rs = New ADODB.Recordset with rs .CursorType = adOpenStatic .CursorLocation = adUseClient .Open "persoane", con, adOpenStatic, _ adLockOptimistic, adCmdTable .AddNew .Fields("Marca")=InputBox( _ "Altă valoare pentru Marca") .Fields("Nume")=InputBox( _ "Altă valoare pentru Nume") .Update End With rs.close con.close set rs=nothing set con=nothing End Sub Reamintim că la deschiderea unei mulţimi de înregistrări ca sursă se poate lua şi o interogare de selecţie ca şi comandă sau o interogare de selecţie salvată, s.a.m.d. Din proceduri VBA se pot lansa şi interogări de ştergere, actualizare sau adăgare cu ajorul comenzii .Execute. Până acum, în această secţiune am prezentat exemple de folosire a obiectelor ADODB. În continuare vom analiza folosirea obiectelor ADOX pentru definirea şi crearea structurilor de baze de date. Procedura următoare crează un tabel cu numele "colaboratori" ce are două câmpuri "Marca" şi "Nume" în baza de date: C:\doros\Ang.accdb Sub CreareTabelPrinVBA() On Error GoTo CreateTableError Dim tbl As New Table Dim cat As New ADOX.Catalog ' Deschide catalogul bazei de date cat.ActiveConnection = "Provider=Microsoft.Access.OLEDB.10.0;" _ & "Data Source=C:\doros\Ang.accdb;" _ & "Data Provider=Microsoft.ACE.OLEDB.12.0" tbl.Name = "colaboratori" tbl.Columns.Append "Marca", adInteger 137
tbl.Columns.Append "Nume", adVarWChar, 50 cat.Tables.Append tbl Debug.Print "Table 'MyTable' is added." 'eliberarea memoriei obiectelor Set cat.ActiveConnection = Nothing Set cat = Nothing Set tbl = Nothing Exit Sub CreateTableError: Set cat = Nothing Set tbl = Nothing If Err <> 0 Then MsgBox Err.Source & "-->" & Err.Description, , "Error" End If End Sub Odată create tabelele, structura lor poate fi modificată. Se pot adăuga noi coloane, se pot şterge dintre cele existente se pot modifica dintre atributele lor, etc. Principalele tipuri de coloane care trebuie precizate la instrucţiunea .Column.Append sunt: Constant Codul Description adBinary
128
Binary
adBoolean
11
Boolean
adUnsignedTinyInt
17
Byte
adCurrency
6
Currency
adDate
7
Date/Time
adDouble
5
Double
adGUID
72
GUID
adInteger
3
Long
adLongVarBinary
205
Long Binary (OLE Object)
adLongVarChar
201
Memo
adNumeric
131
Numeric
adSmallInt
2
Integer
adSingle
4
Single
adVarWChar
202
Text
ADOX conţine obiecte şi pentru creare sau modificare de indecşi, creare sau modificare de interogări, creare de indecşi. În fine, următorul exemplu modifică comanda SQL a unei interogări stocate în baza de date: 138
'--------------------------------------Public Sub ADOXSchimbSQL() Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command ' Deschiderea catalogului de tabele ale bazei de date cat.ActiveConnection = CurrentProject.Connection ' Accesarea interogării Set cmd = cat.Procedures(1).Command ' Modificarea comenzii cmd.CommandText = "SELECT * FROM ANG;" ' Salcvarea noii comenzi Set cat.Procedures(1).Command = cmd Set cat = Nothing Set cmd = Nothing End Sub
139
CAPITOLUL VII. ÎNDRUMAR PENTRU REALIZAREA
PROIECTULUI LA DISCIPLINA „BAZE DE DATE” 7.1. Cerinţe minimale Studenţii anului II, de la specializările „Finanţe şi bănci‖ şi „Management‖, trebuie să întocmească, la finalul semestrului I, anul II de studii, la disciplina „Baze de date‖, un proiect prin care să demonstreze posibilitatea folosirii produsului MS ACCESS în gestionarea bazelor de date. Pentru disciplina „Baze de date‖, nota finală obţinută de un student este compusă din: ► 1 punct din oficiu; ► Max. 3 puncte pentru activitatea din timpul semestrului la această disciplină; ► Max. 3 puncte pentru susţinerea proiectului; ► Max. 3 puncte pentru susţinerea examenului final. Dacă un student nu susţine corect acest proiect, va fi declarat restanţier la această disciplină. Proiectul va conţine o bază de date tip MS Access compusă din următoarele obiecte: ► Cel puţin 2 tabele între care să existe relaţii; ► Formulare de introducere de date în aceste tabele; ► Diverse interogări bazate pe aceste tabele; ► Rapoarte pentru listarea informaţiilor conţinute în tabele. Baza de date va fi salvată pe un CD sau FD şi obiectele precizate în paragraful anterior trebuie să fie funcţionale. Studentul va prezenta împreună cu baza de date o documentaţie a conţinutului, listată şi îndosariată (cca 10-15 pagini), structurată pe următoarele capitole: ► Motivaţia lucrării; ► Descrierea datelor şi variabilelor utilizate; ► Descrierea machetelor pentru formularele de introducere de date; ► Descrierea machetelor pentru rapoartele de listare a informaţiilor conţinute în tabele; ► Listingul şi explicaţia diverselor interogări sau module utilizate; ► Concluzii trase după realizarea proiectului şi propuneri de perfecţionare.
140
Cerinţele prezentate mai sus pentru baza de date şi pentru documentaţia aferentă sunt obligatorii şi minimale.
7.2. Baza de date a. Tabelele Tabelele cuprinse în baza de date trebuie să se refere la un domeniu concret şi să cuprindă date necesare pentru rezolvarea unei probleme. Redăm, în continuare, câteva exemple de posibile teme: ► gestiune depozit de materiale, întocmire balanţă şi fişe; ► evidenţă încasări de la clienţi, întocmire balanţă şi fişe; ► evidenţa plăţilor către furnizori, întocmire balanţă şi fişe; ► evidenţa numerarului, întocmire registru de casă; ► evidenţa contului curent, reconstituire extras de cont; ► gestiune chioşc de închiriat filme; ► evidenţa cheltuielilor de scară; ► evidenţa bonurilor de materiale; ► evidenţa invitaţiilor la un teatru; ► evidenţa contribuţiei artiştilor la spectacole; ► evidenţa vânzărilor biletelor de avion; ► evidenţa vanzărilor biletelor de tren; ► evidenţa articolelor contabile şi întocmirea balanţei şi fişelor ; ► evidenţa maşinilor dintr-un parc auto; ► evidenţa mijloacelor fixe; ► evidenţa obiectelor de inventar; ► evidenţă de oferte şi cereri la o agenţie imobiliară; ► registru de intrări şi ieşiri la un secretariat; ► evidenţa enoriaşilor unei parohii; ► evidenţa notelor studenţilor; ► registru de înmatriculare; ► fişe de bibliotecă; ► ocuparea camerelor într-un hotel; ► ocuparea camerelor într-un cămin; ► evidenţa bolnavilor într-o clinică; ► evidenţa membrilor unui club
141
Tema problemei de rezolvat este aleasă de student, nu neapărat din listă şi se va aprecia originalitatea. Este recomandabil, ca studenţii să-şi aleagă teme cu care au avut tangenţă în experienţa lor profesională. Bazele de date vor fi relaţionale, normalizate măcar până la forma normală 3 şi vor conţine informaţii neredundante. Deci, este incorect ca să existe două tabele cu următoarea structură: Catalog Rulaj Cod Nume Preţ Cod Nume Data Cantitate TipOperatie deoarece al doilea tabel conţine informaţii redundante (câmpul Nume) şi nu respectă a doua regulă de normalizare care necesită ca fiecare coloană care nu este cheie să depindă complet de cheia primară, nu doar de o parte a cheii. (câmpul Nume depinde numai de câmpul Cod, nu de combinaţia Cod+Data, care constituie cheia primară). Nu este corect nici un tabel de forma: TaxeStudenţi Nr. Matricol Nume Rata1 Rata2 deoarece conţine o listă de câmpuri şi nu e în forma normală 1.
b. Interogările Prin obiectele create, studentul va face dovada că înţelege diversele tipuri de interogări: de extragere (SELECT cu toate clauzele), de inserare (INSERT), de modificare (UPDATE) şi de ştergere (DELETE). Cele mai importante interogări ale proiectului vor fi cele care se referă la datele din mai multe tabele, simultan.
c. Formularele Formularele create trebuie să conţină posibilităţi de introducere a tuturor datelor definite în 142
Rata2
tabele. Atunci când e necesar să se introducă date prin exploatarea unor relaţii dintre tabele, se va apela la tehnica modulelor Visual Basic, a interogărilor (de tip join) sau a subformularelor. Mai mult de jumătate din numărul formularelor din proiect, trebuie să nu fie create cu expertul. În formulare se vor plasa controale de următoare tipuri: ► Etichete; ► Casete text; ► Casete combo; ► Imagini, linii sau dreptunghiuri; ► Butoane de comandă. În figurile de mai sus sunt prezentate câteva exemple de formulare. Se va aprecia pozitiv, existenţa în proiect, a unui formular care monitorizează funcţionarea obiectelor proiectului.
d. Rapoartele Rapoartele trebuie să poată lista toate informaţiile din tabele, sub diferit e forme, în diferite moduri ordonare sau de grupare. Mai mult de jumătate din numărul rapoartelor din proiect, trebuie să nu fie create cu expertul. Atunci când e necesar listeze date prin exploatarea unor relaţii dintre tabele, se va apela la tehnica interogărilor (de tip join). În rapoarte se vor plasa controalele următoare: ► Etichete; ► Casete text; ► Imagini, linii sau dreptunghiuri. Cel puţin într-un raport, se vor plasa câmpuri care vor conţine valori rezultate ale funcţiilor de grupare. În figura de mai jos prezentăm un exemplu de raport:
143
7.3. Documentaţia Studentul va prezenta împreună cu baza de date o documentaţie a conţinutului, listată şi îndosariată (cca 10-15 pagini), structurată pe următoarele secţiuni: ► motivaţia lucrării; ► descrierea datelor şi variabilelor utilizate; ► descrierea machetelor pentru formularele de introducere de date; ► descrierea machetelor pentru rapoartele de listare a informaţiilor conţinute în tabele; ► listingul şi explicaţia diverselor interogări sau module utilizate; ► concluzii trase după realizarea proiectului şi propuneri de perfecţionare. Studenţii vor trebui să respecte întocmai structura acestei documentaţii, dar în redarea ei nu este obligatoriu să se recurgă la formulările stricte redate mai jos, ca model. În documentaţie nu vor fi redate elemente teoretice, ci vor fi prezentate succint elementele conţinute în proiect. Prima pagină a dosarului va avea următorul format: 144
ACADEMIA COMERCIALĂ SATU MARE
PROIECT LA DISCIPLINA BAZE DE DATE Folosirea MS ACCESS în gestionarea unei baze de date conţinutul bazei de date . Autor student: numele studentului, anul şi specializarea .
a. Motivaţia lucrării
- Satu Mare 2010
În această secţiune se pot prezenta elemente precum cele redate în modelul de mai jos: conţinutul bazei de date Acest proiect este destinat gestionării , menirea organizaţiei într-o organizaţie , pentru angajaţii săi de la numele compartimentului sau biroului compartimentul . Proiectul este realizat cu ajutorul aplicaţiei MS Access Office 2003. Pentru funcţionarea lui sunt necesare: numărul şi pregătirea angajaţilor, numărul de calculatoare, alte resurse de hard sau soft, etc
.
b. Descrierea datelor şi variabilelor utilizate În această secţiune vor fi prezentate sau explicate: ► toate câmpurile tabelelor, natura, tipul, dimensiunea şi restricţiile informaţiilor pe care le stochează; ► câmpurile calculate ale interogărilor; ► variabilele calculate ale controalelor din formulare sau rapoarte. De exemplu, pentru un tabel cu numele „Sahisti‖, cu informaţii despre sportivii legitimaţi, se va explica structura, după modelul următor: Structura de tabel pentru tabelul “sahisti” 145
Camp
Tip
Nul Explicaţii despre conţinut
NrCarnet
întreg lung
Da
Nume
text (50)
Numele sportivului, nu depăşeşte 50 caractere, cu litere mari, Da fără diacritice. Dacă sunt mai multe cuvinte, între ele se intercalează cratimă
text Prenume (70)
Toate prenumele pe care le are sportivul, nu depăşeşte 70 Da caractere, cu litere mari, fără diacritice. Dacă sunt mai multe cuvinte, între ele se intercalează cratimă
Numărul legitimaţiei are 6 cifre, e necesară o variabilă de tip Întreg lung, valori pozitive
Una dintre valorile ―M‖ pentru sexul masculin, sau respectiv, ―F‖ pentru sexul feminine
Sex
text (1) Da
Nascut
date
Titlu
Titlul sau categoria sportivă. Pentru sportivii de sex masculine sunt posibile valorile: ―NC‖ – neclasificat, ―III‖ – categoria a III-a, ―II‖ – categoria a II-a, ―I‖ – categoria a I-a, text (3) Da ―CM‖ – candidat de maestru, ―M‖ –maestru, ―FM‖ – maestru FIDE, ―MI‖ – maestru internaţional, ―GM‖ – mare maestru internaţional. Pentru sportive sunt posibile valorile corespunzătoare: ―WCM‖, ―WM‖, ―WFM‖, ―WMI‖, ―WGM‖.
Da Data neşterii sportivului, în format zz.ll.aaaa
CIV
întreg
Coeficientul valoric al sportivului. Poate lua valori între 401 şi 1400, când e calculat de federaţia naţională, sau valori Da între 1401 şi 2900, când e calculat de federaţia internaţională, Valorile pot fi stocate într-un număr întreg pozitiv (pe doi octeţi)
Poza
text (100)
Numele întreg (inclusive calea de directoare) a fişierului acre Da conţine imagine pozei sportivului. E recomandat să existe un camp cu lărgime considerabilă.
IdFide
întreg lung
Codul acordat de federaţia internaţională pentru sportiv. Are Da 7 cifre, e necesară o variabilă de tip Întreg lung, valori pozitive.
c. Descrierea machetelor pentru formularele de introducere de date sau a rapoartelor de ieşire În aceste secţiuni vor fi prezentate sau explicate formularele (sau rapoartele) folosite. În documentaţie formularele şi rapoartele vor fi prezentate distinct, în secţiuni separate. 146
Machetele corespunzătoare formularelor (sau rapoartele) vor fi captate cu ajutorul tastei „PrintScreen‖ şi inserate în documentaţie. Se vor prezenta toate controalele incluse în fiecare formular (sau raport). Se vor prezenta toate proprietăţile, metodele şi evenimentele la care s-a intervenit cu modificări, la nivel global pentru formular (sau raport), sau pentru fiecare control în parte. De exemplu: Primul formular este destinat, după cum are şi numele, completării tabelului CATALOG. Este un formular simplu, finisările care s -au făcut sunt: scoaterea butoanelor de navigare; introducerea unui combobox pentru selectarea articolelor după nume; introducerea a 4 butoane de comenzi pentru ştergere, salvare, restaurare şi adăugare articole.
d. Listingul şi explicaţia diverselor interogări sau module utilizate Pentru fiecare dintre interogările create se va explica ce realizează, se va afişa comanda SQL şi, în cazul interogărilor de selecţie, se va prezenta schiţat macheta rezultatului. De exemplu: Interogarea următoare listează numele şi salariul şoferilor din ta belul „Ang‖: Select NumeA, Sal from Ang where fnc=”SOFER” Extragere SOFERI numeA
sal
Popan
800
Matus
1100
Chivu
950 147
Dacă proiectul conţine module VisualBasic, sursele acestora vor fi listate şi explicate.
e. Concluzii trase după realizarea proiectului şi propuneri de perfecţionare În câteva fraze vor fi redate: limitele proiectului realizat; constrângerile pe care le suportă; posibilităţile de îmbunătăţire şi de extindere.
148
BIBLIOGRAFIE
Doros, I.. —Societatea informațională. Orașul virtual-Editura Gutenberg Arad-2009 Doros, I., Popei, V. —Dictionar pentru utilizatorii de calculatoare personale - Editura Dacia Cluj-Napoca-2007 Doros, I., Todor., L, Ardelean, A.E., Tulbure, C. —Informatica pentru utilizatorii de calculatoare personale - Editura Dacia Cluj-Napoca-2005 Lungu, I., Sabău, Gh. ,s.a.— Sisteme informatice. Analiză, proiectare şi implementare, Editura Economică, Bucureşti 2003 Muntean, D.V., Andreica, A., Todor, L.S., Doros, I. —Bazele informaticii. Aplicații- Editura Gutenberg Arad-2009 Năstase,P., Mihai,F.— Baze de date – Microsoft Access 2000 , Editura Teora, Bucureşti 2003 Sabău,G.,Muntean,M., Dardală,M.,Bologa,R., Bologa, A.R.— Baze de date, Editura MatrixRom, Bucureşti 2008 Velicanu, M.-Baze de date prin exemple- Editura ASE București -2007
*** — http://office.microsoft.com/ro-ro/access - pagină web a prodului Access
149
