Chapter 6 homework:
Exercise 6.2.2:
Write the following queries, based on the database schema Product(maker, model, type)
PC(model, speed, ram, hd, price)
Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price)
of Exercise 2.4.1, and evaluate your queries using the data of that exercise.
e) Find those pairs of PC models that have both the same speed and RAM. A pair should be listed only once; e.g., list(i, j) but not (j, i);
Exercise 6.3.1:
Write the following queries, based on the database schema Product(maker, model, type)
PC(model, speed, ram, hd, price)
Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price)
of Exercise 2.4.1. You should use at least one subquery in each of your answers and write each query in two significantly differently ways (e.g., using different sets of the operators EXISTS, IN, ALL, and ANY).
c) Find the laptops whose speed is slower than that of any PC;
Exercise 6.4.6:
Write the following queries, based on the database schema Product(maker, model, type)
PC(model, speed, ram, hd, price)
Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price)
of Exercise 2.4.1, and evaluate your queries using the data of that exercise.
d) Find, for each speed of PC above 2.0, the average price;
Exercise 6.6.4:
Suppose we have a transaction T that is a function which runs “forever,” and at each hour checks whether there is a PC that has a speed of 3.5 or more and sells for under $1000. If it finds one, it prints the information and terminates. During this time,
other transactions that are executions of one of the four programs described in Exercise 6.6.1 may run. For each of the four isolation levels – serializable, repeatable read, read committed, and read uncommitted – tell what the effect on T of running at this isolation level is.
