SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

Relational Algebra

deon avatar
deon
19 diapositives · 509 Views · Aug 24, 2014

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

#relational algebra #relational algebra ppt #powerpoint #presentation
Relational Algebra - slide 1
01 / 19Relational Algebra
1 / 17

Télécharger la présentation

Relational Algebra
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  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