Next: Closure of a Set Up: Functional Dependencies Previous: Functional Dependencies

## 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.

Next: Closure of a Set Up: Functional Dependencies Previous: Functional Dependencies

Osmar Zaiane
Tue Jun 9 15:12:55 PDT 1998