This title slide introduces the topic "Set Operations and Renaming in Relational Algebra." It includes the subtitle with instructor "Your Name" and date "October 10, 2024."

Set Operations and Renaming

in Relational Algebra

Instructor: Your Name | Date: October 10, 2024

Source: University-level course presentation

Relational algebra is a procedural query language for relational databases and the theoretical foundation for SQL. It features key set operations like union (∪), intersection (∩), and difference (−), plus the renaming operator (ρ) for relations and attributes.

Introduction to Relational Algebra

• Procedural query language for relational databases
• Theoretical foundation for SQL
• Key set operations: Union (∪), Intersection (∩), Difference (−)
• Renaming operator (ρ) for relations and attributes

The slide defines the union operation (∪) as including all tuples from relations R or S (or both), requiring identical schemas. It exemplifies this with Students ∪ Faculty equaling all people on campus, both sharing the schema {ID, Name, Dept}.

Union (∪)

| R ∪ S includes all tuples that are in R or S (or both). Relations must have compatible (identical) schemas. | Students ∪ Faculty = All people on campus. R(Students): {ID, Name, Dept} S(Faculty): {ID, Name, Dept} (same schema). |

Source: Set Operations in Relational Algebra

The slide "Intersection (∩)" defines it as the set of tuples common to both relations R and S, requiring compatible schemas with matching attributes and types. An example is Enrolled ∩ Eligible, representing students enrolled in a course who also meet honors eligibility criteria.

Intersection (∩)

The slide defines the relational difference operation R − S as returning tuples in R absent from S, requiring compatible schemas and noting it is not commutative. It exemplifies this with Applicants − Admitted yielding rejected applicants, e.g., 80 tuples from 100 applicants minus 20 admitted.

Difference (−)

Source: Relational Algebra

The Renaming Operator (ρ) uses syntax ρnewName(R) for relations and ρnewAttr(R) for attributes to ensure compatibility and disambiguate names. Examples include ρStudent(S) for renaming a relation and ρSID(ID), Name(Sname)(Students) for attributes.

Renaming Operator (ρ)

• Syntax for relations: ρnewName(R)
• Syntax for attributes: ρnewAttr(R)
• Purpose: ensures compatibility and disambiguates names
• Example (relation): ρStudent(S)
• Example (attributes): ρSID(ID), Name(Sname)(Students)

The slide contrasts an invalid direct union of Students and Faculty relations, which fails due to differing schemas. It shows a valid approach using renaming (ρDept) to align schemas before union, followed by selection (σ) for CS or Math departments.

Combining Operators Example

| Students ∪ Faculty Error: Relations must have identical schemas for union. Different names or attributes prevent set operation. | σ{Dept='CS' ∨ 'Math'}(ρDept(Students) ∪ ρDept(Faculty)) Combines students and faculty in CS/Math after renaming for compatibility. |

This slide outlines SQL equivalents for relational algebra operations. Union (∪) uses UNION, Intersection (∩) uses INTERSECT, Difference (−) uses EXCEPT, and Renaming (ρ) uses AS aliases in the SELECT clause.

SQL Equivalents

• Union (∪): SELECT FROM R UNION SELECT FROM S;
• Intersection (∩): SELECT FROM R INTERSECT SELECT FROM S;
• Difference (−): SELECT FROM R EXCEPT SELECT FROM S;
• Renaming (ρ): AS aliases in SELECT clause.

This table compares relational algebra operations (Union, Intersect, Difference, Rename) with their symbols (∪, ∩, −, ρ), SQL equivalents (UNION, INTERSECT, EXCEPT, AS), and requirements. Union, Intersect, and Difference need compatible schemas, while Rename has none.

Summary: Comparison Table

{ "headers": [ "Operation", "Symbol", "SQL", "Requirements" ], "rows": [ [ "Union", "∪", "UNION", "Compat. schemas" ], [ "Intersect", "∩", "INTERSECT", "Compat. schemas" ], [ "Difference", "−", "EXCEPT", "Compat. schemas" ], [ "Rename", "ρ", "AS", "N/A" ] ] }

This slide lists practice questions on relational algebra and SQL, including computing (R ∪ S) − T, renaming 'Age' to 'Years' in Employee using ρ syntax, and writing SQL for Students ∩ Faculty where Dept='CS'. It ends by encouraging discussion of solutions in class.

Practice Questions

• Compute (R ∪ S) − T and describe result.
• Write ρ syntax to rename 'Age' to 'Years' in Employee.
• Write SQL for Students ∩ Faculty where Dept='CS'.
• Discuss solutions in class!