Heading

The process of removal of duplicate or redundant data from the relation is called normalization. Normalization improves data integrity and data consistency. It also increases the storage efficiency of the database.

The main aim of normalization is to decrease data redundancy and to achieve data integrity and data consistency. If the same data is stored in different places of a database, it may lead to data inconsistency, and insufficient use of storage. This decreases the efficiency of the database. Hence, normalization is very much important in the design of an efficient database.

The different normal forms are:
i. Un-normalized form
ii. First Normal Form
iii. Second NormalForm
iv. Third Normal Form
v. Boyce-Codd Normal Form
vi. Fourth Normal Form
vii. Fifth Normal Form
viii. Domain Key Normal Form

Every attribute in the table must be a single-valued attribute. If a relation contains a composite or multi-valued attribute, it violates 1 st normal form. Entries in the column should be of the same data type. Each row must be uniquely identifiable.
The above table has multi-valued Course attribute. Hence, the table is not in 1NF. To convert it into 1NF, one of the following methods can be followed. Method 1 Method 2.

For a relation to be in 2 nd normal form , the following conditions must be satisfied by the relation:
a. The relation must be in 1 st normal form.
b. The relation must not contain any partial dependency. If the partial subset of candidate key determines non-prime attribute, it is called partial dependency.
To convert a relation that has a partial dependency to 2NF, we need to split the table into two tables. 2NF reduces the redundant data from getting stored in memory.

For a relation to be in 3NF, the following conditions must be satisfied.
1. The relation must be in 2NF
2. The non-prime attributes should not have a transitive dependency.
3.To satisfy 3NF, either LHS should be a super key or RHS should be a prime attribute

a. BCNF does not allow dependencies between attributes that belong to a candidate key
b. A relation is in BCNF if it is in 3NF and for every FD, LHS is super key
c. A relation is in BCNF if and only if in every non-trivial functional dependency X→Y, X is a super key
d. BCNF can lead to lossy decomposition or dependencies may be lost.
e. BCNF is free from redundancy.
f. Steps to convert a relation into BCNF
i. Place the 2 candidate primary keys (which are dependent on each other) in separate entities

4NF
For a relation to be in 4NF
1. It should satisfy BCNF
2. It should not have a multi-valued dependency.
It states that the table must not contain more than 1 multivalued dependency (can contain 1 multivalued dependency) 4NF eliminates independent many-to-one relationships between columns
5NF
1.The 5 th Normal Form is satisfied when all tables are decomposed into as many tables as possible to avoid redundancy.
2. Conditions for a relation to be in 5NF o The relation should be in 4NF
It should not have join dependency
3.The 5 th Normal Form is also known as the Project Join Normal Form or Projected Normal Form.

If a part (or a partial subset) of a candidate key determines a non-prime attribute, it is called partial dependency.
Part of primary key → non-prime attribute Partial dependencies are removed by the Second Normal Form.

a. Transitive dependency
i. If A→B and B→C are two FDs, then A→C is called transitive dependency
b. If there is a transitive dependency, we have to decompose the relation into two
c. The transitive dependency can be solved by the following steps:
i. Move all items involved in the transitive dependency (B and C in the above example) to a new entity
ii. Identify the primary key for the new entity iii. Place the primary key for the new entity as a foreign key on the original entity
d. The 3NF removes the transitive dependencies.