Basic Concepts

1. Functional dependencies.
   • Functional dependencies are a constraint on the set of legal relations in a database.
   • They allow us to express facts about the real world we are modeling.

   • The notion generalizes the idea of a superkey.
   • Let and .

   • Then the functional dependency holds on R if in any legal relation r(R), for all pairs of tuples and in r such that , it is also the case that .

   • Using this notation, we say K is a superkey of R if .
   • In other words, K is a superkey of R if, whenever , then (and thus ).
2. Functional dependencies allow us to express constraints that cannot be expressed with superkeys.
3. Consider the scheme

    Loan-info-schema = (bname, loan#, cname, amount) 
   
   

   if a loan may be made jointly to several people (e.g. husband and wife) then we would not expect loan# to be a superkey. That is, there is no such dependency

    loan#    cname
   
   

   We do however expect the functional dependency

    loan#    amount 
   
   		 loan#    bname
   
   

   to hold, as a loan number can only be associated with one amount and one branch.

4. A set F of functional dependencies can be used in two ways:
   • To specify constraints on the set of legal relations. (Does F hold on R?)
   • To test relations to see if they are legal under a given set of functional dependencies. (Does r satisfy F?)
5. Figure 6.2 shows a relation r that we can examine.

    
   Figure 6.2:   Sample relation r.
   

6. We can see that is satisfied (in this particular relation), but is not. is also satisfied.
7. Functional dependencies are called trivial if they are satisfied by all relations.
8. In general, a functional dependency is trivial if .
9. In the customer relation of figure 5.4, we see that is satisfied by this relation. However, as in the real world two cities can have streets with the same names (e.g. Main, Broadway, etc.), we would not include this functional dependency in our list meant to hold on Customer-scheme.
10. The list of functional dependencies for the example database is:
    • On Branch-scheme:

       bname    bcity 
      
      		 bname    assets 
      
      

    • On Customer-scheme:

       cname    ccity 
      
      		 cname    street 
      
      

    • On Loan-scheme:

       loan#    amount 
      
      		 loan#    bname 
      
      

    • On Account-scheme:

       account#    balance 
      
      		 account#    bname 
      
      

      There are no functional dependencies for Borrower-schema, nor for Depositor-schema.