Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

C175 Data Management Foundations > 3.9 Relational Algebra > Flashcards

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q
  1. What operation does the symbol σ represent in relational algebra?
  2. What is the operation written as?
A

Select

Greek Letter: sigma

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q
  1. What operation does the symbol π represent in relational algebra?
  2. What is the operation written as?
A

Project

Greek Letter: Pi

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q
  1. What operation does the symbol × represent in relational algebra?
  2. What is the operation written as?
A

Product

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q
  1. What operation does the symbol ⨝ represent in relational algebra?
  2. What is the operation written as?
A

Join

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q
  1. What operation does the symbol ∪ represent in relational algebra?
  2. What is the operation written as?
A

Union

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q
  1. What operation does the symbol ∩ represent in relational algebra?
  2. What is the operation written as?
A

Intersect

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q
  1. What operation does the symbol − represent in relational algebra?
  2. What is the operation written as?
A

Difference

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q
  1. What operation does the symbol ρ represent in relational algebra?
  2. What is the operation written as?
A

Rename

Greek Letter: rho

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q
  1. What operation does the symbol γ represent in relational algebra?
  2. What si the operation written as?
A

Aggregate

Greek Letter: gamma

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Fill in the blank: The symbol σ corresponds to the operation _______ in relational algebra.

A

Select

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Fill in the blank: The symbol π corresponds to the operation _______ in relational algebra.

A

Project

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Fill in the blank: The symbol ρ corresponds to the operation _______ in relational algebra.

A

Rename

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Fill in the blank: The symbol γ corresponds to the operation _______ in relational algebra.

A

Aggregate

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is the relational algebra operation equivalent to SQL PROJECT?

A

SELECT Column1, Column2

This operation selects specific columns from a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Set operations in MYSQL

What operations are part of the SQL standard?

A
  1. The UNION, INTERSECT, and MINUS keywords are part of the SQL standard.
  2. MySQL supports UNION but not INTERSECT and MINUS.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Set operations in MYSQL

A ∩ B

What is this implememented as in SQL

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Set operations in MYSQL

A - B

What is this implememented as in SQL

A 
SELECT A1, A2 
FROM  A 
LEFT JOIN B 
ON (A1 = B1 AND A2 = B2)
WHERE B1 IS NULL;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What are equivalent expressions in relational algebra?

A

Relational algebra expressions that operate on the same tables and yield the same result.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What is the primary role of a query optimizer?

A

To convert an SQL query into a sequence of lower-level database actions known as a query execution plan.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

List the steps involved in the query optimization process.

Convert, generate, est, determine, convert

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

What does cost estimation in query optimization refer to?

A

A numeric estimate of the processing time required, combining storage media access and computation time.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

The order of operations does not change the _______ of relational algebra expressions.

A

[result]

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

How does changing the order of operations alter the result of an expression.

A

The order of operations does not change the result of relational algebra expressions.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

How should you plan to execute each SQL query?

A

There can be multiple execution plans for a single SQL query.

34
Q

How do Query optimizers typically choose an optimal expression?

A
  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
Q

Which operation is 1 & which is 2?

A
  1. select
  2. project
36
Q
  1. What is the select statement committing?
  2. What is the result?
A
  1. The select operation selects employees who earn more than $50,000 in salary.
37
Q
  1. What is the project statement committing?
  2. What is the result?
A
38
Q
  1. What is the select statement committing?
  2. What is the result?
A
39
Q
  1. What is the product statement committing?
  2. What is the result?
A
  1. The product operation generates the combination of all rows from both tables.
40
Q

How would Maria Rodriguez be in the union result?

The union operation combines all rows of two compatible tables into a single table

A

Maria Rodriguez is both an employee and a student, and thus appears twice in result

Duplicate rows are excluded from the result table.

41
Q

How many rows are in the table defined by the following expression?

A
  • 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
Q

What does the operation A−B represent?

A

The difference between two sets, retrieving all rows from table A that are not present in table B.

retrieving is the result, the leftovers after the substraction

43
Q

What SQL syntax is used to achieve the set difference A−B?

A 
SELECT A1, A2 
FROM A 
LEFT JOIN B ON (A1 = B1 AND A2 = B2) 
WHERE B1 IS NULL;
44
Q

What does a LEFT JOIN do in SQL?

A

Joins all rows from table A with matching rows from table B, selecting rows from A even if there is no match.

45
Q

What condition is used to filter results in the set difference query?

A

WHERE B1 IS NULL

46
Q

What does the condition WHERE B1 IS NULL indicate?

A

It includes only those rows from A that do not have corresponding entries in B.

47
Q

Why is the LEFT JOIN technique necessary in some SQL databases?

A

Because they do not support the MINUS or EXCEPT operators directly.

48
Q

Using except

What is an alternative syntax for achieving the set difference if supported?

A
49
Q

Would you consider The LEFT JOIN technique as a simpler option than using the MINUS or EXCEPT operators?

A
  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
Q

What SQL statement is used to write the intersection using aliases A and B?

A

This statement returns all rows present in both table A and table B.

51
Q

What can be used instead of the INTERSECT statement if the SQL database does not support it?

A

INNER JOIN

The INNER JOIN can achieve the same result as the intersection operation.

52
Q

How would you rewrite an INNER JOIN to find the intersection of tables A and B?

A

Replace column1 and column2 with the appropriate column names for matching.

53
Q

The SQL statement SELECT * FROM A \_\_\_\_\_\_ SELECT * FROM B; is used to return rows present in both tables.

A

INTERSECT

Note The UNION operator, is used to combine the results of two queries and return all distinct rows from both.

54
Q

Why would the INNER JOIN yield similar results to an INTERSECT statement.

A

Both approaches yield the same effect in terms of returning intersecting rows.

55
Q

What does the operation result in?

A
  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
Q

How can changing the order of operations can alter the result of an expression.

A
  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
Q

How can a execution plan possibly have more than one SQL query.

A
  1. Query optimizers generate multiple execution plans, estimate the running time for each, and choose the fastest plan.
  2. Most queries can be executed in many different ways.
58
Q

How does a Query optimizer typically choose optimal expressions?

A

Most query optimizers use more precise measures, such as bytes read from storage media or CPU processing time.

59
Q

Why wouldn’t the join operation be considered a low-level database action.

A
  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
Q

What is Relational Algebra?

A

A formal language that serves as the foundation for languages like SQL.

61
Q

What do queries in Relational Algebra operate on?

A

Relations.

62
Q

Name three types of relations used in the context of relational algebra.

A
  • College Relation
  • Student Relation
  • Application Relation
63
Q

What fields are contained in the Student Relation?

A
  • Student ID
  • Name
  • GPA
  • High school size
64
Q

What are the unique attributes in the relations discussed?

A
  • College name
  • Student ID
  • Application major
65
Q

What is the purpose of the Select Operator (σ)?

A

To filter rows based on conditions.

66
Q

Provide an expression.

Students with GPA>3.7

A

σ(GPA > 3.7)(Student)

67
Q

Provide an expression.

Students with GPA>3.7 and HS<1000

A

σ(GPA > 3.7) AND (HS<1000)

in SQL, the logical ‘AND’ operator is represented by the word AND rather than a symbol (^).

68
Q

Provide an expression.

Applications to Stanford CS major

A

σ(cName= ‘Stanford’) AND (major= ‘cs’)

69
Q

What does the Project Operator (π) do?

A

Selects certain columns from a relation.

70
Q

Provide an Expression

ID and decisions of all applications

A
71
Q

Provide an Expression

ID and name of students with GPA>3.7

A
72
Q

How can Select and Project Operators be combined?

A

Operators can be composed.

73
Q

Provide an expression

List of application majors and decisions

How does relational algebra handle duplicates?

A

It eliminates duplicates in query results. Learn more

74
Q

Provide an expression

Names and GPAs of students with HS>1000 who applied to CS and were rejected

what is a Cross-Product:

A

Combines two relations. Video

75
Q

Provide an expression

Names and GPAs of students with HS>1000 who applied to CS and were rejected

what is a Natural Join:

A

You dont have to write the condition. Video

76
Q

What does the Natural Join (∞) operator do?

A

Performs a cross-product while enforcing equality on all attributes with the same name.

77
Q

What is the Theta Join (θ)?

A

Applies a condition to the result of the cross-product of two relations.

78
Q

List the primary operators of Relational Algebra.

A
  • Select
  • Project
  • Cross-Product
  • Natural Join
  • Theta Join
79
Q

Fill in the blank: The __________ operator is used for selecting columns.

A

Project

80
Q

What is the effect of a natural join on duplicate columns?

A

Removes duplicate columns

81
Q

What is a natural join equivalent to?

A

Performing a cross-product followed by a selection enforcing equality on common attributes

C175 Data Management Foundations (29 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions