NORMALIZATION

What is normalization?

Database Design Technique used for avoiding redundant data and dependency data.

Before going to view the rules lets understand some basics,

Primary Key – unique identify for the database records without duplication.

        Following Characteristics of Primary Key,

·         Primary key does not allow null value

·         Primary key must be unique

·         Primary values cannot be changed

·         Primary key created when new records inserted.

Composite Key – Combination of multicolumn values for identity a record unique.

Foreign Key –

Foreign Key - insert record in foreign key table will throw error if foreign Key doesn’t have entry in Primary Key.

Transitive Functional Dependencies

A transitive functional dependency is when changing a non-key column , might cause any of the other non-key columns to change

1NF - (no duplication of data/ single value for each column)

    1NF Rules:

·         Each column contain single value

·         Each rows inserted must be unique

  

2NF – (Use of Referential integrity, Primary Key and Foreign Key)

 2NF Rules

·         Follow  1NF

·         Single column Primary Key

3NF Rules

·         Follow 2NF

·         Has no transitive Functional Dependencies

Boyce-Codd Normal Form (BCNF)

Even when a database is in 3^rd Normal Form, still there would be anomalies resulted if it has more than one Candidate Key.  

Sometimes is BCNF is also referred as 3.5 Normal Form.

4^th Normal Form

If no database table instance contains two or more, independent and multivalued data describing the relevant entity , then it is in 4^th Normal Form.

5^th Normal Form

A table is in 5^th Normal Form only if it is in 4NF and it cannot be decomposed in to any number of smaller tables without loss of data.