703 views

Consider the following schema:

• SUPPLIER (supId : integer, supName : string, supAddress : string)
PARTS (partId : integer, partName : string, partColour : string)
CATALOG (supId : integer, partId : integer, price : real)

The key fields are underlined, and the domain of each field is listed after the field name. The CATALOG relation lists the prices charged for parts by suppliers.

1. Let the relations have the following properties:

$\begin{array}{|l|l|l|} \hline \text{Relation} & \text{Total Number of Tuples} & \text{No. of tuples per block} \\ \hline \text{SUPPLIER} & 2,000 & 25 \\ \hline \text{PARTS} & 4,500 & 30 \\ \hline \text{CATALOG} & 9,000 & 45 \\ \hline \end{array}$

Estimate the number of block accesses required to produce the result of the following query: Find the names of suppliers who supply every part.

2. Write the above query (given in (a)) in relational algebra using some or all of the following operators: SELECT, PROJECT, JOIN, CARTESIAN PRODUCT, UNION, INTERSECTION, DIFFERENCE.

(a) Query:
SELECT supName
FROM supplier S
WHERE (SELECT COUNT(DISTINCT(partId))
FROM catalog
WHERE S.supId=supId) = (SELECT COUNT(*)
FROM parts)

For the two inner queries no. of block accesses =(4500/30)+(9000/45) = 350

For each tuple in supplier we execute two inner queries so,

no of block accesses = 350 * 2000 =700000

no. of block accesses for table supplier is (2000/25) 80

So total block accesses = 700080