1 / 27

Chapter 3 Section 3.4 Relational Database Operators (Relational Algebra)

Chapter 3 Section 3.4 Relational Database Operators (Relational Algebra). Database Systems: Design, Implementation, and Management 7th Edition Peter Rob & Carlos Coronel. What is Relational Algebra?. Part of Relational DB Theory

edrennen
Download Presentation

Chapter 3 Section 3.4 Relational Database Operators (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. Chapter 3 Section 3.4 Relational Database Operators (Relational Algebra) Database Systems: Design, Implementation, and Management 7th Edition Peter Rob & Carlos Coronel

  2. What is Relational Algebra? • Part of Relational DB Theory • Operations that any RDBMS should provide for data manipulation • NOT directly included in products; capabilities generally provided via QBE or SQL

  3. What is included in RA? • Set Operations • Operations specific to RDBs • Recent Advanced Add-Ons • All RA operations work on one or more relations, and produce a relation as a result

  4. UNION • Produces a resulting relation that contains a tuple for every tuple in either or both of two input relations (duplicates only occur once) • The Relations being combined must be union-compatible (type-compatible) • e.g. CurrentEnrollments U HistoricalEnrollments; MailListFromSierraClub U MailListFromAudabonSoc;

  5. INTERSECTION • Produces a resulting relation that contains a tuple for every tuple in BOTH of two input relations • The Relations being combined must be union-compatible (type-compatible) • MailListFromSierraClub INTERSECT MailListFromNewBabyMag;

  6. SET DIFFERENCE (MINUS) • Produces a resulting relation that contains a tuple for every tuple in FIRST of two input relations AND NOT IN the second. • The Relations being combined must be union-compatible (type-compatible) • MailListFromMarketingCompany - CurrentCustomerList;

  7. CARTESIAN PRODUCT(TIMES) • Produces a resulting relation that contains all attributes in either input relation and a tuple for every possible combination of tuples in two input relations. • The Relations being combined must be product-compatible • BY ITSELF, not usually useful in the real world

  8. Relational Database Operators • PRODUCTproduces a list of all possible pairs of rows from two tables. (old) Figure 2.8 PRODUCT

  9. RESTRICT/SELECT • Produces a resulting relation, containing only the tuples that meet some condition (hence a “horizontal” subset of the original relation) • e.g. employees in department #4, students majoring in CS, students with a GPA < 2.0

  10. PROJECTion • Produces a resulting relation, containing only the attributes that are requested (hence a “vertical” subset of the original relation) • e.g. last name, first name and salary of employees; last name, major, year of students; dept, class of sections

  11. Relational Database Operators • JOIN allows us tocombineinformation from two or more tables. • JOIN is the real power behind the relational database, allowing the use of independent tables linked by common attributes.

  12. JOINS • Produces a resulting relation that contains all attributes in either input relation and a tuple for “every possible combination of tuples in two input relations that meet some condition”.

  13. Relational Database Operators • Natural JOINlinks tables by selecting only the rows with common values in their common attribute(s). It is the result of a three-stage process: • A PRODUCT of the tables is created. (Figure 3.12) • A SELECT is performed on the output of the first step to yield only the rows for which the common attribute values match. (Figure 3.13) • A PROJECT is performed to yield a single copy of each attribute, thereby eliminating the duplicate column. (Figure 3.14)

  14. Natural Join, Step 1: PRODUCT

  15. Natural Join, Step 2: SELECT

  16. Natural Join, Step 3: PROJECT

  17. Natural Join (continued) • Final outcome yields table that • Does not include unmatched pairs • Provides only copies of matches • If no match is made between the table rows, • the new table does not include the unmatched row

  18. Relational Database Operators • EquiJOINlinks tables based on anequality conditionthat comparesspecified columnsof each table. The outcome of the EquiJOIN does not eliminate duplicate columns and the condition or criteria to join the tables must be explicitly defined. • Theta JOINis anequiJOINthat compares specified columns of each table using a comparison operator other than the equality comparison operator. • In anOuter JOIN,the unmatched pairs would be retained and the values for the unmatched other tables would be left blank or null.

  19. Outer Join • Matched pairs are retained and any unmatched values in other table are left null • In outer join for tables CUSTOMER and AGENT, three scenarios are possible: • Left outer join • Yields all rows in CUSTOMER table, including those that do not have a matching value in the AGENT table • Right outer join • Yields all rows in AGENT table, including those that do not have matching values in the CUSTOMER table • FULL OUTER JOIN - keeps all tuples in first or second relation even if no matching tuples

  20. Left Outer Join

  21. Right Outer Join

  22. DIVISION • Useful for finding all X who are doing something with all Y • e.g. find all students who are taking all three of these sections: 66416, 66417,66419 • book e.g. find all locations that are associated with both codes A & B

  23. Relational Database Operators • DIVIDE typically involves the use of one single-column table and one two-column table.

  24. A Minimally Complete Set of RA Operations • RESTRICT, • PROJECT, • UNION, • SET DIFFERENCE, • CARTESIAN PRODUCT • Others can be derived • R INTERSECT S is equivalent to (R U S) - ((R -S) U (S - R)) • Theta JOIN is equivalent to cartesian product followed by restrict • NATURAL JOIN is equivalent to Cartesian product preceded by rename and followed by project and restrict

  25. Enhancements • Aggregate Functions - SUM, AVERAGE, MAXIMUM, MINIMUM • Aggregates within Group • e.g. GROUPING BY: Dept#; COUNT SSN, AVERAGE SALARY (Employee) - for each department give the count of # employees and the average salary

  26. A Final Word While RA is not seen commercially, it is a foundation on what is available commercially. It is a commonly understood basis for comparison and for communication.

  27. End Relational Algebra

More Related