In this problem, we consider indexes for the relation from our running battleships exercise. Assume:

Ships(name, class , launched)

1. Name is the key.
2. The relation Ships is stored over 50 pages.
3. The relation is clustered on class so we expect that only one disk access is needed to find the ships of a given class.
4. On average, there are 5 ships of a class, and 25 ships launched in any given year.
5. With probability p1 the operation on this relation is a query of the form SELECT * FROM Ships WHERE name = n.
6. With probability p2 the operation on this relation is a query of the form SELECT * FROM Ships WHERE class = c.
7. With probability p3 the operation on this relation is a query of the form SELECT * FROM Ships WHERE launched = y.
8. With probability 1 - p1 - p2 - p3 the operation on this relation is an insertion of a new tuple into Ships.
9. One disk access is needed to read a page of the index every time we use that index to locate tuples with a given value for the indexed attribute (s). If an index page must be modified (in the case of an insertion), then another disk access is needed to write back the modified page.
10. Likewise, in the case of an insertion, one disk access is needed to read a page on which the new tuple will be placed, and another disk access is needed to write back this page. We assume that, even without an index, we can find some page on which an additional tuple will fit, without scanning the entire relation.

Consider the creation of indexes on name, class, and launched.

1. For each combination of indexes, come up with a formula that calculates the average cost of an operation in terms of p1, p2, and p3. Show the process.
2. For a read intenstive application where p1 = 0.5, p2 = 0.2, p3 = 0.2, what is the best choice of indexes? Show the process.
3. For a read intenstive application where p1 = 0.2, p2 = 0.5, p3 = 0.2, what is the best choice of indexes? Show the process.
4. For a read intenstive application where p1 = 0.2, p2 = 0.2, p3 = 0.5, what is the best choice of indexes? Show the process.
5. For a write intenstive application where p1 = 0.1, p2 = 0.1, p3 = 0.1, what is the best choice of indexes? Show the process.

Answer rating: 100% (QA)

ANSWERS 1 Index on name The average cost of an operation is p1 1 1 p2 1 1 p3 1 1 1 p1 p2 p3 1 1 2 2p... View the full answer