Cap. 2 Sistemul de gestiune a bazelor de date Microsoft Access

2.1 Componentele principaleAccess este o componentă a pachetului de programe Microsoft Office, care

permite crearea şi gestionarea de baze de date de dimensiuni reduse, la care se pot conecta maximum 31 de utilizatori simultan. Pentru baze de date mari (de ordinul Gigabytes-ilor) se recomandă folosirea SGBD-urilor Oracle, SQL Server, Sybase.

Spre deosebire de alte sisteme, Access stochează toate componentele unei baze de date într-un singur fişier, cu extensia *.mdb. Componentele principale ale sistemului Access, numite şi obiecte (Objects) sunt următoarele (fig. 1):

Tabele (Tables) – structuri bidimensionale destinate păstrării datelor; Interogări (Queries) – instrumente de selectare şi de afişare a a datelor

din una sau mai multe tabele, pe bază de criterii; cu ajutorul acestora se pot realiza şi funcţii de creare tabele, adăugare, modificare şi ştergere înregistrări în tabele;

Formulare (Forms) – ferestre destinate introducerii datelor, afişării şi modificării lor; permit crearea interfeţei grafice cu utilizatorul şi afişarea pe ecran a graficelor;

Rapoarte (Reports) – situaţii obţinute pe bază de interogări sau tabele şi care pot fi afişate pe ecran sau la imprimantă;

Macro-comenzi (Macros) – secvenţe de acţiuni care se execută la iniţierea unei singure comenzi a utilizatorului;

Module (Modules) – subprograme scrise în Visual Basic Access (VBA), care pot implementa răspunsuri la evenimente-utilizator, funcţii de prelucrare şi validare a datelor, iniţializări ale bazei de date. O caracteristică importantă a sistemului Access o constituie proiectarea

asistată de „vrăjitori” (Wizards), care oferă asistenţă programatorului la crearea tabelelor, a interogărilor, a formularelor şi a rapoartelor. Aceste instrumente au rolul de a pune la dispoziţia proiectanţilor opţiuni din care pot selecta varianta optimă, de asemenea asigură asistenţă on-line (help) pentru documentarea în timpul proiectării.

Prin stabilirea de relaţii între tabele se asigură coerenţa şi consistenţa bazei de date, astfel încât ştergerile, adăugările, modificările la nivelul unei tabele să nu conducă la disfuncţionalităţi ale sistemului. Totuşi ştergerile de date, crearea şi distrugerea de tabele sunt operaţii care provoacă fragmentarea spaţiului de disc rezervat fişierului .mdb. Acces-ul are integrată comanda Tools->Database Utilities->Compact and Repair Database pentru compactarea periodică a bazei de date şi păstrarea performanţelor ei.

Crearea unei baze de date se poate face la deschiderea mediului, dacă se alege opţiunea Blank Access Database, fie ulterior, când mediul este deschis şi se activează opţiunea File->New Database. După deschiderea unei bazei de date sunt accesibile componentele ei, grupate pe tipuri de obiecte (Tables, Queries, Forms etc), aşa cum se observă în fig.1; aşezarea obiectelor pe desktop este caracteristică versiunii Access 2000.

Vor fi prezentate în continuare modalităţi de creare şi exemple de utilizare a obiectelor Access menţionate anterior.

2.2 Tabele (Tables)Sunt structuri de stocare a datelor constituite din linii, numite înregistrări şi

din coloane numite atribute sau câmpuri, cărora li se atribuie un nume, un tip de dată şi o dimensiune. Crearea şi modificarea structurii tabelelor, ca şi încărcarea datelor în tabele se face numai în cadrul opţiunii Objects->Tables.Access oferă mai multe modalităţi de creare a unui tabel: Datasheet View, Design View, Table Wizard, Import Table, Link Table (fig. 2)

Fig. 1 Componente principale ale sistemului Access

Tabele

Interogări

Rapoarte

Macro comenzi

Module

Formulare

În

modul Datasheet View se crează un tabel cu 20 de câmpuri, având denumirea Field1,..Field 20 şi 30 de înregistrări libere, pregătite pentru completare. În modul Table Wizard, Acces propune două categorii de tabele: Business şi Personal şi în cadrul lor mai multe tipuri de tabele, cu câmpuri predefinite, din care proiectantul să poată alege.Prin Import Table se pot prelua tabele din alte baze de date Access, FoxPro, Paradox sau se pot construi tabele şi prelua date din fişiere text, HTML sau Excel.În modul Link Table nu mai este necesară copierea tabelei în propria bază de date, ci se stabileşte o legătură cu aceasta, astfel încât se pot citi şi modifica datele (dacă există un acces autorizat).Modalitatea cea mai frecvent folosită pentru crearea tabelelor este Design View (fig. 3) şi ea va fi descrisă în continuare pas cu pas:

1. Tables->New->Design View;2. Apar două fereastre pe Desktop: fereastra de proiectare a tabelei (Table

Design), unde se stabilesc câmpurile prin numele (Field Name) şi tipul lor (Data Type) şi fereastra de proprietăţi ale câmpurilor (Field Properties);

3. Se tastează numele câmpului şi se alege unul dintre tipurile de date propuse de Access: Text – şir de caractere; Number – câmp de tip numeric; Date/Time – câmp de tip dată sau timp; Autoincrement – număr cu incrementare automată; Yes/No – câmp boolean cu două valori; Lookup Wizard – câmp care preia datele din alt tabel sau dintr-o listă de valori.

4. În funcţie de tipul de dată selectat, fereastra Field Properties va conţine alte informaţii în rubrica Field Size, pe care utilizatorul le poate modifica:

Fig. 2 Modalităţi de creare a tabelelor

Text: setat pe valoarea implicită 50, dar se pot reprezenta şiruri de maximum 255 de caractere; Number: se pot defini câmpuri de tip Byte (28 – val maximă 255), Integer (216 – valoare maximă + 32768), Long Integer este tipul implicit şi are valoari pe 32 biţi (de la –2.147.483.648 la 2.147.483.647 fără fracţii), Single – numere fracţionare pe 32 biţi cu precizie de 7 cifre ( de la –3.402823E38 la –1.401298E–45) pentru valori negative şi de la 1.401298E–45 la 3.402823E38 pentru valori pozitive.

Valorile numerice Double – numere fracţionare pe 8 bytes cu 15 cifre precizie.

5. Se pot completa şi alte rubrici ale ferestrei Field Properties, cum ar fi: Format: personalizează formatul de apariţie (modul de afişare) a valorilor în câmpul respectiv şi depinde de tipul de dată precizat în Data Type:

Tipuri de date

Fereastra Field

Properties

Butonul SaveButonul care activează

cheia primară

Fig. 3 Crearea tabelelor în modul Design View

Câmpuri de date – PK

- Date/Time - permite alegerea unui format al datei sau orei dintre cele propuse: General Date, Long Date, Short Date Long Time, Short Time

- Text poate conţine unul dintre caracterele speciale: @ - un caracter sau un spaţiu trebuie tastat în câmpul respectiv;& - nu este necesară tastarea unui caracter în câmpul respectiv;< - forţează toate caracterele tastate la litere mici; >- forţează toate caracterele tastate la litere mari

- Number specifică formate predefinite (Currency, Euro, Fixed, Standard, Percent, Scientific) sau proprii utilizatorului (informaţii despre modul de afişare a valorilor pozitive, negative, zero şi a numerelor fără valoare - Null);

Input Mask: proprietate care ajută utilizatorul la introducerea datelor în câmpul respectiv:

- 0 impune introducerea pe poziţia respectivă a unei cifre, fără semn;

- 9 impune acceptarea unei cifre sau unui spaţiu, fără semn;- # indică acceptarea unei cifre sau unui spaţiu, cu semn;- L solicită introducerea unei litere, obligatoriu;- ? indică introducerea unei litere, opţional;- A impune introducerea unei litere sau unei cifre, obligatoriu;- a impune introducerea unei litere sau cifre, opţional.

Caption: stabileşte titlul câmpului în modul de introducere date; Default Value: reprezintă valoarea iniţială (constantă, expresie sau funcţie) care apare în câmp la adăugarea unei noi înregistrări şi care poate fi modificată ulterior; Validation Rule: se stabileşte o regulă de validare, la nivel de înregistrare, a valorii introduse în acest câmp, prin funcţii sau expresii Access; Validation Text: se tastează un mesaj, care apare ca eroare în timpul introducerii datelor, dacă regula de validare nu este respectată; Indexed: se precizează dacă un câmp, altul decât cheia primară, este index în tabelă şi dacă se acceptă duplicarea valorilor lui; opţiunile posibile: No, Yes (Duplicate OK) şi Yes (No Duplicate);

6. Se stabileşte cheia primară formată din unul sau mai multe câmpuri;7. Se salvează structura tabelei cu opţiunea File->Save sau cu ajutorul

butonului Save şi se precizează numele tabelei.

Exemplu de folosire a proprietăţilor unui câmp: Câmpul Categ din tabela de mai jos este de tip Text, are lungimea maximă de 9 caractere şi are setate următoarele proprietăţi: Format: “>” însemnând că toate caracterele care ce se tastează sunt transformate în litere mari; Input Mask: LLL – pe primele trei poziţii se plasează obligatoriu litere

??? - pe următoarele trei poziţii se plasează opţional litereaaa - pe următoarele trei poziţii se plasează opţional litere sau spaţii

Validation Rule: primele litere să nu fie “ABC” Validation Text: dacă s-a tastat combinaţia de litere “ABC” apare mesajul de eroare “Ati tastat propria firma!” Indexed: No, deci câmpul nu constituie index suplimentar.

2.3

Relaţii între tabele (Relationships)Relaţiile se formează prin stabilirea unei legături între un câmp (o

combinaţie de câmpuri) dintr-un tabel şi câmpurile corespunzătoare din alt tabel.Legăturile între tabele sunt de trei tipuri:

1. relaţia unu la mai mulţi ( one-to-many ) – este cea mai frecvent utilizată şi se realizează între cheia primară a tabelei T1 şi un câmp similar, ca tip şi ca dimensiune din T2, numit şi cheie străină. Semnificaţia legăturii este că oricărei valori a câmpului cheie străina-C21 trebuie să-i corespundă o valoare a câmpului cheie cheie-C1. În timp ce în tabela T1 valoarea este unică, în tabela T2 ea se poate repeta de un număr infinit de ori.

Fig. 4 Exemplu de utilizare a proprietăţilor unui câmp

1

Tabela T1 Tabela T2C1 – Primary Key C2 – Primary Key

................ C21 – Foreign Key

................ ..........

2. relaţia mai mulţi la mai mulţi (many-to-many ) – se aplică la cazurile în care valorii unui câmp din prima tabelă îi corespund mai multe valori în a doua tabelă şi invers, unei valori a unui câmp din a doua tabelă îi corespund mai multe valori din prima tabelă. Exemplul clasic al relaţiei este între tabela Studenţi şi Cursuri: un student este înscris la mai multe cursuri, un curs este ţinut pentru mai mulţi studenţi. Rezolvarea problemei se face prin spargerea relaţiei many-to-many în

două relaţii one-to-many, cu ajutorul unei tabele de joncţiune (legătură) – T3. Cheile primare din T1 şi T2 vor constitui (vor face parte) cheia primară compusă a tabelei T3.

Tabela T1 Tabela T2C1 – Primary Key C2 – Primary Key

................

Tabela T3C31 Primary KeyC32 Primary Key

3. Relaţia unu-la-unu ( one-to-one) - are loc între două tabele care au aceeaşi cheie primară. Se defineşte prin intermediul ei o tabelă compusă din cele două tabele iniţiale. Relaţia este utilă în cazul structurilor mari, care au nevoie de mai mult de 255 de câmpuri (limita Access-ului pentru un singur tabel) sau pentru creşterea vitezei de căutare a datelor, dacă nu toate înregistrările din primul tabel au corespondent în al doilea tabel.

Crearea unei relaţii se face astfel (fig.5):a) Din meniul Tools->opţiunea Relationships sau activând butonul

Relationships se deschide fereastra Relationships;b) Se adaugă tabelele relaţionate din fereastra Show Tables;c) Se selectează câmpul PK din tabela principală şi se „trage” cu ajutorul

mouse-ului până la câmpul corespunzător din tabela asociată;d) Se deschide fereastra de stabilire a detaliilor relaţiei (Edit Relationships):

câmpurile corespunzătoare din tabele, butonul Join Type - tipul de legătură stabilit şi trei casete de validare:

Enforce Referential Integrity – impune integritatea referenţială

∞

∞∞

∞∞

11

Cascade Update Related Fields – actualizarea în cascadă a câmpurilor implicate în relaţie; Cascade Delete Related Records – ştergerea în cascadă a câmpurilor implicate în relaţie;

e) Se închide fereastra Relationships şi se salvează legăturile create.

Verificarea integrităţii referenţiale este realizată de sistemul Access la solicitarea proiectantului BD şi constă în:

Verificarea câmpurilor incluse în relaţie: să fie de acelaşi tip şi să aibă aceeaşi dimensiune;

Introducerea unei valori în câmpul cheie străină din tabela secundară se face numai dacă această valoare se regăseşte în câmpul cheie primară din tabela principală;

Nu se admit ştergeri sau actualizări ale unei valori a cheii primare atâta timp căt există înregistrări corespunzătoare în tabela secundară;

Pentru realizarea ştergerilor şi actualizărilor în cascadă s-au prevăzut cele două casete de validare Cascade Update şi Cascade Delete. Ştergerea în cascadă înseamnă că o solicitare de ştergere a unei înregistrări din tabela primară va determina ştergerea înregistrărilor corespunzătoare din tabelul asociat.

Fig. 5 Stabilirea relaţiilor între tabele

Similar se produce şi actualizarea în cascadă: modificarea valorii câmpului cheie primară din tabela principală determină modificarea tuturor înregistrărilor corespunzătoare din tabela asociată.

Relaţiile dintre tabele conferă consistenţa şi coerenţa bazei de date, de aceea în cadrul proiectării bazei de date stabilirea corectă a legăturilor dintre tabele este foarte importantă.Asocieri – în cursul definirii unei relaţii Access pune la dispoziţie un buton pentru definirea tipului de asociere prestabilit. Asocierea defineşte modul în care înregistrările din tabelele corelate vor fi combinate într-o interogare: Asocieri interne (inner join) – tipul de asociere implicit; o interogare cu

asociere internă va afişa numai acele înregistrări care corespund ambelor tabele.

Asocieri externe (outer join) – precizează că o interogare va selecta toate înregistrările dintr-o tabelă şi numai pe cele care corespund din celălalt tabel.

Sunt de 2 tipuri, în funcţie de tabela din care se preiau toate înregistrările:o Asociere stânga (left join) o Asociere dreapta (right join).

Asocierile externe nu se folosesc prea des deoarece sunt mai puţin flexibile, mai ales când sunt implicate mai multe tabele. Asociere intrinsecă (self join) – este o relaţie care asociază unui câmp dintr-

un tabel un câmp din acelaşi tabel. Se crează prin duplicarea tabelei şi stabilirea legăturii între câmpuri. Este o asociere foarte performantă deoarece permite căutări recursive. De exemplu: o tabelă care conţine date despre părinţi şi copii.