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