Consider a distributed database for a bookstore chain called National Books with three sites called EAST, MIDDLE,
Question:
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.Fundamentals of Database Systems
ISBN: 978-0136086208
6th edition
Authors: Ramez Elmasri, Shamkant Navathe