Finding Candidate Keys using Functional Dependencies

Relational databases rely on structured data. A key concept in database management is determining candidate keys that are minimal sets of attributes that can uniquely identify every row in a table. In this chapter, we will focus on the basics of candidate keys, highlighting how to find candidate keys using functional dependencies.

What are Functional Dependencies?

Functional dependencies describe the relationships between the attributes in a table. For instance, if we say "X → Y", it means that if two rows share the same value for attribute "X", they must also share the same value for attribute "Y". Let us see a practical example −

Suppose we have a table called EMPLOYEE with the following records −

For this table −

• ID → NAME holds because each ID maps uniquely to one NAME.
• ID → CITY and ID → STATE also hold.

However, NAME → ID does not hold, because "John" corresponds to two different ID values. These relationships are the building blocks for finding candidate keys.

Trivial and Non-Trivial Functional Dependencies

While studying functional dependencies, we must understand its trivial and non-trivial cases as well.

• Trivial Dependency − If an attribute set X trivially determines itself (e.g., {ID, NAME} → {ID}), it is known as trivial.
• Non-Trivial Dependency − Dependencies that are not trivial, like ID → NAME, fall into this category.

Trivial dependencies are always true, but non-trivial ones guide us in understanding the structure of the table.

What is Attribute Closure?

To determine candidate keys, we need to understand the concept of Attribute Closure. The closure of an attribute set is the total set of attributes that can be functionally determined from it.

Steps to Calculate Attribute Closure

We can start with the attributes we want to find the closure for. Then, add all attributes directly determined by the starting set. Thereafter, add attributes recursively that can be derived from the expanded set using functional dependencies. Repeat the steps until no more attributes can be added.

Finding Candidate Keys: Step-by-Step Example

Let us consider the EMPLOYEE table. It has the following functional dependencies −

• ID → NAME
• ID → CITY
• ID → STATE
• CITY → STATE

The attributes of the table are: {ID, NAME, CITY, STATE}

Step 1: Identify the Attribute Closures

In the very first step, we need to find the closure for each attribute or set of attributes −

Closure of ID −

• Start with {ID}
• Using ID → NAME, add NAME to the closure: {ID, NAME}
• Using ID → CITY, add CITY: {ID, NAME, CITY}
• Using CITY → STATE, add STATE: {ID, NAME, CITY, STATE}
• Final closure: {ID, NAME, CITY, STATE}

Closure of NAME −

• Start with {NAME}
• No functional dependency allows adding more attributes
• Final closure: {NAME}

Closure of CITY −

• Start with {CITY}
• Using CITY → STATE, add STATE: {CITY, STATE}
• Final closure: {CITY, STATE}

Step 2: Identify Candidate Keys

Candidate keys are the minimal sets of attributes whose closures include all attributes in the table ({ID, NAME, CITY, STATE}).

From the closures −

• ID is a candidate key because its closure is the full set of attributes.
• NAME and CITY are not candidate keys because their closures do not cover all attributes.

Checking Composite Keys

Let us now check combinations of attributes to see if they are candidate keys −

Closure of {ID, NAME} −

• Start with {ID, NAME}.
• Already covers all attributes due to the closure of ID.
• Not minimal because ID alone is sufficient.

Closure of {ID, CITY} −

• Just like {ID, NAME}, the combination {ID, CITY} is not minimal because ID alone suffices.

Closure of {ID, CITY, STATE} −

• This combination includes all attributes, but again, ID is a subset, making it non-minimal.

Thus, ID remains the only candidate key for this relation.

Distinguishing Candidate Keys, Super Keys, and Primary Keys

Make a note of the following important points that will help you distinguish Candidate Keys, Super Keys, and Primary Keys −

• Candidate Key − Minimal set of attributes that uniquely identifying rows. In this case, ID.
• Super Key − Any superset of a candidate key. For example, {ID, NAME} is a super key.
• Primary Key − A candidate key chosen as the main identifier.

Conclusion

In this chapter, we covered the concept of how to find candidate keys using functional dependencies. We started with the basics of functional dependencies and touched upon trivial and non‑trivial dependencies, and also the concepts of attribute closure and minimal sets.

Through a detailed example using the EMPLOYEE table, we determined the candidate key (ID), distinguished it from super keys, and clarified its role in database design.