The Normal forms are used to remove redundancy in the given database. But removing redundancy should not loose any previous data. We should not loose any data while removing the redundancy.
3NF : Relation with X -> Y form FD where X is Super key (or) Y is a Prime attribute.
let R(ABC) with FDs as AB -> C and C -> B, with AB as CK. This R is in 3NF as it satisfies the above condition.
Data be,
A B C
0 |
1 |
1 |
1 |
1 |
2 |
1 |
0 |
3 |
0 |
0 |
4 |
4 |
1 |
1 |
5 |
1 |
1 |
6 |
1 |
1 |
7 |
1 |
1 |
The above table is 3NF but we are still having redundancy in B and C column at last 4 rows.
Can we able to remove this also?
Yes we can, by decompsing into BCNF
as R1(AB) , AB as CK , R2(AC), AC as CK and R3(CB) and C as CK
The R3 will have data look like this,
Here we don't have redundancy compared to 3NF.
The Normalization is done to remove redundancy as much as possible. Since FD preserving is not a compulsory to have in decomposed relation, but LossLess is must as we have to preserve data integrity by not adding an extra row or deleting of row. Hence Lossless decomposition property is necessary for Normalization but FD preserving is not.
Please correct me if i am wrong. I have tried to explaing what i knew.