3.9 Relational Algebra Flashcards
1
Q
What is the equivalent of a relational algebra expression?
A
An SQL query defining a single result table.
2
Q
- What operation does the symbol
σrepresent in relational algebra? - What is the operation written as?
A
Select
Greek Letter: sigma
3
Q
- What operation does the symbol
πrepresent in relational algebra? - What is the operation written as?
A
Project
Greek Letter: Pi
4
Q
- What operation does the symbol
×represent in relational algebra? - What is the operation written as?
A
Product
5
Q
- What operation does the symbol
⨝represent in relational algebra? - What is the operation written as?
A
Join
6
Q
- What operation does the symbol
∪represent in relational algebra? - What is the operation written as?
A
Union
7
Q
- What operation does the symbol
∩represent in relational algebra? - What is the operation written as?
A
Intersect
8
Q
- What operation does the symbol
−represent in relational algebra? - What is the operation written as?
A
Difference
9
Q
- What operation does the symbol
ρrepresent in relational algebra? - What is the operation written as?
A
Rename
Greek Letter: rho
10
Q
- What operation does the symbol
γrepresent in relational algebra? - What si the operation written as?
A
Aggregate
Greek Letter: gamma
11
Q
Fill in the blank: The symbol σ corresponds to the operation _______ in relational algebra.
A
Select
12
Q
Fill in the blank: The symbol π corresponds to the operation _______ in relational algebra.
A
Project
13
Q
Fill in the blank: The symbol ρ corresponds to the operation _______ in relational algebra.
A
Rename
14
Q
Fill in the blank: The symbol γ corresponds to the operation _______ in relational algebra.
A
Aggregate
15
Q
What is the operation equivalent to SQL SELECT?
A
SELECT * FROM Table WHERE expression
This operation retrieves rows from a table that meet a specified condition.
16
Q
What is the relational algebra operation equivalent to SQL PROJECT?
A
SELECT Column1, Column2
This operation selects specific columns from a table.
17
Q
What is the relational algebra operation equivalent to SQL PRODUCT?
A
SELECT * FROM Table1 CROSS JOIN Table2
This operation combines all rows from Table1 with all rows from Table2.
18
Q
What is the relational algebra operation equivalent to SQL JOIN?
A
SELECT * FROM Table1 INNER JOIN Table2 ON expression
This operation combines rows from two tables based on a related column.
19
Q
What is the relational algebra operation equivalent to SQL UNION?
A
SELECT * FROM Table1 UNION SELECT * FROM Table2
This operation combines the results of two queries, removing duplicates.
20
Q
What is the relational algebra operation equivalent to SQL INTERSECT?
A
SELECT * FROM Table1 INTERSECT SELECT * FROM Table2
This operation retrieves rows that are common to both tables.
21
Q
What is the relational algebra operation equivalent to SQL DIFFERENCE?
A
SELECT * FROM Table1 MINUS SELECT * FROM Table2
This operation retrieves rows from Table1 that are not in Table2.
22
Q
What is the relational algebra operation equivalent to SQL RENAME?
A
No direct SQL syntax; typically done by aliasing
This operation allows renaming tables and columns for clarity.
23
Q
What is the relational algebra operation equivalent to SQL AGGREGATE?
A
SELECT GroupColumn, Function(Column) FROM Table GROUP BY GroupColumn
aggregate functions is typically expressed using the Group By operation along with an Aggregation operation
24
Q
Set operations in MYSQL
What operations are part of the SQL standard?
A
- The
UNION,INTERSECT, andMINUSkeywords are part of the SQL standard. - MySQL supports
UNIONbut notINTERSECTandMINUS.
25
# Set operations in MYSQL
`A ∩ B`
What is this implememented as in SQL
26
# Set operations in MYSQL
`A - B`
What is this implememented as in SQL
```
SELECT A1, A2
FROM A
LEFT JOIN B
ON (A1 = B1 AND A2 = B2)
WHERE B1 IS NULL;
```
27
What are equivalent expressions in relational algebra?
Relational algebra expressions that operate on the **same tables** and yield the **same result**.
28
What is the primary role of a query optimizer?
To convert an SQL query _into_ a *sequence of lower-level database actions* known as a query execution plan.
## Footnote
[📖](https://share.evernote.com/note/2cd2feba-116d-56cf-845f-f027219bfc8a)
29
List the steps involved in the query optimization process.
## Footnote
Convert, generate, est, determine, convert
* Convert SQL query into a relational algebra expression
* Generate equivalent expressions
* Estimate the cost associated with each operation
* Determine the optimal expression with the lowest total cost
* Convert the optimal expression into a query execution plan
30
What does cost estimation in query optimization refer to?
A numeric estimate of the *processing time required*, combining storage media access and computation time.
31
The order of operations does not change the _______ of relational algebra expressions.
[result]
32
How does changing the order of operations alter the result of an expression.
The order of operations does not change the result of *relational algebra expressions*.
33
How should you plan to execute each SQL query?
There can be multiple execution plans for a single SQL query.
34
How do Query optimizers typically choose an optimal expression?
1. They consider various factors, including the estimated **cost** of operations, the **efficiency** of accessing data, and overall query execution **time**.
2. Query optimizers do not solely rely on the total number of rows processed.
35
Which operation is 1 & which is 2?
1. select
2. project
36
1. What is the select statement committing?
2. What is the result?
1. The select operation selects employees who earn more than $50,000 in salary.
37
1. What is the project statement committing?
2. What is the result?
38
1. What is the select statement committing?
2. What is the result?
39
1. What is the product statement committing?
2. What is the result?
1. The product operation generates *the combination of all* rows from both tables.
40
How would Maria Rodriguez be in the union result?
## Footnote
The union operation combines all rows of two compatible tables into a single table
Maria Rodriguez is both an employee and a student, and thus appears twice in result
## Footnote
Duplicate rows are excluded from the result table.
41
How many rows are in the table defined by the following expression?
* If the values were the same but the column names were different, the intersection would still be valid
* The `INTERSECT` operation focuses on matching values rather than column names.
Result `0`
42
What does the operation A−B represent?
The difference between two sets, retrieving all rows from table `A` *that are _not_ present in table* `B`.
## Footnote
retrieving is the result, the leftovers after the substraction
43
What SQL syntax is used to achieve the set difference A−B?
```SELECT A1, A2
FROM A
LEFT JOIN B ON (A1 = B1 AND A2 = B2)
WHERE B1 IS NULL;```
44
What does a LEFT JOIN do in SQL?
Joins all rows from table A with matching rows from table B, selecting rows from A even if there is no match.
45
What condition is used to filter results in the set difference query?
WHERE B1 IS NULL
46
What does the condition` WHERE B1 IS NULL` indicate?
It includes only those rows from `A` that *do not have corresponding entries* in `B`.
47
Why is the `LEFT JOIN` technique necessary in some SQL databases?
Because they do not support the `MINUS` or `EXCEPT` operators directly.
48
# Using except
What is an alternative syntax for achieving *the set difference* if supported?
49
Would you consider `The LEFT JOIN` technique as a simpler option than using the `MINUS` or `EXCEPT` operators?
1. `LEFT JOIN` requires more steps to achieve the same result, as it involves joining tables and then filtering results based on NULL values.
2. `MINUS` or `EXCEPT` directly expresses the *intention to find the difference between two sets*, making the query clearer and more concise.
50
What SQL statement is used to write the intersection using aliases A and B?
## Footnote
This statement returns all rows present in both table A and table B.
51
What can be used instead of the INTERSECT statement if the SQL database does not support it?
INNER JOIN
## Footnote
The INNER JOIN can achieve the same result as the intersection operation.
52
How would you rewrite an `INNER JOIN` to find the _intersection_ of tables `A` and `B`?
## Footnote
Replace column1 and column2 with the appropriate column names for matching.
53
This SQL statement does what?
```
SELECT * FROM A
INTERSECT
SELECT * FROM B;
```
- `INTERSECT` compares two queries and returns only the rows that are in both **queries**.
- It's useful for finding overlapping data between tables or queries
54
Why would the `INNER JOIN` yield similar results to an `INTERSECT` statement.
Both approaches yield the same effect in terms of returning intersecting rows.
55
What does the operation result in?
1. No `Student` rows are identical, in all three columns, to any `Employee` rows.
2. Therefore the difference operation does not remove any of the three `Employee` rows.
56
Which particular change within the order of operations; could alter the result of an expression.
1. Changing the order of other operations, such as *select and aggregate*, can alter the result.
2. The order of some operations, such as select and project, can be reversed without affecting the result.
57
How can a execution plan possibly have more than one SQL query.
1. Query optimizers generate multiple execution plans, simultaneously estimating the running time for each, after which choosing the fastest plan.
2. Most queries can be executed in many different ways.
58
How does a Query optimizer typically choose optimal expressions?
Most query optimizers use more precise measures; such as bytes read from storage media or CPU processing time.
59
Why wouldn't the `JOIN` operation be considered a low-level database action.
1. The join operation is complex and can be executed in many different ways.
2. Query optimizers *decompose join operations into other operations*, such as product and select, prior to generating a query execution plan.
60
What is Relational Algebra?
A formal language that serves as the foundation for languages like SQL.
61
What do queries in Relational Algebra operate on?
Relations.
62
Name three types of relations used in the context of relational algebra.
* College Relation
* Student Relation
* Application Relation
63
What fields are contained in the Student Relation?
* Student ID
* Name
* GPA
* High school size
64
What are the unique attributes in the relations discussed?
* College name
* Student ID
* Application major
65
What is the purpose of the Select Operator (σ)?
To filter rows based on conditions.
66
# Provide an expression.
Students with GPA>3.7
σ(GPA > 3.7)(Student)
67
# Provide an expression.
Students with GPA>3.7 and HS<1000
σ(GPA > 3.7) AND (HS < 1000) (Student)
## Footnote
in SQL, the logical 'AND' operator is represented by the word AND rather than a symbol (`^`).
68
# Provide an expression.
Applications to Stanford CS major
σ(cName= 'Stanford') AND (major= 'cs')
69
What does the Project Operator (π) do?
Selects certain columns from a relation.
70
# Provide an Expression
ID and decisions of all applications
71
# Provide an Expression
ID and name of students with GPA>3.7
72
How can Select and Project Operators be combined?
Operators can be composed.
73
# Provide an expression
List of application majors and decisions
## Footnote
How does relational algebra handle duplicates?
## Footnote
It eliminates duplicates in query results. [Learn more](https://share.evernote.com/note/e604451b-1a7d-fe39-adba-654bc7550ab8)
74
# Provide an expression
Names and GPAs of students with HS>1000 who applied to CS and were rejected
## Footnote
what is a Cross-Product:
## Footnote
Combines two relations. [Video](https://www.youtube.com/embed/tii7xcFilOA?start=570&end=655&autoplay=1)
75
# Provide an expression
Names and GPAs of students with HS>1000 who applied to CS and were rejected
## Footnote
what is a Natural Join:
## Footnote
You dont have to write the condition. [Video](https://www.youtube.com/embed/tii7xcFilOA?start=740&end=890&autoplay=1)
76
What does the Natural Join (∞) operator do?
Performs a cross-product while enforcing equality on all attributes with the same name.
77
What is the Theta Join (θ)?
Applies a condition to the result of the cross-product of two relations.
78
List the primary operators of Relational Algebra.
* Select
* Project
* Cross-Product
* Natural Join
* Theta Join
79
Fill in the blank: The __________ operator is used for selecting columns.
Project
80
What is the effect of a natural join on duplicate columns?
Removes duplicate columns
81
What is a natural join equivalent to?
Performing a cross-product followed by a selection enforcing equality on common attributes
