Use the facts given in previous exercises to sumplement the tasks if needed.
1. The example database used in Introduction to Databases course is described in page http://www.cs.helsinki.fi/u/laine/tkpv/pizza2sql.html. Consider the following query
select i.orderid, i.productid, pn.objectname, p.modelid, p.price, i.amount from itemordered i, product p, objectname pn where p.productid=i.productid and i.modelid=p.modelid and i.orderid=3019 and pn.language='english' and pn.objectid=i.productida) What does it do?
2. Consider the database of task 1. Tables product and objectname are implemented as heaps. Page size is 4 KB. The average length of a product record is 55 bytes and the corresponding length of an objectname record is 60 bytes. There is space available for 40 buffers. How would you implement the join (key - foreign key) of these tables. How many disk accesses is needed?
3. Consider the database of task 1. Tables ordered and itemordered are implemented as heaps. Both have a B+ -tree secondary index on column OrderId. OrderId is the key of table Ordered. Estimate the number of disk accesses for the different ways of implementing the join based on the OrderId column. Let the length of an Ordered record be 70 bytes and the length of an ItemOrdered record 60 bytes, in average. You may use 40 buffer frames.
4. The result of an outer join contains the result of a normal join and in addition the rows that did not match are included as paired with a null value record. Consider the techniques of implementing join in implementing outer join? May they all be modified for outer join. Do they apply for both one left outer join and for full outer join. Describe for some technique how it should be modified.
5. Construct a small example to show that projection and intersection and projection and difference are not commutable (i.e. projectx (A intersect B) is not equal to (projectx (A)) intersect (projectx (B)).