Is multiplication allowed in relational algebra?

Question

I have a relation R

cid    sid  gradepoint  credits
-------------------------------
CS425  001  4.0         3
...

I need to calculate the GPA as gradepoint * credits. How do I express this with a relational algebra expression?

My best guess is

π cid, sid, gradepoint * credits (R)

but I'm not sure if I can multiply attributes with anything other than a constant.

Sulfate

Depends how pure your algebra is. E.g. from Wikipedia: "There is nothing in relational algebra introduced so far that would allow computations on the data domains (other than evaluation of propositional expressions involving equality). For example, it is not possible using only the algebra introduced so far to write an expression that would multiply the numbers from two columns.." – Stylography

What is a reference to the "relational algebra" you are using? There are many. PS Please use text for text, don't use images/links--use code format for tables; use words for symbols; or google 'unicode symbol'. – Wowser

Answer 1

Relational algebra doesn't address domain-specific operations. It neither includes nor excludes it, just like real algebra neither includes nor excludes operations on relations.

If you allow multiplication by constants, you're already combining algebras (which is pretty much required for any practical application) so I see no reason to disallow multiplication between attributes.

Kibitka

Answer 2

Notice that if expressions like you are using are allowed then it is projection that is doing the multiplying. Instead of its inputs being a relation value & attribute names, its inputs are a relation value and expressions of some sort that include names of operators whose inputs are values of attribute types. Your projection is parsing and multiplying. So it is a different operator than one that only accepts attribute names.

The projection that takes attribute expressions begs the question of its implementation given an algebra with projection only on a relation value and attribute names. This is important in an academic setting because a question may be wanting you to actually figure out how to do that, or because the difficulty of a question is dependent on the operators available. So find out what algebra you are supposed to use.

We can introduce an operator on attribute values when we only have basic relation operators taking attribute names and relation values. Each such operator can be associated with a relation value that has an attribute for each operand and an attribute for the result. The relation holds the tuples where the result value is equal to the the result of the operator called on the operand values. (The result is functionally dependent on the operands.)

So suppose we have the following table value Times holding tuples where left * right = result:

left  right  result
-------------------
 0     0      0
 1     0      0
 ...
 0     1      0
 1     1      1
 2     1      2
 ...

You have

/* tuples where
course [cid]'s student [sid] earned grade [gradepoint] and credits [credits]
*/ 
R

/* tuples where
[credits] * [gradepoint] = [result]
*/ 
T

If your calculated attribute is result then you want

/* tuples where
FOR SOME credits & gradepoint,
        course [cid]'s student [sid] earned grade [gradepoint] and credits [credits]
    AND [credits] * [gradepoint] = [result]
*/ 
project cid, sid, result (
    R natural join (rename left\credits right\gradepoint (Times))
    )

Relational algebra - recode column values

PS Re algebra vs language: What is a reference to the "relational algebra" you are using? There are many. They even have different notions of what a "relation" is. Some so-called "algebras" are really languages because the expressions don't only represent the results of operators being called on values. Although it is possible for an algebra to have operand values that represent expressions and/or relation values that contain names for themselves.

PS Re separation of concerns: You haven't said what the attribute name of the multiplication result is. If you're expecting it to be credit * gradepoint then you're also expecting projection to map expression-valued inputs to attribute names. Except you are expecting credit * gradepoint to be recognized as an expression with two attribute names & and an operator name in one place but to be just one attribute name in another. There are solutions for these things in language design, eg in SQL optional quotes specialized for attribute names. But maybe you can see why simple things like an algebra operating on just attribute names & relation values with unique, unordered attribute names helps us understand in terms of self-contained chunks.

Wowser