(a) Closure of a relation : Closure of a set of functional dependencies. A Closure is a set of FDs is a set of all possible FDs that can be derived from a given set of FDs. It is also referred as a Complete set of FDs. If F is used to donate the set of FDs for relation R, then a closure of a set of FDs implied by F is denoted by F+.
Therefore closure of {F,G,H} = EFGH
(b) Candidate Key: Candidate key is a set of attributes that uniquely determines a relation.
Candidate key of above relation: ACGDEF
(c) A relation is in 3NF under following two conditions:
(i) For FD A->B,
A is a superkey for R.
(ii) B is not a prime attribute, that is B is not a part of candidate key.
Therefore, ACG->B is in 3NF.
C->D is in 3NF.
DE->GC is in 3NF.
3NF violations FD's:
B->G.
G->A.
H->EF.
DEF->H.
(iv) An FD is in BCNF if it satisfies the following condition:
For a relation R: A->B, A is a superkey for R.
BCNF violation:
ACG->B, B->G, C->D, G->A, DE->GC, H->EF, DEF->H.
Question 1: Functional Dependencies [7 marks Consider a relation R on attributes (A, B, C, D,...
Consider a relation R(A,B,C,D,E) with the following functional dependencies: 8. AB C BCD CDE DEA (a) Specify all candidate keys for R. (b) Which of the given functional dependencies are Boyce-Codd Normal Form (BCNF) violations'? (c) Give a decomposition of R into BCNF based on the given functional dependencies. (d) Give a different decomposition of R into BCNF based on the given functional dependencies. (e) Give a decomposition of R into 3NF based on the given functional dependencies.
Consider a...
Consider a relation R(A,B,C,D,E) with the following functional dependencies: 8. AB C BCD CDE DEA (a) Specify all candidate keys for R. (b) Which of the given functional dependencies are Boyce-Codd Normal Form (BCNF) violations'? (c) Give a decomposition of R into BCNF based on the given functional dependencies. (d) Give a different decomposition of R into BCNF based on the given functional dependencies. (e) Give a decomposition of R into 3NF based on the given functional dependencies.
Language: SQL - Normalization and Functional
Dependencies
Part 4 Normalization and Functional Dependencies Consider the following relation R(A, B, C, D)and functional dependencies F that hold over this relation. F=D → C, A B,A-C Question 4.1 (3 Points) Determine all candidate keys of R Question 4.2 (4 Points) Compute the attribute cover of X-(C, B) according to F Question 43 (5 Points) Compute the canonical cover of F.Show each step of the generation according to the algorithm shown in class....
Consider a relation R with ve attributes A, B, C, D, and E. You are given the following functional dependencies: A->B, BC->E, and ED->A. (a) List all keys for R. (10 points) (b) Is R in BCNF? If it is, explain why. If is not, decompose it into a collection of BCNF relations. (20 points) (c) Is R in 3NF? If it is, explain why. If it is not, convert it into a collection of 3NF relations. (20 points)
Here's a relation (R), its attributes and its functional dependencies (F): R(A, B, C, D, E) C D → B A → D D → C E → C What is the closure of AB ({AB}+)? What is the closure of F (F+)? [ set of closures for all LHS][each LHS on one line] What is the minimal set (cover) for F? Provide a key for relation R (a minimal set of attributes that can determine all attr.) Decompose the...
Table T1(A, B, C, D, E) functional dependencies: A -> B AC -> DE D -> C List candidate key(s), and how you found the key(s). Which functional dependency causes BCNF violation? Decompose T1 into BCNF tables. Please show all work here.
Consider a relation schema R with attributes ABCDEFGH with functional dependencies S: S={B→CD, BF→H, C→AG, CEH→F, CH→B} Employ the BCNF decomposition algorithm to obtain a lossless decomposition of R into a collection of relations that are in BCNF. Make sure it is clear which relations are in the final decomposition and project the dependencies onto each relation in that final decomposition.
Assume that R(A, B, C, D, E, F) has been decomposed into S(A, C, E, F) and other relations. If the dependencies for R are: AB rightarrow C, C rightarrow E, E rightarrow D, D rightarrow F, F rightarrow D. (a) Find ALL non-trivial functional dependencies that hold in S (b) Determine the keys and superkeys of S (c) For each one of your functional dependencies from part a) indicate if it is a BCNF violation, a 3NF violation or...
Consider relation R(A,B,C,D) with functional dependencies: B → C D→ A BA → D CD → B Decompose R into Boyce-Codd Normal Form (BCNF). Clearly show all intermediary steps.
Dependency Very Good dependency (key dependency) : XA where Table Very good If all dependencies in a table are '"very good", the table is in BCNF X is a candidate key Good Good dependency: X-> A where If all dependencies in a table are "very good" or "good", the table is in 3NF X is not a candidate key X is a part of a candidate key A is prime attribute Bad Bad dependency (Transitive Dependency): X A where If...