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!
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.