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

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


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

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

… ‘name’ depends on ‘ssn’

define ‘depends’


Functional dependencies

Definition:

‘a’ functionally determines ‘b’


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

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

other examples??


Functional dependencies

ssn -> name, address

ssn, c-id -> grade


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 - Armstrong’s axioms

Reflexivity:

Augmentation:

Transitivity:

‘sound’ and ‘complete’


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

Additional rules:

Union

Decomposition

Pseudo-transitivity


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+

if A+ = {all attributes of table}

then ‘A’ is a candidate key


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)

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

C

A

B

Diagrams

AB->C (1)

A->BC (2)

B->C (3)

A->B (4)


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?

define it properly

compute it efficiently


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


‘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’ 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)


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’


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

  • Domain; Ref. Integrity constraints

  • Assertions and Triggers

  • Functional dependencies

    • why

    • definition

    • Armstrong’s “axioms”

    • closure and cover


  • Login