# Background for Database Normalization

Tom Kelliher, CS 318

Mar. 6, 2002

### From Last Time

Relationship between SQL, relational algebra, and calculi.

### Outline

1. The problem.

2. Decomposition.

3. Functional dependencies.

4. Properties of functional dependencies.

### Coming Up

Further properties of functional dependencies.

# The Problem

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

```CREATE TABLE Person (
SSN         INTEGER,
Name        CHAR(20),
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:

1. Suppose we want to insert John with no hobbies?

We have to come up with an effective ``No Hobby'' hobby.

2. Suppose we later want to add a hobby for John. Do we update the original tuple or add a new tuple?

Special case!

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

4. Suppose Jane no longer gardens.

Another special case!

These update anomalies are the result of undesirable redundancy in the DB.

## Decomposition

1. Consider the decomposition of Person into:
```Person(SSN, Name, Address)
Hobby(SSN, Hobby)
```
1. How does this solve our problems?

2. What redundancy remains? Does it matter?

2. Consider this decomposition of Person:
```SSN(SSN)
Name(Name)
Hobby(Hobby)
```
1. No redundancy, right?

2. What's wrong with this decomposition?

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

# Functional Dependencies

1. Functional dependency definition:
A constraint over a relation 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 .
Informally, we would say determines .

2. What type of constraint have we already see which is a functional dependency?

3. Example:

How are our update anomalies manifest here?

Any other dependencies in the first Person relation?

4. What are the functional dependencies in the Person/Hobby relations?

5. What are the functional dependencies inherent in this flawed E/R diagram?

6. Nomenclature:
1. Individual attributes: A, B, C.

2. Sets of attributes: ABC, , , .

# Properties of Functional Dependencies

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 FD f.
The closure of (written ) is the set of all FDs entailed by . Note .

Armstrong's Axioms:

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

2. Augmentation of FDs. If then for a relation instance containing .

Proof?

3. Transitivity of FDs. If and then .

Proof is a homework problem.

Thomas P. Kelliher
Tue Mar 5 12:24:00 EST 2002
Tom Kelliher