Optimeringsövning (Databasteknik II)

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

E

EnrEnameTelDept
7Tom4711B
3Anne1717B
11Zeb1420A
............

D

DnrDnameAddress
AComputer17 Some Street
BSecurity8 Some Road
.........

Expressed with the usual notation:

E(Enr, Ename, Tel, Dept)
D(Dnr, Dname, Address)
State the following three queries in
  1. What is the name of Tom's department? (With the example data above, the result would be "Security".)
  2. Which people (with number and name) work in Security?
  3. 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
PROJECTA, B, C, ...(SELECTCONDITION(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