Functional Dependencies - Database Systems - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Functional Dependencies - Database Systems - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Functional Dependencies

Why FD's

Meaning of FD’s

Keys and Superkeys

Inferring FD’s

Improving Relation Schemas

• Set of relation schemas obtained by translating

from an E/R diagram might need improvement

• modeling with E/R diagrams is an art, not a science; relies on experience and intuition
• multiple alternative designs are possible, how to choose?
• Problems caused by redundant storage of info
• wasted space
• anomalies when updating, inserting or deleting tuples
• Basic idea: replace a relation schema with a collection of "smaller" schemas. Called decomposition

Functional Dependencies

• X -> A is an assertion about a relation R that

whenever two tuples of R agree on all the attributes of X , then they must also agree on the attribute A.

• Say “ X -> A holds in R .”
• Convention: …, X , Y , Z represent sets of attributes; A , B , C ,… represent single attributes.
• Convention: no set formers in sets of attributes, just ABC , rather than { A , B , C }.

Example

Consumers(name, addr, candiesLiked, manf,

favCandy)

• Reasonable FD’s to assert:

• name -> addr
• name -> favCandy
• candiesLiked -> manf

FD’s With Multiple Attributes

• No need for FD’s with > 1 attribute on right.

• But sometimes convenient to combine FD’s as a shorthand.
• Example: name -> addr and name -> favCandy become name -> addr favCandy

• > 1 attribute on left may be essential.

• Example: store candy -> price

Keys of Relations

• K is a superkey for relation R if K

functionally determines all of R.

• K is a key for R if K is a superkey, but no

proper subset of K is a superkey.

Example, Cont.

• {name, candiesLiked} is a key because neither

{name} nor {candiesLiked} is a superkey.

• name doesn’t -> manf; candiesLiked doesn’t -> addr.

• There are no other keys, but lots of superkeys.

• Any superset of {name, candiesLiked}.

E/R and Relational Keys

• Keys in E/R concern entities.

• Keys in relations concern tuples.

• Usually, one tuple corresponds to one entity,

so the ideas are the same.

• But --- in poor relational designs, one entity

can become several tuples, so E/R keys and

Relational keys are different.

Discovering Keys

• Suppose schema was obtained from an E/R

diagram.

• If relation R came from an entity set E, then key for R is the keys of E
• If R came from a binary relationship from E1 to E2:
• many-many: key for R is the keys of E1 and E
• many-one: key for R is the keys for E1 (but not the keys for E2)
• one-one: key for R is the keys for E1; another key for R is the keys for E

Key Example

14

Con- sumers Likes Candies

Likes(consumer, candy) Favorite

Favorite(consumer, candy)

Married

husband

wife

Married(husband, wife)

name addr name manf

Buddies

Buddies(name1, name2)

key: consumer candy

key: name1 name

key: consumer

keys: husband or wife

Manipulating FD's

• Need to be able to reason about FD's in order to

support the normalization process (improving relational schemas)

• Some preliminaries:
  • can split FD X -> A B into X -> A, X -> B
  • can combine FD's X -> A, X -> B into FD X -> A B
  • Since A -> A is trivially true, no point in having any attribute on the RHS that is also on the LHS

Closure of a Set of FD's

• If we have FD's A -> B and B -> C, then it is also

true that A -> C.

• Ex: If name -> address and address -> phone, then name -> phone.

• What about a chain of such deductions?

• Called closure

Example of Closure Algorithm

• Given relation with attributes A, B, C, D, E, F and

FD's

• AB -> C
• BC -> A, BC -> D
• D -> E
• CF -> B
• Compute closure of {A,B}.
• Answer:
• Z := {A,B}
• add C
• add A and D
• add E
• final answer is Z = {A,B,C,D,E} (^19)

Use of Closure Algorithm

• Now we can check if a particular FD

A1 … An -> B follows from a set of FD's S:

• compute {A1,…,An} +^ using S
• if B is in the closure, then the FD follows
• otherwise it does not