Lucrarea de laborator nr. 4 (10 ore)

Tema: Baze de date relaţionale. Principiile de stocare a informaţiei în tabele

Obiective generale :a) Familiarizarea studenţilor cu proiectarea unui model de baze de date;b) Crearea fişierului bazei de date; p roiectarea şi completarea tabelelor cu

date.

Obiective operaţionale:a) Să cunoască elementele de bază ale ferestrei aplicaţiei Access şi ale ferestrei

bazei de date;b) Să cunoască structura ferestrei de proiectare a tabelelor bazei de date şi să

o poată completa cu date;c) Să cunoască tipurile şi proprietăţile datelor în Access, şi să efectueze

gestiunea lor;d) Să cunoască care este rolul cheii primare şi să aplice în practică metodele

de modificare a câmpului cheie primară; Să înţeleagă însemnătatea creării legăturii între tabelele bazei de date şi să o poată realiza.

Sarcini:a) Definirea scopului

Exemplu: Să se creeze o bază de date care să automatizeze activitatea unei firme comerciale, obiectul de acţiune al coreea constă în comercializarea autoturismelor.

Din enunţul problemei propuse rezultă că baza de date va fi alcătuită din cel puţin trei tabele (în problema propusă se vorbeşte despre automobile, comercializări, cumpărători).

Fiecare model auto propus spre vânzare este identificat prin: codul modelului, denumirea lui, puterea motorului, culoarea automobilului, numărul de uşi, cutia de transmisie (automată sau manuală), căptuşeala, preţul fixat de uzină, cheltuielile de transport, pregătirea pentru vânzare, tipul modelului (special sau nu special).

Clienţii, adică cumpărătorii , sunt persoane fizice identificate prin: codul clientului, forma de adresare, nume, prenume, localitate, adresă, telefon.

Tranzacţiile ce au fost efectuate sunt specificate prin: codul tranzacţiei, codul modelului, codul clientului, preţul de vânzare, data efectuării tranzacţiei, data livrării automobilului, numărul de automobile comandat de fiecare client.

b) Proiectarea tabelelor bazei de date (definirea câmpurilor, proprietăţilor, cheia primară)

1. Proiectaţi structura tabelului Modele Auto şi proprietăţile câmpurilor (tabelele 5.4, 5.5);2. Definiţi cheia primară pentru câmpul Codul modelului ;3. Salvaţi tabelul;4. Proiectaţi structura tabelului Clienţi şi proprietăţile

1

câmpurilor (tabelele 5.6, 5.7);5. Definiţi cheia primară pentru câmpul Codul clientului ;6. Salvaţi tabelul;7. Proiectaţi structura tabelului Tranzacţii şi proprietăţile câmpurilor (tabelele 5.8, 5.9);8. Definiţi cheia primară pentru câmpul Codul tranzacţiei ;9. Salvaţi tabelul.

c) Definirea valorilor câmpurilor1. Afişaţi tabelul Modele Auto în regim de introducere a datelor;2. Completaţi tabelul cu date (figura 5.6);3. Închideţi tabelul;4. Afişaţi tabelul Tranzacţii în regim de introducere a datelor;5. Completaţi tabelul cu date (figura 5.7);6. Închideţi tabelul;7. Afişaţi tabelul Clienţi în regim de introducere a datelor;8. Completaţi tabelul cu date (figura 5.8);9. Închideţi tabelul.

d) Definirea relaţiilor dintre tabele1. Pentru tabelele Modele Auto şi Tranzacţii , definiţi corelaţia de tipul 1:n

(one to many), câmpul de legătură fiind Codul modelului ;2. Asiguraţi-vă integritatea referenţială, modificarea în cascadă, excluderea

în cascadă a înregistrărilor;3. Pentru tabelele Clienţi şi Tranzacţii , definiţi corelaţia de tipul 1:n (one to

many), câmpul de legătură fiind Codul clientului ;4. Asiguraţi-vă integritatea referenţială, modificarea în cascadă, excluderea

în cascadă a înregistrărilor;5. Salvaţi relaţiile create;6. Închideţi baza de date şi programul Access.

e) Renovarea structurii bazei de date

Excludeţi câmpul Preţ de vânzare din tabelul Tranzacţii .

Crearea tabelului unei BD constă din două etape:

Proiectarea structurii (Table Design) ; Introducerea datelor (Datasheet View).Pentru a stabili cheia primară şi a finaliza proiectarea structurii tabelului:

1. Selectați tabelul al cărui cheie primară doriți să o setați sau să o modificați;2. În modul Table Design selectaţi câmpul ales pentru cheia primară, în

exemplul dat Codul modelului,(Codul tranzacţiei, Codul clientului) ;3. În fila Pornire (Home), în grupul Vizualizări (Views), faceți clic pe Vizualizare

(View), apoi pe Vizualizare proiect (Design view);4. În grila de proiectare a tabelului, selectați câmpul sau câmpurile pe care doriți să le

utilizați pe post de cheie primară;Pentru a selecta un câmp, faceți clic pe selectorul de rând pentru câmpul pe care îl doriți.Pentru a selecta mai multe câmpuri, mențineți apăsată tasta CTRL, apoi faceți clic pe

selectorul de rând pentru fiecare rând.

2

5. În fila Proiect (Design), în grupul Instrumente (Tools), faceți clic pe Cheie primară (Primary Key), figura 5.1.

Figura 5.1. Grupul (Tools), butonul Primary Key

Un indicator de cheie se va afișa în stânga câmpului sau câmpurilor pe care le specificați ca fiind cheie primară.

De exemplu, se cere de creat tabelul Modele Auto.

La etapă de proiectare a structurii acestui tabel se vor lua în consideraţie datele din tabelul 5.5.

Se introduce numele câmpului , se selectează tipul datelor , se introduc comentariile .

Tabelul 5.5Definirea câmpurilor şi a tipurilor de date (tabelul Modele Auto)

Numele câmpului(Field Name)

Tipul de date(Data Type)

Descrierea(Discription)

Codul modelului AutoNumber Codul conform regis t rului

Modelul Text Denumirea modelului

Puterea motorului Text Puterea motorului

Culoarea Text Culoarea Auto

Numărul de uşi Number 3 sau 4 sau 5

Cutia de t ransmisie Text Automată sau manuală

Căptuşeala Text Catifea, piele , s tofă e tc .

Alte caracter is t ic i Memo Caracter is t ic i le supl imentare

Preţul f ixat de uzina Currency Preţul s tabi l i t de uzină

Chel tuiel i le de t ransport Currency Cheltuiel i le de t ransport

Pregăt i rea de vânzare Currency Cheltuiel i le pentru vânzare

Model special Yes/No Model special

Pentru fiecare câmp se completează secţiunea Field Properties. Pentru aceasta este necesar să activaţi celula cu denumirea câmpului şi să introduceţi proprietăţile (modificându-le în caz de necesitate), corespunzător listei din tabelul 5.6.

3

Tabelul 5.6Definirea proprietăţilor câmpurilor (tabelul Modele auto)

Field Name Field Properties

Codul modelului AutoNumber (Celelalte proprietăţi rămăn neschimbate).

Modelul Field Size: 18Indexed: Yes(Duplicates Ok)

Puterea motorului Field Size: 9

Culoarea Field Size: 17

Numărul de uşi Field Size: ByteFormat: StandardDecimal places: 0Default Value : 4Validation Rule: 2 or 3 or 4 or 5Validation Text: Valoare gresită pentru campul Numărul de uşi

Cutia de transmisie Field Size: 12Default value: ManualValidation Rule: Manual or AutomaticValiation Text : Valoare greşită pentru câmpul Cutia de transmisie

Căptuşeala Field Size: 20

Alte caracterist ici câmp de tip Memo , nu necesită nici un fel de restricţi i în privinţa lungimii ;

Preţul f ixat de uzină Format: CurrencyDecimal places: Auto

Cheltuieli le de transport

Format: CurrencyDecimal places: Auto

Pregătirea de vânzare Format: Currency

Decimal places: AutoDefault value: 500

Model special Format: (Yes/No)

Deoarece baza de date Auto constă din trei tabele , creaţi al doilea tabel Tranzacţii , conform algoritmului pentru tabelul Modele Auto , cu datele prezentate în tabelele 5.7 şi 5.8 şi al treilea tabel Clienţi conform datelor ilustrate în tabele 5.9 şi 5.10

Tabelul 5.7Definirea câmpurilor şi a tipurilor de date(tabelul Tranzacţii)

Field Name Data Type Discription

Codul t ranzacţ ie i Auto Number Codul t ranzacţ ie i (c i fre)

Codul modelului Number Codul modelului procurat

Codul c l ientului Number Codul c l ientului care procură modelul

Preţul de vânzare Currency Preţul de vânzare

4

Field Name Data Type Discription

Data t ranzacţ ie i Date /Time Data când a fost efectuată comanda

Data l ivrăr i i Date /Time Data executăr i i comenzi i

Numărul de Auto Number Numărul de automobile comandate

Alte note Memo Comentar i i , observaţ i i , note

Tabelul 5.8Definirea proprietăţilor câmpurilor (tabelul Tranzacţii)

Field Name Field PropertiesCodul t ranzacţ ie i Indexed: Yes (No Duplicates)

Cheie primară

Codul modelului

Indexed , pentru toate câmpurile : Yes (Duplicates Ok)

Codul c l ientului

Preţul de vânzare

Data t ranzacţ ie i

Data l ivrăr i i

Numărul de Auto

Alte note

Salvaţi tabelul creat, atribuindu-i numele Tranzacţii . Deschideţi tabelul în modul de vizualizare Open şi introduceţi datele (minimum 30 de înregistrări).

Tabelul 5.9Definirea câmpurilor şi a tipurilor de date(tabelul Clienţi)

Field Name Data Type Discription

Codul c l ientului Auto Number Codul c l ientului

Adresarea Text Domnul , Doamna sau Domnişoara

Numele Text Indicaţ i Numele

Prenumele Text Indicaţ i Prenumele

Local i ta tea Text Oraşul sau satul de reşedinţă .

Adresa Text Strada, blocul e tc .

Telefonul Text Numărul de te lefon

Note Memo Note speciale despre c l ient

5

Tabelul 5.10Definirea proprietăţilor câmpurilor (tabelul Clienţi)

Field Name Field PropertiesCodul c l ientului Indexed: Yes (No Duplicates) Cheie

primară

Adresarea Indexed , pentru toate câmpurile :Yes (Duplicates Ok)

Indexed , pentru toate câmpurile : Yes (Duplicates Ok)

Numele

Prenumele

Local i ta tea

Adresa

Telefonul

Note

Salvaţi tabelul creat, atribuindu-i numele Tranzacţii . Deschideţi tabelul în modul de vizualizare Open şi introduceţi datele (minimum 30 de înregistrări).

Crearea unei chei primare unice este denumită în baze de date asigurarea integrităţii de entitate.

Pentru a trece la a doua etapă de creare a tabelelor , etapa de inserare a datelor în tabel, se va deschide tabelul creat( Modele Auto) în modul de vizualizare Open şi se vor introduce, de exemplu, 30 de înregistrări (figura 5.11. În mod analog pentru tabelele Tranzacţii (figura 5.12) şi Clienţi (figura 5.13).

Figura 5.11. Datele din tabelul Modele Auto

Figura 5.12. Datele din tabelul Tranzacţ i i

6

Figura 5.13. Datele din tabelul Clienţ i

Adăugarea unui câmp prin introducerea de date

Când creați un tabel nou sau deschideți un tabel existent în Vizualizarea foaie de date (DataSheet View), aveți posibilitatea să adăugați un câmp la tabel prin introducerea datelor în coloana Adăugare câmp nou (Click to Add) din foaia de date.

Crearea unei relații între tabele

1. În fila Fișier (File), faceți clic pe Deschidere (Open);2. În caseta de dialog Deschidere (Open), selectați și deschideți baza de date;3. În fila Instrumente bază de date (DatabaseTools), în grupul Relații (Relationships),

faceți clic pe Relații (Relationships)

1. Se va deschide un panou de editare vizuală a acestora. Prima activare a acestei opţiuni are efect şi afişarea unei ferestre cu obiectele bazei de date ce se pot plasa în panou prin dublu-clic sau selectarea obiectului şi butonul Add (figura 5.14).

Figura 5.14. Amplasarea obiectelor în fereastra pentru relaţii

Pentru a defini o legătură între două tabele, trebuie să existe compatibilitate între tipul de date al cheii primare şi cel al cheii externe corespunzătoare. După plasarea tuturor tabelelor în panoul de editare, definirea propriu-zisă a unei relaţii se realizează prin metoda drag-and-drop (selectare şi glisare) dinspre cheia primară a unui tabel spre cheia externă corespunzătoare

7

sau invers. După realizarea acestei operaţii , finalizarea definirii unei legături se realizează în fereastra din figura 5.15, unde este prezentată definirea unei relaţii între tabelul „Modele Auto” (cheie primară Codul Modelului ) şi tabelul „Tranzacţii” (cheie externă Codul Modelului ).

Figura 5.15 Definirea unei legături între două tabeleDacă o cheie este formată din mai multe câmpuri, acestea se editează

corespunzător în fereastral Edit Relationship, figura 5.10. Opţiunile din fereastra Edit Relationships au următoarea semnificaţie: Enforce Referenţial Integrity - defineşte o restricţie de integritate

referenţială. Nu se permite actualizarea valorii unei chei externe, dacă această valoare nu există deja în tabelul unde aceasta este cheie primară.

Cascade Update Reiated Fields - actualizarea valorii unei chei primare dintr-un tabel se propagă şi în înregistrările din tabelele unde aceasta este cheie externă.

Cascade Delete Reiated Fields - ştergerea valorii unei chei primare, are ca efect şi ştergerea înregistrărilor din tabelele unde aceasta este cheie externă.

În caseta Relationship Type sunt prezentate tipurile de legături care pot exista între două tabele: One to One - 1-1, One to Many - 1-n.

Dacă în această casetă este afişat mesajul Indeterminate , înseamnă că tipul de relaţie nu poate fi definit, cea mai probabilă eroare fiind incompatibilitatea între tipul de date al cheii primare şi cel al cheii externe sau inexistenţa unei chei primare în relaţia respectivă .

Butonul de comandă Join Type permite şi definirea unor tipuri de relaţii particulare între tabele, aşa cum se observă din figura 5.16.

Figura 5.16. Caseta Join Propertîes

8

Variantele 2 şi 3 permit utilizarea implicită în interogări a join-urilor externe de tip Left şi Right , care presupun într-o interogare luarea în considerare a tuturor înregistrărilor dintr-un tabel, indiferent dacă au sau nu înregistrări corespondente după câmpul (câmpurile) de legătură în celalalt tabel. În exemplul de legătură prezentat în figura 5.11, este posibilă o interogare între cele două tabele, în care vor fi luate în considerare toate înregistrările din tabelul Modele Auto , indiferent dacă au sau nu corespondent în tabelul Tranzacţii .

Deschiderea ferestrei Relații (Relationships)1. În fila Fișier (File), faceți clic pe Deschidere (Open);2. În caseta de dialog Deschidere (Open), selectați și deschideți baza de date;3. În fila Instrumente bază de date (DatabaseTools), în grupul Relații (Relationships),

faceți clic pe Relații (Relationships) figura 5.17;

Figura 5.17. Grupul Relationships4. Dacă baza de date conține relații, apare fereastra Relații (Relationships); 5. Dacă baza de date nu conține relații și deschideți fereastra Relații pentru prima oară,

apare caseta de dialog Afișare tabel (Show Table). Faceți clic pe Închidere (Close) , figura 5.18 pentru a închide caseta de dialog.

Figura 5.18. Grupul Relationships

Sarcini de control

Proiectaţi baza de date (personală) pentru informatizarea unuia din următoarele domenii de activitate:

1. TransportAngajaţi i f irmei sunt identificaţi prin: nume, adresă şi număr de telefon.Şoferii sunt remuneraţi printr-un salariul de bază , la care se adaugă sporuri de

vechime în muncă şi prime în funcţie de kilometrii parcurşi .Firma dispune de un parc auto ce constă din autovehicule caracterizate prin:

număr de înmatriculare, model şi o serie de detali i tehnice: capacitatea de transport (pentru transport marfă), numărul de locuri (pentru transport pasageri) , consumul de carburanţi , anul de producere şi data efectuării ult imului control tehnic.

Pentru f iecare transport efectuat se completează o f işă pe care sunt consemnate data şi ora plecării .

Clienţi i pot f i atât persoane fizice, cât şi persoane juridice, caz în care se specifică denumirea firmei şi codul fiscal .

Tarifarea servicii lor se face în mod diferenţiat , în funcţie de t ipul vehiculului solici tat ş i de distanţa pe care o efectuează transportul , prin aplicarea tarifului pe kilometrul parcurs de respectivul t ip de vehicul la numărul total de kilometri ( firma dispune de un nomenclator al distanţelor pe destinaţi i) .

Vehiculele necesită reparaţi i periodice ce implică consumuri de piese de schimb.

9

2. Societatea de asigurări Clienţi i societăţi i pot f i persoane fizice şi juridice caracterizate printr-un număr

unic, nume şi prenume / denumire, adresă şi telefon. Aceştia pot să încheie diferi te t ipuri de asigurări (de bunuri , pe viaţă etc.) . Asigurări le sunt încheiate de agenţi ce sunt identificaţi printr-un cod unic, nume şi prenume . Contractul de asigurare este caracterizat printr-un număr, data încheierii , obiectul asigurării , perioada (în luni) , valoarea asigurării şi prima ce va trebui plăti tă în f iecare lună de client.

Clienţi i efectuează plata primelor prin ordin de plată ( persoane juridice ) sau direct la casierie (persoane fizice), el iberându-se chitanţe.

Documentul de plată conţine: numărul documentului , data la care a fost întocmit şi suma care a fost plăti tă. În momentul producerii riscului pentru care a fost încheiată asigurarea, societatea plăteşte cl ientului despăgubiri . La plata despăgubiri lor se întocmeşte un proces verbal care este caracterizat printr-un număr, data încheierii , descrierea cauzei ce a generat despăgubirea şi procentul prin care este despăgubit cl ientul .

3. Bancar Se cunosc următoarele date : unui cont bancar î i pot corespunde mai multe carduri , în t imp ce un card este emis pentru operaţiuni vizând un singur cont. Cu un card se pot efectua mai multe tranzacţi i . Pentru deschiderea unui cont de card se încheie un contract între un client şi o agenţie bancară. O agenţie bancară poate administra mai multe conturi de card, dar un card nu poate f i administrat decât de o singură agenţie bancară. Tranzacţi i le pot f i depuneri sau extrageri de sume în/dintr-un cont de card. Suma rămasă în cont se actualizează după f iecare tranzacţie.

4. Servicii hoteliereClientul care doreşte să rezerveze o cameră trebuie sa-şi precizeze identi tatea,

numărul de zi le pentru care face rezervarea, numărul de persoane şi , eventual, anumite caracterist ici ale camerei, cum ar f i: etajul , vederea, numărul de locuri ; un client poate face mai multe rezervări în t impul anului; o cameră este ocupată, din momentul rezervării unui cl ient; f iecare cameră este identi f icată printr-un număr şi o categorie; f iecărei categorii de camere î i corespunde un anumit preţ .

5. Admiterea studenţilor6. Farmaceutic7. Firmă cosmetică8. Fabrica de bomboane Bucuria9. Fabrica de confecţi i Ionel10. Combinatul de vinuri Cricova11. Fabrica de încălţăminte Zorile12. Fabrica de băuturi Vitanta13. Fabrica de covoare Floare14. Combinatul de pâine Franzeluţa15. Combinatul de lactate Incomlac16. Fabrica de conserve Olăneşti

10