Consider a distributed database for a bookstore chain called National Books with three sites called EAST, MIDDLE, and WEST. The relation schemas are given in Exercise. Consider that BOOKS are fragmented by Splice amounts into:

B1: BOOK1: $price up to $20

B2: BOOK2: $price from $20.01 to $50

B3: BOOK3: $price from $50.01 to $100

B4: BOOK4: $price $100.01 and above.

Similarly, BOOK_STORES are divided by zip codes into:

S1: EAST: Zip up to 35000.

S2: MIDDLE: Zip 35001 to 70000.

S3: WEST: Zip 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 nonreplicated 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 price 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. Give a sample 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.

Exercise

Consider the following relations:

BOOKS(Book#, Primary_author, Topic, Total_stock, splice)

BOOKSTORE(Store#, City, State, Zip, Inventory_value)

STOCK(Store#, Book#, Qty)

Total stock is the total number of books in stock, and Inventory_value is the total inventory value for the store in dollars.