Lucrarea de laborator

Proiectarea Bazelor de Date Relationale

Proiectarea bazelor de date relationale porneste de la modelul Entitate-Asociere, reprezentat printr-o diagrama Entitate-Asociere si stabileste relatiile si asocierile dintre acestea, prin intermediul referentierilor folosind chei straine. Proiectul conceptual al unei baze de date este independent de SGBD-ul care va fi folosit pentru proiectarea logica si implementarea acesteia.

1.     Entitati si Asocieri.

Modelul Entitate-Asociere este un model semantic, bazat pe semnificatia care se atribuie diferitelor obiecte, concepte, etc din lumea reala. O entitate este 'orice poate fi identificat in mod distinctiv'.

In proiectarea bazelor de date se considera doua categorii de entitati:

Entitati obisnuite (puternice, normale, regular entities)

Entitati slabe (dependente, weak entities).

Entitatile normale au o existenta proprie in cadrul modelului, in timp ce entitatile slabe nu pot exista decat daca exista o entitate normala (puternica) cu care sunt asociate. De exemplu, intr-o baza de date a unei intreprinderi, entitatea ANGAJAT este o entitate puternica, deoarece ea exista in mod mod normal in modelul activitatii intreprinderii, in timp ce o entitate DEPENDENT este o entitate slaba: nu se va inregistra o astfel de entitate decat daca exista parintele angajat in acea institutie.

In modelarea bazelor de date, entitatile de acelasi tip sunt grupate in multimi de entitati, fiecare multime de entitati (si fiecare entitate in parte) fiind caracterizata printr-o multime de atribute.

In ceea ce priveste tipurile entitatilor, in modelul relational se pot defini subtipuri, care reprezinta o specializare a unui tip de entitati. Se pot defini ierarhii pe mai multe nivele de subtipuri de entitati. Este evidenta analogia dintre subtipuri si clasele derivate din modelul orientat pe obiecte, dar in modelul relational subtipurile se proiecteaza intr-un mod diferit de proiectarea obiect orientata, dupa cum vom vedea in cele ce urmeaza.

2.     Definirea diagramei Entitate-Asociere

Diagrama Entitate-Asociere, construita pentru a modela activitatea dintr-un domeniu dat, reprezinta multimile de entitati si asocierile dintre acestea. Ea constitue un proiect abstract al bazei de date, care nu poate fi transformat direct intr-o baza de date relationala, deoarece este imprecisa si nu evidentiaza clar modul in care se pot crea relatiile si asocierile intre acestea. De aceea, pornind de la o diagrama Entitate-Asociere se parcurge o etapa de proiectare conceptuala, in care se definesc relatiile (tabelele) si asocierile dintre acestea in termeni relationali.

Vom exemplifica proiectarea conceptuala a unei baze de date pentru urmarirea activitatii unei intreprinderi incepand cu definirea diagramei Entitate-Asociere. Pentru aceasta se parcurg mai multe etape

a)     Definirea multimilor de entitati puternice (normale)

Multimile de entitati normale dintr-o baza de date trebuie sa defineasca categoriile (persoane, organizatii, obiecte, etc) prin care se poate descrie activitatea modelata. De exemplu, intr-o intreprindere, activitatea este organizata in SECTII, in care lucreaza ANGAJATI, care produc diferite PRODUSE prin asamblarea mai multor COMPONENTE; componentele sunt achizitionate (cumparate) de la mai multi FURNIZORI, iar produsele rezultate sunt vandute la CLIENTI. Pentru fiecare multime de entitati se identifica atributele necesare descrierii activitatii respective. Pentru exemplul ales se stabilesc urmatoarele multimi de entitati puternice si atributele lor:

SECTII (Nume, Buget)

ANGAJATI (Nume, Prenume, DataNastere, Adresa, DataAngajarii, Salariu)

PRODUSE (Nume, Descriere)

COMPONENTE (Nume, Descriere)

FURNIZORI (Nume, Adresa, Oras)

CLIENTI (Nume, Adresa, Oras)

b)     Definirea multimilor de entitati slabe.

Intr-o baza de date pot sa existe si multimi de entitati slabe, care nu au o existenta proprie, ci exita numai daca sunt asociate cu multimi de entitati puternice. De exemplu, pentru stocarea datelor despre persoanele dependente de un angajat (fii, sotii, soti, etc) se defineste o multime de entitati DEPENDENTI. Daca se presupune ca un ANGAJAT nu are doua persoane dependente cu acelasi prenume (o persoana care are doi fii cu acelasi prenume este o situatie cam improbabila , atunci multimea de entitati DEPENDENTI este:

DEPENDENTI (Prenume, Relatia

O multime de entitati slabe e afla intodeauna intr-o asociere N:1 cu multimea de entitati puternice de care depinde, deci intre multimea DEPENDENTI si multimea de entitati ANGAJATI exista asocierea N:1

c) Definirea subtipurilor.

Pentru unele tipuri de entitati se pot crea subtipuri care contin atribute suplimentare necesare pentru a defini o anumita specializare a tipului respectiv. De exemplu, din tipul ANGAJATI se creaza subtipul PROGRAMATORI pentru care este necesar atributul Limbaj, pentru a preciza ce limbaj de programare cunoaste cel mai bine programatorul respectiv. Acest atribut nu este necesar pentru toti ceilalti angajati (daca nu unrt programatori, atunci nu trebuie sa stie nici-un limbaj de programare) si de aceea s-a creat un subtip (PROGRAMATORI) pentru specializarea tipului ANGAJATI:

ANALIZA: Filtre numerice iir - lucrarea de laborator ANALIZA: Recursivitate - proiect de tehnologie didactica

PROGRAMATORI (Nume, Prenume, DataNastere, Adresa, DataAngajarii, Salariu,Limbaj)

O multime de entitati de un subtip dat este intotdeauna in asociere 1:1 cu multimea de entitati de supertipul acesteia. Pentru exemplul de mai sus, un angajat este un (singur) programator; iar un programator este chiar un angajat, deci asocierea intre multimile de entitati ANGAJATI si PROGRAMATORI exista asocierea de tip 1:1.

d) Definirea asocierilor

Asocierile dintre multimile de entitati se deduc din modul in care se desfasoara activitatea modelata. De exemplu, daca in intreprinderea respectiva activitatea este organizata pe sectii si fiecare angajat lucreaza intr-una (si numai una) dintre sectii, atunci intre multimile de entitati SECTII-ANGAJATI exista asocierea 1:N

Un produs este compus din mai multe COMPONENTE si fiecare componenta poate fi inclusa in mai multe PRODUSE, deci asocierea COMPONENTE-PRODUSE este este M:N

O componenta poate fi achizitionata de la mai multi FURNIZORI, iar un furnizor poate oferi mai multe COMPONENTE, deci asocierea FURNIZORI-COMPONENTE este M:N. Orice asociere poate avea diferite atribute care o caracterizeaza. De exemplu, asocierea FURNIZORI-COMPONENTE (care poate fi denumita ACHIZITII) poate sa contina o referinta la angajatul care se ocupa de o anumita achizitie. Se poate considera ca multimile de entitati FURNIZORI-COMPONENTE-ANGAJATI se afla toate intr-o asociere de ordinul 3, M:N:P.

Un produs poate fi vandut mai multor CLIENTI si fiecare client poate cumpara mai multe PRODUSE, deci asocierea dintre multimile de entitati PRODUSE-CLIENTI este M:N. La fel ca in cazul precedent, aceasta asociere (pe care o putem denumi VANZARI) poate sa contina o referinta la angajatul care se ocupa de vanzarea respectiva, si se poate considera ca multimile de entitati PRODUSE-CLIENTI-ANGAJATI se afla toate intr-o asociere de ordinul 3, M:N:P.

Multimile de entitati si asocierile dintre acestea sunt reprezentate in diagrama Entitate-Asociere de mai jos.

Diagrama Entitate-Asociere este reprezentarea unui model conceptual al bazei de date si nu presupune nici un mod specific de gestiune a datelor. O astfel de diagrama poate sa fie folosita pentru proiectarea unei baze de date in orice model de date ( ierarhic, retea, relational sau orientat pe obiecte). Transpunerea componentelor diagramei Entitate-Asociere intr-un model specific de gestiune a datelor permite dezvoltarea unui model conceptual al bazei de date. In aceasta lucrare se va studia trecerea de la diagrama Entitate-Asociere la modelul relational.

3.     Trecerea de la diagrama E-A la modelul relational

Proiectul conceptual al unei baze de date relationale contine relatiile (date prin schemele de relatii) si asocierile intre acestea si poate fi dezvoltat pe baza diagramei Entitate-Asociere. Proiectul conceptual este independent de SGBD-ul care va fi folosit pentru implementarea modelului, deoarece componentele principale ale unei baze de date relationale (relatiile si asocierile) au aceeasi forma pentru marea majoritate a SGBD-urilor folosite in mod curent (Oracle, SQL Server, Informix, Access, etc). Pot sa difere, de la un SGBD la altul, unele din componentele auxiliare folosite pentru programarea aplicatiilor (cum sunt procedurile stocate, triggere, etc).

Proiectul conceptual al unei baze de date relationale poate fi dezvoltat independent de un SGBD anume, ca un proiect pe hartie, conceput si definit de analisti in domeniul bazelor de date. De obicei insa, fiecare SGBD pune la dispozitie un numar de instrumente mai mult sau mai putin 'prietenoase' de proiectare a relatiilor, astfel incat in mod frecvent, chiar proiectul conceptual se dezvolta in cadrul unui SGBD. De exemplu, Access ofera o interfata vizuala de proiectare a relatiilor si a asocierilor (asa cum s-a vazut in lucrarea 2) deosebit de usor de utilizat. La fel, SQL Server permite proiectarea vizuala a relatiilor, iar Oracle Developer 2000 adauga facilitati grafice de proiectare pentru SGBD Oracle. Exista si posibilitatea conversiei intre tabele definite in diferite SGBD-uri (prin export-import), dar aceste conversii nu asigura toate echivalentele de la un SGBD la altul. De aceea, cel mai convenabil este ca sa se utilizeze instrumentele de proiectare a relatiilor ale acelui SGBD pentru care se intentioneaza sa fie dezvoltata aplicatia de baze de date.

In continuare vom exemplifica proiectarea relatiilor in Access si in SQL Server, dat fiind ca aceste SGBD-uri ofera o interfata grafica pentru crearea reletiilor si reprezentarea diagramei Entitate-Asociere. In Access relatiile si asocierile intre ele se pot vedea in diagrama de asocieri (Relationships), care este corespondentul relational al diagramei Entitate-Asociere. In aceasta diagrama cheile primare sunt reprezentate ingrosat (bold), iar asocierea 1:N este reprezentata printr-o linie care uneste cele doua relatii asociate, marcata cu 1 (la capatul dinspre relatia referentiata, care contine cheia primara) si cu (la capatul dinspre relatia care referentiaza si deci contine cheia straina). In SQL Server, reprezentarea diagramei E-A este asemanatoare, doar ca pentru asociere se reprezinta un simbol cheie la capatul liniei spre relatia referentiata.

3.1  Proiectarea relatiilor

a) Multimile de entitati puternice (normale) din diagrama E-A devin relatii, cu atributele date de atributele entitatilor. Numele fiecarei relatii trebuie sa fie unic intr-o baza de date. Numele unui atribut trebuie sa fie unic in cadrul unei reatii, dar pot exista atribute cu acelasi nume in relatii diferite, care sunt complet diferite in cadrul bazei de date, posibil diferite si din punct de vedere semantic. De exemplu atributul Nume (al unui angajat) este complet diferit de atributul Nume al unei sectii.

La astfel de relatii, cheia primara se alege fie ca o combinatie de atribute care defineste in mod unic un tuplu al relatiei, fie printr-un atribut special adaugat pentru identificare unica a tuplelor. De exemplu, pentru relatia ANGAJATI, cheia primara poate fi atributul compus (Nume, Prenume, DataNasterii, Adresa) care identifica unic o persoana, dar o cheie primara definita pe o multime de atribute provoaca ineficienta oricarei operatii asupra relatiei (inserare, actualizare, interogare). Aceatsa ineficienta se datoreaza faptului ca la orice operatie trebuie sa fie comparate valorile atributelor din cheia primara a tuplelor. La introducerea unui nou tuplu in relatia ANGAJATI ar trebui sa fie comparate valorile pentru Nume, Prenume si DataNasterii ale tuplului care se introduce cu valorile aceloras atribute ale tuturor tuplelor existente, pentru a nu introduce (din greseala) tupluri duplicat. Practica curenta pentru definirea cheii primare a unei relatii corespunzatoare unei multimi de entitati puternice este de a se adauga un atribut suplimentar (in general de tip numeric), care poate fi intretinut astfel incat sa aiba valori unice in relatie (tabela). Majoritatea SGBD-urilor ofera functia de intretinere a unicitatii cheii primare definita ca un numar (autonumber). De exemplu, pentru relatia ANGAJATI cheia primara este un atribut special adaugat pentru identificare (IdAngajat). La fel se procedeaza si pentru celelalte relatii care corespund multimilor de entitati puternice (SECTII, COMPONENTE, PRODUSE, FURNIZORI, CLIENTI), adica se adauga cate un atribut de identificare unica a tuplelor (respectiv IdSectie, IdComponenta, IdProdus, IdFurnizor, IdClient).

b) Multimile de entitati slabe din diagrama E-A devin relatii aflate in asociere N:1 cu relatia coresunzatoare multimii de entitati de care acestea depind, deci cheia primara a relatiei referentiate (puternice) este cheie straina in relatia dependenta. De exemplu, cheia primara IdAngajat a relatiei ANGAJATI este cheie straina (cu acelasi nume) in relatia DEPENDENTI. Cheia primara a relatiei dependente poate fi o combinatie formata din atributul cheie straina si alt atribut care asigura posibilitatea de identificare unica a unui tuplu, sau poate fi un atribut suplimentar de identificare. In exemplul prezentat in figura de mai jos, cheia primara a relatiei DEPENDENTI este compusa din atributul cheie straina (IdAngajat si atributul Prenume.

c) Multimile de entitati care sunt subtipuri ale unui tip dat devin relatii aflate in asociere 1:1 cu multimea de entitati de tipul respectiv. In relatia corespunzatoare subtipului dat se defineste o cheie straina care referentiaza cheia primara a relatiei care contine tipul (supertipul) corespunzator. Aceasta cheie straina este, in acelati timp si cheia primara a relatiei de subtip. De exemplu, relatia PROGRAMATORI are cheia primara IdAngajat, care este si cheie straina care referentiaza atributul cheie primara cu acelasi nume din relatia ANGAJATI.

Diagrama relatiilor si a asocierilor in Access

Diagrama relatiilor si a asocierilor in SQL Server

3.2  Proiectarea asocierilor

a) Asocierea N:1 dintre doua multimi de entitati din diagrama E-A se realizeaza in modelul relational prin intermediul unei chei straine in prima relatie (cea cu aritatea N a asocierii) care referentiaza cheia primara (sau o cheie candidata) din relatia referentiata (cea cu aritatatea 1 a asocierii). De exemplu, asocierea N:1 intre relatiile ANGAJATI-SECTII se realizeaza prin cheia straina IdSectie adaugata relatiei ANGAJATI, care referentiaza cheia primara cu acelas nume a relatiei SECTII.

Astfel de atribute (pentru definirea cheilor straine) nu exista in modelul E-A si ele trebuie sa fie adaugate in modelul relational pentru definirea ascierii N:1, asa cum in modelul ierarhic pentru o astfel de asociere se adauga link-uri intre noduri.

b) Asocierea binara M:N dintre doua multimi de entitati din diagrama E-A se realizeaza in modelul relational prin intermediul unei noi relatii, numita relatie de asociere. Aceasta noua relatie se afla in asociere M:1, respectiv N:1 cu fiecare din cele doua relatii date prin intermediul a doua chei straine care referentiaza cheile primare (sau chei candidate) din relatiile date.

De exemplu, asocierea M:N dintre relatiile COMPONENTE-PRODUSE se realizeaza prin intermediul relatiei COMPOZITII care contine cheile straine IdComponenta, IdProdus, corespunzatoare cheilor primare din relatiile COMPONENTE, respectiv PRODUSE.

Cheia primara a unei relatii de asociere poate fi compusa din cheile straine care referentiaza cele doua relatii asociate (de exemplu, cheia primara compusa din cheile straine IdComponenta, IdProdus din relatia COMPOZITII). O alta posibilitate de definire a cheii primare este de a adauga unui atribut de identificare (de exemplu, cheia primara IdVanzare din relatia de asociere VANZARI).

O relatie de asociere poate sa contina si alte atribute in afara cheilor straine, atribute care caracterizeaza asocierea dintre relatiile date. De exemplu, relatia COMPOZITII mai contine atributul Numar, care caracterizeaza asocierea: numarul de componente de un anumit tip continute intr-un produs.

c)   Asocierea multipla M:N:P dintre mai multe (n) multimi de entitati din diagrama E-A se realizeaza in mod asemanator cu asocierea binara, prin intermediul unei noi relatii care se afla in asociere M:1, N:1, P:1, etc, cu fiecare din relatiile date. Aceasta asociere este realizata prin intermediul a n chei straine, fiecare cheie straina referentiind cheia primara (sau o cheie unica) dintr-una din relatiile date. De exemplu, relatia ACHIZITII contine 3 chei straine (IdComponenta, IdFurnizor, IdAchizitor) care referentiaza relatiile COMPONENTE, FURNIZORI, ANGAJATI. Cheia primara a relatiei de asociere se poate compune din toate cheile straine sau se poate adauga un taribut de identificare (cum este cheia IdAchizitie in relatia ACHZITII). In exemplul prezentat, asocierea dintre multimile de entitati ANGAJATI-CLIENTI-PRODUSE este realizata prin relatia VANZARI.

3.3  Proiectarea indecsilor

In bazele de date relationale indecsii sunt structuri de date care permit ordonarea tuplurilor unei relatii. Desi, din punct de vedere teoretic, nu este necesar ca tuplurile sa fie ordonate, operatiile asupra relatiilor se executa mult mai eficient daca tuplurile sunt oronate. Ordonarea tuplurilor se realizeaza prin intermediul indecsilor.

Exista doua categorii de indecsi intr-o relatie: indexul primar si indecsi secundari.

Indexul primar se creaza pe atributul (sau atributele) cheii primare a relatiei si se implementeaza ca un arbore binar ordonat sau ca o tabela de dispersie (hash-table) si indica de fapt modul in care sunt memorate tuplurile relatiei. Intr-o relatie, definirea indexului primar este implicita, el se creaza la definirea cheii primare.

Un index secundar nu schimba modul de memorare a tuplurilor relatiei, ci creaza o noua structura (arbore binar ordonat sau hash-table), care contine linkuri catre tuplurile ordonate dupa indexul primar. Un index secundar poate sa fie creat pe o cheie candidata si atunci nu va contine tupluri duplicat, sau pe orice alt atribut, dar in aceasta situatie pot sa existe tupluri cu aceeasi valoare a indexului (tupluri duplicat). Indecsii (primar si secundari) se definesc la crearea tabelei, dar indecsii secundari pot fi adaugati sau stersi si ulterior.

Exercitii:

1.     Creati baza de date descrisa in aceasta lucrare, in Access sau SQL Server.

2.     Adaugati informatii de numere de telefon pentru angajati, astfel incat sa se poata trece un numar oarecare de numere de telefon al fiecarui angajatul (0, 1, 2, 3,.. numere de telefon). Indicatie: se va crea o relatie noua, TELEFOANE, care sa contina o cheie straina IdAngajat care referentiaza relatia ANGAJATI.

3.     Adaugati doua subtipuri noi pentru tipul ANGAJAT: subtipul VANZATOR si subtipul ACHIZITOR. Este necesara modificarea asocierilor pentru relatiile VANZARI si ACHIZITII ? Verificati si explicati rezultatul.