**Tom Kelliher, CS 318**

**Mar. 6, 2002**

Read 8.1--4.

Relationship between SQL, relational algebra, and calculi.

- The problem.
- Decomposition.
- Functional dependencies.
- Properties of functional dependencies.

Further properties of functional dependencies.

Consider the following E/R diagram, schema, and relation instance:

CREATE TABLE Person ( SSN INTEGER, Name CHAR(20), Address CHAR(50), Hobby CHAR(20), PRIMARY KEY (SSN, Hobby));Note that neither SSN nor Hobby may be NULL, since they're part of a primary key.

Questions:

- Suppose we want to insert John with no hobbies?
We have to come up with an effective ``No Hobby'' hobby.

- Suppose we later want to add a hobby for John. Do we update the
original tuple or add a new tuple?
Special case!

- Consider how much work must be done if Evil Tom becomes Good Tom.
This is counter-intuitive, making three DB changes to reflect one real change.

- Suppose Jane no longer gardens.
Another special case!

- Consider the decomposition of Person into:
Person(SSN, Name, Address) Hobby(SSN, Hobby)

- How does this solve our problems?
- What redundancy remains? Does it matter?

- How does this solve our problems?
- Consider this decomposition of Person:
SSN(SSN) Name(Name) Address(Address) Hobby(Hobby)

- No redundancy, right?
- What's wrong with this decomposition?

- No redundancy, right?
- Key idea --- E/R design may result in redundancy. We need a set of
tools to find an eliminate redundancy.

- Functional dependency definition:
A constraint over a relation

Informally, we would say determines .**R**of the form where and are sets of attributes of . If is an instance of , it is said to satisfy this functional dependency if:For every pair of tuples

**s**and**t**in , if**s**and**t**agree on then**s**and**t**agree on . - What type of constraint have we already see which is a functional
dependency?
- Example:
How are our update anomalies manifest here?

Any other dependencies in the first Person relation?

- What are the functional dependencies in the Person/Hobby relations?
- What are the functional dependencies inherent in this
*flawed*E/R diagram? - Nomenclature:
- Individual attributes:
**A**,**B**,**C**. - Sets of attributes:
**ABC**, , , .

- Individual attributes:

Entailment: Consider all relation instances over attributes
. We say the set of FDs entails the FD **f** if the
following property is true:

If satisfies every FD in , then satisfies FDThe closure of (written ) is the set of all FDs entailed by . Note .f.

* Armstrong's Axioms*:

- Reflexivity of FDs. If then
.
Proof. Consider a relation instance over attributes . If agree on , then they agree on . Hence, .

Such trivial FDs are entailed by any set of FDs.

- Augmentation of FDs. If then
for a relation instance
containing .
Proof?

- Transitivity of FDs. If and
then .
Proof is a homework problem.

Tue Mar 5 12:24:00 EST 2002