Question 1. Given R= {C, S, J, D, P, Q, V} and set of functional dependencies {FD1: JP->C, FD2:SD->P, FD3: J->S}
a. Find key (s) of R. (number each candidate key for full credit and show your work for credit)
b. Decompose R to get R' in 2NF, 3NF and BCNF. (use the FD labels given above to identify FDs. RX (W, X, Y, Z). each relation should show the FDs.
2NF relations are:
3NF tables are:
c. Normalize the relation to 2NF and then 3NF. (hint recall the generalized definition of 2NF and 3NF), (use the FD labels given above to identify FDs. Each relation should be shown in the form RX (W, X, Y, Z). each relation should show the FDs that hold for that relation describe your reasoning clearly.
2NF relations are:
3NF tables are:
Question 2. given R (A, B, C, D, E), with functional dependencies AB-> D, AC->E, BC-> D, D->A, and E->B. we want to project those FDs on to relation S (A, B, C). give the FDs that hold in S. (label and number each FD that holds in S. each relation should be shown in the form RX (W, X, Y, Z). describe your reasoning clearly.
Question 3. given R (A, B, C) with key AB-> C. (parts A and B are unrelated)
a. show one added additional dependency that causes a 2NF violation.
b. Show two added dependencies that cause a BCNF violation.
Question 4. given R (A, B, C, D, E, F) with functional dependencies:
{FD1: ABD -> C, FD2: CD-> B, FD3: CDF -> BE, FD4: BDF->C}
a. Find and number the candidate keys using the given functional dependencies. Show your work and reasoning for credit.
b. What are the prime attributes of F?
Question 5. given the relation R (J, K, L, M, N) with the functional dependencies.
JK-> L, KL->M, LM -> N, MN-> J and NJ-> k
Is this relation in BCNF?
(you must show all the work and reasoning that led you to a conclusion. This question cannot involve transformation to 2NF, 3NF etc. answer should be based solely on the dependencies of BCNF)
(hint: you can use the fact that AB-> C is equivalent to ABD-> CD)
Question 6. let R (A, B, C, D, E) be decomposed in to relations with the following three sets of attributes R1: {A, B, C}, R2: {B, C, D} and R3: {A, C, E}.
Given the following FDs:
FD1: A-> D, FD2: D->E, FD3: B->D
Using the chase algorithm, determine whether decomposition is lossless join.
(if necessary, apply FDs in order shown, clearly show the result of each application of an FD. And clearly show that final answer each tableau where a change has occurred should be shown.)