2A.Consider the following relations for a database that keeps track of business trips
of salespersons in a sales office:
SALESPERSON(Ssn, Name, StartYear, DeptNo)
TRIP(Ssn, FromCity, ToCity, DepartureDate, ReturnDate, TripId)
EXPENSE(TripId, AccountNo, Amount)
i) A trip can be charged to one or more accounts. Specify the foreign keys for
this schema, stating any assumptions you make.
ii) Write relation algebra expression to get the details of salespersons who have
travelled between Mumbai and Delhi and the travel expense is greater that Rs.
50000.
iii) Write relation algebra expression to get the details of salesperson who had
incurred the greatest travel expenses among all travels made.
iv) List the salespersons who have not been on any trips.
v) Find the total number of trips made by each salesperson.

Question

Answered

Answer :

mishalalim1 mishalalim1 · Answer 1 · 2024-05-12T09:06:53-04:00

Answer:

i) To represent the relationships between the tables using foreign keys:

In TRIP, the Ssn would be the foreign key, such that Ssn references the SALESPERSON table.

In the EXPENSE table, the foreign key referring to the TRIP table would be TripId.

Explanation

i) To represent the relationships between the tables using foreign keys:

In TRIP, the Ssn would be the foreign key, such that Ssn references the SALESPERSON table.

In the EXPENSE table, the foreign key referring to the TRIP table would be TripId.

Assumptions:

What makes the SALESPERSON so unique is that it can go through more than one travel.

The TRIP can have many of the records of EXPENSE based on the trip in the table EXPENSE.

ii) For the information on the salespersons who have traveled between Mumbai and Delhi with travel expenses of more than Rs. 50000:

σ(FromCity='Mumbai' ∧ ToCity='Delhi' ∧ Amount > 50000) (SALESPERSON ⨝ TRIP ⨝ EXPENSE)

iii) To get the details of the salesperson who incurred the greatest travel expenses:

π(Ssn, Name, MAX(Amount)) (SALESPERSON ⨝ TRIP ⨝ EXPENSE)

iv) To list the salespersons who have not been on any trips:

π(Ssn, Name) (SALESPERSON - (σ(Ssn=TRIP.Ssn) (SALESPERSON ⨝ TRIP)))

v) To find the total number of trips made by each salesperson:

γ(Ssn, COUNT(TripId) as TotalTrips) (TRIP) ⨝ SALESPERSON