Relational algebra

Question 1

Name 2 unary operations in relational algebra

Answer 1

• Projection
• Selection

Question 2

Explain the unary operation projection

Answer 2

Symbol: pi
Operation: selects attributes removing duplicate tuples
Example: pi attribute1, attribute2, … attributeN (tableName)
SQL equivalent: SELECT attribute1, attribute2, …, attributeN FROM (tableName)

Question 3

Explain unary operation selection

Answer 3

Symbol: σ
Operation: Selects tuples by predicate
Example: σ predicate (TableName)
SQL equivalent: WHERE predicate (FROM tableName)

Question 4

Name and describe the 5 binary operations in relational algebra

Answer 4

Cartesian product
Union
Intersection
Difference
Join

Question 5

Explain the binary operation cartestian product

Answer 5

Symbol: x
Operation: Shows a concatenation of all tuples in two relations
Example: A x B
SQL equivalent: CROSS join

Question 6

Explain the binary operation union

Answer 6

Symbol: U
Operation: Gets all the tuples in either one or both relations
Example: Relation U Relation
SQL equivalent: UNION

Question 7

Explain the binary operation intersection

Answer 7

Symbol: ⋂
Operation: Gets all the tuples present in BOTH relations
Example: Relation ⋂ Relation
SQL equivalent: INTERSECT

Question 8

Explain the binary operation difference

Answer 8

Symbol: -
Operation: Gets all the tuples in one set but NOT in another
Example: Relation - Relation
SQL equivalent: EXCEPT/MINUS

Question 9

Explain the binary operation join?

Answer 9

Symbol: ⋈
Operation: Joins two relations over all common attributes x
Example: Relation ⋈ Relation
SQL equivalent: INNER JOIN

Question 10

What does it mean if two sets are union compatible?

Answer 10

• There is the same number of attributes
• Attributes have the same domain

Question 11

What is the set equivalence to A ⋂ B?

Answer 11

A - (A - B)

Question 12

What is the difference between relational algebra and SQL?

Answer 12

Relational algebra is the formal language
- Theoretical foundations of SQL
SQL is the ‘real language’
- Actually use it in databases

Question 13

What are the fundamental relational algebra operations?

Answer 13

Selection
Projection
Cartesian product
Union
Set difference

Question 14

What are NOT the fundamental relational algebra operations?

Answer 14

Join
Intersection

Question 15

What makes an operation fundamental?

Answer 15

If it cannot be made up of anything other than itself

Question 16

What happens with duplicate tuples in relational algebra?

Answer 16

They are eliminated