What is Normalization
Ans : A normalization is a process through which we compose table with in small table so that remove
delicacy, redundancy and inconsistency in the table or database than improve the performance of data
accessibility.
First Normal Form:
1. A table must have a key attributes.
Company_Table
Project_Id Project_Name Emp_Id Emp_Name Category Hourly_Rate
P001 MySQl E1001 Amit A 4000
P001 MySQl E1002 Raj B 3000
P001 MySQl E1003 Deepak C 2000
P002 Oracle E1001 Amit A 4000
P002 Oracle E1004 Vinit B 3000
Second Normal Form
1. A table in First Normal Form
2. All Non key attributes depends upon key attribute.
Project_Table
Project_Id Project_Name
P001 MySQl
P002 Oracle
Employee_Table
Emp_Id Emp_Name Category Hourly_Rate
E1001 Amit A 4000
E1002 Raj B 3000
E1003 Deepak C 2000
E1004 Vinit B 3000
ProjectEmployee
Project_Id Emp_Id
P001 E1001
P001 E1002
P001 E1003
P002 E1001
P002 E1004
Third Normal Form:
1. A table in Second Normal Form
2. There is no Transitive Dependency
Project_Table
Project_Id Project_Name
P001 MySQl
P002 Oracle
Employee_Table
Emp_Id Emp_Name
E1001 Amit
E1002 Raj
E1003 Deepak
E1004 Vinit
Category_Table
Category Hourly_Rate
A 4000
B 3000
C 2000
ProjectEmployee
Project_Id Emp_Id
P001 E1001
P001 E1002
P001 E1003
P002 E1001
P002 E1004
[Link] is Normalization. Explain its Normal form.?
Ans: Normalization is process through which reduce or remove redundancy, remove insertion anomaly,
deletion anomaly and updation anomaly and decompose the table.
There is one table as follows
Std_Enroll_No Std_Name Age
DS1001 Amit 22
DS1002 Sumit 23
DS1001 Amit 22
In above table, There is row level delicacy. So that we define the key attribute.
Std_Enroll_No Std_Name Age
DS1001 Amit 22
DS1002 Sumit 23
There is an another table as follows
Std_Enroll_No Std_Name C_Id C_Name F_Id F_Name Salary
DS1001 Amit C1 [Link]. F1 NKB 30000
DS1002 Sumit C2 [Link]. F2 VH 40000
DS1003 Deepak C1 [Link]. F1 NKB 30000
DS1004 Raj C1 [Link]. F1 NKB 30000
In above table, There is column level delicacy. There insertion anomaly, deletion anomaly and updation
anomaly. So that we decompose table.
Std_Enroll_No Std_Name
DS1001 Amit
DS1002 Sumit
DS1003 Deepak
DS1004 Raj
C_Id C_Name
C1 [Link].
C2 [Link].
F_Id F_Name Salary
F1 NKB 30000
F2 VH 40000
1. Fist Normal Form:
1. There must not multi-valued attribute.
2. A table must have a key attributes.
There is a table. It is not in First Normal Form because there is no key attribute and there is
multivalued attribute.
Std_Enroll_No Std_Name Mobile_No
DS1001 Amit 9826123456 / 9977123456
DS1002 Sumit 8956325890
DS1003 Deepak 9165123456 / 9304123456
DS1004 Raj 9425123456
There are three ways to convert above table in First Normal Form.
1. First Way
Std_Enroll_No Std_Name Mobile_No
DS1001 Amit 9826123456
DS1001 Amit 9977123456
DS1002 Sumit 8956325890
DS1003 Deepak 9165123456
DS1003 Deepak 9304123456
DS1004 Raj 9425123456
Key Attributes: (Std_Enroll_No, Mobile_No)
2. Second Way
Std_Enroll_No Std_Name Mobile_No1 Mobile_No2
DS1001 Amit 9826123456 9977123456
DS1002 Sumit 8956325890 Null
DS1003 Deepak 9165123456 9304123456
DS1004 Raj 9425123456 Null
Key Attribute: Std_Enroll_No
3. Third Way
Break table and define key attributes. One is Base Table another is reference table.
Std_Enroll_No Std_Name
DS1001 Amit
DS1002 Sumit
DS1003 Deepak
DS1004 Raj
Key Attribute: Std_Enroll_No
Std_Enroll_No Mobile_No
DS1001 9826123456
DS1001 9977123456
DS1002 8956325890
DS1003 9165123456
DS1003 9304123456
DS1004 9425123456
Key Attribute: (Std_Enroll_No, Mobile_No)