Chapter 4

1. Chapter 4 Relational Algebra

2. Introduction • Relational algebra becomes popular after publication of E.F.Codd’s relational model data in 1970. • It is a procedural language useful for representing query execution plan,& relatively close to SQL. • Relational algebra is set of operations which accepts one relation & produce new relation as a result. • This query is applied to tables/relations & output is also a table/relation.

3. Fundamental Operations • UNARY OPERATION • Projection Operation (∏) • Select Operation () • Rename Operation (ρ) • BINARY OPERATIONS (Set Operation ) • Set Operations • Union operation (U) • Difference operation (-) • Intersection operation (∩) • Join Operations ( ) • Cartesian Product Operations (X) • Division Operation (%)

4. Select Operation () OVERVIEW (conditional) • This operator is used to select some rows from table which satisfy particular selection condition given in selection operation. • selection operator selects a set of tuples that satisfy a selection condition. • output of query is exactly same as input schema of table. • this is unary relational operator having only one input table. B) SYNTAX Where, Attribute_name : Name of column in table Comparison_operator : = , < , <= , > , >= , <>

5. C) EXAMPLE σage < 30 (Employee)

6. Projection operation (∏) A) OVERVIEW (unconditional) • This operator is used for selecting some of many columns in table to display in result set. • Projection operator can select column or set of columns of table to be display in output of query. • We can select only few columns or all columns of a table as per requirements. • This is unary relational operator havg only one input table. B) SYNTAX

7. C) EXAMPLE 1. Find employee’s salary from employee table & salary

8. Rename Operation (ρ) A) OVERVIEW • We can give alternate name to any column or any table of query expressions using operator called as rename operator. • This operator is specially introduced to select specific column from joined table containing multiple columns of same column name. B) SYNTAX

9. Set Operation 1. INTRODUCTION • SQL SET operator allow combining results from two or more SELECT statements or combines result set of multiple queries. • The result of to queries can be combined using the set operations union, intersection and difference. 2. REQUIREMENTS • SELECT statement of both queries must retrieve the same number of columns. • Data types of corresponding columns in each query must be of same type.

10. Union Operator A) OVERVIEW • Union effectively appends the result of first query to the result of second query • It does not eliminate all duplicate rows and they are printed in result expression B) SYNTAX

11. Intersect Operator A) OVERVIEW • This operator finds out all rows that are common in both result of query 1 & in the result of query 2. • It does not eliminate all duplicate rows & they are printed in result expression. B) SYNTAX

12. Difference Operator A) OVERVIEW • Returns all rows that are in the result of query 1 but not in the result of query 2. B) SYNTAX

13. Cartesian Product A) OVERVIEW • A cross join performs relational product or Cartesian product of two tables specified in query. • In this case every row in first table will be joined with every row in second. So finaly number of rows in result table will b equals to product of number of rows in table 1 and number of rows in table2 B) SYNTAX

14. Join Operator A ) OVERVIEW • Join operator helps us to retrieve data from multiple tables or relations. • most common type of join is “Natural join” in which columns havg same name in two table will be taken for joining tables. • There are various types of joins possible in relational algebra. B) SYNTAX

15. Types 1. NATURAL JOINS • A natural join returns all rows by matching values in common columns having same name & data types of columns and that column should be present in both tables. 2. INNER JOINS • In many cases, tables are joined according to search conditions that find only the rows with matching values ; this type of join is knows as inner join • Inner join joins two tables when there is at least one match between two tables.

16. 3. OUTER JOINS • In an outer join the resultant table contains only the combinations of rows that satisfy the join conditions. • Rows that do not satisfy the join condition are discarded. Outer join joins two table although there is no match between two joining tables. • Outer joins are useful when u are trying to determine which values in related tables cause referential integrity problem. • Left outer join :- Table on left side may contain null values. • Left outer join takes all tuples in left relation • that did not match with any tuple in the right • relation.

17. Right Outer Join :- Table on right side may contain null values. • Right outer join takes all tuples in right relation that • did not match with any tuple in the right relation. • Full Outer Join :- Table on both sides of operator may contain null • values. • Full outer join takes all tuples in left relation that did not • match with any tuple in the right relation and also • involves all tuples in the right relation that did not match • with any tuples in left relation.

18. Relational Division Operator OVERVIEW • The divide operator operates in two tables that must have common columns between them. • The relation divide operator returns the record in one record set that have values that match all the corresponding values in second record set. B) SYNTAX