# If the aggregate function is min or max duplicate a

This preview shows pages 6–8. Sign up to view the full content.

If the aggregate function is min or max, duplicate A values do not have any effect. However, there should be no duplicates on ( A , B ); the first expression removes such duplicates, while the second does not. 13.9 Consider the issue of interesting orders in optimization. Suppose you are given a query that computes the natural join of a set of relations S . Given a subset S 1 of S , what are the interesting orders of S 1? Answer: The interesting orders are all orders on subsets of attributes that can potentially participate in join conditions in further joins. Thus, let T be the set of all attributes of S 1 that also occur in any relation in S S 1. Then every ordering of every subset of T is an interesting order. 13.10 Show that, with n relations, there are (2( n 1))! / ( n 1)! different join orders. Hint: A complete binary tree is one where every internal node has exactly two children. Use the fact that the number of different complete

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

View Full Document
Exercises 7 binary trees with n leaf nodes is: 1 n parenleftbigg 2( n 1) ( n 1) parenrightbigg If you wish, you can derive the formula for the number of complete binary trees with n nodes from the formula for the number of binary trees with n nodes. The number of binary trees with n nodes is: 1 n + 1 parenleftbigg 2 n n parenrightbigg This number is known as the Catalan number , and its derivation can be found in any standard textbook on data structures or algorithms. Answer: Each join order is a complete binary tree (every non-leaf node has exactly two children) with the relations as the leaves. The number of different complete binary trees with n leaf nodes is 1 n ( 2( n 1) ( n 1) ) . This is because there is a bijection between the number of complete binary trees with n leaves and number of binary trees with n 1 nodes. Any complete binary tree with n leaves has n 1 internal nodes. Removing all the leaf nodes, we get a binary tree with n 1 nodes. Conversely, given any binary tree with n 1 nodes, it can be converted to a complete binary tree by adding n leaves in a unique way. The number of binary trees with n 1 nodes is given by 1 n ( 2( n 1) ( n 1) ) , known as the Catalan number. Multiplying this by n ! for the number of permutations of the n leaves, we get the desired result. 13.11 Show that the lowest-cost join order can be computed in time O (3 n ). Assume that you can store and look up information about a set of relations (such as the optimal join order for the set, and the cost of that join order) in constant time. (If you find this exercise difficult, at least show the looser time bound of O (2 2 n ).) Answer: Consider the dynamic programming algorithm given in Sec- tion 13.4. For each subset having k + 1 relations, the optimal join order can be computed in time 2 k + 1 . That is because for one particular pair of subsets A and B , we need constant time and there are at most 2 k + 1 2 different subsets that A can denote. Thus, over all the ( n k + 1 ) subsets of size k + 1, this cost is ( n k + 1 ) 2 k + 1 . Summing over all k from 1 to n 1 gives the binomial expansion of ((1 + x ) n x ) with x = 2. Thus the total cost is less
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