I have been told a way to obtain lossless join BCNF but I don't know how to calculate candidate keys (also called super key[s] in some cases) and trivial dependencies.
I have been given the following relation:
R = (A, B, C, D, E, F)
R is relation name, and
A, B, C, D, E, F are its attributes.
The given functional dependencies (FDs) are:
A B → C D E F
C → D
E → F
The algorithm says that the first FD is not non-trivial, but here
A B is a super key so we don't break original relation based on this.
Moving to the second FD, it's neither non-trivial nor
C is candidate key. So, we break the relation:
R1 = (A, B, C, E, F)and
R2 = (C, D)
Moving to the third FD it's nor non-trivial neither
E is candidate key. So, we break the relation again:
R11 = (A, B, C, E),
R12 = (E, F)and
R2 = (C, D)
Hence, we obtained Loss Less BCNF.
But, I always get confused on how to calculate candidate key(s) and see if a FD is non-trivial, although I am quite aware of the definitions1. I also googled and read some documents but still didn't understood this properly. Please help me to figure out candidate key(s), super key(s), non-trivial FD(s) in the easiest way possible so that I never struck with the concept again.
Why do we only see left one's
(A B, C, E) for candidate key and not the right one's
( F, D, C D E F) in the three FDs written above?
1 My definitions:
Super key: A set of one or more attributes which taken collectively, allow us to uniquely identify an entity in an entity set.
Candidate Key: A super key for which no proper subset is also a super key.