Q1: (20 Points) Assume that a chain of computer stores that sells computers, components, and parts uses the following global schema for a relational database that keeps information about items at each store. Item (itemNo, itemName, supplier, unitfest) Store (stereName, state, manager, telephone) Stock (itemder storeNamen atxanland recrderleint) The company has stores in five different states (namely, Minnesota, Illinois, California, Indiana, and Maryland). Assume that the company stock about 15000 different items. Each item comes from only one supplier but the same supplier can supply several items. The database keeps track of what items are in which store and the reorder point which is the number of items that is the minimum each store wishes to keep in stock. When the quantity on hand falls to the reorder point, state's manager contacts supplier to place a new order. Note that, each store serves customers in its own geographical area. Assume all this information is currently stored in a central database. The company hired you to design a distributed database for their system hoping to make operations more efficient. c) Give an instance of the data using data of your choice and show the fragmented and/or replicated database. Make sure that there are no empty fragments. d) Do the following for each of the following queries: (1) write the query in SQL, (2) draw the query operator plan, (3) perform data localization, (4) show the query plan over fragments, and (6) find how many sites are involved to answer this query. a. Find the total quantity of item number 1001 on hand in all stores. b. List all suppliers who supply items that are stored in a Minnesota's store. Check the available quantity on hand for item named 'Hammer' in a store names 'XYZ'. C. Q1: (20 Points) Assume that a chain of computer stores that sells computers, components, and parts uses the following global schema for a relational database that keeps information about items at each store. Item (itemNo, itemName, supplier, unitfest) Store (stereName, state, manager, telephone) Stock (itemder storeNamen atxanland recrderleint) The company has stores in five different states (namely, Minnesota, Illinois, California, Indiana, and Maryland). Assume that the company stock about 15000 different items. Each item comes from only one supplier but the same supplier can supply several items. The database keeps track of what items are in which store and the reorder point which is the number of items that is the minimum each store wishes to keep in stock. When the quantity on hand falls to the reorder point, state's manager contacts supplier to place a new order. Note that, each store serves customers in its own geographical area. Assume all this information is currently stored in a central database. The company hired you to design a distributed database for their system hoping to make operations more efficient. c) Give an instance of the data using data of your choice and show the fragmented and/or replicated database. Make sure that there are no empty fragments. d) Do the following for each of the following queries: (1) write the query in SQL, (2) draw the query operator plan, (3) perform data localization, (4) show the query plan over fragments, and (6) find how many sites are involved to answer this query. a. Find the total quantity of item number 1001 on hand in all stores. b. List all suppliers who supply items that are stored in a Minnesota's store. Check the available quantity on hand for item named 'Hammer' in a store names 'XYZ'. C
