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: Π 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.
|
*********************
No comments:
Post a Comment