Relational Algebra - Column Values

Question

Suppose I have a “animals” table whose rows represent different animals, and there is a kind of columns that can have values ​​like “cat”, “dog”, “horse”, “cow”, etc. Suppose I’m only interested in whether the animal is a dog or not. In sql (at least in MySQL) I can make a request, for example select (species = 'dog'), as isDog from animals, to return 1 for dogs and 0 otherwise. How can I express this in RA? He does not select, because we do not limit the lines. Is it possible to use a project operator, although my expression (species = 'dog') is not an attribute as such? Or how can I handle this?

EDIT: I want to achieve what will be achieved using a project statement in a column that does not exist, but rather is based on the truth value of the statement. For example, animals with tables containing rows with one "variety" of a column, having rows: cat, dog, horse, cow. I need a boolean value that could be renamed to 'isDog', which will result in the values ​​0,1,0,0 (1 = true, 0 = false). I get this information in MySQL by selecting (species = 'dog') as isDog, and I'm wondering if the RA really uses a project statement with (species = 'dog') to select such a dynamically created column or is there another way to deal with by this?

Answer 1

TL DR To enter specific values ​​into an expression in relational algebra, you must have a way to write table literals. Usually, the necessary operators do not become explicit, but, on the other hand, some notation, for example, meaning, is often used in algebra exercises.

For your case, using the simplest additional relationships (with a conditional table literal similar to SQL VALUES):

(restrict SPECIES=dog Animals) natural join TABLE{ISDOG}{<1>} union (restrict SPECIES<>dog Animals) natural join TABLE{ISDOG}{<0>} 

If you need a reference to relational algebra with a general computing operator, see Chris Date EXTEND and his classic tutorial. Introduction to Database Systems, 8th Edition.

---

(More generally: to output a table with rows with new columns with values ​​that are arbitrary value functions in the input rows, we must have access to the tables corresponding to the operators. Either we use a table literal, or suppose that a Boolean function and use its name as the name of the table.)

But it turns out that the relational model was designed so that:

• Each operator of the algebra corresponds to a certain logical operator. NATURAL JOIN and AND
RESTRICT theta and AND theta
UNION and OR
MINUS and AND NOT
PROJECT all but C and EXISTS C
etc

• Each nested algebra expression corresponds to a specific nested logical expression. Animals &
  "animal named NAME is AGE years old ... and is of species SPECIES"
restrict SPECIES=dog Animals & "animal named NAME is AGE years old ... and is of species SPECIES" AND SPECIES=dog

• A sentence is a statement. A predicate is a statement template. If each base table contains rows that make a true sentence from a predicate parameterized by its columns, then the query contains rows that make a true sentence from a corresponding predicate parameterized by its columns.

   -- table of rows where animal named NAME is AGE years old ... and is of species SPECIES Animals -- table of rows where animal named NAME is AGE years old ... and is of species SPECIES AND if SPECIES=dog then ISDOG=1 ELSE ISDOG=0 -- ie rows where animal named NAME is AGE years old ... and is of species SPECIES AND (SPECIES=dog AND ISDOG=1 OR SPECIES<>DOG AND ISDOG=0) -- ie rows where animal named NAME is AGE years old ... and is of species SPECIES AND SPECIES=dog AND ISDOG=1 OR animal named NAME is AGE years old ... and is of species SPECIES AND SPECIES<>dog AND ISDOG=0 (restrict SPECIES=dog Animals) natural join TABLE{ISDOG}{<1>} union (restrict SPECIES<>dog Animals) natural join TABLE{ISDOG}{<0>} 

• Thus, you can simply use logic, exact engineering language (including software), science (including computer) and mathematics to describe the result tables.

   -- table of rows where animal named NAME is AGE years old ... and is of species SPECIES AND ISDOG=(if SPECIES=dog then 1 else 0) 

• Thus, you can use table expressions and / or logical expressions in specifications, depending on what is more clear, based on (sub) expressions under ().

    Animals natural join table of rows where if SPECIES=dog THEN ISDOG=1 ELSE ISDOG=0 

  (The table corresponding to this IF expression has a row for each row, and the row "dog" is the only one with 1.)

  (Nb SQL ON and WHERE have this table form on the left and a predicate with functions on the right.)

• An algebra expression computes strings that satisfy its corresponding logical expression.

It may not be obvious to you which equivalent relational expressions correspond to equivalent logical expressions and vice versa. But all that matters is that your customers understand the algebra and / or logic in the specification, and your programmers can write an equivalent SQL expression.

Understanding the semantics of relational algebra and SQL, see this answer and its references .

PS SQL SELECT does what a projection does, but also does other things that are not projections that are performed in algebra by renaming, joining, and / or table literals. What you want is not a projection. It is called EXTEND by Chris Date. I would advise anyone to use / reference to date algebra. Although adding RESTRICT / WHERE and EXTEND to arbitrary logical expressions (wffs and terms) raises the question of how to relate algebraically to logical expressions. This answer explains that / how you can always algebraically express logical expressions given by literal and / or operator tables.