2023年11月25日

Database代写 | ITEC 3220 Assignment 2

Assignment 2
ITEC 3220 Winter 2021 Due Date: March 10 at 11:00pm

The cover page of each assignment should include the last name, first name, student number of all the team members. Only one member of each team should submit the assignment.

Submission: upload your submission file on e-Class. The assignment must be typed. Your submission should consist of only one file in PDF format.

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 if FD1 and FD2 are equivalent. Explain your answer (show your work).

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

    of your work.

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