14.13

Consider the instructor relation shown in Figure 14.1.

  1. Construct a bitmap index on the attribute salary, dividing salary values into four ranges; below \(50,000\), \(50,000\) to below \(60,000\), \(60,000\) to below \(70,000\), and \(70,000\) and above.

  2. Consider a query that requests all instructors in the Finance department with a salary of \(80,000\) or more. Outline the steps in answering the query, and show the final and intermediate bitmaps constructed to answer the query.

We reproduce the instructor relation below:

  1. Construct a bitmap index on the attribute salary, dividing salary values into four ranges; below \(50,000\), \(50,000\) to below \(60,000\), \(60,000\) to below \(70,000\), and \(70,000\) and above.

Bitmaps for salary, with \(S_1\), \(S_2\), \(S_3\) and \(S_4\) representing the given intervals in the same order.

  1. Consider a query that requests all instructors in the Finance department with a salary of \(80,000\) or more. Outline the steps in answering the query, and show the final and intermediate bitmaps constructed to answer the query.

The question is a bit trivial if there is no bitmap on the dept_name attribute. The bitmap for the dept_name attribute is:

To find all instructors in the Finance department with salary of \(80,000\) or more, we first find the intersection of the Finance department bitmap and \(S_4\) bitmap of salary and then scan on these records for salary of \(80,000\) or more.

Intersection of Finance department bitmap and \(S_4\) bitmap of salary:

Scan on these records with salary \(80,000\) or more gives Wu and Singh as the instructors who satisfy the given query.