The Relational Algebra

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 FromSet 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 S R  S

Properties of Union Operation • The input relations must be union compatible. • Commutativity -It means that result of R  S is same as that of S  R. • 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 S Q=R S

Properties of Intersection Operation • The input relations must be union compatible. • Commutativity -It means that result of R S is same as that of S R. • 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 S Q=R -S

Properties of Difference Operation • The input relations must be union compatible. • They are not Commutative-It means that result of R - S is same as that of S - R. • 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 Q=RXS S

Properties of Cartesian Product Operation • The relations on which Cartesian product operation is applied need not necessary to be union compatible. • Commutativity-It means that result of R XS is same as that of S XR. • Associativity- It means that PX ( QXS) = (PXQ)XS Where P,Q and S are relations. • The resultant relation(table) may hold duplicate attributes if some attributes of two relations are defined on common domains. • 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 • 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(A1, A2, . . ., An) and S(B1, B2, . . ., 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 DEPT 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

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 THE RESULTANT RELATION THE RESULT OF SELF JOIN

Properties of Join Operation • Commutativity-It means that result of R ⋈S is same as that of S ⋈R. • Associativity- Using this the same rows appear in the final resuIt regardless of the order in which relations are joined. P⋈ ( Q⋈ S) = (P⋈ Q)⋈ S Where P,Q and S are relations. • We can combine a selection with a cross product to form a join. R ⋈S=  (R X S) • 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. • Rows whose join attributes are null,don’t appear in the resultants relation(table). • More than two tables can also be joined but it increases the complexity. • 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 Q Q Q Q The resultant relation R obtained by P÷Q in each case when tuples of Q are different i ii iii iv R R R R

Properties of Divide Operation • It is a binary operation as it operates on two relations(tables). • 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. • The division operation is suited to queries that include the phrase “for all”. • It is very rarely used in database applications. BACK TO INDEX

THANKS BACK TO INDEX

The Relational Algebra