1 / 20

Normalization

Normalization. 1NF, 2NF, 3NF. Introduction. In Kp.88 we have the suppliers and parts database S { S#, SNAME, STATUS, CITY} P { P#, PNAME, COLOR, WEIGHT, CITY} SP { S#, P#, QTY} What happens if the design is changed in some way like Supplier ’ s CITY is inserted in SP  SCP

lilah-alvarez
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. Introduction • In Kp.88 we have the suppliers and parts database • S {S#, SNAME, STATUS, CITY} • P {P#, PNAME, COLOR, WEIGHT, CITY} • SP {S#, P#, QTY} • What happens if the design is changed in some way like • Supplier’s CITY is inserted in SP  SCP • See Kp. 408 Fig.11.1

  3. Introduction (cont.1) • In Fig. 11.1(kp.408), the sample value for relvar SCP • There are many redundant information • S1’s city  London: 6 times • S4’s city  London: 3 times • What’ll be remained if update on S1’s city happens incorrectly due to the redundancy? • Some S1’s city is London • Some S1’s city is Amsterdam • ……  good design principle “One fact in one place” = avoiding redundancy

  4. Introduction (cont.2) • Normalization • Concerns about how a given relation containing certain undesirable properties can be converted to a more desirable form • Normal forms • If a relation satisfies a certain specified set of constraints

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

  6. 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!

  7. 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

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

  9. 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) also see fig.11.6 (kp.418) FIRST{S#, STATUS, CITY, P#, QTY} PRIMARY KEY {S#, P#}

  10. Anomalies in 1NF • Let’s think about anomalies due to the FD S#CITY • 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

  11. 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>: 4th from the bottom in fig.11.6 (kp.418)

  12. 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 • May cause inconsistency if we miss updating any tuple

  13. 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} • See fig. 11.8 (kp. 420)

  14. Solution for the anomalies of 1NF(cont.) • New relvars • SECOND{S#, STATUS, CITY} • SP{S#, P#, QTY} • See fig. 11.8 (kp. 420) and check • Can we insert <S5, , Athens>? • Can we delete <S3, p2, 200> without deleting S3’s information in SECOND? • Can we update S1’s city in one tuple only?

  15. 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 reduced from the primary key {S#, P#} • SECOND{S#, STATUS, CITY}, SP{S#, P#, QTY} • 2NF

  16. 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

  17. 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> in Fig.11.8 (kp.420), we lose STATUS information of Athens also.

  18. 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 have to update 2 tuples • May cause inconsistency if we miss updating any tuple

  19. 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 • See fig.11.10 (kp. 423) for example tables

  20. 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