Normalization
What is Normalization ?
" It is the process of reducing a large table into smaller tables in order to remove
redundancies and anomalies by identifying their functional dependencies is
known as Normalization . "
Or
"The process of decomposing a large table into smaller table is known as
Normalization ."
Or
"Reducing a table to its Normal Form is known as Normalization . "
Table
T3
T1 T2
What is Normal Form ?
A table without redundancies and anomalies are said to be in Normal Form .
New Section 1 Page 8
Redundancies:
The repetition of unnecessary data.
Anomalies:
The side effect caused due to DML operations (insert, update, delete).
Levels of Normal Form .
1. First Normal Form ( 1NF )
2. Second Normal Form ( 2NF )
3. Third Normal Form ( 3NF )
4. Boyce - Codd Normal Form ( BCNF )
Note : If any Table / entity is reduced to 3NF , then the table is said to be normalized.
New Section 1 Page 9
1. First Normal Form ( 1NF ) :
- No duplicates records .
- Multivalued data should not be present .
QSPIDERS
QID NAME COURSE
QID NAME C1 C2 C3
1 A JAVA
1 A JAVA MT
2 B JAVA , SQL
2 B JAVA SQL
3 C MT , SQL
3 C SQL MT
1 A MT
2. Second Normal Form ( 2NF )
- Table should be in 1NF
- Table should not have Partial Functional Dependency .
EMPLOYEE - ( EID , ENAME , SAL , DEPTNO , DNAME , LOC )
Eid ename sal Deptno dname Loc
1 A 100 10 D1 L1
2 B 120 20 D2 L2
3 C 320 10 D1 L1
4 D 251 10 D1 L1
Eid - ename ,sal
Deptno - dname , loc
composite key attribute results in PFD:-
( Eid , deptno ) -> ( Ename , Sal , Dname , Loc )
R1 - ( EID , ENAME , SAL )
R2 - ( DEPTNO , DNAME , LOC ) Deptno dname Loc
Eid ename sal
10 D1 L1
1 A 100
20 D2 L2
2 B 120
10 3 C 320
4 D 251
3. Third Normal Form ( 3NF )
- Table should be in 2NF .
- Table should not have Transitive Functional Dependency .
Transitive functional dependency:-
- Employee - ( EID , Ename , Sal , comm , Pin code , state , country )
- EID -> ENAME
SAL COMM
PINCODE -> STATE
COUNTRY
R1- ( eid , ename , comm )
R2- ( pincode , state , country )
11
