The table Faculty has 60,000 rows, each row occupies 100 bytes, and the database page size is 4 k bytes. Assuming pages in the index and data ?les are 100% occupied, estimate the number of page transfers required for the following SELECT statement in each of the cases listed below.

Question

```
SELECT F.DeptId
FROM Facu lty F
WHERE F.Id = ’111111111’
```

a. The table has no index.
b. The table has a clustered B+ tree index on Id. Assume a (nonleaf) index entry has 20 characters.
c. The table has an unclustered B+ tree index on Id. Assume a (nonleaf) index entry has 20 characters.
d. The table has an unclustered B+ tree index on (Id, DeptId). Assume that an index entry now has 25 characters.
e. The table has an unclustered B+ tree index on (DeptId, Id). Assume that an index entry now has 25 characters.

Answer 1

a. Since each page of the table accommodates about 40 rows, the table occupies about 1500 pages and on average half of these will have to be transferred.
b. A non-leaf page in the index can accommodate 200 index entries, so a three-level index is required. Hence, 3 page transfers will be required.
c. A three-level index is still required. Hence, 4 page transfers are required (one additional transfer to get the row).
d. 160 index entries ?t in a page and a three level index is required. An index-only strategy is now possible involving 3 page transfers
e. 160 index entries ?t in a page and a three level index is required. An index-only strategy is now possible although a scan of the leaf level is now required. The leaf level contains about 60000/160 = 375 pages, and about half of these will have to be transferred.