Number of Possible Super Keys in DBMS

Super Key

In DBMS, a set of attributes used to identify each row uniquely is called a super key. The super key is the super subset of the primary key or candidate key. Each primary key or candidate key is a super key, but each super key cannot be called a candidate key.

We will count the number of super keys present in a relational model (table) by understanding various examples, which are discussed below:

Note:

A UNION B = A + B - (A INTERSECTION B)

A UNION B UNION C = A + B + C - (A INTERSECTION B) - (B INTERSECTION C) - (C INTERSECTION A) + (A INTERSECTION B INTERSECTION C)

Example1:

Suppose we have a relational model with attributes as {A, B, C, D} and A as the candidate key, then count the number of super keys.

Solution:

As we know candidate key is also a super key with no subset as a super key, so all the super keys will contain the candidate key.

So super keys will be: {A, AB, AC, AD, ABC, ABD, ACD, ABCD}

So, the generalized formula will be for the table if only one candidate key is available and K is the number of attributes, then total super keys = 2^(K-1).

Note: Let a relational model with N attributes then the maximum number of super keys will be 2^N-1 (when all the attributes are candidate keys).

Example 2:

If a relational model has N attributes as {A1, A2...An} and candidate key as {A1A2A3A4} then the number of super keys?

Solution:

The total number of super keys will be 2^(N-4).

Example 3:

If a relational model has N attributes as {A1, A2...An} and candidate keys as {A1, A2}, then the number of super keys?

Solution:

Total super keys = (super keys of A1) UNION (super keys of A2)

Total super keys = 2^(N-1) + 2^(N-1) - 2^(N-2)

Example 4:

If a relational model has N attributes as {A1, A2...An} and candidate keys as {A1, A2A3}, then the number of super keys?

Solution:

Total super keys = (super keys of A1) UNION (super keys of A2A3)

Total super keys = 2^(N-1) + 2^(N-2) - 2^(N-3)

Example 5:

If a relational model has N attributes as {A1, A2...An} and candidate keys as {A1A2, A3A4}, then the number of super keys?

Solution:

Total super keys = (super keys of A1A2) UNION (super keys of A3A4)

Total super keys = 2^(N-2) + 2^(N-2) - 2^(N-4)

Example 6:

If a relational model has N attributes as {A1, A2...An} and candidate keys as {A1A2, A2A3}, then the number of super keys?

Solution:

Total super keys = (super keys of A1A2) UNION (super keys of A2A3)

Total super keys = 2^(N-2) + 2^(N-2) - 2^(N-3)

Example 7:

If a relational model has N attributes as {A1, A2...An} and candidate keys as {A1, A2, A3}, then the number of super keys?

Solution:

Total super keys = (super keys of A1) UNION (super keys of A2) UNION (super keys of A3)

Total super keys = 2^(N-1) + 2^(N-1)+2^(N-1) - 2^(N-2) - 2^(N-2) - 2^(N-2)+ 2^(N-3)

Example 8:

Let's suppose we have a set of attributes as R:{A, B, C, D, E, F, G, H} and functional dependencies as:

A -> BC

B -> CFH

E -> A

F -> EG

CH -> G

Solution:

First, we will identify the candidate keys, which are: {AD, BD, ED, FD}

So will get total super keys = (super keys of AD) UNION (super keys of BD) UNION (super keys of ED) UNION (super keys of FD)

For a single AD, we have six options that can be chosen or not, so it will be 2⁶.

The combination of two super keys like AD INTERSECTION BD will be 2⁵.

For the combination of three super keys, the total options will be 2⁴.

For the combination of four super keys, the total options will be 2³.

Number of single combinations = 4

Number of two combinations = 6

Number of three combinations = 4

The number for four combinations = 1

So total super keys will be: 4*2⁶ - 6*2⁵ + 4*2⁴ -1*2³ = 120

Example 9:

If a relational model has N attributes as {A1, A2...An} and there is one candidate key made with K attributes. Find the value of K such that the number of candidate keys must be maximum.

Solution:

Choosing K values from N is done by ^NC_K.

^NC_K = (!N)/!(N-K)!k

For the above value, K = N/2 for maximum.