Hi,
Iwould like somebody to help me with this exercise :
Consider the following relations:
BOOKS (Book#, Primary_author, Topic, Total_stock, $price)
BOOKSTORE (Store#, City, State, Zip, Inventory_value)
STOCK (Store#, Book#, Qty)
Consider a distributed
database for a bookstore chain called National Books with 3 sites called EAST, MIDDLE, and WEST. The relation schemas are given in question 24.20. Consider that BOOKS are fragmented by $price amounts into:
B1:BOOK1:up to $20.
B2:BOOK2:from 20.01 to $50.
B3:BOOK3:from 50.01 to $100.
B4:BOOK4: 100.01 and above.
Similarly, BOOKSTORES are divided by Zipcodes into:
S1:EAST:Zipcodes up to 35000.
S2:MIDDLE:Zipcodes 35001 to 70000.
S3:WEST:Zipcodes 70001 to 99999.
Assume that STOCK is a derived fragment based on BOOKSTORE only.
a. Consider the query:
SELECT Book#, Total_stock
FROM Books
WHERE $price > 15 and $price < 55;
Assume that fragments of BOOKSTORE are non-replicated and assigned based on region. Assume further that BOOKS are allocated as:
EAST:B1,B4.
MIDDLE:B1,B2.
WEST:B1,B2,B3,B4.
Assuming the query was submitted in EAST, what remote subqueries does it generate? (write in SQL).
b. If the bookprice of Book#= 1234 is updated from $45 to $55 at site MIDDLE, what updates does that generate? Write in English and then in SQL.
c. Given an example query issued at WEST that will generate a subquery for MIDDLE.
d. Write a query involving selection and projection on the above relations and show two possible query trees that denote different ways of execution.
Some help?
Thanks a lot
