33607416-baze-de-date-access-laborator-de-ioan-mocian-130412081535-phpapp01.pdf

Ioan MOCIAN

BAZE DE DATE Lucrri de laborator

UNIVERSITATEA PETRU MAIOR TRGU MURE 2008

Cuvnt nainte

Prezentul ndrumtor de lucrri de laborator pentru baze de date vine s completeze cursul de baze de date cu aspectele practice care trebuie testate n cadrul laboratorului i proiectului. Acest ndrumtor urmrete structura cursului predat i se compune din 5 lucrri de laborator care se vor executa pe durata mai multor edine.

Lucrrile de laborator propuse vor trebui efectuate consecvent, deoarece ele sunt gndite ca un proces continuu, adic rezultatele lor vor fi folosite de lucrrile care urmeaz. Prin urmare, acestea nu se pot face dect n ordinea lor fireasc. Cu ct vei lucra mai contiincios, cu att vei avea satisfacii mai mari i mai puin timp irosit.

Trebuie s avei n vedere c bazele de date, trebuie s aib date coerente pentru a putea s testm funcionarea formularelor, interogrilor i rapoartelor. Fiecare student va trebui s implementeze proiectul biblioteca cu propriile date introduse, parcurgnd toate etapele de proiectare i implementare a unei baze de date.

Proiectul Biblioteca implementat corect v asigur nota de trecere a examenului prin susinerea lui n cadrul colocviului.

Coninutul celor 4 lucrri, sau mai corect spus, 3 lucrri de laborator i un proiect, este urmtorul:

Lucrarea 1 conine studierea conceptelor bazelor de date relaionale. La sfritul lucrrii sunt enumerai termenii pe care studentul i are de clarificat. n Excel ca trebui sa dea exemple din cele 3 tipuri de relaii existente 1:1, 1:N i N:N.

Lucrarea 2 este dedicat studierii etapelor de proiectare a unei baze de date, vor fi studiate cele 7 etape ale unui proiect, cu exemplificare proiectul Biblioteca. La sfritul lucrrii, studenii vor avea create structura tabelelor, diagrama de relaii i completate 10 formulare cu specificaii de cmp.

Lucrarea 3 este dedicat studierii limbajului SQL, coninnd exemple cu instruciunile de baz. Studenii trebuie s neleag funcionarea expresiilor prezentate, s le explice i poat concepe alte exemple. Aceste expresii vor fi reluate, pe viu, n cadrul programului Access.

Lucrarea 4 cuprinde iniierea n utilizarea programului Access cu finalitate n implementarea proiectului Biblioteca. Se ntinde pe durata a mai multor laboratoare, iar n final fiecare student va avea propria aplicaie de baz de date n Access numit Biblioteca_nume_prenume.mdb.

Lucrarea 5 este dedicat elementelor de performan ale programului Access, n care se vor crea macro-uri i se va particulariza aplicaia Biblioteca, pentru a arta ca o aplicaie profesional.

Aplicaia Biblioteca prezentat n acest ndrumtor poate fi un bun exemplu pentru aplicaii proprii.

Trgu Mure, 20 ianuarie 2008 Autorul

3

Cuprins

Cuvnt nainte .............................................................................................................. 3 Lucrarea 1. Terminologia bazelor de date relaionale ............................................. 6 Definiii .............................................................................................................. 6 Desfurarea lucrrii ........................................................................................ 15 Lucrarea 2. Elemente de proiectare a bazelor de date relaionale ....................... 16 Consideraii teoretice .. 16 Studiu de caz. Proiectarea bazei de date Biblioteca 16 Desfurarea lucrrii ....................................................................................... 17 Lucrarea nr. 3. Iniiere n limbajul SQL ................................................................ 18 Operaiuni simple folosind limbajul SQL .. 18 Regsirea datelor 18 Sortarea datelor .. 19 Filtrarea datelor .. 20 Operatorii clauzei WHERE 20 Filtrare avansat 21

Operaiuni avansate folosind limbajul SQL . 25 Cmpuri calculate . 25

Desfurarea lucrrii ......................................................................... 26 Lucrarea nr. 4. Studiu de caz: Baza de date Biblioteca ................................. 27 Consideraii generale despre programul Access ... 27

Interfaa programului ACCESS 27 Crearea tabelelor 28 Relaii ntre tabele . 30 Crearea relaiilor cu Lookup Wizard 31 Crearea tabelelor bazei de date Biblioteca 34 Crearea formularelor pentru introducerea datelor 36 Testarea expresiilor SQL de la lucrarea nr. 3 42

Crearea interogrilor . 44 Vederea Crile din bibiotec . 44

Vederea Crile mprumutate . 46 Vederea Situaie cititor .. 46

Crearea formularelor pentru vizualizarea informaiilor 47 Crearea formularului de vizualizare a crilor din bibliotec . 49 Crearea formularului de vizualizare a situaiei unui cititor . 51

Rapoartele bazei de date Biblioteca .. 55 Finalizarea lucrrii nr.4 . 59

Lucrarea nr. 5. Particularizarea bazei de date Biblioteca ............................. 61 O scurt prezentare a Macro-urilor .. 61 Crearea macro-urilor cu singur aciune . 62 Rularea unei macrocomenzi din fereastra Database 63 Macrocomenzi ataate evenimentelor .. 64 Utilizarea macrocomenzilor AutoExec i AutoKeys .. 64 Metod rapid de pornire a aplicaiilor 65 Desfurarea lucrrii 66 Crearea macro-rilor aplicaiei Biblioteca 66

4

Crearea formei de start pentru aplicaia Biblioteca .... 68 Adaptarea formularelor aplicaiei Biblioteca . 72 Configurarea formularului de start 73

Anexe Anexa A. Teme de proiect propuse .. 74

Anexa B. Comenzile SQL 76 Anexa C.Formulare de proiectare a bazelor de date relaionale .... 78

5

Baze de date ndrumtor de lucrri de laborator Lucrarea nr.1

Lucrarea nr. 1. Terminologia bazelor de date relaionale

n aceast lucrare se vor clarifica principalele noiuni i concepte care vor fi folosite n studiul bazelor de date relaionale.

Definiii

Baz de date (Database) O colecie de date nrudite, stocate n mod organizat, care se refer la un anumit subiect sau obiectiv, mpreun cu instrumentele folosite pentru manipularea lor.

Date Valorile care se stocheaz n baza de date. Acestea sunt statice n sensul c rmn aceleai pn nu intervine o modificare manual sau automat.

Informaii - Reprezint date pe care le prelucrm ntr-un mod care le confer semnificaie i utilitate pentru noi atunci cnd lucrm cu respectivele date. Informaiile sunt dinamice, n sensul c se modific n permanen dup cum se modific datele din baza de date, dar i n sensul c pot fi prelucrate ntr-un numr nelimitat de moduri. Ideea care trebuie reinut este c trebuie s prelucrm datele pentru a le putea transforma n informaii cu sens. Valoare nul - Reprezint o valoare care lipsete sau care nu este cunoscut. Deseori, valoarea nul se confund cu un zero sau unul sau mai multe spaii albe, ceea ce este total greit, din urmtoarele motive:

Un zero poate avea mai multe semnificaii cum ar fi nivelul stocului unui anumit produs, numrul de apariii a unui cod etc.

Dei un ir text format din unul sau mai multe spaii albe nu nseamn nimic pentru cei mai muli dintre noi, reprezint categoric semnificaie pentru un limbaj de interogare cum ar fi SQL. Un spaiu alb este un caracter ca oricare altul cum ar fi a.

Un ir de lungime zero adic dou ghilimele consecutive fr spaiu ntre ele , este de asemenea o valoare acceptabil pentru limbaje precum SQL i poate fi semnificativ n anumite circumstane.

Tabel (Table) - Reprezint structurile eseniale dintr-o baz de date, iar fiecare tabel reprezint ntotdeauna un singur subiect concret, cum ar fi studeni, produse, vnzri etc. Ordinea logic a nregistrrilor i a cmpurilor din cadrul unui tabel nu are nici o importan, iar fiecare tabel conine cel puin un cmp cunoscut sub numele de cheie primar care identific n mod unic fiecare nregistrare a tabelului. Un exempu de tabel:

StudentID Nume Prenume Sectia 5001 Pop Mariana TCM 5002 Ban Ioan TCM 5012 Lazr Ana IEI 5065 Ban Lucia IMPI 5032 Pop Dorin MEC

nregistrri

Cmpuri

6


Datele dintr-o baz de date relaional pot exista independent de modul n care sunt stocate fizic n calculator, datorit acestor ultime dou caracteristici ale unui tabel. Pentru utilizator acest lucru este foarte bun, deoarece acesta nu mai trebuie s cunoasc locaia fizic a unei nregistrri pentru a putea regsi datele.

Subiectul pe care l reprezint un tabel dat poate fi un obiect sau un eveniment. Cnd subiectul este un obiect, tabelul reprezint o cantitate palpabil, precum o persoan, un produs sau un lucru oarecare. Indiferent de tipul su, un obiect are caracteristici care pot fi stocate sub form de date, care vor putea fi prelucrate ulterior ntr-un numr mare de moduri.

Cnd subiectul unui tabel este un eveniment, nseamn c tabelul reprezint ceva care se produce la un anumit moment de timp i care are caracteristici pe care dorii s le nregistrai. Exemplul clasic care se poate da aici este tabelul cu consultaiile inute de un medic de familie.

Tabelele pot fi, ntr-o alt clasificare, de dou tipuri: Tabele de date care furnizeaz date folosite pentru furnizarea de informaii i reprezint

tipul de tabel cel mai frecvent ntlnit ntr-o baz de date. Datele din acest tip de tabel sunt dinamice deoarece se pot manipula (modificare, tergere) i converti n informaii ntr-o anumit form sau manier. Cu astfel de tabele vei lucra foarte frecvent n decursul lucrului cu baza de date.

Tabele de validare care stocheaz date pe care le vei folosi cu scopul precis de a implementa integritatea datelor. De obicei, un tabel de validare conine nume de localiti, coduri de produse, categorii de activiti etc. Datele din acest tip de tabel sunt statice, adic se modific foarte rar.

Cmp (Field) - Reprezint cea mai mic structur din baza de date i reprezint o caracteristic a subiectului tabelului cruia i aparine. Cmpurile sunt structurile care stocheaz efectiv datele, care apoi pot fi regsite i prezentate ca informaii n aproape orice configuraie pe care o putei imagina. Fiecare cmp dintr-o baz de date corect proiectat conine singur valoare, iar numele su va identifica tipul de valoare admis. Astfel, procesul de introducere a datelor devine foarte intuitiv.

nregistrare (Record) - Reprezint o instan unic a subiectului unui tabel. nregistrarea este alctuit din ntregul set de cmpuri dintr-un tabel, indiferent dac respectivele cmpuri conin sau nu valori. Datorit modalitii de definire a unui tabel, fiecare nregistrare este definit n baza de date prin intermediul unei valori unice a cmpului cheie primar a nregistrrii respective. Astfel, dac avem un tabel de persoane, o nregistrare din tabel trebuie s identifice fiecare persoan din tabel, care este un unicat.

nregistrrile reprezint un element cheie pentru nelegerea relaiilor dintre tabele, deoarece va trebui s cunoatei relaia dintre o nregistrare a unui tabel i alte nregistrri din alt tabel.

Vedere (Query) - Este un tabel virtual compus din cmpuri dintr-unul sau mai multe tabele ale bazei de date; tabelele care alctuiesc vederea sunt cunoscute sub numele de tabele de baz. Modelul relaional i atribuie unei vederi atributul de virtual deoarece i preia datele din tabele de baz, nu-i stocheaz propriile sale date. De fapt, singurele informaii referitoare la o vedere care sunt stocate n baza de date se refer la structura vederii respective. Numeroase programe SGBDR principale lucreaz cu vederi, dar unele (precum Microsoft Access) le denumesc interogri salvate.

7


Impresari

Cheie primar

Sportivi

Cheie extern

Cheie (Key) - sunt acele cmpuri speciale care ndeplinesc roluri foarte bine determinate n cadrul unui tabel, iar tipul cheii definete rolul acesteia n interiorul tabelului. Un tabel poate conine numeroase tipuri de chei, dar cele mai importante sunt cheia primar i cheia extern.

O cheie primar este un cmp sau un grup de cmpuri care identific n mod unic fiecare nregistrare din cadrul unui tabel; dac o cheie primar este compus din dou sau mai multe cmpuri, este cunoscut sub numele de cheie primar compozit. O valoare a unei primare identific o anumit nregistrare din ntreaga baz de date. Cheia primar impune integritatea la nivel de tabel i faciliteaz stabilirea relaiilor cu alte tabele din baza de date.

O cheie extern dintr-un tabel este acea cheie care este cheie primar n alt tabel. Ea nu trebuie s fie unic dup cum vei observa n urmtorul exemplu, rolul ei este, n special, de a asigura legtura cu alt tabel. n figura 1.2 se pot vedea o cheie primar i o cheie extern.

Fig.1.2. Exemplu de cmpuri cheie primar i cheie extern

Se observ cum cmpul ImpresarID este cheie primar pentru tabelul Impresari i cheie extern pentru tabelul Sportivi. Acolo unde este cheie extern, valoarea ei se poate repeta, ceea ce este firesc, deoarece un impresar poate avea mai muli sportivi. Cmpul ImpresarID din cele dou tabele asigur legtura dintre ele, asigurnd n acelai timp i integritatea datelor, adic fiecare sportiv are un impresar valabil.

Cnd determinai c ntre dou tabele exist o relaie, n mod caracteristic stabilii relaia respectiv prelund o copie a cheii primare din primul tabel i ncorpornd-o n structura celui de-al doilea tabel, unde devine cheie extern. Numele de cheie extern deriv din faptul c al doilea tabel are deja o cheie primar proprie, iar cheia primar pe care o introducei din primul tabel este extern pentru al doilea tabel.

Index - Este o structur pe care un program SGBDR o pune la dispoziie pentru mbuntirea procesului de prelucrare a datelor. Un index nu are nici o legtur cu structura logic a bazei de date. Unicul motiv pentru care discutm despre termenul index n acest capitol este faptul c oamenii l confund deseori cu termenul cheie.

Index i cheie reprezint o alt pereche de termeni folosii eronat n mod frecvent i pe scar larg n industria bazelor de date. (Mai inei minte deosebirile dintre date i informaii?). Vei sesiza ntotdeauna diferena dintre cei doi termeni dac reinei c, n timp ce cheile sunt structuri logice pe care la identificarea nregistrrilor dintr-un tabel, indecii reprezint structuri fizice utilizate la optimizarea procesului de prelucrare a datelor.

Prin folosirea indecilor, sortrile i filtrrile unei baze de date se face ntr-un timp mult mai scurt.

ImpresarID Nume Prenume Telefon 100 Becali Ioan 0745-655482 101 Popescu Gic 0745-658312 102 Becali Victor 0744-547212

SportivID ImpresarID Nume sportiv Telefon 8001 100 Mutu Adrian 0745-657329 8002 101 Neaga Ioan 0744-768432 8003 100 Chivu Cristian 0723-546291

Cheie primar

8


Studenti

Relaii (Relationships) - ntre dou tabele exist o relaie atunci cnd nregistrrile din primul tabel pot fi asociate cu nregistrrile din al doilea tabel. Relaia se poate stabili prin intermediul unui set de chei primare i chei externe sau cu ajutorul unui al treilea tabel, numit tabel de legtur (cunoscut i sub numele de tabel asociativ).

Figura 1.3 ilustreaz relaii stabilite prin intermediul cheilor primare i al cheilor externe.

StudID Nume Prenume OrasStudent 6001 Pop Remus Reghin ........ 6002 Szabo Zoltan Oradea ........ 6003 Costea Florian Zalau ........ 6004 Timocea Sebastian Brasov ........ 6004 Mocean Vasile Fagaras ........

O relaie este o component important a unei baze de date relaionale. O relaie permite crearea de vederi din tabele multiple i este crucial pentru integritatea datelor, ntruct contribuie la cantitii de date redundante i la eliminarea datelor duplicate. Putei caracteriza o relaie n trei moduri: n funcie de tipul relaiei dintre tabele, de maniera n care fiecare tabel particip la relaie i de gradul de participare al fiecrui tabel.

Relaii unu cu unu - Dou tabele au o relaie unu cu unu cnd o singur nregistrare din primul tabel este corelat cu o singur nregistrare din al doilea tabel i o singur nregistrare din al doilea tabel este corelat cu o singur nregistrare din primul tabel. n figura 2.10 este reprezentat o astfel de relaie.

ntr-o asemenea relaie, un tabel servete ca tabel printe, iar cellalt ndeplinete rolul de tabel copil. Relaia se stabilete prin preluarea unei cpii a cheii primare a tabelului printe i ncorporarea acesteia n structura tabelului copil, unde devine o cheie extern. Acesta este un tip special de relaie, deoarece este unicul n cadrul cruia ambele tabele pot folosi efectiv aceeai cheie primar.

n figura 1.4 este prezentat un exemplu clasic de relaie unu la unu. n acest caz SALARIAI este tabelul printe, iar SALARIU este tabelul copil. Se observ c fiecare salariat din primul tabel are un singur corespondent din al doilea tabel.

StudID CursID 6001 C001 6002 C213 6002 C001 6001 C213 6002 C015 6003 C001 6003 C213 6001 C015 6003 G001 6001 G001

CursID Denumire Credite ProfesorID C001 PUC 5 25461 C213 Baze de date 5 25461 C032 SIM 4 56821 C015 GD 5 12843 G001 AutoCAD 4 32584 G004 Inventor 5 3212 G007 Intellicad 4 25461

Fig .1.3. Relaii ntre tabele

Orar student(table de legtur) Cursuri

9


Clienti

Salariai

Fig. 1.5. Exemplu de relaie unu cu mai muli

Imprumuturi

SalariatID Nume Prenume Telefon 100 Ban Ioan 0745-646321 .. 101 Pop Dorin 0723-548211 .. 102 Lazr Liviu 0264-542138 .. 103 Crian Ovidiu 0740-764282 ..

Fig. 1.4. Exemplu de relaie unu la unu

Relaia unu la unu poate fi imaginat ca o rupere n dou a tabelului. Dei cmpurile din aceste tabele pot fi combinate ntr-un singur tabel, proiectantul bazei de date a ales s plaseze n tabelul SALARIAI cmpurile ce pot fi vzute de orice membru al organizaiei i n tabelul SALARIU cmpurile ce pot fi vzute doar de personalul autorizat, tiut fiind c salariile sunt, de obicei, confideniale.

Relaii unu cu mai muli - ntre dou tabele exist o relaie unu cu mai muli cnd o nregistrare din primul tabel poate fi corelat cu una sau mai multe nregistrri din al doilea tabel, n timp ce o nregistrare din al doilea tabel poate fi corelat cu o singur nregistrare din primul tabel. S studiem un exemplu generic pentru acest tip de relaie.

Modelul printe/copil pe care l-am utilizat pentru a descrie o relaie unu cu unu se aplic i n acest caz, partea unu a relaiei este tabelul printe, iar tabelul din partea mai muli este tabelul copil. O relaie de tipul unu cu mai muli se stabilete prin preluarea unei cpii a cheii primare a tabelului printe i ncorporarea acesteia n structura tabelului copil, unde devine o cheie extern.

Exemplul din figura 1.5 ilustreaz o relaie de tip unu cu mai muli caracteristic.

SalariatID Salar orar Sporuri 100 34.50 10% .. 101 23.00 5% .. 102 17.45 20% .. 103 16.00 18% ..

ClientID CarteID Data 9002 5648 .... 9001 690423 .... 9004 6563 .... 9003 65323 .... 9003 09542 .... 9003 64823 .... 9002 75001 .... 9005 10045 .... 9005 76100 ....

ClientID Nume Prenume 9001 Pop Dorin ....... 9002 Ban Ion ....... 9003 Lazr Ana ....... 9004 Buzan Maria ....... 9005 Beldean Vian .......

Salariu

10


Cursuri

Studenti

O singur nregistrare din tabelul CLIENTI poate fi corelat cu una sau mai multe nregistrri din tabelul IMPRUMUTURI, dar o nregistrare din tabelul IMPRUMUTURI este corelat cu o singur nregistrare din tabelul CLIENTI. Dup cum probabil ai dedus, cmpul ClientID este o cheie extern n tabelul IMPRUMUTURI.

Relaia unu cu mai muli este cea mai obinuit relaie care exist ntre dou tabele dintr-o baz de date i este cea mai uor de identificat. Relaia este extrem de important din punct de vedere al integritii datelor, deoarece ea v ajut s eliminai datele duplicate. Relaii de tip mai muli cu mai muli - ntre dou tabele exist o relaie de mai muli cu mai muli cnd o nregistrare din primul tabel poate fi corelat cu una sau mai multe nregistrri din al doilea tabel i o nregistrare din al doilea tabel poate fi corelat cu una sau mai multe nregistrri din primul tabel. O relaie din aceast categorie se stabilete cu ajutorul unui tabel de legtur. Un tabel de legtur faciliteaz asocierea nregistrrilor dintr-un tabel cu nregistrrile din cellalt tabel i asigur lipsa oricror probleme la operaiile de adugare, tergere sau modificare a datelor corelate.

Un tabel de legtur se definete prin preluarea unor cpii ale cheii primare din fiecare tabel i utilizarea lor pentru a forma structura noului tabel. n realitate, aceste cmpuri ndeplinesc dou roluri distincte: mpreun formeaz cheia primar compozit a tabelului de legtur, iar separat, fiecare poate fi asimilat unei chei externe.

O relaie de tip mai muli cu mai muli care nu este stabilit n mod corespunztor se numete nerezolvat. Figura 1.6 prezint un exemplu clasic i clar de relaie de tip mai muli cu mai muli nerezolvat. n acest exemplu, o nregistrare din tabelul STUDENTI poate fi corelat cu mai multe nregistrri din tabelul CURSURI, n timp ce o singur nregistrare din tabelul CURSURI poate fi corelat cu mai multe nregistrri din tabelul STUDENTI.

Aceast relaie este nerezolvat datorit particularitii intrinseci a relaiei de tip mai muli cu mai muli. Principala problem este urmtoarea: cum se pot asocia cu uurin nregistrri din primul tabel cu nregistrrile din al doilea tabel? Pentru a reformula ntrebarea folosind


CursID Denumire Credite ProfesorID Sala C001 PUC 5 25461 208 ........ C213 Baze de date 5 25461 208 ........ C032 SIM 4 56821 209 ........ C015 GD 5 12843 207 ........ G001 AutoCAD 4 32584 207 ........ G004 Inventor 5 3212 208 ........ G007 Intellicad 4 25461 208 ........

Fig. 1.6. Un exemplu tipic de relaie mai muli cu mai muli nerezolvat

11


tabelele din figura 1.6, cum se asociaz un student cu mai multe cursuri sau un anumit curs cu mai muli studeni? Se insereaz cteva cmpuri Student n tabelul CURSURI sau mai multe cmpuri Curs n tabelul STUDENTI? Oricare din aceste metode va ngreuna lucrul cu datele i va afecta n mod negativ integritatea datelor. Cea mai bun metod const n din crearea i utilizarea unui tabel de legtur, care va rezolva relaia de tip mai muli cu mai muli n maniera cea mai adecvat i mai eficient. Figura 1.7 prezint implementarea practic a cestei soluii.

Este important ca dumneavoastr s cunoatei tipul de relaie care exist ntre tabelele dintr-o pereche, deoarece acesta determin modul n care sunt corelate tabelele, dac nregistrrile din tabele sunt interdependente sau nu, precum i numrul minim i maxim de nregistrri corelate care pot exista n cadrul relaiei.

Tipul de participare - Cnd stabilii o relaie ntre dou tabele, fiecare tabel particip la relaie ntr-o manier particular. Tipul de participare pe care l atribuii unui tabel dat determin dac n respectivul tabel trebuie s existe o nregistrare nainte de a putea introduce nregistrri n tabelul corelat. Exist dou tipuri de participri:

Obligatorie - tabelul trebuie s conin cel puin o nregistrare nainte de a putea introduce nregistrri n tabelul corelat.

Opional nu este obligatoriu ca tabelul s conin vreo nregistrare nainte de a putea introduce nregistrri n tabelul corelat.

Gradul de participare - Determin numrul minim de nregistrri existente ntr-un tabel al unei relaii, asociate cu o singur nregistrare a unui tabel corelat, respectiv numrul maxim de nregistrri care pot exista ntr-un tabel al unei relaii, asociate cu o singur nregistrare din tabelul corelat.


StudID CursID 6001 C001 6002 C213 6002 C001 6001 C213 6002 C015 6003 C001 6003 C213 6001 C015 6003 G001 6001 G001

CursID Denumire Credite ProfID C001 PUC 5 25461 C213 Baze de date 5 25461 C032 SIM 4 56821 C015 GD 5 12843 G001 AutoCAD 4 32584 G004 Inventor 5 3212 G007 Intellicad 4 25461

Fig. 1.7. Rezolvarea unei relaii de tip mai muli cu mai muli cu ajutorul unui tabel de legtur

12


Specificaie de cmp - Reprezint toate elementele unui cmp. Fiecare specificaie de cmp ncorporeaz trei tipuri de elemente: generale, fizice i logice.

Elementele generale reprezint informaiile fundamentale referitoare la cmp i includ elemente precum numele cmpului, descrierea i tabelul printe.

Elementele fizice determin modul de construire a unui cmp i modul de reprezentare a acestuia pentru persoana care l utilizeaz. Aceast categorie include elemente precum tipul de date, lungimea i formatul de afiare.

Elementele logice descriu valorile stocate ntr-un cmp i includ articole precum valoarea obligatorie, intervalul de valori i valoarea prestabilit.

Aceste specificaii de cmp o s le completai folosindu-v de nite formulare, care vor fi descrise n detaliu n capitolul urmtor.

Integritatea datelor - prin se nelege validitatea, consecvena i acurateea datelor incluse ntr-o baz de date. Nu pot accentua suficient faptul c nivelul de acuratee al informaiilor pe care le regsii din baza de date este direct proporional cu nivelul de integritate al datelor pe care l impunei bazei de date.

Integritatea datelor reprezint unul dintre cele mai importante aspecte ale procesului de proiectare a bazelor de date i nu v este permis s-l subestimai, s-l trecei cu vederea i nici mcar s-l neglijai parial. Trebuie s fii contient de faptul c dac nu respectai regulile de integritate, suntei pasibili de a obine informaii cu grave erori, care sunt greu de depistat. Gndii-v numai la implicaiile posibile atunci cnd baza dumneavoastr de date e folosit pentru tranzacii comerciale sau financiare.

Exist patru tipuri de integritate a datelor pe care le vei implementa pe durata procesului de proiectare a bazelor de date. Trei dintre acestea se bazeaz pe diferite aspecte ale structurii bazei de date i sunt denumite n conformitate cu zona (nivelul) la care opereaz. Cel de-al patrulea tip de integritate a datelor se bazeaz pe modul n care o organizaie i percepe i i utilizeaz datele.

n continuare vor fi prezentate descrierile fiecrui tip de integritate. Integritatea la nivel de tabel asigur lipsa nregistrrilor duplicate n interiorul

tabelului i faptul c acel cmp care identific fiecare nregistrare din tabel este unic i nu are niciodat valoare nul.

Integritatea la nivel de cmp asigur faptul c structura fiecrui cmp este solid, c valorile din fiecare cmp sunt valide, consecvente i precise, precum i c se asigur o definire consecvent, n ntreaga baz de date, a cmpurilor de acelai tip (Cod postal, de exemplu).

Integritatea la nivel de relaie (cunoscut i sub numele de integritate referenial) asigur soliditatea relaiei dintre dou tabele, precum i faptul c nregistrrile din tabele sunt sincronizate ori de cte ori se introduc, se actualizeaz sau se terg date din oricare dintre cele dou tabele.

Reguli de desfurare a activitii impun restricii sau limitri asupra anumitor aspecte ale bazei de date, pe baza modalitilor n care o organizaie i percepe i i actualizeaz datele. Aceste restricii pot afecta aspecte ale proiectrii bazelor de date, precum i intervalul i tipurile de valori stocate ntr-un cmp, tipul i gradul de participare a fiecrui tabel n cadrul unei relaii, precum i tipul de sincronizare utilizat pentru integritatea la nivel de relaie, n anumite relaii.

13


Baza de date relaional - A fost conceput n anul 1969 i este azi cel mai folosit model de baze de date n gestiunea bazelor de date. Acest model este fundamentat pe dou ramuri ale matematicii teoria mulimilor i logica predicatelor de ordinul nti. ntr-adevr, numele modelului provine de la termenul relaie, care constituie o parte a teoriei mulimilor.

O concepie greit, larg rspndit, este aceea c modelul relaional i-ar fi preluat numele de la faptul c ntre tabelele unei baze de date relaionale exist relaii.

O baz de date relaional stocheaz datele n relaii, pe care un utilizator le percepe ca pe nite tabele. Fiecare relaie este compus din nregistrri i cmpuri, iar ordinea fizic a nregistrrilor sau a cmpurilor dintr-un tabel este complet lipsit de importan, iar fiecare nregistrare a tabelului este identificat, nu dup locul unde se afl, ci dup un cmp care conine o valoare unic. Prin urmare, utilizatorul nu este obligat s cunoasc locaia fizic a unei nregistrri aa cum se ntmpl la celelalte modele de baz de date (ierarhic i reea), pentru regsirea datelor.

Sistem de gestiune a bazelor de date relaionale (SGBDR) - Este un program software folosit pentru crearea, ntreinerea, modificarea i manipularea unei baze de date relaionale. Numeroase programe SGBDR furnizeaz i instrumentele necesare pentru a putea crea aplicaii destinate utilizatorilor finali care lucreaz cu datele din baza de date. Numeroasele faciliti oferite de diferii productori evolueaz foarte rapid, devenind din ce n ce mai performante.

Primele SGBDR-uri au fost concepute pentru sisteme de tip desktop. Acestea presupun existena unei copii a bazei de date pe fiecare calculator care nu este un mare avantaj, n ceea ce privete actualizarea datelor. n acest context a aprut necesitatea amplasrii n mod centralizat a ntregii baze de date care s fie accesat de toi utilizatorii. Aa au aprut SGBDR-urile de tip client-server n care bazele de date se stocheaz pe un server de baze de date, iar utilizatorii interacioneaz cu datele prin intermediul unor aplicaii rezidente pe propriul calculator numit clientul bazei de date.

Creatorul de baze de date folosete programul client-server SGBDR pentru a crea i ntreine programele de aplicaie de baze de date i programele accesorii pentru utilizatorul final. Acesta implementeaz integritatea i securitatea datelor din serverul de baze de date, avnd astfel posibilitatea de a fundamenta o diversitate de aplicaii utilizator pe acelai set de date, fr a afecta integritatea i securitatea datelor.

Cele mai cunoscute SGBDR-uri sunt: Microsoft Access Microsoft SQL Server Oracle MySQL PostgreSQL

Microsoft Access este un SGBDR comercial de tip desktop, folosit pentru gestionarea bazelor de date de dimensiuni mici i medii. Poate acoperi fr probleme segmentul firmelor mici i mijlocii. Este sistemul de gestiune pe care l vom studia i noi n aceast carte.

Microsoft SQL Server este un SGBDR de tip client-server care accept baze de date foarte mari i un numr foarte mare de tranzacii i ruleaz numai pe sistemele de operare Windows. Este soluia ideal pentru firmele mari, la un pre de cost relativ sczut.

Oracle este liderul mondial al pieei SGBDR-urilor de tip client-server. Oracle accept baze de date enorme i un numr imens de tranzacii. Oracle ruleaz pe numeroase sisteme de operare, este un SGBDR complex a crui operare i ntreinere trebuie fcute de un administrator special instruit pentru acest scop. Se ntlnete la marile companii transnaionale, deoarece preul su este pe msura performanelor.

14


MySQL este un SGBDR din categoria open-source(codul surs este pus gratuit la dispoziia utilizatorilor) fiind unul din liderii pieei. MySQL este rapid, stabil i accept baze de date de mari dimensiuni, dar i lipsesc cteva din caracteristicile importante ale limbajului SQL. Versiunile viitoare i propun includerea acestor caracteristici. Ruleaz pe mai multe sisteme de operare i este gratuit pentru utilizarea n scopuri personale(care nu aduc un ctig).

PostgreSQL este un SGBDR din categoria open-source, fiind unul din liderii pieei. Accept baze de date de mari dimensiuni, este recunoscut pentru setul su bogat de caracteristici.

Desfurarea lucrrii

Lucrarea se consider efectuat dac:

1. Studentul i-a nsuit termenii i a neles conceptele de mai jos: Baz de date

Date Informaii Valoare nul Tabel Cmp nregistrare Vedere Cheie Index Relaii Relaii unu cu unu Relaii unu cu mai muli Relaii de tip mai muli cu mai muli Tipul de participare Gradul de participare Specificaie de cmp Integritatea datelor Baza de date relaional Sistem de gestiune a bazelor de date relaionale (SGBDR)

2. Pe baza exemplelor prezentate, studentul va concepe cte un exemplu original cu cele 3 tipuri de relaii ntre dou tabele, care vor fi prezentate n Excel.

15


Lucrarea nr. 2. Elemente de proiectare a bazelor de date relaionale

n aceast lucrare se vor parcurge cele 7 etape de proiectare a unei baze de date, avnd ca scop familiarizarea studenilor cu problemele intervenite n conceperea unei baze de date.

Consideraii teoretice

Ca orice lucru inventat de om, i bazele de date au un scop bine definit. Este foarte important s nelegem scopul pentru care e nevoie de o baz de date, pentru a alege cele mai optime soluii.

nainte de a crea o baz de date, aceasta trebuie proiectat, la fel cum, pentru a construi o cas avem nevoie de un proiect. Iat cele 7 etape care trebuie parcurse pentru a face un proiect:

Etapa 1 Definirea unei declaraii de intenie i a obiectivelor misiunii;

Etapa 2 Analiza bazei de date curente;

Etapa 3 Crearea structurilor de date;

Etapa 4 Determinarea i instituirea relaiilor ntre tabele;

Etapa 5 Determinarea i definirea regulilor de desfurare a activitii;

Etapa 6 Determinarea i definirea vederilor;

Etapa 7 Verificarea integritii datelor.

Studenii vor studia din curs Capitulul 3, pentru a-i nsui cunotinele legate de fiecare etap a procesului de proiectare. Pentru a nelege la modul concret, cum se face proiectarea unei baze de date, vom aborda un studiu de caz.

Studiu de caz. Proiectarea bazei de date Biblioteca

Declaraia de intenie. Rolul bazei de date este de a ine la zi stocul de cri din bibliotec i de a furniza informaii despre cititori i mprumuturi.

Obiectivele misiunii: Dorim s avem informaii despre autorii care au cri n bibliotec.

Dorim s avem informaii despre editurile care au cri n bibliotec.

Dorim s avem o clasificare a crilor pe domenii.

Dorim s avem informaii despre cititorii bibliotecii.

Dorim s avem situaii despre mprumuturi.

Dorim s tim la un moment dat, unde sunt exemplarele unei anumite cri.

16


Studenii vor ncerca s formuleze alte versiuni ale declaraiei de intenie i s propun i alte obiective ale misiunii bazei de date.

Crearea structurilor de date i a relaiilor. Presupunem c dup analiza obiectivelor misiunii i dup mai multe iteraii, am ajuns la structura prezentat mai jos.

Crearea foilor de specificaii. Studenii vor completa foi de specificaii pentru cel puin 10 cmpuri care s acopere toate tipurile de date. Formularele folosite sunt Specificatii camp.xls, care se gsete pe server n directorul Baze de date.

Desfurarea lucrrii

n directorul Biblioteca al fiecrui student va trebui s se creeze un fiier Word numit Documentatie proiect Biblioteca.doc n care vor apare Declaraia de intenie, Obiectivele misiunii i Diagrama de relaii. Diagrama se va executa n Word folosind comenzile Drawing.

De asemenea, n acelai director vei crea fiierul Specificatii camp.xls, care va conine pe 10 foi, cele 10 specificaii de cmp cerute.

Observaie important! Tratai cu mare grij cele 2 fiiere cerute, pentru c ele vor sta la baza lucrrilor care urmeaz.

17


Lucrarea nr. 3. Iniiere n limbajul SQL Aceast lucrare de laborator const n parcurgerea exemplelor de expresii SQL pentru ca studenii s neleag acest limbaj, care va fi folosit n lucrrile urmtoatoare.

Operaiuni simple folosind limbajul SQL

Regsirea datelor

Regsirea datelor se face cu instruciunea SELECT, folosit pentru a regsi una sau mai multe coloane. Aceasta este cea mai folosit instruciune din SQL.

Din tabelul tblStudenti prezentat n figura 3.1 ne propunem s extragem numele i prenumele studenilor.

Fig. 3.1. Tabelul tblStudenti simplificat

Expresie SQL: SELECT Nume, Prenume FROM tblStudenti;

Rezultat:

StudID Nume Init Prenume Sectia An Grupa Stare 1 Bogdan P. Mircea Florin IEI 1 1311 Bugetar 2 Meruta I. Cosmin IEI 3 1332 Bugetar 3 Pop T. Marius Traian IEI 2 1321 Bugetar 4 Bucur P. Mihaela IEI 2 1321 Bugetar 5 Chirila I. Laura IEI 3 1331 Bugetar 6 Cotirla L. Raluca Adina TCM 1 1111 Bugetar 7 Cotoara G. Ovidiu TCM 1 1111 Bugetar 8 Cozma D. Dumitru TCM 2 1121 Bugetar 9 Damian N. Daniel MEC 4 1241 Bugetar

10 Farcas I. Calin Florin MEC 4 1241 Taxa

Nume Prenume Bogdan Mircea Florin Meruta Cosmin Pop Marius Traian Bucur Mihaela Chirila Laura Cotirla Raluca Adina Cotoara Ovidiu Cozma Dumitru Damian Daniel Farcas Calin Florin

18


Observai expresia SQL care se citete astfel: selecteaz coloanele Nume i Prenume din tabelul tblStudenti. S-a folosit i clauza FROM, aa cum se vede. Rezultatul obinut este evident.

Dac dorii s selectai toate coloanele tabelului se folosete expresia urmtoare:

Expresie SQL: SELECT * FROM tblStudenti

n locul listei de coloane se pune acest simbol * .

Sortarea datelor

Prin sortarea datelor se nelege aranjarea nregistrrilor unui tabel ntr-o anumit ordine, de regul, alfabetic sau cresctoare/descresctoare. Sortarea datelor se face cu ajutorul clauzei ORDER BY a instruciunii SELECT.

La tabelul din figura 3.1 ne propunem s scoatem numai numele i prenumele studenilor dar n ordine alfabetic.

Expresie SQL: SELECT Nume, Prenume FROM tblStudenti ORDER BY Nume;

Rezultat:

Observai clauza ORDER BY care ordoneaz nregistrrile n ordine alfabetic dup valorile din cmpul Nume. Se pune ntrebare, ce ne facem dac vrem s ordonm invers alfabetic (de la Z la A)? Sau dac avem un cmp numeric i dorim s ordonm descresctor dup acest cmp. Iat rspunsul:

Pentru a indica ordinea descresctoare de sortare se folosete cuvntul cheie DESC. Dac cmpul dup care se face sortarea este alfanumeric, sortarea se face alphabetic,

iar dac este numeric sau de dat calendaristic, sortarea se face cresctor/descresctor.

Dac nu se indic direcia de sortare, se ia sortarea cresctoare care este implicit. (Cazul prezentat.)

Iat cum arat o expresie SQL n care apare i cuvntul cheie DESC:

Nume Prenume Bogdan Mircea Florin Bucur Mihaela Chirila Laura Cotirla Raluca Adina Cotoara Ovidiu Cozma Dumitru Damian Daniel Farcas Calin Florin Meruta Cosmin Pop Marius Traian

19


Expresie SQL: SELECT Nume, Prenume FROM tblStudenti ORDER BY Nume DESC

Reinei c rezultatul sortrii datelor este un tabel care are acelai numr de nregistrri ca i originalul.

Filtrarea datelor

Prin filtrarea datelor se nelege extragerea dintr-un tabel numai a anumitor nregistrri de care avem nevoie, de exemplu, din tabelul de studeni avem nevoie numai de cei din anul 2. Pentru a executa aceast operaiune vei folosi clauza WHERE a instruciunii SELECT.

Regsirea numai a nregistrrilor dorite, presupune includerea n expresia SQL a unui criteriu de cutare a nregistrrilor dorite. ntr-o instruciune SELECT, datele sunt filtrate prin specificarea criteriilor de cutare n clauza WHERE. Locul acesteia este imediat dup numele tabelului (clauza FROM), ca n exemplul urmtor. Fie tabelul din figura 3.1.

Ne propunem s filtrm studenii din anul 2. expresia SQL este urmtoarea: Expresie SQL: SELECT StudID, Nume, Init, Prenume, Sectia, Grupa FROM tblStudenti WHERE An = 2;

Rezultat: StudID Nume Init Prenume Sectia Grupa 3 Pop T. Marius Traian IEI 1321 4 Bucur P. Mihaela IEI 1321 8 Cozma D. Dumitru TCM 1121

Operatorii clauzei WHERE

Clauza WHERE pe care am examinat-o a testat egalitatea determin dac o coloan conine valoarea specificat. Limbajul SQL accept mai muli operatori condiionali, dup cum urmeaz:

= Egalitate Non-egalitate != Non-egalitate < Mai mic dect Mai mare dect >= Mai mare sau egal cu !> Nu mai mare dect BETWEEN ntre dou valori specificate IS NULL Este o valoare NULL

20


Observai c pentru non-egalitate exist dou simboluri, aceasta pentru c diversele SGBDR-uri accept pe unul sau altul dintre ele.

Iat cteva cazuri concrete de folosire a operatorilor:

Interval de valori: WHERE Pret BETWEEN 2.5 AND 10; (afieaz toate nregistrrile care au n coloana Pret valori ntre 2.5 i 10).

Valoare NULL: WHERE Pret IS NULL; (afieaz toate nregistrrile care nu au pre).

Non-egalitate: WHERE Stare Bugetar; (afieaz toate nregistrrile din tabelul studeni, folosit mai nainte, care nu sunt bugetari). Este evident c putem formula i altfel condiia, dar aici am folosit non-egalitatea.

Ceilali operatori se folosesc la fel ca cel de egalitate prezentat puin mai n fa.

Filtrare avansat

Filtrrile prezentate mai sus se ntlnesc mai rar n practic deoarece sunt foarte simple. Filtrrile pe care le vei folosi n mod curent, sunt filtrri mai complicate la care criteriile sunt exprimate prin expresii complexe. Aceste criterii creeaz condiii puternice de cutare. Vom folosi ali operatori cum sunt AND, OR, NOT i IN.

De multe ori, filtrarea dup o coloan nu rezolv problema pe care o avem. Pentru a filtra dup mai multe coloane se folosete operatorul AND. Fie tabelul din figura 3.2. Ne propunem s facem diferite filtrri.

Fig. 3.2. Tabel cu studeni

Operatorul AND. Prima filtrare ar fi studenii de la IEI din anul 1. Iat expresia SQL: Expresie SQL: SELECT Nume, Prenume, Grupa, Stare FROM tblStudenti WHERE Sectia=IEI AND An = 1;

Rezultat: Nume Prenume Grupa Stare Bogdan Mircea Florin 1311 Bugetar Meruta Cosmin 1312 Taxa

StudID Nume Init Prenume Sectia An Grupa Stare 1 Bogdan P. Mircea Florin IEI 1 1311 Bugetar 2 Meruta I. Cosmin IEI 1 1312 Taxa 3 Pop T. Marius Traian IEI 2 1321 Bugetar 4 Bucur P. Mihaela IMPI 2 1321 Bugetar 5 Pop I. Laura IEI 3 1331 Taxa 6 Cotirla L. Raluca Adina TCM 1 1111 Bugetar 7 Cotoara G. Ovidiu TCM 1 1111 Bugetar 8 Cozma D. Dumitru TCM 2 1121 Bugetar 9 Damian N. Daniel MEC 4 1241 Bugetar

10 Cozma I. Calin Florin MEC 4 1241 Taxa

21


Urmrii expresia SQL i verificai rezultatul obinut. ncercai i alte filtrri asemntoare. Observai folosirea operatorului AND. Cum ar trebui modificat expresia SQL, pentru a afia rezultatul n ordine alfabetic invers?

Operatorul OR. O alt filtrare pe care ne-o propunem, este s filtrm studenii din anul 2 de la IEI sau TCM.

Expresie SQL: SELECT Nume, Prenume,Sectia, Grupa, Stare FROM tblStudenti WHERE (Sectia=IEI OR Sectia=TCM) AND An = 2;

Rezultat: Nume Prenume Sectia Grupa Stare Pop Marius Traian IEI 1321 Bugetar Cozma Dumitru TCM 1121 Bugetar

Observai folosirea parametrilor OR i AND, respectiv apariia celor dou paranteze. Aceste paranteze au legtur cu ordinea operaiilor OR i AND. n ordinea operaiilor, operatorul AND se execut naintea operatorului OR, ceea ce ar duce la rezultate eronate, de aceea a fost pus operatorul OR n parantez, ca s-l execute primul, tiut fiind c parantezele au prioritate la execuie.

Operatorul IN. Folosirea acestui operator are ca scop specificarea unui domeniu de condiii, oricare dintre ele putnd fi ndeplinite. Operatorul IN necesit o list de valori valide, care s fie separate prin virgule i cuprinse ntre paranteze.

Ne propunem s alegem din tabelul din figura 3.2 toi studenii care au numele Pop i Cozma.

Expresie SQL: SELECT Nume, Prenume,Sectia, Grupa, Stare FROM tblStudenti WHERE Nume IN (Pop, Cozma);

Rezultat: Nume Prenume Sectia Grupa Stare Pop Marius Traian IEI 1321 Bugetar Pop Laura IEI 1331 Taxa Cozma Dumitru TCM 1121 Bugetar Cozma Calin Florin MEC 1241 Taxa

Din analiza acestui exemplu, putem observa fr greutate c operatorul IN face cam acelai lucru ca i operatorul OR, deci se poate scrie o expresie SQL cu acesta. Care este aceast expresie?

Se pune, pe bun dreptate, ntrebarea de ce mai avem nevoie de nc un operator dac avem unul care face acelai lucru. Rspunsul este c operatorul IN are unele avantaje care l fac de preferat fa de operatorul OR. Iat aceste avantaje:

Cnd lucrai cu liste lungi de opiuni valide, sintaxa operatorului IN este mai simpl i uor de citit, principalul avantaj.

Ordinea de evaluare este mai simplu de gestionat, cnd operatorul IN este folosit n asociaie cu operatorii AND i OR.

22


Aproape totdeauna, operatorii IN se execut mai rapid dect listele de operatori OR. Un mare avantaj este c operatorul IN poate s conin o alt instruciune SELECT, i astfel v permite s construii clauze WHERE foarte dinamice. Acest aspect va fi reluat mai trziu.

Operatorul NOT. Acest operator al clauzei WHERE are o singur funcie neag orice condiie care l urmeaz. Deoarece NOT nu este utilizat niciodat n sine (totdeauna se folosete n asociaie cu alt operator), sintaxa lui e diferit de toi ceilali operatori. Spre deosebire de ali operatori, cuvntul cheie NOT poate fi utilizat naintea coloanei dup care se face filtrarea, nu imediat dup aceasta.

Iat un exemplu sugestiv, extragerea studenilor nebugetari din tabelul din figura 3.2.

Expresie SQL: SELECT Nume, Prenume,Sectia, Grupa, Stare FROM tblStudenti WHERE NOT Stare=Bugetar;

Rezultat: Nume Prenume Sectia Grupa Stare Meruta Cosmin IEI 1312 Taxa Pop Laura IEI 1331 Taxa Cozma Calin Florin MEC 1241 Taxa

Dup cum se vede, acelai lucru l puteam obine i cu operatorul . Iari se pune ntrebarea de ce s folosim, totui, operatorul NOT? ntr-adevr, pentru clauzele WHERE simple, cum e cea prezentat, nu se poate spune c ar exista un avantaj real n folosirea operatorului NOT. Acesta este ns foarte util n clauzele mai complexe. De exemplu, dac folosii operatorul NOT n asociaie cu un operator IN, va fi mult mai simplu s gsii toate nregistrrile care nu corespund cu o list de criterii.

Operatorul LIKE. Aici vei nva ce sunt caracterele de nlocuire, cum se folosesc ele i cum s facei cutri cu ajutorul lor. Pn acuma, toi operatorii, foloseau valori cunoscute, iar ei se ocupau de cutarea corespondenelor dintre valori, dac sunt mai mari sau mai mici dect altele, dac verific un domeniu de valori etc. De multe ori apare necesitatea filtrrii nregistrrilor dup unele criterii care nu folosesc valori cunoscute n totalitate. De exemplu, dorii s cutai nume de persoane care ncep cu o liter, care conin un grup de litere etc. Acest lucru nu se poate face cu criterii simple comparare.

O soluie, pe care ne-o propune SQL, este folosirea caracterelor de nlocuire. Caracterele de nlocuire sunt caractere ce au nelesuri speciale n clauzele WHERE din SQL, iar limbajul SQL accept diverse tipuri de caractere de nlocuire.

Pentru a utiliza caracterele de nlocuire n clauzele de cutare, trebuie utilizat operatorul LIKE. Acesta anun sistemul de gestiune a bazei de date c n urmtorul model de cutare se va folosi o potrivire dup caractere de nlocuire, nu o simpl potrivire de egalitate.

Cutarea cu caractere de nlocuire poate fi utilizat numai cu cmpuri de tip text. Reinei acest lucru!

n continuare vor fi prezentate caracterele de nlocuire folosite de programul ACCESS.

23


Caracterul de nlocuire *. Este cel mai frecvent utilizat. ntr-un ir de cutare, * nseamn corespunde cu oricte apariii a oricrui caracter. Exemplul care urmeaz v va ajuta s nelegei acest caracter.

Fig. 3.3. Tabel cu studeni

Ne propunem s filtrm toate nregistrrile n care numele studenilor, din tabelul din figura 3.3, ncepe cu Pop.

Expresie SQL: SELECT Nume, Prenume,Sectia, Grupa, Stare FROM tblStudenti WHERE Nume LIKE Pop* ORDER BY Nume;

Rezultat: Nume Prenume Sectia Grupa Stare Pop Laura IEI 1331 Taxa Popa Ovidiu TCM 1111 Bugetar Popescu Marius Traian IEI 1321 Bugetar Popovici Dumitru TCM 1121 Bugetar

Observai ghilimelele care se pun i ordonarea rezultatului dup cmpul Nume. ncercai i alte filtrri folosind aceast tehnic.

Caracterul de nlocuire ? . Acest caracter (semnul ntrebrii) este utilizat la fel ca simbolul * , dar nu asigur corespondena mai multor caractere, ci numai a unuia singur. Exemplul care urmeaz v ajut s nelegei folosirea lui. Folosind tabelul din figura 3.3, scriei expresii SQL care s ilustreze folosirea acestui caracter de nlocuire.

Observaie! n diferite SGBDR semnele de nlocuire ar putea s fie diferite. Astfel, n Oracle * este nlocuit cu % , iar ? este nlocuit cu liniua de subliniere _ . Pentru a nu avea probleme este bine s studiai documentaia SGBDR-ului pe care l vei folosi.

StudID Nume Init Prenume Sectia An Grupa Stare 1 Bogdan P. Mircea Florin IEI 1 1311 Bugetar 2 Brustur I. Cosmin IEI 1 1312 Taxa 3 Popescu T. Marius Traian IEI 2 1321 Bugetar 4 Brucan P. Mihaela IMPI 2 1321 Bugetar 5 Pop I. Laura IEI 3 1331 Taxa 6 Cotirla L. Raluca Adina TCM 1 1111 Bugetar 7 Popa G. Ovidiu TCM 1 1111 Bugetar 8 Popovici D. Dumitru TCM 2 1121 Bugetar 9 Branea N. Daniel MEC 4 1241 Bugetar

10 Cozma I. Calin Florin MEC 4 1241 Taxa

24


Operaiuni avansate folosind limbajul SQL

Cmpuri calculate

Dup tii de la proiectarea bazelor de date, o regul de baz ne spune c fiecare cmp trebuie s fie independent, adic valoare sa s nu depind de valorile din alte cmpuri. Exemplul clasic care se poate da aici este cel al cmpului Valoare care nu trebuie s apar ntr-un tabel care are Pretul unitar i Cantitatea, dar avem nevoie de acest cmp care este rezultatul nmulirii dintre pre i cantitate. Ei bine, acest cmp trebuie calculat.

Un alt exemplu este cel al adresei care se compune din concatenarea rezultatelor din mai multe cmpuri.

n ambele exemple, datele stocate n tabele nu sunt exact ceea ce are nevoie aplicaia dumneavoastr. n loc s regsii datele aa cum sunt, pentru ca dup aceea s le reformatai n aplicaia client sau n raport, dorii s regsii datele transformate, calculate sau reformatate direct din baza de date.

Aici intervin cmpurile cu valori calculate, pe care le vom numi n continuare cmpuri calculate. Spre deosebire de toate coloanele de pn acum, cmpurile calculate nu exist, de fapt, n baza de date. Un cmp calculat este creat din mers, n interiorul unei instruciuni SELECT din limbajul SQL. De menionat faptul c numai baza de date tie care coloane dintr-o instruciune SELECT sunt realmente coloane din tabele i care sunt cmpuri calculate. Din perspectiva unui client, datele unui cmp calculat sunt returnate n acelai mod ca i datele din oricare coloan.

Cel mai simplu mod de a nelege crearea cmpurilor calculate este de a alege exemple sugestive pe care s le comentm.

Cmpuri calculate prin concatenare. De multe ori apare necesitatea concatenrii valorilor text din mai multe coloane. De exemplu, ntr-un raport trebuie s scriem identitatea unei persoane format din numele complet, aa cum se obinuiete n practic. S scriem expresia SQL care face acest lucru pentru persoanele din tabelul din figura 3.3. Expresie SQL: SELECT Nume + + Init + + Prenume AS Student, Sectia, An, Grupa, Stare FROM tblStudenti ORDER BY Nume;

Rezultat:

Student Sectia An Grupa Stare Bogdan P. Mircea Florin IEI 1 1311 Bugetar Branea N. Daniel MEC 4 1241 Bugetar Brucan P. Mihaela IMPI 2 1321 Bugetar Brustur I. Cosmin IEI 1 1312 Taxa Cotirla L. Raluca Adina TCM 1 1111 Bugetar Cozma I. Calin Florin MEC 4 1241 Taxa Pop I. Laura IEI 3 1331 Taxa Popa G. Ovidiu TCM 1 1111 Bugetar Popescu T. Marius Traian IEI 2 1321 Bugetar Popovici D. Dumitru TCM 2 1121 Bugetar

25


Observai sintaxa folosit i ceva nou a aprut, cuvntul cheie AS dup care urmeaz cuvntul Student, pe care l regsim n capul de tabel, ca nume a noului cmp, obinut prin concatenarea celor trei. Cuvntul cheie AS introduce un alias care este un nou nume dat unui cmp. Reinei aceast tehnic de creare a unor cmpuri.

Cmpuri calculate aritmetic. Aceste cmpuri calculate rezult dup efectuarea unor calcule aritmetice asupra datelor regsite. Pentru a nelege mecanismul crerii acestor cmpuri, vom lua un exemplu practic. Presupunem c avem un tabel n care inem intrrile ntr-o magazie de produse, al unei firme de comer cu piese auto. Tabelul se numete tblProduse i are coloanele Cod, Denumire, Furnizor, PU, Cantitate. Se cere un raport al intrrilor n magazie n care apare i cmpul Valoare, obinut prin produsul cmpurilor PU i Cantitate. n figura 3.4 este prezentat acest raport.

Cod Denumire Furnizor PU Cantitate Valoare 1001 Bujie DK1 Sinterom SA 12 30 360 1023 Acumulator 56A Rombat SA 124 25 3100 1231 Parbriz VW Cobra SRL 512 12 6144 1089 Antigel -30 Promaxim SRL 5.2 50 26 1904 Ulei PKT 1 Calota SRL 6.4 60 384

Fig. 3.4. Raport cu cmpul valoare calculate

Expresie SQL: SELECT Cod, Denumire, Furnizor, PU, Cantitate, PU*Cantitate AS Valoare FROM tblProduse;

Observai i reinei sintaxa folosit pentru crearea noului cmp VALOARE.

Desfurarea lucrrii

Toate expresiile SQL studiate, se vor copia ntr-un fiier Word cu numele Expresii SQL studiate.doc care se va salva n directorul My Documents / Baze de date / Lucrarea 3.

Observaie important! Toate expresiile SQL care vor fi n fiierul Word se presupune c au fost studiate i nelese, studentul putnd oricnd s dea explicaii n legtur cu ele, altfel va suporta consecinele. Aceste expresii vor vor fi testate n lucrarea nr. 4, pe viu, ntr-o baz de date real.

26


Lucrarea nr. 4. Studiu de caz: Baza de date Biblioteca

Aceast lucrare de laborator const n implementarea n Access a bazei de date Biblioteca pe care ai proiectat-o n lucrarea nr. 2. Acum vei folosi cunotinele acumulate despre bazele de date ntr-o aplicaie concret, folosind programul Access.

Consideraii generale despre programul Access

Interfaa programului ACCESS

Pentru a lucra cu orice aplicaie informatic, primul lucru pe care trebuie s-l facem este s-i cunoatem interfaa de pornire. Pentru ACCESS interfaa de pornire apare dup lansarea acestuia cu metoda general Start / Programs / Microsoft Access. Vei gsi repede programul observnd c iconia sa conine mic cheie de yal. Toate capturile i descrierile se refer la versiunea Access 2003. Dac avei alt versiune trebuie s inei cont de diferenele care ar putea apare, care nu sunt, oricum, eseniale.

Spre deosebire de alte programe Microsoft, programul ACCESS ncepe cu o caset de dialog n care vi se cere numele i s alegei directorul bazei de date pe care dorii s o deschidei, chiar dac este una nou (Blank Database). Dup ce ai dat numele bazei de date noi, numit aici db1, va apare interfaa din figura 4.1.

Dup cum se vede, o baz de date ACCESS poate fi definit ca o colecie de obiecte: Tabele (tables) Cereri de interogare (queries) Formulare (forms)

Fig. 4.1. Interfaa de pornire Access (fereastra Database)

27


Rapoarte (reports) Pagini Web (pages) Comenzi macro (macros) Module (modules)

n continuare vor fi prezentate, pe scurt, aceste obiecte, urmnd s fie reluate atunci cnd le vom folosi efectiv.

Tabele (tables) sunt obiecte definite de utilizator n care sunt stocate datele. Sunt obinuitele tabele ale bazelor de date.

Formulare (forms) sunt obiecte care permit introducerea datelor, afiarea acestora sau controlul ntregii aplicaii. Acestea v permit s facei aplicaii performante chiar dac suntei un nceptor n baze de date.

Cereri de interogare (queries) sunt obiecte care permit vizualizarea informaiilor obinute prin prelucrarea datelor din una sau mai multe tabele i/sau alte cereri de interogare. Aici vei folosi din plin cunotinele dobndite despre limbajul SQL. Rapoarte (reports) sunt obiecte care permit formatarea i tiprirea informaiilor obinute n urma consultrii bazei de date, sub form de documente pe hrtie.

Pagini Web (pages) reprezint un obiect care include un fiier HTML i alte fiiere suport n vederea furnizrii accesului la date prin intermediul Internet-ului.

Comenzi macro (macros) reprezint un obiect care conine o definiie structurat a uneia sau mai multor aciuni pe care ACCESS le realizeaz ca un rspuns la un anumit eveniment.

Module (modules) reprezint un obiect care conine proceduri definite de utilizator, scrise n limbajul Visual Basic. Iat o ocazie de a v etala cunotinele de Visual Basic. Pe lng obiectele prezentate, pe interfaa de pornire, mai exist cteva butoane (Open, Design, New, un buton de tergere i cteva de afiare a obiectelor) a cror nelegere i rol este uor de dedus, de aceea nu vor mai fi prezentate.

Dup cum spuneam, ACCESS-ul are un instrument numit wizard care v va ajuta s construii mai uor tabele, formulare sau interogri, dar un singur lucru nu poate face: s v suplineasc lipsa cunotinelor dobndite n capitolele precedente. Astfel, dac nu ai neles mecanismul legturilor dintre tabele, e puin probabil c vei ajunge la rezultate mulumitoare, chiar dac suntei monitorizai ndeaproape de instrumentul wizard.

Crearea tabelelor

nainte de a ncepe s creai tabele, este de la sine neles c avei deja un proiect de baz de date, sau dac suntei la nceput de studiu, mcar o diagram de relaii i structurile tabelelor. Spuneam la nceputul acestui curs, ct de important e s avei un proiect corect de baz de date, pentru a nu avea probleme la faza de implementare.

Crearea structurii tabelelor se poate face n trei moduri: Utiliznd fereastra de proiectare (Create table in design view); Utiliznd instrumentul Wizard (Create table by using wizard); Prin introducerea datelor (Create table by entering data).

28


Consider c modul cel mai eficient de creare a tabelelor, care se potrivete utilizatorilor romni, l reprezint primul mod (Create table in design view), motiv pentru care va fi prezentat numai acesta. Pentru a v satisface curiozitatea nu avei dect s le ncercai pe celelalte dou, pentru a ajunge la o concluzie. Revenind la primul mod de creare a unui tabel, dai un dublu clic pe Create table in design view i pe ecran va apare fereastra Table din figura 4.2.

nelegerea acestei ferestre nu e grea: suntei invitai s dai numele cmpurilor (Field Name), s alegei din combobox tipul de dat pentru cmpul respectiv (Data Type) i s introducei o scurt descriere, acolo unde este cazul, despre ce va conine acel cmp (Description). Pentru alegerea tipului de dat, revedei paragraful Anatomia unei specificaii de cmp din capitolul 3 al cursului de baze de date.

n toolbar-ul de sub meniul din figura 4.2, exist mai multe butoane, unele cunoscute din alte aplicaii Windows. Exist un buton (cel cu o chei) pe care nu l-ai mai ntlnit pn acum. Acest buton v ajut s stabilii cheia principal a tabelului pentru cmpul curent (cel cu sgeata din stnga), printr-un simplu clic pe el.

n funcie de tipul de dat ales pentru cmp, n partea de jos apare un subtabel n care putei seta proprietile cmpului curent. Aceste proprieti sunt adaptate tipului de dat: numr, text, data calendaristic etc. S lum pe rnd aceste proprieti, avnd ca reper tipul de dat numeric:

Field Size este dimensiunea cmpului. Executarea unui clic pe sgeata derulant va deschide o list de opiuni privind dimensiunea cmpului.

Format este formatul sub care se prezint valoarea introdus n cmp. Executarea unui clic pe sgeata derulant va deschide o list de opiuni privind formatul cmpului.

Decimal Places este proprietatea care stabilete numrul de zecimale ce pot fi atribuite cmpului. Se poate alege un numr ntre 0 i 15, sau Auto pentru determinarea automat a numrului de zecimale.

Input Mask reprezint impunerea unui format de introducere pentru toate datele acelui cmp. Formatul de introducere are o mare importan n cadrul cmpurilor ce conin date de tip text sau dat calendaristic.

Fig. 4.2. Fereastra Table

29


Caption este eticheta pentru specificarea unui nume atribuit cmpului, n cazul n care acesta este utilizat n cadrul formularelor sau cnd tabelul respectiv este afiat.

Default Value este o valoare care este atribuit automat, n momentul cnd utilizatorul nu introduce nici o valoare n acel cmp.

Validation Rule este criteriul care va fi verificat nainte de validarea valorii introduse n acel cmp. Criteriul este introdus sub form de expresii care folosesc:

Operatorii: =, +, -, *, /, , =, AND, OR, BETWEEN, IN, IS NULL; Identificatorii se dau n paranteze drepte []; Funcii; Constante;

Validation Text reprezint textul care va aprea pe bara de mesaje n cazul n care valoarea introdus nu respect criteriul impus de regula de validare.

Required este proprietatea care stabilete dac introducerea unei valori n acel cmp este obligatorie. Acest cmp poate avea una din valorile Yes / No.

Indexed este proprietatea care stabilete dac acel cmp are un index care accept valori duplicat sau numai valori unice. Dac nu dorim un index pentru acel cmp se alege valoare No.

Proprietile descrise mai sus se refereau la tipul de cmp Numeric. Pentru alte tipuri de cmp vor apare i proprieti noi (cele mai multe rmn). Astfel, datele de tip Text i Memo au o proprietate numit Allow Zero Length, adic admiterea lungimii zero. Aceast proprietate are valoarea Yes sau No.

O proprietate important pentru cmpul care conine date de tip Autonumber este New Values. Opiunile Increment sau Random permit stabilirea modului n care cmpului respectiv i se vor acorda valori automat de ctre sistem.

Relaii ntre tabele

Relaiile dintre tabele sunt lucruri cunoscute, deja, de ctre oricare dintre dumneavoastr, nu-i aa? S vedem acum ce modalitate folosete ACCESS-ul pentru a face legtura ntre tabele. Din punct de vedere al momentului crerii acestora, exist 2 tipuri de relaii ntre tabelele unei baze de date ACCESS i anume:

Relaii permanente se stabilesc dup definirea tabelelor i sunt cerute de modelul relaional fcnd parte din structura bazei de date. Aceasta se realizeaz, de obicei, prin corespondena cheie primar cheie extern i sunt memorate n baza de date.

Relaii temporare se stabilesc ntre tabele cu ocazia definirii unor cereri de interogare, nefiind nregistrate n structura bazei de date.

Dup cum tim, ntre dou tabele ntre care exist o relaie, datele nu pot fi introduse oricum. De exemplu, dac ntr-o baz de date avem tabelele tblFacturiPrimite i tblFurnizori ntre care exist o relaie unu cu mai muli, nu putem introduce date n tabelul tblFacturiPrimite pn nu avem cel puin un furnizor n tabelul tblFurnizori. Cum rezolv ACCESS-ul aceast prevedere din proiectul bazei de date (tipul de participare)? Prin impunerea integritii refereniale (Enforce Referential Integrity), dup cum se vede n figura 4.3.

30


n urma acestei setri, s-a fcut o legtur unu cu mai muli ntre cele dou tabele, a crei reprezentare se vede n figura 4.4.

Stabilirea legturilor ntre tabele se face, fie n faza de creare a tabelului (folosind Lookup Wizard), fie n fereastra Relationship care se afieaz cu butonul , din toolbar-ul din fereastra Database. Printr-un clic pe acest buton se deschide tabela Relationship prezentat n figura 4.4.

Dac nu apar toate tabelele n fereastra Relationship, cu un clic-dreapta n interiorul acesteia apoi alegnd opiunea Show Table va fi afiat fereastra Show Table, cu care se pot aduga i celelalte tabele.

Legtura dintre tabele se face trgnd cu mouse-ul cmpul de legtur dintr-un tabel peste cmpul corespunztor din cellalt tabel. Dup ce s-a fcut legtura, putem face un clic-dreapta pe legtur, apar 2 posibilitai: Edit Relationship... i se deschide caseta de dialog pentru stabilirea integritii refereniale artat n figura 4.3, respectiv Delete cu care putem terge legtura. Cnd citii aceast secven, este bine s o facei cu calculatorul n fa, deoarece altfel, aceste manevre sunt greu de neles. Dac ceva nu ai neles, ntrebai profesorul care v ndrum la laborator.

Crearea relaiilor cu Lookup Wizard...

Cea mai comod cale de a crea relaii permanente ntre dou tabele este folosirea facilitii Lookup Wizard, pus la dispoziia noastr de programul ACCESS. Aceast metod const n legarea tabelelor n faza de proiectare prin intermediul unei chei primare i a unei chei externe. Prima dat de proiecteaz tabelul din partea unu a relaiei unu cu mai muli. La cel de-al doilea tabel, cnd se definete cmpul de legtur, pentru tipul de dat (Data Type) se alege opiunea Lookup Wizard... (ultima dintre opiuni). n urma acestei manevre se deschide caseta de dialog din figura 4.6.

Fig. 4.3. Stabilirea integritii refereniale

Fig. 4.4. Diagrama de relaie ntre cele 2 tabele, aa cum apare n ACCESS.

31


Se alege prima opiune, cea care ne spune c vom lega cmpul nostru cu un alt cmp dintr-un tabel sau o interogare, I want the lookup.... Merit explicat i opiunea a doua I will type in the values that I want, care ne spune c putem lega acest cmp cu o list de valori introduse de noi chiar n aceast faz. Aceast opiune este indicat s o folosii cnd avei un numr mic de valori pe care poate s le ia un anumit cmp. ncercai i aceast variant pentru a-i nelege rolul.

Cu butonul Next se avanseaz n pasul urmtor cnd vi se cere s alegei, dintr-o list, tabelul cu care va fi legat. Dup alegerea tabelului, vei alege cheia primar i un alt cmp pentru a identifica nregistrarea (acest lucru l vei nelege cnd vei introduce, efectiv, date n tabel). Aceast manevr se vede n figura 4.7.

n cazul nostru se vor selecta cmpurile TaraID i Denumire, care vor fi trecute n fereastra din dreapta. Cu butonul Next se va trece la pasul urmtor (figura 4.8) care ne recomand s face invizibil coloana cu cheia primar, pentru c la operare nu ne va ajuta cu nimic, fiind un cod numeric fr semnificaie pentru noi.

Fig. 4.6. Primul pas al procedurii Lookup Wizard

Fig. 4.7. Alegerea cmpului de legtur

32


Cu butonul Next se trece la pasul urmtor care v cere s stabilii numele cmpului care va apare n rapoarte, de regul se las cel implicit, propus de calculator. Dup aceast manevr se alege butonul Finish care ne va avertiza c o relaie a fost creat i c ar trebui salvat. Dac nu s-a greit nimic, se apas butonul Next.

Pentru a verifica relaia creat se apas butonul , care va deschide foaia Relationships. Cu un clic-dreapta pe linia relaiei o putei edita sau terge. ncercai aceast manevr pentru a nelege ce se ntmpl.

Diferena ntre cele dou tipuri de legturi permanente, legarea prin metoda tragerii (drag and drop) n fereastra Relationship i legarea prin metoda Lookup Wizard..., nu prea iese n eviden dect la crearea formularelor de introducere a datelor. La prima metod Access-ul va pune automat o caset de text (textBox) n care trebuie s introducem manual valori de la tastatur, iar la a doua metod va apare n formular o caset combinat (comboBox), din care putem alege elegant valoarea din lista afiat.

Dac ai reuit s nelegei aceste diferene, nseamn c ai fcut un pas important spre a putea crea aplicaii Access tot mai performante. Poate n acest moment nu sesizai diferenele dintre cele dou metode, dar cu siguran, le vei nelege atunci cnd vei crea formulare, puin mai trziu.

Crearea tabelelor bazei de date Biblioteca

Fig. 4.8. Recomandarea pentru a face invizibil cheia primar

Fig. 4.9. Avertizarea de salvare a relaiei create.

33


Diagrama bazei de date este prezentat n figura 4.10.

Structura i proprietile cmpurile tabelelor bazei de date sunt prezentate n tabelul urmtor:

Tabel Cimp Tip data Constringeri Observatii tblAutori AutorID AutoNumber Not Null Nume Character(150) Not Null Prenume Character(150) Not Null Nationalitate Character(15) DataN Data/Time zz-lll-aa DataD Data/Time zz-lll-aa tblEdituri EdituraID AutoNumber Not Null Denumire Character(150) Not Null Localitate Character(150) Tara Character(50) tblCarti CarteID AutoNumber Not Null AutorID Long Integer Not Null EdituraID Long Integer Not Null Denumire Character(250) Not Null DomeniuID Long Integer Not Null AnAparitie Integer Pagini Integer Valoare Single Stoc Integer tblDomenii DomeniuID AutoNumber Not Null Denumire Character(50) Not Null Explicatii Character(250) tblImprumuturi ImprumutID AutoNumber Not Null CarteID Long Integer Not Null CititorID Long Integer Not Null DataImprumut Data/Time zz-lll-aa Perioada Integer DataRestituire Data/Time zz-lll-aa tblCititori CititorID AutoNumber Not Null

Fig. 4.10. Diagrama bazei de date Biblioteca

34


Nume Character(150) Not Null Prenume Character(150) Not Null ProfesiaID Long Integer Not Null DataN Data/Time zz-lll-aa Adresa Character(150) Localitate Character(150) JudetID Long Integer Not Null Observatii Character(150) tblJudete JudetID AutoNumber Not Null Denumire Character(75) Not Null Abreviere Character(2) Not Null tblProfesii ProfesiaID AutoNumber Not Null Denumire Character(75) Not Null Explicatii Character(200)

Dup crearea tabelelor, caseta Database arat ca n figura 4.11.

n continuarea acestei lucrri de laborator, urmeaz s creai formularele de introducere a datelor, care se vd n figura 4.12. Mai nti vor fi create formularele de introducere a datelor. Dup exemplele prezentate n curs putei trece la crearea acestor formulare.

Fig. 4.11. Tabele bazei de date Biblioteca

Fig. 4.12. Formularele bazei de date Biblioteca

35


Crearea formularelor pentru introducerea datelor

n figura 4.12 se vd formularele de introducere a datelor n tabele. Avnd n vedere relaiile stabilite intre tabele, introducerea datelor nu poate fi fcut oricum. Astfel, nu putem introduce o care dac numele autorului nu este n tabelul cu autori. Ca regul, n relaiile 1:N, mai nti se vor introduce date n tabelul din partea unu a relaiei i numai apoi n tabelul din partea mai muli a relaiei.

Formularul frmIntroducereAutori.

Pentru crearea formularului, parcurgem paii urmtori:

1. n caseta de dialog Database, dm comanda Forms New AutoForm:Columnar(vezi figura 4.13).

Se alege tabelul tblAutori, care va fi completat de ctre viitorul formular. Se apas butonul OK.

2. Va apare forma brut a formularului, aa cum apare n figura 4.14.

Fig. 4.13. Alegerea tipului de formular i a tabelului

Fig. 4.14. Formularul brut

36


Acest formular ar mai trebui puin aranjat. De exemplu, pentru cmpul Nationalitate ar trebui s avem un comboBox din care s alegem dintr-o list, nationalitatea autorului. Pentru cmpurile cu date calendaristice DataN i DataD ar trebui s indicm sub ce form trebuie introduse aceste date, stiut fiind c la acest tip de dat apar des probleme din cauza introducerilor incorecte.

Modificarea formularului se poate face numai n modul Design view, care se obine apsnd butonul . Figura 4.15 arat cum trebuie s fie formularul n Design view.

Pentru a transforma caseta de text Nationalitate, executm clic-dreapta pe ea, alegem opiune Change To, apoi opiunea Combo Box, dup care va trebui s introducem lista cu naionalitile autorilor. Acest lucru se face prin completarea proprietilor Row Source Type (Value List) i Row Source (Romana; Maghiara; Straina), dup cum se poate vedea n figura 4.16.

De asemenea, s-au introdus 2 etichete (zz/lll/aa) care ne indic, cum s introducem data calendaristic.

Pentru activarea tabelului cu proprietile se apas butonul din toolbar-ul din partea de sus.

Fig. 4.15. Formularul n modul Design view

37


3. Prin apsarea butonului , se iese din modul Design i se activeaz formularul care trebuie s arate ca n figura 4.17.

Formularul frmIntroducereCarti.

nainte de a ncepe crearea efectiv a formularului, trebuie s analizm tabelul pe care l va completa. Acesta are 3 cmpuri care trebuie completate cu chei externe: AutorID, EditurID i DomeniuID. Aceste chei sunt valori de tip AutoNumber care sunt greu de folosit pentru c trebuie, de exemplu, introdus un autor printr-un numr. Ar fi mult mai potrivit pentru operator s aleag dintr-o list un nume de autor cu care e obinuit i nu un cod numeric care nu i sugereaz nimic. Este ceea ce ne propunem.

Pentru crearea acestui formular primii pai sunt identici cu cel precedent, cu observaia c vom alege acum tabelul tblCarti.

Trecem n modul Design view, pentru a putea efectua modificrile n formular. n figura 4.18, putei vedea cum arat formularul nainte de setarea proprietilor. Se observ c acesta are ca surs tabelul tblCarti.

Fig. 4.16. Atribuirea valorilor pentru proprietile Row Source Type i Row Source

Fig. 4.17. Forma final a formularului

Fig. 4.18. Formularul n stare brut

38


n aceast stare, pe baza legturilor dintre tabele, n casetele comboBox vor apare listele cu cheile primare ale celor 3 cmpuri implicate. Pentru ca n aceste casete s apar denumirile, cu care suntem obinuii, trebuie s modificm unele proprieti ale celor 3 comboBox-uri, care se gsesc n categoria Format (vezi primul buton).

Setarea surselor se pot deduce clar din figura 4.19.

Observai c sursele pentru cele 3 cmpuri de chei externe sunt nite expresii SQL simple:

AutorID: SELECT tblAutori.AutorID, tblAutori.Prenume+" "+tblAutori.Nume AS Expr1 FROM tblAutori ORDER BY tblAutori.Nume;

EdituraID: SELECT tblEdituri.EdituraID, tblEdituri.Denumire FROM tblEdituri ORDER BY Denumire;

DomeniuID: SELECT tblDomenii.DomeniuID, tblDomenii.Denumire FROM tblDomenii ORDER BY Denumire;

Fig. 4.19. Setarea proprietilor legate de sursa nregistrrilor

39


Fiecare din aceste expresii au ca rezultat cte 2 cmpuri, iar pe noi ne intereseaz numai al doilea care ne spune ceva concret. Trebuie s afim, deci, numai acest cmp. Pentru aceasta trebuie s setm, din tab-ul Format, proprietile Column Count(2) i Column Width(0;1.2), care ne spun c avem 2 coloane, care au limea de 0, respectiv 1.2 inch. Asta nseamn c prima coloan nu se vede, iar a doua are 1.2 inch lime.

Setarea acestor proprieti se vede n figura 4.20.

Prin apsarea butonului , se iese din modul Design i se activeaz formularul care trebuie s arate ca n figura 4.21.

Fig. 4.20. Setarea proprietilor Column Count i Column Widths

Fig. 4.21. Forma final a formularului

40


Dup modelele prezentate, creai i celelalte formulare de introducere a datelor, prezentate mai jos n figura 4.22.

Fig. 4.22. Formulare care trebuie create

41


Testarea expresiilor SQL de la lucrarea 3

Pentru a scrie expresiile SQL n Access trebuie s deschidem fereastra corespunztoare. Vei proceda astfel:

n fereastra Database se apas butonul Queries apoi se d dublu-click pe opiunea Create query in Design view .

Se va deschide fereastra Show table unde se apas butonul Close.

n partea stng a Toolbar-ului superior, va apare butonul , prin apsarea cruia se deschide fereastra din figura 4.23.

Observai c n mod automat apare scris instruciunea SELECT; care se termin cu punct i virgul, care semnific terminarea expresiei SQL. n aceast fereastr se scriu de mn expresiile SQL. Nu uitai de simbolul ; punct i virgul de la sfritul expresiei.

Pentru verificarea expresiei SQL introduse folosii butonul Run SQL localizat conform figurii 4.24.

Dac totul a decurs bine, se va afia un tabel cu datele generate de interogare.

Desfurarea lucrrii

Deschidei o baz de date nou, cu numele Expresii_SQL. mdb, n cadrul creia se vor face toate testrile.

La lucrarea nr. 3 ai salvat n fiierul Expresii SQL studiate.txt mai multe expresii SQL pe care acum o s le reluai i o s le verificai n cadrul programului Access. Expresiile SQL se pot copia direct din fiier. Rezultatele pe care le vei obine vor trebui s corespund cu cele de la lucrarea nr. 3.

Va trebui s creai i s umplei cu date tabelele, aa cum sunt prezentate n exemplele de la lucrarea nr. 3.

Tabelele care vor trebui create sunt, tblStudenti i tblEvidenta care sunt artate mai jos:

Fig. 4.23. Fereastra pentru scrierea

expresiilor SQL

Fig. 4. 24. Butonul Run SQL

42


Tipul de dat pentru fiecare cmp l vei deduce din tabelele afiate. Pentru a obine rezultatele de la lucrarea 3, va trebui s introducei n tabele aceleai date.

Fiecare expresie SQL va fi salvat cu un nume sugestiv.

StudID Nume Init Prenume Sectia An Grupa Stare 1 Bogdan P. Mircea Florin IEI 1 1311 Bugetar 2 Meruta I. Cosmin IEI 3 1332 Bugetar 3 Pop T. Marius Traian IEI 2 1321 Bugetar 4 Bucur P. Mihaela IEI 2 1321 Bugetar 5 Chirila I. Laura IEI 3 1331 Bugetar 6 Cotirla L. Raluca Adina TCM 1 1111 Bugetar 7 Cotoara G. Ovidiu TCM 1 1111 Bugetar 8 Cozma D. Dumitru TCM 2 1121 Bugetar 9 Damian N. Daniel MEC 4 1241 Bugetar

10 Farcas I. Calin Florin MEC 4 1241 Taxa

Cod Denumire Furnizor PU Cantitate Valoare 1001 Bujie DK1 Sinterom SA 12 30 360 1023 Acumulator 56A Rombat SA 124 25 3100 1231 Parbriz VW Cobra SRL 512 12 6144 1089 Antigel -30 Promaxim SRL 5.2 50 26 1904 Ulei PKT 1 Calota SRL 6.4 60 384

tblStudenti

tblEvidenta

43


Crearea interogrilor

Vederile, aa cum le-am nvat la curs, se numesc n Access, interogri (queries). Ne reamintim c vederile sunt nite tabele virtuale, aduc ele nu exist n realitate ci sunt generate de ctre programul Access, atunci cnd avem nevoie de ele. Aceste tabele virtuale sunt completate cu date din unul sau mai multe tabele.

Vederile sunt, putem spune, rezultatul sau scopul pentru care am muncit s crem o baz de date. Acestea, n combinaie cu formularele i rapoartele stabilesc ct de performant e aplicaia de baz de date pe care ai creat-o sau o folosii.

n cazul aplicaiei noastre, Biblioteca.mdb, vederile sunt sugerate de obiectivele misiunii. Sigur c n faza de proiectare, nu pot fi identificate toate vederile, dar la o baz de date bine proiectat, necesitatea unei vederi poate fi rezolvat oricnd.

n continuare vom ncerca s crem cteva vederi, folosind limbajul SQL.

Vederea Crile din bibiotec

Ai putea spune, pe bun dreptate, c toate crile se afl n tabelul tblCarti, deci n-ar mai fi nevoie de o vedere. ncercai s afiai acest tabel (un dublu clic pe numele lui). Vei observa c la autori, edituri i domenii avem nite coduri care nu ne spun mare lucru, de fapt vedei cheile primare ale acestora. O astfel de informaie nu este de mare ajutor, de aceea va trebui s creai o vedere din care s se vad denumirile explicite, pentru autori, edituri i domenii, pe baza legturilor dintre tabele.

Pentru a scrie expresiile SQL trebuie s deschidem fereastra corespunztoare. Vei proceda astfel:

n fereastra Database se apas butonul Queries apoi se d dublu-click pe opiunea Create query in Design view .

Se va deschide fereastra Show table unde se apas butonul Close.

n partea stng a Toolbar-ului superior, va apare butonul , prin apsarea cruia se deschide fereastra din figura 4.23.

Observai c n mod automat apare scris instruciunea SELECT; care se termin cu punct i virgul, care semnific terminarea expresiei SQL. n aceast fereastr se scriu de mn expresiile SQL. Nu uitai de simbolul ; punct i virgul de la sfritul expresiei.

Pentru verificarea expresiei SQL introduse folosii butonul Run SQL localizat conform figurii 4.24.

Fig. 4.23. Fereastra pentru scrierea

expresiilor SQL

44


Dac totul a decurs bine, se va afia un tabel cu datele generate de interogare.

n cazul nostru expresia SQL care trebuie introdus, se poate vedea n figura 4.25.

Iat expresia SQL, scris mai clar: SELECT tblAutori.Prenume + " " + tblAutori.Nume AS Autor, tblCarti.Denumire, tblEdituri.Denumire AS Editura, tblCarti.AnAparitie AS Aparut, tblCarti.Pagini, tblCarti.Valoare, tblCarti.Stoc FROM tblCarti, tblAutori, tblEdituri WHERE tblCarti.AutorID=tblAutori.AutorID AND tblCarti.EdituraID=tblEdituri.EdituraID ORDER BY tblCarti.Denumire;

Lansai aceast comand SQL cu butonul Run SQL, iar dac nu ai fcut vreo greeal ar trebui s obinei ceea ce se vede n figura 4.26.

Din aceast vedere se pot vedea clar informaiile despre crile din biblioteca noastr. Bineneles c voi o s avei afiate crile pe care le-ai introdus n baza de date pe care ai creat-o.

Fig. 4. 24. Butonul Run SQL

Fig. 4.25. Expresia SQL pentru afiarea crilor din bibliotec

Fig. 4.26. Interogarea cu crile din bibliotec

45


Vederea Crile mprumutate

Expresia SQL este: SELECT tblCarti.Denumire AS [Denumirea cartii], tblCititori.Prenume+" "+tblCititori.Nume AS Cititor, tblImprumuturi.DataImprumut, tblImprumuturi.Perioada, tblImprumuturi.DataRestituire FROM tblCarti, tblAutori, tblEdituri, tblImprumuturi, tblCititori WHERE tblImprumuturi.CarteID=tblCarti.CarteID AND tblCarti.EdituraID=tblEdituri.EdituraID AND tblCarti.AutorID=tblAutori.AutorID AND tblImprumuturi.CititorID=tblCititori.CititorID ORDER BY tblCarti.Denumire;

Rezultatul este prezentat n figura 4.27.

Vederea Situaie cititor

Una dintre cele mai importante informaii pentru bibliotecari este situaia de mprumuturi a unui cititor, adic ce cri i cnd a scos cititorul respectiv, eventual cnd le-a restituit. O astfel de interogare, care este o interogare cu parametrii, are expresia SQL de mai jos: SELECT tblCititori.Nume + " " + tblCititori.Prenume AS Cititor, tblCarti.Denumire AS [Denumirea cartii], tblImprumuturi.DataImprumut, tblImprumuturi.Perioada, tblImprumuturi.DataRestituire FROM tblImprumuturi, tblCarti, tblCititori WHERE tblImprumuturi.CititorID=tblCititori.CititorID AND tblImprumuturi.CarteID=tblCarti.CarteID AND tblImprumuturi.CititorID=[Introduceti ID-ul cititorului];

Observai cum a fost introdus expresia pentru cererea parametrului (ultimul rnd). Cererea parametrului a fost introdus ntre paranteze drepte.

Situaia cititorului Pop Mircea cu ID-ul 4, este prezentat n figura 4.28.

Pentru a lista situaia unui cititor trebuie numai s reinei ID-ul acestuia care se poate afla din tabelul tblCititori.

Fig. 4.27. Rezultatul interogrii qryImprumuturi

Fig. 4.28. Situaia unui anumit cititor

46


Pe baza acestor exemple, ncercai s identificai i alte vederi pe care le-ai putea crea. Sugestii: crile unei anumite edituri, ce cri au scos pensionarii, elevii, sau studenii etc.

Crearea formularelor pentru vizualizarea informaiilor

Dup cum tim, formularele se pot folosi att pentru introducerea datelor n tabelele bazei de date, ct i n vizualizarea informaiilor din baza de date. Formularele de introducere de date sunt mai uor de creat, deoarece se bazeaz, n general, pe un tabel, eventual pe cte o interogare simpl ataat de vreun comboBox. Formularele de vizualizare sunt mai greu de fcut, deoarece se bazeaz pe interogri uneori complicate, iar uneori trebuie scris i ceva cod n VBA.

Acest tip de formulare conin, pe lng cmpurile provenite din tabele i interogri, obiecte suplimentare care le fac mai performante, cum ar fi butoane de comand, etichete etc., numite generic, controale. Aceste controale se gsesc n toolbar-ul Toolbox, prezentat n figura 4.29.

n tabelul urmtor vor fi descrise aceste controale.

Numele controlului Descriere

Select Objects (indicator) Deselecteaz controlul selectat anterior i restabilete funcia normal pentru indicatorul mouse-ului. Butonul Pointer este controlul activat implicit atunci cnd este afiat bara de instrumente Toolbox.

Control Wizards Activeaz/Dezactiveaz programele Control Wizards. Aceste utilitare ne ajut s generm obiecte de control complexe, cum ar fi grupurile de opiune, casetele de liste i casetele combinate.

Label Conine orice text fix, folosit pentru diferite descrieri sau notaii de pe formular.

Text Box Caset n care se afieaz sau din care se citesc date de tip text. Este acelai obiect ca cel studiat n Visual Basic.

Option Group Caset dreptunghiular de dimensiune variabil, n care putem plasa, butoane comutatoare, butoane de opiune sau casete de validare. Numai un singur obiect de control din interiorul acestei casete poate fi selectat la un moment dat. Cnd executm click pe un obiect din grup obiectul selectat anterior va fi deselectat. Deci, casetele de validare nu se comport ca n Visual Basic, unde puteau fi selectate, nici una, una sau mai multe.

Toggle Button Buton care, atunci cnd e selectat, comut din starea ON n starea OFF. Starea ON corespunde valorii Yes (-1), iar starea OFF corespunde valorii No (0). n cadrul unui grup de opiune, acionarea unui buton comutator dezactiveaz butonul comutator acionat anterior. Putem utiliza aceste butoane pentru a permite

Fig. 4.29. Bara de instrumente Toolbox

47


utilizatorului s selecteze o valoare dintr-un set.

Option Button Buton rotund care se comport identic cu un buton comutator. Butoanele de acest tip sunt utilizate cel mai des n grupurile de opiune pentru selectarea unei valori dintr-un set de opiuni.

Check Box Caset de validare care, atunci cnd este selectat, comut ntre starea ON i starea OFF. Casetele de validare multiple trebuie plasate n afara grupurilor de opiune astfel nct s putem selecta mai multe opiuni simultan.

Combo Box Caset combinat format dintr-o caset de text editabil, n care putem introdu