-1

Database (all entries are integers):

ID | BUDGET
1  | 20
8  | 20
10 | 20
5  | 4
9  | 4
10 | 4
1  | 11
9  | 11

Suppose my constraint is having a budget of >= 10. I would want to return ID of 1 only in this case. How do I go about it? I've tried taking the cross product of itself after selecting budget >= 10 and returning if id1 = id2 and budget1 <> budget2 but that does not work in the case where there's only 1 budget that is >= 10. (EG below)

ID | BUDGET
1  | 20
8  | 20
10 | 20
1  | 4
5  | 4
9  | 4
10 | 4
9  | 4

If I were to do what I did for the first example, nothing will be returned as budget1 <> budget2 will result in an empty table.

EDIT1: I can only use relational algebra to solve the problem. So SQL's exist, where and count keywords cant be used.

Edit2: Only project, select, rename, set difference, set union, left join, right join, full inner join, natural joins, set intersection and cross product allowed

CXY
  • 33
  • 8

1 Answers1

0

The question is not completely clear to me. If you want to return all the ID for which there is a budget greater than 10, and no budget less than 10, the expression is simply the following:

π(ID)(σ(BUDGET>=10)(R)) - π(ID)(σ(BUDGET<10)(R))

If, an the other hand, you want all the ID which have all the budgets present in the relation and greater then 10, then we must use the ÷ operator:

R ÷ π(BUDGET)(σ(BUDGET>=10)(R))

From your comment, the second case is the correct one. Let’s see how to compute the division from its definition (applied to two generic relations R(A) and S(B)):

R ÷ S = πA-B(R) - πA-B((πA-B(R) x S) - R)

where R is the original relation, and

S = π(BUDGET)(σ(BUDGET>=10)(R)),

that is:

BUDGET
------
20
11

Starting from the inner expression:

πA-B(R) is equal to πID(R) =

ID
--
1
5
8
9
10

then πA-B(R) x S) is:

ID BUDGET
---------
 1  20
 1  11
 5  20
 5  11
 8  20
 8  11
 9  20
 9  11
10  20
10  11

then ((πA-B(R) x S) - R) is:

ID BUDGET
---------
 5  20
 5  11
 8  11
 9  20
10  20

then πA-B((πA-B(R) x S) - R) is:

ID
__
 5
 8
 9
10

and, finally, subtracting this relation from πA-B(R) we obtain the result:

ID
--
 1
Renzo
  • 26,848
  • 5
  • 49
  • 61
  • In this case the ID must exist in all budgets that are >=10. If we're using this π(ID)(σ(BUDGET>=10)(R)) - π(ID)(σ(BUDGET<10)(R)) it'll return 1 and 8 which is not correct as 8 is does not exist with a budget of 11 – CXY Feb 11 '21 at 12:30
  • So, the operator to use is the division. I edited the answer. – Renzo Feb 11 '21 at 13:51
  • Omg thank you!! It's was really tricky to wrap my head around the division thing but your original post helped a ton. Thank you!! – CXY Feb 11 '21 at 14:44