1 / 18

Normalization

Normalization. 1NF, 2NF, 3NF. 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.

ira-stuart
Télécharger la présentation

Normalization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related