Relational Algebra

Selection

A unary operation uses a condition to select rows from a relation and create a new relation. σc(R) Example: σtitle = 's_rep'(Employee) SQL: SELECT * FROM Employee WHERE title = 's_rep';

Projection

A unary operations that select columns from a relation (ultimately returning rows containing just the attributes selected). πA1, A2, A3...An(R) EX: πlast_name, title, loc_num(Employee) SQL: SELECT last_name, title, loc_num FROM Employee;

Union

a binary operation that combines all rows into a single relation while eliminating any duplicates. R ∪ S Example: πemp_name, title(Employee1) ∪ πemp_name, title(Employee2) SQL: SELECT * FROM Employee1 UNION SELECT * FROM Employee2;

Natural Join

a binary operation that combines two relations over their common attributes, eliminating one occurrence of each common attribute. R >< S EX: (πc_id,last_name,address,city,state,zip (Customer)) >< (πc_id,ord_no (Orders)) SQL: SELECT Customer.c_id, last_name, address, city, state, zip, Orders.ord_no FROM Customer, Orders WHERE Customer.c_id = Orders.c_id;

Intersection

a binary operation that creates a new relation containing all the rows that are in both the first and the second relations. R ∩ S SQL: SELECT Employee1.*, Employee2.* FROM Employee1, Employee2 WHERE Employee1.e1_id = Employee2.e2_id;

Difference

a binary operation that creates a relation including the rows that are in the first relation, but not in the second. R - S πemp_name, title(Employee1) - πemp_name, title(Employee2) SELECT * FROM Employee1 WHERE e1_id NOT IN (SELECT e2_id FROM Employee2);

Cartesian Product

a binary operation that multiplies two relations to create a new relation containing every possible pair of rows from the two original relations. R X S Example: Employee X Customer SQL SELECT Employee.*, Customer.* FROM Employee, Customer;

Left Outer Join

a binary operation, rows from the first relation that do not have matching values in the shared column of the second relation are included in the result relation. SQL: SELECT Customer.*, Orders.* FROM Customer LEFT JOIN Orders ON Customer.c_id = Orders.c_id;

Restricted Cartesian product

binary multiplies two relations to create a new relation using a WHERE clause added to the SQL statement that would apply a search condition to each row, and only the rows that meet the search condition would be retained for the resulting relation. σEmployee.e_id = Customer.e_id (Employee X Customer) SQL: SELECT Employee.*, Customer.* FROM Employee, Customer WHERE Employee.e_id = Customer.e_id;