Exercises-chapter-11 - 1 CHAPTER 11 RELATIONAL DATABASE DESIGN ALGORITHMS AND FURTHER DEPENDENCIES Answers to Selected Exercises 11.15 Show that

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
CHAPTER 11: RELATIONAL DATABASE DESIGN ALGORITHMS AND FURTHER DEPENDENCIES Answers to Selected Exercises 11.15 Show that the relation schemas produced by Algorithm 11.2 are in 3NF. For completeness we repeat here Algorithm 11.2: Relational Synthesis into 3NF with Dependency Preservation. Input: A universal relation R and a set of functional dependencies F on the attributes of R 1. Find the minimal cover G of F 2. For each left-hand side X take all the A 1 ,..., A n such that X A 1 , . .., X A n . Take the attributes of X and A 1 ,..., A n together into one relation X A 1 ... A n , with X as key. 3. Place all remaining attributes (that do not occur in any FD) together in a single relation. Answer: By construction for each R i that is constructed in step 2, there is an FD X { A 1 ,..., A n } (actually, a set of FD’s X A 1 ,..., X A n ), there are no other FD’s for this R i. Hence, the FD X { A 1 ,..., A n } is certainly not a partial FD, because X is the key by construction. Furthermore, it is not transitive either, which would require that X has no attributes in common with the key. Hence, the only one FD of each R i is not partial and not transitive, and thus R i is in 3NF, and thus the complete schema is in 3NF. This material has been taken from draft Pre-Publication Material, Copyright AWL2004 1
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
11.16 Show that if the matrix S resulting from Algorithm 11.1 does not have a row that is all "a" symbols, then projecting S on the decomposition and joining it back will always produce at least one spurious tuple. Answer: The matrix S initially has one row for each relation R i in the decomposition, with "a" symbols under the columns for the attributes in R i . Since we never change an "a" symbol into a "b" symbol during the application of the algorithm, projecting S on each R i at the end of applying the algorithm will produce one row consisting of all "a" symbols in each S(R i ). Joining these back together again will produce at least one row of all "a" symbols (resulting from joining the all "a" rows in each projection S(R i )). Hence, if after applying the algorithm, S does not have a row that is all "a", projecting S over the R i 's and joining will result in at least one all "a" row, which will be a spurious tuple (since it did not exist in S but will exist after projecting and joining over the R i 's). If this line of reasoning is not immediate clear, take R(A,B,C) without any FD’s and its decomposition R 1 (A,B) and R 2 (B,C). Then the initial matrix S will be A B C R 1 a 1 a 2 b 12 R 2 b 21 a 2 a 3 The algorithm will stop immediate, as there are no FD’s at all. Let us take an extension of R (a concrete set of tuples), that corresponds with the initial
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 09/07/2011 for the course MIS unknown taught by Professor Unknown during the Fall '09 term at New York Institute of Technology-Westbury.

Page1 / 7

Exercises-chapter-11 - 1 CHAPTER 11 RELATIONAL DATABASE DESIGN ALGORITHMS AND FURTHER DEPENDENCIES Answers to Selected Exercises 11.15 Show that

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online