数据库复习题

8.11 Consider the following relations:

Emp(eid: integer, ename: varchar, sal: integer, age: integer, did: integer) Dept(did: integer, budget: integer, floor: integer, mgreid: integer)

Salaries range from $10,000 to $100,000, ages vary from 20 to 80, each

department has about five employees on average, there are 10 floors, and budgets vary from $10,000 to $1 million. You can assume uniform distributions of values. For each of the following queries, which of the listed index choices would you choose to speed up the query? If your database system does not consider index-only plans (i.e., data records are always retrieved even if enough

information is available in the index entry), how would your answer change? Explain briefly.

1. Query: Print ename, age, and sal for all employees.

(a) Clustered hash index on ?ename, age, sal? fields of Emp. (b) Unclustered hash index on ?ename, age, sal? fields of Emp. (c) Clustered B+ tree index on ?ename, age, sal? fields of Emp. (d) Unclustered hash index on ?eid, did? fields of Emp. (e) No index

2. Query: Find the dids of departments that are on the 10th floor and have a budgetof less than $15,000.

(a) Clustered hash index on the floor field of Dept. (b) Unclustered hash index on the floor field of Dept.

(c) Clustered B+ tree index on ?floor,budget? fields of Dept. (d) Clustered B+ tree index on the budget field of Dept. (e) No index.

Exercise 9.12 What is sequential flooding of the buffer pool?

Exercise 9.13 Name an important capability of a DBMS buffer manager that is notsupported by a typical operating system’s buffer manager. Exercise 10.3 Answer the following questions:

1. What is the minimum space utilization for a B+ tree index? 2. What is the minimum space utilization for an ISAM index?

3. If your database system supported both a static and a dynamic tree index (say,

ISAM and B+ trees), would you ever consider using the static index in preference to the dynamic index? Exercise 10.1

Consider the B+ tree index of order d = 2 shown in the following figure. 1. Show the tree that would result from inserting a data entry with key 9 into this tree. 2. Show the B+ tree that would result from inserting a data entry with key 3 into

the original tree. How many page reads and page writes does the insertion require? 3. Show the B+ tree that would result from deleting the data entry with key 8 from

the original tree, assuming that the left sibling is checked for possible redistribu- tion. 4. Show the B+ tree that would result from deleting the data entry with key 8 from

the original tree, assuming that the right sibling is checked for possible redistribution. 5. Show the B+ tree that would result from starting with the original tree, inserting

a data entry with key 46 and then deleting the data entry with key 52. 6. Show the B+ tree that would result from deleting the data entry with key 91

from the original tree. Root

2、一个处于初始状态的linear hash文件当前只有一个bucket。当前的hash函数对是(h0, h1)。这里，hi(key) = h(key) mod (2i); h是一个hash函数。hi可看成是先对key应用h，然后再只看最后的i位来确定hash后的地址。假设每个页面可以放2个entry。当前的hash文件含有一个entry：21* (101012)。

0 21* ← next

每当产生一个溢出页（overflow page）时，分割操作（split）就应该被触发。请画出如下每个插入操作之后linear hash文件的状态。 ? 14* (11102), ? 7* (1112),

? 35* (1000112), and ? 28* (111002).

请注意：以上插入操作是累积的，因此最终的hash文件应该包含21*, 14*, 7*, 35*和28*。

本文来源：https://www.bwwdw.com/article/z7w5.html