Tabele in Access - Proiectarea tabelelor

Tabele in Access

Proiectarea tabelelor este activitatea care are rolul esential in realizarea unei baze de date, celelalte obiecte fiind obtinute functie de tabelele create.

Un tabel reprezinta o colectie de date legate intre ele, memorata pe linii si coloane; fiecare linie contine o inregistrare-entitate completa de date referitoare la un anumit tip de obiecte. La randul ei, fiecare inregistrare este compusa din coloane sau campuri-un camp reprezentand cea mai mica entitate de date.

O tabela se poate afla in doua stari, intre care poate trece prin activarea comenzii specifice de pe ToolsBar. Acestea sunt:

Proiectare(Design View) - in care se poate defini o tabela noua, sau modifica structura uneia deja create.

Vizualizare(Datasheet View) - in care se pot afisa sau actualiza(adaugarea de inregistrari noi, stergeri de inregistrari sau modificarea valorilor din campuri) datele din tabela.

3.1 Proiectarea tabelelor in Access

Pentru acrea o tabela noua, va trebui:

sa apasam butonul Tables din fereastra Database si apoi sa executam dublu clic pe una din optiunile

Create table in Design view - pentru a crea tabelul prin definire campurilor si a proprietatilor acestora;

Create table by using wizard - pentru a crea tabelul cu ajutorul vrajitorului;

Create table by entering data - pentru a crea tabelul prin introducerea inregistrarilor, fara a mai defini structura acestui. In acest caz campurile tabelului vor fi denumite automat "field1", "field2", etc.

sa apasam butonul Tables din fereastra Database si apoi sa executam clic pe butonul New din partea de sus a acestei ferestre. In acest caz se deschide o noua fereastra care are urmatoarele optiuni:

Datasheet View - proiectarea tabelului print introducerea inregistrarilor;

Design View - proiectarea tabelului prin definirea campurilor

Table Wizard - construirea tabelului cu ajutorul vrajitorului;

Import Table - aducerea fizica unui tabel dintr-o alta baza de date;

Link Table - construirea unei legaturi catre un tabel dintr-o alta baza de date, fara ca acesta sa fie adus fiyic in baza noastra de date.

Figura 3.1.1 Fereastra butonului New

Definirea unei tabele se va face prin precizarea urmatoarelor elemente:

Numele tabelei. Se stabileste la crearea tabelei in urma unei intrebari explicite facuta de sistem, sau se modifica(ca de altfel orice nume de obiect) din fereastra Database, prin metodele folosite in Windows.

Campurile si proprietatile (caracteristicile, atributele) acestora.

Proprietatile tabelei.

Modificarea structurii unei tabele in care au fost deja introduse date, poate duce la pierderea sau deteriorarea caracteristicilor acestora, fiind o operatie care este indicat a se efectua dupa ce se face o copie de siguranta a tabelei respective.

3.1.1 Definirea campurilor

Majoritatea tabelelor unei baze de date au unul sau mai multe campuri ce identifica in mod unic fiecare inregistrare din acel tabel. Un camp se caracterizeaza prin: nume, tip de data si proprietati.

Numele campurilor se va introduce in coloana Field Name, aflata in partea superioara a formularului de proiectare a tabelelor.

Regulile de constituire a numelui unei tabele, ca si a celorlalte obiecte folosite in Access, sunt cele introduse de standardul "Windows".

Pentru exemplificare vom construi tabela GESTIUNI din exemplul anterior, prezentat la tipurile de relatii.

3.1.1.1 Tipuri de date ale campurilor

Fiecarui tip de informatii ii va corespunde un anumit tip de date care determina tipul campului. Microsoft Access pune la dispozitia utilizatorului urmatoarele optiuni pentru tipul de date cu care se va incarca fiecare camp:

AutoNumber nu poate fi modificat manual, fiind de pre-ferat a se folosi drept cheie primara a unui tabel. Desi este incrementat secvential in mod implicit, se poate stabili si o incrementare aleatoare;

Text este destinat informatiei de tip sir de caractere (ma-xim 255 caractere); - combinatii de litere si cifre sau caractere nefolosite in calcule matematice

Number are mai multe subtipuri, determinate de pro-prietatea field size:

Byte cu interval valoric intre 0 si 255;

Integer cu interval valoric intre-32.768 si 32.767;

Long Integer cu interval valoric intre -2.147.483.648 si 2.147.483.647;

Single cu interval valoric intre-3,4*10³⁸ si 3,4*10³⁸;

Double cu interval valoric intre-1,797*10³⁰⁸ si 1,797*10³⁰⁸;

Decimal cu interval valoric intre -22337203685477.5808 si 922337203685477.5808

Memo este utilizat pentru text de lungime ce depaseste capacitatea de 255 caractere a tipului text;

Date/Time contine informatii de data si/sau timp;

Currency contine o valoare asociata la o anumita moneda, precizie de 15 cifre la stanga virgulei zecimale si 4 cifre la dreapta;

Yes/No contine una din cele doua stari: yes/no, on/off, true/false.

Obiecte OLE este tot un camp de capacitate mare ce contine obiecte provenite din aplicatii ca Excel , Word . Dimensiunea maxima este in jur de 1 GOctet

Lookup permite stocarea unei valori, dar afiseaza textul ce insoteste valoarea respectiva. Acest tip de camp poate fi legat de fapt de un alt tabel sau interogare, de unde isi extrage informatiile pe care le afiseaza sub forma unui combo box(lista ascunsa);

Figura 3.1.1.1.1 Crearea unui tabel in modulul Design View

Dupa cum se observa in figura 3.1.1.1.1 exista si o zona de comentarii. Acestea se introduc in Description, a treia coloana din partea de sus a formularului de proiectare a tabelelor. Desi nu este obligatoriu, este utila folosirea lor pentru cresterea lizibilitatii aplicatiilor.

Atunci cand cream o tabela trebuie sa stabilim cheia primara a tabelei. O tabela care are cheie primara, va avea garantata integritatea existentiala. Deci stabilirea cheii primare si configurarea cu atentie a proprietatilor campurilor tabelelor, sunt cele mai simple si puternice metode pentru crearea unei baze de date relationale cu integritate existentiala si de domeniu. Cheia primara se stabileste cu ajutorul comenzii Primary Key din meniul Edit. Aceasta functie se apeleaza dupa ce am selectat campul sau campurile care dorim sa fie cheie primara a tabelei. Acelasi lucru il putem face alegand functia Primary Key din meniul derulant ce apare in urma executarii unui clic dreapta pe campul ce dorim sa fie cheie primara.

Figura 3.1.1.1.2 Stabilirea cheii primare

3.1.1.2 Proprietatile campurilor

Existenta panoului Field Properties este foarte importanta deoarece permite stabilirea de proprietati suplimentare pentru anumite campuri. Proprietatile unui camp, il vor insoti oriunde acesta va fi folosit, existand totusi posibilitate ca unele dintre ele sa fie modificate temporar(de exemplu prin intermediul unor controale folosite in formulare sau rapoarte, asa cum se va prezenta in alte capitole).

Dupa ce s-a ales un anumit tip de data, in functie de acesta, se vor afisa in partea de jos a formularului, si restul de proprietati care pot fi stabilite pentru acel camp. Setarea proprietatilor campurilor este mult usurata de wizard-uri si liste ascunse.

Felul proprietatilor este specific fiecarui tip de date care se fixeaza pentru un camp. Totusi multe dintre proprietati sunt comune, la cele mai utilizate tipuri de date (Text, Number, Date/Time, Currency, Yes/No).

In continuare sunt prezentate cele mai utilizate proprietati:

Caption - desi campul are un nume, introducerea unui alt nume in aceasta caseta, duce la folosirea acesteia din urma atunci cand se va afisa numele campului. Aceasta actiune este valabila numai la afisarea numelui campului, orice referire la acesta facandu-se cu numele efectiv al campului. Daca nu se introduce nimic - se fa folosi la afisare numele campului.

Default Value - Introducerea unei valori valide in aceasta caseta, reprezinta valoarea de initializare a acestui camp la crearea unei inregistrari noi. Daca nu se introduce nimic campul va fi initializat cu valoarea NULL.

NULL arata lipsa unei valori in campul respectiv. Valoarea NULL poate fi testata sau folosita in expresii. Ea se comporta intr-un mod specific daca este folosita in operatii relationale, logice, aritmetice sau ca parametru a unei functii si anume se propaga ca rezultat al expresiei, indiferent de forma acesteia.

Nota: Exista doua exceptii de la regula cu privire la comportamentul valorii NULL:

prin concatenarea cu NULL a unui sir, se obtine sirul si nu NULL;

in functiile agregate(prezentate in alt capitol) NULL se ignora.

Validation Rule - stabileste domeniul de valabilitate al valorilor pe care poate sa le ea campul, fiind cel mai puternic instrument prin care se configureaza integritatea de domeniu. Pentru a nu introduce date eronate putem defini reguli de validare asupra campurilor, reguli ce nu ne vor permite sa introducem date necorespunzatoare.

Prin aceasta caseta se introduce o conditie, care daca nu este adevarata, va produce doua actiuni:

Afisarea unui mesaj(introdus in caseta Validation Text);

Blocarea parasirii de catre focus(locul unde se afla cursorul) a campului respectiv pana cand operatorul nu va introduce o data care sa determine ca respectiva conditie sa fie adevarata.

In principal se va tine seama de urmatoarele doua reguli:

Expresiile se creeaza avand obligatoriu ca unul din termeni valoarea introdusa in campul respectiv. Numele acestuia se va omite, considerandu-se implicit.

Daca nu se introduce nici un operator la inceputul conditiei acesta va fi implicit operatorul relational de egalitate "=".

Asa cum am mai aratat, daca aceasta regula de validare aplicata datei care a fost introdusa in acest camp nu este adevarata, atunci valoarea respectiva este respinsa asteptandu-se introducerea unei date corecte.

Deci ca o data sa fie acceptata trebuie sa respecte tipul respectiv, regula de validare si masca de introducere(Input Mask Aceste trei proprietati determina practic integritatea de domeniu pentru campul respectiv.

In zona Validation Rule se poate introduce o conditie astfel:

- direct de la tastatura. De exemplu scriem : <20 - va determina pentru un camp de tip byte numai introducerea de valori de la 0 la 19, sau >=1 AND <= 10 - va determina pentru un camp numeric introducerea de valori de la 1 la 10.

- apasand butonul din dreapta caz in care se va deschide fereastra Expression Builder in care vom putea crea propriile reguli de validare folosind diferite functii sau operatori.

Figura 3.1.1.2.1 Fereastra Expression Builder

Validation Text - stabileste un text care va fi afisat daca regula de validare nu este adevarata. Este indicat ca acest mesaj sa fie foarte explicit si clar pentru a lamuri operatorul asupra erorii comise.

Format - reprezinta felul in care vor fi afisate datele in camp, folosindu-se un anumit sablon. La anumite tipuri (Number, Date) se pot folosi sabloanele din Windows/Control Panel/Regional Settings.

Input Mask - Este un sablon care este folosit la introducerea datelor. Pentru crearea lui este pus la dispozitie un wizard. (La instalarea Access-ului, trebuie mentionat in mod special acest wizard.). Aceasta proprietate prin care se pot elimina anumite caractere care se incearca a se introduce, va actiona si ea in sensul respectarii integritatii de domeniu a campului respectiv.

Field Size - stabileste dimensiune campului astfel:

. La Text reprezinta lungimea campului

. La Numere reprezinta tipul sistemului de codificare, si anume:

. Byte - 8 biti, pozitiv

. Integer - 16 biti, pozitiv si negativ

. Long Integer - 32 biti, pozitiv si negativ

. Single - Virgula mobila simpla precizie, 7 cifre semnificative

. Duble - Virgula mobila dubla precizie, 14 cifre semnificative

Decimal Places - folosit numai la tipurile de date numerice si reprezentand numarul de zecimale. Exista si optiunea Auto, care seteaza automat numarul de zecimale functie de datele introduse.

New Value - la tipul AutoNumber stabileste daca noile numere se acorda prin incrementare sau random(aleator).

Required si Allow ZeroLenght garanteaza introducerea de date in campurile de tip cheie si la cele in care aplicatia solicita existenta unor informatii diferite de null. Prin aceste proprietati, care fac ca o inregistrare introdusa intr-o tabela sa aiba sens, sa fie logica, se introduce o noua metoda de realizare a integritatii bazei de date.

Indexed. Desi Microsoft Access creeaza un index dupa cheia primara, pentru a spori viteza de cautare, se pot folosi si alti indecsi pentru campurile cu utilizare frecventa. Chiar daca utilizarea acestora mareste timpul de introducere a datelor datorita timpului necesar pentru actualizarea indecsilor suplimentari, procesele de cautare vor fi accelerate de existenta lor.

Stabilirea daca un camp va fi sau nu indexat se face functie de utilizarea frecventa sau mai putin a campului respectiv in operatii de sortare sau selectie. Aceasta proprietate isi arata eficienta in special la tabele cu mii de inregistrari, unde se pot face economii de timp de executie la nivelul orelor.

Exista trei posibilitati:

No

Yes - Duplications Ok

Yes - No Duplications - evident asa se seteaza un camp cheie primara

Access este prevazut cu un mecanism foarte simplu si direct de efectuare chiar in timpul utilizarii a sortarii pe unul sau mai multe campuri. Acest sistem este activ in orice obiect unde este utilizata o tabela. El consta din selectia sau pozitionarea cursorului pe campului respectiv si lansarea acestei operatii din meniul contextual(clic dreapta), asa cum se vede si din figura urmatoare.

Un mecanism identic cu cel prezentat anterior este folosit in Access si pentru efectuarea unor operatii simple de selectie, numite filtre. In meniul contextual afisat dupa pozitionarea pe o anumita valoare dintr-un camp sunt disponibile 3 posibilitati de filtrare, si anume:

Filter By Selection - prin care se selectioneaza toate inregistrarile care pentru campul respectiv au aceiasi valoare cu cea in care este plasat cursorul.

Filter Excluding Selection - prin care se selectioneaza toate inregistrarile care pentru campul respectiv au o valoare diferita de cea in care este plasat cursorul.

Filter For - prin care se selectioneaza toate inregistrarile care pentru campul respectiv au aceiasi valoare ca aceea pe care se introduce in caseta respectiva

Operatiile de sortare sau filtrare efectuate pot fi usor anulate prin comanda Remove Filter/Sort.

3.1.1.3 Proprietatea Lookup

La campurile de tip Number, Text sau Yes/No, se poate atasa proprietatea Lookup, care permite introducerea de date prin intermediul unui control List Box(lista derulanta) sau Combo Box(lista ascunsa) direct din una din urmatoarele trei tipuri de surse:

Valoarea unor campuri dintr-o alta tabela sau cerere - cea mai utilizata.

Dintr-o lista de valori introdusa si atasata casetei

Numele unor campuri dintr-o alta tabela sau cerere.

Figura 3.1.1.3.1 Pagina Lookup

Pentru a seta aceasta proprietate se pot folosi doua metode:

Wizard LookUp - lansat din lista derulanta a tipurilor campurilor (vezi figura 3.1.1.1.1) - care practic asista utilizatorul in setarea proprietatilor paginii LookUp, din Field Properties.

direct in pagina LookUp. Datele ce trebuiesc completate in pagina de LookUp sunt:

Display Control - Se alege intre List Box sau Combo Box

Row Source Type - Una din cele trei tipuri de surse prezentate mai sus.

Row Source - exista doua posibilitati functie de sursa de date precizata:

Numele tabelei /cererii ce este folosita ca sursa de date (selectata dintr-o lista ascunsa);

Valorile din lista, separate prin ; (pentru tipul de sursa Value List)

Bound Column - Numarul (incepand de la 1) campului din tabela /cererea, de la care importam datele de intrare.

Column Count - Numarul (incepand de la 1) campurilor din tabela /cererea care se vor afisa in lista (separate tot prin ; ). Deci pe langa campul din ale carui date vom selectiona ceea ce vrem sa introducem (Bound Column) este uneori necesar sa afisam mai multe campuri (Column Count) pentru a permite transmiterea tuturor informatiilor necesare. De exemplu, vrem sa selectionam un camp care contine un cod, dar este necesar sa afisam si denumirii explicite a ceea ce este codificat.

Column Head - specifica daca se vor afisa si denumirile campurilor

Column Widths si List Widths - specifica latimea coloanelor si a intregii liste.

List Row - specifica numarul de linii care se afiseaza, restul liniilor fiind accesate eventual prin bare derulante.

Limt to List - setata pe Yes blocheaza introducerea de date in afara celor din lista.

Desi toate aceste proprietati pot fi setate si prin formulare este bine ca aceste proprietati sa fie atasate tabelei si nu formularelor. Acest lucru devine evident daca ne gandim ca aceiasi tabela poate fi folosita de mai multe formulare sau interogari.

3.1.2 Proprietatile tabelei

Se introduc printr-un formular ce apare la actionarea pictogramei specifice de proprietati(o mana ce arata un tabel) sau din meniul Access.

Aceste proprietati, se refera la setari ce se refera la comportamentul tabelei in ansamblu. Cea mai folosita este:

Validation Rule - introduce o conditie a carei evaluare se face in momentul cand se termina de introdus(modificat) o inregistrare si se trece la alta. Prin aceasta se introduce o regula de validare care se poate referi la relatiile dintre campuri, ea completand in acest fel configurarea integritatii de domeniu.

Daca evaluarea conditiei nu este adevarata, se vor produce doua actiuni:

Afisarea unui mesaj(introdus in caseta urmatoare, Validation Text);

Blocarea iesirii din inregistrare pana cand evaluarea conditiei va fi adevarata

Aceasta facilitate se foloseste cand se doresc a se verifica corelatii intre datele introduse in mai multe campuri, deoarece evaluarea se va face dupa ce se termina introducerea datelor in toate campurile inregistrarii. In exemplu se verifica ca data introdusa in campul [C1] sa fie inferioara datei din campul [C2] (cand se face referire la un camp acesta este scris obligatoriu intre paranteze drepte).

Figura 3.1.2.1 Proprietatile tabelei-stabilirea unei reguli de validare

3.2 Vizualizarea tabelelor

Incarcarea cu date a tabelelor (popularea), indiferent de modul in care se creeaza un tabel, se realizeaza prin Datasheet View. La adaugarea de date in acest mod, se respecta toate regulile de validare referitoare la integritatea referentiala, integritatea de domeniu si integritatea existentiala; daca nu se respecta conditiile de validare, utilizatorul este avertizat printr-un mesaj de atentionare, oferindu-i-se totodata posibilitatea corectarii erorilor de introducere.

Obiectele Datasheet sunt prevazute cu o interfata utilizator simpla si deosebit de performanta, bazata aproape exclusiv pe lucrul cu mouse-ul, care permite facilitati deosebite privind personalizarea structurii de afisare. Obiectul Datasheet, are implementate toate facilitatile Windows privind lucrul cu ferestre(redimimensionari, pozitionari, copieri sau mutari de obiecte, etc.)

Figura 3.2.1 Fereastra Datasheet

Prezentam in continuare cele mai utilizate operatii care se pot efectua in fereastra Datasheet:

Selectia unei inregistrari - clic pe marcajul din dreptul inregistrarii;

Selectia unui camp - clic pe numele campului;

Selectia multipla - clic urmat de parcurgerea cu mouse-ul apasat a zonei respective(sau selectii multiple cu tasta Shift apasata);

Stergerea unei inregistrari - selectie + meniu contextual + Delete;

Mutarea unei coloane - selectie + 'tragere' pe noua pozitie;

Modificarea latimii unei coloane - pozitionare pe linia de demarcare dintre numele coloanelor + tragere;

Sortare sau filtrare - selectia unei celule + meniu contextual + comanda(au fost deja prezentate);

Cautarea sau inlocuirea unor date - ca in standardul Windows cu Find si Replace;

Rolul ferestrelor Datasheet, este acela de a oferi proiectantilor de aplicatii un modul prin care sa isi testeze obiectele create. Pentru popularea tabelelor, este insa de preferat incarcarea datelor prin intermediul unui formular, mai ales cand se aloca date in mai multe tabele.

3.3 Aplicatie

O firma are mai multe gestiuni situate la adrese diferite. In aceste gestiuni se gasesc mai multe produse in stocuri diferite, iar un produs se poate gasi in mai multe gestiuni. Proiectam tabele bazei de date astfel:

1. Tabelul "GESTIUNI" care contine urmatoarele campuri:

- Cod_G - cheie primara(se observa cheita din dreptul acestui camp, precum si proprietatea Indexed stabilita automat la "Yes(No Duplicates)"), ce contine date numerice(Number - Integer) cu valori mai mari decat 100(se seteaza in proprietatea Validation Rule conditia >100, iar Validation Text contine mesajul ce va fi afisat in momentul in care nu este respectata conditia si anume "Un numar mai mare decat 100")(vezi figura 3.3.1).

- DenumireG - camp de tip Text pentru care se stabileste proprietatea Required la Yes pentru a obliga introducerea unei date in acest camp.

- Adresa - camp de tip Text.

Figura 3.3.1 Proiectarea tabelului "GESTIUNI"

2. Tabelul "PRODUSE" care contine campurile(vezi figura 3.3.2):

- Cod_P - cheie primara care contine date pozitive. Se stabileste in proprietatea Validation Rule conditia >0, proprietatea Validation Text contine mesajul de atentionare in cazul introducerii unei valori eronate "Un numar pozitiv", iar proprietatea Indexed este setata la "Yes(No Duplicates)".

- DenumireP - camp de tip Text

- Um - unitatea de masura de tip Text

- Pret - camp de tip real. Se alege tipul de date Number si se seteaza proprietatea Field Size la valoare Single.

Figura 3.3.2 Proiectarea tabelului PRODUSE

3. Tabelul "STOCURI", care este un tabel intermediar ce realizeaza legatura dintre cele doua tabele, datorita existentei unei relatii de tipul many to many, contine campurile:

- Cod_G este cheie externa si va contine acelasi tip de date ca si campul de legatura din tabela "GESTIUNI". Pentru acest camp se seteaza proprietatea Lookup astfel incat sa fie permisa introducerea de date prin intermediul unui "Combo Box" din campul Cod_G al tabelei parinte. Pentru aceasta se seteaza urmatoarele proprietati din pagina Lookup:

- Display Control se stabileste la Combo Box;

- Row Source Type se seteaza Table/Query;

- Row Source precizeaza sursa de date si anume tabelul GESTIUNI;

- Bound Column se seteaza la 1 intrucat campul Cod_G se gaseste in prima pozitie in tabelul GESTIUNI;

- Column Count va fi 1 deoarece, pentru exemplul considerat, este suficient sa vedem numai datele din codul gestiunii

- Cod_P este tot cheie externa si va fi de acelasi tip de date cu campul Cod_P din tabela parinte PRODUSE. Si pentru acest camp putem sa ne legam la sursa de date deja construita, din tabela parinte, deci vom seta proprietatea Lookup la fel ca la campul Cod_G.

- Stoc este un camp de tip Number, care apare in aceasta tabela intermediara deoarece stocul depinde atat de gestiune cat si de produse(stocul de produs se stabileste la nivel de gestiune).

Figura 3.3.3 Stabilirea proprietatii Lookup pentru campurile Cod_G si Cod_P din tabelul STOCURI

Dupa proiectarea tabelelor trebuiesc stabilite relatiile ce leaga cele trei tabele ale bazei de date, dar acest lucru va fi prezentat in capitolul urmator, si apoi se trece la introducerea inregistrarilor. Intrucat introducerea inregistrarilor in tabele se realizeaza prin intermediul ferestrei Datasheet, descrisa anterior, voi prezenta doar cateva exemple, pentru care ori nu sunt indeplinite regulile de validare, ori datele sunt introduse prin selectionarea dintr-o lista.

1. Daca introducem in campul Cod_G un numar mai mic decat 100 se va afisa un mesaj de atentionare.

2. Daca se omite introducerea unei denumiri de gestiune se va afisa un mesaj corespunzator

3. Daca se introduce in campul Cod P un numar negativ va fi afisat mesajul scris la proprietatea Validation Text. Si in cazul introducerii in cheia primara a unei valori care mai exista, se va afisa un mesaj dat de Access

4. In cazul definirii unor surse de date vom alege din liste ascunse valorile ce urmeaza sa le primeasca campurile Cod_G si Cod_P din tabelul STOCURI.