Question

(a) What are the operations that correspond to the query expressed using this SQL statement?

SELECT Supplier, Project FROM Part_needs, Parts_inventory WHERE Quantity \( \le 10\).

(b) What is the output of this query given the database in Table 9 and 10 as input?

Short answer

(a) The resultant answer is \({P_{1,3}}\left( {{s_C}\left( {{J_2}(R,S)} \right)} \right)\).

(b) The resultant answer is \((23,1),(23,3),(31,3),(32,4)\).

Step-by-step solution

Given data 

Table 9 and 10 is given.

Concept of sets

The concept of set is a very basic one. It is simple; yet, it suffices as the basis on which all abstract notions in mathematics can be built.

A set is determined by its elements. If\(A\)is a set, write\(x \in A\)to say that\(x\)is an element of\(A\).

Simplify the expression

a)

The first line "SELECT Supplier. Project" means that they are interested in the attribute Supplier and Project. The second line "FROM Part_needs. Parts_Inventory" means that to use the "Part_needs" data set and the Parts_Inventory data set.

Thus first combine the two tables, which can be done using the join \({J_2}\).

Note: The index is 2, because the two data sets have 2 columns in common (Part_number and Project).

\({J_2}\).

The third line "WHERE Quantity means that the only want the Supplier and Project information for those \(n\)-tuples that satisfy the condition \({\rm{C}} = \{ \) Quantity \( \le 10\} \).

\({s_C}\left( {{J_2}(R,S)} \right)\).

Since the Suppler and Project information are in the first and third column in the table, take the projection \({P_{1,3}}\), \({P_{1,3}}\left( {{s_C}\left( {{J_2}(R,S)} \right)} \right)\).

Simplify the expression

(b)

First joint the information in table 9 and 10 into one table:

\(\begin{array}{*{20}{c}}{{\rm{ Supplier }}}&{{\rm{Par}}{{\rm{t}}_{\rm{n}}}{\rm{umber}}}&{{\rm{ Project }}}&{{\rm{ Quantity }}}&{{\rm{Colo}}{{\rm{r}}_{\rm{c}}}{\rm{ode}}}\\{23}&{1092}&1&2&2\\{23}&{1101}&3&1&1\\{23}&{9048}&4&{12}&2\\{31}&{4975}&3&6&2\\{31}&{3477}&2&{25}&2\\{32}&{6984}&4&{10}&1\\{32}&{9191}&2&{80}&4\\{33}&{1001}&1&{14}&8\end{array}\)

Remove the rows that do not satisfy the condition:

\(\begin{array}{*{20}{c}}{{\rm{ Supplier }}}&{{\rm{Par}}{{\rm{t}}_{\rm{n}}}{\rm{umber}}}&{{\rm{ Project }}}&{{\rm{ Quantity }}}&{{\rm{Colo}}{{\rm{r}}_{\rm{c}}}{\rm{ode}}}\\{23}&{1092}&1&2&2\\{23}&{1101}&3&1&1\\{31}&{4975}&3&6&2\\{32}&{6984}&4&{10}&1\end{array}\)

Next restrict the data set to only include the "Supplier" and “Project” data:

\(\begin{array}{*{20}{c}}{{\rm{ Supplier }}}&{{\rm{ Project }}}\\{23}&1\\{23}&3\\{31}&3\\{32}&4\end{array}\)

There are no repetitions in the table, thus the output of the query then contains all \(n\)-tuples in the above table: \((23,1),(23,3),(31,3),(32,4)\).