Functional Dependencies

Functional Dependencies

Reading and Exercises • Database Systems- The Complete Book: Chapter 3.1, 3.2, 3.3., 3.4 • Following lecture slides are modified from Jeff Ullman’s slides for Fall 2002 -- Stanford CSCE 520

Database Design • Goal: • Represent domain information • Avoid anomalies • Avoid redundancy • Anomalies: • Update: not all occurrences of a fact are changed • Deletion: valid fact is lost when tuple is deleted CSCE 520

Functional Dependencies • FD: X  A for relation R X functional determines A, i.e., if any two tuples in R agree on attributes X, they must also agree on attribute A. • X: set of attributes • A: single attribute • If t1 and t2 are two tuples of r over R and t1[X]= t2[X] then t1[A]= t2[A] What is the relation between functional dependencies and primary keys? CSCE 520

Functional Dependency Example • Owner(Name, Phone) • FD: Name  Phone • Dog(Name, Breed, Age, Weight) • FD: Name, Breed  Age • FD: Name, Breed  Weight CSCE 520

Example - FD Dog-Kennels(Name,Breed,Age,Weight,Date,Kennel) Functional Dependencies: Name,Breed  Age Name,Breed  Weight CSCE 520

FD with Multiple Attributes • Right side: can be more than 1 attribute – splitting/combining rule • E.g., FD: Name, Breed  Age FD: Name, Breed  Weight combine into: FD: Name, Breed  Age,Weight • Left side cannot be decomposed! CSCE 520

FD Equivalence Let S and T denote two sets of FDs. • S and T are equivalent if the set of relation instances satisfying S is exactly the same as the set of instances satisfying T. • A set of FDs S follows from a set of FDs T if every relation instance that satisfies all FDs in T also satisfies all FDs in S. • Two sets of FDs S and T are equivalent if S follows from T and T follows from S. CSCE 520

Trivial FD Given FD of the form A1,A2,…,AnB1,B2,…,Bk FD is • Trivial: if the Bs are subset of As • Nontrivial: if at least one of the Bs is not among As • Completely nontrivial: if none of the Bs is in As. CSCE 520

Keys and FD • K is a (primary) key for a relation R if • K functionally determines all attributes in R • 1 does not hold for any proper subset of K • Superkey: 1 holds, 2 does not hold CSCE 520

Example • Dog-Kennels(Name,Breed,Age,Weight,Date,Kennel) • Name,Breed,Date is a key: • K={Name,Breed,Date} functionally determines all other attributes • The above does not hold for any proper subset of K • What are? • {Name,Breed,Kennel} • {Name,Breed,Date,Kennel} • {Name,Breed,Age} • {Name,Breed,Age,Date} CSCE 520

Where do Keys Come From? • Assert a key K, then only FDs are K  A for all attributes A (K is the only key from FDs) • Assert FDs and deduce the keys E/R gives FDs from entity set keys and many-one relationships CSCE 520

E/R and Relational Keys • E/R keys: properties of entities • Relation keys: properties of tuples • Usually: one tuple corresponds to one entity • Poor relational design: one entity becomes several tuples CSCE 520

Closure of Attributes • Let A1,A2,…,An be a set of attributes and S a set of FDs. The closure of A1,A2,…,An under S is the set of attributes B such that every relation that satisfies S also satisfies A1,A2,…,An  B. • Closure of attributes A1,A2,…,An is denoted as {A1,A2,…,An}+ CSCE 520

Algorithm – Attribute Closure • Let X = A1,A2,…,An • Find B1,B2,…,Bk  C such that B1,B2,…,Bk all in X but C is not in X • Add C to X • Repeat until no more attribute can be added to X • X= {A1,A2,…,An}+ CSCE 520

Closures and Keys • {A1,A2,…,An}+ is a set of all attributes of a relation if and only if A1,A2,…,An is a superkey for the relation. CSCE 520

Projecting FDs • Some FD are physical laws • E.g., no two courses can meet in the same room at the same time • A professor cannot be at two places at the same time. • How to determine what FDs hold on a projection of a relation? CSCE 520

FD on Relation • Relation schema design: which FDs hold on relation • Given: X1 A1, X2  A2, …, Xn  An whether Y  B must hold on relations satisfying X1 A1, X2  A2, …, Xn  An • Example: A  B and B  C, then A C must also hold CSCE 520

Inference Test • Test whether Y  B • Assume two tuples agree on attributes Y • Use FDs to infer these tuples also agree on other attributes • If B is one of the “other” attributes, then Y  B holds. CSCE 520

Armstrong Axioms • Reflexivity • If {A1,A2,…,Am} superset of {B1,B2,…,Bn} then A1,A2,…,Am  B1,B2,…,Bn • Augmentation • If A1,A2,…,Am  B1,B2,…,Bn then A1,A2,…,Am,C1,…,Ck  B1,B2,…,Bn,C1,…,Ck • Transitivity • If A1,A2,…,Am  B1,B2,…,Bn and B1,B2,…,Bn C1,C2,…,Ck then A1,A2,…,Am  C1,C2,…,Ck CSCE 520

FD Closure • Compute the closure of Y, denoted as Y+ • Basis: Y+ = Y • Induction: look FD, where left side X is subset of Y+ . If FD is X  A then add A to Y+ . CSCE 520

Finding All FDs • Normalization: break a relation schema into two or more schemas • Example: • R(A,B,C,D) • FD: AB C, C D, D  A • Decompose into (A,B,C), (A,D) • FDs of (ABC): A,B C and C  A CSCE 520

Basic Idea • What FDs hold on a projections: • Start with FDs • Find all FDs that follow from given ones • Restrict to FDs that involve only attributes from a schema CSCE 520

Algorithm • For each X compute X+ • Add X  A for all A in X+ - X • Drop XY  A if X  A • Use FD of projected attributes CSCE 520

Tricks • Do not compute closure of empty set of the set of all attributes • If X+ = all attributes, do not compute closure of the superset of X CSCE 520

Example • ABC with A  B, B  C and projection on AC: • A+ = ABC, yields A  B and A  C • B+ = BC, yields B  C • C+ = C, yields nothing • BC+ = BC, yields nothing • Resulting FDs: AB, AC, BC • Projection AC: A  C CSCE 520

Functional Dependencies