西安交大数据库复习题3

复习题3

1.[Disks and Access Time]Consider a disk with a sector扇区size of 512 bytes, 63

sectors per track磁道, 16,383 tracks per surface盘面, 8 double-sided platters柱面(i.e., 16 surfaces). The disk platters rotate at 7,200 rpm (revolutions per

minute). The average seek time is 9 msec, whereas the track-to-track seek time is

1 msec.Suppose that a page size of 4096 bytes is chosen. Suppose that a file

containing 1,000,000 records of 256 bytes each is to be stored on such a disk. No record is allowed to span two pages (use these numbers in appropriate places in your calculation).

1) What is the capacity of the disk?

2) If the file is arranged sequentially on the disk, how many cylinders are needed?

2.Construct a B+-tree for the following set of key values:

(2, 3, 5, 7, 11, 17, 19, 23, 29, 31)

Assume that the tree is initially empty and values are added in ascending order. Construct B+-trees for the cases where the number of pointers that will fit in one node is as follows:

a. Four

b. Six

c. Eight

3.For each B+-tree of Exercise 2, show the form of the tree after each of the following

series of operations:

a. Insert 9.

b. Insert 10.

c. Insert 8.

d. Delete 23.

e. Delete 19.

4.Suppose that we are using extendable hashing on a file that contains records with the

following search-key values:

2, 3, 5, 7, 11, 17, 19, 23, 29, 31

Show the extendable hash structure for this file if the hash function is h(x) = x mod 8 and buckets can hold three records.

5.Show how the extendable hash structure of Practice Exercise 4 changes as the result of

each of the following steps:

a. Delete 11.

b. Delete 31.

c. Insert 1.

d. Insert 15.

6.Consider the instructor relation shown in Figure 11.1.

Figure 11.1.

Construct a bitmap index on the attribute salary, dividing salary values into 4 ranges: below 50000, 50000 to below 60000, 60000 to below 70000, and 70000 and above.

7.设关系r1(A，B，C)，r2(C，D，E)有如下特性：r1有200,000个元组，r2有45,000

个元组，一块中可容纳25个r1元组或30个r2元组；试估算以下每一种策略计算r1|><|r2所需存取的块数(假设可用于连接操作的内存数量为300个块):

1) 块嵌套循环连接

2) 归并连接(假设r1,r2已按连接属性排序)

3) 混合散列连接(假设分5路进行散列并且没有偏斜)

8.设关系r1(A，B，C(fk))，r2(C，D，E(fk))和r3(E，F)，其主码分别为A，C，E。假设

r1有35000个元组，r2有25000个元组，r3有10000个元组。试估计r1|><|r2|><|r3

的元组个数；