1 / 22

Database Systems {week 03a}

Rensselaer Polytechnic Institute CSCI-4380 – Database Systems David Goldschmidt, Ph.D. Database Systems {week 03a}. Functional dependencies (FDs). A functional dependency on relation R is a logical expression of the form X  Y X and Y are sets of attributes of R

gada
Download Presentation

Database Systems {week 03a}

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 03a}

  2. Functional dependencies (FDs) • A functional dependency on relation R is a logical expression of the form X  Y • X and Y are sets of attributes of R • i.e. X = { A1, A2, ..., An } and Y = { B1, B2, ..., Bm } where n = m or n <> m • X Y means that whenever any pair of tuplesin R have the same values for attributes in X, then they must also have the same values for attributes in Y

  3. Functional dependencies (FDs) • For each X Y defined on relation R,it means that X functionally determines Y • Or more specifically, the attributes of X functionally determine the attributes of Y • More generally, a functional dependency adds meaning to attributes of R • In some cases, the occurrence of duplicate tuples does not make semantic sense

  4. Rules about FDs • For a given relation R, we look at the set of all functional dependencies to tell us what tuples we can (and should) store • We can also reason by applying simple inference rules to the tuples • e.g. transitivity, splitting/combining, etc.

  5. Trivial functional dependencies • A constraint of any kind on relation R is said to be trivial if it holds for every instance of R • If Y  X, then X  Y is true for all relations • In other words, a trivial functional dependency has a right-hand side (Y) that is a subset of its left-hand side (X) • e.g. name artist  name • e.g. name  name Reflexivity rule What’s the point? We can remove trivial FDs!

  6. Trivial-dependency rule • The functional dependency X  Y is equivalent to X  Z where attributesof Z are all those attributes of Y thatare not also attributes of X • In other words, some of the attributes on the right-hand side (of X  Y) are also on the left (X) • We can simplify this by removing attributes from the right-hand side that also appear on the left

  7. Augmentation • Given functional dependency • X  Y • We can always add a set Z of attribute(s) • XZ  YZ • This is called augmentation

  8. Splitting • Given functional dependency X  Y as • A1, A2, ..., An  B1, B2, ..., Bm • We can split it into multiple functional dependencies (singletons) as follows: • A1, A2, ..., An  B1 • A1, A2, ..., An  B2 • ... • A1, A2, ..., An  Bm

  9. Combining • Given functional dependencies as follows: • A1, A2, ..., An  B1 • A1, A2, ..., An  B2 • ... • A1, A2, ..., An  Bm • We can combine attributes on the right-hand side to form functional dependency • A1, A2, ..., An  B1, B2, ..., Bm

  10. Transitivity • Given functional dependencies • X  Y and Y  Z • We can unequivocally conclude that • X  Z • And if some attributes of Z are also attributes of X, we can eliminate them from the right-hand side (trivial-dependency rule)

  11. Keys • For a given relation R, we look at the set of functional dependencies to identify which attribute(s) imply all the rest • These attribute(s) form a key on R • Set K = { A1, A2, ..., An } is a key on R if: • K functionally determine all other attributes of R • No proper subset of K functionally determinesall other attributes of R

  12. Uniqueness of keys • By definition, a key must be unique • A key K must functionally determine all other attributes of relation R • e.g. Student( id, name, address ) • The key is the id attribute

  13. Minimalityof keys • By definition, a key must be minimal • No proper subset of key K can functionally determine all other attributes of relation R • e.g. Student( id, name, address ) • Even though id and name together might be unique, the id attribute is minimal

  14. Superkeys • A set of attributes that contains a keyis called a superkey (a superset of a key) • The uniqueness constraint must be satisfied • The minimality constraint need not be satisfied • Every key is a superkey • e.g. Student( id, name, address ) • Attribute id is both a key and a superkey • Attributes (id, name) form a superkey

  15. Exercises (part one) • Model a US Census relation • Name, SSN, address, city, state, zip,area code, phone number, etc. • Use only a single relation • Describe functional dependencies • Identify keys and superkeys

  16. Inference • Given relation R with attributes A, B, C, D, E and A  BC, CD  E, BE  C • What does AE functionally determine (infer)? • In other words, AE  _____?

  17. Closure • 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

  18. Closure example • 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

  19. Closure of a set of attributes • 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

  20. Closure • A set F of functional dependencies implies a functional dependency X  Y if Y  X+ • In other words, if Y is in the closure of X, then functional dependency X  Y is true

  21. Keys revisited • 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}

  22. Exercises (part two) • Review the US Census relation • What other functional dependenciescan you infer? • Pick pairs of attributes (e.g. name andstate) and identify the resulting closure • In other words, what is the set of attributes X+ functionally determined by set X (the pair)?

More Related