This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: Schema Refinement and Normal Forms 193 (c) C → D and C → A both cause violations of BCNF. One way to obtain a (lossless) join preserving decomposition is to decompose R into AC , BC , and CD . 2. (a) Candidate keys: BD (b) R is in 1NF but not 2NF. (c) Both B → C and D → A cause BCNF violations. The decomposition: AD , BC , BD (obtained by first decomposing to AD , BCD ) is BCNF and lossless and join-preserving. 3. (a) Candidate keys: ABC , BCD (b) R is in 3NF but not BCNF. (c) ABCD is not in BCNF since D → A and D is not a key. However if we split up R as AD , BCD we cannot preserve the dependency ABC → D . So there is no BCNF decomposition. 4. (a) Candidate keys: A (b) R is in 2NF but not 3NF (because of the FD: BC → D ). (c) BC → D violates BCNF since BC does not contain a key. So we split up R as in: BCD , ABC . 5. (a) Candidate keys: AB , BC , CD , AD (b) R is in 3NF but not BCNF (because of the FD: C → A). (c) C → A and D → B both cause violations. So decompose into: AC , BCD but this does not preserve AB → C and AB → D , and BCD is still not BCNF because D → B . So we need to decompose further into: AC , BD , CD . However, when we attempt to revive the lost functioanl dependencies by adding ABC and ABD , we that these relations are not in BCNF form. Therefore, there is no BCNF decomposition....
View Full Document
- Fall '12
- Database normalization, BCNF, CANDIDATE KEYS