Normalization:

-> The process of organizing data to minimize redundancy or data duplication is called as Normalization. -> If you allow duplicate rows in a table, then there is no way to uniquely address a given row via programming. -> Described as "normalized" if it is in the third normal form. -> Most of the third normal tables are free of insertion, update, and deletion anomalies. -> Selective denormalization can subsequently be performed for performance reasons.

Why Normalization:

-> To free the database of modification anomalies. -> When an attempt is made to modify(update, insert, or delete from) a table, undesired side effects may follow. This will happen if a table has been normalized properly.

Example:

Consider the below given table
sidnamestate
1Rajeevuttarpradesh
2veenakarnataka
3kishoreuttarpradesh
4manishkarnataka
5sureshtamilnadu
...
...
...
10000rajuandrapradesh
In the above shown studenta table duplication is possible, for that we can see normalization.
stidstatename
1karnataka
2uttarpradesh
3tamilnadu
4andrapradesh
5maharastra
..
..
..
..
29kasmir
-> This is called as lookup table/parent table/master table.(independent)
sidnamestates
1rajeev2
2veena1
3kishore2
4manish1
5suresh3
...
...
...
...
-> This is called as details table/child table.(dependent)
create table states ---- parent table
(
stid int primary key,
statename varchar(40),
)
create table students ----child table
(
sid int primary key,
name varchar(40),
stateid int foreign key references states(stid)
)

insert into states(stid,statename) values (1,'karnataka')
insert into states(stid,statename) values (2,'uttarpradesh')
insert into students(sid, name, stateid) values (1,'rajeev',1)
insert into students(sid,name,stateid) values (2,'veena',2)

Assignments:

Prepare normalization table for the below given data.
(1)
pidnameBGstatename
1rahulo+veKarnataka
2veenao-veKarnataka
3manishb+veUttarpradesh
4suresho+veAndhrapradesh
5bharatb+veUttarpradesh
(2)
bknameauthornamecost
C# basicsrajeev300
c# basicsmahesh300
SQL basicsveena198
JS basicsharish200
SQL basicsbharath198
JS basicsrajeev200
Linux fundamentals rajeev99
C# basicsveena300

Normal Forms:

Default rule: Every table should have primary key.

First Normal Form:

-> No repeating groups across columns and within a column. Difficulty in querying the table. Answering such questions as 'which customer have telephone number X?' -> There should not be any duplicate row -> It should have a primary key. -> All column values must be atomic.(to avoid complication in manipulating list of value)

First normal form example:

assume that you are creating consumer orders table having the below given column names {order_ID, customer_ID, items}. But a customer can buy multiple items in different quantities. -> As a resolution for this we can generate a table like below. -> {order_ID, customer_ID, item1,quant1,item2,quqnt2,item3,quant3} -> Still it has some problems like, "how would you go about determining the quantity of hammers ordered by all customers during a particular month"? More than what if you want add one more item to it? -> Hence, it will not allow repeating groups as columns as well.(point 1) Result: # {order_ID,customer_ID,order_item_num,quantity,item} #key is {order_ID,order_item_num}

Second Normal Form:

-> Should be in first normal form -> Given any candidate key,k and any attribute, A;that is not a constituent of a candidate key, A depends upon the whole of k rather than just a part of it. (or) simply every non key column is fully dependent on the (entire) primary key.

Second Normal Form Example:

Emp_Dept {Empno, empname,empsal,Deptno,Deptname} so here what will happen if a given employee is working foe more than one department? In the above case P>K will be {Empno,Deptno} Through it satisfies 1st NF, it fails 2nd NF because Deptno Deptname (Departname can be known by Deptno it means that deptname columns not fully depending on the Full Primary Key) The above example is in 1st NF but not in 2nd NF.

Third Normal Form:

-> The relation R(table) is in second normal form(2NF) -> Every non prime attribute of R is non transitively dependent. -> (i.e directly dependent) on every candidate key of R.

Third Normal Form example:

Consider the employeeDepartment table having the below given columns. {Empno,Empname,Empsal,Deptno, Deptname} Suppose that every employee works for only one department, in such case this is 2nd NF. But {Empno Deptno Deptname}. This violates 3rd NF.So create new Dept table.