Relational Algebra Flashcards
databases and relations
σ age > 30 (Employees)
Selection – Filters rows where the condition holds true. Returns all employees older than 30.
π name, age (Employees)
Projection – Selects specific columns (name and age) from the relation, removing duplicates.
Employees ∪ Managers
Union – Combines all unique tuples from both relations. Relations must have the same schema.
Employees ∩ Managers
Intersection – Returns tuples that exist in both relations. Requires union-compatible relations.
Employees − Retired
Set Difference – Returns tuples in Employees that are not in Retired.
Employees × Departments
Cartesian Product – Combines every row in Employees with every row in Departments.
Employees ⨝ dept_id = id (Departments)
Theta Join – Joins relations where the condition (here, dept_id = id) is true.
Employees ⨝ dept_id = id (Departments)
Equijoin – A theta join where the condition is equality.
Employees ⨝ Departments
Natural Join – Joins using all common attribute names, removing duplicate columns.
ρ E (Employees)
Rename – Renames a relation or its attributes. Here, Employees is renamed to E.
Projects ÷ RequiredSkills
Division – Returns projects that require all the skills listed in RequiredSkills.
Employees ⟕ Departments
Left Outer Join – Returns all rows from Employees, and the matched rows from Departments. Unmatched rows in Departments result in NULLs.
Employees ⟖ Departments
Right Outer Join – Returns all rows from Departments, and the matched rows from Employees. Unmatched rows in Employees result in NULLs.
Employees ⟗ Departments
Full Outer Join – Returns all rows when there is a match in either Employees or Departments. Unmatched rows result in NULLs on the side lacking a match.
Employees ⋉ Departments
Semijoin (Left) – Returns rows from Employees that have at least one matching row in Departments, based on the join condition, but does not include columns from Departments.
Employees ⋊ Departments
Semijoin (Right) – Returns rows from Departments that have at least one matching row in Employees, based on the join condition, but does not include columns from Employees.
Employees ▷ Departments
Antijoin (Left) – Returns rows from Employees that do not have a matching row in Departments, based on the join condition.
Departments ◁ Employees
Antijoin (Right) – Returns rows from Departments that do not have a matching row in Employees, based on the join condition.
Employees ⋈ (σ dept_id = 10 (Departments))
Join with Selection – Joins Employees with Departments where dept_id is 10, combining join and selection operations.
π name (σ age < 25 (Students))
Selection and Projection – Selects students younger than 25 and projects only their names.
γ dept_id; avg(salary) → avg_salary (Employees)
Aggregation – Groups Employees by dept_id and calculates the average salary for each department.
τ age (Employees)
Sorting (Order By) – Sorts the Employees relation by the age attribute in ascending order.
