Relational Algebra Operations (ch.8)

Question 1

Define the SELECT operation.

Answer 1

• unary operation to select a subset of tuples/rows from a relation R that satisfy the selection condition
• like a filter or restriction that keeps only tuples/rows satisfying a qualifying condition

select [condition] (expression)

• where condition involves one or more comparisons of attributes or constants with other attributes or constants

Question 2

Define the PROJECT operation.

Answer 2

• unary operation to produce a new relation with only some of the attributes of R (and removes duplicate tuples/rows)
• selects certain columns from the table; discards the others
• used to project the relation over certain attributes only

project [attribute-list] (expression)

• where attribute-list is a list of attributes, each present in “expression”

Question 3

Define the RENAME operation.

Answer 3

• unary operator to rename attribute names of a relation

rename [attribute-list] (expression)

• # of attributes in “attribute-list” must be equal to the # of attributes of the relation corresponding to “expression”

Question 4

Define the UNION operation.

Answer 4

• binary operation, produces a relation that includes all tuples that are either in relation A or in relation B, or in both A and B. (duplicate tuples removed)

(expression 1 union expression 2)

• the schemas of relations in each expression must be compatible (same # attributes, same data types; attribute names may differ)

Question 5

Define the INTERSECTION operation.

Answer 5

• binary operation, produces a relation that includes all tuples in both relation A and relation B

(expression1 intersect expression2)

• schemas of the relations in each expression must be compatible (same # attributes and same data types; attribute names may be different)

Question 6

Define the MINUS (SET DIFFERENCE) operation.

Answer 6

• binary operation, produces relation that includes all tuples that are in relation A but not in relation B

(expression1 minus expression2)

• schemas of relations in each expression must be compatible (same # attributes and same data types, attribute names may be different)

Question 7

Define the TIMES (CARTESIAN PRODUCT) operation.

Answer 7

• binary operation, produces a relation Q with the combined attributes of relation A and relation B, and includes as tuples all possible combinations of tuples from A and B; resulting relation Q has one tuple for each combination of tuples from A and B
• if A has x tuples, and B has y tuples, then Q has x * y tuples
• most useful when used with SELECT operations that match values of attributes coming from the component relations

(expression1 times expression2)

• no restriction on schemas of two expressions

Question 8

Define the JOIN operation.

Answer 8

• binary operation, producing relation Q that has attributes of relation A and relation B, that includes as tuples only those combinations that satisfy the join condition
• similar to CARTESIAN PRODUCT, except that instead of all possible combinations of tuples from A and B, it restricts tuples to combinations that satisfying the join condition

(expression1 join expression2)

• no restriction on schemas of relations in expressions

Question 9

Define the DIVISION operation.

Answer 9

• binary operation, producing a relation R(X) that includes all tuples t[X] in R₁(Z) that appear in R₁ in combination with every tuple from R₂(Y), where Z = X union Y

Example 1: “Retrieve the names of employees who work on all the projects that ‘John Smith’ works on.”

Example 2: T <– R ÷ S