1 / 18

Chapter 4

Chapter 4. Relational Algebra. 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.

alaura
Download Presentation

Chapter 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related