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 different ways (e.g., using different sets of the operators EXISTS, IN, ALL, and ANY). a) Find the makers of PC's with a speed of at least 3.0. b) Find the printers with the highest price. ! c) Find the laptops whose speed is slower than that of any PC.

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
Question

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 different ways (e.g., using different
sets of the operators EXISTS, IN, ALL, and ANY).


a) Find the makers of PC's with a speed of at least 3.0.
b) Find the printers with the highest price.
! c) Find the laptops whose speed is slower than that of any PC.
! d) Find the model number of the item (PC, laptop, or printer) with the
highest price.
! e) Find the maker of the color printer with the lowest price.
!! f) Find the maker(s) of the PC(s) with the fastest processor among all those
PC's that have the smallest amount of RAM. 

2.4.14 Exercises for Section 2.4
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:
Product (maker, model, type)
PC (model, speed, ram, hd, price)
Laptop (model, speed, ram, hd, screen, price)
Printer (model, color, type, price)
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.
2.4. AN ALGEBRAIC QUERY LANGUAGE
maker model type
1001 рс
1002 ре
1003 pc
A
A
A
A
A
A
B
B
B
B
с
D
D
D
D
D
E
E
E
E
E
E
E
E
E
F
F
G
H
H
2004 laptop
2005 laptop
laptop
2006
1004
рс
рс
pc
laptop
1005
1006
2007
1007 рс
1008
рс
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 2.20: Sample data for Product
53
Transcribed Image Text:2.4.14 Exercises for Section 2.4 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: Product (maker, model, type) PC (model, speed, ram, hd, price) Laptop (model, speed, ram, hd, screen, price) Printer (model, color, type, price) 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. 2.4. AN ALGEBRAIC QUERY LANGUAGE maker model type 1001 рс 1002 ре 1003 pc A A A A A A B B B B с D D D D D E E E E E E E E E F F G H H 2004 laptop 2005 laptop laptop 2006 1004 рс рс pc laptop 1005 1006 2007 1007 рс 1008 рс 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 2.20: Sample data for Product 53
54
CHAPTER 2. THE RELATIONAL MODEL OF DATA
model speed ram | hd
hd
price
1001 2.66 1024 250 2114
1002 2.10
512 250
995
512 80
478
1003 1.42
1004 2.80 1024 250
649
1005 3.20
512 250
630
1006 3.20 1024 320 1049
1007 2.20 1024 200
510
770
1008 2.20
2048 250
1009 2.00 1024 250
650
1010 2.80 2048 300
770
959
1011 1.86 2048 160
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
60 15.4
549
2003 1.80 512
2004 2.00 512 60
13.3
1150
2500
2005 2.16
1024 120 17.0
2006 2.00 2048 80 15.4
1700
120 13.3
1429
2007
2008
2009 1.60 512 80 14.1
1.83
1024
1.60 1024 100 15.4
900
680
2010 2.00 2048 160 15.4 2300
(b) Sample data for relation Laptop
model color type
price
3001 true ink-jet 99
3002 false
laser
239
laser
899
120
120
100
200
3003 true
3004 true
3005 false laser
3006 true
ink-jet
3007 true laser
(c) Sample data for relation Printer
Figure 2.21: Sample data for relations of Exercise 2.4.1
ink-jet
2.4. AN ALGEBRAIC QUERY LANGUAGE
! g) Find those pairs of PC modeis that have both the same speed and RAM.
A pair should be listed only once; e.g., list (i, j) but not (j, i).
55
!! 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.
Transcribed Image Text:54 CHAPTER 2. THE RELATIONAL MODEL OF DATA model speed ram | hd hd price 1001 2.66 1024 250 2114 1002 2.10 512 250 995 512 80 478 1003 1.42 1004 2.80 1024 250 649 1005 3.20 512 250 630 1006 3.20 1024 320 1049 1007 2.20 1024 200 510 770 1008 2.20 2048 250 1009 2.00 1024 250 650 1010 2.80 2048 300 770 959 1011 1.86 2048 160 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 60 15.4 549 2003 1.80 512 2004 2.00 512 60 13.3 1150 2500 2005 2.16 1024 120 17.0 2006 2.00 2048 80 15.4 1700 120 13.3 1429 2007 2008 2009 1.60 512 80 14.1 1.83 1024 1.60 1024 100 15.4 900 680 2010 2.00 2048 160 15.4 2300 (b) Sample data for relation Laptop model color type price 3001 true ink-jet 99 3002 false laser 239 laser 899 120 120 100 200 3003 true 3004 true 3005 false laser 3006 true ink-jet 3007 true laser (c) Sample data for relation Printer Figure 2.21: Sample data for relations of Exercise 2.4.1 ink-jet 2.4. AN ALGEBRAIC QUERY LANGUAGE ! g) Find those pairs of PC modeis that have both the same speed and RAM. A pair should be listed only once; e.g., list (i, j) but not (j, i). 55 !! 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.
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps

Blurred answer
Knowledge Booster
Intermediate SQL concepts
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education