Unit 4 - Relational Algebra

Question 1

What are the inputs and outputs of a query?

Answer 1

Both the inputs and outputs of a query are relations

Question 2

Operators are either unary or binary. What’s the diff?

Answer 2

Unary accepts ONE relation as input; binary accepts TWO relations as input.

Question 3

What are the unary operators?

Answer 3

Selection, projection, rename

Question 4

What does selection Operator (sigma) return?

Answer 4

Returns a subset of tuples (rows) that satisfies some condition(s)
It’s like FILTERING a spreadsheet

Question 5

What is the order for selection operator?

Answer 5

sigma <attribute> <comparison> <constant> (Relation R)</constant></comparison></attribute>

comparison operator can be
>, <, =, >=, <=, !=

can also compare two attributes

Question 6

Can you have more than one selection condition when using selection operator?

Answer 6

Yes. Can link using boolean operators: AND, OR, NOT
e.g., GPA <= 3.6 AND Age = 20

Question 7

Sigma <C2> ( Sigma <C1> ( R ) ) What's the order of execution?</C1></C2>

Answer 7

From the inside out
Select tuples from R that match C1, then on that result filter for C2

(here, you get same result either way)
(which is same as, sigma <C1>)</C1>

Question 8

What does projection operator (Pi) return?

Answer 8

Only the selected columns (fields/attributes)
Pi <Attribute 1, Attribute 4,..> (R)

Question 9

What does renaming operator (rho) return?

Answer 9

Allows us to rename,
1. the output relation (from other operators)
2. certain attributes in the output relation

can also make a copy of a relation and rename that
rho <S> (R)
becomes just (S) which is a copy of (R)</S>

Question 10

What must be true of the inputs for binary operators?

Answer 10

Input relations MUST BE COMPATIBLE:
1. SAME NUMBER of attributes
2. same attribute NAMES and DATA TYPES

Question 11

R Union S returns what?

Answer 11

A relation containing ALL tuples that occur in R and ALL tuples that occur in S
(duplicate tuples are removed)

Question 12

R Intersection S returns what?

Answer 12

A relation containing only the tuples in BOTH R and S

Question 13

R Difference S returns what?

Answer 13

A relation containing all tuples in R but not in S.
If a relation is R is also in S, it is removed.

Question 14

What does Cross-Product (Cartesian Product) do? Relation S X Relation S = ?

Answer 14

A relation where all fields of R are followed by all fields of S
Forms all possible combos of R X S

Question 15

What does JOIN operator do?

Answer 15

Joins a group of tables
Establishes connections between data records in different tables

Question 16

What are the two types of JOIN operator?

Answer 16

1. Natural join
2. Theta (or Equi) Join

Question 17

What does natural join do? R natural join S = ?

Answer 17

First, R X S (must have 1+ common attribute(s))
Assume attribute C is the common attribute.
ONLY keep values where R.C = S.C
Remove the duplicate attribute (R.C and S.C just become C again)
ALL other attributes are added

Question 18

What does Theta/Equi join do?

Answer 18

Joins two relations/tables based on conditions or relationships between attributes

E.g., R < theta join > (R.Ai = S.Bj) (S)
is the same as saying, do R X S, then filter for all values where R.Ai = S.Bj.

Question 19

When would you use Theta/Equi join?

Answer 19

When you have some condition that must be met.

E.g., if you DON’T have the same name attribute, but values are the same, you can say,
Prof <theta> (Pid = id) (Lectures)
In Prof table, it's "Pid" but in Lecture table, it's "id" (but same values) so you can just make them equal in a theta join</theta>

Question 20

What are the four types of join within Natural join and Theta(Equi) join?

Answer 20

Inner, Left-join, Right-join, Outer

Inner - get records where join condition met, ONLY.
Left - get everything from the left, and only the records that meet the join condition on the right. (‘NULL’ if A doesn’t have match)
Right - opposite ^
Outer - get all records from left and right. ‘NULL’ if no match from the other side.

Question 21

Division operation. R divided by S returns what?
(S must be a subset of R)

Answer 21

Gives us records in R that are associated with all records in S (doesn’t include records of S in result)

Question 22

If you want to use union, intersection, and difference, what MUST you have?

Answer 22

SAME number of attributes, same names, and same datatypes!