question

UfuckVT avatar image
UfuckVT asked

Changing Relationship into BCNF

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?

dependencyrdbms
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.