Tuesday, 15 August 2017

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
(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;
(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;
(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 <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;
(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;
(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: Π stunamestuname, 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.

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







No comments:

Post a Comment

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...