- 127 Views
- Uploaded on
- Presentation posted in: General

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

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

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)

- Formal query languages
- rel algebra and calculi

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

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

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

motivation: ‘good’ tables

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

‘good’ or ‘bad’?

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

‘Bad’ - why?

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

- What caused the problem?

… ‘name’ depends on ‘ssn’

define ‘depends’

Definition:

‘a’ functionally determines ‘b’

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

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

‘X’, ‘Y’ can be sets of attributes

other examples??

ssn -> name, address

ssn, c-id -> grade

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

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

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?

“Armstrong’s axioms” guarantee soundness and completeness:

Reflexivity:

e.g., ssn, name -> ssn

Augmentation

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

Transitivity

ssn->address

address-> county-tax-rate

THEN:

ssn-> county-tax-rate

Reflexivity:

Augmentation:

Transitivity:

‘sound’ and ‘complete’

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

Additional rules:

Union

Decomposition

Pseudo-transitivity

Prove ‘Union’ from the three axioms:

Prove ‘Union’ from the three axioms:

Prove Pseudo-transitivity:

Prove Decomposition

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

ssn, c-id -> grade

ssn-> name, address

ssn-> ssn

ssn, c-id-> address

c-id, address-> c-id

...

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

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

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

ssn, c-id -> grade

ssn-> name, address

{ssn}+ ={ssn,

name, address }

}F

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

ssn, c-id -> grade

ssn-> name, address

{c-id}+ = ??

}F

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

ssn, c-id -> grade

ssn-> name, address

{c-id, ssn}+ = ??

}F

if A+ = {all attributes of table}

then ‘A’ is a candidate key

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

result := a;while (changes to result) dofor each in F do beginif result then result := result end

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?

C

A

B

Diagrams

AB->C (1)

A->BC (2)

B->C (3)

A->B (4)

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

ssn, c-id -> grade

ssn-> name, address

ssn,name-> name, address

ssn, c-id-> grade, name

Fc

F

why do we need it?

define it properly

compute it efficiently

why do we need it?

easier to compute candidate keys

define it properly

compute it efficiently

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

‘extraneous’ attribute:

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

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

ssn, c-id -> grade

ssn-> name, address

ssn,name-> name, address

ssn, c-id-> grade, name

F

Algorithm:

examine each FD; drop extraneous LHS or RHS attributes

merge FDs with same LHS

repeat until no change

Trace algo for

AB->C (1)

A->BC (2)

B->C (3)

A->B (4)

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)

AB->C (1)

A->BC (2)

B->C (3)

in (2): ‘C’ is extr.

AB->C (1)

A->B (2’)

B->C (3)

AB->C (1)

A->B (2’)

B->C (3)

in (1): ‘A’ is extr.

B->C (1’)

A->B (2’)

B->C (3)

B->C (1’)

A->B (2’)

B->C (3)

(1’) and (3) merge

A->B (2’)

B->C (3)

nothing is extraneous:

‘canonical cover’

BEFORE

AB->C (1)

A->BC (2)

B->C (3)

A->B (4)

AFTER

A->B (2’)

B->C (3)

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