1. A database has the following schema,

Product(maker, model, type)

PC(model, speed, ram, hd, price)

Laptop(model, speed, ram, hd, screen, price)

Printer(model, color, type, price)

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 (note that 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 drive (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 inkjet, typically), and the price.

Some sample data for the relation Product is shown in Figure 1. Sample data for the other three

relations is shown in Figure 2. Manufacturers and model numbers have been "sanitized." but

the data is typical of products on sale at the beginning of 2007.

Following is an example for answering the questions

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

Task 1: we need to find laptops with 100GB HD. To find those laptop we will do select of Laptop relation and save the result in R1 Relation

Task 2: to find the Manufacturer which is available in Product relation so we have to do the join between Product relation and R1(result from task1) and save the result in R2 relation

Task 3: Now run the projection on with attribute maker on R2.

The relation algebra will be as follows

R1 := _{hd 100} (Laptop)

R2 := Product >

R3 := _maker (R2)

The final relation R3 will be as follows:

maker

E

A

B

F

G

Write expressions of relational algebra to answer the following queries. For the data of Figures

1 and 2, show the result of your query. However your answer should work for arbitrary data,

not just the data of these figures.

.

a) Find all the laptop model whose speed is 2.0 or higher and price is at least 2000.

b) Find PC manufacturer with PC price more than 900.

c) Find those manufacturers that sell Laptops, but not PC's.

.

maker A A A A A B B B pc B D D D pc D D E E E E E E E E E F F G H H model type 1001 pc 1002 pc 1003 pc 2004 laptop 2005 laptop 2006 laptop 1004 pc 1005 1006 pc 2007 laptop 1007 pc 1008 pc 1009 pc 1010 3004 printer 3005 printer 1011 pc 1012 pc 1013 pc 2001 laptop 2002 laptop 2003 laptop 3001 printer 3002 printer 3003 printer 2008 laptop 2009 laptop 2010 laptop 3006 printer 3007 printer Figure 1. Sample data for Product model speed ram hd price 1001 2.66 1024 250 2114 1002 2.10 512 250 995 1003 1.42 512 80 478 1004 2.80 1024 250 649 1005 3.20 512 250 630 1006 3.20 1024 320 1049 1007 2.20 1024 200 510 1008 2.20 2048 250 770 1009 2.00 1024 250 650 1010 2.80 2048 300 770 1011 1.86 2048 160 959 1012 2.80 1024 160 649 1013 3.06 512 80 529 (a) Sample data for relation PC model speed ram hd screen price 2001 2.00 2048 240 20.1 3673 2002 1.73 1024 80 17.0 949 2003 1.80 512 60 15.4 549 2004 2.00 512 60 13.3 1150 2005 2.16 1024 120 17.0 2500 2006 2.00 2048 80 15.4 1700 2007 1.83 1024 120 13.3 1429 2008 1.60 1024 100 15.4 900 2009 1.60 512 80 14.1 680 2010 2.00 2048 160 15.4 2300 (6) Sample data for relation Laptop model color type price 3001 true ink-jet 99 3002 false laser 239 3003 true laser 899 3004 true ink-jet 120 3005 false laser 120 3006 true ink-jet 100 3007 true laser 200 (c) Sample data for relation Printer Figure 2 Sample data for PC, Laptop, and Printer