Question:
Consider the following database relations for a textbook ordering system used by a college bookshop:
Book (b-copy#, bname, ISBN#, author, price, subject)
Order (s#, b-copy#, order-date, payment)
Student (s#, sname, address, phone, course)
Assuming that:
?The attribute subject contained in the Book relation indicates the subject area a particular book belongs to. Examples of these subjects include 'programming', 'databases', 'operating systems', etc.
? The Student relation contains 1500 records;
? The Order relation contains 3500 records.
? The Book relation contains 4500 records. Each book copy has a unique number b-copy#. There are only 150 copies of "databases" books.
Provide the following:
(i) Express the following query in SQL:
"find all student names who have ordered any database book".
(ii) Choose two possible methods to evaluate the query, and use the relational algebra to formulate clearly all the steps required in the evaluation.
(iii) For each method, produce a query tree depicting all the steps.
(iv) For each method, calculate the number of tuples for all the intermediate relations (namely, size of the intermediate relations) required for each step.