{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

# exercises-chapter-11-with-answers - 1 CHAPTER 11 RELATIONAL...

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

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

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

View Full Document
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.
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern