Binary Operations in Relational Algebra & SQL

Binary Operationsin Relational Algebra & SQL

Set Based: UNION, INTERSECTION, DIFFERENCE

UNION operation Example A RESULT=STUDENT INSTRUCTOR B Suppose names of people are distinct SQL for previous example Fig 6.4: (SELECT Fn, Ln FROM STUDENT) UNION (SELECT Fname, Lname FROM INSTRUCTOR);

Union Compatibility • Requirement for the traditional set operators • Strong requirement • Same number of columns • Each corresponding column is compatible • Positional correspondence

INTERSECTION operation Example Suppose names of people are distinct A B RESULT=STUDENT INSTRUCTOR SQL for previous example Fig 6.4: (SELECT Fn, Ln FROM STUDENT) INTERSECT (SELECT Fname, Lname FROM INSTRUCTOR);

SET DIFFERENCE operation Example STUDENT - INSTRUCTOR INSTRUCTOR - STUDENT Suppose names of people are distinct (d) RESULT=INSTRUCTOR - STUDENT B (e) RESULT=STUDENT - INSTRUCTOR A SQL for previous example Fig 6.4: (SELECT Fn, Ln FROM STUDENT) MINUS (SELECT Fname, Lname FROM INSTRUCTOR); B

CARTESIAN PRODUCT operation Example B Relational Algebra: RESULT= Faculty ✕ Student SQL: SELECT * FROM Faculty, Student;

EQUI-Join EQUI-Join EQUI-Join Example: • RESULT= Faculty (Faculty.FacSSM=Offering.FacSSN)Offering; • SELECT * FROM Faculty, Offering WHEREFaculty.FacSSN=Offering.FacSSN;

Exercise 1 for Equi-Join • SQL query Result • T1 (T1.P=T2.A)T2 • T1 (T1.Q=T2.B)T2

NATURAL-Join Example: • RESULT= Faculty * Offering; • SELECT * FROM EMPLOYEE NATURAL JOIN DEPARTMENT

THETA Join Example: • RESULT=Car {CarPrice>BoatPrice} Boat; • Result=R1 {Condition} R2; Condition: {<, >, =, ≤, ≥, ≠}; • EquiJoin when “=“. • SELECT * FROM Car, Boat WHERECarPrice>BoatPrice;

THETA Join EQUI-Join Theta-Join Example: • SELECT * FROM Faculty, Offering WHEREFaculty.FacSSM=Offering.FacSSN;

Exercise 2 Department Student Faculty • Write Relational Algebra and SQL queries for following questions: • What are the names of students who are from department ‘Computer Science’? • What are the names of faculties who are younger than a student? • What are the names of faculties who works in ‘Keller Hall’?

Summary • Binary Operation • Operation from Set Theory • UNION • INTERSECTION • DIFFERENCE • CARTESIAN PRODUCT • Join Operation • Equi-Join • Natural Join • Theta Join

Reference Materials in the slides are from Elmasri, Navathe, Fundamentals of Database Systems, 6th, Addison Wesley and Michael V. Mannino, Database: Design, Application Development & Administration, Third Edition, McGraw Hill

Binary Operations in Relational Algebra & SQL