Finding Attribute Closure and Candidate Keys using Functional Dependency

Quiz

In database management and normalization, it's important that one understands the concepts of functional dependency, closure property, and candidate keys. These concepts help us design efficient relational schemas, ensure data integrity, and optimize queries. In this chapter, we will elaborate these concepts with the help of practical examples and also understand the process of finding closures and identifying candidate keys.

What is Functional Dependency?

For a basic recap on functional dependency, it describes a relationship between attributes in a relational schema. If X → Y, it means the value of X uniquely determines the value of Y. Now here, X is the determinant, and Y is the dependent.

Let us see one example for this. In a table where each employee has a unique ID, the dependency ID → Name this signifies that knowing an employee's ID is enough to find their name.

Functional dependencies can be defined formally. Consider two tuples, T1 and T2, in a relational schema. If T1.X = T2.X this implies T1.Y = T2.Y. Then X → Y is a valid functional dependency.

But this relationship is not necessarily reciprocal. So the attribute Y may not determine X.

Example of Functional Dependency

Let us consider a relational schema R (A, B, C) with the following data −

A	B	C
1	1	4
1	1	3
4	2	6
6	5	7

Let's understand the dependencies −

A → C − This does not hold. This is because A = 1 maps to both C = 4 and C = 3.
A → B − This is valid since each unique value of A maps to a unique value of B.
B → A − This is also valid for the given dataset.

Thus, we derive the following functional dependencies −

A → B
B → A

What is Attribute Closure?

The closure of an attribute set, X, which is denoted as $\mathrm{X^{+}}$. This is the set of all attributes that can be determined by X using the given functional dependencies. The closures properties help us to identify keys and candidate keys for a relational schema.

Following are the Steps to Find Closure −

We can start with $\mathrm{X^{+}}$ = X (the attribute set itself).
Iteratively add attributes that can be determined from the functional dependencies.
Stop when no more attributes can be added to $\mathrm{X^{+}}$.

Example of Closure

Using the functional dependencies, say, A → B, B → D, and CD → E −

Finding $\mathrm{A^{+}}$	Finding $\mathrm{B^{+}}$
Start with $\mathrm{A^{+}}$ = {A} A → B: Add B to $\mathrm{A^{+}}$ B → D: Add D to $\mathrm{A^{+}}$ CD → E: $\mathrm{A^{+}}$ contains C and D, so add E	Start with $\mathrm{B^{+}}$ = {B} B → D: Add D to$\mathrm{B^{+}}$ No further dependencies to apply
Final closure: $\mathrm{A^{+} \:=\: \{A,\: B,\: C,\: D,\: E\}}$	Final closure: $\mathrm{B^{+} \:=\:\{B,\: D\}}$

Concept of Candidate Key

A candidate key is a minimal set of attributes that can uniquely identify all other attributes in a relational schema.

A candidate key's closure must contain all attributes in the schema.
If multiple candidate keys exist, they provide alternate ways to uniquely identify tuples.

Finding Candidate Keys

To find candidate keys we must follow the following set of points in mind.

Compute the closure of each attribute (or combination of attributes).
If the closure contains all attributes in the schema then the attribute set is a candidate key.
Ensure the minimality by checking if removing any attribute from the set still results in a closure containing all attributes.

Example of Candidate Keys

Relational Schema R (A, B, C, D, E) −

Functional dependencies −

A → B, C
B → D
CD → E

Step 1: Find Closures

The following table highlights how you can find closures −

Finding $\mathrm{A^{+}}$	$\mathrm{B^{+}}$	$\mathrm{CD^{+}}$
Start with {A} A → B, C: Add B, C B → D: Add D CD → E: Add E $\mathrm{A^{+} \:=\: \{A,\: B,\: C,\: D,\: E\}}$	Start with {B} B → D: Add D No further dependencies to apply $\mathrm{B^{+} \:=\:\{B,\: D\}}$	Start with {C, D} CD → E: Add E C, D, E does not reach A or B, so $\mathrm{CD^{+}}$ is incomplete
$\mathrm{A^{+}}$ contains all attributes, so A is a candidate key.	$\mathrm{B^{+}}$ does not contain all attributes, so B is not a candidate key.	CD is not a candidate key.

Step 2: Check Combinations

Other candidate keys can be found by combining attributes and checking closures.

Shortcut for Finding Candidate Keys −

If a single attribute or minimal set of attributes has a closure, and it is containing all schema attributes, that set is a candidate key. Additionally:
If E is determined by CD, then CD can be replaced with CB. This combinations involving B should be checked.

By systematically using dependencies, we can infer keys without exhaustive searches.

Conclusion

In this chapter, we understood how to use functional dependencies to find closures and candidate keys. We started with the basics of functional dependency and understood its role in identifying relationships between attributes. Then, we used examples to understand how closures work and how they help determine candidate keys.

We also analyzed the efficient methods for finding candidate keys and highlighted their significance in relational schema design.

Print Page