Consider a distributed database for a bookstore chain called National Books with three sites called EAST, MIDDLE, and WEST. The relation schemas is as below.

BOOKS(Book#, Primary_author, Topic, Total_stock, $price)

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.

Consider that BOOKS are fragmented by $price 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 FROM WHERE

Book#,Total_stock

Books

$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: MIDDLE: WEST:

B1, B4

B1, B2

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.