Sistemul de Gestiune a Bazelor de Date ACCESS Lucrarea de laborator

Sistemul de Gestiune a Bazelor de Date ACCESS

In aceasta lucrare este prezentata  modalitatea de creare si administrare a bazelor de date in sistemul de gestiune a bazelor de date Access, care este o componenta a pachetului Microsoft Office. Access ofera posibilitati modeste de administrare a datelor si asigurare a securitatii tranzactiilor si, de aceea se foloseste in aplicatii mici de baze de date. In schimb, instrumentele de creare a bazei de date si a aplicatiilor sunt deosebit de flexibile si 'prietenoase', in cea mare parte permitind proiectare vizuala intr un mediu integrat. De aceea, mediul Access poate fi considerat un bun punct de pornire pentru studierea proiectarii aplicatiilor de baze de date.

Cand se lanseaza programul Access, pe ecran apare o fereastra de dialog care ofera posibilitatea creerii unei baze de date noi sau deschiderea uneia deja existente (cum este, de exemplu, baza de date Northwind care se gaseste in Microsoft OfficeOfficeSamplesNorthwind.mdb). In Access, nu se pot deschide simultan mai multe baze de date. Se pot lansa insa mai multe copii ale programului Access si deschiderea in fiecare din ele a unei baze de date. Pentru selectarea si crearea obiectelor se lucreaza cu fereastra Database. Modul de afisare Database include bara de meniuri Database, bara de instrumente Database si fereastra Database care ocupa partea centrala a suprafetei de lucru. Din cele sase tipuri principale de obiecte pe care programul Access le accepta vor fi referite ulterior urmatoarele patru:

Tabele (Tables) corespund relatiilor din modelul relational si organizeaza pe linii si coloane datele stocate

Interogari (Queries) sunt cererile adresate tabelelor pentru extragerea sau organizarea datelor intr-un anumit mod si afisarea acestora. Sunt proiectate de obicei cu ajutorul unei interfete grafice si sunt memorate in limbajul standard structurat de interogare (Structured Query Language- SQL).

Formulare (Forms) ofera o interfata atragatoare pentru introducerea datelor in baza de date sau pentru vizualizarea datelor pe ecran. Formularele din Access sunt folosite ca principala interfata cu utilizatorul in aplicatiile de baze de date prin mascarea (ascunderea) tabelelor cu informatii brute.

Rapoarte (Reports) ofera rezultatele interogarilor pentru a fi tiparite in forma dorita pentru aplicatia respectiva.

Pentru fiecare tip de obiect Access se pot folosi diferite comenzi date prin butoanele laterale ale ferestrei Database:

Open: deschiderea obiectului (ceea ce inseamna 'vizualizare' sau 'afisare' pentru tabele si formulare si 'executie' pentru interogari).

Desing: proiectarea obiectului (editarea obiectului selectat)

New: crearea unui obiect nou de tipul curent.

1.  Crearea tabelelor in Access

Pentru crearea sau modificarea tabelelor, in fereastra Database, se selecteaza comanda Tables care afiseaza panoul cu toate tabelele existente. La comanda de creare a unei tabele noi (comanda New), se deschide o fereastra de dialog (New Table) in care sunt prezentate mai multe optiuni de afisare si creare

Datasheet View prezinta o foaie de calcul alba in care se introduc valorile datelor. Daca nu se definesc tipurile de date in modul de afisare Design, programul Access le intuieste singur.

Design View este o grila in care se pot selecta definitiile datelor dintr-o lista; in acest mod de afisare nu se introduce practic nici o valoare.

Table Wizard este un program expert care, dupa alegerea unei baze de date predefinite, conduce procesul de selectare a campurilor si de stabilire a cheilor si a sistemului de relatii.

Import Table este o metoda folosita pentru a importa  o tabela de date dintr-un alt fisier, creat in programul Access sau intr-o alta aplicatie de baza de date care este recunoscuta de catre Access.

Link Table opereaza la fel ca metoda anterioara, dar datele externe raman in fisierul extern.

Atunci cand se creaza o tabela noua in modul de afisare Design View, pe ecran apare o fereastra care are in partea superioara 'grila de campuri' (Field Grid)- locul in care se introduc numele si se specifica tipul campurilor care vor alcatui tabela. Panoul din partea de jos, denumit 'proprietatile campurilor' (Field Properties) permite modificarea proprietatilor fiecarui camp din tabela.

Un nou camp intr-o tabela se creaza astfel:

Se introduce un nume in coloana Field Name.

Se selecteaza un tip de date in coloana Data Type din caseta combinata corespunzatoare.

Optional se poate introduce in coloana Description un comentariu care descrie modul de utilizare a campului.

Pentru a introduce un nou camp, grila se poate parcurge tabelar.

La inchiderea ferestrei modului de afisare Design View, Access salveaza modificarile efectuate in tabela originala, in cazul editarii unei tabele existente, sau solicita introducerea unui nume pentru tabela nou creata.

Tipul de date selectat pentru fiecare camp in parte determina modul de stocare folosit de Access. De aceea, selectarea tipului corect de date pentru fiecare camp este un element important in vederea obtinerii unor informatii corecte din baza de date. In aplicatie se pot folosi tipurile de date Text (un set de max. 50 caractere), Number (un numar intreg sau in virgula mobila), Date/Time, AutoNumber (un numar intreg care este incrementat automat pe masura ce sunt introduse noi inregistrari intr-o tabela).

Pentru fiecare tabela trebuie sa fie specificata cheia primara (Primary Key) care este o submultime a campurilor (atributelor) tabelei cu proprietatea ca are valoare unica pe toate randurile (tuplurile) tabelei. Cheia primara se poate stabili pe unul sau mai multe campuri, prin selectarea acestora si actionarea comenzii Primary Key din bara de instrumente (care are ca pictograma o cheie de lacat).

Celelalte proprietati ale unui camp din tabela se stabilesc in panoul Field Properties si depind de tipul de date al acestuia si de calitatea de a apartine cheii primare sau nu. Toate tipurile de date prezinta mai multe proprietati (optiuni), dintre care unele pot fi configurate. In general, optiunile prestabilite de programul Access sunt satisfacatoare pentru cele mai multe campuri de date si numele lor sunt suficient de explicative. O atentie mai deosebita trebuie sa fie acordata proprietatilor: 'camp cerut' (Required), 'admite lungime zero' (Allow Zero Length)  si 'camp indexat' (Indexed).

REFERAT: Programarea liniara - problema clasica de transport REFERAT: Documentatie Proiect SCD - controlul Traficului Feroviar

Un camp pentru care se selecteaza optiunea Yes pentru proprietatea Required este un camp in care nu se admit valori NULL; daca se selecteza optiunea No, atunci valoarea acestui camp poate sa fie specificata sau nu, nespecificarea valorii insemnand o valoare de NULL pentru acel camp.

Proprietatea Allow Zero Length este prezenta numai pentru tipul de date Text. Optiunea Yes pentru aceasta proprietate valideaza acceptarea unui text de lungime zero, iar optiunea No invalideaza  un text de lungime zero.

O alta proprietate a campurilor care poate fi configurata este proprietatea Indexed. Daca se selecteaza una din optiunile Yes(No Duplicates), sau Yes(Duplicates OK) programul Access creaza un index (o structura de date diferita de tabela insasi), care este folosit pentru cautarea rapida a inregistrarilor dupa valoarea acelui camp. Atunci cand nu se admit duplicate, trebuie ca valorile din campul indexat sa fie diferite in inregistrarile tabelei. Acest lucru se asigura automat daca acel camp este cheia primara; daca campul nu este cheie primara, atunci valorile introduse sunt verificate si se rejecteaza acele inregistrari care au valori duplicat in campul astfel indexat. Pentru un camp care constitue singur cheia primara, nu se admite index cu duplicate.

2.  Introducerea datelor in tabelele bazei de date Access

In aplicatiile Access, pentru introducerea si afisarea datelor se folosesc formulare (Forms). Formularele pot fi proiectate in mod vizual astfel incat sa prezinte utilizatorului datele intr-un aspect cat mai sugestiv si, in acelasi timp, sa faciliteze modul de introducere a datelor de catre utilizator (prin selectii ale valorilor din seturi de valori permise, prin afisarea unor mesaje sugestive atunci cand se introduc valori eronate, etc).

In afara de posibilitatea de introducere a datelor prin intermediul formularelor, in timpul proiectarii bazei de date, programatorul poate sa introduca direct date in tabele, pentru a verifica diferite asocieri si evenimente care vor avea loc in cursul exploatarii bazei de date. Pentru acesta, se deschide (cu comanda Open) tabela selectata, si tabela este afisata direct antr-o fereastra care contine denumirile coloanelor tabelei si toate liniile (inregistrarile) existente. O noua inregistrare se poate adauga la sfarsitul tabelei, prin completarea directa a valorilor din toate campurile cerute. Introducerea de date eronate (care nu respecta tipurile de date sau proprietatile campurilor) este semnalata ca eroare si introducerea trebuie sa fie reluata.

Acest mod de introducere si afisare de date nu poate fi utilizat decat de proiectant si este de neconceput ca utilizatorii sa poata accesa astfel tabelele bazei de date. Pentru primele experimentari de proiectare in Access se va utiliza, totusi acest mod simplu de introducere si afisare a datelor din tabele, dat fiind ca nu prezinta nici un pericol de alterare a unor date importante.

3.  Crearea interogarilor in Access

Interogarile (Queries) memoreaza comenzile de operare asupra tabelelor si de returnare a rezultatului. Comenzile de interogare se transmit bazei de date ca si comenzi SQL (asa cum s-au prezentat in prima lucrare), dar mediul Access ofera o modalitate mai simpla de construire a interogarilor si anume printr-un limbaj de interogare prin exemple (Query by Example - QBE). Acest limbaj ofera o interfata utilizator care faciliteaza formularea interactiva a interogarilor folosind variabile de domeniu sau constante pentru a forma modelul (schema conceptuala) a tuplurilor (inregistrarilor) rezultat. O interogare formulata de utilizator prin QBE este convertita de mediul Access in comenda SQL echivalenta, care poate fi vizualizata selectand optiunea SQL View (mod de afisare SQL) din meniul View.

Pentru a crea o interogare folosind limbajul QBE, in fereastra Database, se selecteaza panoul Queries, si apoi comanda New. Pe ecran este afisata caseta de dialog New Query care contine mai multe optiuni pentru crearea interogarilor. Pentru exemplificarea modului de creare a unei interogari, se va alege optiunea Design View. La comanda OK cu aceasta selectie, se deschide caseta de dialog Show Table si afiseaza grila  de construire a interogarii QBE.

Deoarece majoritatea interogarilor se bazeaza pe date existente in tabele, mediul Access asteapta ca utilizatorul sa selecteze o sursa de date valida care poate fi atat tabela, dar si o interogare existenta in baza de date, deoarece ambele tipuri de componente returneaza obiecte de tip tabela, din care interogarile pot extrage date. Dupa selectarea unei surse de date, caseta de dialog Show Table poate fi inchisa pentru a elibera ecranul si a vedea intreaga grila QBE. Aceasta grila este formata din doua sectiuni: sectiunea din partea de sus contine tabelele (sau interogarile) selectate, cu prezentarea asocierilor dintre acestea, iar sectiunea din partea de jos a ferestrei contine grila interogarii (Query Grid). si este cea in care se lucreaza efectiv. Randurile din grila interogarii sunt folosite astfel:

Field (Camp). Intrarea in fiecare celula de pe acest rand este numele unui camp din sursa de date. Introducerea numelui unui camp aici se poate face din doua motive: fie se urmareste ca datele din campul respectiv sa apara in rezultatele interogarii, fie se doreste sortarea sau alegerea inregistrarilor din sursa de date in functie de o anumita valoare plasata in acest camp.

Table (Tabela). Este sursa de date in care se gasesc campurile listate mai sus. Poate fi o tabela sau o alta interogare.

Sort (Sortare). Reprezinta modul in care trebuie sortate inregistrarile returnate de interogare. Intrarile valide in aceasta celula sunt: ascending (0-9, A-Z), descending (9-0, Z-A) si not sorted (nesortate sau necompletate). Sortarea se aplica asupra campului afisat deasupra ordinii de sortare; pot fi sortate oricate campuri din grila.

Show (Afisare). Aceasta caseta este validata automat, indicand astfel ca datele din campul selectat trebuie afisate ca parte a rezultatelor interogarii. In cazul in care caseta nu este validata, campul respectiv este folosit pentru sortare si/sau criterii, dar nu este afisat pe ecran.

Criteria (Criterii). Un sir introdus in aceasta celula indica faptul ca respectivul camp trebuie sa corespunda sirului pentru ca datele din inregistrarile asociate sa fie incluse in rezultat. Acest sir poate include oricate criterii pentru campul listat, separate prin cuvantul cheie AND.

or (sau). Orice sir introdus in aceasta celula face parte din criteriile de selectie pentru campul corespunzator, dar aceste criterii sunt diferite de cele introduse in celula anterioara. Daca datele din campul listat respecta criteriile din celula Criteria sau pe cele din celula or, inregistrarea asociata va fi inclusa in setul de rezultate.

Cand se deschide grila QBE, programul Access presupune ca utilizatorul doreste sa construiasca o interogare corespunzatoare unei comenzi SQL se tip SELECT. Exista o serie de caracteristici si functii suplimentare utile in procesul de construire a interogarilor SELECT cum ar fi utilizarea functiilor statistice pe linia Totals a unei interogari in vederea calcularii totalurilor si a altor valori. Daca in modul de afisare Design View a unei interogari se selecteaza de pe bara de instrumente butonul Totals (are o pictograma reprezentand litera greceasca sigma - S ), in grila QBE apare o linie noua intitulata Total. In campul Total pot fi selectate dintr-o lista derulanta mai multe functii care opereaza asupra campului corespunzator si shimba modul in care acesta este afisat in rezultat: Group by, Sum, Avg, Min, Max, s.a..

Pentru crearea celorlalte tipuri de interogari, se selecteaza din meniul Query tipul de interogare dorit ceea ce conduce la actualizarea campurilor din grila (Update Query, Delete Query, etc).

Atunci cand se specifica mai multe tabele pentru o interogare, tabelele sunt considerate asociate. si se efectueaza  operatia de algebra relationala join, care va fi studiata intr-o lucrare ulterioara.

Exercitii:

1.     Sa se creeze in Access o baza de date care sa contina tabelele oferite de  Serverul Oracle (EMP, DEPT), cu aceleasi campuri si semnificatie. Sa se introduca mai multe inregistrari in fiecare tabela (nu neaparat cu aceleasi date), folosind modul Open al tabelelor.

2.     Sa se construiasca interogarile de la punctul 2, folosind limbajul QBE. Comparati comenzile SQL create de mediul Access corespunzator interogarilor QBE (cu comanda de meniu View/SQL View), cu cele pe care le-a fi introdus direct.

4.  Asocierile intre tabele. Integritatea referentiala a bazei de date.

In proiectarea si exploatarea bazelor de date de orice tip, se folosesc asocieri (relationships) intre multimile de entitati (datele) componente, pentru a modela realitatea pe care baza de date o reprezinta. Fiind date doua multimi de entitati, M1 si M2, se pot defini trei tipuri de asocieri:

Unul-la-unul (One-to-one): este asocierea in care unei entitati din multimea M1 ii coreaspunde o singura entitate din multimea M2, si reciproc; se noteaza cu 1:1.

Unul-la-multe (One-to-many): este asocierea in care unei entitati din multimea M1 ii coreaspund una sau mai multe entitati in multimea M2, dar unei entitati din M2 ii corespunde o singura entitate in multimea M1; se noteaza cu 1:N

Multe-la-multe (Many-to-many): este asocierea in care unei entitati din multimea M1 ii coreaspund una sau mai multe entitati in multimea M2, si, de asemenea, unei entitati din M2 ii corespund una sau mai multe entitati in multimea M1; se noteaza cu M:N

In modelul relational, asocierile dintre multimile de entitati de date sunt asocieri intre tabelele componente si se realizeaza prin intermediul cheilor tabelelor. Intr-o tabela se pot defini urmatoarele tipuri de chei: cheia primara (PRIMARY KEY), chei candidate (CANDIDATE KEY) si chei straine (FOREIGN KEY).

O cheie candidata intr-o relatie (tabela) este o submultime de campuri (coloane) ale tabelei care are valori unice in tabela respectiva, adica nu exista doua linii ale tabelei care sa aiba valori identice in toate campurile care apartin unei chei candidate. Este posibil insa ca o cheie candidata sa admita valori de NULL. O cheie primara este o submultime de campuri ale tabelei care are valori unice in tabela respectiva, adica nu exista doua linii ale tabelei care sa aiba valori identice in toate campurile care apartin unei cheii primare si nu sunt admise valori de NULL. Cheia primara este, in mod implicit, cheie candidata. O tabela poate avea oricate chei candidate, dar o singura cheie primara. O cheie straina este o submultime de atribute (campuri) ale tabelei astfel incat valoarea acesteia (a tuturor campurilor care o compun) este egala cu valoarea unei chei candidate din tabelul referentiat, sau are valoarea NULL. O tabela poate sa aiba zero sau oricat de multe chei straine. Campurile corespondente din cheia straina si cheia candidata referentiata trebuie sa fie compatibile ca tip, dar nu este neaparat nevoie sa aiba aceeasi denumire.

Prin intermediul cheilor se pot defini toate tipurile de asocieri intre tabele. Asocierea 1:1 intre doua tabele se realizeaza daca cheia primara dintr-o tabela este, de asemenea, cheie primara si in cealalta tabela. Asocierea 1:N se realizeaza prin intermediul unei chei straine: o cheie straina (definita intr-o tabela) care referentiaza o cheie primara dintr-o alta tabela, realizeaza asocierea 1:N intre tabela care contine cheia primara si tabela care contine cheia straina. Asociere M:N nu se poate defini direct intre doua (sau mai multe) tabele, ci numai prin intermediul unei alte tabele (numita tabela de asociere), definit astfel incat fiecare din tabelele date realizeaza o asociere de tipul 1:N cu tabela de asociere. Pentru aceasta, tabela de asociere contine cate o cheie straina care referentiaza cheia primara corespunzatoare din fiecare din tabelele date.

Cheile impun constrangeri asupra valorii datelor care se pot memora in tabele si respectarea acestor constrangeri asigura integritatatea datelor. Constrangerile se definesc la proiectarea conceptuala a bezei de date si se introduc in aplicatie intr-un mod care depinde de sistemul de gestiune si de instrumentele de proiectare folosite.

In Access, cheia primara a unei tabele se defineste la proiectarea tabelei. Cheile candidate nu se definesc explicit, ci pot fi testate doar la introducerea datelor. Cheile straine permit stabilirea de asocieri intre tabele si se definesc in doua etape. In prima etapa, la crearea tabelelor, campurile (sau campul) care vor constitui cheia straina trebuie sa fie definite de acelasi tip de date (cu acelasi domeniu) ca si campurile corespunzatoare din cheia primara din tabela pe care o referentiaza. Dupa definirea tabelelor (tabelele referentiate si tabelele care referentiaza), se foloseste comanda de meniu Tools/Relationships (sau comanda Relationships din bara de instrumente, care are o pictograma reprezentand un arbore) pentru a defini asocierea si deci cheia straina intre tabele.

La actionarea comenzii Relationships, programul Access afiseaza o fereastra numita Relationships si mai multe comenzi de meniu asociate acestei ferestre. In fereastra Relationships sunt reprezentate tabelele asociate, fiecare tabela avand toate campurile definite, iar o asociere este reprezentata printr-un link (conexiune) intre doua tabele, in dreptul atributelor (campurilor) corespundente. Tabelele afisate pot fi rearanjate in cadrul ferestrei tragandu-le cu mouse-ul. Acest mod de afisare se poate observa in Figura 1.

Pentru a crea o noua asociere intre doua tabele, se parcurg urmatorii pasi:

1.     Se adauga in fereastra Relationships tabelele intre care se doreste crearea de asocieri. Pentru aceasta se actioneaza comanda Show Table din meniul Relationships sau din meniul de context, obtinut prin click pe butonul dreapta al mousului in fereastra Relationships. La aceasta comanda, se deschide inca o fereastra, cu titlul Show Table, care listeaza toate tabelele definite. Se selecteaza una sau mai multe tabele si se da comanda de buton Add, care introduce tabelele selectate in fereastra Relationship. Dupa acesta, fereastra Show Table poate fi inchisa (cu comanda de buton Close).

2.     Cheile primare din fiecare tabela sunt afisate cu caractere ingrosate. Cu mouse-ul, se trage numele cheii primare din tabela referentiata peste numele campului corespunzator cheii straine sau cheii primare din tabela care referentiaza. Va fi afisata o noua fereastra Relationships, care permite stabilirea unor optiuni de asociere intre tabele. Prin acest mecanism, se definesc atat asocieri 1:1 cat si asocieri 1:N. Link-ul de conectare intre doua tabele asociate are eticheta 1 pe capatul dinspre tabela referentiata (care contine cheia primara) si eticheta pe capatul dinspre tabela care referentiaza (care contine cheia straina).

3.     In fereastra Relationships (Figura 2) apar numele campurilor care au fost asociate. In majoritatea situatiilor, se recomanda selectarea casetei de validare Enforce Referential Integrity (forteaza integritatea referentiala), ceea ce impune verificarea conditiei de integritate referentiala, adica pentru cheia straina din tabela care referentiaza nu se admit decat valori care exista in cheia primara dintr-un tuplu (linie) din tabela referentiata.

4.     In fereastra de editare a unei asocieri Relationships se mai poate valida optiunea de 'actualizare in cascada' a campurilor corelate prin referentiere (Cascade Update Related Fields) si optiunea de 'stergere in cascada' a campurilor corelate prin referentiere (Cascade Delete Related Fields).

5.     Comanda de buton Join Type (Tipul de cuplare) permite stabilirea tipului de cuplare (join) intre tabele. La actionarea acestei, comenzi se deschide o fereastra de dialog modal (Join Propeerties) prin care se poate selecta unul din trei tipuri de operatii de cuplare. Prima optiune este cea implicita (cuplare interna - internal join) este cea mai frecvent utilizata; celelalte doua tipuri (cuplari externe la dreapta sau la stanga) vor fi studiate intr-o lucrare ulterioara.

In exemplul preentat s-au definit asocieri 1:1 (intre tabelele STUDENTI si STUDENTI DETALII) si asocieri 1:N (intre tabelele STUDENTI --INDRUMARE si PROFESORI -- INDRUMARE). Intre tabelele STUDENTI si PROFESORI asocierea de M:N este realizata prin tabelul de asociere INDRUMARE si cele doua asocieri 1:N definite pentru acesta.

Exercitii

1.     Creati baza de date cu tabelele si asocierile din Figura 1. Introduceti date in tabele (de exemplu, chiar datele grupei si a profesorilor dumneavoastra).

2.     Creati urmatoarele interogari folosind limbajiul QBE: (a) Care sunt numerele de identificare (StudentId) si numele studentilor nascuti inainte de anul 1980? (b) Care este numarul de telefon al profesorului Ionescu?