Question 2 Given Relational Schema R B C D E Set Functional Dependencies F Ab Bc C Abc D Q43822819
This is how we have to do it!:
Question 2 Given is the relational schema R= (A, B, C, D, E) and the set of functional dependencies F = {AB → BC,C –→ A, ABC → 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 3NF To check whether the relation is in a given form we need F and all candidate keys We check for every a -B 1. a Bis trivial, i.e., Bsa 2. a is a superkey 3. for every X EB-a, there exists a candidate key y such that X Ey a -B 1. a- Bis trivial, i.e., Bsa 2. a is a superkey that one of the following holds To decompose, we first compute If the schema is already in the desired form, the algorithm will preserve the schema Yes F+ Fc No llossless Decomposition obtained by our algorithm is Dependency preservation is… by our algorithm lossless not guaranteed guaranteed Hence, for every relation schema R 1. if R is 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 Fc = customer_id, employee_id – type employee_id → branch_name customer_id, branch_name → employee_id BI050 19/20 6A N. 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) BI050 19/20 6AN. Yakovets Show transcribed image text Question 2 Given is the relational schema R= (A, B, C, D, E) and the set of functional dependencies F = {AB → BC,C –→ A, ABC → 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 3NF To check whether the relation is in a given form we need F and all candidate keys We check for every a -B 1. a Bis trivial, i.e., Bsa 2. a is a superkey 3. for every X EB-a, there exists a candidate key y such that X Ey a -B 1. a- Bis trivial, i.e., Bsa 2. a is a superkey that one of the following holds To decompose, we first compute If the schema is already in the desired form, the algorithm will preserve the schema Yes F+ Fc No llossless Decomposition obtained by our algorithm is Dependency preservation is… by our algorithm lossless not guaranteed guaranteed Hence, for every relation schema R 1. if R is 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 Fc = customer_id, employee_id – type employee_id → branch_name customer_id, branch_name → employee_id BI050 19/20 6A N. 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) BI050 19/20 6AN. Yakovets
Expert Answer
Answer to Question 2 Given is the relational schema R= (A, B, C, D, E) and the set of functional dependencies F = {AB → BC,C –…
OR