2. Suppose you are given a relation R with four attributes ABCD. For each of the

Question

2. Suppose you are given a relation R with four attributes ABCD. For each of the following sets of FDs and super keys, do the following:

a. Identify the best normal form that R satisfies (1NF, 2NF, 3NF, or BCNF).

b. If R is not in BCNF, can you decompose it into a set of BCNF relations that preserve the FDs? Identify any FDs that could not be preserved from the decomposition.

i. Super keys: B

FDs: C -> D, C -> A, B -> C

ii. Super keys: BD

FDs: B -> C, D -> A

iii. Super keys: ABC, BCD

FDs: ABC -> D, D -> A

iv. Super keys: A

FDs: A -> B, BC -> D, A -> C

v. Super keys: AB, BC, CD, AD

FDs: AB -> C, AB -> D, C -> A, D -> B

Explanation / Answer

i. Super keys: B
FDs: C -> D, C -> A, B -> C
a. R is in 2NF but not 3NF
b. C -> D and C -> A both cause violations of BCNF. One way to obtain a (lossless) join preserving decomposition is to decompose R into AC, BC, and CD.

ii. Super keys: BD
FDs: B -> C, D -> A
a. R is in 1NF but not 2NF
b. Both B -> C and D -> A cause BCNF violations. One possible decomposition: AD, BC, BD is BCNF and lossless and join-preserving.

iii. Super keys: ABC, BCD
FDs: ABC -> D, D -> A
a. R is in 3NF but not BCNF
b. No BCNF decomposition

iv. Super keys: A
FDs: A -> B, BC -> D, A -> C
a. R is in 2NF but not 3NF

b. BC -> D violates BCNF, split up R as in: BCD, ABC

v. Super keys: AB, BC, CD, AD
FDs: AB -> C, AB -> D, C -> A, D -> B
a. R is in 3NF but not BCNF
b. No BCNF decomposition

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.