Baze Date Normalizarea

7/29/2019 Baze Date Normalizarea

1/13

CAPITOLUL 2. PROIECTAREA MODELULUI RELAIONAL

AL DATELOR PRIN NORMALIZARE

n literatura de specialitate, n funcie de complexitatea bazei de date sunt abordate

urmtoarele metode de proiectare: proiectarea modelului relaional printr-un model static (utiliznd limbajul UML); proiectarea modelului relaional printr-un model conceptual (utiliznd modelul entitate asociere); proiectarea modelului relaional prin procesul de normalizare.n continuare este prezentat proiectarea modelului relaional prin procesul de normalizare.

Normalizarea este procesul care presupune descompunerea unui tabel relaional alctuitdintr-un set de atribute, n dou sau mai multe tabele care vor forma baza de date , cu scopul de aelimina redundanele (memorarea repetat a acelorai date) i anomaliile care pot aprea noperaiile de adugare, modificare sau tergere de nregistrri. Acest proces se fundamenteaz peconceptele:

dependen funcional

dependen multivaloare

forme normale.

Ideea central care st la baza proiectrii unei baze de date relaionale este aceea de dependena datelor [Dollinger]. Aceasta se refer la faptul c ntre atributele unei relaii (tabel relaional)sau ntre atribute din relaii diferite pot exista anumite legturi logice (dependene), iar acestelegturi influeneaz proprietile relaiilor n raport cu operaiile curente care apar n exploatareabazei de date, cum ar fi: adugarea, modificarea i tergerea nregistrrilor.

Exemplu:

Fie relaia Aprovizionare (Cod_Fz, Nume-fz, Adresa_Fz, DenMarfa, PretFact):

Cod_Fz Nume_Fz Adresa_Fz DenMarfa PretFact

100 SC ALFA SRL Bucuresti Portocale 5

100 SC ALFA SRL Bucuresti Banane 3

101 SC BETA SRL Brasov Portocale 4.8

102 SC GAMA SRL Galati Kiwi 6

----------- ---------- --------- --------

Analiznd aceast relaie se observ o redundan a datelor pentru aprovizionrile realizate cuacelai furnizor. Altfel spus valorile asociate atributelor Nume_Fz i Adres_Fz se repet lafiecare marf livrat de acel furnizor. Mai mult aceast redundan a datelor conduce la apariiaurmtoarelor anomalii:

Anomalia de adugare nu se poate nregistra un furnizor att timp ct acesta nulivreaz o marf.


2/13

Anomalia de tergere dac se terg toate mrfurile livrate de un furnizor, atunci sepierd, n mod neintenionat, i caracteristicile acelui furnizor (numele furnizorului, adresafurnizorului).

Anomalia de modificaredac se modific, spre exemplu, adresa unui furnizor, atuncieste necesar parcurgerea ntregii relaii pentru a opera modificarea la nivelul tuturor

nregistrrilor.Aceste anomalii pot fi eliminate, dac tabelul iniial Aprovizionare este supus unui proces denormalizare, n urma cruia va fi descompus n urmtoarele tabele:

Furnizori (Cod_Fz, Nume_Fz, Adresa_Fz)

Livrare (Cod_fz, DenMarfa, PretFact)

Furnizori

Cod_Fz Nume_Fz Adresa_Fz

100 SC ALFA SRL Bucureti

101 SC BETA SRL Brasov

102 SC GAMA SRL Galai

Livrare

Cod_Fz Den_Marfa PretFact

100 Portocale 5

100 Banane 3

101 Portocale 4.8

102 Kiwi 6


3/13

2.1. Dependene funcionale

2.1.1. Dependena funcional simpl

ntre dou atribute X i Y exist o dependen funcional simpl, notat , dac i

numai dac fiecrei valori a atributului X i corespunde o singur valoare a atributului Y.Atributul X se numete determinantul iar Y determinatul dependenei.

De exemplu, pentru o valoare asociat unui CodMarfa i corespunde o singur valoare pentruDenMarfa. n acest caz, este vorba despre o dependen funcional ce se stabilete ntre celedou atribute, reprezentat astfel: CodMarfa DenMarfa.

Alte exemple de dependene funcionale sunt reprezentate mai jos:

NrFact Datafact

NrFact CodFz.

CodMarfa, Nrfact PretFact

Tipuri de dependene funcionale:

Dependena funcional total.Dependena funcional XY este total dac nu existnici un subset Z al atributului X astfel nct ZY.

Exemplu:NrFact DatafactNrFact CodFzCodMarfa, Nrfact PretFact

Dependena funcional parial. Dependena funcional este parial dacexist un subset Z al atributului X (ZX) astfel ncat ZY.

Exemplu:CodMarfa, Nrfact Codfz este o dependen parial deoarece exist subsetulNrfact care determin funcional CodFz.

Dependena funcional trivial. Dependena funcional este trivial dacexist un subset Y al atributului X (YX) astfel ncat XY.

Exemplu: CodMarfa, Nrfact Nrfact este o dependen trivial, care nu aduce nici unplus de informaie.

Dependenele funcionale pot fi deduse folosind un set de reguli, numite axiomele luiArmstrong [Dollinger]:

1. Reflexivitate: Dac Y X, atunci X Y.

Exemplu: Dac Nrfact (CodMarfa, Nrfact), atunci(CodMarfa, Nrfact) Nrfact.

Aceast axiom genereaz dependenele funcionale triviale.

2. Augmentare (Amplificare):Dac X Y, atunci X Z Y Z

Exemplu: Dac NrFact CodFz , atunci

(Nrfact, Nrdocplata) (Codfz, Nrdocplata).


4/13

3. Tranzitivitate:Dac XY i YZ, atunci XZ.

Exemplu: Dac NrFact CodFz i Codfz Denfz, atunci NrfactDenfz. Aceastaxiom este utilizat n aplicarea formelor normale.

2.1.2. Dependena funcional multipl

ntre dou atribute X i Y exist o dependen funcional multipl, notat

X Y, dac i numai dac pentru o valoare a atributului X corespund dou sau mai multevalori a atributului Y.

De exemplu, pentru o valoare asociat unui CodFz vor corespunde mai multe valori pentruNrFact, deoarece la date calendaristice diferite unui furnizor i se pot emite mai multe facturi. nacest caz, este vorba despre o dependen funcional multiplce se stabilete ntre cele douatribute, reprezentat astfel:

CodFz NrFact. De asemenea, pe o factur se pot regsi mai multe mrfuri aprovizionate,

fapt pentru care putem spune c ntre atributele Nrfact i CodMarfa se stabilete o dependenfuncional multipl (Nrfact CodMarfa).

2.2. Dependene multivaloare

Se consider relaia R(X,Y,Z), unde X,Y,Z sunt atributele acesteia (simple sau compuse). Fiexi,yi,zi valorile atributelor X,Y i Z [Dollinger].

Spunem c exist o dependen multivaloarea atributului Z fa de Y, notat YZ, dacpentru orice valori x1, x2, y, z1i z2, x1x2, z1z2, astfel nct tuplurile (x1,y,z1) i (x2,y,z2) facparte din relaia R, atunci i tuplurile (x1,y,z2) i (x2,y,z1) fac parte din relaia R.

Din simetria definiiei rezult c dependena multivaloare YZ implic dependenamultivaloare YX.

De exemplu, n relaia R(Serie_Facultate, NrMatricolStudent, NumeProfesor) existdependene multivaloare, deoarece la seria unei faculti sunt nscrii mai muli studeni, carestudiaz cu mai muliprofesori la materii diferite. Dac fiecare student studiaz cu toi profesorii,atunci exist urmtoarele dependene multivaloare:

Serie_Facultate NrMatricolStudent

Serie_Facultate NumeProfesor

n mod practic, dac exist tuplurile (EAM_SeriaA, NrMatricol1, Profesor_Info) i

(EAM,_SeriaA, NrMatricol2, Profesor_Mate) atunci vor exista i tuplurile (EAM_SeriaA,NrMatricol1, Profesor_Mate) i (EAM_SeriaA, NrMatricol2, Profesor_Info).

n general dependenele multivaloare sunt mai dificil de identificat i mai rar ntlnite n practic,fapt ce ne determin s nu insistm mai mult asupra lor.


5/13

2.3 Formele normale

Teoria clasic a normalizrii este construit n jurul a cinci forme normale.E.F.Codd, printelemodelului relaional, a definit iniial trei forme normale [Fotache]: FN1, FN2 i FN3. Apoi,forma normal Boyce-Codd, numit astfel dup numele celor doi specialiti n domeniu, s-a dorita fi o form generalizat a FN2 i FN3. Aceste patru forme normale sunt asociate dependenelorfuncionale.

Formele normale 4 i 5 (FN4, FN5), asociate n literatura de specialitate cu numele cercettoruluiFagin, se bazeaz pe dependenele multivaloare.

Muli specialiti consider c pentru elaborarea unei baze de date relaionale sunt suficient deparcurs primele trei forme normale. Plecnd de la acest prezumie, n continuare vom prezentadoar primele trei forme normale (FN1, FN2 FN3).

Forma normal 1 (FN1) O relaie (tabel) este n prima form normal dac toate atributelesale sunt atomice(elementare, care nu se mai pot descompune) i nerepetitive.Un atribut esteconsiderat elementar atunci cnd descompunerea lui nu prezint interes pentru aplicaia ce se va

dezvolta.Exemplu: Fie tabelul Furnizor (CodFz, DenFz, Adresa)

Localitate Strada Numr

CodFz DenFz Adresa

1 SC Agro SA Bucureti, str. Popa Nan, nr.3

2 SC Muntenia SA Bucureti, str. Alexandriei, nr.36

3 SC Tram SA Braov, str. Biserica Neagr, nr.10

---------- -------------- --------------

Tabelul Furnizor nu respect FN1 dac exist cerine privind gruparea furnizorilor duplocaliti, deoarece este mult mai greu s extragi aceast informaie dintr-o adres complet.

Forma normal 2 (FN2).O relaie este n cea de-a doua form normal dac respect FN1 iorice atribut non-cheie este total dependentfa de cheia primar a relaiei.

Exemplu:Fie tabelul Marfuri_Facturi (NrFact, CodMarfa, DenMarfa, UM, Calitate, CantFact).

NrFact CodMarfa DenMarfa UM Calitate CantFact

10 100 Portocale kg 1 70

10 200 Kiwi kg 1 90

11 400 Pomelo buc 2 50


6/13

11 100 Portocale kg 1 100

--------- --------------- ---------------- ---- ----------- ---------------

Analiznd aceast relaie, pot fi identificate urmtoarele dependene funcionale:

CodMarfa DenMarfa, UM, Calitate, deoarece fiecare cod unic de marf determin ovaloare pentru denumirea mrfii, unitate de msur, calitate.

NrFact, CodMarfa CantFact, atributul compus (Nrfact, CodMarfa) determinfuncional CantFact.n mod practic, unei facturi nu i se poate asocia o cantitate facturattotal, altfel spus ntre NrFact i CantFact nu poate exista o dependen funcional.

Pornind de la aceste dependene se deduce c relaia Marfuri_Facturi are o cheie primarcompus, format din atributele NrFact i CodMarfa.

n mod practic, tabelul Marfuri_Facturi nu respect FN2 deoarece se poate observadependena funcional a atributelorDenMarfa, UM, Calitatefa de CodMarfa, ceea ce implic

dependena parial a acestor atribute fa de cheia relaiei format dinNrFacti CodMarfa.Aceast dependen funcional parial are drept consecine o serie de anomalii:

anomalia de adugare nu se pot aduga noi mrfuri atta timp ct acestea nu suntaprovizionate, altfel spus nu apar pe o factur;

anomalia de tergere - dac se terge nregistrarea corespunztoare pentru o singurmarf existent, atunci se pierd i informaiile referitoare la denumire marf, unitate demsur i calitate;

anomalia de modificare - dac se cere modificarea calitii pentru o marf, trebuiecutate toate nregistrrile n care se regsete aceasta, ceea ce implic parcurgerearelaiei n ntregime.

Figura 2.1 Dependene funcionale identificate

Aceste anomalii se pot elimina prin descompunerea relaiei Marfuri_Facturi n relaiile:

Marfuri (CodMarfa, DenMarfa, UM, Calitate)

MarfurFacturate (NrFact, CodMarfa, CantFact)


7/13

Forma normal 3 (FN3).O relaie este n FN3 dac respect FN2 i toate atributele non -cheiesunt dependente direct de cheia primar. Altfel spus FN3 interzice dependenele tranzitive.

Exemplu:

Fie tabelul Facturi_Furnizori (NrFact, DataFact, CodFz, DenFz, Localitate)

NrFact DataFact CodFz DenFz Localitate

10 01/03/2009 1 SC Agro SA Bucureti

11 05/03/2009 1 SC Agro SA Bucureti

12 12/03/2009 2 SC Tram SA Braov

---------- -------------- --------------

Pot fi identificate urmtoarele dependene funcionale:

NrFact DataFact, CodFz, deoarece fiecare numr de factur determin o valoarepentru data facturii i codul furnizorului care a emis acea factur.

CodFz DenFz, Localitate, deoarece fiecare cod unic de furnizor determin o valoarepentru denumirea furnizorului i Localitatea acestuia.

Se observ ca ntre atributele NrFact i DenFz, Localitate apar dependene tranzitive, fapt ce nedetermin s afirmm c tabelul Facturi_Furnizori nu respect FN3.

Aceste dependene funcional tranzitive au drept consecine o serie de anomalii identificabile laadugarea, modificarea sau tergerea nregistrrilor din cadrul acestui tabel.

Aceste anomalii pot fi eliminate prin descompunerea relaiei Facturi_Furnizori n relaiile:

Facturi (NrFact, DataFact, CodFz)

Furnizori (CodFz, DenFz, Localitate)


8/13

2.4 Cadrul metodologic de proiectare a modelului relaional prin normalizare

Proiectarea unei baze de date relaionale prin normalizare se poate realiza prin:

descompunerea tabelului relaional iniial n mai multe tabele, utiliznd formele normale(FN1, FN2, FN3, FN4, FN5);

descompunerea tabelului relaional iniial n mai multe tabele, utiliznd matricea

dependenelor funcionale sau graful dependenelor.

2.4.1 Proiectarea modelului relaional prin normalizare utiliznd formele normale

Paii recomandai a firealizai pentru proiectarea modelului relaional prin normalizare, utilizndformele normale sunt [Fotache]:

1. Inventarierea atributelor i constituirea tabelului relaional iniial (relaiei universale).Se vor prelua toate atributele care fac obiectul problemei de rezolvat, din documenteleprimare i situaiile de ieire.

2. Se determin cheia primar a relaiei i se reprezint toate dependenele funcionale cedecurg de aici.

3. Se elimin atributele calculabile i cele repetitiveastfel nct tabelul iniials respecteFN1.

4. Se elimin dependenele funcionale parialeprin descompunerea tabelului iniial ntabele cu structuri mai simple, care s respecte FN2.

5. Se elimin dependenele funcionale tranzitive prin descompunerea tabelelor din etapaanterioar astfel nct acestea s respecte FN3.

6. Se identific eventualele dependene multivaloarei se elimin astfel nct s tabelele srespecte FN4, respectiv FN5.

2.4.2 Proiectarea modelului relaional prin normalizare utiliznd matricea dependenelor

funcionale

Demersul metodologic de proiectare a unui model relaional prin normalizare utiliznd matriceadependenelor funcionale sau graful dependenelorpresupune parcurgerea urmtoarelor etape:

1. Inventarierea atributelor. ntr-un dicionar al datelor se vor prelua toate atributele carefac obiectul problemei de rezolvat, din documentele primare i situaiile de ieire.


9/13

2. Specificarea regulilor de gestiunediverse restricii/condiii impuse datelor. La nivelulacestei etape, algoritmii de calcul sunt asociai i ei regulilor de gestiune.

3. ntocmirea dicionarului de date de ctre proiectantul bazei de date care va urmrirespectarea urmtoarelor reguli:

atributele sunt nscrise o singur dat; sunt eliminate atributele sinonime;

sunt eliminate atributele calculate.

4. Stabilirea cheilor primare.

5. Stabilirea dependenelor funcionalece pot fi descrise printr-un graf al dependenelorsau n cadrul unei matrice a dependenelor funcionale.

6. Pentru atributele izolate se vor cuta grupuri de atribute ce pot constitui determinaniai acestora.

7. Formarea tabelelor - cu fiecare cheie primar i atributele determinate direct i

netranzitiv se va forma un tabel.8. Identificarea atributelor cu rol de cheie extern.

9. Definitivarea modelului relaional.

EXEMPLU:

O societate comercial dorete s-i informatizeze activitatea de aprovizionare cu mrfuri de lafurnizori. Furnizoriisocietii, se identific printr-un cod unic, nume, localitate, telefon i contbancar.

Aprovizionarea cu mrfuri se realizeaz n baza facturilor primite de la furnizori, fiecare Facturidentificndu-se printr-un numr unic, dat factur, data scadenei, codul, denumirea, contulbancar al furnizorului, codul, denumirea i contul clientului, codul, denumirea, unitatea demsur, cantitatea i preul mrfurilor facturate, valoarea acestora, valoare TVA i valoareatotal a facturii. Pentru fiecare zi de ntrziere (fa de data scadenei) a plii facturii, furnizoriipercep o penalizare de 1% din valoarea total a facturii.Nomenclatorul mrfurilor include referine la codul de marf, denumire marf, unitatea demsur i calitate. Nomenclatorul va conine maxim 100 de mrfuri distincte ale cror uniti demsur sunt exprimate n kg, buc, litri.Plata facturii se realizeaz printr-o Chitan, conform facturii emise, n care se precizeaznumrul chitanei, data chitanei, suma pltit, denumirea i contul emitentului, denumirea i

contul beneficiarului (furnizorului). Societatea pltete cu un astfel de document o singurfactur.Se cere s se proiecteze baza de date relaional prin normalizare utiliznd matriceadependenelor funcionale.n acest scop s-au parcurs paii urmtori (descrii n cadrul metodologic de proiectare amodelului relaional prin normalizare):

1. Inventarierea atributelor. Pe baza informaiilor referitoare la activitatea de aprovizionare cumrfuri de la furnizori, se poate ntocmi dicionarul de atribute:


10/13

Nr.

Crt.

Atribut Semnificaie

1. CodFz Cod Furnizor

2. CodBenef Cod Beneficiar

3. NumeFz Nume Furnizor4. Localitate Localitate Furnizor

5. Telefon Telefon Furnizor

6. ContBancar Cont Bancar Furnizor

7. NrFact Numr Factur

8. DataFact Data Facturii

9. DataScad Data Scaden Factur

10. CantitateFact Cantitate Facturat

11. PretFact Pret Facturare

12. CotaTVA Cota TVA

13 Valoaremarfa Valoare Marf Facturat

14. TVA Valoare TVA

15. ValoareFactura Valore Total Factur

16. CodMarfa Cod Marf

17. DenMarfa Denumire Marf

18. UM Unitate de Msur19. Calitate Calitate Marf

20. Nrchit Numr Chitan

21. DataChit Data Chitan

22. SumaPl Suma Pltit

23. TotalSumePl Sume Totale Pltite

24. Penalizare Penalizare Factur

25 Nr_zile-intarz Nr. Zile de ntrziere

2. Specificarea regulilor de gestiune: O factur este emis de un singur furnizor.

O factur poate conine mai multe mrfuri, iar o marf se poate regsi pe maimulte facturi.


11/13

O factur poate fi pltit ealonat.

O chitan este conform cu o factur emis de furnizor.

Algoritmi de calcul:

Valoare_Marf=CantitateFact*PretFact

TVA=CotaTVA*Valore_Marfa

ValoareFactura=Valoare_Marfa+TVA

Nr_zile_ntarz=DataChit - DataScad

Penalizare =Nr_zile_intarziere*ValoareFactura*1/100

Totalsumepl= suma pl

3. ntocmirea dicionarului de date,se va realiza pe baza urmtoarelor reguli:

fiecare atribut este nscris o singur dat; sunt eliminate atributele sinonime (n exemplul prezentat, CodFz i CodBenef sunt

sinonime, deci se va reine doar unul dintre acestea CodFz);

nu sunt preluate atributele calculate (Valoare_Marfa, TVA, ValoareFactura,Nr_zile_ntarz, Penalizare, Totalsumepl).

Dicionarul de date (DD): CodFz, NumeFz, Localitatea, ContBancar, Telefon, NrFact, DataFact,DataScad, CantitateFact, PretFact, CotaTVA, CodMarfa, DenMarfa, UM, Calitate, NrChit,DataChit, SumaPl.

4. Stabilirea cheilor primare: CodFz, NrFact, CodMarfa, NrChit.5. Stabilirea dependenelor funcionale.

a. Graful dependenelor funcionale simple:

b. Graful dependenelor funcionale multiple:Nrfact CodFz CodMarfa NrFact

NrFact NrChit NrFact CodMarfa


12/13

6. Atributele izolate: CantitateFact, PretFact i CotaTVA vor fi determinate de un grup deatribute aa cum sunt reprezentate mai jos.

Ne punem ntrebarea De ce Nrfact nu determin funcional, spre exemplu, Cantitatefact?.n mod practic, o factur poate conine mai multe mrfuri achiziionate i, prin urmare, Nrfact nuva determina o singur valoare pentru CantitateFact. Pentru o factur (NrFact) se poate asocia ovaloare total facturat, dar nu se pot nsuma cantitaile, preurile, CotaTva a mrfurilorfacturate.

Atributul compus (Nrfact, CodMarfa) reprezint o cheie primar care se va aduga cheiloridentificate n etapa precedent.

Dependenele funcionale identificate anterior se vor reprezenta ntr-o Matrice adependenelor funcionale (reprezentat ntr-o form simplificat, fr a include dependenele

funcionale multiple i dependenele funcionale multiple tranzitive, n figura 2.2). Liniile icoloanele acestei matrici sunt reprezentate de atributele din dicionarul de date i atributelecompuse care au fost identificate ca fiind chei primare. Cheile primare sunt marcate princaracterul #. Pentru fiecare cheie primar, pe acea linie, sunt reprezentate tipurile de dependenefuncionale identificate astfel:

1- dependenele funcionale simple

1T - dependenele funcionale simple tranzitive


13/13

Figura 2. 2 Matricea dependenelor funcionale

7. Formarea tabelelor. Fiecare cheie primar i atributele determinate direct i netranzitivvor forma un tabel.

8. Identificarea cheilor externe (atributele subliniate prin linie discontinu la nivelulmodelului relaional).

9. Definitivarea modelului relaional.FURNIZORI(CodFz, NumeFz, Localitate, Telefon, ContBancar)

FACTURI(NrFact, DataFact, DataScad, CodFz)

MARFURI(CodMarfa, DenMarfa, UM, Calitate)

MARFURIFACTURATE (CodMarfa, NrFact, CantitateFact, PretFact, CotaTVA)

CHITANTE (NrChit, DataChit, SumaPl, NrFact)

Baze Date Normalizarea

Documents

Transcript of Baze Date Normalizarea