Write the following queries, based on the database schema

of Exercise 2.4.1, and evaluate your queries using the data of that exercise.

a) Find the average speed of PC’s.

b) Find the average speed of laptops costing over $1000.

c) Find the average price of PC’s made by manufacturer “A.”

d) Find the average price of PC’s and laptops made by manufacturer “D.”

e) Find, for each different speed, the average price of a PC.

f) Find for each manufacturer, the average screen size of its laptops.

g) Find the manufacturers that make at least three different models of PC.

h) Find for each manufacturer who sells PC’s the maximum price of a PC.

i) Find, for each speed of PC above 2.0, the average price.

j) Find the average hard disk size of a PC for all those manufacturers that make printers.

Exercise 2.4.1

This exercise builds upon the products schema of Exercise 2.3.1. Recall that the database schema consists of four relations, whose schemas are:

Some sample data for the relation Product is shown in Fig. 2.20. Sample data for the other three relations is shown in Fig. 2.21. Manufacturers and model numbers have been “sanitized,” but the data is typical of products on sale at the beginning of 2007. Write expressions of relational algebra to answer the following queries. You may use the linear notation of Section 2.4.13 if you wish. For the data of Figs. 2.20 and 2.21, show the result of your query. However, your answer should work for arbitrary data, not just the data of these figures.

a) What PC models have a speed of at least 3.00?

b) Which manufacturers make laptops with a hard disk of at least 100GB?

c) Find the model number and price of all products (of any type) made by manufacturer B.

d) Find the model numbers of all color laser printers.

e) Find those manufacturers that sell Laptops, but not PC’s. !

f) Find those hard-disk sizes that occur in two or more PC’s.

g) 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).

h) Find those manufacturers of at least two different computers (PC’s or laptops) with speeds of at least 2.80.

i) Find the manufacturer(s) of the computer (PC or laptop) with the highest available speed.

j) Find the manufacturers of PC’s with at least three different speeds.

k) Find the manufacturers who sell exactly three different models of PC. Draw expression trees for each of your expressions.

Exercise 2.3.1

In this exercise we introduce one of our running examples of a relational database schema. The database schema consists of four relations, whose schemas are:

The Product relation gives the manufacturer, model number and type (PC, laptop, or printer) of various products. We assume for convenience that model numbers are unique over all manufacturers and product types; that assumption is not realistic, and a real database would include a code for the manufacturer as part of the model number. The PC relation gives for each model number that is a PC the speed (of the processor, in gigahertz), the amount of RAM (in megabytes), the size of the hard disk (in gigabytes), and the price. The Laptop relation is similar, except that the screen size (in inches) is also included. The Printer relation records for each printer model whether the printer produces color output (true, if so), the process type (laser or ink-jet, typically), and the price. Write the following declarations:

a) A suitable schema for relation Product.

b) A suitable schema for relation PC.

c) A suitable schema for relation Laptop.

d) A suitable schema for relation Printer.

e) An alteration to your Printer schema from (d) to delete the attribute color.

f) An alteration to your Laptop schema from (c) to add the attribute od (optical-disk type, e.g., c do r dvd). Let the default value for this attribute be ’none’ if the laptop does not have an optical disk.