Lectia 3 - Normalizarea bazei de date

1. Lectia 3 - Normalizarea bazei de date

2. Dependenţele de date (data dependencies) reprezintă constrângeri care se impun valorilor atributelor unei relaţii şi care determină proprietăţile relaţiei în raport cu operaţiile de inserare, ştergere şi actualizare a tuplurilor. • Pe baza dependenţelor de date se pot stabili reguli de definire a relaţiilor, astfel încât acestea să prezinte anumite proprietăţi, proprietăţi care caracterizează formele normaleale relaţiilor (sau gradele de normalizare ale acestora). • O formă normală a unei relaţii (normal form) presupune anumite condiţii pe care trebuie să le îndeplinească valorile atributelor şi dependenţele de date definite pe acea relaţie.

3. Normalizarea relaţiilor (normalization) constă în descompunerea lor, astfel încât relaţiile rezultate să îndeplinească condiţii din ce în ce mai restrictive în ceea ce priveşte dependenţele de date, adică să corespundă unor forme normale cât mai avansate. • Prin normalizare se elimină (sau se micşorează) redundanţa datelor memorate în relaţii şi anomaliile care provin din această redundanţă. • Exemplu: • Fie relaţia AP(IdAngajat,Nume,Prenume,Adresa,IdProiect,Ore). Fiecare tuplu al relaţiei conţine informaţii despre un angajat şi numărul de ore aferente fiecăruia dintre proiectele la care acesta lucrează.

4. Dacă se admite că un angajat lucrează la mai multe proiecte, atunci cheia primară a relaţiei AP este PK = {IdAngajat,IdProiect}. Se observă că datele despre fiecare angajat (Nume, Prenume, Adresa) se repetă în fiecare tuplu corespunzător fiecărui proiect la care acesta lucrează, ceea ce reprezintă un grad ridicat de redundanţăa datelor. Această redundanţă are ca efect atât creşterea spaţiului de memorare a relaţiei, cât şi anomalii de actualizare a relaţiei. Anomaliile de actualizare a relaţiei apar la inserarea, ştergerea sau actualizarea tuplurilor relaţiei.

5. Anomalii de inserare: • nu se pot introduce date despre un angajat (identificatorul angajatului, numele, prenumele, adresa) dacă nu există cel puţin un proiect la care acesta să lucreze. • se poate introduce un nou tuplu care conţine alte valori ale atributelor Nume, Prenume sau Adresa, pentru aceeaşi valoare a identificatorului IdAngajat. • De exemplu, se poate introduce tuplul (1,Dragomir,Eugen,Bucuresti,P3,110) • Acest tuplu este acceptat de SGBD, deoarece are cheia primară (1,P3), care nu mai există în relaţia AP. Dar în acest moment starea relaţiei AP nu este consistentă din punct de vedere semantic deoarece există doi angajati (Ionescu Ion şi Dragomir Eugen) care au acelaşi număr de identificare (IdAngajat = 1).

6. Anomalii de ştergere: • Dacă se şterg toate tuplurile referitoare la un anumit proiect din relaţia AP, atunci se pot pierde toate datele referitoare la acei angajaţi care lucrează doar la proiectul respectiv. De exemplu, dacă se şterg tuplurile referitoare la proiectul P2 (1,Ionescu,Ion,Bucuresti,P2,150), (2,Popescu,Petre,Craiova,P2,50), (3,Marin,Mihai,Ploieşti,P2,120), atunci se pierd toate informaţiile despre angajatul Marin Mihai (identificatorul angajatului, numele, prenumele, adresa).

7. Anomalii de actualizare: • Dacă se modifică valoarea unuia din atributele care au valori redundante (Nume,Prenume sau Adresa) într-un tuplu al relaţiei AP, starea relaţiei poate deveni inconsistentă. • De exemplu, dacă în tuplul • (1, Ionescu,Ion,Bucuresti,P2,150) se modifică atributul Nume la valoarea Gheorghiu, atunci în relaţie vor exista tuplurile: (1,Ionescu,Ion, Bucuresti,P1,100) şi (1,Gheorghiu,Ion,Bucuresti,P2,150), adică doi angajati cu nume diferite (Ionescu şi Gheorghiu) au acelaşi număr de identificare (1). • De asemenea, pot să apară numeroase alte situaţii de inconsistenţă atunci când se fac actualizări într-o astfel de relaţie care prezintă date redundante.

8. Anomaliile descrise mai sus se pot elimina dacă relaţia APse descompune în două relaţii echivalente: • relaţia A(IdAngajat,Nume,Prenume,Adresa), cu cheia primară IdAngajat • relaţia P(IdAngajat,IdProiect,Ore), cu cheia primară {IdAngajat,IdProiect}, iar atributul IdAngajat este o cheie străină care referă cheia primară cu acelaşi nume din relaţia A. !!!Aceste relaţii nu mai prezintă redundanţă şi nici anomalii la actualizarea lor.

9. Normalizarea este un proces formal de analiză a relaţiilor bazate pe chei primare (sau pe baza cheilor candidat în cazul BCNF). Normalizarea presupune îndeplinirea unor reguli prin care baza de date se poate normaliza până la un anumit grad. Dacă o cerinţă nu este satisfăcută, relaţia trebuie descompusă în mai multe relaţii, care individual satisfac cerinţele formei normale.

10. FN Normalizare

11. FN1 • O relaţie este normalizată în prima formă normală (FN1) dacă fiecare atribut ia numai valori atomice şi scalare din domeniul său de definiţie. • Exemplu: • PERSOANE(IdPersoana,Nume,Prenume,Adresa,NrTelefon) • atributul NrTelefon poate lua mai multe valori (telefon de acasă, telefon la birou, telefon mobil), deci este o relaţie nenormalizată. • Această relaţie se poate normaliza prin înlocuirea atributului NrTelefon cu trei atribute, câte unul pentru fiecare valoare posibilă: • PERSOANE(IdPersoana,Nume,Prenume,Adresa, TelefonAcasa,TelefonBirou,TelefonMobil) • O soluţie mai eficientă este de a înlocui relaţia dată cu relaţiile: • PERSOANE(IdPersoana,Nume,Prenume,Adresa) • TELEFOANE(IdPersoana,NrTelefon)

12. O soluţie mai eficientă este de a înlocui relaţia dată cu relaţiile: PERSOANE(IdPersoana,Nume,Prenume,Adresa) TELEFOANE(IdPersoana,NrTelefon) aflate în asociere 1:N prin cheia străină IdPersoana din relaţia TELEFOANE, care referă cheia primară cu acelaşi nume din relaţia PERSOANE.

13. FN2 • O relaţie este în a doua formă normală, dacă este în prima forma normală (FN2) şi fiecare atribut, care nu aparţine cheii primare, este într-o dependenţă funcţională totalăfaţă de cheia primară. • Rezultă că, dacă orice cheie a unei relaţii este formată dintr-un singur atribut, relaţia este în FN2. • De asemenea, este evident faptul că o relaţie compusă din două atribute este în FN2, deoarece, fie cheia este formată din ambele atribute şi atunci nu există atribute neprime, fie cheia este formată dintr-unul din atribute iar dependenţa funcţională a celuilalt atribut (care este atribut neprim) faţă de cheie este totală.

14. Exemplu: Schema relaţiei este: AP(IdAngajat,Nume, Prenume,Adresa,IdProiect,Ore), iar mulţimea dependenţelor funcţionale FAP stabilite pe baza semnificaţiei atributelor este cea specificată deja, adică: FAP = {IdAngajat→Nume,IdAngajat→Prenume, IdAngajat→Adresa,{IdAngajat,IdProiect}→Ore} a - Dependenţe funcţionale în relaţia AP (AP nu este în FN2); b, c -descompunerea relaţiei AP în relaţiile A şi P(sunt în FN2).

15. Mulţimea dependenţelor funcţionale din relaţia A este FA={IdAngajat→Nume, IdAngajat→Prenume,IdAngajat→Adresa}, din care se deduce cheia primară IdAngajat. În toate dependenţele funcţionale din FA atributele neprime Nume,Prenume,Adresa sunt total dependente faţă de cheia primară a relaţiei, deci relaţia A este în FN2. Mulţimea dependenţelor funcţionale din relaţia P este FP= {{IdAngajat,IdProiect} →Ore}, din care se deduce cheia primară {IdAngajat,IdProiect}. În dependenţa funcţională din FP atributul neprim Ore este total dependent faţă de cheia primară a relaţiei, deci relaţia P este în FN2.

16. FN3 • O relaţie este în a treia formă normală, daca este în forma normală doi şi fiecare atribut care nu aparţine cheii primare (atribut neprim) nu este într-o dependenţă tranzitivăfaţă de cheia primară. Adică, toate dependenţele funcţionale ale relaţiei, determinate de cheia primară, sunt totale şi nu există nici o dependenţă a unui atribut neprim faţă de alt atribut neprim. • Orice relaţie formată din două atribute este în FN3 deoarece ea se află în FN2, şi nu poate exista nici un atribut neprim care să determine funcţional un alt atribut neprim, deoarece o relaţie cu două atribute nu poate avea decât cel mult un atribut neprim.

17. Exemplu: AFS(IdAngajat,Nume,Prenume,Adresa,Func-tie,Salariu), cu mulţimea dependenţelor funcţionale FAFS = {IdAngajat→ Nume, IdAngajat→Prenume, IdAngajat→Functie, Functie→Salariu} Cheia primară a relaţiei este atributul IdAngajat, şi ea poate fi dedusă din mulţimea FAFS a dependenţelor funcţionale. Se consideră că fiecare atribut ia numai valori atomice şi scalare, deci relaţia este în FN1.

18. Mulţimea dependenţelor funcţionale este formatădin dependenţe funcţionale totale ale unor atribute neprime faţă de cheia primară a relaţiei, deci relaţia este în FN2. Dependenţa funcţională (Functie→Salariu) semnifică faptul că în instituţia respectivă toţi salariaţii cu aceeaşi funcţie au acelaşi salariu (adică funcţia determină salariul, ceea ce este plauzibil). Această dependenţă funcţională a atributului neprim Salariu faţă de alt atribut neprim (Functie), arată că relaţia nu este în a treia formă normală (FN3). a- Dependenţele funcţionale ale relaţiei AFS (AFS nu este în FN3); b, c - descompunerea relaţiei AFS în relaţiei AF şi FS.

19. Chiar dacă relaţia AFS este în FN2, în aceasta încă mai există redundanţă a datelor, deoarece valoarea salariului corespunzător unei funcţii se înregistrează de mai multe ori, pentru fiecare salariat care deţine acea funcţie. • Această redundanţă provoacă anomalii: • Inserare: nu se poate înregistra valoarea salariului corespunzător unei anumite funcţii, dacă nu se înregistrează cel puţin un salariat cu acea funcţie. • Ştergere: dacă se şterg tuplurile corespunzătoare tuturor salariaţilor care deţin o anumită funcţie, atunci se pierde informaţia referitoare la salariul corespunzător funcţiei respective. • Actualizare: dacă se modifică salariul fără să se modifice corespunzător şi funcţia, atunci pot exista în relaţie două sau mai multe tupluri care au aceeaşi valoare a atributului Functie, dar valori diferite ale atributului Salariu, deci nu este respectată dependenţa funcţională Functie→Salariu.

20. Astfel de redundanţe şi anomaliile de actualizare pe care le provoacă se pot elimina dacă se descompune relaţia în două (sau mai multe) relaţii, care să nu conţină date redundante. • Relaţia AFSse poate descompune în relaţiile: • AF(IdAngajat,Nume, Prenume,Adresa,Functie) • FS(Functie,Salariu) • Cheia primară a relaţiei AF este IdAngajat, şi se poate deduce uşor din dependenţele funcţionale ale relaţiei AF. • Cheia primară a relaţiei este atributul Functieşi se deduce cu uşurinţă din mulţimea FFS a dependenţelor funcţionale ale acestei relaţii.