Homework for CS240A: Databases and Knowledge Bases

Problem A. Consider the the sales relation in slide 6 of the notes

1. Show the result of pivoting the relation on pid and timeid.

2. Write a collection of SQL queries to produce the result requested in point 1.

3. Show the result of pivoting the relation on locid and timeid.

4. Write DB2's queries to produce the results of point 1 and 3 (hint: you should use the supergroup constructs).

Problem B: Consider the bitmap example--slide 13 in the notes

1. For the same data how will the bitmap index change if the the rating values range from 1 to 10.

2. How would you use (or not use) the bitmap index to answer the following queries:
   • How many customers with rating less than 3 are male?
   • What percentage of customers are male?
   • How many customers are named Who?
   • What is the rating value with the largest number of customers?