Understanding Database Primary Keys and Functional Dependencies

In database design, identifying the primary key is crucial for ensuring data integrity and structure. A primary key uniquely identifies each record in a table. It is a special type of candidate key selected to uniquely identify all tuples in a relation.

What is a Candidate Key?

A candidate key is a minimal superkey. A superkey is a set of one or more attributes that, taken together, allow us to uniquely identify a tuple in the relation. Minimal means that no proper subset of the superkey is also a superkey.

Understanding Functional Dependencies (FDs)

Functional dependencies describe the relationships between attributes. An FD X → Y means that the value of attribute set X uniquely determines the value of attribute set Y. In our case, the given functional dependencies are:

• $A \rightarrow BC$
• $D \rightarrow E$
• $BC \rightarrow D$
• $A \rightarrow D$

The universal relation is $R(A, B, C, D, E)$. We need to find a primary key from the given options.

How to Find a Primary Key using Attribute Closure

To find a primary key, we can calculate the closure of attribute sets. The closure of an attribute set X, denoted as $X^+$, is the set of all attributes that are functionally dependent on X. If $X^+$ contains all attributes of the relation R, then X is a superkey. To be a primary key, X must also be minimal (no proper subset of X is a superkey).

Let's calculate the closure for the attributes/sets of attributes from the options and also check if any single attribute could be a superkey.

Calculating Closure of Option 3: {A}

We want to find $A^+$.

1. Start with the initial set: {$A$}
2. Apply $A \rightarrow BC$: Add $B$ and $C$. Current set: {$A, B, C$}
3. Apply $A \rightarrow D$: Add $D$. Current set: {$A, B, C, D$}
4. Apply $BC \rightarrow D$: $BC$ are in the set {$A, B, C, D$}, and $D$ is already in the set. No new attributes added.
5. Apply $D \rightarrow E$: $D$ is in the set {$A, B, C, D$}, so add $E$. Current set: {$A, B, C, D, E$}

So, $A^+ = \{A, B, C, D, E\}$. Since $A^+$ contains all attributes of R, {$A$} is a superkey.

Is {$A$} minimal? The only proper subset of {$A$} is the empty set {}. The closure of the empty set is always empty. So, {$A$} is minimal. Therefore, {$A$} is a primary key.

Checking Other Options

Since we found that A is a primary key, any option containing A along with other attributes (like AD, AB) cannot be a *minimal* superkey, and therefore cannot be a primary key.

Let's verify the other options:

Calculating Closure of Option 1: {BC}

We want to find $(BC)^+$.

1. Start with the initial set: {$B, C$}
2. Apply $BC \rightarrow D$: Add $D$. Current set: {$B, C, D$}
3. Apply $D \rightarrow E$: Add $E$. Current set: {$B, C, D, E$}
4. Check other FDs: $A \rightarrow BC$, $A \rightarrow D$. None of these apply as $A$ is not in the current set.

So, $(BC)^+ = \{B, C, D, E\}$. This does not contain attribute $A$. Thus, {$BC$} is not a superkey, and therefore not a primary key.

Calculating Closure of Option 2: {AD}

We want to find $(AD)^+$.

1. Start with the initial set: {$A, D$}
2. Apply $A \rightarrow BC$: Add $B$ and $C$. Current set: {$A, D, B, C$}
3. Apply $D \rightarrow E$: Add $E$. Current set: {$A, D, B, C, E$}
4. Apply $BC \rightarrow D$: $BC$ are in the set, $D$ is already there.
5. Apply $A \rightarrow D$: $A$ is in the set, $D$ is already there.

So, $(AD)^+ = \{A, B, C, D, E\}$. {$AD$} is a superkey. However, we already found that $A$ alone is a superkey ($A^+ = \{A, B, C, D, E\}$). Since $A$ is a proper subset of $AD$, $AD$ is not a minimal superkey, and thus not a primary key.

Calculating Closure of Option 4: {AB}

We want to find $(AB)^+$.

1. Start with the initial set: {$A, B$}
2. Apply $A \rightarrow BC$: Add $C$. Current set: {$A, B, C$}
3. Apply $A \rightarrow D$: Add $D$. Current set: {$A, B, C, D$}
4. Apply $BC \rightarrow D$: $BC$ are in the set, $D$ is already there.
5. Apply $D \rightarrow E$: Add $E$. Current set: {$A, B, C, D, E$}

So, $(AB)^+ = \{A, B, C, D, E\}$. {$AB$} is a superkey. However, we already found that $A$ alone is a superkey ($A^+ = \{A, B, C, D, E\}$). Since $A$ is a proper subset of $AB$, $AB$ is not a minimal superkey, and thus not a primary key.

Based on the closure calculations, only {$A$} is a minimal superkey that determines all attributes of the relation R. Therefore, the primary key is A.

Summary of Closure Calculations

Thus, the primary key of relation R is A.

Revision Table: Database Key Concepts

Additional Information: Finding All Candidate Keys

In some cases, there might be multiple candidate keys. To find all candidate keys, one systematic approach involves:

1. Find the closure of each single attribute. If any single attribute's closure is the full set of attributes, that attribute is a candidate key.
2. If no single attribute is a candidate key, find the closure of pairs of attributes, then triples, and so on, until you find a superkey.
3. Once a superkey X is found, check if any proper subset of X is also a superkey. If no proper subset is a superkey, X is a candidate key.
4. Repeat until all minimal superkeys are found.

An alternative method involves finding the 'essential' attributes - those attributes that do not appear on the right-hand side (RHS) of any functional dependency. These attributes must be part of *every* candidate key. In our FDs: {A → BC, D → E, BC → D, A → D}, the attributes on the RHS are B, C, D, E. The only attribute not on the RHS is A. This suggests A must be part of any candidate key. Since $A^+$ already covers all attributes, A is the only candidate key.