CREAREA RELATIILOR INTRE TABELE

SCOPUL LUCRARII

Intr-o BD Access 95 bine proiectata datele sunt stocate in mai multe tabele. Deoarece Access este un SRGBD inseamna ca el permite asocierea si extragerea informatiilor inrudite, care sunt memorate in tabele. Altfel, o BD ar fi lipsita de sens.

Retineti : tabelele dintr-o BD relationala sunt corelate !

Si in aceasta lucrare se vor discuta aspectele teoretice avand ca baza de discutie exemplul firmei de calculatoare, pe care l-am dezvoltat pana acum pe parcursul celorlalte lucrari de laborator. Se vor construi toate relatiile pe care acea baza de date le implica, astfel incat sa putem obtine orice informatie dorim. Cu alte cuvinte, relatiile pe care le vom crea reprezinta acel set optim si minim de legaturi intre tabele, care ne furnizeaza toate informatiile dorite( nu exista redundanta a legaturilor).

In desfasurarea lucrarii sunt prezentate toate tabelele acestei baze de date, precum si toate relatiile logic posibile.

PREZENTARE TEORETICA

CONDITIILE PENTRU REALIZAREA UNEI RELATII ( SAU ASOCIERI )

Pentru ca doua sau mai multe tabele sa permita asocierea este obligatoriu ca ele sa contina unul sau mai multe campuri, care prin constructie sa identifice unic fiecare inregistrare din acel tabel . Un astfel de camp se numeste cheie primara. Corelarea se realizeaza apoi prin introducerea unor campuri cu acelasi nume si de acelasi tip de data ( sau compatibil) in fiecare tabel dintre cele care se doresc a fi asociate.

Campurile comune prin care se face asocierea sunt cheie primara pentru un tabel si cheie externa ( secundara) pentru celalalt. Se recomanda ca acest camp sa fie identic in toate tabelele corelate, iar tipul lor sa fie acelasi sau compatibil. Procedand astfel Access poate crea automat corelatiile atunci cand tabelele sunt parte a unei interogari.

In concluzie, retineti ca pentru a crea o relatie intre tabele trebuie :

sa selectati acele tabele pe care le doriti legate, functie si de anumite criterii logice si inrudiri intre ele ;

tabelele trebuie sa contina fiecare campurile comune ;

campurile pe care se va baza relatia sa contina valori cu acelasi nume si de acelasi tip de data

( sau compatibil).

TIPURI DE RELATII

Sunt de trei tipuri :

1-la-1 ( one to one) ;

1-la-n ( one to many) ;

n-la-n ( many to many) .

In ordinea in care ele apar cel mai des avem :

1-la-n ( one-to-many)

Este cea mai frecvent intalnita si cea mai utila.

Prin definitie intr-o relatie 1-la-n o inregistrare din tabelul din partea "unu" a relatiei poate avea mai multe inregistrari corespondente in tabelul din partea "mai multi", dar o inregistrare din tabelul din partea "mai multi" poate avea cel mult o inregistrare in tabelul din partea "unu" .

Pentru crearea acestei relatii adaugam campurile care constituie cheia primara din tabelul din partea "unu" , drept chei externe in tabelul din partea "mai multi".

Exemplu : Un exemplu de astfel de relatie este dat in figura 1 . Acolo apare relatia dintre tabelele 'tblCalculator' si 'tblDepartament'. Observati cum este legata cheia primara a tabelului 'tblDepartament' de cheia secundara 'departamentID' din tabelul 'tblCalculator'. Interpretarea este urmatoarea : mai multe calculatoare pot apartine unui departament. O alta relatie de acest tip este intre 'tblDepartament' si 'tblOperator' : mai multi operatori pot lucra intr-un departament. Relatia nu se poate inversa. La fel intre 'tblFunctie' si 'tblOperator' : mai multi operatori pot avea aceeasi functie, dar un operator nu poate avea mai multe functii. In sfarsit, relatia intre 'tblSoftware' si 'tblLicenta' : mai multe pachete software pot avea o singura licenta. Invers nu se poate.

Fig. 1 - Fereastra Relationships: toate relatiile 1-la-n ale bazei de date din exemplu .

n-la-n ( many-to-many)

Este cea mai generala.

Prin definitie, in acest tip de relatie o inregistrare din tabelul "unu" poate avea mai multe inregistrari in tabelul "mai multi" si reciproc, o inregistrare din tabelul "mai multi" poate avea mai multe Inregistrari in tabelul "unu" Pentru a ne da seama de o astfel de relatie ea trebuie studiata din ambele directii !

Solutia pentru crearea unei astfel de relatii este un tabel intermediar care separa relatia n-la-n intr-o pereche de relatii 1-la-n . In acest tabel inermediar vom pune cheile primare din ambele tabele ale relatiei n-la-n .De exemplu relatia intre cititor si carte. Privita dinspre cititor: un cititor poate imprumuta mai multe carti. Privita dinspre carte: o carte poate fi imprumutata mai multor cititori( exista mai multe exemplare disponibile dintr-un volum).

Exemplu : Sunt posibile urmatoarele relatii n-la-n :

Mai multe calculatoare pot rula acelasi program, asa cum si mai multe programe pot rula pe un acelasi calculator. Conform cu prezentarea teoretica am descompus relatia mai multe programe - mai multe calculatoare intr-o pereche de relatii 1-la-n. Tabelul intermediar care poate fi creat este 'tblSWCalc', in care introducem cheile primare din tabelul 'tblSoftware' si din tabelul 'tblCalculatoare'( vedeti figura 2) .

Mai multi operatori pot lucra pe acelasi calculator si invers pe mai multe calculatoare poate lucra un singur operator. Am descompus aceasta relatie intr-o pereche de relatii 1-la-n prin tabelul intermediar 'tblCalcOperator'( vedeti figura 2).

Mai multi operatori pot lucra cu acelasi program si mai multe programe pot fi rulate de un

singur operator. Aceasta relatie este posibila prin intermediul celor deja existente. Daca se creaza totusi si aceasta relatie se ajunge la redundanta.

Fig. 2 - Relatiile n-la-n separate in doua relatii 1-la-n cu ajutorul unor tabele intermediare

( 'tblSWCalc' si 'tblCalcOperator').

In aceste tabele apar atatea campuri de tipul cheilor primare ale tabelelor aflate in legatura, cate chei primare sunt implicate( vezi tabelele date in 'Desfasurarea lucrarii').

1-la-1 ( one-to-one)

Aceasta este cea mai rar intalnita.

Prin definitie, in acest tip de relatie o inregistrare din tabelul "unu stanga" poate avea cel mult o inregistrare corespondenta in tabelul "unu dreapta" si reciproc, o inregistrare din tabelul "unu dreapta" poate avea cel mult o inregistrare corespondenta in tabelul "unu stanga" .

Aceasta relatie este similara cu un singur tabel in care se introduce un camp nou. Astfel tabelul cuprinde campurile din ambele tabele ce apartin relatiei.

Pentru crearea acestui tip de relatie intalnim situatiile :

daca ambele tabele au acelasi subiect ( adica aceeasi cheie primara) stabilim relatia punand acelasi camp in ambele tabele ;

daca tabelele au subiecte diferite, cu chei primare diferite, alegem unul dintre tabele si ii punem cheia primara in celalalt tabel, pe post de cheie externa ( sau cheie straina). In tabelul care primeste cheia straina trebuie sa stabilim proprietatea 'Indexed' a acestui camp la

'Yes ( No Duplicates)'.

Exemplu : in baza de date analizata putem crea o astfel de relatie intre tabelele 'tblSoftware' si 'tblLicenta'. Nu este posibil ca un pachet software sa aiba mai multe licente de functionare si nici invers, adica nu putem avea mai multe pachete de programe cu aceeasi licenta. Cheia secundara 'licentaID' din 'tblSoftware' ia valorile cheii primare a tabelului 'tblLicenta', dar fara duplicate.

Fiecare pachet de programe are astfel o singura licenta din cele disponibile, creindu-se astfel intre cele doua tabele o corespondenta biunivoca ( vedeti figurile 3, 4 si 5) .

Cand este folosita o relatie 1-la-1 ?

in cazul structurilor foarte mari, care depasesc limita maxima de 255 campuri intr-un tabel ;

cresterea eficientei, in cazul in care numai cateva inregistrari au nevoie de campuri aditionale ;

asigurarea securitatii datelor, in cazul in care campurile din cel de-al doilea tabel sunt facute disponibile doar persoanei care supervizeaza .

Fig. 3 - Proprietatea 'Indexed' a campului cheie secundara ( aici 'licentaID') trebuie stabilita la 'Yes ( No duplicates)' pentru a se obtine o relatie 1-la-1 intre doua tabele cu chei primare diferite. Daca aceasta proprietate nu se stabileste astfel, atunci ceea ce cream este o relatie 1-la-n obisnuita.

Fig. 4 - Fereastra 'Relationships' unde am creat o relatie 1-la-1.

Campul cheie secundara 'licentaID'

este cel pentru care am stabilit proprietatea Indexed la 'Yes( No duplicates

Fig.5 - In fereastra de proprietati ale unei relatii ( acesta va fi descrisa ulterior) se observa tipul relatiei : 1-la-1.

Relatiile se construiesc pe baza urmatoarelor tabele ( figura 6):

Fig. 6 - Tabelele bazei de date al exemplului ales.

Relatiile posibile sunt cele din figura 6.1 :

Fig. 6.1 - Toate relatiile neredundante din baza de date a exemplului ales .

DESFASURAREA LUCRARII

Pentru exemplul din lucrarile 1 si 2 se realizeaza relatiile intre tabelele existente. Se analizeaza tipurile de relatii si asocieri intre tabelele realizate.

a.       Creati cate o relatie de tip 1-la-1, 1-la-n si n-la-n folosindu-va de exemplul de baza de date pe care l-ati ales la fiecare statie de lucru.

b.      Analizati exemplul de baza de date pe care il aveti si apoi creati toate relatiile neredundante ale bazei de date alese de dvs. astfel incat sa nu existe informatie pe care sa nu o puteti obtine .

CREAREA RELATIILOR

Pentru a crea o relatie urmati pasii urmatori :

Pasul 1 :

Se selecteaza fereastra Database si se alege optiunea Relationships( adica relatii) din meniul Tools ( figura 7) .

Fig. 7 - Optiunea Relationships din meniul Tools : aceasta porneste procesul de creare a unei noi relatii.

Tabelele necesare unei relatii - minim doua pentru ca o relatie sa existe - trebuie adaugate in fereastra 'Relationships' ( figura 8) . Aceasta se poate face in trei moduri :

: prin intermediul ferestrei 'Show Tables' ( figura 9), unde se selecteaza cate un tabel din cele dorite si apoi se apsa butonului Add ; in aceasta fereastra se poate intra doar daca fereastra Relationships este activa !

Cum se intra in ferastra Show Table? Prin apasarea iconitei : din bara cu instrumente ( toolbar) ;

Dupa ce s-au introdus in fereastra Relationships toate tabelele, in fereastra 'Show Table' se apasa butonul Close .

Fig.8 - Fereastra 'Relationships '.

Fig. 9 - Fereastra 'Show Table' din care, dupa selectarea fiecarui tabel dorit, se apasa butonul Add pentru transferul in fereastra Relationships. Incheierea se face cu butonul Close.

executand un clic-deapta de mouse oriunde in interiorul ferestrei Relationships, dar nu pe

tabele sau relatii. Apare meniul din figura 10. Alegeti optiunea 'Show Table' ;

Fig. 10 - A doua posibilitate de selectare a tabelelor dorite pentru o relatie.

: tragand cu mouse-ul ( drag-and-drop) tabelele respective din fereastra 'Database' in fereastra 'Relationships' .

Pasul 2

In fereastra Relationships, unde sunt adunate acum toate tabelele dorite, puteti stabili o relatie prin selectarea unui camp din tabelul primar si tragerea cu mouse-ul

( drag-and-drop) a acestuia peste campul corespunzator din tabelul asociat.

Nu uitati ! In orice relatie unul din tabele este primar ( partea "unu" a relatiei) si celalalt este tabelul asociat ( partea " mai multi" a relatiei) .

Va aparea automat fereastra din figura 11, unde dumneavoastra stabiliti detaliile relatiei .

Fig. 11 - Fereastra de stabilire a detaliilor relatiei.

Aici apar numele tabelelor si campurilor implicate in relatie:

in coloana 'Table/Query' apare numele campului din tabelul primar ;

in coloana 'Related Table/Query' apare numele campului din tabelul asociat .

Denumirile acestor campuri se pot modifica daca este nevoie prin pozitionarea cu mouse-ul pe linia respectiva si executarea unui clic-stanga pe indicatorul sageata-jos. In partea de jos este numit si tipul de relatie : one-to-one, one-to-many sau many-to-many.

Tot aici intalnim si caseta de validare 'Enforce Referential Integrity' ( Impune

Integritatea Referentiala). Marcarea acestei optiuni va ofera posibilitatea de precizare a unor actualizari/stergeri in cascada, posibile in cadrul acestei relatii.

Observati in partea de jos a ferestrei tipul relatiei in curs de creare. In figura data

este vorba despre o relatie "one-to-many"( 1-la-n) .

Butonul 'Join type' activeaza ferestra din figura 12 , unde precizati tipul de

combinare prestabilit, pentru a fi utilizat in interogari . Vedeti paragrafele unde sunt explicate tipurile de asociere.

Fig. 12 - Stabilirea tipului asocierii din cadrul unei relatii .

O relatie nou-creata este simbolizata printr-o linie ce uneste tabelele implicate in relatie. Pe unul din capetele liniei apare numarul "1", iar pe celalalt capat al liniei apare semnul "∞". Prin aceasta simbolistica identificati usor care este tabelul din partea "unu" a relatiei si care este cel din partea "mai multi" .

Cum se sterge un tabel ?( din cadrul ferestrei Relationships)

Nu este vorba de stergerea unui tabel cu tot ceea ce contine el, ci de stergerea unui tabel nedorit in fereastra Relationships . O astfel de stergere nu determina stergerea relatiei ! in care este el implicat, ci suprima pur si simplu afisarea liniei ce semnifica relatia existenta.

Cum se sterge o relatie ?

Pentru a sterge o relatie executati un clic-stanga pe linia care uneste doua tabele, in fereastra Relationships. Apoi apasati tasta Del. Sau faceti un clic-dreapta pe linia ce reprezinta relatia si apoi alegeti optiunea 'Delete'. Apare mesajul de mai jos, unde raspundeti cu Yes

( evident) .

Confirmarea stergerii unei relatii.

Pasul 3:

Noua relatie trebuie salvata.

Salvarea se face astfel :

fie apasati pe iconita cu discheta :

fie, la inchiderea ferestrei Relationships, trebuie sa confirmati cu Yes la intrebarea de mai jos: 

Confirmarea salvarii unei relatii .

INTEGRITATEA REFERENTIALA

S-a pomenit in paragrafele anterioare despre aceasta optiune. In continuare veti afla despre ce este vorba.

Este posibil ca intr-un tabel oarecare sa se stearga una sau mai multe inregistrari. Daca acestea erau referite in anumite relatii sau in cadrul unor formulare si interogari, rezulta ca toate acestea vor fi compromise.

Se spune ca integritatea referentiala a BD respective a fost compromisa .

Intern, o verificare a integritatii unei BD inseamna sute de linii de cod( si poate mult mai multe) . Este deci mult mai bine sa evitam din faza de proiectare astfel de situatii.

Ce implica integritatea referentiala ?

Pentru a crea o relatie care cere integritate referentiala, campurile corespunzatoare din tabele trebuie sa fie de acelasi tip si sa aiba aceiasi dimensiune. De exemplu, nu se poate crea o relatie intre doua campuri intre care unul este de tipul text si celalalt de tipul numar, sau unul este de tip virgula mobila-single iar celalalt intreg.

! Atentie : unui camp de tip AutoNumber din tabelul primar trebuie sa-i corespunda in

tabelul asociat tot un camp AutoNumber sau un camp compatibil( intreg lung sau ReplicationID).

Cand se selecteaza optiunea 'Referential Integrity', mediul Access insista ca unei introduceri de date in campul cheii straine sa-i corespunda o introducere de date de acelasi tip si in tabelul primar ( adica sa am o data in tabelul primar la care sa fac referinta in tabelul asociat ).

Exista insa si situatii in care se poate ca unei date din tabelul asociat sa nu-i corespunda nimic in tabelul primar. Este cazul unei donatii anonime. Astfel, desi intr-un ipotetic tabel Donatii am completat o inregistrare( deci o noua donatie), aceasta poate sa nu aiba o corespondenta in tabelul Donator ( care este tabelul primar din relatie) .

Optiunea de integritate referentiala vine cu doua operatii : 'Actualizare in cascada' ( Cascade Update) si 'Stergere in cascada' ( Cascade Delete). Ele pot fi activate independent una de alta, sau in acelasi timp.

In fereastra din figura 13 se prezinta caseta de dialog corespunzatoare optiunii 'Referential Integrity'. Se observa casutele de marcare ale optiunilor tip cascada, sub cea de marcare a optiunii 'Enforce Referential Integrity'.

Fig. 13 - Optiunea 'Referential Integrity' impreuna cu actiunile asociate ( 'Cascade Update' si 'Cascade Delete') se marcheaza in aceasta fereastra ( observati indicatorul mouse-ului).

Ce inseamna actualizare in cascada ?

Aceasta semnifica faptul ca atunci cand se face o modificare asupra datelor continute intr-o inregistrare a unui tabel primar, ea este reflectata in toate inregistrarile corespunzatoare din tabelele asociate.

Pare o optiune ce ne da putere in manipularea relatiilor. Da, este o optiune puternica, dar poate duce si la dezastre daca nu este folosita cu mare grija.

Retineti ca : aceasta optiune nu-si are rostul in cazul campurilor cu tip care interzice modificarea. Este cazul campurilor AutoNumber care nu permit modificarea lor !

Ce inseamna stergere in cascada ?

Ca si actualizarea in cascada, si stergerea in cascada are aceleasi avantaje( cand este folosita unde trebuie) si dezavantaje( la o folosire incorecta).

Aceeasi remarca finala ca si la actualizarea in cascada.

Obs.: Este posibil ca o cheie primara sa fie multipla, si atunci pe langa campul de tip AutoNumber apare, sa spunem, si un camp de tip text, de exemplu numele de familie. Evident, campul de tip text poate fi inlocuit. Este cazul schimbarii numelui de familie dupa o casatorie. Optiunea de stergere in cascada poate fi acum activata, deoarece stim exact ceea ce se va intampla.

TIPURI DE ASOCIERI ( Join types)

O relatie, atunci cand este creata, capata neaparat anumite proprietati.

Una dintre acestea este tipul de asociere .

La ce ajuta tipul de asociere ?

Asocierea stabileste modul in care inregistrarile din niste tabele asociate vor fi combinate intr-o interogare.

Cum se stabileste tipul de asociere al unei relatii ?

Din fereastra din figura 6, prin apasarea butonului 'Join type' se ajunge in fereastra din figura 14. Acesta este fereastra 'Join Type'. Aici aveti trei posibilitati de alegere, fiecare excluzandu-le pe celelalte ( neputand fi aleasa decat una singura din cele trei).

O asociere poate fi de trei tipuri : interna, externa si intrinseca.

Asocierea interna ( 'inner join'):

Este tipul prestabilit de asociere, si apare prima in fereastra Join Type din figura 14.

Fig. 14 - Marcarea asocierii interne in fereastra Join Type.

Este cea mai cunoscuta si mai flexibila.

O interogare cu asociere interna intoarce numai acele inregistrari care isi corespund in ambele tabele. Deci mereu perechi interogare-interogare. Interogarile trebuie sa fie prezente in ambele tabele.

Asocieri externe( 'outer join') :

Se stabilesc prin marcarea celei de-a doua sau celei de-a treia optiuni in fereastra 'Join Type' ( vezi figura 15).

Fig. 15 - Marcarea primei variante a asocierii externe : asocierea externa stanga.

O interogare cu asociere externa selecteaza toate inregistrarile dintr-un tabel, impreuna cu datele care corespund din celalalt tabel.

Sunt doua posibilitati de realizare :

asociere externa stanga : selectarea tuturor inregistrarilor din tabelul din partea "unu" a relatiei impreuna cu toate informatiile care corespund din tabelul din partea "mai multi" . Corespunde optiunii 2 din fereastra Join Type ( figura 15) ;

asociere externa dreapta : selectarea tuturor inregistrarilor din tabelul din partea "mai multi" a relatiei impreuna cu toate informatiile care corespund din tabelul din partea "unu". Corespunde optiunii 3 din fereastra Join Type ( figura 16) .

Fig. 16 - Marcarea celei de-a doua variante a asocierii externe : asociere externa dreapta .

Asocieri intrinseci ( 'self join'):

Optiunea aceasta este folosita pentru asocierea unui camp dintr-un tabel cu un camp din acelasi tabel. De exemplu un tabel cu caini de expozitie. Aici se poate introduce un camp care sa semnifice parintele unui anume caine. Ar trebui ca acest camp sa faca parte dintr-un tabel Parinte, tabel care sa fie legat de cel primar( al cainilor) prin campul IdentificatorParinte ( ParinteID).

Dar, deoarece parintele este tot un caine, inregistrarea pentru parinte va apartine aceluiasi tabel( al cainilor).

In cazul unei interogari cu o astfel de asociere, tabelul cauta in el insusi pentru a afla informatiile cerute. Este o metoda de asociere performanta, deoarece permite cautari recursive.

Cum se realizeaza o asociere intrinseca ?

Se alege din fereastra 'Database' un anume tabel si se transfera in fereastra 'Relationships', dupa metodele aratate. Se aduce apoi, din nou, acelasi tabel in fereastra Relationships. Acest al doilea tabel identic cu primul va purta numele 'tabel_1'. Se trage campul Identificator din tabelul copie peste campul de legatura dorit apartinand tabelului original, exact in modul cum procedati daca ati avea tabele diferite. Dupa aceasta se urmeaza procedura standard de creare a unei relatii.

OBS. : Dintre modurile de asociere, in majoritatea situatiilor se alege asocierea interna drept asociere implicita ( default).

INTREBARI

Ce se numeste cheie primara ? Dar cheie secundara ?

Care sunt tipurile de relatii ?

Care este regula de baza in crearea oricarei relatii ( relativ la campurile care se leaga) ?

Prin ce se caracterizeaza o relatie n-la-n ?

Ce tip implicit de asociere foloseste Access ?

Descrieti integritatea referentiala .

Ce consecinte are validarea optiunii de actualizare in cascada ? Dar cea de stergere in cascada ?

Intr-o relatie 1-la-1 pot exista valori duplicate ale cheii secundare ? Ce se intampla in cazul in care avem duplicate ?