PostDatabase Management System / RDBMS

09-Relational-Database-Design-BCNF

2023-11-03

Relational-Database-Design-BCNF

Lossless-join Decomposition

For the case of $R = R_{1} \cup R_{2}$ a decomposition of $R$ into $R_{1}$ and $R_{2}$ is lossless join if and only if at least one of the following dependencies is in $F^{+}$ :

$R_{1} \cup R_{2} \to R_{1}$ $R_{1} \cup R_{2} \to R_{2}$

Dependency Preservation

Let the schema $R$ is decomposed into $R_{1}, R_{2}, \dots R_{n}$ .
Let $F_{i}$ be the subset of dependencies $F^{+}$ that only includes attributes in $R_{i}$ for $1 \leq i \leq n$ ,
The decomposition is dependency preserving, if ${(F_{1} \cup F_{2} \cup \dots \cup F_{n})}^{+} = F^{+}$
If the decomposition is not dependency preserving, then checking updates for violation of functional dependencies may require computing joins, which is expensive.

Example: $R = {A, B, C}$ $F = {A \to B, B \to C}$ can be decomposed in two different ways

$R_{1} = {A, B}, R_{2} = {B, C}$
- Lossless-join decomposition
  - $R_{1} \cap R_{2} = {B} a n d B \to B C$
  - Dependency preserving
$R_{1} = {A, B}, R_{2} = {A, C}$
- Lossless-join decomposition
  - $R_{1} \cap R_{2} = {A} a n d A \to A B$
  - Not dependency preserving (cannot check $B \to C$ without computing $R_{1} ⨝ R_{2}$ )

BCNF