databasedatabase-designrelational-databasecandidate-key

Is there another candidate key? If so, what is it?

Q. It is known that for R(A,B,C,D,E):

  1. R has exactly 5 superkeys.
  2. ABC is a candidate key.
  3. D is a non-prime attribute.
  4. ABE and ACE are not superkeys.

Is there another candidate key? If so, what is it?

Edit: The question of the problem is to determine if there is another candidate key in R(A,B,C,D,E) besides ABC, considering the 1,2,3,4 conditions hold true.

My approach is that, according to the 2nd condition ABC is a candidate key then the super keys are: ABC,ABCD,ABCE,ABCDE.

But the 1st condition says there are exactly 5 super keys which means the 5th super key could be the other candidate key. And according to 3rd and 4th condition the only other SK/CK could be BCE since ACE and ABE can't be SK's.

But if BCE is a candidate key BCDE should by a super key and this makes a total of 6 super keys and violates the 1st condition.

I am not exactly sure where I went wrong. Please help me analysing it right.

Solution

  • Let’s try to detail the steps to solve the problem.

    There are 25 (= 32) possible subsets of the attributes:

    {{}, A, B, C, D, E, AB, AC, AD, AE, BC, BD, BE, CD, CE, DE, ABC, ABD, ABE,
 ACD, ACE, ADE, BCD, BCE, BDE, CDE, ABCD, ABCE, ABDE, ACDE, BCDE, ABCDE}

    We are looking for candidate keys different from ABC, knowing the facts that you have mentioned.

    1. ABC is a candidate key.

    If ABC is a candidate key then any subset of it cannot be a candidate key. This excludes the possibility of having {}, A, B, C, AB, AC, BC as candidate keys. Analogously, each superset of it cannot be a candidate key. This exclude ABCD, ABCE, ABCDE.

    So we have now the following possible candidate keys:

    {D, E, AD, AE, BD, BE, CD, CE, DE, ABC, ABD, ABE,
 ACD, ACE, ADE, BCD, BCE, BDE, CDE, ABDE, ACDE, BCDE}
    1. D is a non-prime attribute.

    D is not a prime attribute so it can be included in superkeys but not in candidate keys or part of them. This means that if D is present in a set of attributes, then the set cannot be a candidate key. So this excludes D, AD, BD, CD, DE, ABD, ACD, ADE, BCD, BDE, CDE, ABCD, ABDE, ACDE, BCDE, from being candidate keys.

    We have now the following possible candidate keys:

    {E, AE, BE, CE, ABC, ABE, ACE, BCE}
    1. ABE and ACE are not superkeys.

    Since ABE and ACE are not superkeys, they are not candidate keys as well, and of course none of their subsets are candidate keys. So, this exclude also E, AE, BE, CE, ABE, ACE.

    So we have now the following possible candidate keys:

    {ABC, BCE}

    We know already that ABC is a candidate key, so the only remaining possibility is that BCE is a candidate key.

    But we know that:

    1. R has exactly 5 superkeys. 2. ABC is a candidate key.

    From these two facts, follows that ABC, ABCE, ABCD, ABCDE are four superkeys. Only one other is missing.

    So, if BCE is a candidate key, as you have already noted, this would imply that also BCE and BCDE are superkeys. We have six different superkeys, and this is in contradiction with the hypothesis.

    On the other hand, if BCE is not a candidate key, then we have only four superkeys, and also this in contradiction with the hypothesis.

    So, we could say that it is not possible to give an answer to the question. Finally note that we cannot solve it also if we assume a non-usual meaning for “superkey”, as it is sometimes used: that is, a strict superkey, a set of attributes that strictly includes a key. In this case, if BCE is a candidate key, we have again four superkeys: ABCE, ABCD, ABCDE, and BCDE. If, on the other hand, BCE is not a candidate key, we have only three superkeys: ABCE, ABCD, and ABCDE.