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
(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 in 1NF for several reasons. It does not satisfy 2NF since dependency n -> b is partial, since it only depends on b and not s. It does not satisfy 3NF since o -> r indicates that a non prime attribute depends on another non-prime attribute. BCNF is not satisfied since 3NF is not satisfied.
Would splitting R into
What, if anything, is wrong?
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.