Normalization
Normalization
• Normalization is the process of organizing the
data in the database.
• Normalization is used to minimize the
redundancy from a relation or set of relations. It
is also used to eliminate the undesirable
characteristics like Insertion, Update and Deletion
Anomalies.
• Normalization divides the larger table into the
smaller table and links them using relationship.
• The normal form is used to reduce redundancy
from the database table.
Types of Normal Forms
• There are the four types of normal forms:
First Normal Form (1NF)
• A relation will be 1NF if it contains an atomic
value.
• It states that an attribute of a table cannot hold
multiple values. It must hold only single-valued
attribute.
• First normal form disallows the multi-valued
attribute, composite attribute, and their
combinations.
• Example: Relation EMPLOYEE is not in 1NF
because of multi-valued attribute EMP_PHONE.
• EMPLOYEE table:
Second Normal Form (2NF)
• In the 2NF, relational must be in 1NF.
• In the second normal form, all non-key
attributes are fully functional dependent on
the primary key
• Example: Let's assume, a school can store the
data of teachers and the subjects they teach.
In a school, a teacher can teach more than
one subject.
Third Normal Form (3NF)
A relation will be in 3NF if it is in 2NF and not contain any
transitive partial dependency.
• 3NF is used to reduce the data duplication. It is also used to
achieve the data integrity.
• If there is no transitive dependency for non-prime
attributes, then the relation must be in third normal form.
• A relation is in third normal form if it holds atleast one of
the following conditions for every non-trivial function
dependency X → Y.
• X is a super key.
• Y is a prime attribute, i.e., each element of Y is part of some
candidate key.
• Example:
EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP dependent on EMP_ID.
The non-prime attributes (EMP_STATE, EMP_CITY) transitively dependent on super
key(EMP_ID). It violates the rule of third normal form. We need to move the EMP_CITY
and EMP_STATE to the new <EMPLOYEE_ZIP> table, with EMP_ZIP as a Primary key.
Boyce Codd normal form (BCNF)
• BCNF is the advance version of 3NF. It is stricter
than 3NF.
• A table is in BCNF if every functional dependency
X → Y, X is the super key of the table.
• For BCNF, the table should be in 3NF, and for
every FD, LHS is super key.
Example: Let's assume there is a company where
employees work in more than one department.
The table is not in BCNF because neither EMP_DEPT nor
EMP_ID alone are keys.
To convert the given table into BCNF, we decompose it into
three tables:
Functional dependencies:
EMP_ID → EMP_COUNTRY
EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
Candidate keys:
For the first table: EMP_ID
For the second table: EMP_DEPT
For the third table: {EMP_ID, EMP_DEPT}
Inclusion Dependency
• Inclusion dependencies are quite common. They typically show little
influence on designing of the database.
• The inclusion dependency is a statement in which some columns of a
relation are contained in other columns.
• The example of inclusion dependency is a foreign key. In one relation, the
referring relation is contained in the primary key column(s) of the
referenced relation.
• Suppose we have two relations R and S which was obtained by
translating two entity sets such that every R entity is also an S entity.
• Inclusion dependency would be happen if projecting R on its key
attributes yields a relation that is contained in the relation obtained by
projecting S on its key attributes.
• In inclusion dependency, we should not split groups of attributes that
participate in an inclusion dependency.
• In practice, most inclusion dependencies are key-based that is involved
only keys.
Decomposition
Decomposition of a relation is done when a
relation in relational model is not in
appropriate normal form. Relation R is
decomposed into two or more relations if
decomposition is lossless join as well as
dependency preserving.
Decomposition is of two types :
• Lossless join Decomposition
• Lossy Decomposition
Lossless Join Decomposition
Consider there is a relation R which is decomposed
into sub relations R1 , R2 , …. , Rn.
• This decomposition is called lossless join
decomposition when the join of the sub relations
results in the same relation R that was
decomposed.
• For lossless join decomposition, we always have-
R1 ⋈ R2 ⋈ R3 ……. ⋈ Rn = R , where ⋈ is a
natural join operator
• Example
• Consider the following relation R( A , B , C )-
A B C
1 2 1
2 5 3
3 3 3
R( A , B , C ) relation is decomposed into two sub relations R1( A , B ) and R2( B , C )-
For lossless decomposition, we must have-
A B B C R1 ⋈ R2 = R
1 2 2 1 if we perform the natural join ( ⋈ ) of the sub
2 5 5 3 relations R1 and R2 , we get-
A B C
3 3 3 3
1 2 1
R1( A , B ) R2( B , C ) 2 5 3
3 3 3
Lossy Join Decomposition
Consider there is a relation R which is decomposed into
sub relations R1 , R2 , …. , Rn.
• This decomposition is called lossy join decomposition
when the join of the sub relations does not result in
the same relation R that was decomposed.
• The natural join of the sub relations is always found to
have some extraneous tuples.
• For lossy join decomposition, we always have- R1 ⋈
R2 ⋈ R3 ……. ⋈ Rn ⊃ R where ⋈ is a natural join
operator
• Example : Consider that we have table STUDENT with
three attribute roll_no , sname and department.
Student
Roll_no Sname Dept
111 parimal COMPUTER
222 parimal ELECTRICAL
This relation is decomposed into two relation no_name and name_dept :
No_name name_dept
Roll_no Sname Sname Dept
111 parimal parimal COMPUTER
222 parimal parimal ELECTRICAL
In lossy decomposition ,spurious tuples are generated when a natural join is applied to
the relations in the decomposition.
stu_joined
Roll_no Sname Dept The decomposition is a
111 parimal COMPUTER bad decomposition or
111 parimal ELECTRICAL Lossy decomposition.
222 parimal COMPUTER
222 parimal ELECTRICAL
Multivalued Dependency
Multivalued dependency occurs when two attributes in a table are
independent of each other but, both depend on a third attribute.
• Example: Suppose there is a bike manufacturer company which
produces two colors(white and black) of each model every year.
BIKE_MODEL MANUF_YEA COLOR Here columns COLOR and MANUF_YEAR
R
are dependent on BIKE_MODEL and
M2001 2008 White independent of each other.
In this case, these two columns can be
M2001 2008 Black called as multivalued dependent on
M3001 2013 White BIKE_MODEL. The representation of
these dependencies is shown below:
M3001 2013 Black BIKE_MODEL → → MANUF_YEAR
M4006 2017 White BIKE_MODEL → → COLOR
M4006 2017 Black This can be read as "BIKE_MODEL
multidetermined MANUF_YEAR" and
"BIKE_MODEL multidetermined
• The above relation violates Fourth Normal Form in Normalization.
• To correct it, divide the table into two separate tables and break
Multivalued Dependency −
BIKE_MODEL MANUF_YEAR BIKE_MODEL COLOR
M2001 2008 M2001 White
M2001 2008 M2001 Black
M3001 2013 M3001 White
M3001 2013 M3001 Black
M4006 2017 M4006 White
M4006 2017 M4006 Black
What is Join Dependency?
• If a table can be recreated by joining multiple
tables and each of this table have a subset of
the attributes of the table, then the table is in
Join Dependency. It is a generalization of
Multivalued Dependency.
• Join Dependency can be related to 5NF,
wherein a relation is in 5NF, only if it is already
in 4NF and it cannot be decomposed further.
• Example
<Employee>
EmpName EmpSkills EmpJob (Assigned Work)
Tom Networking EJ001
Harry Web Development EJ002
Katie Programming EJ002
The above table can be decomposed into the following three
tables; therefore it is not in 5NF: