Homework #1 Solution

Questions on Chapter (2):

 

Please answer the following questions:

 

   Q1 [1.5 pts]: How many different ways (considering orders os tuples and attributes) are there to represent a relation instance if that instance has [1.5 pts]:

 

o Three attributes and three tuples?

o Four attributes and five tuples?

o N attribues and m tuples?

 

 

   Q2 [1.5 pts]: Assume a database with the following Schema: Product(maker, model, type)

PC(model, speed, ram, hd, price)         hd: hard disk Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price)

Below are sample data for the above tables:

 

 

model  Speed  ram   hd            price

 

 

 

 

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

 

Sample data for relation PC

 

model  Speed  ram   hd            price

 

2001         2.00 2048 240 20.1     3673

2002         1.73  1024 80     17.o  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  8-0    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

 

Sample data for relation Laptop

 

maAr          modd         type

 

A     1001           pc     

A     1002           pc     

A     1003           pc     

A     2004           laptop       

A     2005           laptop       

A     2006           laptop       

B     1004           pc     

B     1005           pc     

B     1006           pc     

B     2007           laptop       

C     1007           pc     

D     1008           pc     

D     1009           pc     

D     1010           pc     

D     3004           printer       

D     3005           printer       

E      1011           pc     

E      1012           pc     

E      1013           pc     

E      2001           laptop       

E      2002           laptop       

E      2003           laptop       

E      3001           printer        model         color type  price

E      3002           printer        3001 true   ink- j et      99

E      3003           pr int er      3002 false  laeer  239

F      2008           laptop        3003 true   laeer  899

F      2009           laptop        3004 true   ink- jet       120

G     2010           laptop        3005 false  laeer  120

H     3006           printer        3006 true   ink-J et       100

H     3007           pr inter       3007 true   laeer  200

 

 

 

Sa1nplc data for Product               Sample data for relation  Printer

 

Write expressions of relational algebra to answer the following queries [1.5 pts]:

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

 

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

100 GB?

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 sells laptops but not PC’s?

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

   Q3 [2 pts]: For the above schema <product, PC, Laptop, Printer tables, express the following constraints. Also, for the data samples given above, indicate any violations to your constraints [2 pts]:

a) A PC with a processor speed < 2.00 must not sell for more than

$500?

b)A laptop with a screen size < 15.4” must have at least a 100 GB

Hard Disk or sell for less than $1,000?

c) No manufacturer of PC’s may also make laptops?

d) A manufacturer of a PC must also make a laptop with at least as great a processor speed?