FOR FREE YEAR SOLVED

Normalization

Before seeing this chapter please follow the previous chapter: Schema Refinement

Concept of Normalization

We already discuss the insertion, deletion, and update problem in the previous chapter Schema Refinement because of redundancy and to reduce redundancy we decompose the table.

As per the previous example (Schema Refinement) we continue here further discussion.

The above redundancy is coming from Functional Dependency.

Cid  Cname Fid Fname Salary – this FD create redundancy because as FD rule we know every time Cid is the same all the data of Cname Fid Fname Salary will be the same 

To reduce the redundancy we decompose the table into two tables to solve the problem of insertion, deletion, and updation (previously said).

Now we check two things after decomposition 

1. Lossless Decomposition

2. Dependency Preserving

Checking lossless decomposition:

Dependency Preserving

So, it is dependency preserving.

# Why we check lossless decomposition because some query we have to join the two tables to get the answer 

Then in any query which is done by joining, we will get duplicate value because 

# And why we check dependency preserving because by FDs we get any answer from a query like if we want to know Sid whose has a relation with Fname, we will get the answer because 

So, at the time of decomposition, if we lost Function Dependencies of the original table, we will not get the right answer as per the query.

Some important point:

# Another important thing if we decompose the table it reduces redundancy but it increases query processing time and also increases maintenance cost if we decompose several time to reduce redundancy.

# Now quotation is how we know in what position we decompose table, actually Normalization says what is the point from where we decompose table.

# Normalization says us the position of the table from where we decompose the table.

Important point: Normalization says some set of a rule which says us to better way decompose the table.

Different type Normalization Form

1NF  2NF 3NF  BCNF 4NF  5NF

We use 1NF, 2NF, 3NF, BCNF to reduce redundancy which is only created by FDs 

X → Y

We use 4NF, 5NFand onwards to reduce redundancy which is created by multi-valued dependency.

X ↠ Y.

So, we only discussed here that redundancy which is created FDs.

So, we can also say at BCNF, redundancy will be 0%, which is created by FDs.

Actually, we normalized data up to 3NF, if we normalized data at the last level then query processing time will be increased and also maintenance cost. Because more normalization means more decomposition.