Functional Dependencies(Cont.) Let R be a relation schema acR and BCR The functional dependency C→B holds on R if and only if for any legal relations r(R),whenever any two tuples f and t2 of r agree on the attributes a,they also agree on the attributes B.That is, tila]=t2[a]t[B]=t2[B] Example:Consider r(A,B )with the following instance of r. 1 4 1 5 3 7 On this instance,A->B does NOT hold,but B->A does hold. Database System Concepts-5th Edition,Oct 5,2006 7.12 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 7.12 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Functional Dependencies (Cont.) Let R be a relation schema R and R The functional dependency → holds on R if and only if for any legal relations r(R), whenever any two tuples t1 and t2 of r agree on the attributes , they also agree on the attributes . That is, t1 [] = t2 [] t1 [ ] = t2 [ ] Example: Consider r(A,B ) with the following instance of r. On this instance, A → B does NOT hold, but B → A does hold. 1 4 1 5 3 7
Functional Dependencies(Cont.) K is a superkey for relation schema R if and only if K->R K is a candidate key for R if and only if K→R,and for no ac K,→R Functional dependencies allow us to express constraints that cannot be expressed using superkeys.Consider the schema: bor loan =(customer id,loan number,amount ) We expect this functional dependency to hold: loan number-→amount but would not expect the following to hold: amount->customer name Database System Concepts-5th Edition,Oct 5,2006 7.13 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 7.13 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Functional Dependencies (Cont.) K is a superkey for relation schema R if and only if K → R K is a candidate key for R if and only if K → R, and for no K, → R Functional dependencies allow us to express constraints that cannot be expressed using superkeys. Consider the schema: bor_loan = (customer_id, loan_number, amount ). We expect this functional dependency to hold: loan_number → amount but would not expect the following to hold: amount → customer_name
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 F holds on R if all legal relations on R satisfy the set of functional dependencies F. Note:A specific instance of a relation schema may satisfy a functional dependency even if the functional dependency does not hold on all legal instances. For example,a specific instance of loan may,by chance,satisfy amount->customer name. Database System Concepts-5th Edition,Oct 5,2006 7.14 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 7.14 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 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 r satisfies F. specify constraints on the set of legal relations We say that F holds on R if all legal relations on R satisfy the set of functional dependencies F. Note: A specific instance of a relation schema may satisfy a functional dependency even if the functional dependency does not hold on all legal instances. For example, a specific instance of loan may, by chance, satisfy amount → customer_name
Functional Dependencies (Cont.) A functional dependency is trivial if it is satisfied by all instances of a relation Example: customer name,loan number->customer name customer name->customer name In general,a>Bis trivial if Ba Database System Concepts-5th Edition,Oct 5,2006 7.15 @Silberschatz,Korth and Sudarshan
Database System Concepts - 5 7.15 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Functional Dependencies (Cont.) A functional dependency is trivial if it is satisfied by all instances of a relation Example: customer_name, loan_number → customer_name customer_name → customer_name In general, → is trivial if
Closure of a Set of Functional Dependencies Given a set F of functional dependencies,there are certain other functional dependencies that are logically implied by F. For example: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+. F+is a superset of F. Database System Concepts-5th Edition,Oct 5,2006 7.16 @Silberschatz,Korth and Sudarshan
Database System Concepts - 5 7.16 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Closure of a Set of Functional Dependencies Given a set F of functional dependencies, there are certain other functional dependencies that are logically implied by F. For example: 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+. F+ is a superset of F