University of Helsinki, Depaertment of Computer Science
Database Management, Practice Session 4, Spring 2003

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.productid
a) What does it do?
b) Construct a tree representation of the query (find out the operations of relational algebra and define their sequence)
c) Estimate the size on final and intermediate results, assuming that table itemordered has 100000 rows for 25000 orders, table product has 100 rows and table objectname has 600 rows for three languages.

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