The Relational Algebra CONTENTS 1 Relational Algebra Operations

The Relational Algebra

CONTENTS 1 Relational Algebra Operations From Set Theory * * UNION INTERSECTION MINUS CARTESIAN OPERATION 2 Binary Relational Operations *JOIN *DIVISION

Relational Algebra Operations From Set Theory UNION Operation The result of this operation, denoted by R S, is a relation that includes all tuples that are either in R or in S or in both R and S. Duplicate tuples are eliminated. Union operation is a binary which means that it always operates on two relations. The notation for the union of relations(table) R &S is R S Is Q We have two relations R&S , then the union of R &S as shown in fig.

R ID NAME 501 AMIT 503 ANAND 504 SONAL 507 RAJNI 510 KAPIL 512 RANJIT S ID NAME R S 503 ANAND 504 SONAL ID NAME 506 MADHU 501 AMIT 510 KAPIL 503 ANAND 504 SONAL 506 MADHU 507 RAJNI 510 KAPIL 512 RANJIT

Properties of Union Operation 1. The input relations must be union compatible. 2. Commutativity -It means that result of R S is same as that of S R. 3. Associativity- It means that P ( Q S)= (P Q) S Where P, Q and S are relations. BACK TO INDEX

INTERSECTION OPERATION The result of this operation, denoted by R S, is a relation that includes all tuples that are in both R and S. The two relations used in intersection operation must be “union compatible’’ The intersection operation is a binary operation. The notation for the Intersection of relations(table) R &S is R S Is Q We have two relations R&S , then the intersection of R &S as shown in fig.

R ID NAME 501 AMIT 503 ANAND 504 SONAL 507 RAJNI 510 KAPIL 512 RANJIT S ID NAME 503 ANAND 504 SONAL 506 MADHU 510 KAPIL Q=R S ID NAME 503 ANAND 504 SONAL 510 KAPIL

Properties of Intersection Operation 1. The input relations must be union compatible. 2. Commutativity -It means that result of R S is same as that of S R. 3. Associativity- It means that P ( Q S)= (P Q) S Where P, Q and S are relations. BACK TO INDEX

Set Difference (or MINUS) Operation The result of this operation, denoted by R - S, is a relation that includes all tuples that are in R but not in S. The two relations used in difference operation must be “union compatible’’ The Difference operation is a binary operation, which means that it always operates on two relations The notation for the Difference of relations(table) R &S is R - S Is Q

R ID NAME 501 AMIT 503 ANAND 504 SONAL 507 RAJNI 510 KAPIL 512 RANJIT S ID NAME 503 ANAND 504 SONAL 506 MADHU 510 KAPIL Q=R - S ID NAME 501 AMIT 507 RAJNI 512 RANJIT

Properties of Difference Operation 1. The input relations must be union compatible. 2. They are not Commutative-It means that result of R - S is same as that of S - R. 3. They are not Associative- It means that P - ( Q - S) ≠ (P - Q) - S Where P, Q and S are relations. BACK TO INDEX

CARTESIAN (or cross product) Operation The Cartesian Product of two relations result in new relation that include concatenation of every tuple of first relation with every tuple of second relation. In other words , the new relation is created consisting of all possible combinations of the tuple. It is also known as the Cross Product or Cross Join. The Cartesian operation is a binary operation. Which means that it always operates on two relations. The notation for the Cartesian Product of relations(table) R &S is RXS Is Q

R ID NAME 501 AMIT 503 ANAND 504 SONAL Q=RX S S ID NAME 503 ANAND 506 MADHU R. ID R. NAME S. ID S. NAME 501 AMIT 503 ANAND 501 AMIT 506 MADHU 503 ANAND 506 MADHU 504 SONAL 503 ANAND 504 SONAL 506 MADHU

Properties of Cartesian Product Operation 1. The relations on which Cartesian product operation is applied need not necessary to be union compatible. 2. Commutativity-It means that result of R X S is same as that of S X R. 3. Associativity- It means that P X ( Q XS) = (P X Q) XS Where P, Q and S are relations. 4. The resultant relation(table) may hold duplicate attributes if some attributes of two relations are defined on common domains. 5. The Degree of the resultant operation is equal to the sum of the degree of all the relations i. e. Degree of R= Degree of P + Degree of Q 6. The total number of rows in the resultant operation is equal to the product of the number of rows of the first and the second relation i. e. Total number of Tuples of R = Total number of Tuples of P X Total number of Tuples of Q. BACK TO INDEX

Binary Relational Operations JOIN Operation ◦ The sequence of Cartesian product followed by select is used quite commonly to identify and select related tuples from two relations, a special operation, called JOIN. It is denoted by ∞ ◦ This operation is very important for any relational database with more than a single relation, because it allows us to process relationships among relations. ◦ The general form of a join operation on two relations R(A 1, A 2, . . . , An) and S(B 1, B 2, . . . , Bm) is: R∞<join condition>S where R and S can be any relations that result from general relational algebra expressions. Consider the example of EMP & DEPT table

EMP ID ENAME SALARY DEPT ID 101 ANU 12000 01 103 PRIYA 15000 01 106 ANKUR 13000 02 DEPT ID DNAME 01 FINANCE 01 MARKETING 02 PACKING EMP ID ENAME SALARY DEPT ID DNAME 101 ANU 12000 01 01 FINANCE 103 PRIYA 15000 01 01 MARKETING 106 ANKUR 13000 02 02 PACKING EMP∞dept_id=dept_no DEPT

In the above example, the DEPT ID attribute of EMP table is matched with the DEPTNO attribute of DEPT table and if both are equal then the resultant tuples from both the tables are fetched as a single row The common attribute in both the tables should not have the same name so as to preserve the uniqueness of the column names in the resultant relation. If they have the same name then they should be renamed to preserve uniqueness. There is another join known as Natural Join in which there is no need of explicitly naming the columns. The join is Performed by joining all those col from the first table to any col in the second table with the same name The result of Natural Join in the above example is as shown

EMP ID ENAME SALARY DEPT ID DNAME 101 ANU 12000 01 FINANCE 103 PRIYA 15000 01 MARKETING 106 ANKUR 13000 02 PACKING Natural Join on EMP & DEPT table The natural Join is also referred as Inner Join

The Another type of join in which relation is joined to it self by comparing the values with a col of a single relation is known as Self Join EMP ID ENAME MANG ID 101 REKHA ------- 103 KAPIL 101 104 AMAR 106 ATUL ------- 107 PUNEET 1 -1 THE RESULTANT RELATION ENAME KAPIL REKHA AMAR ATUL PUNEET REKHA THE RESULT OF SELF JOIN

Properties of Join Operation 1. Commutativity-It means that result of R ⋈ S is same as that of S ⋈ R. 2. Associativity- Using this the same rows appear in the final resu. It regardless of the order in which relations are joined. P ⋈ ( Q ⋈ S) = (P ⋈ Q) ⋈ S Where P, Q and S are relations. 3. We can combine a selection with a cross product to form a join. R ⋈ S= (R X S) 4. We can commute a selection with a join. If the selection condition only involves attributes of one of the arguments to the join. (R ⋈ S )= (R) ⋈ S. The attributes must appear only in R and not in S. 5. Rows whose join attributes are null, don’t appear in the resultants relation(table). 6. More than two tables can also be joined but it increases the complexity. 7. Joins are most commonly used when there exists a relationship between tables i. e. where a join condition is based on a Primary key and Foreign key columns. BACK TO INDEX

DIVISION Operation The division operation results in a new relation such that every tuple appearing in the resultant relation must exist in the dividend relation in combination with the every tuple in divisor relation. It is binary operation which operates in two relations. The division is denoted by a symbol “÷” The notation for the division of two relations P & Q is P÷Q. where P is the dividend relation and Q is the divisor relation. Consider an example, in which we take two tables P and Q

P M N m 1 n 1 m 1 n 2 m 2 n 1 m 3 n 3 m 4 n 1 m 4 n 2 m 1 n 3 Q Q N N n 1 n 2 Q Q N Q n 1 - n 2 n 3 i ii R R M M m 1 m 4 m 2 m 3 iii R M m 1 iv R M m 1 m 2 m 3 m 4 The resultant relation R obtained by P÷Q in each case when tuples of Q are different

Properties of Divide Operation 1. It is a binary operation as it operates on two relations(tables). 2. In the Operation P÷Q , the relation P is known as dividend relation and relation Q is known as a divisor relation. If the dividend relation has degree m+n and the divisor relation has a degree of n , then the resultant relation will have a degree m. 3. The division operation is suited to queries that include the phrase “for all”. 4. It is very rarely used in database applications. BACK TO INDEX

THANKS BACK TO INDEX