ch6 exercise new.ppt

R = (A, B, C, G, H, I)F = ( A ? B A ? C CG ? H CG ? I B ? H} What is the key of R given F? (AG+)1. Result= AG2. Result= ABCG (A ? C; A ? B and A ? AG)3. Result= ABCGH (CG ? H and CG ? AGBC)4. Result=ABCGHI (CG ? I and CG ? AGBCH) Is AG a candidate key?1. AG ? R2. Does A+ ? R?  3. Does G+ ? R? Exercise Let R = (A, B, C, D, E), F = {A?BC, CD?E, B?D, E?A} and the decomposition R1= (A, B, C) R2= (A, D, E) Is the decomposition lossless? Yes because the common attribute A is a key for R1 Is the decomposition dependency preserving? No: we loose CD?E, B?D Exercise Let R(A, B, C, D) and F={AB?CD, B ?C}. Is R 2NF? Key: AB B ?C violates 2NF, since B is subset of key and C is not prime attribute. Let R(A, B, C, D) and F={AB?CD, C ?D}. Is R 2NF? Key: AB C ?D, C is not a subset of key, so R is in 2NF Exercise Let R(A, B, C, D, E) and F={A?B, BC?E, and ED ?A} List the candidate key(s) of R ACD BCD CDE Decompose R in 3NF. R is already in 3NF. All the attributes of R are prime. Exercise Let a relation: Sale (Customer, Store, Product, Price) and the constraints: A Customer buys from 1 store There is a unique price for each product in a store Which are the FD implied by the above descriptions: {Customer} ? {Store} {Store, Product} ? {Price} Candidate key: {Customer, Product} Is Sale in 3NF? No – both FDs violate 3NF. Decompose Sale into 3NF R1(Customer,Store), R2(Store,Product,Price) Is the decomposition dependency preserving? Yes each dependency is preserved in a table BUT (see next page) Exercise Our decomposition (Customer,Store), (Store,Product,Price) is lossy because the common attribute Store is not a key of any table KEY: Customer, Product Exercise Let R(A, B, C, D, E, F, G), F={AB ?CD, C ?EF, G ?A, G?F, CE ?F} Decompose R into 3NF relations Candidate keys GB First, we need the canonical cover FC={AB ?C, AB ? D, C ?E, C ? F, G ?A, G ? F,} 3NF Decomposition R1={ABC}, R2={ABD} R3={CE}, R4={CF} R5={GA}, R6={GF} R4={GB} Exerc