GATE2025_CS1_Q47

Consider a relational schema team(name, city, owner), with functional dependencies {name → city, name → owner}.

The relation team is decomposed into two relations, t1(name, city) and t2(name, owner). Which of the following statement(s) is/are TRUE?

Note: This is a Multiple Select Question (MSQ). One or more options may be correct.

✅ Final Answer:

The correct options are (B) and (C).

🔹 Short Answer:

The only candidate key for the relation team is {name}. Since the left side of both functional dependencies (name → city and name → owner) is a superkey, the original relation is in BCNF, making statements (A) and (D) false. The decomposition into t1(name, city) and t2(name, owner) results in two relations that are also in BCNF. Furthermore, the decomposition is lossless because the intersection of their attributes, {name}, is a key for both. Thus, statements (B) and (C) are true.

🔸 Long Answer:

To solve this, we need to analyze the properties of the original relation and its decomposition based on the given functional dependencies (FDs).

Step 1: Find the Candidate Key for `team(name, city, owner)`

The given FDs are {name → city, name → owner}. Let's find the closure of the attribute name:

• {name}⁺ = {name} (initially)
• Using name → city, we add city: {name}⁺ = {name, city}
• Using name → owner, we add owner: {name}⁺ = {name, city, owner}

Since the closure of {name} contains all attributes of the relation, {name} is the only candidate key.

Step 2: Check the Normal Form of the original relation `team`

BCNF Condition: A relation is in BCNF if for every non-trivial FD X → Y, X is a superkey.

• For FD name → city, the left-hand side (LHS) is {name}, which is a superkey. The condition holds.
• For FD name → owner, the LHS is {name}, which is also a superkey. The condition holds.

Since all FDs satisfy the BCNF condition, the relation team is in BCNF. This makes statement (A) FALSE.

3NF Condition: A relation is in 3NF if for every non-trivial FD X → Y, either X is a superkey OR Y is a prime attribute (part of a candidate key). Since every relation in BCNF is also in 3NF, team is also in 3NF. This makes statement (D) FALSE.

Step 3: Analyze the Decomposed Relations

The decomposition is into t1(name, city) and t2(name, owner).

• For `t1(name, city)`: The only applicable FD is name → city. The candidate key is {name}. Since the LHS of the FD is a superkey of `t1`, the relation `t1` is in BCNF.
• For `t2(name, owner)`: The only applicable FD is name → owner. The candidate key is {name}. Since the LHS of the FD is a superkey of `t2`, the relation `t2` is in BCNF.

Therefore, statement (B) is TRUE.

Step 4: Check for Lossless Join Property

A decomposition of a relation R into R1 and R2 is lossless if it satisfies two conditions:

1. The union of attributes of R1 and R2 must be equal to the attributes of R.
   Attr(t1) ∪ Attr(t2) = {name, city} ∪ {name, owner} = {name, city, owner}. This matches Attr(team). Condition is met.
2. The intersection of attributes of R1 and R2 must be a superkey for either R1 or R2.
   Attr(t1) ∩ Attr(t2) = {name}. Is {name} a superkey of t1 or t2? Yes, we found it is the candidate key for both. Condition is met.

Since both conditions are met, the decomposition is a lossless join. This makes statement (C) TRUE.

Bibliography:
- Elmasri, R., & Navathe, S. B. (2017). Fundamentals of Database Systems. Pearson. (Chapters 15 & 16: Functional Dependencies, Normalization, and Relational Decomposition).
- Korth, H. F., Silberschatz, A., & Sudarshan, S. (2010). Database System Concepts. McGraw-Hill. (Chapter 8: Relational Database Design).

Ask your doubts in comment form our GURUJEE will reply ASAP, click notify me button so that you get notified on reply.