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 $price amounts into:
B1: BOOK1: $price up to $20
BOOK2: $price from $20.01 to $50
BOOK3: $price from $50.01 to $100
B4: BOOK4: $price $100.01 and above
Similarly, BOOK_STORES are divided by zip codes into:
EAST: Zip up to 35000
MIDDLE: Zip 35001 to 70000
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: 81, 84
MIDDLE: 81, 82
WEST: 81, 82, 83, 84
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 S45 to S55 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, $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.
a. Give an example of two simple predicates that would be meaningful for the BOOKSTORE relation for horizontal partitioning.
b. How would a derived horizontal partitioning of STOCK be defined based on the partitioning of BOOKSTORE?
c. Show predicates by which BOOKS may be horizontally partitioned by topic.
d. Show how the STOCK may be further partitioned from the partitions in (b) by adding the predicates in (c).
Fundamentals of Database Systems
ISBN: 978-0136086208
6th edition
Authors: Ramez Elmasri, Shamkant Navathe