Member Avatar for katerinaaa

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

  • 2 Contributors
  • 2 Replies
  • 1K Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by katerinaaa
Member Avatar for stymiee

Show us what you've done so far. We're not going to do your homework for you but if you show an effort we can offer advice and tips to help you learn.

Member Avatar for katerinaaa 
BOOK1=select * 
from BOOKS
where $price<=20;

BOOK2=select *
from BOOKS
where ($price >=20.01)AND($price<=50);

BOOK3=...
BOOK4=...

EAST=select *
from BOOKSTORE
where Zip<=35000;

MIDDLE=...
WEST=....

But then I don't understand what I have to do

"Assuming the query was submitted in EAST, what remote subqueries does it generate?"

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.