Extended Relational Algebra.

Extended Relational Algebra:

Extended operators relational algebra are given as follows:

1. Natural Join (⋈)

2. Assignment  (è)

3. Intersection (∩)

4. Division (÷)

Natural Join:

It is a binary operators which takes two relations R and S and it is denoted as:

R  ⋈  S

and it will give the tuple in the tuple in the output relation related with primary key and foreign key relationship.

In the expression R and S are the relation where we perform natural join , It does Cartesian  product and then it extracts the tuple from the Cartesian product having parent and child relationship.

Example1:

 Write an expression using Natural join to ,list product details along with order details.

Syntax:

σ  (Product X orders);

Write above expression using Cartesian Product:

σ  (  σprod = product_id, mfr =  mfr_id (product X orders));

SQL:

SELECT *, *

FROM orders, product

WHERE mfr = mfr_id and prod = prod_id;

Example2:

Write an expression using Natural join to display customer name and its employee name if employee is having sales more than 50% quota.

∏                  σ

    customer_name (     sales > 0.5 * quota (customer ⋈  salesrep));

Using Cartesian Product:

∏                  σ                    σ

    custname name  (   sales > 0.5 * quota (  cust = emp_id(customer X salesrep)));

Left OuterJoin ():

A left outer join is denoted as , when it is used with two relation say R and S, it will extract the rows related with parent and child relationship along with additional tuple present in left hand side table present in left hand side table by storing the columns of write hand side table for corresponding rows as NULL;

Right Outer Join ():

It is denoted as , when it apply over two relational R and S, it will extract the tuples  related with parent parent child relationship along with the additional tuples in the right hand side table by storing the columns of left side table for corresponding rows as NULL.

Full OuterJoin( ):

It is denoted as  , when used with two relation it will first perform a natural join then it will also extract additional row from the left hand side table and then additional row from right hand side table.

 

Assignment  (è):

It is a binary operator and used to assign a relation to another relation. It is denoted as:

R è  S 

It can be used to simplify the complex relation by breaking to sub-relations and assign those sub relation as new names. 

A new name in the Relation, a similar concept is available as Views,

Consider Following example:

∏name (σsales > quota (salesrep))   ----> R

∏custname (σcredit_limit > 2500 (customer))  ---> S   

R U S

Intersect(∩):

It is binary  operator which takes two relation R and S , It is denoted as R S where R and S must satisfy the following properties:

1. Arity of both relation will same.

2. Domain of first column in R is must same to first column of S.

Example:

∏name (σsales >quota(salesrep ))   ---> R

∏customer (σcreatdit_limit  > 2500 (Customer))   -->S

R ∩ S

Division(÷):

It is binary operator which takes two relation R and S and it is donated as R ÷ S where R and S must satisfy following property.

1. Attributes of S are a proper subset of attribute of R.

S  c  R => S c R and R c S => R = s.

S c R => R  != S

2. Domain of corresponding attribute in R and S must be same.

When division is applied for R and S the output will have all the attribute of R but not in S, and the tuple which are there in R and S both.