1 / 14

Relational Algebra

Learn the theoretical foundation behind relational databases and how to manipulate table contents using relational operators. Explore the use of union, intersect, difference, divide, product, project, select, and join operations.

rudolfd
Download Presentation

Relational Algebra

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. Relational Algebra Theory behind the relational engine

  2. Relational Set Operators • Relational algebra • Defines theoretical way of manipulating table contents using relational operators • Use of relational algebra operators on existing relations produces new relations:

  3. Union Combines all rows from two tables, excluding duplicate rows. The tables must have the same attribute characteristics.

  4. Intersect Yields only the rows that appear in both tables. The tables must have the same attribute characteristics. Difference Yields all rows in one table that are not found in the other table. Subtracts one table from the other. The tables must have the same attribute characteristics.

  5. DIVIDE This uses one single-column table as the divisor and one 2-column table as the dividend. The tables must have a common column. The output of the operation is a single column with the values of column ‘a’ from the dividend tables rows where the value of the common column in both tables match.

  6. Product Yields all possible pairs of rows from two tables – also known as Cartesian product. If one table has six rows and the other tables has three rows the resulting table will have 18 rows. 6 X 3 = 18

  7. Project Yields all values for selected attributes. Yields a VERTICAL SUBSET of a table.

  8. Select Also known as Restrict, yields values for all rows found in the table that satisfy a given condition. SELECT can be used to list all the row values or it can yield only those rows that match a specified criterion. Yields a HORIZONTAL SUBSET of a table.

  9. Join Allows information to be combined from two or more tables. This is the real power behind the relational database, where independent tables can be linked by common attributes (PK to FK fields).

  10. Relational Algebra Operators (continued) Combines every record with every record from each table First, a Product is completed.

  11. Relational Algebra Operators (continued) Gets rid of every row where the PK/FK does not match Next, a SELECT occurs using the PK and FK as selection criteria.

  12. Relational Algebra Operators (continued) Gets rid of the duplicated key Finally, we get rid of the extra column. Remember, project yields the vertical subset, which gets rid of one of the duplicate PK-FK fields.

  13. Relational Algebra Operators (continued)

  14. Relational Algebra Operators (continued) Outer Joins are useful for finding values that cause referential integrity problems.

More Related