Loading in 5 sec....

Temple University – CIS Dept. CIS331– Principles of Database SystemsPowerPoint Presentation

Temple University – CIS Dept. CIS331– Principles of Database Systems

- 178 Views
- Uploaded on

Download Presentation
## PowerPoint Slideshow about 'Temple University CIS Dept. CIS331 Principles of Database Systems' - chelsi

**An Image/Link below is provided (as is) to download presentation**

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

### Temple University – CIS Dept.CIS331– Principles of Database Systems

V. Megalooikonomou

Functional Dependencies

(based on notes by Silberchatz,Korth, and Sudarshan and notes by C. Faloutsos at CMU)

General Overview

- Formal query languages
- rel algebra and calculi

- Commercial query languages
- SQL
- QBE, (QUEL)

- Integrity constraints
- Functional Dependencies
- Normalization - ‘good’ DB design

Overview

- Domain; Ref. Integrity constraints
- Assertions and Triggers
- Security
- Functional dependencies
- why
- definition
- Armstrong’s “axioms”
- closure and cover

Functional dependencies

motivation: ‘good’ tables

takes1 (ssn, c-id, grade, name, address)

‘good’ or ‘bad’?

Functional dependencies

takes1 (ssn, c-id, grade, name, address)

Functional dependencies

‘Bad’ - why?

Functional Dependencies

- Redundancy
- space
- inconsistencies
- insertion/deletion anomalies (later…)

- What caused the problem?

Functional dependencies

Informally: ‘if you know ‘a’, there is only one ‘b’ to match’

Functional dependencies

formally:

if two tuples agree on the ‘X’ attribute,

they *must* agree on the ‘Y’ attribute, too

(e.g., if ssn is the same, so should address)

… a functional dependency is a generalization of the notion of a key

Functional dependencies

K is a superkey for relation R iff K -> R

K is a candidate key for relation R iff:

K -> R

for no a K, a -> R

Functional dependencies

Closure of a set of FD: all implied FDs – e.g.:

ssn -> name, address

ssn, c-id -> grade

imply

ssn, c-id -> grade, name, address

ssn, c-id -> ssn

FDs - Armstrong’s axioms

Closure of a set of FD: all implied FDs – e.g.:

ssn -> name, address

ssn, c-id -> grade

how to find all the implied ones, systematically?

FDs - Armstrong’s axioms

“Armstrong’s axioms” guarantee soundness and completeness:

Reflexivity:

e.g., ssn, name -> ssn

Augmentation

e.g., ssn->name then ssn,grade-> ssn,grade

FDs - Armstrong’s axioms

Transitivity

ssn->address

address-> county-tax-rate

THEN:

ssn-> county-tax-rate

FDs – finding the closure F+

F+ = F

repeatfor each functional dependency f in F+

apply reflexivity and augmentation rules on fadd the resulting functional dependencies to F+for each pair of functional dependencies f1and f2 in F+iff1 and f2 can be combined using transitivitythen add the resulting functional dependency to F+

until F+ does not change any further

We can further simplify manual computation of F+ by using the following additional rules

FDs - Armstrong’s axioms

Prove ‘Union’ from the three axioms:

FDs - Armstrong’s axioms

Prove ‘Union’ from the three axioms:

FDs - Armstrong’s axioms

Prove Pseudo-transitivity:

FDs - Armstrong’s axioms

Prove Decomposition

FDs - Closure F+

Given a set F of FD (on a schema)

F+ is the set of all implied FD.

E.g.,

takes(ssn, c-id, grade, name, address)

ssn, c-id -> grade

ssn-> name, address

}F

FDs - Closure F+

ssn, c-id -> grade

ssn-> name, address

ssn-> ssn

ssn, c-id-> address

c-id, address-> c-id

...

F+

FDs - Closure F+

R=(A,B,C,G,H,I)

F= { A->B

A->C

CG->H

CG->I

B->H}

Some members of F+:

A->H

AG->I

CG->HI

FDs - Closure A+

Given a set F of FD (on a schema)

A+ is the set of all attributes determined by A:

takes(ssn, c-id, grade, name, address)

ssn, c-id -> grade

ssn-> name, address

{ssn}+ =??

}F

FDs - Closure A+

takes(ssn, c-id, grade, name, address)

ssn, c-id -> grade

ssn-> name, address

{ssn}+ ={ssn,

name, address }

}F

FDs - Closure A+

takes(ssn, c-id, grade, name, address)

ssn, c-id -> grade

ssn-> name, address

{c-id}+ = ??

}F

FDs - Closure A+

takes(ssn, c-id, grade, name, address)

ssn, c-id -> grade

ssn-> name, address

{c-id, ssn}+ = ??

}F

FDs - Closure A+

Algorithm to compute a+, the closure of a under F

result := a;while (changes to result) do for each in F do begin if result then result := result end

FDs - Closure A+ (example)

R = (A, B, C, G, H, I)

F = {A B, A C, CG H, CG I, B H}

(AG)+

1. result = AG

2. result = ABCG (A C and A B)

3. result = ABCGH (CG H and CG AGBC)

4. result = ABCGHI (CG I and CG AGBCH)

Is AG a candidate key?

Is AG a super key?

Does AG R?

Is any subset of AG a superkey?

Does A+R?

Does G+R?

FDs - ‘canonical cover’ Fc

Given a set F of FD (on a schema)

Fc is a minimal set of equivalent FD. E.g.,

takes(ssn, c-id, grade, name, address)

ssn, c-id -> grade

ssn-> name, address

ssn,name-> name, address

ssn, c-id-> grade, name

F

FDs - ‘canonical cover’ Fc

ssn, c-id -> grade

ssn-> name, address

ssn,name-> name, address

ssn, c-id-> grade, name

Fc

F

FDs - ‘canonical cover’ Fc

why do we need it?

easier to compute candidate keys

define it properly

compute it efficiently

FDs - ‘canonical cover’ Fc

define it properly - three properties

every FD a->b has no extraneous attributes on the RHS

same for the LHS

all LHS parts are unique

if the closure is the same, before and after its elimination

or if F-before implies F-after and vice-versa

FDs - ‘canonical cover’ FcFDs - ‘canonical cover’ Fc

ssn, c-id -> grade

ssn-> name, address

ssn,name-> name, address

ssn, c-id-> grade, name

F

FDs - ‘canonical cover’ Fc

Algorithm:

examine each FD; drop extraneous LHS or RHS attributes

merge FDs with same LHS

repeat until no change

FDs - ‘canonical cover’ Fc

Trace algo for

AB->C (1)

A->BC (2)

B->C (3)

A->B (4)

(4) and (2) merge:

AB->C (1)

A->BC (2)

B->C (3)

FDs - ‘canonical cover’ Fc

AB->C (1)

A->BC (2)

B->C (3)

in (2): ‘C’ is extr.

AB->C (1)

A->B (2’)

B->C (3)

FDs - ‘canonical cover’ Fc

AB->C (1)

A->B (2’)

B->C (3)

in (1): ‘A’ is extr.

B->C (1’)

A->B (2’)

B->C (3)

FDs - ‘canonical cover’ Fc

B->C (1’)

A->B (2’)

B->C (3)

(1’) and (3) merge

A->B (2’)

B->C (3)

nothing is extraneous:

‘canonical cover’

Overview - conclusions

- Domain; Ref. Integrity constraints
- Assertions and Triggers
- Functional dependencies
- why
- definition
- Armstrong’s “axioms”
- closure and cover

Download Presentation

Connecting to Server..