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