(a) Here there are 2 candidate keys are AB and BC because {AB}+ = {A,B,C,D,E} using FDs AB -> C, BC -> D, CD -> E and DE -> A and {BC}+ = {A,B,C,D,E} using FDs BC -> D, CD -> E , DE -> A and AB -> C.
There is no other candidate key because every candidate key must contain attribute B since B cannot be obtained by other attributes than B.
(b) Here the FD CD -> E and DE -> A are violating BCNF because neither CD nor DE are candidate key( since every FD of the form X-> A should have X as candidate key in BCNF).
(c) Decomposing R based on FD CD -> E, will given R1(C, D, E) and R2( {A,B, C,D,E} - {C,D,E} + {C,D} ) = R2(A,B,C,D).
Hence the decomposition of R based on FD CD -> E will give R1(C, D, E) and R2(A,B,C,D).
(d) Decomposing R based on FD DE -> A, will given R1(A, D, E) and R2( {A,B, C,D,E} - {A,D,E} + {D,E} ) = R2( B,C,D, E).
Hence the decomposition of R based on FD DE -> A will give R1(A, D, E) and R2( B,C,D, E).
(e) Here in relation R, the FD CD -> E is violating 3NF because neither CD is candidate key nor E is part of any key. Also FD DE -> A is violating 3NF because neither DE is candidate key nor A is part of any key.
Hence in decomposition of R based on FD CD -> E will give R1(C, D, E) , based on FD DE -> A will give R2(A, D, E) and hence R3(B, D, E) will be in 3NF( DE is common attribute between R2 and R3).
Hence decomposition of R into R1(C, D, E) , R2(A, D, E) and hence R3(B, D, E) will be in 3NF.
Please comment for any clarification.
Consider a relation R(A,B,C,D,E) with the following functional dependencies: 8. AB C BCD CDE DEA (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.
Consider a...
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...
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 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.
Question 1: Functional Dependencies [7 marks Consider a relation R on attributes (A, B, C, D, E, F,G, H) and the following functional dependen- cies. B →G C →D DE →GC → EF DEF → H (a) What is the closure of [F, G, Hy? (b) List all of the candidate keys of R under the dependencies above. (c) List all of the FDs above that are 3NF violations (d) List all of the FDs above that are BCNF violations....
Consider the following relation R(A,B,C,D,E,G) and the set of functional dependencies F = { A → BCD BC → DE B → D D → A} Give a 3NF decomposition of the given schema based on a canonical cover
Write the complete proof.
Consider the relational schemas given below and the respective sets of functional dependencies valid in the schemas For each one of the relational schemas, determine the highest normal form, which is valid for a schema. Justify your answer If a schema is not in BCNF, then decompose it into a minimum number of schemas so that each one of them is in BCNF. Justify your answers. Justification must include the derivations of minimal keys from the...
Given the following relation schemas and the sets of FD's: a- R(A,B,C,D) F={ABẠC,C7D, D´A, BC+C} b- R(A,B,C,D) F={BẠC, BD, AD>B} C- R(A,B,C,D) F={AB-C, DC+D, CD+A, AD+B} d- R(A,B,C,D) F={AB=C, C+D, D™B, DE} e- R(A, B, C, D, E) F= {AB+C, DB+E, AE>B, CD+A, ECD} In each case, (i) Give all candidate keys (ii) Indicate the BCNF violation Give the minimal cover and decompose R into a collection of relations that are BCNF. Is it lossless? Does it preserve the dependencies?...
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)
Suppose we have the following relation (R) that keeps track of car sales: R(car date_sold, salesperson#, comission_rate, discount) In addition to the functional dependency implied by the primary key (car#), assume the following functional dependencies: date_sold discount salesperson commission_rate Explain why this relation is not in Boyce-Codd Normal Form (BCNF). a. b. Show how you would normalize this relation to achieve Boyce-Codd Normal Form by drawing a new relational schema. Highlight all primary keys by underlining them and use arrows...