K
K
KOPC18862015-03-05 15:04:04
SQL
KOPC1886, 2015-03-05 15:04:04

How to write a query using relational algebra language?

The relations simulating the work of the bank and its branches are given. A client can have several accounts, and they can be placed both in one and in different branches of the bank. R1 contains information about all clients and their accounts in our bank's branches. Each client, in accordance with his account, can count on some credit from our bank, the amount of credit allowed is also fixed.
R1
Customer Name Branch # Account # Balance Credit
R2
Branch # District
Use relational algebra to write queries to select:
1. Branches whose customers have accounts with a balance greater than $1,000.
2. Clients who have accounts in all branches of this bank.
3. Clients who have only one account in different branches of the bank. That is, in general, these customers can have several accounts, but there is no more than one account in one branch.
4. Clients who have accounts in several bank branches located in only one area.
5. Branches that do not have a single client.
6. Branches that have customers with an account balance of 0 (zero).
7. Branches that have customers with a loan that exceeds the account balance by 2 times.
I more or less succeeded in compiling a query in sql, but not using relational algebra.
The relational algebra query should look something like this
: "R4 = (R2[R3GroupNumber = R2.GroupNumber ^ R3.Subject = "DB"] R3)[Name]"
For example question 2 - Clients who have accounts in all branches of a given bank
In sql -

SELECT ФИОклиента   FROM r1 GROUP BY ФИОклиента   HAVING COUNT(DISTINCT Nфилиала ) = (SELECT COUNT(DISTINCT Nфилиала) FROM r2)

And using relational algebra, I don’t know how to express question 2.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question