Please visit, subscribe and share 10 Minutes Lectures in Computer Science

## 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 (Click above) Unary Selects all tuples that satisfy the selection condition from a relation R. σ (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; (Click above) Unary Produces a new relation with only some of the attributes of R, and removes duplicate tuples. Π (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; (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; (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 ⋈ R2 It is equivalent to; σ (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; (Click above) Binary Produces all the combinations of tuples from R1 and R2 that satisfy a join condition with only equality comparisons. R1 ⋈ R2 Example: RA: σstudent.rno = sub_regd.rno (student X sub_regd) SQL: SELECT * FROM student, sub_regd WHERE student.rno = sub_regd.rno; (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. (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. (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. (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.

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