{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

If the aggregate function is min or max duplicate a

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

View Full Document Right Arrow Icon
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
Background image of page 6

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

View Full Document Right Arrow Icon
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
Background image of page 7
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}