高级数据库技术问答题(附答案)

Database SystemProblem 1The hash join algorithm described in the textbook computes the natural join of two relations.Write pseudocode for an iterator that implements a hash based algorithm for computing an “anti-join” operation:R anti-join S contains all tuples in R that have no matching tuples in S.Although anti-join is not an operation that can be specified directly in SQL, it is implemented in the query processor to support some types of queries. Your pseudocode must define the standard iterator functions open(),next(),and close(), and must fetch the input tuples using iterator calls (in other words, R and S may not be base relations, but may be outputs of other operators). Show what state information the iterator must maintain between calls. For simplicity, you can assume that S is small and easily fits in memory. Here are more details on anti-join: /wiki/Relational_algebra#AntijoinProblem 2Consider an index organized as a B+ tree. The leaf nodes contain pointers to a total of N records, and each block that makes up the index has m pointers. We wish to choose the value of m that will minimize search times on a particular disk device.We are given the following information:(a) For the disk that will hold the index, the time to read a given block into memory can be approximated by (25 + .02*m) milliseconds. The 25 milliseconds represent the seek and latency components of the read, the .02*m milliseconds is the transfer time. (That is, as m becomes larger, the larger the block will be and the more time it will take to read it into memory.)(b) Once the block is in memory a binary search is used to find the right pointer. So the time to process a block in main memory is (a + b log_2 m) milliseconds, for some constants a , b. ("log_2 m" means log base 2 of m.)(c) The main memory time constant "a" is much smaller than the disk seek and latency time of25 milliseconds.(d) The index is full, so that the number of blocks that must beexamined per search is log_m N.Questions:(i) What value of m minimizes the time to search for a given record? An approximate answer is OK. (HINT: If you come up with an equation which is hard to solve algebraically, try plugging in values to locate the root of the equation.) The value you obtain should be independentof "b"!(ii) What happens as the seek and latency constant (25ms) decreases? For instance, if this constant is cut in half, how does the optimum m value change?Answer:Let T1 be the time taken to read one block into main memory and T2 be the time to process that block in memory. If n blocks are examined to search for a given record, then the time taken for this search is :n * (T1 + T2)Substituting the appropriate expressions for all the variables, we getT(m) = [ (25 + 0.02 m) + (a + b log2m) ] * log m NIgnoring a in comparison with 30, we getT(m) = [25 + 0.02 m + b log2m] * log m NSubstituting log2m by ln(m)/ln(2) and log m N by ln(N)/ln(m), we getT(m) = [ (25 + 0.02m)/ln(m) ] * ln(N) + b * ln(N)/ln(2)Hence, to minimize T(m), we need to minimize f(m) = (25 + 0.02m)/ln(m). A number of techniques can be applied to determine the value of m that minimizes this expression. For example, taking the derivative and setting it to zero (f'(m) = 0), we getm * (ln (m) - 1) = 1250This can be solved to yield an integral value of 272. It is easy to verify that this value does indeed minimize f(m) and hence T(m).Seek+latency constant decreases : Let us split T(m) into three components as follows (here, t s is the seek + latency time constant which was 30 in the above case) :•Total seek and latency time T a = t s * ln(N)/ln(m)•Total transfer time T b = 0.02 * m * ln(N)/ln(m)•Total binary search time T c = b * ln(N)/ln(2)Of these, T c is independent of m, T a is a decreasing function of m whereas T b is an increasing function of m. If t s is very small, then T a is negligible compared to T b. Hence, we must choose a small value of m to minimize the search time. On the other hand, if T a is very large compared to T b, then the former dominates and we choose a large value of m to minimize search times. From these limiting cases, it is easy to see that if t s decreases, the optimum value of m decreases. In particular, if t s = 25/2 = 12.5, we get an optimum value of 155 (by proceeding along the same lines as above).Problem 3To build a hash index for a multi-attribute search key, we can use an approach called partitioned hashing. The partitioned hash function is really a list of hash functions, one for each attribute in the search key. Suppose that we wish to build a partitioned hash index on R(A, B) with 2 n buckets numbered 0 to 2 n 1. In this case, the partitioned hash function consists of two hash functions h A and h B. Hash function h A takes a value of A as input and produces a result with n A bits, and h B takes a value of B as input and produces a result with (n n A ) bits. The two results are concatenated together to produce the result of the partitioned hash function, which is then used to index the buckets. To locate records with A = a and B = b, we simply go directly to the bucket numbered h A (a) h B (b) (in binary).(a) Which buckets do we have to examine in order to locate all records with A = a?(b) Suppose we are given a query mix. Each query in this mix will either ask for records with a given value of A, or it will ask for records with a given value of B (but never both). With probability p, the value of A will be specified. Give a formula in terms of n, n A, and p for the expected number of buckets that must be examined to answer a random query from the mix.(c) Find the value of n A, as a function of n and p, that minimizes the expected number of buckets examined per query?Answer:(a)(b)(c)Firstf(x) =then determine the value of Na that minimizes the f(x) (Tips: use your calculus knowledge)。