Chapter 2 - Relational Algebra Flashcards
What is relational algebra?
It is a procedural language used for performing operations on relational DB.
It serves as the foundational, low-level operations that are used by Database Management Systems (DBMSs) to manipulate data
Relational Algebra vs SQL?
Relational Algebra is procedural, meaning it specifies how operations should be carried out step by step. Users define a series of operations to be performed on data.
SQL, on the other hand, is declarative. In SQL, users specify what data they want to retrieve or manipulate and the DBMS determines how to execute the request.
Relational algebra is basically more low level and SQL more high level
What does it mean when we say relational algebra forms the basis for the implementation of DBMSs.
It provides the fundamental operations that a DBMS needs to perform tasks like querying, inserting, updating and deleting data.
Click to see procedural and declarative difference
Procedure language
Specify what to get and how
to get
E.g.
do while(isspace(s)) s++;
while(d++ = *s++)
Declarative language
Specify what to get
E.g.
out_str = re.sub (r’^\s+’, ‘ ‘,
input_string
What can relational algebra operators be divided into? At a higher level.
It can be divided into basic operators and extended/derived operators
What can basic operators be divided into?
Unary operators and Binary operators
What is a unary operator
Unary operators are operators that operate on a SINGLE OPERAND.
These operators are often used for tasks such as NEGATION, INCREMENTING, DECREMENTING or CHANGING SIGN OF A SINGLE VALUE.
Example Unary operators in programming language include the negation operator -x, the logical NOT operator !x and the increment operator x++
What is a binary operator?
Binary operators are operators that operate on two operands. They require two values or variables to perform an operation.
They are commonly used for arithmetic operations (addition, subtraction, multiplication, division), logical comparisons (equal to, not equal to, greater than, less than) and other operations that involve two values.
Examples of binary operators include addition (x+y), equality comparison (x==y), and logical AND (x && y).
What falls under Unary operators out of the Fundamental Operators?
- Projection (π)
- Selection operator (σ)
- Rename operator (p)
How is the select operator represented?
The Select operator in relational algebra is represented as 𝜎𝑃(𝑟), where:
𝑟 represents one relation (table).
𝑃 represents one or many attributes of relation 𝑟, often referred to as predicates.
What is P
P stands for Predicate.
They are conditions or expressions that can involve logical operators like ‘and’ (∧), ‘or’ (∨), and ‘not’ (¬).
Comparison operators like >=, >, =, !=, <, and <= can also be used in predicates.
Click to see how a select statement is written in relational algebra
𝜎(Gender = ‘Female’ ∧ Age > 20)(Students)
In this predicate:
“Gender = ‘Female’” is a condition that checks if the student’s gender is female.
“Age > 20” is a condition that checks if the student’s age is greater than 20.
The ‘∧’ (logical AND) operator is used to combine these conditions, meaning both conditions must be true for a row to be selected.
How is the project operator represented? and how does this operator work?
Notation: π𝑎,𝑏,…(𝑟)
r represents one relation.
a,b represents the attributes (columns) that you want to select from the relation.
Project operator is a column-wise operation that returns only specified columns.
Is it possible during project operation to end up with return of fewer tuples?
It is possible for the Project operator to return fewer tuples (rows) than the original relation r. This happens when there are duplicate values in the selected columns.
Duplicate values are removed in the result, resulting in a reduced number of tuples
What would the project operator look like in SQL?
SELECT a, b
FROM r
What would the selection operator look like in SQL?
SELECT *
FROM r
WHERE P
Click to see how a project statement is written in relational algebra
π(FirstName, LastName,Department)(Employees)
Click to see example of partial relation input
π_{Location}(𝜎_{YOB>280}(GOT)).
What does the set union operator do? How is it represented?
The Set Union operator, represented as r u s, is used to combine (union) the tuples of the two relations, denoted as r and s.
It retrieves all the tuples from both relations but ELIMINATES DUPLICATE TUPLES
What is the key characteristics of set union?
The key characteristic is that duplicate tuples are not included in the result.
What is the condition for Set Union to be valid?
The condition is that relations r and s must have the same relation schema.
The schemas should match in terms of attribute names, types and order.
In Set Union, what happens when the schema of the relations are not the same?
We cut part of the columns (PROJECT).
What is set difference
Let’s say r - s. We will deduct whatever is in relation s IN THE RELATION r.
is r - s and s - r the same thing in set difference?
NO
How does rename work?
Initial relation r:
Change relation r to relation x
𝜌𝑥 𝑟 , Relation x
Change relation and attributes to relation y with attributes c d e
𝜌𝑦(𝐶,𝐷,𝐸) 𝑟 , Relation y
Why do we require additional operators?
What are these additional operators?
This is because fundamental operators are able to query, but the expression of the query is considered quite complex. With additional operators, it can help to simplify things.
The additional operators are:
1) Join operators
2) Divide operators
3) Intersection
What is the notation for set intersection?
What is the condition for set intersection?
What is the formula for set intersection?
Notation: s ∩ t
Condition: Relation schemas must be compatible
Formula: (s ∪ t) – (s – t) – (t – s) OR s - (s - t)
How is natural join represented?
s ⋈ t for relation s and relation t
It contains all the attributes of both tables, but only one copy of common column.
It only takes the stuff that matches too
How is natural join different from outer join?
Natural join requires the perfect match. If you retain some imperfect matches, then that would be outer join.
What happens since outer join keeps the imperfect matches?
We represent the missing values with NULL.
What is left outer join and how is it represented?
Left outer join: r ⟕ s.
For tuples in left relation r, if not match, keep as NULLs.
For tuples in right relation s. If not match perfectly, delete.
What is full outer join and how is it represented?
R ⟗ S.
Keep both that don’t match. KEEP EVERYTHING
What is right outer join and how is it represented?
Right outer join: r ⟖ s.
For tuples in left relation r, if not match, delete
For tuples in right relation s. If not match perfectly, keep as NULL
How does division work in relational algebra?
Basically u1/u2 and we only keep the things in U1 that totally contains U2.
(Refer to slides for info)
How does assignment work in relational algebra?
Given new tuple(s) E and relation s:
* Suppose the constraint can be met, i.e., same relation schema.
* E potentially can be quite complicated, i.e., a long operation expression.
* Insert: s ← s ∪ E.
* Specify actual items, also feasible.
* Delete: s ← s – E.
* Delete Casterly Rock.
* s ← s – E, where
* E = 𝜎_{Loc=‘Casterly Rock’}(s).
* Or, can also be written as:
* s ← 𝜎_{Loc≠‘Casterly Rock’}(s).
* Update: s ← Π(s)
* s ← Π_{ID+5, Name, Location, YOB}(s).Specify the schema clearly.
What constitutes good performance with regards to DB?
Making applications run faster
Lowering response time of queries/transactions
Improving overall transaction throughout
