Relational Normal Forms Schema Decompositions - IT/Computer Science Assignment Help

Assignment Task:

Task:

Exercise 1: Schema Decompositions [ up to p. 680 (a) Consider the relation R2 (A, B, C, D, E, F, G, H, I, J) and its functional dependencies F = { C → BH, I → DG, J → C, AJ → E, A → F I }. from the previous exercise sheet. The only candidate key is κ = { A, J }. Document your approach in each step and justify your answers briey.

i. Transform R2 according to the 2NF and determine all the candidate key(s) (for each relation). Do not create unnecessary relations.

ii. Further transform the resulting decomposition according to the 3NF and determine all the candidate key(s) (for each relation). Do not create unnecessary relations.

iii. Is your 3NF decomposition also in BCNF? If it is not, decompose it into a BCNF-conform set of relations. If it is, argue why. (b) Consider the relation Students (ID, N ame, AdvisorId, AdvisorN ame, F avoriteAdvisorId) and its functional dependencies F = {ID → {N ame, F avoriteAdvisorId} , AdvisorId → {AdvisorN ame}} which violates BCNF. The only candidate key is κ = {ID, AdvisorId}. Decompose Students into BCNF. Show whether your decomposition is 1) lossless and 2) dependency preserving. Exercise 2: 3NF Synthesis [ up to p. 680 In order to obtain a 3NF schema which is guaranteed to be dependency preserving, the 3NF synthesis algorithm can be used [p. 646]. Consider the following relation R (A, B, C, D, E, F, G, H) for which the following FDs hold: F = {ABH → C, A → D, C → E, BGH → F, F → AD, E → F, BH → E}

(a) Compute the minimal (i.e., canonical) cover F 0 (the rst step in 3NF Synthesis algorithm). Document how you obtained the result by listing and following the three-step procedure from the lecture [p. 645]. (b) Finish the synthesis by demonstrating the remaining three steps. Make sure your documentation is comprehensible. Page 1 of 2

Exercise 3: MVDs and JDs [ up to p. 680 (a) Suppose that A B is a multi-valued dependency in the relation R (A, B, C). Consider the sample tuples specied below. A B C 1 Java English 1 C++ French 1 Python Russian . . . . . . . . . i. Which other tuples must relation R also contain to be in a consistent state?

ii. Decompose the schema into 4NF. (b) Suppose that A B and B C are MVDs in the relation R (A, B, C, D). Consider the sample tuples specied below. A B C D . . . . . . . . . . . . 1 A B X 1 C D Y . . . . . . . . . . . .

i. Which other tuples must relation R also contain to be in a consistent state?

ii. Decompose the schema into 4NF

. (c) Given the following sample relation of trips: Trips Person Destination Vehicle Alex Uni Bike Zora Uni Bike Alex Dentist Bike Alex Uni Bus

i. Determine all minimal keys for Trips and document your approach.

ii. Show that Trips cannot have non-trivial MVDs.

iii. Does Trips in the shown state have any Join Dependencies (JDs)? Show why this is/is not the case.

The above  IT Assignment has been solved by our  IT Assignment  Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.