How to check whether a functional dependency hold in a relation R

How to check whether a functional dependency hold in a relation R, Check the functional dependencies of a relation, Functional dependencies satisfied or not?, Find all the functional dependencies or a table

Exercise

Consider a relation R (A, B, C, D) with the following 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?

(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.

In other words, if you execute the following query, we should always get only one student_name for a given reg_no.

SELECT Student_Name FROM Student WHERE 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 → 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.

(a) A → 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).

(b) A → 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.

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).

(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.

(d) C → D – holds in relation R.  

WHY? 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.

(f) BD → AC – holds in relation R.  

(g) AD → BC – does not hold in relation 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.

(h) D → B – does not hold in relation R.  

WHY? 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.  

Go back to other Normalization Process Exercises