edited by
939 views
2 votes
2 votes

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.

edited by

1 Answer

1 votes
1 votes
(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

Related questions

4 votes
4 votes
3 answers
2
12 votes
12 votes
1 answer
3
go_editor asked May 30, 2016
1,169 views
Construct a context free grammar (CFG) to generate the following language:$L = \{a^nb^mc^{n+m}: \text{n, m are integers, and } n \geq 1, m \geq 1 \}$
5 votes
5 votes
4 answers
4
go_editor asked May 30, 2016
1,729 views
Construct two nonregular languages $L_1$ and $L_2$ such that $L_1 \cup L_2$ is regular.Prove that the languages $L_1$ and $L_2$ constructed above are nonregular and $L_1 ...