## 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.

## No comments:

## Post a Comment