数据库代写 | Assignment 2 ITEC 3220 Winter 2021

Assignment 2
ITEC 3220 Winter 2021

Question 1 [4 marks]
Consider a relation R = (A, B, C, D, E, F) that satisfies the following four FDs: {ABàC, BCàAD,
DàE, CFàB} Does AB à F hold? If so, show a formal proof; otherwise, give a counterexample.
Question 2 [7 marks]
Consider relation schema R(A, B, C, D ) and the two sets of functional dependencies FD1 = {A à B,
A àBC, AB à C, AC à D, Bà C}, and FD2 = {CA à B , BA à D , B à D , DB à C. Determine
Question 3 [14 marks]
Consider a relation R(A, B, C, D, E, F) and the set of functional dependencies FD = { C à AD, AB à
C}.
• Produce a lossless BCNF decomposition for this schema (list both the relations and the
corresponding set of functional dependencies for each of the relations in the decomposition).
Show the full details of your work. Is it dependency-preserving? Explain why.
• If your BCNF decomposition is not dependency preserving, provide a dependency-preserving
3NF decomposition (list both the relations and the corresponding set of functional dependencies).
Show the full details of your work.
Question 4 [14 marks]
Consider the following database schema: R(A, B, C, D, E, F, G, H) with the set of functional
dependencies { F àA, AC àE, E àB, BG àF, BE àD, BDH àE, D àH, CD àA, A àE, AD àBE}
• Produce a BCNF decomposition of this schema (list both the relations and the corresponding set
of functional dependencies for each of the relations in the decomposition). Show the full details
• Produce a 3NF decomposition of this schema (list both the relations and the corresponding set of
functional dependencies). Show the full details of your work.
Question 5 [4 marks]
Assume that the schema R(M, N, O, P, Q) was decomposed into R1(M, N, O) and R2(M, P, Q). Also
assume that the following set F of functional dependencies holds: {N à P, Q à M, OP à Q, M à
NO}. Show that the decomposition or R into R1 and R2 is a lossless.
Question 6 [7 marks]
Give a relation R with 4 or 5 attributes and set FD of functional dependencies that hold such that there
are at least three distinct lossless decompositions of R that verify the BCNF condition.

E-mail: itcsdx@outlook.com  微信:itcsdx