Unit 4 - Relational Algebra Flashcards
What are the inputs and outputs of a query?
Both the inputs and outputs of a query are relations
Operators are either unary or binary. What’s the diff?
Unary accepts ONE relation as input; binary accepts TWO relations as input.
What are the unary operators?
Selection, projection, rename
What does selection Operator (sigma) return?
Returns a subset of tuples (rows) that satisfies some condition(s)
It’s like FILTERING a spreadsheet
What is the order for selection operator?
sigma <attribute> <comparison> <constant> (Relation R)</constant></comparison></attribute>
comparison operator can be
>, <, =, >=, <=, !=
can also compare two attributes
Can you have more than one selection condition when using selection operator?
Yes. Can link using boolean operators: AND, OR, NOT
e.g., GPA <= 3.6 AND Age = 20
Sigma <C2> ( Sigma <C1> ( R ) ) What's the order of execution?</C1></C2>
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>
What does projection operator (Pi) return?
Only the selected columns (fields/attributes)
Pi <Attribute 1, Attribute 4,..> (R)
What does renaming operator (rho) return?
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>
What must be true of the inputs for binary operators?
Input relations MUST BE COMPATIBLE:
1. SAME NUMBER of attributes
2. same attribute NAMES and DATA TYPES
R Union S returns what?
A relation containing ALL tuples that occur in R and ALL tuples that occur in S
(duplicate tuples are removed)
R Intersection S returns what?
A relation containing only the tuples in BOTH R and S
R Difference S returns what?
A relation containing all tuples in R but not in S.
If a relation is R is also in S, it is removed.
What does Cross-Product (Cartesian Product) do? Relation S X Relation S = ?
A relation where all fields of R are followed by all fields of S
Forms all possible combos of R X S
What does JOIN operator do?
Joins a group of tables
Establishes connections between data records in different tables
What are the two types of JOIN operator?
- Natural join
- Theta (or Equi) Join
What does natural join do? R natural join S = ?
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
What does Theta/Equi join do?
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.
When would you use Theta/Equi join?
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>
What are the four types of join within Natural join and Theta(Equi) join?
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.
Division operation. R divided by S returns what?
(S must be a subset of R)
Gives us records in R that are associated with all records in S (doesn’t include records of S in result)
If you want to use union, intersection, and difference, what MUST you have?
SAME number of attributes, same names, and same datatypes!
