Question

Give the tuple calculus expressions for each using the below schema: Make a database up if needed

SPERSON(EmpID, Name, Dept)

TRIP(TripID, EmpID, ToCity, StartDate, EndDate)

EXPENSE(ExpID, TripID, AccountID, Amount)

a) List the names and employee ID of a salesperson.

b) List employee ID and employee names that took a trip(s) to New York city.

c) List the names of all employees who have spent more than $700 on their trip.

d) List employees' names and their IDs that have never taken a trip.

e) List the names of all salespersons that have at least been on one trip.

Answer 1

Please vote if you find this solution helpful :)

a) List the names and employee ID of a salesperson.

select EmpID,Name from SPERSON; => (this will return all the rows in table SPERSON)
select EmpID,Name from SPERSON where EmpID=*;
=> (The question says names and employee ID of 'a' person, so replace * with EmpID to return a specific person details.

b) List employee ID and employee names that took a trip(s) to New York city.

select EmpID,Name from SPERSON s,TRIP t where s.EmpID=t.EmpID and t.ToCity='New York city';

c) List the names of all employees who have spent more than $700 on their trip.

select EmpID,Name from SPERSON s,TRIP t,EXPENSE e where s.EmpID=t.EmpID and t.TripID=e.TripID HAVING e.Amount>$700;

d) List employees' names and their IDs that have never taken a trip.

select EmpID,Name from SPERSON s,TRIP t where s.EmpID=!t.EmpID;

e) List the names of all salespersons that have at least been on one trip.

select EmpID,Name from SPERSON s,TRIP t where s.EmpID=t.EmpID;

thank you.