Relationsalgebraövning (Databasteknik II)

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)

• Specify the foreign keys.
• State the following queries in relational algebra, and also (if it is not immediately obvious to you) in SQL:
  1. Print the SSNs of all salesmen who took trips to Honolulu.
  2. Which cities did the salesman Bengt go to?
  3. Print the total trip expenses incurred by the salesman with SS='234-56-7890'.
  4. Give the details (all attributes of the Trip relation) for trips that exceeded $2000 in expenses.
  5. Give the total trip expenses for each salesperson. Salespersons who haven't taken any trips at all need not be present in the answer.
  6. 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.
  7. 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]