Modifying Relation into BCNF

Question

I am learning DBMS and normalization and I have come across the following exercise. 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 think that (a) would be {b, s} since they identify all attributes without redundancy.

(b) would also be {b, s} since they compose the candidate keys of (a).

(c) would be 1-NF for several reasons. It does not satisfy 2-NF since there are partial-dependencies n -> b. The aforementioned functional dependency only depends on b and not s, hence partial-dependency. It does not satisfy 3-NF since o -> r indicates that a non prime attribute depends on another non-prime attribute. BCNF is not satisfied since 3-NF is not satisfied.

Lastly, if I were to modify the table until it is in BCNF, would splitting 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 eliminating the n -> b and o -> r satisfy BCNF?

I am most confused on the last part regarding satisfying BCNF. I would greatly appreciate any help/thoughts on all steps!

Answer 1

The schema has two candidate keys: {b, s} and {n, s}. You can verify that both are keys be computing the closures of the two sets of attributes.

So the prime attributes are b, s, and n.

You are correct in saying that the relation is not in 2NF, neither in 3NF.

Your proposed decomposition does not produces subschemas in BCNF, since in R1 the dependency o → r still holds, and o is not a superkey of R1.

The “classical” decomposition algorithm for BCNF produces the following normalized schema:

R1(b g t)
R2(o r)
R3(b n)
R4(e n o s)

but the dependencies

b s → e
b s → n
b s → o
b s → r

are not preserved in the decomposition.

A decomposition in 3NF that preserves data and dependencies is the following:

R1(b e n o s) 
R2(b g t)    
R3(o r)

In this decomposition, R2 and R3 are also in BCNF, while the dependency n → b in R1 violates the BCNF.