Normalisation

Question

It is said that if a relation is in bcnf then it is also in 3nf. Then why a lossless and non-dependency preserving decomposition qualifies to be in bcnf but not in 3nf. I am not getting it. Please clear my concept.

Answer 1

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,

C	B
1	1
2	1
3	0
4	0

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.

Comments

Whatever you said Sir I have understood  but my doubt was that the tables that you obtained after decomposition were in bcnf but not in 3nf(as lossless but not dependency preserving). But we know that every​ bcnf relation is also in 3nf. Here it is not the case. The decomposition is in bcnf but not in 3nf.
Does it mean that.. All bcnf "relations" are in 3nf but all "decompositions" into bcnf may not be in 3nf?

---

I am also a Student, please dont call me Sir.

If we observe the process, A 3NF relation is  decomposed to get BCNF relations. We must understand that, FD preserving is optional in any normal form but lossless decomposition is must.

BCNF is more sticter than 3NF, ya we can say all relations in BCNF are also in 3NF with respect to lossless decomposition.

---

Minipanda to be in 3nf it is not necessary relation  should be lossless or decomposition preserving...only condition for 3nf is :3NF : Relation with X -> Y form FD where X is Super key (or) Y is a Prime attribute...so the relation is in bcnf then it is also in 3nf. .... has different meaning

---

Dependency preserving and loss less join both are necessary as if a is son of b than it has to be preserve in table

---

Sarbesh dependency preserving and lossless join are required while DECOMPOSITION of a relation.....

---

Normalization is used to remove redundancy, in other words, normalization involves decomposing a relation/table into smaller relations. So after decomposing we are checking for the above mentioned properties i.e FD preserving and lossless decomposition. Every relation in BCNF is also in 3NF because we will obtain the BCNF after the we bring relation to 3NF.