Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
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.

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 Systems: Design, Implementation, & Management
Database Systems: Design, Implementation, & Management
11th Edition
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Chapter10: Transaction Management And Concurrency Control
Section: Chapter Questions
Problem 1P
See similar textbooks
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

SEE SOLUTIONCheck out a sample Q&A here
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 Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
SEE MORE TEXTBOOKS