Temple university cis dept cis331 principles of database systems l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 51

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


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

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

Download Presentation

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

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 l.jpg

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 l.jpg

General Overview

  • Formal query languages

    • rel algebra and calculi

  • Commercial query languages

    • SQL

    • QBE, (QUEL)

  • Integrity constraints

  • Functional Dependencies

  • Normalization - ‘good’ DB design


Overview l.jpg

Overview

  • Domain; Ref. Integrity constraints

  • Assertions and Triggers

  • Security

  • Functional dependencies

    • why

    • definition

    • Armstrong’s “axioms”

    • closure and cover


Functional dependencies l.jpg

Functional dependencies

motivation: ‘good’ tables

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

‘good’ or ‘bad’?


Functional dependencies5 l.jpg

Functional dependencies

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


Functional dependencies6 l.jpg

Functional dependencies

‘Bad’ - why?


Functional dependencies7 l.jpg

Functional Dependencies

  • Redundancy

    • space

    • inconsistencies

    • insertion/deletion anomalies (later…)

  • What caused the problem?


Functional dependencies8 l.jpg

Functional dependencies

… ‘name’ depends on ‘ssn’

define ‘depends’


Functional dependencies9 l.jpg

Functional dependencies

Definition:

‘a’ functionally determines ‘b’


Functional dependencies10 l.jpg

Functional dependencies

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


Functional dependencies11 l.jpg

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 l.jpg

Functional dependencies

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

other examples??


Functional dependencies13 l.jpg

Functional dependencies

ssn -> name, address

ssn, c-id -> grade


Functional dependencies14 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

FDs - Armstrong’s axioms

Transitivity

ssn->address

address-> county-tax-rate

THEN:

ssn-> county-tax-rate


Fds armstrong s axioms19 l.jpg

FDs - Armstrong’s axioms

Reflexivity:

Augmentation:

Transitivity:

‘sound’ and ‘complete’


Fds finding the closure f l.jpg

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 l.jpg

FDs - Armstrong’s axioms

Additional rules:

Union

Decomposition

Pseudo-transitivity


Fds armstrong s axioms22 l.jpg

FDs - Armstrong’s axioms

Prove ‘Union’ from the three axioms:


Fds armstrong s axioms23 l.jpg

FDs - Armstrong’s axioms

Prove ‘Union’ from the three axioms:


Fds armstrong s axioms24 l.jpg

FDs - Armstrong’s axioms

Prove Pseudo-transitivity:


Fds armstrong s axioms25 l.jpg

FDs - Armstrong’s axioms

Prove Decomposition


Fds closure f l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

FDs - Closure A+

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

ssn, c-id -> grade

ssn-> name, address

{c-id}+ = ??

}F


Fds closure a32 l.jpg

FDs - Closure A+

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

ssn, c-id -> grade

ssn-> name, address

{c-id, ssn}+ = ??

}F


Fds closure a33 l.jpg

FDs - Closure A+

if A+ = {all attributes of table}

then ‘A’ is a candidate key


Fds closure a34 l.jpg

FDs - Closure A+

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


Fds closure a example l.jpg

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 l.jpg

FDs - A+ closure

C

A

B

Diagrams

AB->C (1)

A->BC (2)

B->C (3)

A->B (4)


Fds canonical cover fc l.jpg

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 l.jpg

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 l.jpg

FDs - ‘canonical cover’ Fc

why do we need it?

define it properly

compute it efficiently


Fds canonical cover fc40 l.jpg

FDs - ‘canonical cover’ Fc

why do we need it?

easier to compute candidate keys

define it properly

compute it efficiently


Fds canonical cover fc41 l.jpg

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 l.jpg

‘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 l.jpg

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 l.jpg

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 l.jpg

FDs - ‘canonical cover’ Fc

Trace algo for

AB->C (1)

A->BC (2)

B->C (3)

A->B (4)


Fds canonical cover fc46 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

Overview - conclusions

  • Domain; Ref. Integrity constraints

  • Assertions and Triggers

  • Functional dependencies

    • why

    • definition

    • Armstrong’s “axioms”

    • closure and cover


  • Login