SQLServer Normalization:–

Normalization Definition:

• Normalization process  of organizing the data in any table and normalization in the process of reducing redundancy in the table.
• Normalization process determines which attributes should be grouped in a table.
• If the table contains redundancy the following problem in this problem are called “anomolies”.

• Insert  anamoly
• Update anamoly
• Delete anamoly
  • Normalization process is a “set of rules” and each role is called “one normal form”
  • There are 6 normal forms:

1. [1st normal form]  1NF
2. [2nd normal form] 2NF
3. [3rd normal form] 3NF
4. [Boyce-code normal form] BCNF [Boyce-code]
5. [4^th normal form] 4NF
6. [5^th normal form] 5NF

• Normalization [manages] more recommends more tables- less-columns.
• Normalization recommends store data in its own table

Functional Dependency in DBMS with Example  :-

• There is a table R (A, B), it attributes B depends on A then there is a functional dependency between AB and denoted as follows A-> B

Types of Functional Dependency in SQL Database  :

• Full Functional dependency.
• Partial Functional dependency.
• Transitive Functional dependency.

Full Functional Dependency with Example:

•  In a table if non key depends on part of the key then the dependency is called “Full Functional dependency”.

Partial Functional dependency with Example:

• In a table ,if the non key depends on  part of the key then the dependency is called “partial Functional dependency”

Transitive Functional dependency with Example:

• In a table, if the non key attributes depends on  another non key then it is called “Transitive Functional dependency”

First Normal Form in DBMS with Example:-

A table is said to be in first normal form, If there are no repeating groups in it (or) all attributes are atomic (single).

T1 Table

Billno       Bdate     Ccode     Cname     Addr      Icode  Dept     Qty     rate    Volume   THbill

In the above table is  not according  to about 1NF because some of the attributes are “non-atomic attributes” decompose the table as follows.

Bill Table

• Billno        Bdate       Ccode     Cname     Addr     Thbill 

Item Table

• Billno         Icode      Dept       Qty       Rate       Value.

Second Normal Form in DBMS with Example: 2NF

A table is said to be in 2Nf,if it is in 1NF and no partial dependencies should exist in that table.

Example:

• In the above table is not according to 2NF then decompose the table as follows. 

Bill details: Billno         Icode                  Qty          Value

Item           :Icode           Desc.                    Rate 

Bill

Sellno    sdate    ccode   cname    caddr     thbill

Items

Icode        desc Rate 

Bill details:

Billno       Icode     Qty    Value.

Third Normal Form in DBMS with Example:-

Rule:

A  table is said to be in 3NF,if it is in 2NF and

1. No transitive dependencies should exists in that table.

2. No derived attributes should exist in that table.

• In the above table is n’t according to 3NF then decomposing the table 

CUST:

Ccode       Cname        Caddr

BILL:

Billno           Bdate               Ccode

ITEMS:

Icode       Descript           Rate

BILLDETAILS:

Billno             Icode           Qty