CODD’s  8 Relational Algebra Operations

 

 

RELATIONAL ALGEBRA                       

 

SQL

PSEUDO-CODE  or PROCEDURAL CODE

 

1) R = Customers

   WHERE city = 'Dallas'

SELECT  * 

FROM    Customers

WHERE city = 'Dallas';

For  c1 = first row of customers until c1 = last row of customers

       If  c1.city =  ‘Dallas’

            Display  c1.*

        End-If

End-For

2) R = customers [cid, cname]

SELECT cid, cname

FROM customers;

For  c1 = first row of customers until c1 = last row of customers

       Display  c1.cid, c1.cname

End-For

3) R = customers  x  orders

SELECT   *

from Customers, Orders;

For  c1 = first row of customers until c1 = last row of customers

       For o1 = first row of orders until o1 = last row of  orders

            Display  c1.*,  o1.*

       End-For

End-For

4) R = customers join orders

SELECT   *

FROM Customers, Orders

WHERE Customers.cid=Orders.cid;

For  c1 = first row of customers until c1 = last row of customers

       For o1 = first row of orders until o1 = last row of  orders

            If  (c1.cid = o1.cid)

            Display  c1.*, o1.* (all except for o1.cid)

       End-For

End-For

5) R = Students 

         UNION 

          Alumni

SELECT   *

FROM   Students

UNION

SELECT   *

FROM   Alumni

 

For  s1 = first row of students until s1 = last row of students

            Display s1

End-For

For  a1 = first row of alumni until  a1 = last row of alumni

            Display a1

End-For

 

6) R = Students

           intersect 

           Alumni

 

select  *

from students

intersect

Select  *

from alumni;

 

For  s1 = first row of students until s1 = last row of students

       For  a1 = first row of alumni until  a1 = last row of alumni

            If   (s1.ssn = a1.ssn and s1.name = a1.name) 

                        Display  s1.*    // display ssn and name

            End-If

        End-For

End-For

 

7) R = Students 

           minus 

          Alumni

select  *

from students

MINUS

Select  *

from alumni;

For  s1 = first row of students until s1 = last row of students

       For  a1 = first row of alumni until  a1 = last row of alumni

            If   (s1.ssn  <>  a1.ssn or s1.name <> a1.name) 

                        Display  s1.*    // display ssn and name

            End-If

        End-For

End-For

 

 

8) DIVISION

 

Rel. Algebra 

R1 = Orders [cid, pid]  /   Products [pid]

R2 = R1 Join  Customers

R3 = R2 [cname]

 

SQL

name of customers who ORDERED ALL PRODUCTS (cont.)

 

select ALL customers (cNAME)  where not exists a product that he has not ordered

 

result: TipTop

select  cname  from  customer  c  where  not  exists

   (select  *  FROM  products  p  where not exists

      (select  *   FROM  orders  o   where   c.cid = o.cid  and   p.pid = o.pid ));

 

 

FOR   c  from  row  1  TO  Last  of customers

                foundProductNotOrdered = false

                p  =  row  1  OF products

                WHILE  (foundProductNotOrdered = False   AND  p <= last row of products)

                                o1  =  first  Row  of  Orders

                                WHILE  ((c.cid <> o.cid  OR  p.pid <> o.pid)  &&  (o <= last row of orders)

                                                o1 = next row of orders

                                IF (o >  last  row  of orders)

                                                foundProductNotOrdered = True

                                ELSE

                                                p1 = next  row of products

                IF  (foundProductNotOrdered  = False)

                                print  customer  name