1 / 36

Foundations of Database Systems

Foundations of Database Systems. Functional Dependency Instructor: Zhijun Wang. DBMS and Applications. 1. Announcement. Lab #2 will be given next week Group 1: M409 Saturday 13:00-16:00 (March 23) Group 2:M402 Wednesday 18:30-21:30 (March 27)

guinevere-castro
Download Presentation

Foundations of Database Systems

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. Foundations of Database Systems Functional Dependency Instructor: Zhijun Wang DBMS and Applications 1

  2. Announcement Lab #2 will be given next week Group 1: M409 Saturday 13:00-16:00 (March 23) Group 2:M402 Wednesday 18:30-21:30 (March 27) Assignment 2 is available online DBMS and Applications 2

  3. The Relational Model Broad, flexible model Basis for almost all DBMS products E.F. Codd defined well-structured “normal forms” of relations, “normalization” DBMS and Applications 3

  4. Components of the Relational Model Relation A two-dimensional table consisting of rows and columns Tuples The rows (or records) in a relation Attributes The columns (or fields) in a relation DBMS and Applications 4

  5. Normalization Normalization is a process of evaluating and converting a relation to reduce modification anomalies Essentially, normalization detects and eliminates data redundancy DBMS and Applications 5

  6. An Anomaly An anomaly is an undesirable consequence of a data modification. Modification anomaly DBMS and Applications 6

  7. Modification Anomalies Unexpected side effect Insert, modify, and delete more data than desired Caused by excessive redundancies Strive for one fact in one place DBMS and Applications 7

  8. Functional Dependency A functional dependency occurs when the value of one (a set of) attribute(s) determines the value of a second (set of) attribute(s): StudentID  StudentName StudentID  (DormName, DormRoom, Fee) The attribute on the left side of the functional dependency is called the determinant Functional dependencies may be based on equations: ExtendedPrice = Quantity X UnitPrice (Quantity, UnitPrice)  ExtendedPrice Function dependencies are not equations! DBMS and Applications 8

  9. Functional Dependencies Are Not Equations ObjectColor  Weight ObjectColor  Shape ObjectColor  (Weight, Shape) DBMS and Applications 9

  10. Composite Determinants Composite determinant (key): A determinant of a functional dependency that consists of more than one attribute (StudentName, ClassName)  (Grade) DBMS and Applications 10

  11. Functional Dependency Rules If A  (B, C), then A  B and A C If (A,B)  C, then neither A nor B determines C by itself DBMS and Applications 11

  12. Functional Dependencies in the SKU_DATA Table SKU  (SKU_Description, Department, Buyer) SKU_Description  (SKU, Department, Buyer) Buyer  Department DBMS and Applications 12

  13. Functional Dependencies in the ORDER_ITEM Table (OrderNumber, SKU)  (Quantity, Price, ExtendedPrice) (Quantity, Price)  (ExtendedPrice) DBMS and Applications 13

  14. What Makes Determinant Values Unique? A determinant is unique in a relation if, and only if, it determines every other column in the relation You cannot find the determinants of all functional dependencies simply by looking for unique values in one column: Data set limitations Must be logically a determinant DBMS and Applications 14

  15. Keys A key is a combination of one or more columns that is used to identify rows in a relation A composite key is a key that consists of two or more columns DBMS and Applications 15

  16. Candidate and Primary Keys A candidate key is a key that determines all of the other columns in a relation A primary key is a candidate key selected as the primary means of identifying rows in a relation: There is one and only one primary key per relation The primary key may be a composite key The ideal primary key is short, numeric and never changes DBMS and Applications 16

  17. Surrogate Keys A surrogate key as an artificial column added to a relation to serve as a primary key: DBMS supplied Short, numeric and never changes – an ideal primary key! Has artificial values that are meaningless to users Normally hidden in forms and reports DBMS and Applications 17

  18. Surrogate Keys NOTE: The primary key of the relation is underlined below: RENTAL_PROPERTY without surrogate key: RENTAL_PROPERTY (Street, City,State/Province, Zip/PostalCode, Country, Rental_Rate) RENTAL_PROPERTY with surrogate key: RENTAL_PROPERTY (PropertyID, Street, City, State/Province, Zip/PostalCode, Country, Rental_Rate) DBMS and Applications 18

  19. Use of Functional Dependencies We use functional dependencies to: test relations to see if they are legal under a given set of functional dependencies. If a relation r is legal under a set F of functional dependencies, we say that rsatisfies F. specify constraints on the set of legal relations We say that Fholds onR if all legal relations on R satisfy the set of functional dependencies F. DBMS and Applications 19

  20. Functional Dependencies A functional dependency is trivial if it is satisfied by all instances of a relation E.g. customer-name, loan-number customer-name customer-name customer-name In general,   is trivial if    DBMS and Applications 20

  21. Closure of a Set of Functional Dependencies Given a set F, set of functional dependencies, there are certain other functional dependencies that are logically implied by F. E.g. If A  B and B  C then we can infer that A  C The set of all functional dependencies logically implied by F is the closure of F. We denote the closure of F by F+. We can find all ofF+by applying Armstrong’s Axioms: if   , then   (reflexivity) if  , then    (augmentation) if  , and   , then   (transitivity) DBMS and Applications 21

  22. Example R = (A, B, C, G, H, I)F = { A B, A C , CG H, CG I, B H} some members of F+ A H by transitivity from A B and B H AG I by augmenting A C with G, to get AG CG and then transitivity with CG I CG HI from CG H and CG I : “union rule” can be inferred from definition of functional dependencies, or Augmentation of CG I to infer CG  CGI, augmentation ofCG H to inferCGI HI, and then transitivity DBMS and Applications 22

  23. Procedure for Computing F+ F+ = Frepeatfor each functional dependency f in F+ apply reflexivity and augmentation rules on fadd the resulting functional dependencies to F+for each pair of functional dependencies f1and f2 in F+iff1 and f2 can be combined using transitivitythen add the resulting functional dependency to F+until F+ does not change any further DBMS and Applications 23

  24. Closure of Functional Dependencies Additional rules. If   holdsand  holds, then    holds (union) If    holds, then   holds and  holds (decomposition) If   holdsand   holds, then   holds (pseudotransitivity) The above rules can be inferred from Armstrong’s axioms. DBMS and Applications 24

  25. Example of Computing F+ Compute the closure of the following set F of functional dependencies for relation schema R = {A, B, C, D, E}. Function dependencies are: {A -> BC, CD -> E, B -> D, E -> A} DBMS and Applications 25

  26. Example of Computing F+ A -> BC, B -> D so A -> D,and A -> DC -> E therefore A -> ABCDE E -> A, A -> ABCDE, so E -> ABCDE CD -> E, so CD -> ABCDE B -> D, BC -> CD, so BC -> ABCD CD -> E, so BC -> ABCDE DBMS and Applications 26

  27. Example of Computing F+ So the F+ closure is {A -> ABCDE, B -> BD, C -> C, D -> D, E -> ABCDE, AB -> ABCDE, AC -> ABCDE, AD -> ABCDE, AE -> ABCDE, BC -> ABCDE, BD -> BD, BE -> ABCDE, CD -> ABCDE, CE -> ABCDE, DE -> ABCDE, ABC -> ABCDE, ABD -> ABCDE, ABE -> ABCDE, ACD -> ABCDE, ACE -> ABCDE, ADE -> ABCDE, BCD -> ABCDE, BDE -> ABCDE, CDE -> ABCDE, ABCD -> ABCDE, ABCE -> ABCDE, ABDE -> ABCDE, ACDE -> ABCDE, BCDE -> ABCDE , ABCDE -> ABCDE} DBMS and Applications 27

  28. Canonical Cover Sets of functional dependencies may have redundant dependencies that can be inferred from the others Eg: A  C is redundant in: {A  B, B  C, A  C} Parts of a functional dependency may be redundant E.g. on RHS: {A  B, B  C, A  CD} can be simplified to {A  B, B  C, A  D} E.g. on LHS: {A  B, B  C, AC  D} can be simplified to {A  B, B  C, A  D} Intuitively, a canonical cover of F is a “minimal” set of functional dependencies equivalent to F, with no redundant dependencies or having redundant parts of dependencies DBMS and Applications 28

  29. Canonical Cover A canonical coverfor F is a set of dependencies Fc such that F logically implies all dependencies in Fc, and Fclogically implies all dependencies in F, and No functional dependency in Fc contains an extraneous attribute, and Each left side of functional dependency in Fcis unique. DBMS and Applications 29

  30. Canonical Cover To compute a canonical cover for F:repeatUse the union rule to replace any dependencies in F11 and 12 with 112 Find a functional dependency  with an extraneous attribute either in  or in  If an extraneous attribute is found, delete it from until F does not change DBMS and Applications 30

  31. Example of Computing a Canonical Cover R = (A, B, C)F = {A BC, B C, A B, ABC} Combine A BC and A B into A BC Set is now {A BC, B C, ABC} A is extraneous in ABC because BC logically implies AB C. Set is now {A BC, B C} C is extraneous in ABC since A BC is logically implied by A B and B  C. The canonical cover is: A B B C DBMS and Applications 31

  32. Example in class-I R = (A, B, C,D, E,F,G)F = {A CG, D  EG, BC D, CG BD, ACD B,CEAG} Lists all candidate keys. From AC G and CG BD, We get AC  CG BD, then AC  CG  B, so ACD B, can be simplified asACB, Set is now {A CG, D  EG, BC D, CG BD, AC B,CEAG} Combine ACG and ACB to ACGB. Set is now {A CBG, D  EG, BC D, CG BD, CEAG} DBMS and Applications 32

  33. Example in class-II From ACB,BCD and DEG. We have ACBCD EG, then ACG, so Set is now {A CB, D  EG, BC D, CG BD, CEAG} From CGD,DEG,CEAG, and ACB We get CG  CD CE AC B, then CG  B, Set is now {A CB, D  EG, BC D, CG D, CEAG} DBMS and Applications 33

  34. Example in class-II From CEAG, ACB,BCD, and D EG We have CEACBCDG, then CEG, so Set is now {A CB, D  EG, BC D, CG D, CEA} The canonical cover is: {A CB, D  EG, BC D, CG D, CEA} DBMS and Applications 34

  35. Example in class-II The canonical cover is: {A CB, D  EG, BC D, CG D, CEA} The candidate keys: C must be in the keys. (A, C), (B,C), (D, C), (C, E), (C,G) DBMS and Applications 35

  36. Question?? DBMS and Applications 36

More Related