Adapted from Elmasri, Navathe: Fundamentals of Database Systems, 3d Ed, exercise 7.25.
A company has a database to keep track of the travel expenses of its salespersons.
The database contains the following relations:
Salesperson(SSN, Name, StartYear, DeptNo)
Trip(SSN, FromCity, ToCity, DepartureDate, ReturnDate, TripId)
Expense(TripId, Account, Amount)
Note:
The cost of a trip is split among one or more accounts.
To find the cost of one trip, you have to add the amounts
on that trip's rows in the Expense relation.
-
Specify the foreign keys.
-
State the following queries in relational algebra,
and also (if it is not immediately obvious to you) in SQL:
-
Print the SSNs of all salesmen who took trips to Honolulu.
-
Which cities did the salesman Bengt go to?
-
Print the total trip expenses incurred by the salesman with SS='234-56-7890'.
-
Give the details (all attributes of the Trip relation)
for trips that exceeded $2000 in expenses.
-
Give the total trip expenses for each salesperson.
Salespersons who haven't taken any trips at all need not be
present in the answer.
-
Give the total trip expenses for each salesperson.
Salespersons who haven't taken any trips at all
should be present in the answer, with a total cost of 0.
-
Who worked here the longest?
(That is, which salesperson has the lowest StartYear?)
-
Relational algebra is (almost) never used as a query language
in real database management systems.
So why should you learn relational algebra?
[Solutions]
Thomas Padron-McCarthy
(thomas.padron-mccarthy@oru.se),
11 februari 2007