Optimeringsövning (Databasteknik II)

A database contains the tables E (for Employee) and D (for Department):

Enr	Ename	Tel	Dept
7	Tom	4711	B
3	Anne	1717	B
11	Zeb	1420	A
...	...	...	...

Dnr	Dname	Address
A	Computer	17 Some Street
B	Security	8 Some Road
...	...	...

Expressed with the usual notation:

E(Enr, Ename, Tel, Dept)
D(Dnr, Dname, Address)

State the following three queries in

SQL
relational algebra systematically translated from SQL
efficient relational algebra

What is the name of Tom's department? (With the example data above, the result would be "Security".)
Which people (with number and name) work in Security?
The phone number of everyone called Tom who works in security?

Hint 1: The systematic translation of
select A, B, C, ... from T1, T2, T3, ... where CONDITION
is
PROJECT_{A, B, C, ...}(SELECT_CONDITION(T1 x T2 x T3 x ...))

Hint 2: A relational algebra query is usually more efficient if it produces smaller intermediate results.

[Solutions]

Thomas Padron-McCarthy (thomas.padron-mccarthy@tech.oru.se), 11 februari 2007