Fundamentals of Information System

1
Fundamentals of Information System

• Tutorial 7 Functional Dependency

2
Contents

• The Evils of Redundancy
• Functional Dependency and Basic Concepts
• Some examples

3
The Evils of Redundancy

• Redundancy is at the root of several problems
  associated with relational schemas
• redundant storage, insert/delete/update anomalies
• Integrity constraints, in particular functional
  dependencies, can be used to identify schemas
  with such problems and to suggest refinements.
• Main refinement technique decomposition
  (replacing ABCD with, say, AB and BCD, or ACD and
  ABD).
• Decomposition should be used judiciously
• Is there reason to decompose a relation?
• What problems (if any) does the decomposition
  cause?

4
Functional Dependency

• Functional Dependency
• Describes relationship between attributes in a
  relation.
• If A and B are attributes of relation R, B is
  functionally dependent on A (A-gt B), if each
  value of A in R is associated with exactly one
  value of B in R.

5
Other Basic Concepts

• Closure of a Set of Functional Dependencies F
• Closure of Attribute Set under F
• Canonical Cover
• Lossless-Join Decomposition
• Dependency Preservation

6
Extraneous attributes

• Consider functional dependency in F
• Attribute A is extraneous in if
  , and F logically implies
• Attribute A is extraneous in if
  , and the set of functional dependencies
• 
  logically implies F

7
Canonical Cover

• FC F
• repeat
• Use the union rule to replace any dependencies
  in FC of the form
• and with
  .
• Find a functional dependency in
  FC with an extraneous attribute either in or
  in
• If an extraneous attribute is found, delete it
  from
• until FC does not change

8
Dependency Preservation

• Test each in F for dependency
  preservation
• result
• while (changes to result) do
• for each Ri in the decomposition

9
Example 1

• Professor (ssn, name, age, rank, specialty)
• ssn-gt name, age, rank, specialty
• NBA_Player (year, team, number, person)
• year, team, number -gt person

10
Example 2

• Relation schema R (A, B, C, D, E)
• F A-gtBC, CD-gtE, A-gtD, B-gtD, E-gtA
• A, B, BC
• Candidate keys of R
• FC

11
Example 2 (A)

• Compute A(F A-gtBC, CD-gtE, A-gtD, B-gtD, E-gtA)
• result A
• result ABCD
• result ABCDE

A-gtBC A-gtD
CD-gtE
12
Example 2 (B)

• Compute B(F A-gtBC, CD-gtE, A-gtD, B-gtD, E-gtA)
• result B
• result BD

B-gtD
13
Example 2 (BC)

• Compute BC(F A-gtBC, CD-gtE, A-gtD, B-gtD, E-gtA)
• result BC
• result BCD
• result BCDE
• result ABCDE

B-gtD
CD-gtE
E-gtA
14
Example 2 (Candidate keys of R)

• List Candidate keys of R
• F A-gtBC, CD-gtE, B-gtD, E-gtA
• ? is a candidate key for R
• ? ? R, there is no ? s.t. ? ? ?, ? ? R
• A? B? C? D? E?
• BC? BD? CD?
• Candidate keys A E BC CD

15
Example 2 (FC )

• FC F A-gtBC, CD-gtE, A-gtD, B-gtD, E-gtA
• A-gtBC, A-gtD are combined to A-gtBCD
• FC A-gtBCD, CD-gtE, B-gtD, E-gtA
• D is extraneous in A-gtBCD because
  A-gtBCD is logically implied by A-gtBC and B-gtD
• FC A-gtBC, CD-gtE, B-gtD, E-gtA

16
Example 3 (lossy)
A ? AC ? F?
no
no
A ? AB ? F?
decompose
join
F B ? C, C ? B
17
Example 4 (lossless)
C ? AC ? F?
no
yes
C ? BC ? F?
decompose
join
F B ? C, C ? B
18
Example 5

• Relation Schema R (A, B, C, D, E)
• F A-gtBC, CD-gtE, B-gtD, E-gtA
• R1 (A, B, C) R2 (A, D, E)
• A lossless-join decomposition?
• A dependency-preserving decomposition?
• Ra (A, B, C) Rb (C, D, E)
• A lossless-join decomposition?
• A dependency-preserving decomposition?

19
Example 5 (cont)

• R (A, B, C, D, E) F A-gtBC, CD-gtE, B-gtD,
  E-gtA
• R1 (A, B, C) R2 (A, D, E)
• (1)A lossless-join decomposition?
• A is superkey of R1, so lossless-join
• (2)A dependency-preserving decomposition?
• F1 A-gtBC F2 E-gtA
• 2. test CD-gtE result CD

result doesnt contain E, CD-gtE is not deserved
20
Example 5 (cont)

• R (A, B, C, D, E) F A-gtBC, CD-gtE, B-gtD,
  E-gtA
• Ra (A, B, C) Rb (C, D, E)
• (1)A lossless-join decomposition?
• C is not superkey of Ra and Rb, so lossy-join
• (2)A dependency-preserving decomposition?
• Fa A-gtBC Fb CD-gtE
• 2. test B-gtD result B

result doesnt contain D, B-gtD is not deserved
21
Thanks