operations in relational algebra

Basic and additional operations in relational algebra of relational model in dbms, basic relational algebra operations and equivalent SQL queries, relational algebra select, project, cartesian product, difference, union, intersection, natural join, theta join and equijoin

Operations in Relational Algebra (Click on the links to expand)

Operation and Type	Functionality	Syntax
SELECT (σ) (Click above) Unary	Selects all tuples that satisfy the selection condition from a relation R.	σ <selection condition> (R) Example 1: RA: σage>15 (student) SQL: SELECT * FROM student WHERE age>15; Example 2: RA: σage>15^phone=2020 (student) SQL: SELECT * FROM student WHERE age>15 and phone = 2020;
PROJECT (Π) (Click above) Unary	Produces a new relation with only some of the attributes of R, and removes duplicate tuples.	Π <attribute list> (R) Example: RA: Π regno, name, dob (student) SQL: SELECT regno, name, dob FROM student;
We can combine different relational operations as one.		Example: RA: Π regno, name, dob (σage>15 (student)) SQL: SELECT regno, name, dob FROM student WHERE age>15;
CARTESIAN PRODUCT (X) (Click above) Binary	Produces a relation that has the attributes of R1 and R2 and includes as tuples all possible combinations of tuples from R1 and R2.	R1 X R2 Example 1: RA: σage>15 (student X sub_regd) SQL: SELECT * FROM student, sub_regd; Example 2: RA: Π regno, name, dob (student X sub_regd) SQL: SELECT regno, name, dob FROM student, sub_regd;
THETA JOIN (Click above) Binary	Produces all combinations of tuples from R1 and R2 that satisfy the join condition. This join condition involves attributes from both relations such as follows; R1.a θ R2.b	R1 ⋈ <join condition> R2 It is equivalent to; σ <join condition> (R1 X R2) Example 1: RA: σstudent.rno ≠ sub_regd.rno (student X sub_regd) SQL: SELECT * FROM student, sub_regd WHERE student.rno <> sub_regd.rno; Example 2: RA: σ UGStudent.age > PGStudent.age (UGStudent X PGStudent) SQL: SELECT * FROM UGStudent, PGStudent WHERE UGStudent.age > PGStudent.age;
EQUI-JOIN (Click above) Binary	Produces all the combinations of tuples from R1 and R2 that satisfy a join condition with only equality comparisons.	R1 ⋈ <join condition> R2 Example: RA: σstudent.rno = sub_regd.rno (student X sub_regd) SQL: SELECT * FROM student, sub_regd WHERE student.rno = sub_regd.rno;
NATURAL JOIN (⋈) (Click above) Binary	Same as EQUIJOIN except that the join attributes of R2 are not included in the resulting relation; if the join attributes have the same names, they do not have to be specified at all.	R1 ⋈ R2 Example: RA: (student ⋈ sub_regd) SQL: SELECT * FROM student, sub_regd WHERE student.rno = sub_regd.rno; Here, rno in student is the primary key and rno in sub_regd is the foreign key.
UNION (U) (Click above) Binary	Produces a relation that includes all the tuples in R1 or R2 or both R1 and R2; R1 and R2 must be union compatible.	R1 U R2 Example: RA: Π regno (UGStudent) U Π regno (PGStudent) SQL: (SELECT regno FROM UGStudent) UNION (SELECT regno FROM PGStudent) Result: Combines all register numbers of UGStudent and PGStudnet into one result.
INTERSECTION (∩) (Click above) Binary	Produces a relation that includes all the tuples in both R1 and R2; R1 and R2 must be union compatible.	R1 ∩ R2 Example: RA: Π regno (UGStudent) ∩ Π regno (PGStudent) SQL: (SELECT regno FROM UGStudent) INTERSECT (SELECT regno FROM PGStudent) Result: Shows all register numbers that are common in UGStudent and PGStudnet as result.
DIFFERENCE (-) (Click above) Binary	Produces a relation that includes all the tuples in R1 that are not in R2; R1 and R2 must be union compatible.	R1 – R2 Example: RA: Π regno (UGStudent) - Π regno (PGStudent) SQL: (SELECT regno FROM UGStudent) EXCEPT (SELECT regno FROM PGStudent) Result: Shows all register numbers that are in UGStudent but not in PGStudnet as result.
DIVISON Binary	Produces a relation R(X) that includes all tuples t[X] in R1(Z) that appear in R1 in combination with every tuple from R2(Y), where Z = X ∪ Y.	R1 ÷ R2 Example: RA: Π stuname (Π stuname, coursename (course_taken) ÷ Π coursename (course_required)) SQL: SELECT x.stu from taken x  WHERE NOT EXISTS (SELECT * FROM required y WHERE NOT EXISTS (SELECT * FROM taken z WHERE z.stu=x.stu AND z.course=y.course)); Result: Names of students who have registered all the required courses.

*********************