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 joinpreserving. 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
 Dr.Vishak
 Database normalization, BCNF, CANDIDATE KEYS

Click to edit the document details