Problem Let RABCDEG Let FCD EC EGA GB
Problem • Let R=ABCDEG • Let F={C->D, E->C, EG->A, G->B} • What are all the keys of R? EG • What is the normal form of R? not in 3 NF, not BCNF • Is the following decomposition lossless? Yes – R 1=AEG, R 2=CE, R 3=BG, R 4=DEG • Is the decomposition dependency preserving? No. Does not preserve C->D 1
Problem • Given R = (A, B, C, D, E, G) and F = {A E, B G, CD B, EB CD, G ACE} 1. Find all keys of R. B, CD, GD 2. What is the normal form of R? not 3 NF, not BCNF 3. Find a minimal cover for R A->E, B->G, CD->B, B->D, G ->A, G->C 4. Find a 3 NF decomposition of R that preserves all functional dependencies and is lossless AE, BG, CDB, GA, GC 5. What is the normal form of each relation in the result? All in BCNF. For CDB, we have the minimal cover: CD->B, B->CD 2
Problem • Given R = (A, B, C, D, E) and F = {A B, AC D, CE D, ED B, AE C, B A} • We decompose R to R 1=(A, B, D, E) and R 2=(C, D, E) • Find a minimal cover of FR 1. What is the normal form of R 1? A->B, B->A, AE->D, ED->A, 3 NF • Find a minimal cover of FR 2. What is the normal form of R 2? CE->D, ED->C, BCNF • Is the decomposition lossless? Dependency preserving? Lossless, not dependency preserving 3
Problem • Prove or give a counter example – (X+)+ = X+, True. Easy to see by considering the algorithm for computing the closure. – (X Y)+ = X + Y+ Wrong. F = {A->C, B->C}, X = A, Y = B – (X Y)+ = X + Y+ Wrong. F = {AB->C}, X = A, Y = B 4
Problem • R=ABCDEGHI • In each of the following we are given F and a subset of R. For each sub-relation, (1) state the normal form (2) if it is not in BCNF, decompose to a collection of BCNF relations – R 1=ABCD, F= {A E, E B, C D} Not in 3 NF. Decomposition to AB, BC, CD – R 2=ABG, F={AC E, B G} Not in 3 NF: Decomposition to BG, AB – R 3=AD, F={D G, G H} In BCNF – R 4=DCHG, F={D I, I C} Not in 3 NF: Decomposition to DC, DHG 5
Problem • Show that the algorithm for computing a BCNF decomposition may return different results depending on the order in which it runs, or prove that this is incorrect. • Counter example, R= ABC, F ={A->B, C->B} 6
Problem • Consider a relation R=ABCDE with F = {AB C, BC D, E AD, D B, CD E} – Find all the keys for R. E, CD, BC, AB, AD 7
- Slides: 7