EXAMPLE 1:
Consider a relation R (A, B, C, D) with the following
instance;
instance;
|
A
|
B
|
C
|
D
|
|
1
|
1
|
2
|
3
|
|
1
|
2
|
2
|
3
|
|
1
|
3
|
2
|
3
|
|
2
|
4
|
5
|
6
|
|
5
|
6
|
7
|
8
|
Which of the following functional
dependencies are satisfied by this relation? How?
dependencies are satisfied by this relation? How?
(a) A → B
(b) A → CD
(c) AB → CD
(d) C → D
(e) B → A
(f) BD → AC
(g) AD → BC
(h) D → B
(i) D → C
(j) C → A
Solution:
Functional dependency – For a given
Left Hand Side (LHS) value of an attribute (or set of attributes) of a
functional dependency, there should be at most one Right Hand Side (RHS) value
of an attribute (or set of attributes). Then we would say that the functional dependency
holds on that relation.
Left Hand Side (LHS) value of an attribute (or set of attributes) of a
functional dependency, there should be at most one Right Hand Side (RHS) value
of an attribute (or set of attributes). Then we would say that the functional dependency
holds on that relation.
In other words, if you execute the
following query, we should always get only one student_name for a given reg_no.
following query, we should always get only one student_name for a given reg_no.
SELECT Student_Name FROM Student WHERE
Reg_No = 123;
Reg_No = 123;
Example:
REG_NO →
STUDENT_NAME. This functional dependency means that there is at most one student
name is related to one register number. This is correct.
STUDENT_NAME. This functional dependency means that there is at most one student
name is related to one register number. This is correct.
STUDENT_NAME → REG_NO. This
functional dependency means that there is at most one register number is
related to a student name. This may not be true. Because, there may be more
than one student with the same name.
functional dependency means that there is at most one register number is
related to a student name. This may not be true. Because, there may be more
than one student with the same name.
(a) A →
B – does not hold in relation R.
B – does not hold in relation R.
WHY? In
the table R, we have 3 B values (1, 2 and 3) for a given A value (1).
the table R, we have 3 B values (1, 2 and 3) for a given A value (1).
(b) A →
CD – holds in relation R.
CD – holds in relation R.
WHY? In
R, we have single C and D combination of values for every A value. You can
observe the following from R.
R, we have single C and D combination of values for every A value. You can
observe the following from R.
|
A
|
(C, D)
|
|
1
|
(2, 3)
|
|
2
|
(5, 6)
|
|
5
|
(7, 8)
|
Also observe that whenever 1 comes as
A value, (2, 3) is repeated. That means, this should be true for all records of
R. whenever 1 is inserted as A value for any new record, there must be only one
combination of C and D, (2, 3).
A value, (2, 3) is repeated. That means, this should be true for all records of
R. whenever 1 is inserted as A value for any new record, there must be only one
combination of C and D, (2, 3).
(c) AB → CD – holds in relation R.
WHY?
In R there is one to one relationship between AB and CD, i.e., for a given A
and B combination of values, there is unique C and D combination of values.
In R there is one to one relationship between AB and CD, i.e., for a given A
and B combination of values, there is unique C and D combination of values.
(d) C → D – holds in relation R.
WHY?
In R there is one to one relationship between C and D values.
In R there is one to one relationship between C and D values.
|
C
|
D
|
|
2
|
3
|
|
5
|
6
|
|
7
|
8
|
(e) B → A – holds in relation R.
WHY?
In R there is one to one relationship between B and A values from B. that is,
for a given B value, there is only one associated A value.
In R there is one to one relationship between B and A values from B. that is,
for a given B value, there is only one associated A value.
(f) BD → AC – holds in relation R.
(g) AD → BC – does not hold in relation
R.
R.
WHY?
In R, for a given A and D values (1, 3), there are more than one (3 values – (1,
2), (2, 2), (3, 2)) B and C values.
In R, for a given A and D values (1, 3), there are more than one (3 values – (1,
2), (2, 2), (3, 2)) B and C values.
(h) D → B – does not hold in relation
R.
R.
WHY?
In R, we have 3 different B values (1, 2, and 3) for a given D value (3).
In R, we have 3 different B values (1, 2, and 3) for a given D value (3).
(i) D → C – holds in relation R.
(j) → D – holds in relation R.