I'm learning DBMS and normalization and came across the following exercise while doing so. For the following problem:
Consider the relation R(b,e,s,t,r,o,n,g) with functional dependencies b,s -> e,r,o,n b -> t b -> g n -> b o -> r (a) identify candidate keys (b) identify prime attributes (c) state the highest normal form of this table
I believe (a) is b, s since they identify all properties without redundancy.
(b) would also be b, s because they make up the candidate keys of (a).
For numerous reasons, (c) would be 1-NF. Because there are partial dependencies n -> b, it does not meet 2-NF. The previously mentioned functional dependency is only dependent on b and not s, resulting in partial dependence. It does not satisfy 3-NF because o -> r indicates that one non-prime attribute is dependent on another. Because 3-NF is not satisfied, BCNF is not satisfied.
Finally, if I changed the table till it was in BCNF, would dividing the relation R into:
R1(b, e, s, r, o, n) with b, s -> e, r, o, n
and
R2(b, t, g) with b -> t and b -> g
while ignoring n->b and o->r fulfill BCNF?
I'm especially perplexed by the final sentence about fulfilling BCNF. I would appreciate any advice/thoughts on all steps! This website suggests that there's a transitive dependency b ⟶ o ⟶ r which prevents R1 from being in BCNF. The {b, s} ⟶ n and n ⟶ b pair also mean R1 is not in BCNF. Is that correct?