Till now, we have covered up to the types of functional dependencies. Before reading this post, please read the following posts on Normalization:-
- Part 1: Introduction to Database Normalization
- Part 2: Closure Set of Attributes Method
- Part 3: Types of Functional Dependencies
In this post, we look at some important definitions and move up to the hierarchy of normal forms. Also, we show how to state the highest normal form of a relation.
**This post is entirely theory and may be boring. It requires multiple readings.
Prime
Attribute
·
Any attribute which is part of at least one
candidate key is called a prime attribute.
·
If AF is a candidate key, then A and F are prime
attributes.
Non-prime
attribute
·
An attribute in a relation which is not part of
any candidate key is called a non-prime attribute.
·
If in a relation R(A,B,C,D,E) , AD is the only
candidate key, then B,C and E are non-prime attributes.
Normal Forms
·
It is a property of relations which indicates
the amount of redundancy in the relation.
·
Normal form and degree of redundancy are
inversely proportional.
·
Normalization is a systematic approach applied
on relations to reduce the degree of redundancy and achieve progressively
higher normal forms.
Normalization
Procedure
1)
Determine the highest possible normal form of
the given relation.
2)
Decompose the relation from its existing normal
form to higher normal forms.
Procedure to
find highest Normal Form of the given relation
1)
Find all possible Candidate Keys of the given
relation.
2)
Identify all the existing prime and non-prime
attributes.
3)
Identify all the existing Full Dependencies,
Partial Dependencies, Transitive Dependencies and Overlapping Candidate Key
Dependencies.
4)
Refer to the definition and hierarchy of Normal
Forms for finding the highest possible Normal Form of the given relation.
Hierarchy of
Normal Forms
Normalization is a hierarchical procedure and each stage is
designated by a particular normal form. The hierarchy of normal forms has been
shown in the diagram given below:-
First Normal
Form (1NF)
·
A relation is said to be in 1NF if all the
values in the relation are atomic and single-valued.
·
According to Codd’s rules of Relational Database
Management Systems, every relation will always be in 1NF by default.
·
The relation instance given below does not
satisfy 1NF criterion (it is not in 1NF) as Email is not a single-valued
attribute.
Name
|
Class
|
Email
|
Ravi
|
1
|
|
Rahul
|
2
|
|
Rakesh
|
4
|
·
The above relation instance can be converted to
1NF as shown below:-
Name
|
Class
|
Email
|
Ravi
|
1
|
|
Ravi
|
1
|
|
Rahul
|
2
|
|
Rakesh
|
4
|
Second Normal
Form (2NF)
A relation is said to be in 2NF if:-
·
It is in 1NF.
·
It has no partial dependencies.
Third Normal
Form (3NF)
A relation is said to be in 3NF if:-
·
it is in 2NF.
·
It has no transitive dependencies.
Boyce Codd
Normal Form (BCNF)
A relation is said to be in BCNF if:-
·
it is in 3NF and has no overlapping candidate key dependencies.
The hierarchy of Normal Forms can be represented by the above diagram. So, if a relation is in BCNF, it has to be in 3NF already but the reverse may not be true. Similar statments can be made for the other normal forms as well.
In this post, we have seen the hierarchy of Normal Forms. In the next post, we will look at examples on Normal Forms.
The hierarchy of Normal Forms can be represented by the above diagram. So, if a relation is in BCNF, it has to be in 3NF already but the reverse may not be true. Similar statments can be made for the other normal forms as well.
In this post, we have seen the hierarchy of Normal Forms. In the next post, we will look at examples on Normal Forms.
Was it a nice read? Please subscribe to the blog by clicking on the 'Follow' button on the right hand side of this page. You can also subscribe by email by entering your email id in the box provided. In that case, you will be updated via email as and when new posts are published. Cheers!
Click here to read Part 5
No comments:
Post a Comment