Background for Database Normalization

Tom Kelliher, CS 318

Mar. 6, 2002

Administrivia

Announcements

Assignment

Read 8.1--4.

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),
   Address     CHAR(50),
   Hobby       CHAR(20),
   PRIMARY KEY (SSN, Hobby));

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!

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)
   Address(Address)
   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.

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.