Database Systems {week 03a}

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)?