BCNF and 3NF

Question

1) dependency preservation
2)lossless join

a)If a relation is in 3NF , which of the above points is guaranteed.
b)If a relation is in BCNF , which of the above points is guaranteed

[ I am confused  right now, can I say , if a relation is in BCNF, then is it  by default  lossless/dependency preserving straight away , or is it the fact that,if I derive BCNF decomposed relations using a particular algorithm, then only  I can say that the decomposed relations is lossless/dependency preserving same goes for 3NF].

Please help!

Comments

https://gateoverflow.in/66380/normalization

---

To get Clear Answer of this question, I mailed Prof. Korth, Navathe, & Sudarshan. Got their Reply, discussed in the below lecture:

Watch this: https://youtu.be/Epy_Vw4gFpo?feature=shared 

Answer 1

The question is not making sense as it is. So, making correction.

Let $R$ be a relation. Now if we decompose it and make the decomposition $3NF$, then it is possible to satisfy both lossless and dependency preserving. i.e., for any relation there always exist a decomposition to 3NF, which is guaranteed to satisfy both the given properties.

For BCNF, the decomposition is not guaranteed to satisfy both the properties. Sometimes when lossless property is satisfied, dependency preserving is no longer possible.

Comments

@Arjun $sir$

This is what I got after reading chapters from korth & navathe.

---

By now we know that it is not possible to have all three of the following:

(1) guaranteed nonlossy design

(2) guaranteed dependency preservation

(3) all relations in BCNF.

As we have said before, the first condition is a must and cannot be compromised. The second condition is desirable, but not a must, and may have to be relaxed if we insist on achieving BCNF.

Now we give an alternative algorithm where we achieve conditions 1 and 2 and only guarantee 3NF.

---

Hence lossless join is always guaranteed in BCNF.

---

@Arjun sir

Can you verify this

$R(ABCDE)$

$A\rightarrow BCDE,D\rightarrow CE$

$CK: A$

Not satisfying BCNF. Needs to decompose.

$ABCDE\rightarrow \fbox{ABC}:\ \ \ A\rightarrow BC$

$\downarrow$

$\fbox{CDE}:\ \ \ D\rightarrow CE$

Now in BCNF but does not satisfy Loss less join and Dependency preservation.

Hence it can be said that it is not necessary that if BCNF then will always satisfy Loss less join.

courtesy: @Mk Utkarsh

---

@KUSHAGRA गुप्ता your decomposition is wrong. It is losi. You’ll get extra tuple.

During decomposition, there must be a common attribute and that common attribute must be a key for one of the table. Here the two tables will be (ABD) and (DCE) where D is the key for DCE table

Answer 2

If a relation is in 3NF , which of the above points is guaranteed ==> both are neccessary  

If a relation is in BCNF , which of the above points is guaranteed ==> lossless join is guaranteed but dependency preserving may or may not be guaranteed

https://www.iitg.ernet.in/awekar/teaching/cs344fall11/lecturenotes/september%2006.pdf

Comments

Is the question correct?

---

Suppose we have relation

R(A,B,C,D)

B-->C

A-->D

SO We decompose R into BCNF

R1(A,D)

R2(B,C)

It is dependency preserving but not lossless thus neither lossless nor dependency preserving nature of BCNF is guaranteed

---

This is Wrong answer.

To get Clear Answer of this question, I mailed Prof. Korth, Navathe, & Sudarshan. Got their Reply, discussed in the below lecture:

Watch this: https://youtu.be/Epy_Vw4gFpo?feature=shared 

Answer 3

R[A,B,C,D,E,F] 
A->BCDEF
BC->ADEF
D->E
D->B
B->F
Decompose this into 3NF and BCNF and find the diff ;)

Answer 4

i think if a relation is in 3NF then both lossless and dependency preserving are guaranteed but in case of BCNF only lossless is guaranteed.

Comments

take R(A,B,C) and FD are AB -> C & C-> B. This is not in BCNF as AB is the key ,so second FD is not in BCNF,when we decompose it,we get two relations, R1(A,C) and R2(C,B) hence we loose AB -> C dependency

---

In this case both, AB and AC are the keys.