Assume that a chain of computer stores that sells computers, components, and parts uses the following...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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, unit Cost) Store (storeName, state, manager, telephone) Stock (itemNo, storeName, qtyOnHand, reorderPoint) 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. a) Assume there are 6 sites for the distributed database such that there is one site in each state and one central site. Choose a data distribution schema on the 6 sites and justify your choice (you may consider replication). Note that your schema should support efficient operation of the daily store transactions (e.g. Check the available quantity on hand for item named 'Hammer' in a store names 'XYZ'). b) Write your fragmentation schema using relational algebra. For each fragment, identify the location where it will be stored. 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. c. Check the available quantity on hand for item named 'Hammer' in a store names 'XYZ'. 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, unit Cost) Store (storeName, state, manager, telephone) Stock (itemNo, storeName, qtyOnHand, reorderPoint) 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. a) Assume there are 6 sites for the distributed database such that there is one site in each state and one central site. Choose a data distribution schema on the 6 sites and justify your choice (you may consider replication). Note that your schema should support efficient operation of the daily store transactions (e.g. Check the available quantity on hand for item named 'Hammer' in a store names 'XYZ'). b) Write your fragmentation schema using relational algebra. For each fragment, identify the location where it will be stored. 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. c. Check the available quantity on hand for item named 'Hammer' in a store names 'XYZ'.
Expert Answer:
Related Book For
Accounting Information Systems
ISBN: 978-0133428537
13th edition
Authors: Marshall B. Romney, Paul J. Steinbart
Posted Date:
Students also viewed these finance questions
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Read the case study and answer the question below with a one page response. What does a SWOT analysis reveal about the overall attractiveness of Under Armours situation? Founded in 1996 by former...
-
At its headquarters in Ventura, California, Patagonia's office space feels more like a national park lodge than the main office of a $400 million retailer. It has a Douglas fir staircase and a...
-
Find the quadratic polynomial that best fits the four points (2, 0), (3, -10), (5, -48), and (6, -76).
-
Solve the right triangles with the given parts or state that there is not enough information to solve. Round off results according to Table 4.1. Refer to Fig. 4.37. b = 82.0, c = 881 Data from Table...
-
In general how have interest rates changed since the late 1980s according to Figure 11.1? FIGURE 11.1 Interest and Inflation Rates, 1988-2010 10p Rate (%) 8 6 10-Year Treasury Bonds 4 2 AAA Corporate...
-
Wooster Corporation can either lease or buy a small garage next to its business that will provide parking for its customers. The company can lease the building for a period of 12 years, which...
-
A monopoly water slide has demand of p(Q) = a - bQ, where p is the price per slide ("ride"), Q is the quantity of slides demanded, and a, b > 0. Fixed costs are F = 0 and constant marginal costs are...
-
Jack and Kim were coworkers who fell in love and got married. They sold their principal residence in MD for the net amount of $1,000,000 after all selling expenses. Jack and L bought the house 9...
-
Identify the three most common forms of equity funding.
-
What is a niche market? Provide examples of niche markets in the womens clothing industry.
-
Ann Perkins is a sales representative for The Pampered Chef, a direct sales organization that sells kitchen utensils and related products. Ann, who lives near Houston, is thinking about leaving The...
-
Nancy Wills is purchasing a business named Niagara Laser Optics near Buffalo, New York. The business has had several brushes with the law during the past several years, dealing with claims of false...
-
Describe the impact of a firms pricing structure on its business model.
-
The partners of Apple, Bere, and Carroll LLP share net income and losses in a 5:3:2 ratio, respectively. The capital account balances on January 1, 2013, were as follows: The carrying amounts of the...
-
When a company has a contract involving multiple performance obligations, how must the company recognize revenue?
-
Obtain a copy of the ACAS Code of Practice 1: Disciplinary and Grievance Procedures (2009). Use this, and our suggestions in this chapter, to rewrite or modify either of the procedures you used for...
-
One of the disadvantages of the IEEET1 exciter is following a fault the terminal voltage does not necessarily return to its prefault value. Using PowerWorld Simulator case Problem 12_3 determine the...
-
Rework Example 13.6 if the source impedance at the sending end of line \(\mathrm{A}\) is \(\mathrm{Z}_{\mathrm{G}}=\mathrm{Z}_{\mathrm{A}} / 4=100 \Omega\), and the receiving end of line...
Study smarter with the SolutionInn App