Unit 2 Functional - Dependency-2

Functional Dependency
It is a constraint among attributes which is the main tool for formally

measuring the appropriateness of attribute groupings into relations
29/10/22 1
Functional dependency
R is a relation schema and X & Y are subsets of R.

Definition of FD
If R be a relation schema and X & Y are subsets of R then
X  Y holds good on R if and only if for any two tuples
t1, t2 Є r(R) whenever t1[X] = t2[X], we must have
t1[Y] = t2[Y]
X  Y defines a functional dependency if value of X uniquely

determines value of Y
A functional dependency is a constraint, a business rule.

A functional dependency holds good on a schema.
PK & CK constraints are special FDs.
2
Functional Dependencies Example
Employee Id
Employee Id Name
Name
t1 112 Satish CC JJ
Satish
112 Satish C J
t2
If t1[Employee id] = t2[Employee id]

and
T1[Name]= t2[Name]
|= Employee Id  Name
29/10/22 3
Functional Dependency Examples
• Social security number determines employee name

SSN  ENAME
• Project number determines project name and location
PNUMBER  {PNAME, PLOCATION}
• Employee ssn and project number determines the hours per week
that the employee works on the project
{SSN, PNUMBER}  HOURS
29/10/22 4
Consider this table
Every Department has only one Manager

Assumption – Every Manager has only one phone.
Dep Number Manager SSN Manager Phone
123 111 9982543322
124 112 2445566778
125 113 1111111111
F={ DepNumber Manager SSN, Manager SSN  Manager Phone}
Inferring another functional Dependency

DepNumber  Manager Phone
29/10/22 5
Inference Rules.
• New Dependencies can be inferred from a set of given dependencies

• A set of inference rules will help us infer dependencies in a formal
way
29/10/22 6
Inference Rules.
1. IR1. (Reflexive) If Y subset-of X, then X  Y
2. IR2. (Augmentation) If X Y, then XZ  YZ
3. IR3. (Transitive) If X  Y and Y  Z, then X  Z
4. IR4. (Decomposition): If X  YZ, then X Y and
XZ
5. IR5. (Union): If X  Y and X Z, then X  YZ
29/10/22 7
Trivial functional dependency
X  Y is called a trivial FD if X is a super set of Y.
X  X also is a trivial FD.
A FD is called non-trivial if it is not trivial.
{Ssn}  {Ssn} a trivial FD. (X  X)

{Essn, Pno}  {Essn} a trivial FD.
(left hand side is a super set right hand side)
8
Closure of a set of attributes
• Assume that X is a subset of a schema R & F is a set of

functional dependencies that hold good on R.
• Closure of X is denoted by X+.
• Closure of X (X+) is the maximal (biggest) super set of X such
that X  X+.
• If X+ = R then X is a super key of R
9
Algorithm for computing X+
Given R & F find X+

Algorithm:
Begin
X+ := X
Repeat
For each functional dependency Y  Z ε F,
if X+ contains Y then
X+ := X+Z
Until X+ does not change.
End.
10
Finding closure of a set of attributes (Example)
R(A, B, C, G, H, I)
F = {A  B, A  C, CG  H, CG  I, B  H}
A+ = ?
A+ = A
Since A  B so A+ = AB
Since A  C so A+ = ABC
Consider CG  H, CG is not a subset of A+ so A+ remains
unaltered
Consider CG  I, CG is not a subset of A+ so A+ remains
unaltered
Consider B  H, B is a subset of A+ so A+ = ABCH.
Find out (AG)+ and (CG)+ .
11
Finding a Key (K) for R
Input: A relation R and a set of functional

dependencies F on the attributes of R.
1. Set K:=R.
2. For each attribute A in K {
compute (K– A)+ with respect to F;
if (K – A)+ contains all the attributes in R,
then set K := K – {A};
}
12
Example 1
Consider the following relation:

R(A, B, C, D, E)
Suppose that the following dependencies exist
A  C, C  D, B  E.
Find out a key of R.
Answer: AB
13
Example 2
R(A, B, C, D, E, G, H) &
F = {A B, B  ACE, C BGD, G  DH}.
Find out key(s) of R.
Ans. There are three keys A,B and C
14
Example 3
R(A, B, C, D) &
F = {A  B, B  C, C  BD}.
Ans. A
15
Example 4
R(A, B, C, D, E) &
F = {A  B, B  A, B  C, D  A}.
[Ans. DE]
Solution:
A+= ABC ≠ R so A cannot be a key.
B+ = BAC ≠ R, so B cannot be a key.
D+ = DABC ≠ R and so D cannot be a key.
(DE)+ = DEABC = R and so DE is a key of R.
16
Example
• Finding Candidate Keys and Super Keys of a Relation using FD

set The set of attributes whose attribute closure is set of all attributes
of relation is called super key of relation. For Example, the
EMPLOYEE relation shown in Table 1 has following FD set. {E-ID-
>E-NAME, E-ID->E-CITY, E-ID->E-STATE, E-CITY->E-
STATE} Let us calculate attribute closure of different set of
attributes:
17
MINIMAL SET OF FUNCTIONAL
DEPENDENCIES
• A minimal set of functional dependencies is a set of
dependencies in standard form with no redundancies.
• Minimal cover of set of functional dependencies is the
minimal set of functional dependencies.
• Simple FD − X->Y is a simple FD if Y is a single attribute.
• Left reduced FD: X->Y is a left reduced FD if there are no
extraneous attributes in X.{extraneous attributes: let XA->Y
then A is a extraneous attribute if X_>Y}
• Non-redundant FD − X->Y is a Non-redundant FD if it
cannot be derived from F- {X->y}.
18
Problem
Find the canonical cover of FD {A->BC, B->AC, C->AB}.
Solution
Relational schema R(A,B,C) F: {A->BC, B->AC, C->AB}
Step 1 − Create a singleton right hand side
dependency A->BC will break into A->B, A->C.
F: { A->B A->C B->A B->C C->A C->B}
Step 2 − Remove extraneous attributes if any exists.
F:{ A->B A->C B->A B->C C->A C->B} NO extraneous

attributes exists
Example= AB->C
19
Step 3 − Remove the redundant FD
F: { A->B A->C B->A B->C C->A C->B }
Remove B->A dependency and we can get A from B through B-
>C and C->A.
F= {A->B A->C B->C C->A C->B}

By removing C->B dependency we get B from C through C-
>A , A->B.
F={A->B B->C C->A A->C}

By removing A->C dependency we can determine C from A
through A->B, B->C
Step 4 − The final canonical cover is as follows −

FC ={ A->B, B->C, C->A } [A]+ =BC [B]+=AC [C]+=AB.
20
Exercise
• Find a minimal cover of

F = {BA, D A, AB  D}.
[Answer: G = {D  A, B  D} ]
• Find a minimal cover of

F = {A  B, B  C, AC  D}.
[Answer: G = {A  B, B  C, A  D}]
21
Exercise
Find out a minimal cover for the following set of

functional dependencies. Show the detail of each
step towards the solution.
F = {A  B, ABCD  E, EF  GH, ACDF  EG}.
ANSWER:
G = {A  B, ACD  E, EF  G, EF  H}.
22

Unit 2 Functional - Dependency-2

Uploaded by

Copyright:

Available Formats

Unit 2 Functional - Dependency-2

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Unit 2 Functional - Dependency-2

Uploaded by

Copyright:

Available Formats

Functional Dependency

It is a constraint among attributes which is the main tool for formally

R is a relation schema and X & Y are subsets of R.

X  Y defines a functional dependency if value of X uniquely

A functional dependency is a constraint, a business rule.

If t1[Employee id] = t2[Employee id]

• Social security number determines employee name

Every Department has only one Manager

Dep Number Manager SSN Manager Phone

123 111 9982543322

124 112 2445566778

125 113 1111111111

F={ DepNumber Manager SSN, Manager SSN  Manager Phone}

Inferring another functional Dependency

• New Dependencies can be inferred from a set of given dependencies

X  Y is called a trivial FD if X is a super set of Y.

X  X also is a trivial FD.

A FD is called non-trivial if it is not trivial.

{Ssn}  {Ssn} a trivial FD. (X  X)

• Assume that X is a subset of a schema R & F is a set of

Given R & F find X+

Input: A relation R and a set of functional

Consider the following relation:

Find out key(s) of R.

Ans. There are three keys A,B and C

Find out key(s) of R.

• Finding Candidate Keys and Super Keys of a Relation using FD

F: { A->B A->C B->A B->C C->A C->B}

Step 2 − Remove extraneous attributes if any exists.

F:{ A->B A->C B->A B->C C->A C->B} NO extraneous

F= {A->B A->C B->C C->A C->B}

F={A->B B->C C->A A->C}

Step 4 − The final canonical cover is as follows −

• Find a minimal cover of

• Find a minimal cover of

Find out a minimal cover for the following set of

You might also like