SQL Relational Algebra Query

Question

I have a schema that has two relations. One is loan which has the attributes loan-number, branch-name and amount. The other is borrower which has customer-name and loan-number as its attributes. These two relations are linked via loan-number.

How would I write a query in relational algebra to find the names of customers with a balance less than 10000?

How would I do this as a SQL query?

See [this answer & its question](http://stackoverflow.com/a/24425914/3404097). — philipxy, Jun 17 '16 at 11:41

score 4 · Accepted Answer · answered Jan 08 '14 at 14:01

4

Do some research on relational algebra 8 main operators: restriction, projection, cartesian product, join, union, intersection, set difference and division.

To answer your question:

loan(loan_number, branch_name, amount)
borrower(customer_name, loan_number)

Perform a natural join of the both relations, apply the restriction (balance less than 10000) and then display the names with the use of a projection. The following 2 relational algebra expressions below will both answer your question relational algebra code

meaning of symbols

Both expressions evaluate to the following SQL query:

select customer_name
from borrower b, loan l
where b.loan_number=l.loan_number and amount>10000;

answered Jan 08 '14 at 14:01

D. Rattansingh

1,569
3
19
30

PLease never suggest implicit joins. THey are a SQL antipattern. No one should be learning this long-ago replaced (and for replaced for good reason) syntax. – HLGEM Jan 08 '14 at 14:06
@HLGEM I'm a bit puzzled by your comment - a natural join in this instance is just a join between 2 tables with a common value (The result of the natural join is the set of all combinations of tuples in R and S that are equal on their common attribute names). It's not the practice of joining 2 tables and then specifying the join the where clause which I think is what you're referring to ? – SteveB Jan 08 '14 at 14:10
According to strict relational algebra, when using the join symbol without a predicate, it implies a natural join - however when using SQL you need to explicitly put it in - taken from the connolly & begg Database systems book. – D. Rattansingh Jan 08 '14 at 16:09
@bhs you're correct. A natural join is a join between 2 relations on the common attributes - i.e. attributes involved in foreign key to primary key relationships. – D. Rattansingh Jan 08 '14 at 16:10
Correct me if I'm wrong, but if your're using a natural join symbol with a predicate (or theta-join), you'd have to rename two relations with matching attributes in order to match them? (e.g. Relation A has attribte id, name and Relation B has attribute id, color. rename B to id2 and use id=id2 on the theta join) – Daniel Macias Apr 13 '15 at 21:58

score 1 · Answer 2 · answered Jan 08 '14 at 14:10

Disclaimer: I am not too familiar with relational algebra.

SQL can be quickly seen as using an implicit inner join and then an filter on loan.amount:

SELECT customer-name
FROM Borrower, Loan
WHERE Customer.loan-number = Loan.loan-number
 AND Loan.amount > 10000

And then translate this into an relational algebra keeping in mind that the following symbols are the only ones needed for this:

The select operation (σ): - to identify a set of tuples which is a part of a relation and to extract only these tuples out. The select operation selects tuples that satisfy a given predicate or condition.

The project operation (Π): - returns its argument relation with certain attributes left out.

 Π customer-name (σ Borrower.loan-number=Loan.loan-number (σ Loan.amount>10000 (Borrower X Loan)))

score 0 · Answer 3 · answered Jan 08 '14 at 13:55

I believe the SQL would be relatively straightforward... something like the following will probably suffice:

SELECT 
  b.customer-name,
  SUM(l.amount)
FROM
  borrower b
  JOIN loan l
    ON b.loan-number = l.loan-number
GROUP BY
  b.customer-name
HAVING
  SUM(l.amount) < 10000

SQL Relational Algebra Query

3 Answers3