PROIECTAREA UNEI BAZE DE DATE RELTIONALE

Introducere

In acest capitol ne ocupam de :

Descrierea unei baze de date (BD),

Determinarea listei de tabele ale BD,

Stabilirea cheilor fiecarui tabel,

Proiectarea normalizata a unei BD,

Proiectarea diagramelor cu legaturile dintre relatii.

Vom prezenta cateva tehnici de proiectare riguroasa a BDR. Pentru modelarea completa a se vedea cursul I. Acest capitol ofera informatii suficiente pentru a intelege si a concepe o diagrama E R ( entity-relationship ) si a intelege o proiectare normalizata a unei BDR. Pentru aceasta trebuie parcursa succesiunea de pasi :

P1. Crearea specificatiei BDR,

P2. Crearea listei de entitati,

P3. Adaugarea cheilor la entitatile create la P2,

P4. Normalizarea relatiilor,

P5. Utilizarea de nume adecvate,

P6. Constructia diagramelor E/R

Crearea specificatiei BD

Structura BD trebuie stabilita in concordanta cu viitoarele obiective, cerute de utilizatori, pe care BD trebuie sa le satisfaca. Consideram exemplul unui magazin de inchiriere de DVD-video. Obiectivul acestei BD de inchiriere, este de a pastra inregistrarile privind clientii care frecventeaza magazinul, precum si cele care privesc DVD-urilor existente in stoc. BD ar trebui sa ne permita sa aflam ce DVD-uri sunt imprumutate si cui, sa determinam daca exista un DVD anume pentru a fi imprumutat, sa ne informeze asupra DVD-urilor care nu au fost restituie la timp. Sa poata ajuta clientii care nu isi amintesc exact titlul DVD-ului sau filmele in care a jucat o anumita actrita.

Lista de entitati

Trebuie sa intocmim lista de entitati (oameni, lucruri, concepte ) despre care trebuie sa pastram informatii in BD. Pentru BD de inchiriere a DVD-urilor trebuie sa pastram informatii referitoare la entitatile urmatoare: clienti, DVD si actori.

Entitatile sunt abstractii ale obiectelor din universul real ce sunt descrise in BD prin tabele.Apoi determinati atributele care descriu entitatile.

clienti(NUME , PRENUME, ADRESA, TELEFON, NR CARTE_DE_CREDIT) .

dvd-video(TITLU, STARE, NR_CLIENT, DATA_INCHIRIERII, DATA_RESTITUIRII, CATEGORIE,

OBSERVATII, PRODUCATOR, ADRESA_PRODUCATOR, NR_TEL_PRODUCATOR)

actori(NUME, PRENUME, DATA_NASTERII, DATA_MORTII, GEN)

facturi(NR_CLIENT, DATA_INCHIRIERII, DATA_RESTITUIRII, UMAR_FACTURA

NR_DVD, VALOARE_FACTURA )

STAREA arata daca DVD este in stoc, inchiriat, disponibil, distrus. Ne putem da seama ca informatiile referitoare la producator, stocate in tabelul dvd-video pot constitui o tabela separata. Putem imparti tabelul dvd-video in doua tabele, unul pentru dvd-video si unul pentru producatori .

dvd-video(TITLU, STARE, NR_CLIENT, DATA_INCHIRIERII, DATA_RESTITUIRII, CATEGORIE ,

OBSERVATII).

producatori(NUME_PRODUCATOR,ADRESA_PRODUCATOR, NR_TELEFON _PRODUCATOR ).

Adaugarea de chei 

Intr-o BD fiecare tabel trebuie sa contina o cheie primara ( o coloana sau o combinatie de coloane care identifica in mod unic fiecare linie din tabel ).

Ce atribute ar trebui sa fie folosite pentru o cheie primara in tabelele dvd-video si clienti Daca nu exista nici o combinatie de campuri care sa fie cheie primara va trebui sa adaugam un atribut fara nici o semnificatie pentru tabel. De exemplu pentru client ar putea fi un NUMAR_CLIENT care il identifica pe client si care are semnificatie de identificator al unui client numit frecvent numar serial.Analog s pentru DVD adaugam un NR-DVD.

O cheie poate fi compusa - constituita din doua sau mai multe coloane. Valorile din aceste coloane sunt combinate pentru a forma valoarea finala a cheii. In baza de date de compact discuri video(DVD) vom folosi drept cheie pentru tabelele clienti, dvd-video, producatori, actor si facturi cate un numar serial. Cheile primare pot fi adaugate ca chei externe in tabelele asociate. De exemplu o coloana cu numele producatorului poate fi adaugata in tabelul dvd-video ca o cheie externa, astfel incat fiecare DVD poate sa fie atasat unui producator. Acest pas poate continua prin adaugarea de noi chei pana la terminarea pasului care da toate restrictiile referentiale.

Normalizarea este o tehnica de a obtine o multime de tabele echivalente care elimina din BD a informatiile redundante si pe cele care prezinta anomalii la actualizare. Exista 5 nivele de normalizare ( FN1 - FN5 ).

Forme normale. Definitii

Prima forma normala (FN1).

O relatie r este in prima forma normala daca si numai daca domeniul oricarui atribut

este atomic ( ia numai valori simple ).

A doua forma normala (FN2).

O relatie r este in a doua forma normala daca este in FN1 si orice atribut, care nu este intr-o cheie, (neprim), este complet dependent de cheia primara.

Aceasta se aplica numai tabelelor care au chei compuse. in acest caz orice coloana care nu este dintr-o cheie trebuie sa depinda de cheia intreaga.

Sa luam tabelul DVD(NR-DVD, NR-ACTOR, TITLU, NUME-ACTOR) unde avem dependentele functionale; NR-ACTOR NUME-ACTOR si NR-DVD TITLU. Solutia este sa impartim coloanele in 2 tabele care sa cuprinda fiecare cate o dependenta si sa mai introducem al treilea tabel care cupleaza liniile celor doua tabele;

DVD( NR-DVD, TITLU),

actor(NR-ACTOR, NUME-ACTOR),

DVD-actor(NR-DVD,NR-ACTOR).

A treia forma normala (FN3)

O relatie r este in a treia forma normala daca este in FN1 si orice atribut neprim nu este tranzitiv dependent de cheia primara. Adica, coloanele care nu fac parte din chei nu depind de alte coloane decat de cheia primara.

Sa consideram tabelul

DVD(NR-DVD, TITLU, CATEGORIE, PRET).

Presupunem ca preturile de inchiriat sunt in functie de categorie, filmele de actiune 2$, de comedie 3$, etc. Atunci CATEGORIE determina PRET si atunci creem un tabel suplimentar care elimina redundanta determinata de pret;

DVD(NR-DVD, TITLU, CATEGORIE),

pret(CATEGORIE, PRET).

A patra forma normala (FN4).

O relatie r este in a patra forma normala daca pentru orice MV- dependenta ne triviala

partea stanga a ei este o super cheie.

A cincia forma normala (FN5). - forma normala de asociere

O relatie r este in a cincea forma normala daca si numai daca orice dependenta de unire este implicata de cheile candidate din r. Numele tabelelor normalizate il trecem la singular. Tabela facturi se descompune in doua tabele: factura si dvd-inchiriat. Atunci cand un client inchiriaza mai multe DVD-uri, pentru fiecare se creeaza cate un rand in tabelul factura si in tabelul dvd-inchiriat .

Coloanele care corespund NR_CLIENT, DATA_RESTITUIRII au fost eliminate din tabelul dvd-video si sunt in dvd-inchiriat. Coloana VALOAREA_FACTURII este stearsa din tabelul factura. Tabelul dvd-inchiriat include o coloana VALOAREA_INCHIRIERII, iar suma totala este determinata adaugand suma coloanele respective din toate randurile din factura. La aceasta valoare se poate adauga si TVA etc.

Cream o tabela categorie pentru a pastra descrierile diferitelor categorii ( comedie, drama, actiune, etc.). Tabelul dvd-video are o coloana care contine codul categoriei.

Mai cream 2 tabele de legaturi; actor-dvd, dvd-producator care exprima o legatura mai multe la mai multi ( many to many ).

Client(NR_CLIENT, NUME-CLIENT, PRENUME_CLIENT, ADRESA,

NR_TELEFON, NR_CARTE_CR),

dvd-video ( NR_DVD, TITLU, STARE, COD_CATEGORIE)m,

Categorie (COD_CATEGORIE, DESCRIERE_CATEGORIE) ,

Producator(COD_PRODUCATOR,NUME_PRODUCATOR,ADRESA_

PRODUCATOR, TELEFON_PRODUCATOR),

Actor(COD_ACTOR,NUME,PRENUME, DATA_NASTERII, GEN, DATA_MORTII),

Factur a(NR_FACTURA, NR_CLIENT, DATA_INCHIRIERII ),

dvd-producator (NRDVD, NR_PRODUCATOR),

dvd-actor(NR_DVD, NR_ACTOR),

Inchiriere-dvd NR_FACTURA,NR_DVD,DATA_RESTITUIRII,VALOARE_

INCHIRIERE).

Atributele subliniate reprezinta cheile simple sau compuse ale fiecarui tabel. Ultimul pas este ca in proiectare sa folosim numai nume potrivite si acceptate de orice dialect SQL.

REGULI DE INTEGRITATE RELATIONALE

Partea de integritate a unui model relational consta din reguli generale compuse din :

- reguli de integritate a entitatilor,

- reguli de integritate referentiale,

- reguli de integritate date de dependente.

O BD poate avea pe langa reguli generale si reguli specifice proprii ei. De exemplu o BD furnizori_de_produse poate cuprinde regula: cantitatea livrata trebuie sa fie mai mare ca zero mai mica deat 10000. Primele doua reguli generale se specifica cu ajutorul cheilor primare si al cheilor secundare.

Chei primare

Cheile primare sunt cazuri speciale de chei candidate. O cheie candidat este un identificator unic. Prin definitie orice relatie are cel putin o cheie candidat. Pentru orice relatie vom alege una din cheile candidat drept cheie primara si cele care raman se numesc chei alternative.

Definitia 1. Fie schema de relatie R=[A₁,A₂,.,A_n] si relatia r de schema R. Multimea de atribute K=[A_i, A_j, A_k] din R se numeste cheie candidat a lui r daca si numai daca satisface urmatoarele doua proprietati independente:

-unicitatea: in orice moment nu exista doua tupluri distincte din r care sa aiba

aceleasi valori pentru K;

-minimizare: nici un atribut din K nu poate fi eliminat fara a distruge

unicitatea.

Cheile primare constituie baza mecanismelor de adresare la nivel de tuplu in modelul relational. O cale de a pecifica un anumit tuplu individual este precizarea (r,k) unde r este numele relatiei si k este o valoare a cheii primare pentru fiecare tuplu. Valorile cheilor primare dintr-o relatie a BD servesc ca referinte de indentificare a tuplurilor. Cheia primara este fundamentala pentru operatiile modelului relational si are acelasi rol ca adresele din memoria principala.

Modelul relational utilizeaza frecvent o adresare asociativa, adica adresarea se face prin chei primare care se bazeaza pe valori, se aplica la nivel logic si nu se bazeaza pe pozitii.

Chei externe

Daca in alta relatie se adauga un atribut sau grup de atribute care in prima relatie este o cheie primara atunci aceasta in al doilea tabel se numeste cheie externa (straina).

Este o cheie externa deoarece face referire la prima relatie si este folosita doar pentru a realiza corespondenta tuplurilor din cele doua relatii.

Sa consideram BD furniprod (furnizori de produse) formata din tabelele (relatiile): furnizori notata fr, produse notata pr si livrare notata fp. Evident ca, codul unui furnizor sa zicem F1 ar trebui sa se gaseasca in relatia de livrare fp numai daca valoarea sa apare ca cheie primara in realtia furnizor fr. De exemplu nu are sens pentru fp sa includem o livrare de la frnizorul F9 daca acesta nu este inclus in relatia fr. De asemenea o valoare pentru atributul P# din relatia fp este permisa numai daca aceiasi valoare apare ca cheie primara P# in relatia pr produse. Atributele F# si P# din relatia de livrare fp se numesc chei externe. Cheia externa trebuie sa aiba acelasi domeniu de valori ca si cheia primara. Legatura dintre cele doua relatii este data de corespondenta dintre cheia primara si cheia externa. Aceasta legatura este alta cale de a reprezenta asocierile (relationship) dintre tupluri. Exista si alte legaturi dintre relatiile fr si pr de exemplu cea data de atributul ORAS dar aceasta nu este o cheie externa ea devine cheie externa daca se adauga in BD o relatie in are atributul ORAS sa devina cheie primara.

Reguli de integritate

Regulile de integritate date de dependente au fost tratate in capitolele III si IV.Regula de integritate a entitatilor este:

"Nici un atribut al unei chei primare dintr-o BDR nu are valoarea null."

Integritatea referentiala (referential integrity)

"Daca o relatie r₂ contine cheia externa Fk care corespunde cheii primare Pk din relatia r₁ a aceleiasi BD atunci orice valoare al lui Fk din r₂ trebuie sa fie egala cu o valoarea a lui Pk dintr-un tuplu a lui r₁ sau sa fie null complet (adica orice valoare a lui Fk trebuie sa fie null). Relatiile r₁ si r₂ nu sunt in mod necesar distincte."

O relatie de baza corespunde in SQL la un tabel de baza si a fost numita pe scurt relatie. Valoare "null" reprezinta o informatie necunoscuta sau o "proprietate inaplicabila". Regulile de integritate sunt justificate de urmatoarele:

- relatiile de baza contin entitati dintr-un univers real

- prin definitie, entitatile universului real sunt distincte, adica au o identificare unica

- cheile primare realizeaza o functie de identificare in modelul relational

- valorile cheilor primare nu pot fi null

Orice stare a unei BD ce nu satisface regulile de integritate este prin definitie inconsistenta (incorecta). In multe cazuri, o alternativa posibila pentru sistem este sa accepte operatiile, dar sa realizeze anumite operatii in plus care sa garanteze ca rezultatul este o starea consistenta. De exemplu daca utilizatorul sterge furnizorul F1 din relatia fr (furnizori) atunci sistemul trebuie sa stearga toate livrarile lui F1 din relatia fp (efect de stergere in "cascada"). In majoritatea sistemelor se specifica ce operatii ar trebui respinse si care trebuie acceptate si pentru acestea ce operatii compensatorii ar trebui realizate de sistem. Aceste sisteme sunt concretizate cu ajutorul clauzelor:

PRIMARY KEY si FOREIGN KEY din instructiunea CREATE TABLE

Cheia primara data de un atribut sau un grup de atribute se defineste cu ajutorul clauzei PRIMARY KEY din comanda CREATE TABLE.

CREATE TABLE fr

(F# INT,

NUMEF CHAR (10),

PRENUMEF CHAR (10),

ORAS CHAR (10),

PRIMARY KEY (F#));

Efectul caluzei PRIMARY KEY este ca, sistemul recunoaste ca F# este o cheie primara pentru relatia fr, care implicit este considerat unic si not null. Orice operatie care va acola aceste restrictii va fi respinsa. In ceea ce priveste proiectantul BD trebuie sa raspunda la urmatoarele 3 intrebari:

1. Se pot accepta valori null pentru o cheie externa?

Raspunsul la aceasta intrebare nu depinde de proiectant ci de universul real al  BD. Se poate face o livrare de la un furnizor care este necunoscut?

Ce se intampla cand incercam sa stergem o referinta la o cheie externa ? De

exemplu sa stergem un furnizor care realizeaza cel putin o livrare? In acest caz in

SQl exista 3 posibilitati:

CASCADES -operatia de stergere in cascada (sterge toate acele livrari)

RESTRICTED -operatia de stergere este restrictionata la cazul cand nu exista

astfel de intrebari

NULLFIES - cheia externa este null in toate livrarile si furnizorii sunt atunci

stersi

3. Ce ar trebui sa se realizeze cand se incearca sa se actualizeze o cheie primara

ce face o referinta la o cheie externa? De exemplu sa se actualizeze numarul

furnizorului pentru care exista cel putin o livrare. Exista aceleasi posibilitati ca la

stergere.

CASCADES operatia de actualizare CASCADE actualizeaza cheile externe cu acele valori

RESTRICTED operatia de actualizare este restrictionata la cazul cand nu exista astfel de livrari

NULLFIES-cheia externa este null si tabelele livrari si furnizori sunt actualizate.

Propunem o sintaxa in DDL pentru clauza FOREIGN KEY.

FOREIGN KEY (cheie_externa) IDENTIFIES nume_relatie registru

NULL [NOT] ALLOWED

DELETE OF nume_relatie "efect"

UPDATE OF cheie-primara_din_relatia "efect"

unde:

1. cheia externa este fie un atribut sau un grup de atribute;

2. registru este un nume de relatie ;

3. cheia primara din relatie specifica cheia primara din nume _relatie_efect;

4. efect este CASCADE sau RESTRICTED sau NULLFIES.

Dam un exemplu de definire a unui tabel pentru relatia fp (furnizori de produse) care indica o multime posibila de chei externe:

CREATE TABLE fp

(F INT,

P# INT,

CANT INT)

PRIMARY KEY (F#,P#)

FOREIGN KEY (F# IDENTIFIES fr

NULL NOT ALLOWED

DELETE OF fr RESTRICTED

UPDATE OF fr F# CASCADE)

FOREIGN KEY (P# IDENTIFIES pr

NULL NOT ALLOWED

DELETE OF pr RESTRICTED

UPDATE OF pr, P# RESTRICTED)