Functional dependencies (FD) are are type of constraint that is based on keys. A superkey is defined as in the relational schema R , where:

a subset K of R is a subkey of R if, in any legal relation r(R), for all pairs, t₁ and t₂ in tuple r such that t₁ is not equal to t₂ then t₁[K]is not equal to t₂[K].

Or, no two rows (tuples) have the same value in the attribute(s) K, which is the key. Now, if there are two attributes (or sets of attributes) A and B that are legal in the relation schema R, we can have a functional dependency where

A implies B

for all pairs of tuples such that t₁[A]is equal to t₂[A] and t₁[B]is equal to t₂[B]. This allows us to state that K is a superkey of R if K implies R. For example, in a relation that has names and social security numbers, whenever your Social Security number is the student ID, the name in that tuple can only contain your name. That is because your name is not unique, but your Social Security is. If I go to the Social Security Administration and search their database for the name "Gary Burt", the results is a large number of people. If I search of the social security number "123-45-6789", the result is one and only one person.

Another example is in the loan information that we looked at before:

Loan-info-schema = (branch-name, loan-name, customer-name, amount)

it can be shown that the loan-number implies both the amount and the branch-name. It does not imply the customer-name because there may be more than one person listed on the load, such as a husband and wife, or parent and child (when the parent co-signs the loan).

Functional dependencies:

• specify a set of constraints on a legal relation.
• test relations to see if they are legal.

Some relations are said to be trivial when they are satisfied by all relations:.

• A implies A
• A implies B and B implies A.

Closure of a Set of Functional Dependencies

It is not enough to look at a single FD. All FDs must be considered in a relation! Given the schema R = ( A, B, C, G, H, I) and the FDs of:

A implies B
A implies C
CG implies H
CG implies I
B implies H

We can show that A implies H because A implies B which implies H.

The notional for a FD is F. The notation of F⁺ is the set of all FDs logically implied by F. There is a set of rules, called Armstrong's axioms, that we can use to to compute closure.

• Reflexivity rule: If A is a set of attributes, and B is a set of attributes that are completely contained in A, the A implies B.
• Augmentation rule: If A implies B, and C is a set of attributes, then if A implies B, then AC implies BC.
• Transitivity rule: If A implies B and B implies C, then A implies C.

These can be simplified if we also use:

• Union rule: If A implies B and A implies C, the A implies AC.
• Decomposition rule: If A implies BC then A implies B and A implies C.
• Pseudotransitivity rule: If A implies B and CB implies D, then AC implies D.

Using mathematical principles, we can not test a set of attributes to see if they are a legal superkey.

Pitfalls in Relational-Database Design

Obviously, we can have good and bad designs. Among the undesirable design items are:

• Repetition of information
• Inability to represent certain information

The relation lending with the schema is an example of a bad design:

Lending-Schema=(branch-name, branch-city, assets, cutomer-name, loan-number, amount)

Looking at the Downtown and Perryridge, when a new loan is added, the branch-city and assets must be repeated. That makes updating the table more difficult, because the update must guarantee that all tuples are updated. Additional problems come from having two people take out one loan (L-23). More complexity is involved when Jones took out a loan at a second branch (maybe one near home and the other near work.) Notice that there is no way to represent information on a branch unless there is a loan.

Decomposition

The obvious solution is that we should decompose this relation. As an alternative design, we can use the Decomposition rule: If A implies BC then A implies B and A implies C.

This gives us the schemas:

• branch-customer-schema = (branch-name, branch-city, assets, customer-name)
• customer-loan-schema = (customer-name, loan-number, amount)

Then when we need to get back to the original table, we can do a natural join on the two relations branch-customer and customer-loan.

Evaluating this design, how does it compare to the first version?

• Looking at the Downtown and Perryridge, when a new loan is added, the branch-city and assets must be repeated. Problem still exists.
• Problems come from having two people take out one loan (L-23). Problem still exists.
• More complexity is involved when Jones took out a loan at a second branch. Problem still exists.
• Notice that there is no way to represent information on a branch unless there is a loan. Problem still exists.

Worse, there is a new problem! When we do the natural join, we get back four additional tuples that did not exists in the original table:

• (Downtown, Brooklyn, 9000000, Jones, L-93, 500)
• (Perryridge, Horseneck, 1700000, Hayes, L-16, 1300)
• (Mianus, Horseneck, 400000, Jones, L-17, 1000)
• (North Town, Rye, 3700000, Hayes, L-15, 1500)

We are no long able to represent in the database information about which customers are borrows from which branch. This is called a lossy decomposition or lossy-join decomposition. A decomposition that is not a lossy-decomposition is a lossless-join decomposition. Lossless-joins are a requirement for good design and this causes constraints on the set of possible relations. We say that a relation is legal if it satisfies all rules, or constraints, imposed.

The proper way to decomposition this example so that we can have a lossless-join is to use three relations.

• branch-schema = (branch-name, assets, branch-city)
• loan-schema = (branch-name, loan-number, amount)
• borrower-schema = (customer-name, loan-number)

Normalization Using Functional Dependencies

Using FDs, it is possible to define several normal forms to help develop good database designs. The two that we will example are Boyce-Codd Normal Form (BCNF) and Third Normal Form (3NF). The requirements for good decomposition are

• Lossless-Join Decomposition
• Dependency Preservation
• Lack of Repetition of Information

We've discussed the lossless decomposition. Dependency preservation specifies that the design insure that when an update is made to the database, that it does not create an illegal relation. In regard to the repetition of information, it is necessary to include the key of another table, so that the joins can be properly formed. That is the only information that should be in both tables!

Boyce-Codd Normal Form

A relation schema R is said to be in BCNF with respect to a set F of FDs, if for all FDs in F⁺ of the form A implies B, where A is a subset of R and B is a subset of R and at least one of the following rules is true:

• A implies B is a trivial FD (B is a subset of A)
• A is a superkey for schema R

Without doing the mathematically proofs, it can be shown that the BCNF results in dependency preservation.

Third Normal Form

A relation schema R is in 3NF with respect to a set F of FDs if, for all FDs in the F⁺ of the form A implies B, where A is a subset of R and B is a subset of R and at least one of the following rules is true:

• A implies B is a trivial FD
• A is a superkey for schema R
• Each attribute in the result of the expression B-A is contained in a candidate key for R

---

CSEE | 461 | 461 S'99 | lectures | news | help