Q1.
a)
Key?
A A
B B
C C
D D
AB A, B, C, D YES
AC A, C
AD A, D
BC B, C, D
BD B, C, D
CD C, D
ABC A, B, C, D SK
ABD A, B, C, D SK
ACD A, C, D
BCD B, C, D
Key is AB, ABC and ABD are superkeys.
b)
R is not in BCNF because at least one of the FDs, eg. violates BCNF.
R is not in 3NF because D is not part of a key.
Decompose R based on :
Decomposition is BCD, ABC. Remaining FDs are BC->D and AB->C. It can be shown easily by computing
closure of attribute set AB with respect to remaining FD set that we can derive AB->D, therefore
decomposition is dependency preserving.
Q2.
a)
First, we split the right hand side of the final FD into C->A and C->D
Key?
A A
B B
C A, C, D
D D
AB A, B, C, D yes
AC A, C, D
AD A, D
BC A,B, C, D yes
BD B, D
CD A, C, D
ABC A, B, C, D SK
ABD A, B, C, D SK
ACD A, C, D
BCD A, B, C, D SK
ABCD
BCD ABC
Keys are AB and BC.
b)
R is not in BCNF because at least one of the FDs, eg. violates BCNF.
R is not in 3NF because D is not part of a key.
Decompose R based on :
CD is BCNF, but ABC is not, because the keys are AB and BC, and C does not contain any of the keys.
Hence C->A is a BCNF violation, and we split again around it, obtaining AC and BC.
Final decomposition is CD, AC, BC with remaining FDs C->A and C->D. It can be easily shown that this is
not dependency preserving (e.g., closure of attribute set AB wrt remaining FDs does not contain D,
hence AB->D is not preserved).
Q3.
At the end, both C and E can still exercise p.
After Step 7: After Step 8:
p, no
p, no
p, yes
Sys
A
B
E
C
D
p, no
p, yes
p, yes
p, yes
p, no
p, no
Sys
A
B
E
C
D
p, no
p, yes
p, yes
ABCD
CD ABC
AC BC