PROBLEMS AND SOLUTIONS

Problems on RDBMS

P1 ) 

What is the highest normal form(HNF) of relation R(A,B,C,D), given Functional Dependencies (FDs)  A--> B; CD--> B; A--> CD; CD--> A ? 

A step by step approach:

Step-1:

             First of all, we've to look for the candidate keys based on the given functional dependencies.  NOTE-- a candidate key is that key which can uniquely identifies all rest of the attributes in a relation.

Here A and CD are the candidate keys. Lets see, why A and CD are candidate keys for more clarity.

'A' can uniquely identifies all rest of the attributes in the given relation R, as we can split all the Functional Dependencies  and see it more clearer;

                                                                 A--> B.....................(1)

                                                                A--> CD....................(2)

This clearly implies that  'A' determines B as can be seen in (1) and also 'A' determines CD, which can further be split and seen as;

                                                                A--> C.......................(3)

                                                                A--> D........................(4)

This shows that how 'A' determines all rest of the attributes (i.e. B,C,D)  of relation R.

Similarly

By seeing the Functional Dependencies , we can say that 'C and D' together determines all rest of the attributes (i.e. A and B )

Lets see how:

                                                                 CD--> B...................(1)

and also                                                 CD--> A...................(2)

This shows that  'C and D' together determines all rest of the attributes (i.e. A and B ) of relation R.

Step 2 : 

             We should know the definition of 2NF prior to testing any relation for 2NF.

2NF i.e. A relation is said to be in 2nd Normal Form no non-key attribute depends partially on Key attribute. Lets say AB is key attribute and C is a non-key attribute, then this relation is said to be in 2NF only when if C does not depend on the part of key attribute i.e. either on A alone or on B alone. If we write it in functional dependency expression then for this relation to be in 2NF

                                             C--> A             or                C--> A   should not exist.

So, in our example of relation R(A,B,C,D), we've to check whether it is in 2 NF or not.

Here only B is the non key attribute, and it does not depend partially on any key attribute, rather  it completely depends on key attribute  as A--> B ;  CD--> B shows this fact.

Therefore, we can say that relation R(A,B,C,D) is in 2NF.

Step 3:

           Now, we have to confirm, whether the relation R(A,B,C,D) is in 3 NF or not.

3NF definition says that, a relation is said to be in 3NF only when there doesn't exist transitive dependency on key attribute. i.e. no non-key attribute should depend indirectly on key attribute.

Lets say, A--> B and B--> C, which shows that, also A--> C, which means C indirectly depends on A via B. If in any relation, this kind of situation exists, then that relation is not in 3NF.

So, in our example of relation R(A,B,C,D), there are not any non-key attributes present that indirectly depends upon our key attributes(i.e. A and CD ). Therefore the relation R(A,B,C,D) is also in 3NF.

This is how, we find Highest Normal Form of any relation/table of a database.