What is the rule for the transformation of a column in Relational Algebra? For example, I want to divide all values of a column with the average of that column. I can get average using aggregate rule. But cannot find the rule for column manipulation. P.S: I am interested in the rule (like \Pi is used for projection).
Updating the value of a base variable is not an algebraic or arithmetic operation, it is an operation of a programming language.
There's no single relational algebra.
There's no standard approach to this either in terms of an algebraic or language operator.
Suppose you supply the division operator on values of a column in the form of a constant base relation called DIVIDE holding tuples where dividend/divisor=quotient. I'll use the simplest algebra, with headings that are sets of attribute names. Assume we have input relation R with column c & average A. We want the relation like R but with each column c value set to its original value divided by A.
This version starts from the simplest specification expression & mechanically converts to algebra:
/* rows where
EXISTS dividend [R(dividend, A) & DIVIDE(dividend, A, c)]
*/
PROJECT c, A (
RENAME c\dividend (R)
NATURAL JOIN
RENAME divisor\A quotient\c (DIVIDE))
This version has a less concise specification expression mechanically derived from concise algebra:
/* rows where
EXISTS quotient [
quotient = c
& THERE EXISTS c [
R(c, A) & DIVIDE(c, A, quotient)]
*/
RENAME quotient\c
PROJECT quotient, A (
R NATURAL JOIN RENAME dividend\c divisor\A (DIVIDE))