Relational Algebra rule for column transformation

Question

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).

Answer 1

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))

See also Relational algebra - recode column values.