GeeksForGeeks AIM 2 - BCNF - databases

Question

How many minimum relations are required for the following Relation R(A, B, C, D, E) with FD {A → BC, CD → E, B → D, E → A} to convert into BCNF without violation of lossless and dependency preserving decomposition _________.

Comments

> In R(A, B, C, E), there is A → BC and E → A. Here, E is the only candidate key and thus the dependency A → BC is violating BCNF, right?

CD →  E, B → D;

BC→ CD;

BC→ E;

Now, the decomposition becomes

R(ABCE)

Where
A → BC

E → A

BC → E

Which is in BCNF.

---

Both $E, A$ are candidate keys. If there was a relation $E\to A, A\to E$ then which would be considered a candidate key?. Deriving $E$ from a $A$ takes a lot of steps, but that doesn’t mean it doesn’t exist. And, we could argue with the closure set of FD for $A$.

Read Defs.

A SuperKey is a set of attributes that uniquely identify a tuple in a relation. Here, both $\{A\}, \{E\},\{AB\}$ is a superkey.

A candidate key is a superkey in which deletion of any attribute makes it lose its superkey status. Both $\{A\}, \{E\}$ conforms to this not $\{AB\}$

---

Also, I agree with the setter’s answer. It’s technically correct, but at what cost. The Main Point of using FDs is to remove redundancies, just to ensure that a dependency lives on, we introduce redundancy in this case.

For example, in $r(A,B,C)$ $AB\to C, C\to B$ I think the only way it conforms to FD preserving lossless BCNF decomposition is to create relation $r(A,B,C), AB\to C$(omitting the $C\to B$) and $r(C,B), C\to B$ where it makes to original relation even worse with redundancy under BCNF decomposition.
 

In the above example, we introduce $r(CDE)$ just to ensure $CD\to E, E\to CD$ just because it isn’t present in the previous two relations.

Anyway, that’s my ramble, and I’m sure people might disagree with my take on it instead of going with the flow. I think it’s worthwhile to ponder.

Answer 1

@atulcse

Candidate Keys: {A, E, CD, BC }....

Decomposition to Bcnf:

1. R(ABC)

2. R(CDE)

3. R(BD)

4. R(EA)

above decomposition is not only in Bcnf but also in 3nf....

3 relation :: BD, ABCE, CDE ...

Hence, it is dependency preserving and lossless.…

 

1. https://gateoverflow.in/102652/Bcnf-decomposition 

 

2. https://gateoverflow.in/60876/Made-easy