temple university cis dept cis331 principles of database systems l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Temple University – CIS Dept. CIS331– Principles of Database Systems PowerPoint Presentation
Download Presentation
Temple University – CIS Dept. CIS331– Principles of Database Systems

Loading in 2 Seconds...

play fullscreen
1 / 51

Temple University – CIS Dept. CIS331– Principles of Database Systems - PowerPoint PPT Presentation


  • 188 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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

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
General Overview
  • Formal query languages
    • rel algebra and calculi
  • Commercial query languages
    • SQL
    • QBE, (QUEL)
  • Integrity constraints
  • Functional Dependencies
  • Normalization - ‘good’ DB design
overview
Overview
  • Domain; Ref. Integrity constraints
  • Assertions and Triggers
  • Security
  • Functional dependencies
    • why
    • definition
    • Armstrong’s “axioms”
    • closure and cover
functional dependencies
Functional dependencies

motivation: ‘good’ tables

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

‘good’ or ‘bad’?

functional dependencies5
Functional dependencies

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

functional dependencies6
Functional dependencies

‘Bad’ - why?

functional dependencies7
Functional Dependencies
  • Redundancy
    • space
    • inconsistencies
    • insertion/deletion anomalies (later…)
  • What caused the problem?
functional dependencies8
Functional dependencies

… ‘name’ depends on ‘ssn’

define ‘depends’

functional dependencies9
Functional dependencies

Definition:

‘a’ functionally determines ‘b’

functional dependencies10
Functional dependencies

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

functional dependencies11
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 dependencies12
Functional dependencies

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

other examples??

functional dependencies13
Functional dependencies

ssn -> name, address

ssn, c-id -> grade

functional dependencies14
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 dependencies15
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
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 axioms17
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 axioms18
FDs - Armstrong’s axioms

Transitivity

ssn->address

address-> county-tax-rate

THEN:

ssn-> county-tax-rate

fds armstrong s axioms19
FDs - Armstrong’s axioms

Reflexivity:

Augmentation:

Transitivity:

‘sound’ and ‘complete’

fds finding the closure f
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 axioms21
FDs - Armstrong’s axioms

Additional rules:

Union

Decomposition

Pseudo-transitivity

fds armstrong s axioms22
FDs - Armstrong’s axioms

Prove ‘Union’ from the three axioms:

fds armstrong s axioms23
FDs - Armstrong’s axioms

Prove ‘Union’ from the three axioms:

fds armstrong s axioms24
FDs - Armstrong’s axioms

Prove Pseudo-transitivity:

fds armstrong s axioms25
FDs - Armstrong’s axioms

Prove Decomposition

fds closure f
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 f27
FDs - Closure F+

ssn, c-id -> grade

ssn-> name, address

ssn-> ssn

ssn, c-id-> address

c-id, address-> c-id

...

F+

fds closure f28
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
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 a30
FDs - Closure A+

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

ssn, c-id -> grade

ssn-> name, address

{ssn}+ ={ssn,

name, address }

}F

fds closure a31
FDs - Closure A+

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

ssn, c-id -> grade

ssn-> name, address

{c-id}+ = ??

}F

fds closure a32
FDs - Closure A+

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

ssn, c-id -> grade

ssn-> name, address

{c-id, ssn}+ = ??

}F

fds closure a33
FDs - Closure A+

if A+ = {all attributes of table}

then ‘A’ is a candidate key

fds closure a34
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
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 a closure
FDs - A+ closure

C

A

B

Diagrams

AB->C (1)

A->BC (2)

B->C (3)

A->B (4)

fds canonical cover fc
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 fc38
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 fc39
FDs - ‘canonical cover’ Fc

why do we need it?

define it properly

compute it efficiently

fds canonical cover fc40
FDs - ‘canonical cover’ Fc

why do we need it?

easier to compute candidate keys

define it properly

compute it efficiently

fds canonical cover fc41
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

fds canonical cover fc42
‘extraneous’ attribute:

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

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

FDs - ‘canonical cover’ Fc
fds canonical cover fc43
FDs - ‘canonical cover’ Fc

ssn, c-id -> grade

ssn-> name, address

ssn,name-> name, address

ssn, c-id-> grade, name

F

fds canonical cover fc44
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 fc45
FDs - ‘canonical cover’ Fc

Trace algo for

AB->C (1)

A->BC (2)

B->C (3)

A->B (4)

fds canonical cover fc46
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 fc47
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 fc48
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 fc49
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’

fds canonical cover fc50
FDs - ‘canonical cover’ Fc

BEFORE

AB->C (1)

A->BC (2)

B->C (3)

A->B (4)

AFTER

A->B (2’)

B->C (3)

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