Practice Questions on Functional Dependencies, Normal Forms, and Decomposition for GATE CSE

Question 1

Consider a relation schema S = (P, Q, R, T, U, V) with the following functional dependencies:

P → Q
P → R
QR → V
QR → U
Q → T

Which of the following is a candidate key of S?

Correct Answer: Option D (PQ)

Solution:

To determine the candidate key, find the closure of each option:

PR: Closure of PR is {P, R, Q, T}, which does not include all attributes.
PRV: Closure of PRV is {P, R, Q, T, U, V}, but it is not minimal as PRV includes redundant attributes.
QRT: Closure of QRT is {Q, R, T, U, V}, which does not include P.
PQ: Closure of PQ is {P, Q, R, T, U, V}, covering all attributes. Since PQ is minimal, it is a candidate key.

Hence, PQ is the candidate key.

Question 2

Consider a relational schema T(X, Y, Z, W, V) with the following functional dependencies:

X → YZ
YW → V
Z → W
V → X

Which of the following is true for the decomposition of T into (X, Y, Z) and (Y, W, V)?

Lossless Join and Dependency Preserving
Lossless Join but not Dependency Preserving
Lossy Join and Dependency Preserving
Neither Lossless Join nor Dependency Preserving

Correct Answer: Option D (Neither Lossless Join nor Dependency Preserving)

Solution:

The decomposition divides T into two relations:

T1 = (X, Y, Z)
T2 = (Y, W, V)

To check for lossless join:

The common attribute is Y. However, Y is not a key for T1 or T2. Hence, the decomposition is not lossless.

To check for dependency preservation:

The dependencies Z → W and V → X are lost in the decomposition. Therefore, the decomposition is not dependency preserving.

Hence, the decomposition is neither lossless join nor dependency preserving.

Question 3

Consider a relation schema T(P, Q, R, S) with the following functional dependencies:

P → S
PQ → R
PR → Q
S → P
S → Q

Which of the following is true for T?

In 1NF but not in 2NF
In 2NF but not in 3NF
In 3NF but not in BCNF
In BCNF

Correct Answer: Option B (In 2NF but not in 3NF)

Solution:

The candidate key for T is P, S.

To check the normal form:

The relation is in 2NF because all non-prime attributes are fully functionally dependent on the candidate key.
However, the dependency S → Q violates the condition for 3NF as S is not a superkey and Q is not a prime attribute.

Thus, the relation is in 2NF but not in 3NF.

Question 4

Consider the relation R(P, Q) where {PQ} is the primary key and the relation S(P, R) where P is the primary key. Assume there are no null values and no foreign keys or integrity constraints.

Query 1: SELECT P FROM R WHERE P IN (SELECT P FROM S);

Query 2: SELECT P FROM S WHERE P IN (SELECT P FROM R);

Which of the following is correct about the queries?

Both queries will always give the same result.
Both queries will always give different results.
Both queries may give the same result.
None of these

Correct Answer: Option C (Both queries may give the same result)

Solution:

For the given queries:

If the values in column P are unique in both relations R and S, then the output of both queries will be the same.
However, if there are missing or extra values for P in either table, the results may differ.

Thus, the queries may give the same result, but it is not guaranteed in all cases.

Question 11

Consider the relation S(P, Q, R, S, T) with the following functional dependencies:

P → Q
Q → R
R → P
S → T
T → S

What is the maximum possible number of superkeys for S?

Answer: ________

Solution:

Candidate keys of S: PR, PS, QS, RT.

Every superkey must contain at least one element from the sets {P, Q, R} and {S, T}. Using combinations:

From {P, Q, R}: Choose 1, 2, or all 3 attributes: C(3,1) + C(3,2) + C(3,3) = 3 + 3 + 1 = 7
From {S, T}: Choose 1 or both attributes: C(2,1) + C(2,2) = 2 + 1 = 3

Total number of superkeys = 7 × 3 = 21.

Final Answer: 21

Question

Which of the following relation schemas with the given functional dependencies is in BCNF?

S(P, Q, R, S) with FD’s PQ → R, R → S, and S → P
S(P, Q, R, S) with FD’s P → Q, Q → R, R → S, and S → P
S(P, Q, R, S, T) with FD’s PQ → R, R → S, and S → T
S(P, Q, R) with FD’s P → Q and Q → R

Correct Answer: None

Solution:

Which Relation Schemas with the Given Functional Dependencies are in BCNF?

Let’s analyze each schema to determine whether it satisfies the conditions of Boyce-Codd Normal Form (BCNF).

Schema 1: S(P, Q, R, S)

Functional Dependencies (FDs):

PQ → R
R → S
S → P

Candidate Key:

PQ: From PQ → R, R → S, and S → P, we can determine all attributes P, Q, R, and S.
Hence, PQ is the candidate key.

Check for BCNF Violations:

PQ → R: PQ is the candidate key. No BCNF violation.
R → S: R is not a candidate key. BCNF is violated.
S → P: S is not a candidate key. BCNF is violated.

Conclusion: This schema is NOT in BCNF.

Schema 2: S(P, Q, R, S)

Functional Dependencies (FDs):

P → Q
Q → R
R → S
S → P

Candidate Key:

P: From P → Q, Q → R, R → S, and S → P, we can determine all attributes P, Q, R, and S.
Hence, P is the candidate key.

Check for BCNF Violations:

P → Q: P is the candidate key. No BCNF violation.
Q → R: Q is not a candidate key. BCNF is violated.
R → S: R is not a candidate key. BCNF is violated.
S → P: S is not a candidate key. BCNF is violated.

Conclusion: This schema is NOT in BCNF.

Schema 3: S(P, Q, R, S, T)

Functional Dependencies (FDs):

PQ → R
R → S
S → T

Candidate Key:

PQ: From PQ → R, R → S, and S → T, we can determine all attributes P, Q, R, S, and T.
Hence, PQ is the candidate key.

Check for BCNF Violations:

PQ → R: PQ is the candidate key. No BCNF violation.
R → S: R is not a candidate key. BCNF is violated.
S → T: S is not a candidate key. BCNF is violated.

Conclusion: This schema is NOT in BCNF.

Schema 4: S(P, Q, R)

Functional Dependencies (FDs):

P → Q
Q → R

Candidate Key:

P: From P → Q and Q → R, we can determine all attributes P, Q, and R.
Hence, P is the candidate key.

Check for BCNF Violations:

P → Q: P is the candidate key. No BCNF violation.
Q → R: Q is not a candidate key. BCNF is violated.

Conclusion: This schema is NOT in BCNF.

Final Answer:

None of the given schemas satisfy the conditions for BCNF.

Question

Consider a relation schema S(P, Q, R, S) with the following two sets of functional dependencies:

H: P → Q, Q → P, PR → S, S → R
G: PQ → R, RP → S, RS → Q

Which of the following is true?

G covers H
H covers G
H and G are equivalent
Neither H covers G nor vice versa

Correct Answer: Option B (H covers G)

Solution:

Relation Schema S(P, Q, R, S) with Functional Dependencies H and G

We are given two sets of functional dependencies:

Set H:

P → Q
Q → P
PR → S
S → R

Set G:

PQ → R
RP → S
RS → Q

Objective:

We need to determine which of the following is true:

G covers H
H covers G
H and G are equivalent
Neither H covers G nor vice versa

Step 1: Does G cover H?

To check if G covers H, we must verify if every dependency in H can be derived using G.

Dependency P → Q from H:In G, we have RS → Q, but there is no direct P → Q or a way to derive Q from P alone. Therefore, P → Q is not derivable from G.
Dependency Q → P from H:Similarly, there is no direct Q → P in G, and it is not derivable.
Dependency PR → S from H:RP → S exists in G, so PR → S is derivable from G.
Dependency S → R from H:S → R is not directly in G, nor can it be derived. Therefore, S → R is not derivable from G.

Conclusion: G does not cover H because dependencies P → Q, Q → P, and S → R are not derivable from G.

Step 2: Does H cover G?

To check if H covers G, we must verify if every dependency in G can be derived using H.

Dependency PQ → R from G:H contains P → Q and PR → S, but there is no direct or transitive derivation of PQ → R in H. Therefore, PQ → R is not derivable from H.
Dependency RP → S from G:PR → S exists in H, so RP → S is derivable from H.
Dependency RS → Q from G:RS → Q is not present in H and cannot be derived from it. Therefore, RS → Q is not derivable from H.

Conclusion: H does not cover G because dependencies PQ → R and RS → Q are not derivable from H.

Step 3: Are H and G equivalent?

For H and G to be equivalent, H must cover G, and G must cover H. Since neither covers the other, H and G are not equivalent.

Step 4: Final Answer

Since neither H covers G nor G covers H, the correct answer is:

Neither H covers G nor vice versa.

Question

Consider a relation T(P, Q, R, S) with only one candidate key. Which of the following is always true?

T is in both 3NF and BCNF
T is in 2NF but not in 3NF
T is in 3NF but not in BCNF
None of these

Correct Answer: Option D (None of these)

Solution:

Consider a specific example:

T(P, Q, R, S) with FDs P → R and R → S.
The only candidate key is P.
The relation is not in 2NF, as R (a non-prime attribute) depends on part of the candidate key P.

Thus, none of the options A, B, or C is always correct.

Final Answer: None of these.

Question

Consider the following statements:

A prime attribute of a relation schema S is an attribute that appears in some candidate key of S.
If every candidate key of a relation is simple, then the relation will always be in 3NF.

Which of the above statement(s) is/are correct?

I only
II only
Both I and II
None of these

Correct Answer: Option D (None of these)

Solution:

Statement I: Incorrect. A prime attribute is one that appears in some candidate key, not necessarily all candidate keys.

Statement II: Incorrect. Even if all candidate keys are simple, the relation may not be in 3NF because transitive dependencies can exist, which violate 3NF.

Final Answer: None of these.

Question

Consider the following table with four possible candidate keys:

P	Q	R	S
a₁	b₁	c₁	d₁
a₂	b₃	c₃	d₂
a₂	b₂	c₁	d₁

The possible candidate keys are:

I: {Q, R}
II: {Q}
III: {P, S}
IV: {R, S}

Which of the above cannot be the candidate key for the given table?

I and IV only
I only
I and III only
III only

Correct Answer: Option B (I only)

Solution:

Analysis:

I: {Q, R}: This cannot be a candidate key because the combination {Q, R} does not uniquely identify all rows in the table.
II: {Q}: This is a candidate key because it uniquely identifies each row.
III: {P, S}: This is a candidate key because the combination {P, S} uniquely identifies all rows.
IV: {R, S}: This is a candidate key because the combination {R, S} uniquely identifies all rows.

Final Answer: I only.

Question

If every non-key attribute is functionally dependent on the primary key, then the relation will always be in:

1NF
2NF
3NF
BCNF

Correct Answer: Option C (3NF)

Solution:

If every non-key attribute is functionally dependent on the primary key, then the relation satisfies the conditions for 3NF.

The relation will be in 2NF because there are no partial dependencies.
The relation will also be in 3NF as there are no transitive dependencies involving non-key attributes.
However, it may not be in BCNF because prime attributes may still depend on non-prime attributes.

Final Answer: 3NF

Question

Which of the following statements is false about a weak entity set?

A weak entity set has no primary keys unless attributes of the strong entity set on which it depends are included.
Weak entities can be deleted automatically when their strong entity is deleted.
Tuples in a weak entity set are not participated according to their relationship with tuples in a strong entity set.
None of these

Correct Answer: Option C (Tuples in a weak entity set are not participated according to their relationship with tuples in a strong entity set)

Solution:

A weak entity set:

Has no primary key unless it includes attributes of the strong entity set.
Is dependent on the strong entity set, so deletion of the strong entity can result in deletion of the weak entity.
Participates in a relationship with tuples in the strong entity set, ensuring referential integrity.

Option C is false because tuples in a weak entity set must be associated with tuples in the strong entity set.

Final Answer: Tuples in a weak entity set are not participated according to their relationship with tuples in a strong entity set.

Question

Consider a relation T(P, Q, R) with the functional dependencies:

P → Q
Q → R
R → Q

Which of the following is correct about the relation T?

Dependency-preserving decomposition is always possible for T.
Lossless decomposition is always possible for T.
Both I and II
Neither I nor II

Correct Answer: Option B (II only)

Solution:

For the given functional dependencies:

Lossless decomposition: It is always possible as per the decomposition properties.
Dependency-preserving decomposition: It may not always be possible. For example, if decomposed into {P, Q} and {Q, R}, dependencies such as R → Q are not preserved in the projections.

Thus, only lossless decomposition is guaranteed.

Final Answer: II only.

Question

Consider a relation S(P, Q, R, S) with the functional dependencies:

P → S
Q → S
S → QR

What is the minimum number of tables required to decompose the relation S into BCNF?

Correct Answer: Option B (2)

Solution:

The candidate key for the given relation is P. To achieve BCNF, the relation must be decomposed as follows:

Table 1: {P, S} with P → S
Table 2: {S, Q, R} with S → QR

Both tables satisfy the BCNF conditions. Thus, a minimum of 2 tables are required.

Final Answer: 2.

Question

Consider a relation schema S(P, Q, R, S, T) with the following functional dependencies:

PQ → R
QR → S
RS → P
PS → T

What is the total number of superkeys in the relation S?

Answer: ________

Solution:

The candidate keys for the relation S are:

{P, Q, S}
{Q, R, S}
{R, S, T}
{P, S, T}

Using the candidate keys, we can calculate the total number of superkeys by considering all possible supersets of these candidate keys, eliminating duplicates:

Each candidate key generates multiple superkeys, but some overlap (e.g., {P, Q, S, T}) occurs across keys.

After accounting for overlaps, the total number of unique superkeys is 9.

Final Answer: 9

Question

Consider a relation schema T(P, Q, R, S) with the following functional dependencies:

P → Q
Q → R
R → S
S → P

The relation is decomposed into:

T₁(P, Q)
T₂(Q, R)
T₃(R, S)

Which of the following is true about this decomposition?

Lossless join and dependency preserving
Lossless join but not dependency preserving
Dependency preserving but not lossless join
Not dependency preserving and not lossless join

Correct Answer: Option A (Lossless join and dependency preserving)

Solution:

The decomposition is as follows:

Table T₁: Contains the dependency P → Q.
Table T₂: Contains the dependency Q → R.
Table T₃: Contains the dependency R → S.

All functional dependencies are preserved across the tables, and the decomposition is lossless because the shared attributes ensure no information is lost during the join operation.

Final Answer: Lossless join and dependency preserving.

Question

Consider the following statements:

All attributes of a relation form a superkey.
A superkey is also a candidate key.
For any SQL query, there exists a unique translation into relational algebra.

Which of the above statement(s) is/are correct?

I and II only
I only
I and III only
II and III only

Correct Answer: Option B (I only)

Solution:

Statement I: Correct. All attributes of a relation together form a superkey, as they can uniquely identify any tuple in the relation.
Statement II: Incorrect. A superkey may not always be a candidate key, as it might contain extraneous attributes.
Statement III: Incorrect. For any SQL query, there might be multiple equivalent translations into relational algebra due to the non-deterministic nature of optimization techniques.

Final Answer: I only.

Question

Consider the following table:

A	B	C
a₁	b₁	c₁
a₁	b₁	c₂
a₂	b₁	c₁
a₂	b₁	c₃

How many non-trivial functional dependencies exist in the above table?

Answer: ________

Solution:

Analyzing the table, the following functional dependencies are observed:

A → B: Attribute B is uniquely determined by A.
C → B: Attribute B is uniquely determined by C.
AC → B: The combination of attributes A and C uniquely determines B.

These are the 3 non-trivial functional dependencies present in the table.

Final Answer: 3

Question

Consider the relation schema T(P, Q, R, S, T, U, V) with the following functional dependencies:

PQ → RS
RS → Q
QT → U
UV → P
V → T

What is the highest normal form that the relation satisfies?

Correct Answer: Option C (3NF)

Solution:

The candidate keys for the relation are:

{PQ, QT, UV}
All attributes are either part of a candidate key or functionally dependent on it.

No partial or transitive dependencies exist, which satisfies the conditions for 2NF and 3NF. However, since there are dependencies like UV → P, the relation does not satisfy BCNF.

Final Answer: The highest normal form is 3NF.

Question

Which of the following statements is false?

A relation where every attribute is prime is always in 3NF.
A relation where every candidate key is simple is always in 2NF.
A relation where every attribute is prime is always in BCNF.
A relation in 3NF with at most one compound candidate key is also in BCNF.

Correct Answer: Option C (A relation where every attribute is prime is always in BCNF)

Solution:

Statement A: True. If all attributes are prime, no partial or transitive dependencies exist, satisfying 3NF.
Statement B: True. If all candidate keys are simple, no partial dependencies exist, satisfying 2NF.
Statement C: False. Even if every attribute is prime, there might still be dependencies that violate BCNF, such as AB → C and C → A.
Statement D: True. A relation in 3NF with one compound candidate key typically satisfies BCNF conditions.

Final Answer: Statement C is false.

Question

Consider the relation schema R(A, B, C, D, E, F) with the following functional dependencies:

AB → C
AC → B
AD → E
B → D
BC → A
E → F

Consider the decomposition of R into:

R₁(ABC)
R₂(ABDE)
R₃(EF)

Which of the following is true about the decomposition?

Dependency preserving and lossless join.
Dependency preserving but lossy join.
Not dependency preserving but lossless join.
Neither dependency preserving nor lossless join.

Correct Answer: Option A (Dependency preserving and lossless join)

Solution:

The decomposition satisfies the following:

Dependency preservation: All functional dependencies are preserved across R₁, R₂, and R₃.
Lossless join: The intersection of R₁ and R₂ contains AB, which is a candidate key for R₁. Similarly, E is a candidate key for R₃, ensuring no information loss.

Final Answer: The decomposition is dependency preserving and lossless.

Question

Consider the relation schema R(A, B, C, D, E, F) with the following functional dependencies:

AB → C
AC → B
AD → E
B → D
BC → A
E → F

Consider the decomposition of R into:

R₁(ABC)
R₂(ABDE)
R₃(EF)

Which of the following is true about the decomposition?

Dependency preserving and lossless join.
Dependency preserving but lossy join.
Not dependency preserving but lossless join.
Neither dependency preserving nor lossless join.

Correct Answer: Option A (Dependency preserving and lossless join)

Solution:

The decomposition satisfies the following:

Dependency preservation: All functional dependencies are preserved across R₁, R₂, and R₃.
Lossless join: The intersection of R₁ and R₂ contains AB, which is a candidate key for R₁. Similarly, E is a candidate key for R₃, ensuring no information loss.

Final Answer: The decomposition is dependency preserving and lossless.

Question

Consider the relation schema R(A, B, C, D, E, F, G, H) with the following functional dependencies:

AC → G
D → EG
BC → D
CG → B
ACD → B
CE → AG

What is the number of different minimal covers possible?

Answer: ________

Solution:

To compute the minimal covers:

Start with the closure of AC: (AC)+ = ABCD.
The extraneous attribute D is removed, simplifying ACD → B.
Four minimal covers are possible by considering different permutations of the dependencies:
- {AC → G, D → EG, BC → D, CG → B, CE → AG}
- {AC → B, D → EG, BC → D, CE → AG}
- {AC → G, D → EG, BC → D, CE → AG}
- {AC → B, D → EG, BC → D, CG → B, CE → AG}

Final Answer: 4 different minimal covers are possible.

Question 1:

Consider the following relation with functional dependencies:

R(X, Y, Z, W, U, V)

Functional Dependencies:

X → YZ
YZ → W
U → Y
WV → XU
ZW → YX

What is the number of candidate keys for the relation R?

Options:

A. 1
B. 2
C. 3
D. 4

Correct Answer:

B. 2

Solution:

The given relation is R(X, Y, Z, W, U, V) with the following functional dependencies:

X → YZ
YZ → W
U → Y
WV → XU
ZW → YX

To determine the candidate keys, calculate the closure of attributes:

(X, U)⁺ = {X, Y, Z, W, U, V}
(W, U)⁺ = {X, Y, Z, W, U, V}

Thus, there are exactly 2 candidate keys: {XU} and {WU}.

Final Answer: 2

Question 2:

Consider A(P, Q, R, S, T, W) and the following functional dependencies:

W → V
T → S
WS → RT
QS → P

Which of the following is the minimal cover of the given FDs?

Options:

A. {W → V, W → S, T → S, WS → RT, QS → P}
B. {W → V, W → S, T → S, W → R, QS → P}
C. {W → V, T → S, W → R, WS → R, QS → P}
D. {W → V, T → S, W → R, W → T, QS → P}

Correct Answer:

D. {W → V, T → S, W → R, W → T, QS → P}

Solution:

Given functional dependencies are:

W → V
T → S
WS → RT
QS → P

Breaking down WS → RT:

WS → R
W → T

Checking QS → P:

QS⁺ = {Q, S, P}
QS → P is essential.

Final minimal cover:

W → V
T → S
W → R
W → T
QS → P

Final Answer: D

Q1: Consider the following statements:

S₁: If every attribute is a prime attribute in a relation R, then R will always be in BCNF.
S₂: If relation R is in 3NF and every candidate key is simple, then R will be in BCNF.

Which of the above statements is correct?

Only S₁
Only S₂
Both S₁ and S₂
None of these

Correct Answer: B

Solution:

S₁: If every attribute is prime, it does not necessarily imply that the LHS of functional dependencies is a key. For example, consider R(ABCD) with FDs {AB → D, C → A, D → C}. Here, every attribute is prime, but R is not in BCNF.
S₂: If a relation R is in 3NF and every candidate key is simple (consists of a single attribute), then R will satisfy the conditions for BCNF. Hence, S₂ is correct.

Q2: Consider the following relation with functional dependencies:

P(R, S, T, U, V)

F = {RS → T, RS → U, U → R, ST → U, ST → V}

What is the highest normal form of the relation P?

2NF
3NF
BCNF
None of these

Correct Answer: B

Solution:

Closure of RS⁺ = {R, S, T, U, V}
Closure of SU⁺ = {S, U, R, T, V}
Closure of ST⁺ = {S, T, U, R, V}

The candidate keys are {RS, SU, ST}, and no functional dependency violates the rules of 3NF. However, U → R is not a superkey, so the relation is not in BCNF but satisfies 3NF.

Q3: Assume a table P has only one candidate key. Which of the following is always true about P?

P is in both 3NF and BCNF
P is in 3NF but may not be in BCNF
P is in 2NF but may not be in 3NF
None of these

Correct Answer: D

Solution:

Consider relation P(A, B, C, D) with functional dependencies:

A → C
B → D
(AB)⁺ = {A, B, C, D}

The candidate key is AB. Since partial dependencies exist (A → C, B → D), the table is not in 2NF, 3NF, or BCNF. Hence, the correct answer is None of these.

Q1:

Consider the following statements regarding relational schemas:

S₁: If every attribute in a relation is a prime attribute, the relation will always be in BCNF.
S₂: If a relation is in 3NF and every candidate key is simple, then the relation will also be in BCNF.

Which of the above statements is correct?

Only S₁
Only S₂
Both S₁ and S₂
None of the above

Correct Answer: B

Solution:

S₁: Even if every attribute in a relation is a prime attribute, it does not guarantee that all functional dependencies will have the left-hand side as a superkey. For instance, in a relation R(A, B, C, D) with dependencies {AB → D, C → A, D → C}, all attributes are prime, but the relation is not in BCNF.

S₂: If a relation is in 3NF and all candidate keys are simple (single attributes), then it satisfies BCNF because no partial or transitive dependencies can exist. Thus, S₂ is correct.

Q2:

Given a relation P(R, S, T, U, V) with the following functional dependencies:

RS → T
RS → U
U → R
ST → U
ST → V

What is the highest normal form of the given relation?

2NF
3NF
BCNF
None of the above

Correct Answer: B

Solution:

The candidate keys are RS, ST, and SU.
U → R violates the BCNF condition as U is not a superkey. Hence, the relation is not in BCNF.
All functional dependencies satisfy 3NF since every non-prime attribute is dependent on a superkey or is part of a candidate key. Therefore, the highest normal form is 3NF.

Q3:

A relation Q(A, B, C, D) has the following functional dependencies:

A → B
C → D

If (A, C) is the only candidate key, which of the following is true?

The relation is in 1NF but not in 2NF.
The relation is in 2NF but not in 3NF.
The relation is in 3NF but not in BCNF.
The relation is in BCNF.

Correct Answer: C

Solution:

The candidate key (A, C) ensures there are no partial dependencies, satisfying 2NF. However, the dependency A → B violates the BCNF condition as A is not a superkey. Thus, the relation is in 3NF but not in BCNF.

Question 7:

Consider a relation with 4 attributes. What is the number of maximum possible candidate keys?

A. 4
B. 5
C. 6
D. 7

Correct Answer:

C. 6

Solution:

Candidate keys are maximum if every two attributes form a candidate key.

Explanation:

If each attribute is a candidate key, the maximum possible is calculated as \( ^4C_2 = 6 \).
If every three attributes form a candidate key, the maximum possible is \( ^4C_3 = 4 \).

Question 8:

Consider the following relation with given functional dependencies:

R(A, B, C, D, E, F)

\{A → BC, CD → E, E → C, D → AEF, ABF → BD, DF → BC\}

What is the number of candidate keys for relation R?

A. 1
B. 2
C. 3
D. 4

Correct Answer:

B. 2

Solution:

The candidate keys for the given relation are:

\((AF)^+ = ABCDEF\)
\((D)^+ = ABCDEF\)

Hence, there are only two candidate keys for relation R.

Question 10:

Given the following instance of a relation:

X	Y	Z
2	8	4
10	2	6
2	12	4
6	4	6

Which of the following FDs are satisfied by the relation?

A. XY → Z, Z → Y
B. YZ → X, Y → Z
C. YZ → X, X → Z
D. XZ → Y, Y → X

Correct Answer:

B. YZ → X, Y → Z

Solution:

Y and Z uniquely determine X, and Y uniquely determines Z.
Thus, \( YZ → X \) and \( Y → Z \) are the only valid functional dependencies.

Q.7

Consider a relation with 4 attributes. What is the number of maximum possible candidate keys?

A) 4
B) 5
C) 6
D) 7

Correct Answer: C

Solution:

Candidate keys are maximized if every two attributes form a candidate key. This is calculated as:

  C(4,2) = 6

If each attribute is a candidate key, the maximum possible keys are:

  C(4,1) = 4

If every three attributes are candidate keys, the maximum possible is:

  C(4,3) = 4

Thus, the maximum number of candidate keys is 6.

Q.8

Consider the following relation with given functional dependencies:

R(A, B, C, D, E, F)

FDs = { A → BC, CD → E, E → C, D → AEF, ABF → BD, DF → BC }

What is the number of candidate keys for relation R?

A) 1
B) 2
C) 3
D) 4

Correct Answer: B

Solution:

The closures of the attributes are calculated as:

(AF)+ = ABCDEF
(D)+ = ABCDEF

Hence, the only two candidate keys for this relation are AF and D.

Q.10

Given the following instance of a relation:

  X | Y | Z
  ---------
  2 | 8 | 4
  10 | 6 | 2
  12 | 6 | 4
  6 | 4 | 8

Which of the following functional dependencies (FDs) are satisfied by the relation?

A) XY → Z, Z → Y
B) YZ → X, Y → Z
C) YZ → X, X → Z
D) XZ → Y, Y → X

Correct Answer: B

Solution:

From the given data:

YZ → X: For every unique combination of Y and Z, X is uniquely determined.
Y → Z: For each Y, Z is uniquely determined.

Hence, the functional dependencies satisfied by the relation are YZ → X and Y → Z.

Q.12

Consider the following relation with given functional dependencies:

R(A, B, C)

FD = { A → BC, B → C, A → B, AB → C }

Which of the following is the canonical cover of relation R?

A) { A → B, B → C, C → A }
B) { A → BC, B → C }
C) { A → B, B → C }
D) None of these

Correct Answer: C

Solution:

Using the decomposition rule:

  { A → B, A → C, B → C, AB → C }

Here, AB → C is redundant, as A → B and B → C cover it. Additionally, A → C is extraneous.

Thus, the canonical cover is { A → B, B → C }.

Q.14

Consider the following relation with given functional dependencies:

X(P, Q, R, S, T)

FD = { P → QR, RS → T, Q → S, T → P }

If X is decomposed, then the decomposition of relation X into X₁(PQR) and X₂(PST) is:

A) Lossless join but not dependency preserving
B) Lossless join and dependency preserving
C) Lossy join but dependency preserving
D) Lossy join and not dependency preserving

Correct Answer: A

Solution:

Relation X is decomposed into:

  X1(PQR) and X2(PST)

P is the common attribute and key for X1, so the decomposition is lossless. However, it is not dependency preserving as:

  Q → S and RS → T are not covered.

Thus, the decomposition is lossless join but not dependency preserving.

Free Workbooks

Question 1

Solution:

Question 2

Solution:

Question 3

Solution:

Question 4

Solution:

Question 11

Solution:

Question

Solution:

Which Relation Schemas with the Given Functional Dependencies are in BCNF?

Schema 1: S(P, Q, R, S)

Schema 2: S(P, Q, R, S)

Schema 3: S(P, Q, R, S, T)

Schema 4: S(P, Q, R)

Final Answer:

Question

Solution:

Relation Schema S(P, Q, R, S) with Functional Dependencies H and G

Set H:

Set G:

Objective:

Step 1: Does G cover H?

Step 2: Does H cover G?

Step 3: Are H and G equivalent?

Step 4: Final Answer

Question

Solution:

Question

Solution:

Question

Solution:

Question

Solution:

Question

Solution:

Question

Solution:

Question

Solution:

Question

Solution:

Question

Solution:

Question

Solution:

Question

Solution:

Question

Solution:

Question

Solution:

Question

Solution:

Question

Solution:

Question

Solution:

Question 1:

Options:

Correct Answer:

Solution:

Question 2:

Options:

Correct Answer:

Solution:

Q1:

Solution:

Q2:

Solution:

Q3:

Solution:

Question 7:

Correct Answer:

Solution:

Question 8:

Correct Answer: