Temple university cis dept cis331 principles of database systems
Download
1 / 51

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


  • 178 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 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) do for each in F do begin if  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


ad