anyone plz can help me with those Qs.
Q1: For the Supplier (S), Part(P), and Supplier-Part (SP) tables shown below, assume the
following SQL statement and the given query tree.
S (S#, NAME) /* Suppliers Table
SP (S#, P# , QTY) /* Every suppliers supplying every part
P (P#, NAME) /* Parts Table
Select S.NAME
From S, SP, P
Where S.S# = SP.S#
AND SP.P# = P.P#
AND P.P# = "P3";
Assume there are 4000 usable bytes in a page after the overhead. We have 1000 suppliers and
50 parts in the database. Every supplier supplies every one the 50 parts. Further, assume that we
have 22 buffers and the BTree index height is 3. What is the most efficient way of performing
this plan in terms of number disk I/O?
where : S 1000row(10pages),SP 50000rows(100pages) Cluster Btree index on S#, p 50rows(20pages) un-clustered Btree index on P#
the condition is: you should first join S &SP ,and then join them to P and then PJ where P#=P3
Q2: Assume our Bank has 1000 accounts that are uniformly distributed over 20 branches. In the
following query tree, assume that the selectivity of the first SL is 20% and of the second SL is
40%. How many rows does this plan process? Show the cost of each step and the total cost.
Select cname, balance
from account, branch
Where balance>1000 and bcity=’york’.
And the first selection of balance>1000 should be bushed ahead of the join condition