PROIECTAREA BAZELOR DE DATE RELAŢIONALE

3. PROIECTAREA BAZELOR DE DATE RELAŢIONALE

Exista mai multe metode de proiectare a BD relationale, dar nici-una nu s-a impus cu desavârsire pâna în prezent. Fiecare dintre ele însa se bazeaza pe consideratii practice referitoare la sistemele modelate, consideratii care stau la baza activitatilor de procesare a datelor si anume: protocoale de functionare a sistemului, modul cum sunt folosite datele, modul de gestionare a acestora etc.

Proiectarea BD cuprinde 3 etape principale:

- Realizarea schemei conceptuale a BD

- Realizarea proiectului logic al BD (schemei logice a BD)

- Realizarea proiectului fizic al BD (schemei fizice a BD)

3.1. Realizarea schemei conceptuale a BD (modelul entitate - legatura)

În prima faza, o echipa nominalizata colecteaza (achizitioneaza) datele corespunzatoare din sistem, apoi urmeaza faza de organizare a acestora utilizându-se modelul entitate-legatura. Principalele concepte folosite în acest model sunt: entitatea, relatia (legatura) si atributul.

Pentru exemplificarea lor vom considera, în cele ce urmeaza, un sistem care reprezinta o universitate compusa din mai multe facultati. La fiecare facultate studiaza mai multi studenti si predau cursurile mai multe cadre didactice (le vom numi profesori). Fiecare student, conform programei scolare, urmeaza mai multe cursuri. Un profesor poate preda mai multe cursuri, dar si un curs poate fi predat de mai multi profesori (fie pe module, fie în totalitate, dar la serii diferite).

3.1.1. Entitatea

Entitatea este un obiect de interes din sistem pentru care trebuie sa existe date înregistrate. De ex., în sistemul descris mai sus obiectele de interes (entitati) sunt: facultate, profesor, student si curs care sunt legate între ele conform figurii de mai jos

  lucreaza_în

FACULTATE PROFESOR

studiaza_în   preda

urmeaza

STUDENT   CURS

Fig. 3.1. Exemplu de sistem de baze de date

Observatii:

Fiecare entitate are o denumire unica în cadrul unui sistem.

Entitatile sunt reprezentate prin substantive, dar nu orice substantiv folosit în descrierea sistemului este entitate, ci numai acelea care au o semnificatie deosebita.

Fiecare entitate trebuie sa fie bine definita si precizata pentru a se evita confuziile.

3.1.2. Relatia (legatura)

Relatia (legatura) este o asociere (raport) nedirectionata între 2 entitati. În exemplul de mai sus relatiile sunt:

- "lucreaza_în" - între entitatile FACULTATE si PROFESOR,

- "studiaza_în" între FACULTATE si STUDENT,

- "preda" între PROFESOR si CURS,

- "urmeaza" între STUDENT si CURS.

Observatii:

- Relatiile sunt reprezentate prin verbe, dar nu orice verb utilizat în descrierea sistemului este relatie.

- Între 2 entitati pot exista mai multe relatii. Ex.: "lucreaza_în" si "conduce" - între entitatile FACULTATE si PROFESOR.

- Pot exista în cadrul unei scheme conceptuale mai multe relatii cu acelasi nume, dar cele care leaga aceleasi entitati trebuie sa aiba nume diferite.

Cardinalitatea unei relatii indica numarul de instante din fiecare entitate care poate participa la relatie. Exista 3 tipuri de cardinalitate:

- "multi-la-unu" (many-to-one, M:1).

Relatia dintre entitatile A si B este de tipul "multi-la-unu" daca fiecarei instante din A i se poate asocia cel mult o singura instanta din B si fiecarei instante din B i se pot asocia mai multe instante din A.

Ex.: "studiaza-la"

M studiaza_la 1

STUDENT FACULTATE

Fig. 3.2. Exemplu de relatie "multi-la-unu" (M:1)

- "unu-la-unu" (one-to-one, 1:1).

Relatia dintre entitatile A si B este de tipul "unu-la-unu" daca fiecarei instante din A i se poate asocia cel mult o singura instanta din B si fiecarei instante din B i se poate asocia cel mult o singura instanta din A.

Ex.: "conduce"

  1 conduce 1

PROFESOR FACULTATE

Fig. 3.3. Exemplu de relatie "unu-la-unu" (1:1)

- "multi-la-multi" (many-to-many, M:M).

Relatia dintre entitatile A si B este de tipul "multi-la-unu" daca fiecarei instante din A i se pot asocia mai multe instante din B si fiecarei instante din B i se pot asocia mai multe instante din A.

Ex.: "preda", "urmeaza".

M preda M

  PROFESOR CURS

Fig. 3.4. Exemplu de relatie "multi-la-multi" (M:M)

Valorile prezentate pâna acum (M:1, 1:1, M:M) reprezinta cardinalitatea maxima a unei relatii. Pe de alta parte, o relatie este caracterizata si de o cardinalitate minima ce indica obligativitatea participarii entitatilor la relatie. Cardinalitatea minima a unei relatii poate avea valorile: 0:0, 0:1, 1:1. Daca avem cardinalitatea minima a unei relatii egala cu 1 înseamna ca exista o participare totala a entitatii la relatie (participare obligatorie). Daca avem cardinalitatea minima egala cu 0 înseamna ca exista o participare partiala a entitatii la relatie. În cadrul reprezentarii grafice cardinalitatea maxima a unei relatii se va afisa fara paranteze, în timp ce cardinalitaatea minima, se va scrie scrie între paranteze. Uneori cardinalitatea minima nu este reprezentata în diagrama, dar cea maxima este obligatorie.

  M(0) studiaza_la 1

  STUDENT   FACULTATE

  1(0) conduce 1(0)

  PROFESOR FACULTATE

  M(0) preda M(0)  

  PROFESOR   CURS

Fig. 3.5. Exemple de reprezentare a cardinalitatii maxime si minime

Alt mod de reprezentare a relatiilor, indicând numai cardinalitatea maxima este:

many_to_one

  studiaza_la

  STUDENT   FACULTATE

one_to_one

  conduce

  PROFESOR   FACULTATE

many_to_many

  preda

  PROFESOR   CURS

Fig. 3.6. Alta varianta de reprezentare a relatiilor, indicând numai cardinalitatea maxima

3.1.3. Atributul

Atributul este o caracteristica a unei entitati sau a unei relatii. Fiecare entitate are un anumit numar de atribute despre care sunt înregistrate date. Ex.: nume, prenume, data. Fiecare atribut poate lua valori care furnizeaza informatii despre entitatea respectiva. Ex.: Ionescu, Aurel, 13.10.84. si relatiile pot avea atribute. Ex.: "lucreaza_în" data_angajarii.

Observatii:

Numele unui atribut este unic în cadrul unei entitati sau al unei relatii.

Atributele sunt întotdeauna substantive, dar nu orice substantiv este atribut.

Pentru fiecare atribut este necesara o descriere, împreuna cu domeniul de valori (întreg, sir de caractere, data calendaristica etc.).

Trebuie evitate atributele indirecte. Ex.: numele_facultatii este un atribut indirect pentru tabelul STUDENT si un atribut direct pentru tabelul FACULTATE.

3.1.4. Chei primare, naturale, artificiale

Cheia unei entitati este un atribut sau set de atribute care identifica în mod unic o instanta a acelei entitati (face distinctie între oricare 2 rânduri diferite ale tabelului asociat entitatii). Cheile sunt de 2 feluri: naturale (au semnificatie reala pentru entitate, ex.: (nume, prenume, data_nasterii)) si artificiale (nu au semnificatie reala pentru entitate, ex.: cod_student, cod_facultate).

Avantajele cheilor artificiale primare:

stabilitatea - Cheile primare artificiale se schimba rar. Schimbarea unei chei primare presupune schimbarea cheilor straine care fac referire la ea.

simplitatea - Au un numar de atribute si de caractere mai mic.

Nu prezinta ambiguitati în reprezentare (ex.: liniute, spatii etc.).

Elimina aparitia valorilor Null.

3.1.5. Cazuri speciale de entitati, relatii, atribute

a)      Subentitate / Superentitate

Subentitatea este o submultime a unei entitati numita superentitatea (ex.: superenti-tatea PERSONAL subentitatile PROFESOR, PERSONAL_ADMINISTRATIV).

Cheia primara, atributele si relatiile unei superentitati sunt valabile pentru orice subentitate, reciproca fiind falsa.

Între o subentitate si superentitatea corespunzatoare exista totdeauna o relatie 1:1 cu cardinalitatea minima 1:0.

Ex.:

PERSONAL

cod_personal

nume

prenume

data_nasterii

Fig. 3.7. Exemplu de subentitate si superentitate

b)      Entitate dependenta (detaliu) / entitate master

O entitae dependenta (detaliu) este o entitate care nu poate exista de sine statatoare, ci numai atasata unei alte entitati, numita entitate master, din cadrul legaturii respective.

- Ex.:

  MODUL   CURS

  cod_curs M(0) face_parte_din 1 cod _curs

  nr_modul   nume_curs

  descriere     descriere

Fig. 3.8. Exemplu de entitate dependenta (detaliu) si entitate master

Între entitatile master si detaliu va exista totdeauna o relatie 1:M având cardinalitatea minima 1:0.

Cheia primara a unei entitati detaliu va fi formata din cheia primara a entitatii master la care se adauga atribute proprii.

c)      Relatii recursive

Relatia recursiva este o relatie între o entitate si ea însasi.

Ex.:

  M(0)

  PROFESOR   este_sef_direct

  1(0)

Fig. 3.9. Exemplu de relatie recursiva

d)      Relatii binare (de tip 2) / Relatii între mai mult de 2 entitati (de tip 3)

Obs.: O relatie de tip 3 nu poate fi sparta în relatii binare între entitatile dependente deoarece se obtin informatii eronate, relatia initiala nemaiputând fi reconstituita din relatiile componente.

Ex.:   STUDENT

  M(0)

  coordoneaza

  PROFESOR

  M(0)

  efectueaza

  M(0)

  PROIECT

Fig. 3.10. Exemplu de relatie între mai mult de 2 entitati (de tip 3)

Fig. 3.11. Relatia de tip 3 initiala

Fig. 3.12. Descompunerea prin proiectie a relatiei de tip 3 în relatii de tip 2

Fig. 3.13. Reconstituirea eronata a relatiei initiale

e)      Atribute simple / compuse / repetitive (multivaloare) / calculate (deduse)

Unui atribut simplu îi corespunde o singura valoare atomica. Ex.: nume_facultate.

Un atribut compus este format din mai multe atribute simple numite componentele sale. Ex.: adresa (oras, strada, numar).

Un atribut repetitiv (multivaloare) poate avea mai multe valori, numarul lor variind de la o instanta la alta. Ex.: nr_telefon (051-435455, 095-770324).

Un atribut calculat (dedus) are valoarea cunoscuta indirect, pe baza calculului cu ajutorul valorilor altor atribute. Ex.: salariul_net = 0,75*salariul_brut.

Obs.: Atributele deduse reprezinta o redundanta a datelor.

3.1.6. Diagrama entitate - legatura

Ex.:

  PROFESOR

FACULTATE 1 lucreaza_în M(0) cod_profesor

cod_facultate   nume

nume_facultate     prenume

adresa 1(0) conduce 1(0) data_nasterii

  grad_didactic

  1   M(0)

studiaza_în   preda

M(0)   M(0)

STUDENT

cod_student M(0) urmeaza M(0) CURS

nume   cod_curs

prenume   nume_curs

data_nasterii     descriere

Fig. 3.14. Exemplu de diagrama entitate - legatura

În diagrama entitate - legatura entitatile sunt reprezentate prin dreptunghiuri, iar relatiile dintre ele prin arce neorientate specificându-se si cardinalitatea acestora. Pentru fiecare entitate se specifica cheile primare (subliniate) si eventual atributele mai importane (semnificative).

3.1.7. Probleme în identificarea entitatilor, relatiilor si atributelor

a)      Relatie - entitate

Uneori este greu de identificat daca o componenta a sistemului este relatie sau entitate. Daca o entitate are o cheie provenita din combinatia cheilor primare a 2 sau mai multe entitati, atunci poate fi definita o relatie.

Ex.:

PROFESOR   PREDARE   CURS

cod_profesor   cod_profesor   cod_curs

  cod_curs

PROFESOR M   preda   M CURS

cod_profesor   cod_curs

Fig. 3.15. Relatie - entitate

b)      Relatie - atribut

O relatie poate fi reprezentata ca un atribut al unei entitati, iar atributele unei entitati pot fi înlocuite cu relatii.

Atunci când un atribut al unei entitati reprezinta cheia primara a altei entitati (e cheie straina), el reprezinta, de fapt, o relatie.

Ex.:

STUDENT   FACULTATE

cod_student   cod_facultate

cod_facultate

  exprima o relatie

STUDENT M studiaza_la   1 FACULTATE

  cod_student     cod_facultate

Fig. 3.16. Relatie - atribut

3.1.8. Algoritm pentru obtinerea modelului entitate - legatura

Etapele pentru realizarea modelului (diagramei) entitate - legatura sunt:

Sunt identificate entitatile sistemului.

Sunt identificate relatiile sistemului si este stabilita cardinalitatea acestora.

Sunt identificate atributele entitatilor si atributele relatiilor sistemului.

Sunt stabilite cheile primare ale entitatilor.

Se traseaza diagrama entitate - legatura a sistemului considerat.

Diagrama entitate - legatura a sistemului (PROFESOR, STUDENT, FACULTATE, CURS) discutat mai înainte este prezentat în Fig. 3.17.

  PERSONAL

  cod_personal

  nume, prenume, data_nasterii

  PERSONAL_ADMINISTRATIV

 

FACULTATE 1   lucreaza_in M(0)   M(0)  

cod_facultate   este_sef

nume_facultate 1(0) conduce   1(0)   PROFESOR

adresa   grad_didactic

  M(0)

    M(0)

studiaza_la   coordoneaza

M(0)   preda

STUDENT   PROIECT

cod_student M(0)   M(0) cod_proiect

nume efectueaza nume_proiect

prenume

data_nasterii

  M(0)

M(0)

    CURS

  urmeaza M(0) cod_curs

  nume_curs

  descriere

  1

  face_parte_din

 

      M(0)

   

  MODUL

Fig. 3.17.   cod_curs

Diagrama entitate-legatura a sistemului   nr_modul

(PROFESOR, STUDENT, FACULTATE, CURS) descriere

3.2. Realizarea schemei (proiectului, diagramei) logice a unei baze de date

Pentru realizarea schemei logice a unei baze de date se porneste de la scheme conceptuala (modelul entitate - legatura) urmarindu-se conversia entitatilor si a legaturilor în tabele relationale.

Regulile de conversie ale entitatilor, legaturilor si atributelor sunt urmatoarele:

3.2.1. Transformarea entitatilor

Regula generala: entitatile se transforma în tabele.

Subcazuri:

a)      Entitatile independente devin tabele independente, adica tabele a caror cheie primara nu contine chei straine.

Ex.: Entitatea "STUDENT" devine tabelul "STUDENT" cu cheia primara cod_student.

b)      Entitatile dependente devin tabele dependente (tabele detaliu) adica tabele a caror cheie primara contine cheia straina ce face referinta la cheia primara a entitatii de care depinde entitatea in cauza.

Ex.: Entitatea "MODUL" devine tabelul "MODUL" a carui cheie primara este formata din cod_curs (care este o cheie straina pentru entitatea "CURS") si nr_modul.

c)      Subentitatile devin subtabele, adica tabele a caror cheie primara este cheia straina pentru tabelul superentitate.

Ex.: Subentitatea "PROFESOR" va avea cheia primara cod_personal, aceeasi cu cea a superentitatii "PERSONAL".

Avantajele supertabelelor: simplificarea programelor de manipulare a datelor.

Dezavantajele supertabelelor: probleme de integritate, apar valori de Null.

Avantajele subtabelelor: mai stabile, mai flexibile, ocupa spatiu mai mic, contin mai putine valori de Null.

Dezavantajele subtabelelor: se ingreuneaza manipularea datelor.

3.2.2. Transformarea relatiilor (legaturilor)

Regula generala: Relatiile (legaturile) se convertesc in chei straine.

Conventie: plasamentul cheii straine este simbolizat printr-o sageata. Atunci cand cheia straina este inclusa in cheia primara, varful sagetii este plin (   ) si este gol in caz contrar ( ).

Cazuri:

a)      Relatiile 1:1 devin chei straine. Cheia straina este plasata in tabelul cu linii mai putine.

Ex.:

a₁) SOT 1 este_casatorit 1 SOTIE - cheia straina poate fi plasata oriunde

a₂) SOT 1 este_casatorit 1(0) SOTIE

a₂) SOT 1(0) este_casatorit 1(0) SOTIE - cheia straina va fi plasata in tabelul cu

  linii mai putine

b)      Relatiile M:1 devin chei straine plasate in tabelul care se afla in partea de "multi" a relatiei.

Ex.:

PROFESOR M(0) lucreaza_in 1 FACULTATE

Cazuri:

b₁)   M(0)   1

Cheia straina nu poate avea valoarea Null, iar in cazul entitatilor dependente ea va face parte chiar din cheia primara a tabelului detaliu.

b₂)   M(0)   1(0)   Fig. 3.18

Cheia straina poate avea valoarea Null si nu poate face parte din cheia primara.

c)      O relatie M:M se transforma in 2 relatii M:1. In acest caz se construieste un tabel special numit tabel asociativ care are 2 chei straine care fac referinta la cheile primare ale celor 2 tabele aflate in relatia M:M. Cheia sa primara este formata din cele 2 chei straine plus (eventual) alte atribute suplimentare.

Ex.:

PROFESOR M preda M CURS

cod_profesor   cod_curs

PROFESOR 1 M(0) PREDARE M(0) 1 CURS

cod_profesor   cod_profesor   cod_curs

  cod_curs

Fig. 3.19

d)      O relatie de tip 3 se transforma intr-un numar de relatii de tip 2, egal cu numarul de tabele asociate. Aceste relatii (legaturi) se stabilesc intre un tabel asociativ si tabelele asociate. Tabelul asociativ are cate o cheie straina pentru fiecare tabel asociat, iar cheia sa primara este formata din toate aceste chei straine plus (eventual) alte atribute suplimentare.

Ex.:

  STUDENT

  M(0)

  coordoneaza

  PROFESOR

  M(0)

  efectueaza

  M(0)

  PROIECT

Fig. 3.20 Relatia de tip 3 initiala

    PROFESOR

  cod_personal

  1

  M(0)

    EFECTUARE_

  COORDONARE

STUDENT 1   M(0) cod_student M(0) 1 PROIECT

cod_student   cod_proiect   cod_proiect

  cod_personal

Fig. 3.21. Tabelele si legaturile rezultate

3.2.3. Transformarea atributelor

Regula generala: Atributele se convertesc in coloane ale tabelelor provenite din entitati sau chiar in tabele.

Cazuri:

a)      Atributele simple ale unei entitati devin coloane in tabelul provenit din acea entitate.

b)      Toate componentele unui atribut compus devin coloane.

Ex.: adresa tara, oras, str,. nr., bl., sc., et., ap.

c)      Atributele repetitive (multivaloare) ale unei entitati devin tabele dependente ce contin fiecare o cheie straina (care face referinta la cheia primara a entitatii) si atributul multivaloare. Cheia primara a unui astfel de nou tabel este formata din cheia straina plus alte coloane suplimentare.

Ex.:

  STUDENT 1   M(0) TELEFON

  cod_student   cod_student Fig. 3.22.

  nr_telefon

d)      - Atributele simple ale unei relatii 1:1 sau M:1 devin coloane ale tabelului care contine cheia straina.

Ex.: atributul data_inscrierii al relatiei studiaza_la devine coloana in tabelul STUDENT.

  STUDENT M(0) studiaza_la 1 FACULTATE

  Fig. 3.23.

  (data_inscrierii)

- Fiecare componenta a unui atribut compus al unei relatii 1:1 sau M:1 se va converti in mai multe coloane in tabelul care contine cheia straina.

e)      - Atributele simple ale unei relatii M:M vor deveni coloane ale tabelului asociativ.

Ex.:   STUDENT M(0) urmeaza M(0) CURS

    Fig. 3.24.

  (nota_examen)

Atributul "nota_examen" al relatiei "urmeaza" devine coloana in tabelul asociat URMARE.

Fiecare componenta a unui atribut compus al unei relatii M:M va deveni o coloana in tabelul asociativ creat.

f)        - Atributele repetitive (multivaloare) ale unei relatii 1:1 sau 1:M devin tabele dependente de tabelul care contine cheia straina.

- Atributele repetitive ale unei relatii M:M devin tabele dependente de tabelul asociativ corespunzator relatiei. Cheia primara a acestor tabele dependente va fi formata din cheia straina respectiva plus una sau mai multe coloane suplimentare.

Ex.:

  STUDENT M(0) urmeaza M(0) CURS

Fig. 3.25.

  (nota_test)

Atributul "test" este un atribut repetitiv. Va rezulta urmatoarea structura:

STUDENT 1   M(0) URMARE M(0) 1 CURS

cod_student   cod_student   cod_curs

  cod_curs

 

  1

  M(0)

  TEST

    cod_student

  cod_curs

  nr_test

Fig. 3.26. Transformarea atributelor repetitive ale unei relatii M:M

3.2.4. Diagrama logica a BD si tabelele asociate

In Fig. 3.27. este prezentata diagrama logica a bazei de date pentru sistemul UNIVERSI-TATE (PROFESOR, STUDENT, FACULTATE, CURS) care a rezultat in urma transfor-marilor prezentate mai inainte si efectuate asupra diagramei entitate-legatura (Fig. 3.17). Tabele asociate acestei diagrame sunt urmatoarele:

PERSONAL(cod_personal, nume, prenume, data_nastere, sex, stare_civila,data_angajare)

PERSONAL_ADMINISTRATIV (cod_personal, profesie, functie)

PROFESOR (cod_personal, grad_didactic, titlu, sef, ore_predate, vechime, cod_facultate)

CURS (cod_curs, nume_curs, descriere, nr_ore)

PREDARE (cod_personal, cod_curs, data_inceput)

MODUL (cod_curs, nr_modul, descriere)

FACULTATE(cod_facultate, nume_facultate, localitate, strada,nr,cod_postal, cod_decan)

STUDENT (cod_student, nume, prenume, data_nasterii, tara, localitatea, strada, nr,

  cod_postal, studii_anterioare, data_inscriere, cod_facultate)

TELEFON (cod_student, nr_telefon, tip_telefon)

PROIECT (cod_proiect, nume_proiect, domeniu)

EFECTUARE_COORDONARE (cod_student, cod_proiect, cod_personal)

URMARE (cod_student, cod_curs, nota_examen, nota_restanta, observatii)

TEST (cod_student, cod_curs, nr_test, nota_test, data_test, observatii)

  PERSONAL

  cod_personal

  nume, prenume, data_nasterii

  PERSONAL_ADMINISTRATIV

  cod_personal

 

FACULTATE 1   lucreaza_in   M(0) M(0)  

cod_facultate     PROFESOR

nume_facultate 1(0) conduce   1(0) cod_personal

adresa   grad_didactic

  1

1   1

M(0)   M(0)   M(0)

STUDENT EFECTUARE_   PROIECT PREDARE

cod_student 1 M(0) COORDONARE M(0) 1 cod_proiect cod_personal

nume cod_student   nume_proiect cod_curs

prenume cod_proiect

data_nasterii   cod_personal   M(0)

    1

1 1

  URMARE   CURS

M(0) cod_student M(0) 1 cod_curs

  cod_curs   nume_curs

    descriere

  1

  1

M(0)   M(0)   M(0)

     

TELEFON   TEST   MODUL

cod_student   cod_student   cod_curs

nr_telefon   cod_curs   nr_modul

  nr_test   descriere

Fig. 3.27. Diagrama logica a BD