Relational Databases Relational Algebra 2 Set operators renaming

Relational Databases Relational Algebra (2) Set operators, renaming, notation Jennifer Widom

Relational Algebra (2) Relational algebra query (expression) on set of relations produces relation as a result College(c. Name, state, enrollment) Student(s. ID, s. Name, GPA, size. HS) Apply(s. ID, c. Name, major, decision) College c. Name state Student enr s. ID s. Name GPA Apply HS s. ID c. Name major dec Jennifer Widom

Relational Algebra (2) Union operator List of college and student names College c. Name state Student enr s. ID s. Name GPA Apply HS s. ID c. Name major dec Jennifer Widom

Relational Algebra (2) Difference operator IDs of andstudents names of students who didn’t apply anywhere College c. Name state Student enr s. ID s. Name GPA Apply HS s. ID c. Name major dec Jennifer Widom

Relational Algebra (2) Intersection operator Names that are both a college name and a student name College c. Name state Student enr s. ID s. Name GPA Apply HS s. ID c. Name major dec Jennifer Widom

Relational Algebra (2) Intersection doesn’t add expressive power (1) College c. Name state Student enr s. ID s. Name GPA Apply HS s. ID c. Name major dec Jennifer Widom

Relational Algebra (2) Intersection doesn’t add expressive power (2) College c. Name state Student enr s. ID s. Name GPA Apply HS s. ID c. Name major dec Jennifer Widom

Relational Algebra (2) Rename operator 1. 2. 3. College c. Name state Student enr s. ID s. Name GPA Apply HS s. ID c. Name major dec Jennifer Widom

Relational Algebra (2) Rename operator To unify schemas for set operators List of college and student names College c. Name state Student enr s. ID s. Name GPA Apply HS s. ID c. Name major dec Jennifer Widom

Relational Algebra (2) Rename operator For disambiguation in “self-joins” Pairs of colleges in same state College c. Name state Student enr s. ID s. Name GPA Apply HS s. ID c. Name major dec Jennifer Widom

Relational Algebra (2) Alternate notation (1) Assignment statements – Pairs of colleges in same state College c. Name state Student enr s. ID s. Name GPA Apply HS s. ID c. Name major dec Jennifer Widom

Relational Algebra (2) Alternate notation (2) Expression tree – GPAs of students applying to CS in CA College c. Name state Student enr s. ID s. Name GPA Apply HS s. ID c. Name major dec Jennifer Widom

Relational Algebra summary Relational Algebra (2) Jennifer Widom