DBMS: Find Super Key of relation R (A, B, C, D, E, F) and convert the relation to 2NF

Consider a relation R with the schema R (A, B, C, D, E, F) with a set of functional dependencies F as follows:

{AB→C, BC→AD, D → E, CF→ B}.

 

Find the super key for this relation and convert it to 2NF.

Given Relation:

R (A, B, C, D, E, F)

Given Functional Dependencies (F):

AB → C
BC → AD
D → E
CF→ B

 

To find the Super Key or Candidate Key, we have two methods:

The first method is to get the Closure of Attributes for each attribute and the possible combinations. And then figure out which attribute(s) can determine all other attributes. This process is too lengthy. Thus we will use the shortcut. The shortcut is to check for the attribute(s) which are NOT present on the Right Side of any Functional Dependency . 

The attribute F is not present on the Right Side of any Functional Dependency. This means that the attribute F cannot be determined by any other attribute(s). Thus F must be present on the candidate key and also the super key.

So let's start with finding the closure of F.

F+ = {F}

From the given set of functional dependencies, the attribute F cannot determine any other attribute. Thus F canNOT be the Super Key.

Let's find the closure of the attribute CF.

{CF}+ :
= {CF} (because of the axiom of reflexivity)
= {CFB} (from the given dependency CF → B)
= {CFBAD} (from the given dependency BC → AD)
= {CFBADE} (from the given dependency D → E)

 

Finally {CF}+ = {CFBADE} or {ABCDEF}

 

Since CF can determine all other attributes, CF is a Candidate Key.

And all the combinations of CF with or without other attribute(s) are Super Keys.

For example,

CF, CFA, CFB, CFA, CFD, CFE, ABCDF, ABCFE,

 

etc are Super Keys.

 

 

Now, coming to the second part of the question. That is to convert the given relation to 2NF.

Firstly, the relation has to be in 1NF. Since the question does not provide any data, we cannot check for the atomicity of the values of attributes. In such cases, the only option we have is assuming that the relation is already in 1NF.

Secondly, we will have to check if the relation has any Partial Dependency. And to know if the relation has any Partial Dependency, we will have to find out the other Candidate Keys for the relation.

We have already determined one Candidate Key which is CF. To check if any other Candidate Key exists, let's see if C or F is replaceable by any other attribute(s). As we have a dependency

AB → C,

we can replace C with AB. Thus now we have:

The Closure of ABF:

{ABF}+ = {ABCDEF}

So now,

Candidate Keys = {CF, ABF}

 

Now we can say:

Prime Attributes = {A, B, C, F}, attributes which are part of Candidate Keys.

Non-prime Attributes = {D, E}, attributes which are NOT part of Candidate Keys.

 

As we know that a dependency is a Partial Dependency if,

A Proper Subset of Candidate Key  → Non-prime Attribute

 

Let's check if there are any such dependencies in the given relation.

AB → C

is not a Partial Dependency. Though AB is a proper subset of candidate key(ABF), C is not a non-prime attribute.

BC → AD

is not a Partial Dependency because BC is not a proper subset of the candidate key.

D → E

is not a Partial Dependency because D is not a proper subset of the candidate key.

CF→ B

is not a Partial Dependency. CF is a subset(not a proper subset) of the candidate key.

 

As there are no partial dependencies the relation is already in 2NF.