Normal Forms

1NF :
      A table is in 1NF if all the attributes in it are atomic. There should not be any multivalued attribute. For example Consider the following scenario

EID Mobile No.
1      8438145610,9494949492
2      8484848484
3      3939393993,3434344343

In this case 1 and 3 have multiple values for Mobile No. attribute. We should eliminate them by create separate columns for each mobile number. If every attribute has only one value, then the table is in 1 NF.

2NF :
       A table is in 2 NF if the following conditions are met
       a) A table is already in 1NF and
       b) For all X->A there should not be a "Partial Dependency"

What is Partial Dependency ?
     
         For all X->A  => X is a proper subset of candidate keys and A is non-prime attribute.


What is a non-prime attribute ?
  
       Any attribute that is not part of candidate keys are called non-prime attribute.


Example 
      Consider the following functional dependency.
    
      A->B
      C->D        

Candidate Key(s) = AC

So non-prime attributes are B and D.

If you check for 2NF in the above FD's then A->B and C->D violates 2NF property, because both exhibit partial dependency property.


3NF :
       A table is in 3 NF if the following conditions are met.
       a) Table is already in 2NF.
       b) For all X->A => X is a super key or A is a prime attribute.

Example 
      Consider the following functional dependency.
    
      A->B
      B->C       

Candidate Key(s) = A.

So non-prime attributes are B and C. Prime attribute is A.

B->C doesn't satisfy the condition for 3NF. So the above FD's violate 3NF.

 BCNF :(strict version of 3NF)
      A table is in 3 NF if the following conditions are met.
       a) Table is already in 2NF.
       b) For all X->A => X is a super key.

Example 
      Consider the following functional dependency.
    
      A->B
      B->C       

Candidate Key(s) = A.

So non-prime attributes are B and C. Prime attribute is A.

B->C doesn't satisfy the condition for BCNF. So the above FD's violate BCNF.

 


Comments

Post a Comment

Popular posts from this blog

Super and Candidate Key

Super Key : In order to understand super key in DBMS we must understand what is closure of an attribute.  Closure of an attribute :         We defined Functional dependency here Functional Dependency . Closure of an attribute is nothing but the set of Axioms that are applied to get all the implied rules from the given rules. You can study about the Axioms here Axioms Let us take an example A->B B->C A+=Closure of A ={A,B,C} (using transitive property and reflexive property) B+=Closure of B ={B,C} (using reflexive property) C+=Closure of C ={C} (reflexive property) AB+=Closure of AB={A,B,C} (using transitive property and reflexive property) AC+=Closure of AC={A,B,C} (using reflexive property) Super Key :       Having understood closure let us define what is superkey. Superkey is an attribute or set of attributes that define all attributes. we find superkey using closure. If closure of any set of attributes ...
Read more

Canonical Cover

Canonical Cover :             Canonical cover or minimal cover is the way of reducing the functional dependencies without changing the meaning of it. Why we go for canonical cover is because it takes time when all functional dependencies are applied on DBMS. Let us work with an example to make it clear : A->BC AB->C A->C There are 3 steps involved in converting to canonical cover  Step 1 :  All the FD's should be in the form X->a where "X" is a set of attribute or a single attribute and "a" must be single attribute. So for the above example  it reduces as follows  A->B A->C AB->C A->C (write it as such this will be dealt in step 3) Step 2:  Remove redundant attributes that is try to remove X if it has more than one attribute. So for the above example AB-> C To reduce this what we must do is we must answer the question if A-...
Read more

Anomalies In detail

Let us take an example In employee table attributes are eid,ename,car,parea (eid is the primary key) Insertion Anomaly :           Let us say for each car there is an allocated parking area. In order to hold that we need to make in an entry in table like this                                                               null,null,Swift, A Since null cannot assigned for eid. We call this as Insertion Anomaly. Deletion Anomaly :          Let us say that we have  only one entry for swift  car that is also associated with employee id 5. Suppose he leaves the company we have to remove him from the table.                                         ...
Read more