Closure of attributes in DBMS Theory - Mini Project: Build & Apply

Closure of Attributes in Database Management

📖 Scenario: You are working with a database design team. They want to find all the attributes that can be functionally determined by a given set of attributes in a table. This helps in understanding keys and dependencies.

🎯 Goal: Build a step-by-step solution to find the closure of a set of attributes given a list of functional dependencies.

📋 What You'll Learn

Create a dictionary called functional_dependencies where keys are tuples of attributes and values are sets of attributes they determine

Create a set called attributes representing the initial attribute set whose closure is to be found

Write a loop that repeatedly adds attributes to the closure set if they can be functionally determined by the current closure

Complete the closure calculation by returning or storing the final closure set

💡 Why This Matters

🌍 Real World

Finding attribute closures is essential in database design to identify candidate keys and ensure normalization.

💼 Career

Database administrators and designers use attribute closure calculations to optimize database schemas and maintain data integrity.

1

Setup Functional Dependencies

Create a dictionary called functional_dependencies with these exact entries: {('A',): {'B', 'C'}, ('B',): {'D'}, ('C',): {'E'}}

DBMS Theory

# Create the functional_dependencies dictionary
# Your code here

Hint

Use a dictionary where keys are tuples of attributes and values are sets of attributes.

2

Define Initial Attribute Set

Create a set called attributes with the exact elements {'A'} to represent the starting attributes.

DBMS Theory

functional_dependencies = {('A',): {'B', 'C'}, ('B',): {'D'}, ('C',): {'E'}}
# Create the attributes set
# Your code here

Hint

Use a set literal with the attribute 'A'.

3

Calculate Closure Core Logic

Create a set called closure initialized with attributes. Then write a while loop that continues as long as new attributes are added. Inside the loop, use a for loop with variables lhs and rhs to iterate over functional_dependencies.items(). Add attributes from rhs to closure if lhs is a subset of closure.

DBMS Theory

functional_dependencies = {('A',): {'B', 'C'}, ('B',): {'D'}, ('C',): {'E'}}
attributes = {'A'}
# Calculate the closure of attributes
# Your code here

Hint

Use a loop that repeats until no new attributes are added to the closure.

4

Complete Closure Calculation

Add a final line that assigns the variable final_closure to the value of closure to store the result.

DBMS Theory

functional_dependencies = {('A',): {'B', 'C'}, ('B',): {'D'}, ('C',): {'E'}}
attributes = {'A'}
closure = set(attributes)
changed = True
while changed:
    changed = False
    for lhs, rhs in functional_dependencies.items():
        if set(lhs).issubset(closure) and not rhs.issubset(closure):
            closure.update(rhs)
            changed = True
# Store the final closure
# Your code here

Hint

Assign the closure set to a new variable to keep the result.

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