Closure of attributes in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Attribute Closure

Given a set of attributes S = {A, B} and functional dependencies F = {A → C, B → D}, what is the closure of S (S⁺)?

A {A, B, C, D}

B {A, B, C}

C {A, B, D}

D {A, B}

💡 Hint

Remember that closure includes all attributes you can reach using the functional dependencies starting from the given set.

The closure of S = {A, B} includes A and B themselves. Using A → C, we add C. Using B → D, we add D. So, S⁺ = {A, B, C, D}.

📋 Factual

intermediate

Properties of Attribute Closure

Which of the following statements about attribute closure is always true?

A The closure of an attribute set is always equal to the entire set of attributes in the relation.

B The closure of an attribute set never contains attributes outside the relation.

C The closure of an attribute set always contains the original set.

D The closure of an attribute set can be empty.

💡 Hint

Think about what closure means in terms of starting attributes.

By definition, the closure of an attribute set includes the attributes in the original set plus any attributes that can be functionally determined from it. So, it always contains the original set.

🔍 Analysis

advanced

Calculating Closure with Multiple Dependencies

Given relation R(A, B, C, D, E) and functional dependencies:

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

What is the closure of {A}?

A {A, B, C}

B {A, B, C, D, E}

C {A, B, C, D}

D {A, B}

💡 Hint

Apply the dependencies step-by-step starting from A.

Starting with {A}, apply A → B to add B. Then B → C adds C. Next, C → D adds D. Finally, D → E adds E. So closure is {A, B, C, D, E}.

❓ Comparison

advanced

Comparing Closures of Different Attribute Sets

Given relation R(A, B, C) with functional dependencies:
1. A → B
2. B → C

Which attribute set has the larger closure?

A Closures of {A} and {B} are equal

B Closure of {B} is larger than closure of {A}

C Closures of {A} and {B} contain only their own attributes

D Closure of {A} is larger than closure of {B}

💡 Hint

Calculate both closures step-by-step.

Closure({A}) = {A, B, C} because A → B and B → C. Closure({B}) = {B, C} because B → C. So closure of {A} is larger.

❓ Reasoning

expert

Determining Candidate Keys Using Closure

Relation R(A, B, C, D) has functional dependencies:
1. A → B
2. B → C
3. C → A

Which of the following attribute sets is a candidate key for R?

A {A, D}

B {D}

C {C, D}

D {B, D}

💡 Hint

Check which sets' closure includes all attributes of R.

Closure({A, D}) includes A, B (A→B), C (B→C), and D itself, so it covers all attributes. Similarly, {B, D} and {C, D} also cover all attributes due to cyclic dependencies among A, B, C. But {D} alone does not. However, minimality matters: {A, D} is minimal and a candidate key.

Practice

1. What does the closure of attributes represent in database design?

easy

A. The total number of tables in a database

B. All attributes functionally determined by a given set of attributes

C. The list of all primary keys in a database

D. The count of foreign keys referencing a table

Solution

1. Step 1: Understand the concept of closure

   The closure of a set of attributes includes all attributes that can be functionally determined from it using given dependencies.

2. Step 2: Relate closure to database design

   This helps identify all attributes dependent on a set, useful for normalization and design.

3. Final Answer:

   All attributes functionally determined by a given set of attributes -> Option B

4. Quick Check:

   Closure = all functionally determined attributes [OK]

Hint: Closure means all attributes you can find from given ones [OK]

Common Mistakes:

• Confusing closure with number of tables
• Thinking closure lists keys only
• Mixing closure with foreign key counts

2. Which of the following is the correct notation for the closure of attribute set A given functional dependencies F?

easy

A. A+

B. F+

C. A*

D. F*

Solution

1. Step 1: Recall standard notation

   The closure of an attribute set A under functional dependencies F is denoted as A+.

2. Step 2: Differentiate from other notations

   F+ denotes closure of the set of functional dependencies, not attributes. A* and F* are not standard notations here.

3. Final Answer:

   A+ -> Option A

4. Quick Check:

   Attribute closure = A+ notation [OK]

Hint: Closure of attributes is always written as A+ [OK]

Common Mistakes:

• Using F+ for attribute closure
• Confusing closure notation with star (*)
• Mixing attribute and dependency closures

3. Given attributes A, B, C, D and functional dependencies:
A -> B, B -> C, C -> D. What is the closure of {A}?

medium

A. {A, B, C, D}

B. {A, B, C}

C. {A, B}

D. {A, D}

Solution

1. Step 1: Start with initial set {A}

   Closure begins with {A}.

2. Step 2: Apply functional dependencies repeatedly

   From A -> B, add B; from B -> C, add C; from C -> D, add D.

3. Final Answer:

   {A, B, C, D} -> Option A

4. Quick Check:

   Applying all dependencies gives full set [OK]

Hint: Keep adding attributes until no new ones appear [OK]

Common Mistakes:

• Stopping after first dependency
• Forgetting to add transitive dependencies
• Confusing closure with just initial attributes

4. Identify the error in this closure calculation: Given A -> B, B -> C, and starting with {A}, the closure is calculated as {A, C}.

medium

A. Closure should be empty

B. Incorrect initial attribute set

C. Wrong functional dependencies used

D. Missed adding attribute B before C

Solution

1. Step 1: Check closure calculation steps

   Starting with {A}, applying A -> B adds B first.

2. Step 2: Verify transitive dependencies

   Only after B is added can B -> C add C. Skipping B is incorrect.

3. Final Answer:

   Missed adding attribute B before C -> Option D

4. Quick Check:

   Closure must add intermediate attributes [OK]

Hint: Add attributes step-by-step, don't skip intermediates [OK]

Common Mistakes:

• Skipping intermediate attributes
• Assuming direct jump to last attribute
• Ignoring order of dependencies

5. Given attributes A, B, C, D, E and functional dependencies:
A -> BC, CD -> E, B -> D. What is the closure of {A, D}?

hard

A. {A, C, D, E}

B. {A, B, C, D}

C. {A, B, C, D, E}

D. {A, B, C, E}

Solution

1. Step 1: Start with {A, D}

   Initial closure is {A, D}.

2. Step 2: Apply A -> BC

   Add B and C, closure becomes {A, B, C, D}.

3. Step 3: Apply B -> D

   D already included, no change.

4. Step 4: Apply CD -> E

   Since C and D are in closure, add E.

5. Final Answer:

   {A, B, C, D, E} -> Option C

6. Quick Check:

   All attributes reachable from {A, D} included [OK]

Hint: Use all dependencies repeatedly until no new attributes [OK]

Common Mistakes:

• Not applying CD -> E after adding C
• Forgetting to add B and C from A
• Stopping closure too early