Candidate key finding using closure in DBMS Theory - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a candidate key in a database?

A candidate key is a minimal set of attributes that can uniquely identify a tuple (row) in a relation (table). It means no attribute can be removed without losing the uniqueness property.

beginner

What does the closure of a set of attributes represent?

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

intermediate

How is closure used to find candidate keys?

To find candidate keys, we compute the closure of attribute sets. If the closure contains all attributes of the relation, that set is a superkey. Minimal superkeys are candidate keys.

beginner

What is the difference between a superkey and a candidate key?

A superkey is any set of attributes that uniquely identifies tuples. A candidate key is a minimal superkey, meaning it has no unnecessary attributes.

beginner

Why do we look for minimal attribute sets when finding candidate keys?

Minimal attribute sets avoid redundancy and ensure the key is as simple as possible while still uniquely identifying tuples.

What does the closure of an attribute set tell us?

A The total number of attributes in the relation

B All attributes functionally determined by that set

C The primary key of the table

D The number of tuples in the table

Closure shows all attributes that can be derived from the given attribute set using functional dependencies.

If the closure of an attribute set includes all attributes of the relation, what is it called?

A Superkey

B Foreign key

C Candidate key

D Partial key

A superkey is any attribute set whose closure contains all attributes of the relation.

What makes a candidate key different from a superkey?

A Candidate key has foreign keys

B Candidate key is larger

C Candidate key is not unique

D Candidate key is minimal

Candidate keys are minimal superkeys with no extra attributes.

Which of these is NOT true about candidate keys?

A They can have redundant attributes

B They uniquely identify tuples

C They are minimal

D There can be multiple candidate keys

Candidate keys must be minimal and cannot have redundant attributes.

Why do we use closure to find candidate keys?

A To find the largest attribute set

B To count the number of tuples

C To find all attributes functionally dependent on a set

D To delete redundant data

Closure helps identify if an attribute set can determine all attributes, indicating a superkey or candidate key.

Explain how to use closure to find candidate keys in a relation.

Think about how closure helps find superkeys and then minimal superkeys.

What is the difference between a superkey and a candidate key? Why is minimality important?

Focus on uniqueness and minimal attribute sets.

Practice

1. What does the closure of a set of attributes represent in candidate key finding?

easy

A. All attributes functionally determined by the set

B. The number of attributes in the set

C. The total number of functional dependencies

D. The primary key of the table

Solution

1. Step 1: Understand closure definition

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

2. Step 2: Relate closure to candidate keys

   If the closure covers all attributes in the relation, the set is a superkey; minimal such sets are candidate keys.

3. Final Answer:

   All attributes functionally determined by the set -> Option A

4. Quick Check:

   Closure = attributes reachable from set [OK]

Hint: Closure shows all attributes a set can determine [OK]

Common Mistakes:

• Confusing closure with number of attributes
• Thinking closure is the primary key itself
• Ignoring functional dependencies in closure

2. Which of the following is the correct notation to represent the closure of attribute set {A, B}?

easy

A. {A, B}+

B. {A, B}*

C. closure(A, B)

D. closure{A, B}

Solution

1. Step 1: Recall standard closure notation

   The closure of a set of attributes X is denoted as X⁺, meaning all attributes functionally determined by X.

2. Step 2: Match notation to options

   {A, B}+ uses the correct notation {A, B}+. Other options are not standard or incorrect.

3. Final Answer:

   {A, B}+ -> Option A

4. Quick Check:

   Closure notation uses plus sign [OK]

Hint: Closure notation always uses plus sign after attribute set [OK]

Common Mistakes:

• Using star (*) instead of plus (+)
• Writing closure as a function call
• Misplacing braces or parentheses

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

medium

A. {A, B}

B. {A, B, C}

C. {A, B, C, D}

D. {A}

Solution

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

   Initially, closure({A}) = {A}.

2. Step 2: Apply functional dependencies stepwise

   From A -> B, add B: closure = {A, B}. Then B -> C adds C: closure = {A, B, C}. Finally, C -> D adds D: closure = {A, B, C, D}.

3. Final Answer:

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

4. Quick Check:

   Closure includes all reachable attributes [OK]

Hint: Apply dependencies repeatedly until no new attributes added [OK]

Common Mistakes:

• Stopping after first dependency
• Ignoring transitive dependencies
• Confusing closure with original set

4. Identify the error in this closure calculation for relation R(A, B, C) with FDs:
A -> B, B -> C. Closure of {B} is given as {B, A}. What is wrong?

medium

A. Closure should include all attributes

B. Closure cannot include B itself

C. Closure should be empty

D. Closure should include C, not A

Solution

1. Step 1: Analyze given closure {B, A}

   Starting with {B}, applying B -> C adds C, so closure should be {B, C}.

2. Step 2: Check if A can be added

   There is no FD that allows B to determine A, so A should not be in closure.

3. Final Answer:

   Closure should include C, not A -> Option D

4. Quick Check:

   Closure only adds attributes functionally determined [OK]

Hint: Only add attributes on right side of FDs from closure set [OK]

Common Mistakes:

• Adding attributes not functionally dependent
• Confusing direction of dependencies
• Assuming closure includes all attributes

5. For relation R(A, B, C, D, E) with FDs:
A -> BC, CD -> E, B -> D. Which of the following is a candidate key?

hard

A. {A, D}

B. {A}

C. {A, C}

D. {B, C}

Solution

1. Step 1: Compute closure of {A}

   Start with {A}. From A -> BC, add B and C: closure = {A, B, C}. From B -> D, add D: closure = {A, B, C, D}. From CD -> E, since C and D are in closure, add E: closure = {A, B, C, D, E}.

2. Step 2: Check minimality of {A}

   Since closure({A}) includes all attributes, {A} is a superkey. It is minimal because it has only one attribute.

3. Final Answer:

   {A} -> Option B

4. Quick Check:

   Minimal set with full closure is candidate key [OK]

Hint: Check closure of smallest sets covering all attributes [OK]

Common Mistakes:

• Not checking transitive dependencies
• Assuming larger sets are always candidate keys
• Ignoring minimality condition