Reference no: EM133422795
Question: 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, unitCost)
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 a 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,
(5) 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'