ρa/a1(πa1(( A x A) - σa1 < a2 (ρa1/a(A) x ρa2/a(A))))
Assuming you have a relation, A, with a single attribute, 'a' (reducing a more complex relation to this is a simple task in relational algebra, I'm sure you got this far), so now you want to find the maximum value in A.
One way to do it is to find the cross product of A with itself, be sure to rename 'a' so your new relation has attributes with distinct names. for example:
(rename 'a' as 'a1') X (rename 'a' as 'a2')
now select 'a1' < 'a2', the resulting relation will have all values except the maximum. To get the max simply find the difference between your original relation:
(A x A) - (select 'a1' < 'a2') ((rename 'a' as 'a1')(A) x (rename 'a' as 'a2')(A))
Then use the project operator to reduce down to a single column as Tobi Lehman suggests in the comment below.
Writing this in relational algebra notation would be (if I remember correctly). Note the final rename (i.e. ρ) is just to end up with an attribute that has the same name as in the original relation:
ρa/a1(πa1((A x A) - σa1 < a2 (ρa1/a(A) x ρa2/a(A))))
1. First we want to PROJECT and RENAME relation A
2. We then to a THETA JOIN with the test a1<a2
3. We then PROJECT the result of the relation to give us a single set of values
a1: 1,2,3 (not max value since a1<a2)
4. We then apply the difference operator with the original relation so:
1,2,3,4 --- 1,2,3 returns 4
4 is the Max value.
I know this is old, but here is a hand-written formula which might be handy!
Relation A: 1,2,3,4
1. First we want to PROJECT and RENAME relation A
2. We then to a THETA JOIN with the test a1<a2
3. We then PROJECT the result of the relation to give us a single set of values
a1: 1,2,3 (not max value since a1<a2)
4. We then apply the difference operator with the original relation so:
1,2,3,4 --- 1,2,3 returns 4
4 is the Max value.
其中结果是比某人年轻的人，以及他们比谁年轻。然而我们的查询是：Only people who are not young to some 这与此完全相反。所以这不是我们的目标，我们需要做更多的事情。如果您这样做：
π PEOPLE.name PEOPLE.age (σ (PEOPLE.age < ALT.age) (PEOPLE x (ρ ALT (PEOPLE))))
这将为我们提供一个由 megan、harry 和 michael 组成的表格，该表格由以下内容组成：仅比某人年轻的人
步骤 3 （得到我们的最终表）
现在我们有一个表，其中包含仅比某人年轻的人，但我们想要的是仅不比某人年轻的人所以什么我们需要做的是从 PEOPLE 表中删除所有比某人年轻的人，只给我们不比某人年轻的人。
因此，我们需要使用减法运算从PEOPLE 表中删除这些元组。这为我们提供了最终查询：
PEOPLE - (π PEOPLE.name PEOPLE.age (σ (PEOPLE.age < ALT.age) (PEOPLE x (ρ ALT (PEOPLE)))))
生成下表：
PEOPLE - (π PEOPLE.name PEOPLE.age (σ (PEOPLE.age < ALT.age) (PEOPLE x (ρ ALT (PEOPLE)))))
PEOPLE.name PEOPLE.age
'jack' 16
'lilly' 16
其中 Jack 和 Lilly 是 PEOPLE 中唯一不比某人年轻的人。
Relational Algebra (Filtering through Comparison)
Recently had this question turn up as practice material in a Database module and when I was searching around for help I couldn't find any good answers. The answer by "Md. Rezwanul Haque" is correct but it's not really explained as it relies on prior knowledge (If you don't understand Cartesian Product that is).
So, here is the answer with my explanation hopefully this makes it easier for some:
The idea is to "Collect what you don't want and remove it from what you have; leaving you with what you want."
Step 1 (Building a Table to Query)
When filtering using SELECTION we can only compare what is in the Tuple we have. This means we need to add to those tuples the data we want to compare it to.
So, we would need to merge our PEOPLE Table with the data we want to compare with. This can be done using the x (Cartesian Product) Operator
Something like this: PEOPLE x PEOPLE however we cannot do this as the resulting table would look something like this:
TABLE: PEOPLE x PEOPLE
PEOPLE.name PEOPLE.age PEOPLE.name PEOPLE.age
'Jack' 16 'Jack' 16
We have duplicate Attribute names this means that we need to create a Copy of the PEOPLE Table, one which has a different name that we can reference. Otherwise we cannot use the x Cartesian Product Operator as it requires that all attributes be unique in the resulting table, you can not have two PEOPLE.name attributes.
This is where we would use the RENAME Operator which would look something like this:
PEOPLE ⨯ (ρ ALT (PEOPLE))
Here what I've done is use the Cartesian Product to merge PEOPLE and ALT where ALT is PEOPLE renamed to ALT
This would give us a Table that looks a bit like this:
TABLE: PEOPLE x ALT
PEOPLE.name PEOPLE.age ALT.name ALT.age
'jack' 16 'jack' 16
'jack' 16 'megan' 15
'jack' 16 'harry' 14
'jack' 16 'lilly' 16
'jack' 16 'michael' 8
(The resulting table is (PEOPLE.size * PEOPLE.size) = 5*5 where size is the number of tuples) Where every value of PEOPLE is put against every value of ALT
Step 2 (Selecting)
Now we can filter through all values and grab the ones we don't want. So let's say I only want the eldest people in PEOPLE this question can be reworded to: Only people who are not younger than someone so we grab only those who are younger than someone. We do this because it's easier to Query for what we don't want that what we do want.
So, our Predicate would be: PEOPLE.age < ALT.age where we are selecting only those who are younger than someone.
If we were to reverse the Predicate to PEOPLE.age > ALT.age we would get a mix of people who are not the eldest, but who are older than at least one person. This could help us obtain the person who is the youngest
Giving us a SELECTION like this:
(σ (PEOPLE.age < ALT.age) (PEOPLE x (ρ ALT (PEOPLE))))
Where the results are people who are younger than someone, and who they're younger than. However our Query is: Only people who are not younger than someone which is the exact opposite of this. So this isn't our goal, we need to do a little bit more. If you were to do:
π PEOPLE.name PEOPLE.age (σ (PEOPLE.age < ALT.age) (PEOPLE x (ρ ALT (PEOPLE))))
This would give us a table consisting of megan, harry, and michael this is a table consisting of: Only people who are younger than someone
Step 3 (Getting our final Table)
Now we have a Table that consists of Only people who are younger than someone but what we want is Only people who are not younger than someone so what we need to do is remove all of the people who are younger than someone from the PEOPLE Table to give us only those who are not younger than someone.
So we need to use the Subtraction Operation to remove those Tuples from our PEOPLE table. Which gives us our Final Query of:
PEOPLE - (π PEOPLE.name PEOPLE.age (σ (PEOPLE.age < ALT.age) (PEOPLE x (ρ ALT (PEOPLE)))))
Which produces the following Table:
PEOPLE - (π PEOPLE.name PEOPLE.age (σ (PEOPLE.age < ALT.age) (PEOPLE x (ρ ALT (PEOPLE)))))
PEOPLE.name PEOPLE.age
'jack' 16
'lilly' 16
Where Jack and Lilly are the only people in PEOPLE who are NOT Younger than someone.
发布评论
评论（8）
假设您有一个关系 A，具有单个属性“a”（减少一个更复杂的关系是关系代数中的一个简单任务，我确信您已经做到了这一点），所以现在您想要找到最大值A 中的值。
一种方法是找到 A 与其自身的叉积，请务必重命名“a”，以便您的新关系具有具有不同名称的属性。例如：（
将“a”重命名为“a1”）X（将“a”重命名为“a2”）
现在选择“a1”<； 'a2'，结果关系将具有除最大值之外的所有值。要获得最大值，只需找到原始关系之间的差异：
然后使用
project运算符减少到单个列，如 Tobi Lehman 在下面的评论中建议的那样。用关系代数符号来写这个（如果我没记错的话）。请注意，最终重命名（即 ρ）只是为了得到与原始关系中具有相同名称的属性：
ρa/a1(πa1(( A x A) - σa1 < a2 (ρa1/a(A) x ρa2/a(A))))
Assuming you have a relation, A, with a single attribute, 'a' (reducing a more complex relation to this is a simple task in relational algebra, I'm sure you got this far), so now you want to find the maximum value in A.
One way to do it is to find the cross product of A with itself, be sure to rename 'a' so your new relation has attributes with distinct names. for example:
(rename 'a' as 'a1') X (rename 'a' as 'a2')
now select 'a1' < 'a2', the resulting relation will have all values except the maximum. To get the max simply find the difference between your original relation:
Then use the
projectoperator to reduce down to a single column as Tobi Lehman suggests in the comment below.Writing this in relational algebra notation would be (if I remember correctly). Note the final rename (i.e. ρ) is just to end up with an attribute that has the same name as in the original relation:
ρa/a1(πa1((A x A) - σa1 < a2 (ρa1/a(A) x ρa2/a(A))))
只是我的两分钱，因为我今天试图自己解决这个问题。
假设我们有 A = 1,2,3
如果您使用，
您将不会获得单个最大值，而是获得两列，例如 1|1, 2|1,3|2,3|1,3|2,3|
3获得 3 的方法是
至少这是我在类似情况下必须做的。
希望它可以帮助某人
Just my two cents as I was trying to solve this today myself.
Lets say we have A = 1,2,3
If you use
you will not get the single max value rather two columns like 1|1, 2|1,3|2,3|1,3|2,3|3
the way to get just 3 is
At least that is what I had to do in a similar situation.
Hope it helps someone
假设我们与属性 A 和值 1,2,3 存在关系，
所以现在..
用 A1 重命名
项目 A 值并再次
项目 A 值并使用 A2 重命名，
使用
A2 加入此项目，即 \join_{A2 所以 - 输出模式：（A2整数，A1整数）
总是听到A2值将小于A1，因为我们像这样
加入（a2） 现在投影A2输出如下所示，
现在与原始属性不同
输出为
3最大值lets think we have a relation with an attribute A and values 1,2,3
so now..
project A values and rename with A1
again
project A values and rename with A2
join this with
A2<A1i.e\join_{A2<A1}so the - Output schema: (A2 integer, A1 integer)
hear always A2 values will be less than A1 because we
joinlike that(a2<a1)now project A2 the output is like below
now diff with original attribute
Output is
3maximum value我现在已经忘记了大部分关系代数语法。仅使用
SELECT、PROJECT、MINUS和RENAME的查询希望您能翻译！
I've forgotten most of the relational algebra syntax now. A query just using
SELECT,PROJECT,MINUSandRENAMEwould beHopefully you can translate!
我知道这已经很旧了，但这里有一个手写的公式，可能会很方便！
关系 A：1,2,3,4
I know this is old, but here is a hand-written formula which might be handy!
Relation A: 1,2,3,4
找到最大值：
策略：
找到那些不是
MAX的x。A关系重命名为d，以便我们可以将每个Ax与所有其他关系进行比较。使用
set Difference来查找在前面的步骤中未找到的Ax。查询是：
Find the MAX:
Strategy:
Find those
xthat are not theMAX.Arelation asdso that we can compare eachAxwith all others.Use
set differenceto find thoseAxthat were not found in the earlier step.The query is:
关系代数（通过比较过滤）
最近这个问题出现在数据库模块中作为练习材料，当我四处寻找帮助时，我找不到任何好的答案。 “Md. Rezwanul Haque”的答案是正确的，但并没有真正解释，因为它依赖于先验知识（如果您不理解笛卡尔积）。
所以，这是我的解释的答案，希望这能让一些人更容易：
这个想法是“收集你不想要的东西，并将其从你拥有的东西中删除；留下你想要的东西。” code>
第 1 步（构建要查询的表）
使用
SELECTION进行过滤时，我们只能比较元组中的内容。这意味着我们需要将要与之比较的数据添加到这些元组中。因此，我们需要将
PEOPLE表与我们想要比较的数据合并。这可以使用x（笛卡尔积）运算符来完成，如下所示：
PEOPLE x PEOPLE但是我们不能这样做，因为结果表看起来像这样：我们有
重复的属性名称这意味着我们需要创建PEOPLE表的副本，该表具有我们可以引用的不同名称。否则，我们无法使用x笛卡尔积运算符，因为它要求所有属性在结果表中都是唯一的，您不能有两个PEOPLE.name属性。这是我们使用
RENAME Operator的地方，它看起来像这样：PEOPLE ⨯ (ρ ALT (PEOPLE))这里我所做的是使用
>笛卡尔积合并PEOPLE和ALT，其中ALT是PEOPLE重命名到ALT这将为我们提供一个看起来有点像这样的表：（
结果表为 (PEOPLE.size * PEOPLE.size) = 5*5，其中 size 是元组的数量）其中
的每个值PEOPLE与ALT的每个值相对应第 2 步（选择）
现在我们可以过滤所有值并获取我们不想要的值。因此，假设我只想要
PEOPLE中最年长的人，这个问题可以改写为：Only who are not young to someone所以我们只抓取那些比某人年轻的人。我们这样做是因为查询我们不想要的内容比查询我们想要的内容更容易。因此，我们的谓词将是：PEOPLE.age
PEOPLE.agePEOPLE.agePEOPLE.agePEOPLE.agePEOPLE.agePEOPLE.age ALT.age，我们仅选择那些比某人年轻的人。如果我们将
Predicate反转为PEOPLE.age > ALT.age我们会得到一组不是最年长的人，但至少比一个人年长。这可以帮助我们获得最年轻的人给我们一个像这样的
SELECTION：这将产生一个像这样的表：
其中结果是比某人年轻的人，以及他们比谁年轻。然而我们的查询是：
Only people who are not young to some这与此完全相反。所以这不是我们的目标，我们需要做更多的事情。如果您这样做：这将为我们提供一个由
megan、harry 和 michael组成的表格，该表格由以下内容组成：仅比某人年轻的人步骤 3 （得到我们的最终表）
现在我们有一个表，其中包含
仅比某人年轻的人，但我们想要的是仅不比某人年轻的人所以什么我们需要做的是从 PEOPLE 表中删除所有比某人年轻的人，只给我们不比某人年轻的人。因此，我们需要使用
减法运算从PEOPLE 表中删除这些元组。这为我们提供了最终查询：生成下表：
其中 Jack 和 Lilly 是 PEOPLE 中唯一不比某人年轻的人。
Relational Algebra (Filtering through Comparison)
Recently had this question turn up as practice material in a Database module and when I was searching around for help I couldn't find any good answers. The answer by "Md. Rezwanul Haque" is correct but it's not really explained as it relies on prior knowledge (If you don't understand Cartesian Product that is).
So, here is the answer with my explanation hopefully this makes it easier for some:
The idea is to
"Collect what you don't want and remove it from what you have; leaving you with what you want."Step 1 (Building a Table to Query)
When filtering using
SELECTIONwe can only compare what is in the Tuple we have. This means we need to add to those tuples the data we want to compare it to.So, we would need to merge our
PEOPLETable with the data we want to compare with. This can be done using thex (Cartesian Product) OperatorSomething like this:
PEOPLE x PEOPLEhowever we cannot do this as the resulting table would look something like this:We have
duplicate Attribute namesthis means that we need to create aCopyof thePEOPLETable, one which has a different name that we can reference. Otherwise we cannot use thex Cartesian Product Operatoras it requires that all attributes be unique in the resulting table, you can not have twoPEOPLE.nameattributes.This is where we would use the
RENAME Operatorwhich would look something like this:PEOPLE ⨯ (ρ ALT (PEOPLE))Here what I've done is use the
Cartesian Productto mergePEOPLEandALTwhereALTisPEOPLE renamedtoALTThis would give us a Table that looks a bit like this:
(The resulting table is (PEOPLE.size * PEOPLE.size) = 5*5 where size is the number of tuples) Where every value of
PEOPLEis put against every value ofALTStep 2 (Selecting)
Now we can filter through all values and grab the ones we don't want. So let's say I only want the eldest people in
PEOPLEthis question can be reworded to:Only people who are not younger than someoneso we grab only those who are younger than someone. We do this becauseit's easier to Query for what we don't want that what we do want.So, our
Predicatewould be:PEOPLE.age < ALT.agewhere we are selecting only those whoare younger than someone.If we were to reverse the
PredicatetoPEOPLE.age > ALT.agewe would get a mix of people who are not the eldest,but who are older than at least one person. This could help us obtain the person who isthe youngestGiving us a
SELECTIONlike this:This would produce a TABLE like this:
Where the results are people who are younger than someone, and who they're younger than. However our Query is:
Only people who are not younger than someonewhich is the exact opposite of this. So this isn't our goal, we need to do a little bit more. If you were to do:This would give us a table consisting of
megan, harry, and michaelthis is a table consisting of:Only people who are younger than someoneStep 3 (Getting our final Table)
Now we have a Table that consists of
Only people who are younger than someonebut what we want isOnly people who are not younger than someoneso what we need to do isremove all of the people who are younger than someone from the PEOPLE Table to give us only those who are not younger than someone.So we need to use the
Subtraction Operationto remove those Tuples from ourPEOPLE table. Which gives us our Final Query of:Which produces the following Table:
Where Jack and Lilly are the
only people in PEOPLE who are NOT Younger than someone.