1 / 19

Decomposition

Decomposition. By Timothy Chen CS157A. Goal to Decomposition. Eliminate redundancy by decomposing a relation into several relations in a higher normal form. Example we saw can be eliminated by decomposing the relation schema Movies(title, year, length, filmType, studioName, starName)

nassor
Download Presentation

Decomposition

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. Decomposition By TimothyChen CS157A

  2. Goal to Decomposition • Eliminate redundancy by decomposing a relation into several relations in a higher normal form

  3. Example we saw can be eliminated by decomposing the relation schema Movies(title, year, length, filmType, studioName, starName) into two relation schemas Movies1(title, year, length, filmType, studioName) Movies2(title, year, starName) Decomposing relations

  4. Our goals of DataBase design For Decomposition • BCNF • Losslessness • Dependency preservation

  5. Boyce-Codd normal form (BCNF) • A relation is in Boyce-Codd normal form (BCNF) if there are only unavoidable functional dependencies among its attributes.

  6. BCNF Example • Movies1(title, year, length, filmType, studioName) • Movies2(title, year, starName) • are in BCNF. The only nontrivial nonreducible FDs are (all in Movies1): • title year -> length • title year -> filmType • title year -> studioName • and they are unavoidable since {title, year} is a key for both relations.

  7. Lossless Decomposition • Reduce unnecessary redundancy • Retrieve information efficiently

  8. How do we know the a decomposition is lossless? • The decomposition is lossless if we can recover

  9. Lossless example • R( A, B, C) -> decomposition • R(A, B) , R(A, C) ->recover back • To R(A, B, C)

  10. Example

  11. Incorrect Decomposition • The data will lost

  12. As this

  13. Dependency Preservation • Dependency preservation is another important requirement since a dependency is a constraint on the database and if X -> Y holds than we know that the two (sets) attributes are closely related and it would be useful if both attributes appeared in the same relation so that the dependency can be checked easily.

  14. What is Dependency Preservation • Each FD specified in F either appears directly in one of the relations in the decomposition, or be inferred from FDs that appear in some relation.

  15. Example • R(A, B) • A= {name, height, birthday, address } • B = { name -> address, birthday} • Decompose into • A 1= {name, birthday, address } • B 1= {name -> address, birthday } • And • A2= {name, height} • B2 = { } • B= B1 U B2

  16. Example 2 • Consider relation ABCD, with FD’s : A ->B, B ->C, C ->D • Decompose into two relations: ABC and CD. • ABC supports the FD’s A->B, B->C. • CD supports the FD C->D. • All the original dependencies are preserved.

  17. Not Dependency Preservation Example • Consider relation ABCD, with FD’s: A ->B, B ->C, C->D • Decompose into two relations: ACD and BC. • no relation supports A ->B So the dependency is not preserved.

  18. Reference • http://www.itu.dk/people/pag/IDB05/Normalizations.pdf • DataBase System Concepts Fifth Edition

  19. Thank you

More Related