Search your Topic HERE....

March 07, 2012

Normalization and Normal Forms - IBPS IT Officers Preparation Materials - DBMS

28 comments

sponsored links

The Process of making a table (or often we call it as Record) NORMAL is called Normalization :P Ok in other words, we can say that the Normalization is the process of simplifying the Table :)

Good definitions... Aren't they ??? ;) But you will get Size Zero marks if you write the above definitions in exams. Lets be lil TECHNICAL.

The design method which helps us to MINIMIZE the redundancies of data (repetitions) and reduces the errors / mistakes in the table is called Normalization. 

Note : To perform this Normalization Process, we should analyze the INTER DEPENDENCIES of the attributes in the tables and should take the subsets of the larger tables to form the Smaller tables. 

In this process, we apply various Normal Forms to the given Database (Table). These Normal Forms helps us to break the BIG tables into smaller ones. Now lets have a look at these Normal Forms...

1st Normal Form : 
  * In 1st Normal Form we deal with the REDUNDANCY of ROWS.
In first Normal Form, each ATTRIBUTE must be ATOMIC. There shouldn't be any repetitions in the Rows. and There shouldn't be any multivalued columns. 

The advantage of the 1st Normal Form is "The queries become easier"

The 1NF eliminates GROUPS by keeping them into different (separate) Tables and connecting them with One to Many relationship. 

2nd Normal Form :

* A table or entity should be in the first normal form, if you want to apply the 2nd Normal form on that.

Note : The first normal form deals with the repetition (redundancy) of data in Rows (Horizontal) , where as the 2nd Normal form deals with the redundancy of the data in Columns (Vertical). 

As I already said, A table should be in the First Normal form if you want to make sure that it is in 2nd normal form. Some more additional conditions are,

  • It shouldn't have a COMPOSITE primary key. (I mean, the key SHOULD NOT be divided into sub parts (logical entities).
  • In 2nd Normal Form, each attribute should FUNCTIONALLY Dependent on the primary key. 
    • What is this Functionally Dependence? 
      • If one or more attributes determines UNIQUELY the values of other attributes, then that property is called as Functionally Dependence.
  • If you find any Non Dependent Attributes, you should move them into Smaller Tables.
  • 2NF helps us to improve the data Integrity (Error free)
3rd Normal Form :

* A table or entity should be in the Second Normal Form (so obviously, it should be in the first normal form too :P) if you want to apply 3rd Normal form on that. Some more conditions are,
  • If the table is in 3rd Normal Form, then it should be Transitive dependencies free.....
    • What is this Transitive Dependence?
      • If there exists TWO separate entities in ONE table. Obviously we should move them into smaller tables.
  • 3nf also further improves the integrity of the data. 
Boyce - Codd Normal Form : (Not Boys Code as some of us are thinking :P )
  • It is the ADVANCED version of the 3rd Normal Form (3NF). 
  • A row in a table is in BCNF, if every determinant of it is a Candidate Key. (Most of the entities of 3rd Normal form are already in Boyce - Codd Normal Form)
    • Why should we use BCNF if already 3NF is there?
      • The 3rd Normal Form misses the inter dependencies between Non Key attributes. 
      • Simple we can say that, a 3NF is not in BCNF if,
        • There are number of Candidate Keys
        • The keys are composed of multiple attributes
        • There are more than one attribute between the Keys.......
4th Normal Form :

* ok ok , Obviuosly it should be in the 3NF if you want to call it 4NF. In addition, we should eliminate Trivial Multivalued Dependencies... I mean, our table shouldn't have multiple sets of Multi Valued Dependencies. 

5th Normal Form :
* It should be in the 4th Normal Form...
  • In this, we should eliminate the dependencies, not determined by the Keys.
  • I mean, every dependency should be the consequence of its Candidate keys...
Thats all for now Friends. Read more materials of IBPS IT Officers Here. All The Best

28 comments:

  1. mam technical ka bhe question dal do

    ReplyDelete
  2. very helpful material

    ReplyDelete
  3. thank u thank u thank u thank u so oooooo much.

    ReplyDelete
  4. thankss alot! very helpful for recalling everything :) but it will be more clear if you put up this with taking example :)

    ReplyDelete
  5. can any one post PDF files for total topics it will be very help full to all

    ReplyDelete
  6. Dear Mam You have described each and every aspect of DBMS but failed to mention the introduction of PRIMARY KEY,CANDIDATE KEY,COMPOSITE KEY etc.It would be great if you could provide some short notes for these topics...Once again thanks for this Legendary work.. Rudra

    ReplyDelete
  7. mam please explain with tables,,,it wud be easy then!!

    ReplyDelete
  8. mam plz exaplain with example..

    ReplyDelete
  9. Please provide examples for better undestanding

    ReplyDelete
  10. thanks for this material. pls topic wise mcq bhi dal dijiye it will be helpful for us

    ReplyDelete
  11. mam pls topic wise quetion bhi dal dijiye

    ReplyDelete
  12. Hi Admin, it will be great help if all this info in PDF format to download

    ReplyDelete
  13. thanku very helpful

    ReplyDelete
  14. Pl provide egs 4 better understanding

    ReplyDelete
  15. pls bescribe it with more example mom

    ReplyDelete
  16. thnq..bt pls explain with the help of an example.....pls..

    ReplyDelete
  17. pls give us one example explaining normal forms....pls....

    ReplyDelete
  18. mam kch b smjh nai aya...very confusing this part :P

    ReplyDelete
  19. it is very easy to understand

    ReplyDelete
  20. very very helpful

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...