Normalization
Process: First,
Second & Third
Normal Form and
BCNF .
Lossless-join and
Dependency Preservation
Properties
Lossless-join property enables us to find
any instance of the original relation from
corresponding instances in the smaller
relations.
Dependency preservation property enables
us to enforce a constraint on the original
relation by enforcing some constraint on each
of the smaller relations.
Loss-less and lossy join
decomposition B C
A B C
2 1
1 2 1
2 2 2 2 2
R R1
3 3 2 3 2
A B
1 2
2 2
3 3
3
The Process of
Normalization
Unnormalized Form (UNF)
A table that contains one or more
repeating groups.
To create an unnormalized table
Transform the data from the information
source (e.g. form) into table format with
columns and rows.
9
1NF
According to EF Codd ,The table should not be
containing any multivalued attribute.
Nominate an attribute or group of attributes to act
as the key for the unnormalized table.
Identify the repeating group(s) in the unnormalized
table which repeats for the key attribute(s).
2NF
Based on the concept of full functional
dependency.
Full functional dependency indicates that if
A and B are attributes of a relation,
B is fully dependent on A if B is functionally
dependent on A but not on any proper subset of
A.
2NF
Table must be in 1NF .
All the non- Primary key attributes should be
fully functional dependent on candidate key
In other words, there should be no partial
dependency in the table
1NF to 2NF
Identify the primary key for the 1NF relation.
Identify the functional dependencies in the
relation.
If partial dependencies exist on the primary key
remove them by placing, then in a new relation
along with a copy of their determinant.
Third Normal Form (3NF)
Based on the concept of transitive
dependency.
Transitive Dependency is a condition where
A, B and C are attributes of a relation such that if
A B and B C,
then C is transitively dependent on A through B.
(Provided that A is not functionally dependent on
B or C).
21
3NF
Table must be in 2nd Normal form
There should be no transitive dependency in
table
2NF to 3NF
Identify the primary key in the 2NF relation.
Identify functional dependencies in the
relation.
If transitive dependencies exist on the
primary key remove them by placing them in
a new relation along with a copy of their
dominant.
24
BCNF
Table should be in 3rd NF .
LHS of each FD should be CK or SK.
Let's take an example
R(ABCD)
FD: AB CD, D A
R(ABCD)
FD: A BCD,BC AD ,D B
R (A,B,C)
FD: A B, B C, C A
R(A,B,C,D,E)
A BCDE,BC ACE , D E
R(A,B,C,D,E)
AB CDE, D A