Question 3 Given Relational Schema R B C D E Set Functional Dependencies F Ab Bc C Bc D R Q43822827
This is how we have to do it!:
Question 3 Given is the relational schema R= (A, B, C, D, E) and the set of functional dependencies F = {AB → BC, C + A,BC → D}. Is R in 3NF? If not, give a lossless and dependency preserving decom- position which is in 3NF. Explain your answer carefully. summary of normal forms BCNF To check whether the relation is in a given form we need We check for every F la → ß 3NF F and all candidate keys a B 1. Bis trivial, i.e., ß sa 2. a is a superkey 3. for every X EB-a, there exists a candidate key y such that Xey F that one of the following holds 1. a-Bis trivial, i.e., B sa 2. a is a superkey No To decompose, we first compute F+ If the schema is already in the desired form, the algorithm will preserve the schema Yes Decomposition obtained by our algorithm is lossless Dependency preservation is … by our algorithm not guaranteed lossless guaranteed Hence, for every relation schema R 1. if Ris in BCNF then it is also in 3NF: 2. if R is in 3NF then it is not necessarily in BCNF; 3NF Decomposition: An Example Relation schema: cust_banker_branch = (customer_id, employee_id, branch_name, type) The functional dependencies for this relation schema are: • customer_id, employee_id → branch_name, type • employee_id → branch_name • customer_id, branch_name → employee_id We first compute a canonical cover • branch_name is extraneous in the RHS of the 1st dependency • No other attribute is extraneous, so we get For customer_id, employee_id → type employee_id → branch_name customer_id, branch_name → employee_id B1050 19/20 6AN. Yakovets 3NF Decomposition Example (Cont.) The for-loop generates following 3NF schema: • (customer_id, employee_id, type ) • (employee_id, branch_name) • (customer_id, branch_name, employee_id) – Observe that (customer_id, employee_id, type) contains a candidate key of the original schema, so no further relation schema needs be added Minor extension of the 3NF decomposition algorithm: at end of for loop, detect and delete schemas, such as (employee_id, branch_name), which are subsets of other schemas The resultant simplified 3NF schema is: • (customer_id, employee_id, type) • (customer_id, branch_name, employee_id) B1050 19/20 6AN. Yakovets Show transcribed image text Question 3 Given is the relational schema R= (A, B, C, D, E) and the set of functional dependencies F = {AB → BC, C + A,BC → D}. Is R in 3NF? If not, give a lossless and dependency preserving decom- position which is in 3NF. Explain your answer carefully.
summary of normal forms BCNF To check whether the relation is in a given form we need We check for every F la → ß 3NF F and all candidate keys a B 1. Bis trivial, i.e., ß sa 2. a is a superkey 3. for every X EB-a, there exists a candidate key y such that Xey F that one of the following holds 1. a-Bis trivial, i.e., B sa 2. a is a superkey No To decompose, we first compute F+ If the schema is already in the desired form, the algorithm will preserve the schema Yes Decomposition obtained by our algorithm is lossless Dependency preservation is … by our algorithm not guaranteed lossless guaranteed Hence, for every relation schema R 1. if Ris in BCNF then it is also in 3NF: 2. if R is in 3NF then it is not necessarily in BCNF;
3NF Decomposition: An Example Relation schema: cust_banker_branch = (customer_id, employee_id, branch_name, type) The functional dependencies for this relation schema are: • customer_id, employee_id → branch_name, type • employee_id → branch_name • customer_id, branch_name → employee_id We first compute a canonical cover • branch_name is extraneous in the RHS of the 1st dependency • No other attribute is extraneous, so we get For customer_id, employee_id → type employee_id → branch_name customer_id, branch_name → employee_id B1050 19/20 6AN. Yakovets
3NF Decomposition Example (Cont.) The for-loop generates following 3NF schema: • (customer_id, employee_id, type ) • (employee_id, branch_name) • (customer_id, branch_name, employee_id) – Observe that (customer_id, employee_id, type) contains a candidate key of the original schema, so no further relation schema needs be added Minor extension of the 3NF decomposition algorithm: at end of for loop, detect and delete schemas, such as (employee_id, branch_name), which are subsets of other schemas The resultant simplified 3NF schema is: • (customer_id, employee_id, type) • (customer_id, branch_name, employee_id) B1050 19/20 6AN. Yakovets
Expert Answer
Answer to Question 3 Given is the relational schema R= (A, B, C, D, E) and the set of functional dependencies F = {AB → BC, C + …
OR