Question

Given the following 7 relations:

MIScompany (name, address, phone, email, FedTaxId, StaTaxId)

branch (branchId, name, address, phone, email, FedTaxId, StaTaxId)

employee (empId, driverId, ssno, name, branchId)

customer (custId, name, address, driverId, ssno, FedTaxId, StaTaxId)

equipment (equipId, name, type, upc, purchaseDate, year, manufacturId, cost, rentFee, branchId )

manufacturer (manufacturId, name, FedTaxId, StaTaxId, phone, email)

rental (rentalId, equipId, custId, rentDate&time, returnDate&time, empId)

1)

Use relational algebra to list every equipment name with more than three quantities and the name of customer who have rented them. The report should contain equipment name and customer name.

Answer 1

1)Use relational algebra to list every equipment name with more than three quantities and the name of customer who have rented them. The report should contain equipment name and customer name.

Solution:-

Here hint is :-

• E means Employee Table
• C means Customer Table
• R means Rental Table  

The INNER JOIN keyword selects records that have matching values in both tables

• Here the Employee name and Customer name is matching values only Consider and
• like same as before EmplyeeID and rentalID matching values Considered
• like same as before rentalID and CustomerID matching values considered

Here is the Command:-

SELECT E.name,C.name FROM equipment E INNER JOIN rental R ON E.equipid= R.equipid

INNER JOIN customer C ON R R.empid = C.empid Group BY E.name HAVING COUNT(*)>3;