1. Engineering
  2. Computer Science
  3. the purpose of this assignment is to familiarize with query...

Question: the purpose of this assignment is to familiarize with query...

Question details

The purpose of this assignment is to familiarize with query optimization.

  • Consider the following four queries specified on Henry Books database (schema and tables):

Q1: List the lastnames of pair of authors who co-authored at least one book.

SELECT a1.AuthorLast, a2.AuthorLast
FROM Author a1, Wrote w1, Author a2, Wrote w2
WHERE a1.AuthorNum = w1.AuthorNum and a2.AuthorNum = w2.AuthorNum
     and w1.BookCode = w2.BookCode and a1.AuthorNum < a2.AuthorNum;


Q2: List the name of publisher and the total number of books from that publisher in inventory from branches that are located in Eastshore Mall.

SELECT p.PublisherName as Publisher, Sum(i.OnHand) as TotalBooks
FROM Publisher p, Book b, Inventory i, Branch r
WHERE p.PublisherCode = b.PublisherCode and b.BookCode = i.BookCode
      and i.BranchNum = r.BranchNum and r.BranchLocation = 'Eastshore Mall'
GROUP BY p.PublisherName;


Q3: List titles of books that are written by author with lastname as Rowling or books available at branch Henry Brentwood.

    SELECT Title
    FROM Book b, Wrote w, Author a
    WHERE b.BookCode = w.BookCode and w.AuthorNum = a.AuthorNum and a.AuthorLast = 'Rowling'
   UNION
    SELECT Title
    FROM Book b, Inventory i, Branch r
    WHERE b.BookCode = i.BookCode and i.BranchNum = r.BranchNum
          and r.BranchName = 'Henry Brentwood' and i.OnHand > 0;


Q4: List the name of publisher that published the book wrote by author with last name as king and as the first author of the book.

SELECT PublisherName
FROM Publisher p, Book b, Wrote w, Author a
WHERE p.PublisherCode = b.PublisherCode
  and b.BookCode = w.BookCode
  and w.AuthorNum = a.AuthorNum
  and a.AuthorLast = 'King'
  and w.Sequence = 1;

Draw the initial query tree for each of these queries, then show how the query tree is optimized (one rule at a time) by the algorithm outlined in chapter 19
and write the SQL query for the final optimized query tree.

  • Extend the sort-merge join algorithm shown in the modified Figure to implement the right outer join operation.
Solution by an expert tutor
Blurred Solution
This question has been solved
Subscribe to see this solution