Normalization

# Normalization

Télécharger la présentation

## Normalization

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
##### Presentation Transcript

1. Normalization 1NF, 2NF, 3NF

2. Normal forms • Levels of normalization • Universe of relvars • Normalized and unnormalized • 1NF relvars • Normalized • 2NF relvars • 3NF relvars • BCNF relvars • 4NF relvars • 5NF relvars • The normalization procedure is reversible • Ex) 2NF  3NF • No information is lost More highly normalized

3. Nonloss Decomposition • Nonloss decomposition satisfies the following two properties; • Breaking down a relvar does not lose information • Reversibility • The original relvar is equal to the join of decomposed relvars (join of its projections) • Correct further normalization has to satisfy this property!

4. Example of nonloss decomposition • S{S#, STATUS, CITY} is decomposed into two ways • SST{S#, STATUS}, SC{S#, CITY}  nonloss • SST{S#, STATUS}, STC{STATUS. CITY}  lossy

5. Functional dependencies and nonloss decompositions • If R1, R2 are projections of some relvar R, and R1 and R2 include all of the attributes of R, • What conditions have to be satisfied to guarantee that joining of R1 and R2 takes us back to original R?  Preservation of functional dependencies • Ex) in the previous example S{S#, STATUS, CITY} : S#STATUS, S#CITY SST{S#, STATUS}, SC{S#, CITY} : S#STATUS, S#CITY SST{S#, STATUS}, STC{STATUS. CITY} : S#STATUS, S#CITY is lost

6. FD diagrams • Pictorial representation of FDs • Ex) FD diagrams for relvars S, SP, and P

7. First Normal Form • A relvar is in 1NF • Iff every tuple contains exactly one value for each attribute in every legal value of the relvar • Ex) FIRST{S#, STATUS, CITY, P#, QTY} PRIMARY KEY {S#, P#}

8. Anomalies in 1NF • Let’s think about anomalies due to the FD S#CITY • FIRST{S#, STATUS, CITY, P#, QTY} • Insert • We cannot just insert a supplier’s city unless the supplier must supply at least one part • Ex) insertion of <S5, , Athens, , >  primary key (S#, p#) value becomes null : not allowed

9. Anomalies in 1NF (cont.1) • Delete • If we delete a sole tuple for a particular supplier, we lose • not only his shipment • but also his city. • Ex) <S3, 10, Paris, P2, 200>: the only tuple for S3

10. Anomalies in 1NF (cont.2) • Update • If we update the city value for a particular supplier, we may have to update many tuples • Ex) <S1, London>  <S1, Amsterdam>: • We have to update 6 tuples in the text example • May cause inconsistency if we miss updating any tuple

11. Solution for the anomalies of 1NF • Decompose relvar • FIRST{S#, STATUS, CITY, P#, QTY} into 2 relvars • SECOND{S#, STATUS, CITY} • SP{S#, P#, QTY}

12. Solution for the anomalies of 1NF(cont.) • New relvars • SECOND{S#, STATUS, CITY} • SP{S#, P#, QTY}

13. Second Normal Form • A relvar is in 2NF iff • 1NF and • Every nonkey attribute is irreducibly dependent on the primary key (즉, 기본키의 일부와 종속성 없음) • Ex) • FIRST{S#, STATUS, CITY, P#, QTY} • Not 2NF because of FDs S#CITY, S#STATUS • S# is a part of the primary key {S#, P#} • SECOND{S#, STATUS, CITY}, SP{S#, P#, QTY} • 2NF

14. Problem of 2NF • Lack of mutual independence among its nonkey attributes • Ex) in SECOND{S#, STATUS, CITY}, SP{S#, P#, QTY}, • we still have an FD CITYSTATUS • Because S#CITY, CITYSTATUS, • S#STATUS : transitive dependency

15. Anomalies of SECOND • SECOND{S#, STATUS, CITY}, • FD S#STATUS, S#CITY, CITYSTATUS • Insert • We cannot insert the fact only that a particular city has a particular status (CITY STATUS) • We can insert the fact only when a supplier is actually in the city • Delete • If we delete a tuple in SECOND, we may delete the STATUS information of the CITY • Ex) if we delete <S5, 30, Athens>, we lose STATUS information of Athens also. (Athens appears only once.)

16. Anomalies of SECOND (cont.) • SECOND{S#, STATUS, CITY}, • FD S#STATUS, S#CITY, CITYSTATUS • Update • If we update the STATUS value for a particular CITY, we may have to update many tuples • Ex) <London, 20>  <London, 30>: • We may have to update 2 tuples • May cause inconsistency if we miss updating any tuple

17. Solution for Anomalies of SECOND • Decompose • SECOND{S#, STATUS, CITY}, • FD S#STATUS, S#CITY, CITYSTATUS • Into SC{S#, CITY}, CS{CITY, STATUS} • The effect of the decomposition is to eliminate the transitive dependencies

18. Third Normal Form • A relvar is 3NF iff • 2NF • Every nonkey attribute is nontransitively dependent on the primary key(즉, 기본키에 이행적 종속성 없음) • In other words, no mutual dependency • Ex) SC, and CS: 3NF