Svar till relationsalgebraövningen (Databaser, fortsätt ningskurs)

Salesperson(SSN, Name, StartYear, DeptNo)
Trip(SSN, FromCity, ToCity, DepartureDate, ReturnDate, TripId)
Expense(TripId, Account, Amount)

• Foreign keys: In the table Trip, SSN is a foreign key which references SSN in the table Salesperson. In the table Expense, TripId is a foreign key which references TripId in the table Trip.
• SQL and relational algebra. (Replace PROJECT, JOIN etc with the appropriate symbols.)
  1. Print the SSNs of all salesmen who took trips to Honolulu.

     select SSN from Trip where ToCity = 'Honolulu';
     

     PROJECT_SSN [ SELECT_{ToCity = "Honolulu"} (Trip) ]
  2. Which cities did the salesman Bengt go to?

     select ToCity from Salesperson, Trip where Name = 'Bengt' and Trip.SSN = Salesperson.SSN;
     

     PROJECT_ToCity ( [ SELECT_{Name = "Bengt"} (Salesperson) ] JOIN_{Salesperson.SSN = Trip.SSN} Salesperson )
  3. Print the total trip expenses incurred by the salesman with SSN='234-56-7890'.

     select sum(Amount) from Trip, Expense
     where SSN = '234-56-7890'
     and Trip.TripId = Expense.TripId;
     

     F_sum(Amount) ( [SELECT_{SSN = "234-56-7890"}(Trip)] JOIN_{Trip.TripId = Expense.TripId} Expense )
  4. Give the details (all attributes of the Trip relation) for trips that exceeded $2000 in expenses.

     select Trip.*
     from Trip, Expense
     where Trip.TripId = Expense.TripId
     group by Trip.TripId
     having sum(Amount) > 2000;
     

     ExpensiveTrips <- SELECT_{sum(Amount) > 2000} [ _TripIDF_sum(Amount) ( Expense ) ]
     Result <- Trip JOIN_{Trip.TripId = ExpensiveTrips.TripId} ExpensiveTrips
  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.

     select Salesperson.SSN, sum(Amount)
     from Salesperson, Trip, Expense
     where Salesperson.SSN = Trip.SSN
     and Trip.TripId = Expense.TripId
     group by Salesperson.SSN;
     

     _{Salesperson.SSN}F_sum(Amount) [ ( Salesperson JOIN_{Salesperson.SSN = Trip.SSN} Trip ) JOIN_{Trip.TripId = Expense.TripId} Expense ]
  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.

     select Salesperson.SSN, sum(Amount)
     from Salesperson left outer join Trip on Salesperson.SSN = Trip.SSN
         left outer join Expense on Trip.TripId = Expense.TripId
     group by Salesperson.SSN;
     

     _{Salesperson.SSN}F_sum(Amount) [ ( Salesperson LEFT OUTER JOIN_{Salesperson.SSN = Trip.SSN} Trip ) LEFT OUTER JOIN_{Trip.TripId = Expense.TripId} Expense ]

     Or: _{Salesperson.SSN}F_sum(Amount) [ Salesperson LEFT OUTER JOIN_{Salesperson.SSN = Trip.SSN} ( Trip JOIN_{Trip.TripId = Expense.TripId} Expense ) ]

     Note: The following doesn't work, since salespersons with no trips have no expenses either:

     select Salesperson.SSN, sum(Amount)
     from Salesperson left outer join Trip on Salesperson.SSN = Trip.SSN, Expense
     where Trip.TripId = Expense.TripId
     group by Salesperson.SSN;
     

     _{Salesperson.SSN}F_sum(Amount) [ ( Salesperson LEFT OUTER JOIN_{Salesperson.SSN = Trip.SSN} Trip ) JOIN_{Trip.TripId = Expense.TripId} Expense ]
  7. Who worked here the longest? (That is, which salesperson has the lowest StartYear?)

     select *
     from Salesperson
     where StartYear in (select min(StartYear) from Salesperson);
     

     Salesperson JOIN_{StartYear = min(StartYear)} [ F_{min(StartYear)} ( Salesperson ) ]
• Why should you learn relational algebra?
  • The inner, lower-level operations of a relational DBMS are, or are similar to, relational algebra operations. We need to know about relational algebra to understand query execution and optimization in a relational DBMS.
  • Some advanced SQL queries requires explicit relational algebra operations, especially outer join.