Obiecte de tip tabel si interogari access

OBIECTE DE TIP TABEL SI INTEROGARI ACCESS

1. Obiectivele unitatii de invatare 11

2. Obiecte de tip tabel (Table)

2.1. Prezentare generala

2.2. Crearea obiectelor de tip tabel

2.3. Definirea relatiilor dintre tabele

2.4. Operatii la nivelul inregistrarilor dintr-o tabela

Studiu de caz  nr. 3

Studiu de caz nr. 4

1. Obiectivele unitatii de invatare 11

Tipuri de date utilizate in Access

Formatele de reprezentare a tipurilor de date

Modalitati de creare a obiectelor Access

Descrierea structurii de catre utilizator (Design View)

Descrierea structurii cu ajutorul asistentilor (Wizard)

Tabele

Crearea structurii si incarcarea datelor intr-o tabela

Stabilirea relatiilor intre tabele

Asigurarea integritatii entitatii si a integritatii referentiale a datelor

2. Obiecte de tip tabel (Tables)

2.1. Prezentare generala

Tabelele reprezinta obiectele principale ale unei baze de date ACCESS, deoarece in ele se stocheaza datele primare referitoare la entitatile participante la aplicatie.

Tabelele sunt identificate printr-un nume unic, atribuit in momentul crearii lor si apar listate in fereastra Database, atunci cand se actioneaza butonul Table. Se recomanda ca acest nume, format din maxim 64 de caractere ce pot fi litere, cifre si spatii, sa sugereze cat mai fidel continutul sau, respectiv entitatea pe care o reprezinta (ex. STUDENTI, NOTE, TAXE, CLIENTI, PRODUSE, FACTURI etc.).

Intr-un tabel, datele sunt organizate pe randuri si pe coloane. Randurile contin informatii despre fiecare exemplar al entitatii respective, iar fiecare coloana reprezinta un camp al entitatii. Pentru randurile unui tabel se foloseste notiunea de inregistrari, iar pentru coloane cea de campuri. Este important de retinut ca fiecare camp al unui tabel va contine acelasi tip de informatie, avand acelasi tip de date si aceleasi caracteristici pentru toate exemplarele din tabel.

Un tabel Access are doua componente principale:

structura

continut

Structura este formata din ansamblul campurilor (atribute, caracteristici) care definesc entitatea respectiva si ea corespunde coloanelor din tabel. Fiecare camp al unui tabel va contine acelasi tip de informatie, avand acelasi tip de data si aceleasi proprietati sau caracteristici (de exemplu acelasi mod de reprezentare, aceeasi lungime maxima, aceleasi reguli de validare etc.)

De asemenea cuprinde informatii despre campurile cheii primare prin continutul carora se defineste unic o inregistrare logica, campurile indexate si tipul indexarii.

Un tabel se considera creat in momentul salvarii structurii sale, chiar daca nu avem date sa ii completam continutul.

Continutul este format din valorile inscrise pe randurile tabelului. Un rand, prin continutul sau reprezinta un anume exemplar din entitatea respectiva, numit si inregistrare logica.

Gestiunea tabelelor (creare, modificare, vizualizare, stergere) dintr-o baza de date se realizeaza din sectiunea Tables a ferestrei bazei de date.

Tabelele Access se pot crea prin:

definirea de catre utilizator a structurii;

pe baza structurilor predefinite;

interogari care au drept efect obtinerea unui nou tabel de date, care poate fi utilizat de alte obiecte Access;

copierea unui tabel deja creat intr-o alta baza de date;

importarea datelor din alte aplicatii, folosind comanda Import;

atasarea, cu ajutorul comenzii Attach File, a unor tabele definite in alte baze de date Access, folosindu-se doar datele existente, fara a putea modifica structura.

O tabela de date dispare din baza de date unde a fost creata si eventual utilizata numai la solicitarea stergerii ei.

Lucrul cu tabele presupune parcurgerea urmatoarelor etape:

- crearea tuturor tabelelor din aplicatie prin definirea structurii lor (descrierea capului de tabel si a cheilor primare) si salvarea acestora;

- realizarea ralatiilor dintre tabelele aplicatiei;

- introducerea datelor in tabele.

2.2. Crearea obiectelor de tip tabel

In Access exista in principal urmatoarele posibilitati de creare a tabelelor:

cu limbajul de definire a datelor, prin forte proprii (Design View);

cu ajutorul asistentilor pentru tabele (Table Wizards);

cu optiunea Datasheet View

Prima metoda permite un control mai amanuntit al proprietatilor tabelului creat. In cadrul acestei metode utilizatorul stabileste structura tabelului, definind: numele campurilor, tipurile de date, proprietati, cheia primara.

A doua metoda este mai usoara si mai rapida, asistentii ghidandu-ne si sugerandu-ne anumite solutii. Ea este recomandata proiectantilor aflati in faza de initiere. Prin aceasta posibilitate tabelele se creeaza pe baza unor sabloane predefinite de catre sistem, pentru tipurile cele mai utilizate de tabele (clienti, produse etc). Tabelele predefinite pot fi adaptate la conceptul si cerintele intregii aplicatii.

A treia metoda se utilizeaza atunci cand este necesara crearea tabelului foarte repede.

Pentru crearea unui tabel prin una din cele trei metode prezentate, se poate aplica una din posibilitatile urmatoare:

Din fereastra Database se selecteaza tipul de obiect Table si se actioneaza butonul de comanda New

Din meniul Insert se alege comanda Table

Se folosesc hiperlink-urile existente in fereastra bazei de date:

Create table in Design View

Create table by using Wizard

Create table by entering data

Alegand una din primele doua variante se afiseaza pe ecran caseta de dialog (figura 6.1), unde se propun mai multe modalitati de descriere a structurii unui tabel:

Fig.1 Modalitati de descriere a structurii unui tabel

In continuare va fi prezentata modalitatea Design View pentru crearea tabelelor.

Este metoda cel mai mult folosita, in care utilizatorul stabileste absolut toti parametrii necesari pentru descrierea structurii unui tabel: numele campurilor, tipurile de date ale acestora, reguli de validare, mesaje de eroare ce vor fi afisate in situatia neindeplinirii conditiei verificate etc.

Pentru crearea unui tabel in Design View se parcurg urmatoarele etape:

definirea structurii

stabilirea cheii primare

salvarea tabelului, atribuindu-i un nume

Tabelul se considera creat in acest moment, avand definit si memorat capul de tabel.

Fizic, crearea unui tabel in Design View se realizeaza astfel:

se selecteaza din fereastra bazei de date tipul de obiecte Tables, se activeaza butonul New si modalitatea Design View sau se actioneaza hyperlink ul Create table in Design View

Apare fereastra ACCESS de descriere a tabelei, formata din doua zone: zona " in care se definesc campurile din structura tabelei si zona "2" in care sunt precizate proprietatile acestora.

Fig.2 Fereastra de descriere a structurii

Descrierea campurilor:

A. Zona "

Apare in partea superioara a ferestrei sub forma unei foi de date cu mai multe randuri si trei coloane: Field Name, Type si Description.

Pentru fiecare camp adaugat in aceasta zona, obligatoriu trebuie tastat numele sau in coloana Field Name si precizat tipul datelor ce vor fi introduse drept valori in el, in coloana Type. Completarea coloanei Description este optionala. Daca se completeaza, va contine elemente de descriere a campului respectiv.

Numele campului Field Name

Numele unui camp trebuie sa respecte urmatoarele reguli:

Poate sa contina litere (majuscule sau minuscule, sau ambele tipuri), cifre si numeroase caractere speciale.

Poate sa contina maximum 64 de caractere alfanumerice, majuscule sau/si minuscule, inclusiv spatii.

Este interzisa utilizarea caracterelor: punct (.), semnul exclamarii (!), paranteze drepte (

Numele campului nu poate sa inceapa cu un spatiu.

In formarea numelui campului se recomanda:

- Sa nu se utilizeze caracterul spatiu; pentru imbunatatirea lizibilitatii numelui se poate inlocui spatiul cu caracterul de subliniere (_) sau se pot utiliza litere majuscule si minuscule in acelasi nume (ex. CodProprietar).

- Reducerea lungimii numelui campurilor pentru a conserva resursele sistemului si a scuti utilizatorul de introduceri lungi atunci cand se fac referiri la numele campului.

Tipul de date al campului (Data Type

Este un controlor de tip lista combinata care are initial prefigurata valoarea Text Utilizatorul isi va alege insa tipul corespunzator de data, avand la dispozitie noua tipuri de date de baza, pentru unele dintre ele fiind disponibile subtipuri de date, in functie de necesitati. Subtipurile se configureaza in zona "2" a ferestrei (sectiunea General).

Tipurile si subtipurile de date ACCESS sunt prezentate in urmatorul tabel:

Comentarii Description

Este o coloana optionala, unde utilizatorul poate introduce sub forma de siruri de caractere diverse informatii cu privire la un camp.

B. Zona "2" (Properties)

Este plasata in partea inferioara a ferestrei si in ea se pot stabili valori pentru doua categorii de proprietati:

Proprietati pe care le au toate campurile;

Proprietati specifice anumitor campuri, in functie de tipul de date.

Proprietatile din categoria General contin urmatoarele optiuni ce pot fi utilizate la nivelul campurile definite anterior:

- Field Size - stabileste dimensiunea maxima admisa pentru tipurile de date Number, AutoNumber, Text. Pentru primele doua, este predefinit tipul Long Integer, iar pentru cel de tip text este predefinita dimensiunea de 50 caractere. Utilizatorul isi poate alege insa valorile necesare functie de situatiile specificate in tabelul anterior.

- Decimal Places - stabileste numarul de zecimale utilizat pentru afisarea unui numar. Valorile posibile sunt Auto (numarul este afisat cu atatea zecimale cu cate a fost calculat; nu sunt afisate mai mult de 15 zecimale) sau un numar intre 0 si 15. Numarul de zecimale afisat poate fi influentat si din proprietatea Format. Aceasta optiune este utilizata la numere reale.

- Format - stabileste formatul de afisare al unui camp. Aceste formate se pot defini de utilizator cu ajutorul unor secvente speciale de caractere sau pentru unele tipuri de date exista deja predefinite formate uzuale.

a. Formatele predefinite de afisare pentru numere sunt urmatoarele:

- Byte, Integer, Long Integer, Double, Single

b. Formatele predefinite de afisare pentru campurile de tip Date/Time sunt prezentate in urmatoarea figura:

Fig.3 Formate de afisare a campurilor de tip Date/Time

c.   Pentru campurile de tip Yes/No formatele de afisare predefinite sunt:

Fig. 4 Formatele predefinite pentru campurile YES/NO

d. In cazul campurilor de tip Text sau Memo, nu exista formate predefinite de afisare prin intermediul unor caractere, utilizatorul stabilindu-si propriile formate. Aceste formate se stabilesc in doua zone:

- Prima zona se utilizeaza pentru a stabili formatul propiu-zis de afisare al continutului campului;

- A doua zona foloseste pentru stabilirea formatului de afisare in cazul valorilor vide sau Null.

Caracterele ce se pot utiliza pentru stabilirea formatelor de afisare pentru campurile de tip Text sau Memo:

@- este obligatorie introducerea unui carcter sau spatiu;

&- nu este obligatorie introducerea unui caracter;

>- transforma toate caracterele in majuscule, indiferent de modul de tastare;

<- transforma toate caracterele in minuscule, indiferent de modul de tastare;

Exemplu:

<;" Cod autovehicul eronat" va afisa sirul introdus cu minuscule, iar in cazul in care nu este introdus un sir va afisa textul -Cod autovehicul eronat.

Si la campurile de tip Text sau Memo, afara de formatele predefinite se pot utiliza si secvente speciale de caractere, care pot fi utilizate dupa necesitati. Sunt caractere care se pot utiliza pentru orice tip de camp si caractere care se pot utiliza numai pentru anumite tipuri de date.

Caracterele care se pot utiliza in orice tip de camp:

"sir caractere"- afiseaza sirul de caractere dintre ghilimele, impreuna cu continutul campului;

!- aliniaza continutul unui camp la stanga, in loc de aliniere la dreapta;

Culoare - afiseaza continutul unui camp in culoarea precizata;

(space)

- Caption - stabileste un sir de caractere care va fi afisat in titlul unei coloane, in momentul introducerii datelor; daca aceasta proprietate nu are nici o valoare, sirul afisat in titlul unei coloane este chiar numele campului; valoarea acestei proprietati nu are nici o influenta asupra numelui campului in utilizarea sa in interogari, VBA, etc. Exemplu: daca numele unui camp este CodOp, si in proprietatea Caption se scrie Cod Operatie, in titlul coloanei respective va apare continutul "Cod Operatie"

- Default Value - stabileste o valoare implicita care este propusa in momentul in care se introduc date intr-un tabel; se recomanda in cazul in care exista o valoare preponderenta a continutului unui camp; in cazul in care utilizatorul nu introduce o alta valoare in campul respectiv, valoarea din aceasta proprietate va fi memorata in continutul acestuia. Exemplu: pentru un camp care memoreaza valoarea TVA, tinand cont ca pentru majoritatea produselor aceasta este 19%, pentru a usura munca de culegere date, este recomandat ca in propietatea Default Value sa se stabileasca aceasta valoare. La introducerea datelor, daca un produs are aceasta cota, atunci valoarea sa nu se mai tasteaza.

- Input Mask - stabileste un format (masca, sablon) de introducere a datelor pentru un anumit camp. La fel ca in cazul proprietatii Format, exista o serie de caractere prin care se stabileste acest format de introducere a datelor. In cazul in care se utilizeaza ambele proprietati, Format are prioritate fata de Input Mask.

Proprietatea Input Mask pune la dispozitia utilizatorului cateva masti predefinite, dar si posibilitatea definirii unor masti personalizate. Mastile personalizate se definesc sub forma a trei zone separate prin caracterul punct si virgula (;):

prima zona se utilizeaza pentru definirea propiu-zisa a formatului de introducere;

a doua zona, se utilizeaza pentru a specifica daca se memoreaza si caracterele care fac parte din sablonul de introducere sau nu; iar valoarea unu inhoba aceasta posibilitate;

a treia zona se utilizeaza pentru a specifica un caracter, care este afisat intr-o pozitie din sablon.

Caractere ce se pot utiliza pentru definirea unui sablon de introducere a datelor:

0 - sunt permise toate cifrele de la 0 la 9, fara semn; nu se permite continut incomplet al campului, daca se utilizeaza acest simbol. Exemplu: o masca definita sub forma 000, va afisa la introducere___ si nu va permite decat introducerea a exact trei cifre.

9 - sunt acceptate cifrele de la 0 la 9, fara semn, inclusiv spatiu; este permis continutul incomplet;

# - sunt permise cifrele de la 0 la 9, inclusiv spatiu si semn; este permis continut incomplet;

L - sunt acceptate doar literele de la A la Z; nu este permis continutul incomplet al campului;

? - sunt permise literele de la A la Z si campuri incomplete;

A - sunt permise toate literele si cifrele, dar nu se accepta continut incomplet al campului;

& - este permis orice caracter sau spatiu, dar nu se accepta continut incomplet al campului;

C - orice caracter sau spatiu, inclusiv continut incomplet al campului;

< - caracterele introduse sunt convertite in minuscule;

> - caracterele introduse sunt convertite in majuscule;

. , - separatori pentru zecimale, respectiv mii;

:; - / separatori pentru date calendaristice si pentru timp;

password - afiseaza caracterul asterisc (*) in momentul introducerii de date;

Exemplu:

>AA<aaa defineste un format de introducere date, in care primele doua caractere sunt obligatorii si transformate in majuscule, iar ultimele trei sunt optionale si transformate in minuscule.

Validation Rule - defineste o regula de validare pentru un camp (se testeaza continutul introdus intr-un camp). Regulile de validare se pot testa dupa introducerea unei valori in campul respectiv sau in momentul salvarii inregistrarii respective. Expresia pentru aceasta proprietate se poate scrie si in mod asistat cu ajutorul ferestrei Expression Builder, actionata prin butonul corespunzator.

Numele campurilor intr-o astfel de expresie se inscriu intre paranteze patrate si intre ele se folosesc operatori, constante sau functii Access.

Exemplu:

>4 and <=500 - valorile introduse trebuie cuprinse intre 5 si 500

<>Null sau Is not Null - campul respectiv trebuie sa contina o valoare

IN ("kg";"l";"m") - sunt singurile valori pe care le poate contine campul

>=Date() - data introdusa trebuie sa fie mai mare sau egala cu data curenta din calculator

Year( data imprumut )<=Year( Date() - anul din data imprumutului trebuie sa fie mai mic sau egal cu anul din sistem

Fig. 5 Definirea validarilor

- Validation Text - permite tastarea unui mesaj de atentionare, care va fi afisat intocmai in momentul in care conditia stabilita in Validation Rule nu este indeplinita.

Exemplu:

"Valoarea tastata nu este cuprinsa intre 10 si 500"

"S-a introdus un termen de predare a proiectului mai mic decat data curenta"

- Required - poate lua valoarea Yes sau No, corespunzatoare valorilor logice True sau False. Prin aceste valori se stabileste daca valoarea unui camp trebuie introdusa obligatoriu sau nu. Pentru campurile de tip Text, Memo, Hyperlink, aceasta proprietate trebuie utilizata impreuna cu Allow Zero Length.

- Allow Zero Length - poate lua valoarea Yes sau No, dupa cum intr-un un camp de tip Text, Memo sau Hyperlink poate sau nu a fi introdus un sir de lungime zero.

- Indexed - stabileste un camp drept index si poate avea una din urmatoarele valori:

No - neindexat

Yes (Duplicates OK) - index si sunt permise valori duplicat pe campul de index

Yes (No Duplicates) - index si nu sunt permise valori duplicat pe campul de index

2. Stabilirea cheii primare si a indecsilor

Atunci cand se creaza un tabel, trebuie precizat un camp sau un grup de campuri prin ale caror valori o inregistrare este unica in colectie. Acest camp sau grup de campuri este cunoscut sub denumirea de cheie primara (Primary Key).

Daca utilizatorul nu precizeaza cheia primara, la salvarea structurii respective sistemul afiseaza un mesaj prin care transmite posibilitatea generarii automate a unui camp numeric de tip AutoNumber, cu numele ID, drept cheie primara.

daca se raspunde cu Yes, se genereaza campul respectiv si in el vor fi introduce automat valori incepand cu 1, in mod strict crescator, pentru fiecare inregistrare de date;

daca se raspunde cu No, se revine in Design View, se afiseaza fereastra structurii tabelului si utilizatorul isi poate preciza cheia primara din campurile existente.

Desi Access poate crea in mod implicit o cheie primara, se recomanda utilizatorului sa-si defineasca singur acest camp, deoarece in structura va apare un camp nou care nu are legatura cu celelalte, utilizatorul fiind obligat sa-i accepte denumirea si proprietatile.

Orice cheie primara este un index. Indecsii realizeaza o clasificare a datelor dupa unul sau mai multe campuri, ceea ce conduce la cresterea vitezei de realizare a interogarilor;

La alegerea campului sau campurilor de tip Primary Key trebuie respectate anumite reguli:

Access nu accepta duplicate sau neintroducere date in valorile cheii primare;

dimensiunea ei afecteaza viteza de operare in baza de date

sa fie usor de tinut minte

Pentru stabilirea cheii primare a unui tabel se procedeaza astfel:

a) Se selecteaza campul sau campurile ce constituie cheia primara:

daca un singur camp din structura tabelului formeaza cheia primara, se selecteaza intreg randul cu numele lui din zona superioara ("1") a ferestrei;

daca doua sau mai multe campuri consecutive din structura formeaza cheia primara, se selecteaza randul primului camp din cheie si apoi cu mouse-ul se selecteaza si randurile campurilor ce urmeaza in secventa;

daca doua sau mai multe campuri ce formeaza cheia nu sunt consecutive in structura, se selecteaza randul primului camp si apoi, tinand apasata tasta Ctrl, se selecteaza pe rand celelate campuri

b) dupa selectarea campurilor are loc definirea propriu-zisa a cheii primare prin utilizarea uneia din urmatoarele posibilitati:

- se selecteaza butonul de "Primary Key" din bara de unelte standard sau

- se activeaza din meniul principal functia Edit, si din submeniul afisat se alege optiunea "Primary Key"

Fig. 6 Structura unei tabele cu precizarea cheii primare

Observatie:

Crearea unei chei primare unice asigura integritatea de entitate.

Afara de cheia primara, in Access se foloseste si cheia externa. O cheie externa nu se defineste explicit in Access, ea fiind stabilita prin definirea relatiilor dintre tabele.

Pentru realizarea unei relatii intre doua tabele, obligatoriu trebuie indeplinite urmatoarele conditii:

in ambele tabele trebuie sa existe un camp comun prin care sa se faca legatura;

intr-unul din tabele acest camp trebuie sa fie cheie primara unica;

in ambele tabele campul comun trebuie sa aiba acelasi tip si aceeasi lungime

In tabelul corespondent, campul de legatura reprezinta cheia externa.

De exemplu, in studiul de caz prezentat in capitolul anterior, legatura intre tabelele Proprietari si Autovehicule se realizeaza prin campul CodProprietar. In prima tabela acest camp este cheie primara, iar in tabela Autovehicule este cheie secundara.

Campul ce reprezinta cheia externa poate fi un camp simplu (exemplul anterior) sau un camp care face parte dintr-o cheie primara multipla (exemplu: la legatura dintre tabelele FiseReparatii si OperatiiFacturate, campul cheie externa NrReparatie din tabelul OperatiiFacturate face parte din cheia primara multipla a acestuia).

Observatie:

O cheie primara nu trebuie sa fie obligatoriu si cheie externa intr-un tabel corespondent, dar o cheie externa trebuie sa fie declarata drept cheie primara in tabelul de legatura.

3. Salvarea unei tabele

Dupa definirea structurii si stabilirea cheii primare, tabelul respectiv se considera creat.

Pentru pastrarea lui pe o perioada nedeterminata de timp, va fi salvat pe o memorie externa. Salvarea tabelului respectiv se va realiza cu una din urmatoarele posibilitati:

se activeaza butonul "Save" din bara de unelte standard sau

se solicita din meniul principal functia File, iar in cadrul ei operatia Save sau Save As .

La prima salvare se va tasta numele tabelei respective.

Salvarile ulterioare se vor efectua cu Save pentru salvarea cu acelasi nume, si cu Save As pentru salvarea cu un alt nume introdus de la tastatura.

2.3. Definirea relatiilor dintre tabele

In prezentarile anterioare s-a precizat faptul ca tabelele sunt proiectate si realizate astfel incat sa functioneze atat independent, cat si in corelatie cu alte tabele din baza de date.

Pentru a avea acces simultan la datele din mai multe tabele ale aceleiasi baze de date, este absolut obligatoriu sa se stabileasca si sa se realizeze relatiile standard dintre ele. Pentru realizarea unei relatii (legaturi) intre doua tabele, este necesar sa existe un camp comun in ambele tabele. Intr-una din tabele campul respectiv trebuie sa fie cheie primara, iar in cea de-a doua tabela sa fie utilizat drept cheie externa.

Tipul si lungimea acestor doua chei trebuie sa fie obligatoriu aceleasi, altfel definirea relatiilor intre tabelele corespondente nu este posibila

In SGBD Access se recomanda ca stabilirea relatiilor intre tabelele aplicatiei sa se faca inaintea introducerii datelor in ele. In felul acesta se elimina posibilitatea de a folosi intr-o tabela inregistrari inexistente in tabela corespondenta. De exemplu, se blocheaza tastarea in tabelul Autovehicule a unui "CodProprietar" inexistent in tabela Proprietari.

In SGBD Access se pot realiza urmatoarele relatii intre tabele:

Relatia 1-1 (one-to-one) este cea mai simpla, dar si cea mai putin uzuala.. Unei valori a campului cheie primara dintr-o tabela, ii corespunde o singura valoare din campul folosit drept cheie externa din cealalta tabela. Cele doua tabele folosite intr-o astfel de relatie se pot combina in una singura, care va contine campurile din ambele tabele.

Relatia 1-n (one-to-many) este cea mai utilizata. Unei inregistrari din tabela aflata in partea stanga a relatiei, ii corespund doua sau mai multe inregistrari aflate in tabela din dreapta relatiei. Asocierea se bazeaza pe un camp cheie primara unic intr-o tabela si un camp cheie externa din cealalta tabela care admite valori duplicate.

Relatia n-1 (many-to-one) este opusa relatiei anterioare. Ea este reflexiva, fiind considerata reflexia relatiei corespondente one-to-many. Relatiile n-1 nu sunt bazate pe campuri cheie primara din nici una din cele doua tabele. Daca se selecteaza o inregistrare din tabela aflata in partea many a relatiei, se poate afla inregistrarea care corespunde cheii sale externe din tabela aflata in partea one a relatiei.

Relatia m-n (many-to-many) se poate defini prin existenta unei a treia tabele intermediare, care are relatii one-to-many si many-to-one cu cele doua tabele.

Pe langa stabilirea relatiilor intre tabelele unei baze de date, Access permite si specificarea unor reguli care forteaza integritatea datelor. Aceste reguli se numesc restrictii de integritate referentiala, care permit pastrarea intacta a relatiilor construite

Integritatea referentiala functioneaza dupa campul cheie. Acest sistem verifica permanent campul cheie, primara si externa, la fiecare operatie de adaugare, modificare sau stergere a unei inregistrari de date. Daca o schimbare a unei chei afecteaza legatura stabilita intre tabele, apare un mesaj de avertizare, prin care se transmite ca au fost violate regulile de integritate referentiala.

Atunci cand doua tabele sunt legate printr-o relatie, una din ele este considerata tabela parinte, cealalta tabela copil. Prin integritatea referentiala se garanteaza faptul ca nu vor exista inregistrari copil fara o inregistrare parinte.

Prin integritatea referentiala, Access previne aparitia unor cazuri de eroare grava a datelor intr-o baza de date, cum ar fi:

adaugarea de inregistrari noi in tabele secundare, daca in tabelul principal nu exista inregistrari asociate;

stergerea unor inregistrari din tabelul principal, daca exista in tabelul secundar inregistrari legate de acesta.

Afara de integritatea referentiala, Access asigura si integritatea entitatii, care impune ca toate valorile cheilor primare sa fie unice intr-o tabela.

Capacitatea de a asigura automat integritatea referentiala este o caracteristica importanta a sistemului Access.

Pentru realizarea efectiva a relatiilor intre tabelele unei aplicatii se parcurg urmatorii pasi:

1. Alegerea tabelelor participante

a)    Se porneste de la baza de date a unei aplicatii, deschisa in acel moment, in care se selecteaza obiectul Tables. Pentru aplicatia specificata anterior, in fereastra bazei da date vor fi afisate numele tuturor tabelelor de date participante: Proprietari, Autovehicule, Angajati, PieseSchimb, InlocuiriPiese, Operatii, OperatiiFacturate, FiseReparatii.

b)   Activand din bara de unelte standard butonul Relationships, sau solicitand din meniul principal functia Tools si in cadrul ei operatia Relationships, va apare fereastra Show Table pentru adaugarea tabelelor care participa la asociere. Aceasta fereastra apare automat in momentul crearii pentru prima data a relatiilor intre tabele. Selectam pe rand fiecare tabel din baza de date si activam butonul ADD pentru adaugarea lor in fereastra (fig. 6.9).

Fig.9 Fereastra de stabilire a relatiilor intre tabele

Dupa crearea relatiilor, la activarea aceluiasi buton sau a aceleiasi operatii, va fi afisata fereastra cu relatiile existente.

Fig. 9 Relatiile existente intre tabelele studiului de caz

Daca pe parcursul utilizarii aplicatiei apar anumite modificari ce afecteaza relatiile existente intre tabele in acel moment (de ex. apare ca necesara o noua tabela, care trebuie inclusa in relatie) este nevoie de reafisarea casetei Show Table. In aceasta situatie se alege una din urmatoarele posibilitati:

se selecteaza butonul Show Table din bara de unelte standard sau

se apasa butonul din dreapta mouse-ului pe suprafata ferestrei si se selecteaza aceeasi optiune

2. Realizarea efectiva a unei relatii

Pentru definirea unei legaturi intre doua tabele trebuie sa existe compatibilitate intre tipul de date al cheii primare si cel al cheii externe corespunzatoare.

Dupa plasarea tuturor tabelelor in panoul de editare, definirea efectiva a unei relatii se face prin drag-and-drop (selectare si glisare) dinspre cheia primara a unei tabele catre cheia externa corespunzatoare.

Ca efect va apare caseta de dialog Edit Relationships (fig. 6.10), ce contine numele celor doua tabele implicate si a campului de legatura.

Fig. 10 Fereastra Edit Relationships

Daca o cheie este formata din mai multe campuri, acestea se editeaza corespunzator in panoul Edit Relationships.

3. Stabilirea tipului de relatie si a integritatii referentiale

In fereastra Edit Relationships apar trei butoane ce pot fi activate si care au urmatoarea semnificatie:

Enforce Referential Integrity - defineste o restrictie de integritate referentiala. Nu se permite actualizarea valorii unei chei externe, daca valoarea respectiva nu exista in tabelul unde aceasta este cheie primara. La introducerea unei inregistrari noi in tabela secundara, se verifica daca valoarea cheii externe se gaseste in tabela primara, in campul corespunzator cheii primare. Acest lucru inseamna ca intai se introduc datele in tabela principala si apoi in cea secundara.

Cascade Update Related Fields - actualizarea valorii unei chei primare dintr-un tabel se propaga si in inregistrarile din tabelele unde aceasta este cheie externa.

Cascade Delete Related fields - stergerea valorii unei chei primare are ca efect si stergerea inregistrarilor din tabelele unde aceasta este cheie externa.

In aceeasi fereastra se afiseaza tipurile de legaturi care pot exista intre doua tabele:

One-to-One;

One-to-Many.

Daca in aceasta caseta apare mesajul Indeterminate, inseamna ca tipul de relatie nu poate fi definit, cea mai probabila eroare fiind incompatibilitatea intre tipul de date al cheii primare si cel al cheii externe sau inexistenta unei chei primare in relatia respectiva.

4. Stabilirea tipului unirii (Join-ul legaturii)

Dupa stabilirea integritatii referentiale se poate stabili tipul unirii prin activarea butonului de comanda Join Type. Acest buton permite definirea a trei tipuri de relatii particulare intre tabele.(fig. 6.11). Primul tip presupune selectarea numai a acelor inregistrari care se regasesc in ambele tabele. Variantele 2 si 3 permit utilizarea implicita in interogari a join-urilor externe de tip LEFT si RIGHT, care presupun  intr-o interogare luarea in considerare a tuturor inregistrarilor dintr-un tabel, chiar daca au sau nu inregistrari corespondente dupa campul de legatura in celalalt tabel

Fig. 11 Fereastra Join Type

5. Salvarea relatiilor stabilite intre tabelele unei baze de date

Legaturile definite si realizate intre tabelele unei baze de date, trebuie memorate si pastrate pe perioada functionarii aplicatiei.

Salvarea lor se realizeaza similar ca a oricarui obiect Access sau fisier:

activandu-se butonul Save din bara de unelte sau

apeland functia File si din submeniul ei operatia Save.

Observatie:

Daca s-a gresit o legatura intre doua tabele, pentru refacerea corecta a ei se procedeaza astfel:

se pozitioneaza cursorul mouse-ului pe linia de legatura ce trebuie modificata si se selecteaza;

se apasa tasta Delete pentru stergerea ei;

se redefineste noua relatie

2.4. Operatii la nivelul inregistrarilor dintr-o tabela

Dupa crearea lor, la nivelul tabelelor dintr-o baza de date pot apare diverse actiuni, fie la nivelul inregistrarii de structura, fie la nivelul continutului de date. Principalele operatii ce se pot executa sunt urmatoarele:

Actualizarea structurii unei tabele consta in efectuarea operatiilor de adaugare campuri (coloane) noi in tabela, stergerea unei coloane sau modificarea anumitor proprietati definite anterior si se realizeaza astfel.

a)    se deschide baza de date si din fereastra ei se selecteaza obiectul Tables si apoi tabela a carei structura trebuie modificata;

b)   se activeaza butonul Design sau se alege optiunea Design View din functia View;

c)    in fereastra afisata apare structura existenta, in care se pot realiza urmatoarele operatii:

- adaugarea unui camp nou la sfarsitul tabelului se realizeaza prin pozitionarea cursorului pe prima linie goala din zona "1" a tabelului si definirea numelui, a tipului avut si a proprietatilor corespunzatoare;

- adaugarea unui camp nou intre doua campuri existente se realizeaza prin plasarea cursorului pe linia corespunzatoare randului deasupra caruia inseram si apelarea operatiei Insert Line din meniul Edit;

- stergerea unei coloane din tabela se realizeaza prin pozitionarea cursorului pe numele campului respectiv si apasarea tastei Delete;

- modificarea proprietatilor specifice unui camp se realizeaza prin pozitionarea cursorului pe numele campului ce urmeaza a fi modificat ti corespunzator se selecteaza noile proprietati ale lui din zona "2" a ferestrei

La sfarsit se va salva noua structura.

Introducerea inregistrarilor de date in tabela se face astfel:

- se selecteaza obiectul Tables si apoi tabelul in care introducem date;

- prin dublu clic pe numele lui sau activand butonul Open apare tabelul prefigurat conform structurii, in care putem introduce date.

Datele se introduc prin plasarea cursorului pe inregistrarea goala din partea de jos a tabelului, indicata prin semnul " " in coloana de selectie a inregistrarii. Datele introduse in celulele acestei linii se vor constitui ca o noua inregistrare.

Stergerea de inregistrari

Pentru a sterge una sau mai multe inregistrari, acestea se selecteaza si apoi se alege una din urmatoarele posibilitati:

se actioneaza tasta Delete sau

se actioneaza butonul Delete Record aflat pe bara de unelte standard sau

din meniul Edit se alege operatia Delete record.

Indiferent de metoda aleasa, apare un mesaj prin care se solicita utilizatorului confirmarea stergerii. Daca este necesara stergerea inregistrarilor selectate, se raspunde cu Yes.

In Access nu exista posibilitatea renuntarii la stergere, motiv pentru care trebuie acordata o atentie deosebita acestei operatii.

Cautarea unei inregistrari se realizeaza in acces secvential sau direct.

Cautarea secventiala se face cu ajutorul butoanelor aflate in partea inferioara a ferestrei DataSheet: Next Record, First Record, Last Record, Previous Record sau bara de derulare verticala.

Pentru cautarea in acces direct, se selecteaza campul dupa al carui continut se realizeaza cautarea, se activeaza butonul Find de pe bara cu instrumente sau din functia Edit se alege operatia Find. Apare caseta de dialog Find in Field. In caseta de text Find What se specifica criteriul de cautare. Butoanele de comanda Find First  (gasirea primei aparitii) si Next (gasirea urmatoarei aparitii) sunt activate cand se introduc date in caseta de text.

Cautarea se poate realiza:

a)    in intreaga tabela (All);

b)   incepand cu inregistrarea curenta pana la sfarsitul tabelei (Down);

c)    de la inregistrarea curenta spre prima inregistrare (Top) sau

d)   de la ultima inregistrare spre inceputul tabelei (Up).

Aceste optiuni se pot preciza in zona Search.

Din lista derulanta Match se pot selecta urmatoarele optiuni:

Whole Field - campul intreg;

Start of Field - inceputul campului;

Any Part of Field - orice parte a campului

Pentru a preciza o cautare care sa tina seama de literele mari sau mici, se marcheaza caseta de validare Match Case.

Modificarea continutului inregistrarilor de date

Pe parcursul utilizarii unei aplicatii pot apare modificari in continutul unor campuri. Pentru a introduce valoarea noua, corecta in locul celei eronate, se pozitioneaza cursorul in campul respectiv si de la tastatura se opereaza modificarea.

Pentru inlocuirea automata a valorilor dintr-un camp, din mai multe inregistrari, se utilizeaza caseta de dialog Replace in Field, care se afiseaza prin meniul Edit, operatia Replace.

Aceasta comanda va inlocui valorile din campurile care au aceleasi valori ca cele introduse in caseta de text Find What

Studiu de caz  nr. 3

Pentru aplicatia "Evidenta cartilor din biblioteca Universitatii Romano-Americane" sa se realizeze fizic pe calculator urmatoarele actiuni:

Crearea bazei de date Access cu numele "Biblioteca"

Crearea tabelelor corespunzatoare entitatilor stabilite la lucrarea de verificare nr.2

Realizarea relatiilor dintre tabele, cu respectarea integritatii referentiale

Introducerea datelor in tabele

Realizarea urmatoarelor interogari;

Afisarea cartilor de informatica din biblioteca

Afisarea cartilor unui anumit autor

Afisarea cartilor imprumutate si nerestituite

Afisarea cartilor imprumutate intr-o anumita perioada de timp

Afisarea cartilor procurate de la o anumita editura, cu pretul lor

Afisarea valorii cartilor pe domenii de activitate

Afisarea valorii cartilor pe autori

Studiu de caz nr. 4

Firma TIRIAC AUTO SA doreste sa faca o analiza a datelor despre automobilele existente in diferite magazine ale sale.

Se cere:

Sa se proiecteze si sa se realizeze baza de date adecvata

Definiti olegatura de tip 1-M intre tabela primara LOCALITATI si tabela legata (secundara) MASINI. Selectati optiunea de integritate referentiala si optiunea cascada pentru legatura dintre cele doua tabele

Pentru toate vehiculele afisati campurile: Model, An, CodLoc, PretVanzare. Salvati cererea de date cu numele INFO MASINI si lansati-o in executie

Pentru toate vehiculele fabricate in 2008 afisati campurile: Model, An, Cost, PretVanzare, NumeLoc. Sortati cererea dupa campul Cost in ordinea descrescatoare. Salvati cererea cu numele MASINI 2008 si rulati-o. Modificati structura cererii prin eliminarea cimpului An si salvati ultima varianta

Pentru toate vehiculele aflate in magazinele din Bucuresti si cu transmisie A4, afisati campurile Model, An, Cost, PretVanzare, Transmisie, CodLoc, NumeLoc

Pentru toate vehiculele afisati campurile: Model, An, Cost, PretVanzare. Adaugati in structura cererii un nou camp: Profit= PretVanzare - Cost. Salvati cererea si afisati rezultatul

Din tabela MASINI calculati costul total, costul mediu si pretul de vanzare mediu pentru toate masinile, in ordinea cronologica a datei vanzarii

Afisati masina cea mai scumpa si masina cea mai ieftina