Question

SELECT c.Name, p.Title, sc.Quantity, p.amazonExpressPrice, s.Name

FROM ShoppingCart sc, Product p, Customer c, Seller s
WHERE sc.CustomerID = c.CustomerID AND
sc.ItemNumber =
p.ItemNumber AND
sc.OrderStatus = 'active'
AND c.Country =
'Oman' AND
p.SellerID =
s.SellerID

Tree n1
PROJECT
|
SELECT
|
JOIN (ShoppingCart, Product)
/ \
Customer = ItemNumber
| |
Country OrderStatus
| |
'Oman' 'active'
|
JOIN (Result of previous join, Seller)
|
SellerID
|
=
/ \
SellerID SellerID
| |
Seller Seller
| |
's' 'p'

Tree n2

PROJECT
|
JOIN (ShoppingCart, Product, Seller)
|
AND (ItemNumber = OrderStatus)
|
AND (Customer = Country AND OrderStatus = 'active')
|
SellerID = SellerID
|
PROJECT
|

SELECT ('Oman')

Tree n3

PROJECT
|
JOIN (Local_ShoppingCart, Local_Product, Local_Seller)
|
AND (Local_ItemNumber = Local_OrderStatus)
|
AND (Local_Customer = 'Oman' AND Local_OrderStatus = 'active')
|
Local_SellerID = Local_SellerID
|
PROJECT
|

SELECT ('Oman')

Assume that the cost of a block read and write is 400000MB, the transfer time is 0.112 milliseconds, and the number of seeks is 7955 with time to transfer one block is 0.11 milliseconds. Calculate the cost of each of the sites listed above.

Answer 1

Step-by-step explanation:

General Cost Calculation for I/O Operations:

1- Cost of Block Reads:

• Given the cost of a block read is 400000MB, and there are several seeks involved, the cost can be calculated as follows:

Cost of Block Read

Number of Seeks

×

Seek Time

+

Transfer Time

Cost of Block Read=Number of Seeks×Seek Time+Transfer Time

Plug in the values to get the total cost.

2- costs of block writes

Similar to block reads, if there are block writes involved, the cost can be calculated using a similar formula.

Example Calculation:

Let's assume you are calculating the cost for Tree n1:

Cost of Block Read (Tree n1)

=7955*Seek Time+0.112 milliseconds

Cost of Block Read (Tree n1)=7955×Seek Time+0.112milliseconds

You would need to use the specific values provided for the seek time.

Repeat the process for other trees if needed.