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.