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).
There's no standard approach to this. Also there's no single relational algebra, so you should give a reference to yours.
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))