#dbms #normalisation

Question

2NF , 3NF, BCNF decomposition allowed if lossless and dependency preserving , both conditions must preserve. Otherwise given decomposition wrong for normal forms. Is this statement correct????

Answer 1

The statement is mostly correct, but it needs some clarification.

In the process of database normalization, the goal is to decompose a relation (table) into smaller relations that are in higher normal forms (e.g., from 1NF to 2NF, 3NF, BCNF, etc.) while preserving the functional dependencies and avoiding data anomalies.

A decomposition is said to be lossless if the original relation can be reconstructed from the smaller relations using a join operation. A decomposition is said to be dependency preserving if it preserves all the functional dependencies of the original relation.

If a decomposition is both lossless and dependency preserving, then it is considered a valid decomposition. However, if a decomposition is not lossless or not dependency preserving, then it may lead to data anomalies, and it is considered a wrong decomposition.

Therefore, the statement "2NF, 3NF, BCNF decomposition allowed if lossless and dependency preserving, both conditions must preserve. Otherwise given decomposition is wrong for normal forms" is mostly correct. However, it is important to note that not all decompositions that satisfy the lossless and dependency preserving conditions are necessarily in higher normal forms. Some decompositions may introduce redundant relations, and some may not be in the highest normal form possible. Therefore, it is important to carefully evaluate the decompositions and choose the one that minimizes redundancy and maximizes the normal form.

Comments

Thanks. But I have one more doubt like imagine relation R(ABC) and FD set { AB→ C, C→ A }, if I go for decomposition to satisfy higher normal form(here it will be BCNF because R is already in 3NF) to avoid/reduce data redundancy , then I can get BCNF by decomposing it into R(AC) and R(CB) where BCNF is satisfying and lossless join satisfying but dependencies are not preserving. So ultimately we can say it is not BCNF after doing decomposition because dependencies are not preserving here. Highest NF can be 3NF here in R(ABC).  Correct??

---

Yes, that's correct. In the example you gave, the original relation R(ABC) is already in 3NF, but not in BCNF. To bring it to BCNF, you decomposed it into R(AC) and R(CB), which is a valid decomposition that is in BCNF and lossless join preserving. However, this decomposition is not dependency preserving because the original functional dependencies AB→C and C→A are not preserved in either of the decomposed relations.

Therefore, we can say that after the decomposition, the resulting relations are in BCNF, but the original relation is not in BCNF. The highest normal form that can be achieved through this decomposition while preserving the functional dependencies is 3NF.

---

yes got it. thanks