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

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) 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.