Wednesday 5 June 2013

Normalization Denormalization different form of normalization

Question: What is Normalization?
Answer: Normalization is a process in relational database where fields and tables are organized to reduce the redundancy and dependency by dividing the larger tables and linking them.

Question: Why We use Normalization?
Answer: We use Normalization to organized the data and reduce the redundancy and dependency.

Question: What happen, if we don't do Normalization?
Answer: Following are the pros, if we don't normalization.
  • Redundancy
  • Heavy database
  • Incorrect database
  • Queries take much time to execute.

Question: What is Denormalization?
Answer: This is the process by with DBA(database admin) attempt to optimize the performance of the database by adding in redundant OR grouped data. The advantages to this is that the data is stored in one table are faster, as opposed to multiple smaller tables where need to multiple joins.

Answer: There are 5 types of Normation i.e 1NF, 2NF, 3NF, 4NF and 5NF.
We can achieve upto 4NF (4NF is very rare)

Question: What are different form of normalization?
Answer: Following are the different form of normalization.
Rule1: Each table cell should contain single value. Like (if user have two phone no then both phone number can't be in one column must have two records for this)
Rule2: Each Record needs to be unique.

Rule1: Must follow 1NF
Rule2: Extract the filed that are non-key columns and place into separate table and releate both table with primary key concept.

Rule1: Must follow 2NF
Rule2: Has no transitive functional dependencies
example: table  fields are 
zip, cityname, statename
In addition, you may wish to instead have separate City and State tables, with the City_ID in the Zip table and the State_ID in the City table.

Rule1: Must follow 3NF
Rule2: All table can have only one primary key.