Functional-dependency
Question 1 |
Consider the relation X(P, Q, R, S, T, U) with the following set of functional dependencies
F = {
{P, R} → {S,T},
{P, S, U} → {Q, R}
}
Which of the following is the trivial functional dependency in F+ is closure of F?
{P,R}→{S,T} | |
{P,R}→{R,T} | |
{P,S}→{S} | |
{P,S,U}→{Q} |
Question 1 Explanation:
X→Y is trivial if Y⊆ X
Question 2 |
Consider the relation scheme R = (E, F, G, H, I, J, K, L, M, N) and the set of functional dependencies {{E,F} → {G}, {F} → {I,J}, {E,H} → {K,L}, {K} → {M}, {L} → {N}} on R. What is the key for R?
{E,F} | |
{E,F,H} | |
{E,F,H,K,L} | |
{E} |
Question 2 Explanation:
A) (EF)+ = EFGIJ. So, EF is not a key.
B) (EFH)+ = EFHGIJKLMN, EFH is deriving all the attributes of R. So, EFH is a key.
C) If EFH is a key, then EFHKL will become the super key.
D) (E)+ = E
B) (EFH)+ = EFHGIJKLMN, EFH is deriving all the attributes of R. So, EFH is a key.
C) If EFH is a key, then EFHKL will become the super key.
D) (E)+ = E
Question 3 |
The following functional dependencies are given:
AB → CD, AF → D, DE → F, C → G, F → E, G → A.
Which one of the following options is false?
{CF}+ = {ACDEFG} | |
{BG}+ = {ABCDG}
| |
{AF}+ = {ACDEFG} | |
{AB}+ = {ABCDFG} |
Question 3 Explanation:
AB → CD
AF → D
DE → F
C → G
F → E
G → A
CF+ = {G,E,A,D,C,F} = {A,C,D,E,F,G} (✔️)
BG+ = {B,G,A,C,D} = {A,B,C,D,G} (✔️)
AF+ = {D,E,A,F} = {A,D,E,F} (❌)
AB+ = {A,B,C,D,G} (❌)
AF → D
DE → F
C → G
F → E
G → A
CF+ = {G,E,A,D,C,F} = {A,C,D,E,F,G} (✔️)
BG+ = {B,G,A,C,D} = {A,B,C,D,G} (✔️)
AF+ = {D,E,A,F} = {A,D,E,F} (❌)
AB+ = {A,B,C,D,G} (❌)
Question 4 |
The following functional dependencies hold true for the relational schema {V, W, X, Y, Z} :
V → W
VW → X
Y → VX
Y → Z
Which of the following is irreducible equivalent for this set of functional dependencies?
V → W
VW → X
Y → VX
Y → Z
Which of the following is irreducible equivalent for this set of functional dependencies?
V→W V→X Y→V Y→Z | |
V→W W→X Y→V Y→Z | |
V→W V→X Y→V Y→X Y→Z | |
V→W W→X Y→V Y→X Y→Z |
Question 4 Explanation:
Step 1:
V → W, VW → X, Y → V, Y → X, Y→ Z
Step 2:
V → W, VW → X, Y → V, Y → X, Y→ Z
(V)+ = V ×
(VW)+ = VW ×
(Y)+ = YXZ
(Y)+ = YVW ×
(Y)+ = YVWX
Without Y → X, the closure of Y is deriving ‘X’ from the remaining attributes.
So, we can remove Y → X as its redundant.
Step 3:
V → W, VW → X, Y → V, Y → Z
(V)+ = VW, the closure of V is deriving W from the remaining FD’s.
So, W is redundant. We can remove it.
So, the final canonical form is
V→W, V→X, Y→V, Y→Z
⇾ So, option (A) is correct.
V → W, VW → X, Y → V, Y → X, Y→ Z
Step 2:
V → W, VW → X, Y → V, Y → X, Y→ Z
(V)+ = V ×
(VW)+ = VW ×
(Y)+ = YXZ
(Y)+ = YVW ×
(Y)+ = YVWX
Without Y → X, the closure of Y is deriving ‘X’ from the remaining attributes.
So, we can remove Y → X as its redundant.
Step 3:
V → W, VW → X, Y → V, Y → Z
(V)+ = VW, the closure of V is deriving W from the remaining FD’s.
So, W is redundant. We can remove it.
So, the final canonical form is
V→W, V→X, Y→V, Y→Z
⇾ So, option (A) is correct.
Question 5 |
Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional dependencies hold: {A → B, BC → D, E → C, D → A}. What are the candidate keys of R?
AE, BE | |
AE, BE, DE | |
AEH, BEH, BCH | |
AEH, BEH, DEH |
Question 5 Explanation:
Using the given functional dependencies and looking at the dependent attributes, E and H are not dependent on any. So, they must be a part of any candidate key.
So only option (D) contains E and H as part of candidate key.
So only option (D) contains E and H as part of candidate key.
Question 6 |
From the following instance of a relation scheme R(A,B,C), we can conclude that:
A functionally determines B and B functionally determines C | |
A functionally determines B and B does not functionally determines C | |
B does not functionally determines C | |
A does not functionally determines B and B does not functionally determines C |
Question 6 Explanation:
From the given instance of relation it can be seen that A functionally determines B, but we cannot conclude this for the entire relation.
But for the given instance it can be seen that B does not functionally determines C, and it can be concluded for entire relation.
But for the given instance it can be seen that B does not functionally determines C, and it can be concluded for entire relation.
Question 7 |
Consider a schema R(A,B,C,D) and functional dependencies A → B and C → D. Then the decomposition of R into R1(AB) and R2(CD) is
dependency preserving and lossless join | |
lossless join but not dependency preserving | |
dependency preserving but not lossless join | |
not dependency preserving and not lossless join |
Question 7 Explanation:
If the given relations are to be lossless then
R1∩R2 ≠ 0
Given R1(A,B), R2
R1∩R2 = 0
Not lossless.
The given relation decomposed into R1(A,B) and R2(C,D) and there are only two functional dependencies A→B and C→D. So the given decomposition is dependency preserving.
R1∩R2 ≠ 0
Given R1(A,B), R2
R1∩R2 = 0
Not lossless.
The given relation decomposed into R1(A,B) and R2(C,D) and there are only two functional dependencies A→B and C→D. So the given decomposition is dependency preserving.
Question 8 |
Given the following relation instance.
x y z
1 4 2
1 5 3
1 6 3
3 2 2
Which of the following functional dependencies are satisfied by the instance?
XY → Z and Z → Y | |
YZ → X and Y → Z | |
YZ → X and X → Z | |
XZ → Y and Y → X |
Question 8 Explanation:
A functional dependency A→B is said to hold if for two tuples t1 and t2.
If for t1[A] = t2[A] then t1[Y] = t2[Y].
If for t1[A] = t2[A] then t1[Y] = t2[Y].
Question 9 |
Let R = (A, B, C, D, E, F) be a relation scheme with the following dependencies: C→F, E→A, EC→D, A→B. Which of the following is a key of R?
CD | |
EC | |
AE | |
AC |
Question 9 Explanation:
Let's check closure for each option,
A) (CD)+ = cdf
Not a key.
B) (EC)+ = ecdabf
Yes, it is a key.
C) (AE)+ = aeb
Not a key. D) (AC)+ = abcf
Not a key.
A) (CD)+ = cdf
Not a key.
B) (EC)+ = ecdabf
Yes, it is a key.
C) (AE)+ = aeb
Not a key. D) (AC)+ = abcf
Not a key.
Question 10 |
Consider a relation R with five attributes V, W, X, Y, and Z. The following functional dependencies hold: VY → W, WX → Z, and ZY → V.
Which of the following is a candidate key for R?
VXZ | |
VXY | |
VWXY | |
VWXYZ |
Question 10 Explanation:
As we can see that attribute XY do not appear in RHS of an FD, they need to be a part of key.
Candidate keys are
VXY, WXY, ZXY
Candidate keys are
VXY, WXY, ZXY
Question 11 |
Let R (A, B, C, D) be a relational schema with the following functional dependencies: A → B, B → C, C → D and D → B.
The decomposition of R into (A, B), (B, C), (B, D)
gives a lossless join, and is dependency preserving | |
gives a lossless join, but is not dependency preserving | |
does not give a lossless join, but is dependency preserving | |
does not give a lossless join and is not dependency preserving
|
Question 11 Explanation:
(A, B) (B, C) - common attribute is (B) and due to B→C, B is a key for (B, C) and hence ABC can be losslessly decomposed into (A, B)and (B, C).
(A, B, C) (B, D) - common attributes is B and B→D is a FD (via B→C, C→D), and hence, B is a key for (B, D). So, decomposition of (A, B, C, D) into (A, B, C) (B, D) is lossless.
Thus the given decomposition is lossless.
The given decomposition is also dependency preserving as the dependencies A→B is present in (A, B), B→C is present in (B, C), D→B is present in (B, D) and C→D is indirectly present via C→B in (B, C) and B→D in (B, D).
(A, B, C) (B, D) - common attributes is B and B→D is a FD (via B→C, C→D), and hence, B is a key for (B, D). So, decomposition of (A, B, C, D) into (A, B, C) (B, D) is lossless.
Thus the given decomposition is lossless.
The given decomposition is also dependency preserving as the dependencies A→B is present in (A, B), B→C is present in (B, C), D→B is present in (B, D) and C→D is indirectly present via C→B in (B, C) and B→D in (B, D).
Question 12 |
Suppose the following functional dependencies hold on a relation U with attributes P, Q, R, S, and T:
P⟶ QR
RS⟶ T
Which of the following functional dependencies can be inferred from the above functional dependencies?
P⟶ QR
RS⟶ T
Which of the following functional dependencies can be inferred from the above functional dependencies?
PS ⟶ Q | |
PS ⟶ T | |
R ⟶ T | |
P ⟶ R |
Question 12 Explanation:
Question 13 |
Consider a relation R ( A , B , C , D , E ) with the following three functional dependencies.
AB → C; BC → D; C → E;
The number of superkeys in the relation R is _____________.
AB → C; BC → D; C → E;
The number of superkeys in the relation R is _____________.
8 |
Question 13 Explanation:
R(A, B, C, D, E)
AB → C
BC → D
C → E
¯ The attributes A, B are not there in the right hand side of any of the given FDs.
¯ So, AB can be a candidate key.
(AB)^+ = ABCDE
¯ (AB)^+ is deriving all the attributes of R Hence, AB is a candidate key.
¯ The number of super keys possible for R with “AB” candidate kay is:
2^5 – 2 = 2^3
= 8
AB → C
BC → D
C → E
¯ The attributes A, B are not there in the right hand side of any of the given FDs.
¯ So, AB can be a candidate key.
(AB)^+ = ABCDE
¯ (AB)^+ is deriving all the attributes of R Hence, AB is a candidate key.
¯ The number of super keys possible for R with “AB” candidate kay is:
2^5 – 2 = 2^3
= 8
Question 14 |
The symbol → indicates functional dependency in the context of a relational database. Which of the following options is/are TRUE?
(X,Y)→ (Z,W) implies X→ (Z,W)
| |
(X,Y)→ (Z,W) implies (X,Y)→ Z | |
((X,Y)→ Z and W→ Y) implies (X,W)→ Z
| |
(X→Yand Y→ Z) implies X→ Z |
Question 14 Explanation:
(A) (X,Y) → (Z,W) implies X → (Z,W)
This statement is not necessarily true. The functional dependency (X,Y) → (Z,W) means that for any given value of X and Y, there is a unique value of Z and W. However, this does not imply that for any given value of X, there is a unique value of Z and W. So, option (A) is FALSE.
(B) (X,Y) → (Z,W) implies (X,Y) → Z
This statement is TRUE. If (X,Y) functionally determines (Z,W), then it also functionally determines Z. This is because for any given combination of X and Y, there is a unique value of Z according to the functional dependency. So, option (B) is TRUE.
(C) ((X,Y) → Z and W → Y) implies (X,W) → Z
This statement is TRUE. If X and Y together determine Z and W determines Y, then X and W together determine Z. This is because for any given combination of X and W, we can find the corresponding values of Y and then use (X,Y) → Z to determine the value of Z. So, option (C) is TRUE.
(D) (X → Y and Y → Z) implies X → Z
This statement is TRUE. If X determines Y and Y determines Z, then X determines Z. This is because the transitive property of functional dependency applies here. So, option (D) is TRUE
Question 15 |
For a database relation R(a,b,c,d) where the domains of a, b, c and d include only atomic values, only the following functional dependencies and those that can be inferred from them hold
a → c
b → d
The relation is in
First normal form but not in second normal form | |
Second normal form but not in third normal form | |
Third normal form | |
None of the above |
Question 15 Explanation:
→ Candidate Key of given relation R is {ab}.
→ So option A is right it is not in second normal form because its part of key(candidate key) determines non-key.
→ The domains of a, b, c and d include only atomic values.
So, which satisfies the definition of first normal form.
→ So option A is right it is not in second normal form because its part of key(candidate key) determines non-key.
→ The domains of a, b, c and d include only atomic values.
So, which satisfies the definition of first normal form.
Question 16 |
The set of attributes X will be fully functionally dependent on the set of attributes Y if the following conditions are satisfied.
X is functionally dependent on Y | |
X is not functionally dependent on any subset of Y | |
Both (a) and (b) | |
None of these |
Question 16 Explanation:
→ A functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.
→ An attribute is fully functional dependent on another attribute, if it is Functionally Dependent on that attribute and not on any of its proper subset.
For example, an attribute X is fully functional dependent on another attribute Y, if it is Functionally Dependent on X and not on any of the proper subset of Y.
Must satisfied conditions:
i) X is functionally dependent on Y and
ii) X is not functionally dependent on any subset of Y.
→ An attribute is fully functional dependent on another attribute, if it is Functionally Dependent on that attribute and not on any of its proper subset.
For example, an attribute X is fully functional dependent on another attribute Y, if it is Functionally Dependent on X and not on any of the proper subset of Y.
Must satisfied conditions:
i) X is functionally dependent on Y and
ii) X is not functionally dependent on any subset of Y.
Question 17 |
In functional dependency Armstrong inference rules refers to
Reflexive, Augmentation and Decomposition | |
Transitive, Augmentation and Reflexive | |
Augmentation, Transitive, Reflexive and Decomposition | |
Reflexive, Transitive and Decomposition |
Question 17 Explanation:
Armstrong's axioms are a set of axioms (or, more precisely, inference rules) used to infer all the functional dependencies on a relational database.
The axioms are sound in generating only functional dependencies in the closure of a set of functional dependencies when applied to that set
Axiom of Reflexivity:
→If X is a set of attributes and Y is a subset of X, then X holds Y. Hereby, X holds Y ( X -> Y) means that X functionally determines Y.
Axiom of Augmentation:
→If X holds Y and Z is a set of attributes, then XZ holds YZ. It means that attribute in dependencies does not change the basic dependencies.
Axiom of Transitivity:
The axiom of transitivity says if X holds Y, and Y holds Z, then X must also hold Z.
The axioms are sound in generating only functional dependencies in the closure of a set of functional dependencies when applied to that set
Axiom of Reflexivity:
→If X is a set of attributes and Y is a subset of X, then X holds Y. Hereby, X holds Y ( X -> Y) means that X functionally determines Y.
Axiom of Augmentation:
→If X holds Y and Z is a set of attributes, then XZ holds YZ. It means that attribute in dependencies does not change the basic dependencies.
Axiom of Transitivity:
The axiom of transitivity says if X holds Y, and Y holds Z, then X must also hold Z.
Question 18 |
Consider the schema R(A,B,C,D) and the functional dependencies A→ B and C→ D. If the decomposition is made as R1(A,B) and R2(C,D), then which of the following is TRUE?
Preserves dependency but cannot perform lossless join | |
Preserves dependency and performs lossless join | |
Does not perform dependency and cannot perform lossless join | |
Does not preserve dependency but perform lossless join |
Question 18 Explanation:
Given Data,
Schema R(A, B, C, D)
Functional dependencies are A→ B and C→ D
Decomposed Schema is R1(A,B) and R2(C,D)
Dependency preservation decomposition:
It is another property of decomposed relational database schema D in which each functional dependency X → Y specified in F either appeared directly in one of the relation schemas Ri in the decomposed D or could be inferred from the dependencies that appear in some Ri.
Decomposition D={ R1 , R2, R3,,.., ,Rm} of R is said to be dependency-preserving with respect to F if the union of the projections of F on each Ri , in D is equivalent to F.
In other words, R ⊂ join of R1, R1 over X. The dependencies are preserved because each dependency in F represents a constraint on the database. If decomposition is not dependency-preserving, some dependency is lost in the decomposition.
→ R1(A,B) is covered A→ B
→ R2(C,D) is covered C→ D
It is Functional Dependency preserving because both the functional dependencies are covered.
Lossless join:
The decomposition is a lossless-join decomposition of R if at least one of the following functional dependencies are in F+ (where F+ stands for the closure for every attribute or attribute sets in F):
R1 ∩ R2 → R1
R1 ∩ R2 → R2
According to functional dependency R1(A,B) ∩ R2(C,D) = null. There is no common key in both the tables. So, it is not lossless join.
Schema R(A, B, C, D)
Functional dependencies are A→ B and C→ D
Decomposed Schema is R1(A,B) and R2(C,D)
Dependency preservation decomposition:
It is another property of decomposed relational database schema D in which each functional dependency X → Y specified in F either appeared directly in one of the relation schemas Ri in the decomposed D or could be inferred from the dependencies that appear in some Ri.
Decomposition D={ R1 , R2, R3,,.., ,Rm} of R is said to be dependency-preserving with respect to F if the union of the projections of F on each Ri , in D is equivalent to F.
In other words, R ⊂ join of R1, R1 over X. The dependencies are preserved because each dependency in F represents a constraint on the database. If decomposition is not dependency-preserving, some dependency is lost in the decomposition.
→ R1(A,B) is covered A→ B
→ R2(C,D) is covered C→ D
It is Functional Dependency preserving because both the functional dependencies are covered.
Lossless join:
The decomposition is a lossless-join decomposition of R if at least one of the following functional dependencies are in F+ (where F+ stands for the closure for every attribute or attribute sets in F):
R1 ∩ R2 → R1
R1 ∩ R2 → R2
According to functional dependency R1(A,B) ∩ R2(C,D) = null. There is no common key in both the tables. So, it is not lossless join.
Question 19 |
Every time the attribute A appears, it is matched with the same value of attribute B but not the same value of attribute C. Which of the following is true?
A→ (B,C) | |
A → B, A→→ C | |
A→ B, C→→A | |
A→→B, B→ C |
Question 19 Explanation:
→ represents functional dependency and
→→ is multivalued dependency.
Functional Dependency:
A → B means that the values of B are determined by the values of A. Two tuples sharing the same values of A will necessarily have the same values of B.
Multivalued dependency: It is a special case of a join dependency, with only two sets of values involved, i.e. it is a binary join dependency.
A multivalued dependency exists when there are at least three attributes (like A,B and C) in a relation and for a value of A there is a well defined set of values of B and a well defined set of values of C. However, the set of values of B is independent of set C and vice versa.
→→ is multivalued dependency.
Functional Dependency:
A → B means that the values of B are determined by the values of A. Two tuples sharing the same values of A will necessarily have the same values of B.
Multivalued dependency: It is a special case of a join dependency, with only two sets of values involved, i.e. it is a binary join dependency.
A multivalued dependency exists when there are at least three attributes (like A,B and C) in a relation and for a value of A there is a well defined set of values of B and a well defined set of values of C. However, the set of values of B is independent of set C and vice versa.
There are 19 questions to complete.