(D-Problems discussed on Friday, 07-Oct-2016)
(Q-Problems due on Tuesday, 11-Oct-2016)
Discussion Problems: -- Prepare (individually)
for tutorial discussion.
T8-D1: (Simple Algorithmic Query Processing)
For each of the following SQL queries, give a sequence of basic DB operations
(using only e-project, e-select, and e-join)
to efficiently implement the SQL query:
(i) Problem 1 on p.606, and (ii) Problem 4 on p.617, (Ch. 13) of [SG3].
T8-D2: (Algorithmic Query Processing requiring an e-join)
For problem T7-D1, give a sequence of basic DB operations
(using only e-project, e-select, and e-join)
to efficiently implement the SQL query.
T8-D3: (More Complex Algorithmic Query Processing)
For problem T7-D2, give a sequence of basic DB operations
(using only e-project, e-select, and e-join)
to efficiently implement each of the queries.
Problems to be Handed in for Grading by the Deadline:
(Note: Please submit hard copy to me.
Not just soft copy via email.)
T8-Q0: (DO THIS FIRST)
First, read the following notes on
algorithmic-query-processing
and
worked examples.
(Especially so, if you missed attending Tutorial 8 today.)
T8-Q1: (5 points) (Simple Algorithmic Query Processing)
Give a sequence of basic DB operations
(using only e-project, e-select, and e-join)
to efficiently implement the following SQL query.
SELECT ID, LastName, FirstName, PayRate FROM EMPLOYEES WHERE (PayRate < 15.00); |
T8-Q2: (5 points) (Algorithmic Query Processing)
For the SQL queries in T7-Q2(b)(ii), give a sequence of basic DB operations
(using only e-project, e-select, and e-join)
to efficiently implement the query.
Analyze the total number of row operations you need.
(If necessary, estimate the sizes of the intermediate tables.)
Note: The query is
| List the Student-ID, SI.Name, Tel-No of all History majors; |
T8-Q3: (10 points) (Algorithmic Query Processing)
For this question, we explore good and bad algorithms
(in terms of efficiency) to implement
the SQL queries in T7-Q2(b)(i) via a sequence of basic DB operations
(using only e-project, e-select, and e-join).
Recall that the query is
| List the Student-ID, Major, Course-ID of all courses taken by History majors; |
(a) The bad algorithm implements the SQL query via the sequence (e-join, e-select, e-project). Give the sequence of operation and analyze the total number of row operations you need. (If necessary, estimate the sizes of the intermediate tables.)
(b) The good algorithm implements the SQL query via the sequence (e-select, e-join, e-project). Give the sequence of operation and analyze the total number of row operations you need. (If necessary, estimate the sizes of the intermediate tables.)
T8-Q4: (10 points) (Multiple Joins Query)
For the SQL query in T7-Q2(b)(iii), give a sequence of basic DB operations
(using only e-project, e-select, and e-join)
that most efficiently implement the query.
Analyze the total number of row operations you need.
(If necessary, estimate the sizes of the intermediate tables.)
Recall that the query is
| List the Student-ID, SI.Name, Tel-No of all History majors who have lectures in "LT13". |