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 α∈ R and β∈ R.
- Then the functional dependency → holds on R if in any legal relation r(R), for all pairs of tuples
t1 and t2 in r such that t1[< α] = t2[< α], it is also the case that t1[< β] = t2[< β].
| A | B | C | D |
|
a1 a1 a2 a2 a3
|
b1 b2 b2 b3 b3
|
c1 c1 c2 c2 c2
|
d1 d2 d2 d3 d4
|
Figure 6.2: Sample relation r.
- Using this notation, we say K is a superkey of R if K → R.
- In other words, K is a superkey of R if, whenever t1[K] = t2[K], then t1[R] = t2[R] (and thus t1 = t2).
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.
6. We can see that A → C is satisfied (in this particular relation), but C → A is not. AB → C is also satis ed.
7. Functional dependencies are called trivial if they are satis ed by all relations.
8. In general, a functional dependency α→ β is trivial if β∈α .
9. In the customer relation of gure 5.4, we see that street → ccity is satis ed 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.