CS457 Syllabus & Progress

### Intro to Inference Rules

• Introduction

• Pre-requisite to decomposing a relation:

 In order to decompose a relation R, we must know whether a dependency a is "good" dependency or a "bad" dependency

• How to tell if a dependency is good or bad:

 A dependencies caused by a key is a "natural" functional dependency and it is a "good" dependency

Therefore:

• In order to determine whether a dependency is "good" depedency or "bad" dependency:

 We must first find all the keys of a relation

• Theory/technique on finding keys in a relation

• The pre-requisite to performing decomposition on a relation R is:

 Finding all the keys of the relation R

• The theory/technique on how to find all the keys of a relation is based on:

 Inference rules

We will first study inference rules now....

• Inference Rules

• Inference rule:

 Inference rule = an assertion that we can apply to a set of functional dependencies to derive another functional dependencies

• The basic inference rules: Armstrongs's Axioms

• The basic inference rules used in database theory:

• Let X, Y and Z be three sets of attributes in the (same) relation R.

The basic inference rules are:

1. Reflexivity rule:

 If   Y ⊆ X   then:   X → Y

2. Augmentation rule:

 If   X → Y   then:   XZ → YZ

3. Transitivity rule:

 If   X → Y and Y → Z   then:   X → Z

• Note:

• The 3 basic inference rules are called:

 The Armstrong's axioms

• Examples of the Armstrong's axioms

• Fact:

 The Armstrong's axioms are very intuitive (and can be proved to be true)

I will first use a example to illustrate their meaing

• Consider the relation:

 Employee-Department SSN fname lname DNO DName +-------------+----------+----------+----------+----------+ | 111-11-1111 | John | Smith | 5 | Research | +-------------+----------+----------+----------+----------+ | 222-22-2222 | Jane | Doe | 4 | Payroll | +-------------+----------+----------+----------+----------+ | 333-33-3333 | Pete | Pan | 5 | Research | +-------------+----------+----------+----------+----------+

• Examples of Armostrong axioms:

1. Reflexivity rule:

 If   Y ⊆ X   then:   X → Y

Example:

 Since { fname } ⊆ { fname, lname } then: {fname, lname} → {fname}

What this rule says is:

• If I have that same value for the attributes fname and for lname in a tuple then:

 I must have the same value for the attribute fname in that tuple

Comment: this is obvious....

2. Augmentation rule:

 If   X → Y   then:   XZ → YZ

Example:

 Since {SSN} → {fname} then: {SSN, DName} → {fname, DName}

The augmentation rule says the following:

• Since I know that SSNfname:

 SSN fname lname DNO DName +-------------+----------+----------+----------+----------+ | 111-11-1111 | John | Smith | 5 | Research | +-------------+----------+----------+----------+----------+ | 222-22-2222 | Jane | Doe | 4 | Payroll | +-------------+----------+----------+----------+----------+ | 333-33-3333 | Pete | Pan | 5 | Research | +-------------+----------+----------+----------+----------+

Example: the SSN=111-11-1111 is always associated with fname='John' in a tuple

Then the following will always be true:

 {SSN, DName} → {fname, DName}

Because the SSN=111-11-1111 and DName='Research' will always have fname='John' and DName='Research' in that tuple !!!

Evidence:

 SSN fname lname DNO DName +-------------+----------+----------+----------+----------+ | 111-11-1111 | John | Smith | 5 | Research | +-------------+----------+----------+----------+----------+ | 222-22-2222 | Jane | Doe | 4 | Payroll | +-------------+----------+----------+----------+----------+ | 333-33-3333 | Pete | Pan | 5 | Research | +-------------+----------+----------+----------+----------+

3. Transitivity rule:

 If   X → Y and Y → Z   then:   X → Z

Example:

 Since: {SSN} → {DNO} and {DNO} → {DName} Then also: {SSN} → {DName}

The transitivity rule says the following:

 If SSN=111-11-1111 in a tuple, then DNO=5 in that tuple: SSN fname lname DNO DName +-------------+----------+----------+----------+----------+ | 111-11-1111 | John | Smith | 5 | Research | +-------------+----------+----------+----------+----------+ | 222-22-2222 | Jane | Doe | 4 | Payroll | +-------------+----------+----------+----------+----------+ | 333-33-3333 | Pete | Pan | 5 | Research | +-------------+----------+----------+----------+----------+ Futhermore, when DNO=5 in a tuple, then DName=Research in that tuple: SSN fname lname DNO DName +-------------+----------+----------+----------+----------+ | 111-11-1111 | John | Smith | 5 | Research | +-------------+----------+----------+----------+----------+ | 222-22-2222 | Jane | Doe | 4 | Payroll | +-------------+----------+----------+----------+----------+ | 333-33-3333 | Pete | Pan | 5 | Research | +-------------+----------+----------+----------+----------+ Therefore: If SSN=111-11-1111 in a tuple, then DName=Research in that tuple