1 / 19

Relational Algebra

Relational Algebra. Agenda: - Mathematical basis of Data Manipulation - Relational Algebra operators. Data Manipulation Language. (a) All data in a relational DB is stored in tables (b) A normalized DB has many tables (c) Parts of some information we need is located in different tables

deon
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 Agenda: - Mathematical basis of Data Manipulation - Relational Algebra operators

  2. Data Manipulation Language (a) All data in a relational DB is stored in tables (b) A normalized DB has many tables (c) Parts of some information we need is located in different tables Example (E-D-P db): Which projects does the Manager of Research Dept work on? DML:A computer-language to specify - what data we want to access, and - what we want to do to the data. Relational Algebra can help to understand SQL (standard DML).

  3. Relational Algebra (RA) What is an algebra ? Formal mathematical system to manipulate symbols Common useful algebras: Real algebra, Complex algebra, Boolean algebra, … RA: manipulate (add, multiply, …) schema instances - schema instance = = table = = set of tuples

  4. RA: Learning by examples.. WORKS_ON EMPLOYEE DEPENDENT

  5. RA Operators: SELECT USE: to select a subset of the tuples in a table SYNTAX: SELECT [conditions] ( TABLE ) Example: OUTPUT = SELECT [DeptNo = 5] ( EMPLOYEE ) EMPLOYEE

  6. RA Operators: SELECT.. SYNTAX: SELECT [conditions] ( TABLE ) conditions - can be any logical expression - must be evaluated for each tuple independently Example: OUTPUT = SELECT [ (DeptNo != 4) AND ( (ID = 222) OR (ID = 111)) ] ( EMPLOYEE) EMPLOYEE

  7. RA Operators: PROJECT USE: to select a subset of the columns in a table SYNTAX: PROJECT [attributes] ( TABLE ) Example: OUTPUT = PROJECT [ Name, ID] ( EMPLOYEE ) EMPLOYEE EMPLOYEE

  8. RA Operators: PROJECT.. SYNTAX: PROJECT [attributes] ( TABLE ) Notes: - PROJECT returns a table - PROJECT returns a set of tuples (no repeated elements) Example: OUTPUT = PROJECT [EmpID, Relationship] ( DEPENDENT ) DEPENDENT OUTPUT

  9. Combination of RA Operators Why ? Arbitrary combinations of RA operators are allowed Combinations are equivalent to sequence of operations Example: OUTPUT = PROJECT [Name, ID] ( SELECT [ SupervisorID = 222] ( EMPLOYEE) ) Equivalent to: OUTPUT1 =SELECT [ SupervisorID = 222] ( EMPLOYEE) OUTPUT = PROJECT [Name, ID] ( OUTPUT1) EMPLOYEE

  10. RA Operators: JOIN USE: to combine data from two tables into one SYNTAX: JOIN [conditions] ( TABLE1, TABLE2 ) Example: OUTPUT = JOIN [ID = IDno] ( EMPLOYEE, WORKS_ON ) WORKS_ON EMPLOYEE …

  11. RA Operators: JOIN.. USE: to combine data from two tables into one SYNTAX: JOIN [conditions] ( TABLE1, TABLE2 ) Example: OUTPUT = JOIN [ID = IDno] ( EMPLOYEE, WORKS_ON )

  12. RA Operators: JOIN How JOIN works 1. Cartesian Product of the two tables is formed 2. For each tuple in the Cartesian Product, condition is tested if TRUE the tuple is placed in OUTPUT else the tuple is discarded WORKS_ON EMPLOYEE

  13. RA Operators: OUTER JOINS USE: Join operations where we want to preserve at least one instance of each tuple (of a table) Example: OUTPUT = LEFT-OUTER-JOIN [ID = EmpID] ( EMPLOYEE, DEPENDENT ) DEPENDENT EMPLOYEE tuples in red have no match in DEPENDENT

  14. RA Operators: OUTER JOINS USE: Join operations where we want to preserve at least one instance of each tuple (of a table) Example: OUTPUT = LEFT-OUTER-JOIN [ID = EmpID] ( EMPLOYEE, DEPENDENT )

  15. RA – Set theoretic operators: UNION USE: Union of two instances of similarly defined schemas SYNTAX: UNION ( TABLE1, TABLE2) Example: X = UNION( (SELECT [EmpID = 222] (DEPENDENTS)), ( SELECT [EmpID = 444] ( DEPENDENTS))) DEPENDENT

  16. RA – Set theoretic operators: INTERSECTION USE: Intersection of two instances of similarly defined schemas SYNTAX: INTERSECTION ( TABLE1, TABLE2) Example: X = INTERSECTION (( (SELECT [EmpID = 222] (DEPENDENTS)), ( SELECT [Relationship = SON] ( DEPENDENTS))) DEPENDENT

  17. RA – Set theoretic operators: DIFFERENCE USE: Set difference of two similarly defined schemas SYNTAX: DIFFERENCE ( A, B) Example: Y = DIFFERENCE ( SELECT [EmpID = 222] (DEPENDENTS), SELECT [Relationship = SON] ( DEPENDENTS)) DEPENDENT Note: DIFFERENCE is not commutative.

  18. RA – Set theoretic operators: DIVIDEBY USE: Reports the quotient of dividing one table by another Syntax: DIVIDEBY( A, B) Example: OUTPUT = DIVIDEBY( WORKSON, PROJ) =  Note: Useful in answering “for-all” type of queries

  19. Summary Relational Algebra is an elegant mathematical DML Practice: Industry standard DML is Structured Query Language (SQL) Mathematical basis for SQL: Relational Calculus Relational Calculus is functionally equivalent to RA Next topic: SQL

More Related