Lecture 11 - Relational Algebra

Question 1

Query

Answer 1

• A query in relational algebra is an expression composed of various operators that extract or manipulate data from one or more relations.
• The result of a query is always a relation: a query (expression) on a set of relations produces a relation as a result

Question 2

Simplest Query

Answer 2

The simplest query is a retrieval of all data from a single relation (table) without any filtering or transformation. It’s equivalent to a SELECT *operation in SQL.

Question 3

Select Operator (σ)

Answer 3

• The Select operator (σ) is used to filter rows in a relation based on a given condition.
  Example:
• (σGPA > 3.7(Student) selects all students with a GPA greater than 3.7.
• SQL Equivalent:
  SELECT * FROM Student WHERE GPA > 3.7;

Question 4

Project Operator (π)

Answer 4

• The Project operator (π) selects specific columns from a relation, effectively reducing the number of attributes.
• Example: (πname, GPA(Student)) projects the Name and GPA columns from the Student relation.
• SQL Equivalent:
  
  • SELECT Name, GPA FROM Student;

Question 5

Duplicates

Answer 5

Relational Algebra inherently operates on sets, which do not allow duplicates. The output of relational algebra operations is a set, meaning it is automatically duplicate-free.

Question 6

Cross Product (x)

Answer 6

• The Cross Product (×) combines every row of one relation with every row of another. It’s also known as the Cartesian product.
• Example: (Student x Course) pairs each student with every course.
• SQL Equivalent:
  
  • SELECT * FROM Student, Course; (without a WHERE clause).

Question 7

Natural Join (⨝)

Answer 7

• The Natural Join (⨝) combines two relations by matching rows based on common attributes (columns with the same name).
• Example: (Student ⨝ Apply) joins Student and Apply where the common attributes (e.g., Student.ID and Apply.ID) match.
• SQL Equivalent:
  
  • SELECT * FROM Student
  • NATURAL JOIN Apply;

Question 8

Union Operator (∪)

Answer 8

• The Union operator (∪) combines the results of two relations, returning all distinct tuples (rows) present in either relation.
• Example: (R ∪ S) returns all unique rows from relations R and S.
• SQL Equivalent:
  
  • SELECT * FROM R
  • UNION
  • SELECT * FROM S;

Question 9

Difference Operator (-)

Answer 9

• The Difference operator (−) returns all tuples that are in one relation but not in the other.
• Example: (R - S) returns rows in R that are not in S.
• SQL Equivalent:
  
  • SELECT * FROM R
  • EXCEPT
  • SELECT * FROM S;

Question 10

Intersection Operator (∩)

Answer 10

• The Intersection operator (∩) returns only the tuples that are present in both relations.
• Example: (R ∩ S) returns rows common to both R and S.
• SQL Equivalent:
  
  • SELECT * FROM R
  • INTERSECT
  • SELECT * FROM S;

Question 11

Rename Operator (ρ)

Answer 11

• The Rename operator (ρ) changes the name of a relation or its attributes.
• Example: (ρs(name)(Student)) renames the Student relation to S and its Name attribute to StudentName.
• SQL Equivalent:
  
  • SELECT Name AS StudentName FROM Student;