13-31. Consider the SUPPLIER, PART, and SHIPMENT relations and distributed database mentioned in the section on query optimization in this chapter.

a. Write a global SQL query (submitted in Columbus) to display the PartNumber and Color for every part that is not supplied by a supplier in Chicago.

b. Design three alternative query-processing strategies for your answer to part a. c. Develop a table similar to Table 13-2 to compare the processing times for these three strategies.

d. Which of your three strategies was best and why?

e. Would data replication or horizontal or vertical partitioning of the database allow you to create an even more efficient query-processing strategy? Why or why not?

Advanced Database Topics TABLE 13-2 Query-Processing Strategies in a Distributed Database Environment Method Time Move PART relation to Detroit and process whole query at Detroit computer. 18.7 minutes Move SUPPLIER and SHIPMENT relations to Chicago and process whole 28 hours query at Chicago computer. Join SUPPLIER and SHIPMENT at the Detroit computer, PROJECT these 2.3 days down to only tuples for Cleveland suppliers, and then for each of these check at the Chicago computer to determine if the associated PART is red. PROJECT PART at the Chicago computer down to just the red items, and 20 seconds for each check at the Detroit computer to see if there is some SHIPMENT involving that PART and a Cleveland SUPPLIER. JOIN SUPPLIER and SHIPMENT at the Detroit computer, PROJECT just 16.7 minutes SupplierNumber and PartNumber for only Cleveland SUPPLIERS, and move this qualified projection to Chicago for matching with red PARTS. Select just red PARTs at the Chicago computer and move the result 1 second to Detroit for matching with Cleveland SUPPLIERS. Source: Based on Date (2003) Advanced Database Topics TABLE 13-2 Query-Processing Strategies in a Distributed Database Environment Method Time Move PART relation to Detroit and process whole query at Detroit computer. 18.7 minutes Move SUPPLIER and SHIPMENT relations to Chicago and process whole 28 hours query at Chicago computer. Join SUPPLIER and SHIPMENT at the Detroit computer, PROJECT these 2.3 days down to only tuples for Cleveland suppliers, and then for each of these check at the Chicago computer to determine if the associated PART is red. PROJECT PART at the Chicago computer down to just the red items, and 20 seconds for each check at the Detroit computer to see if there is some SHIPMENT involving that PART and a Cleveland SUPPLIER. JOIN SUPPLIER and SHIPMENT at the Detroit computer, PROJECT just 16.7 minutes SupplierNumber and PartNumber for only Cleveland SUPPLIERS, and move this qualified projection to Chicago for matching with red PARTS. Select just red PARTs at the Chicago computer and move the result 1 second to Detroit for matching with Cleveland SUPPLIERS. Source: Based on Date (2003)