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) & DIVIDE(dividend, A, c)]
*/
PROJECT c (
        RENAME c\dividend (R)
    NATURAL JOIN
        RENAME quotient\c (
            PROJECT dividend, quotient (SELECT divisor=A (DIVIDE))))

This version has a less concise specification expression mechanically derived from more concise algebra:

/* rows where
EXISTS quotient [
        quotient=c
    &   THERE EXISTS c, divisor [
            R(c) & DIVIDE(c, divisor, quotient) & divisor=A]]
*/
RENAME quotient\c (
    PROJECT quotient (
        R NATURAL JOIN RENAME dividend\c (SELECT divisor=A (DIVIDE))))

See also Relational algebra - recode column values.