1 / 17

Database Systems {week 03b}

Rensselaer Polytechnic Institute CSCI-4380 – Database Systems David Goldschmidt, Ph.D. Database Systems {week 03b}. Closure (review). Given a set of attributes X, the closure X+ is the set of attributes functionally determined by X

jabir
Download Presentation

Database Systems {week 03b}

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. Rensselaer Polytechnic Institute CSCI-4380 – Database Systems David Goldschmidt, Ph.D. Database Systems{week 03b}

  2. Closure (review) • Given a set of attributes X, theclosure X+ is the set of attributes functionally determined by X • Given a relation R and a set F of functional dependencies, we need a way to find whether a functional dependency X  Y is true with respect to F

  3. Closure example (review) • Given relation R with attributes A, B, C, D, E and A  BC, CD  E, BE  C • AE  _____? • From reflexivity, AE+ = { A, E } • From A  BC, AE+ = { A, B, C, E } • No other rules are applicable or add to AE+ • We conclude that AE  ABCE or simply AE  BC • Or AE  A, AE  B, AE  C, and AE  E

  4. Closure of a set of attributes (review) • Given a set F of functional dependencies, the closure X+ of a set of attributes X is determined by the following algorithm: • Initialize X+ to X • Repeat until X+ does not change: • Find any unapplied functional dependency Y  Zin F such that Y  X+ • Set X+ = X+  Z

  5. Keys revisited (review) • A key K for a given relation R is a minimal set of attributes A1, A2, ..., An such that closure {A1, A2, ..., An}+ is the set of all attributes of R • MusicGroup(name, artist, genre,dateformed, datefirstjoined) • name  genre dateformed • name artist  datefirstjoined • K must be (name, artist) because K+ = {name, artist, genre, dateformed, datefirstjoined}

  6. Closure of a set of FDs • Given a set F of functional dependencies, closure F+ is the set of all functional dependencies implied by F • F+ can be found using the set of inference rules (reflexivity, transitivity, augmentation, etc.) • Sets F1 and F2 of functional dependencies are considered equal if they have the same closure (i.e. F1+ = F2+)

  7. Armstrong’s axioms • In addition to determining closure F+ forset F of functional dependencies, we can also derive any functional dependency that follows from F via Armstrong’s axioms: • Reflexivity (if Y  X, then X  Y) • Augmentation (if X  Y, then XZ  YZ) • Transitivity (if X  Y and Y  Z, then X  Z)

  8. Basis • Given a set F of functional dependencies, any set of functional dependencies that’s equivalent to F is called a basis • We limit the possibilities by requiringthat each dependency has a singleattribute on the right-hand side • How many bases are there for a relation Rwith n functional dependencies in F?

  9. Minimal basis • A minimal basis for a relation R is a basis B that satisfies the following conditions: • All functional dependencies in B havesingleton right-hand sides • If any functional dependency is removed from B, the result is no longer a basis • If any left-hand side attribute is removed froma functional dependency of B, the result is no longer a basis

  10. Identifying a minimal basis • Given basis B, we can determine whether it is a minimal basis via the algorithm below: • For each functional dependency X  Y in B, check if B – { X  Y } still implies X  Y • if so, remove X  Y • For each functional dependency XW  Y in B, check if X+ is the same with respect to Fand ( F – { XW  Y } )  { X  Y } • if so, replace XW  Y with X  Y

  11. Exercises (part one) • Given relation R( A, B, C, D ) andfunctional dependencies AB  C,C  D, and D  A • What are the keys of R? • What are the superkeys of R that are not keys? • Is the given set of functional dependencies a basis? Is it a minimal basis?

  12. The need for normalization • What’s wrong with the relation below? • MusicGroup( name, artist, genre, dateformed, datefirstjoined ) • i.e. how can tuples become corruptedor incorrect?

  13. The need for normalization • Without normalization, problems with relations include: • Unnecessary redundancy • Insert anomalies • Update anomalies • Delete anomalies

  14. Decomposition • Splitting a relation into two (more specific) relations is called decomposition • The objective is to have eachresulting relation be atomic • i.e. each relation should containonly information related to the key

  15. Boyce-Codd Normal Form (BCNF) • A given relation R with set F of functional dependencies is in BCNF if and only if all functional dependencies X  Y in F are: • either trivial (i.e. Y  X) • or X is a superkey of R • If relation R is not in BCNF, it is possible to use decomposition to transform R to BCNF

  16. Decomposition into BCNF • Given a set F of functional dependencies for relation R( A1, A2, ..., An ) that is not in BCNF: • Convert F to a minimal basis • Find an X  Y that violates BCNF • Compute closure X+ • Decompose R into: • R1 containing all attributes of X+ • R2 containing { A1, A2, ..., An } – ( X+ – X ) • Project functional dependencies onto R1 and R2 Repeat!

  17. Exercises (part two) • Given relation R( A, B, C, D, E ) andfunctional dependencies AB  AC,CE  D, B  A, and D  AE • What are the keys of R? • What are the superkeys of R that are not keys? • Is the given set of functional dependencies a basis? Is it a minimal basis? • Is relation R in BCNF? If not, decompose R such that it is in BCNF

More Related