1 / 19

Relational Algebra

Relational Algebra. A presentation for CS 457 By Dawn Haddan. Overview. What is relational algebra? A collection of operations to: Manipulate relations (tables) Specify Queries Application to Databases Used to perform many common requests in the relational database model. Definitions.

adrian-hogan
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 A presentation for CS 457 By Dawn Haddan

  2. Overview • What is relational algebra? • A collection of operations to: • Manipulate relations (tables) • Specify Queries • Application to Databases • Used to perform many common requests in the relational database model

  3. Definitions • Relational Data Model • Represents the database as a collection of relations • Relation • For our purposes, a table of values containing rows, where each row is a collection of related values. • Tuple • A row in a table • Attribute • A column header in a table • Domain • A set of values each attribute can take on

  4. Relational Algebra Operations • Basic operations • SELECT • PROJECT • Set Theoretic Operations • UNION • INTERSECTION • CARTESIAN PRODUCT • Database-Specific Operation • JOIN

  5. Basic Operations • SELECT • Selects a subset of tuples (rows) from a relation (table) based upon a boolean selection condition • PROJECT • Selects only attributes (columns) of interest from a table

  6. Select • General form: • σ<SELECTION CONDITION>(R) • With….. • σ as the symbol denoting a selection operation • SELECTION CONDITION equating to a boolean value • R representing a relational algebra expression that results in a relation, most frequently the name of a relation (table)

  7. Select Example • Given the relation (table) named STUDENT: • The SELECT operation σAGE>19(STUDENT) would return the new relation:

  8. Project • General form: • π<ATTRIBUTE LIST>(R) • With….. • π as the symbol denoting a PROJECT operation • ATTRIBUTE LIST as a list of attributes (columns) from the relation R • R representing a relational algebra expression that results in a relation, most frequently the name of a relation (table) • Result • A relation containing only the attributes specified in the order specified • At most the number of tuples (rows) in the original relation– duplicate tuples would constitute an invalid relation

  9. Project Example • Given the relation (table) named STUDENT: • The PROJECT operation πYEAR,SEX(STUDENT) would return the new relation: • Where the duplicate tuple has been removed and the attributes appear in the order specified in the attribute list

  10. Set Theoretic Operations • Both UNION and INTERSECTION … • Are binary operations • Return a single relation • Must be union compatible • The two relations must have the same tuple types • UNION • Produces a relation composed of all the tuples (rows) in either of the original relations • INTERSECTION • Produces a relation composed of all the tuples (rows) that exist in both of the original relations

  11. Set Theoretic Operations • CARTESIAN PRODUCT • Binary operation • Relations not necessarily union compatible • Combines tuples (rows) from two relations, say R and S • Result is a new relation whose attributes are the attributes from R and the attributes from S • Result contains the combined tuples from R and S. For instance, if R has 6 tuples, and S has 5 tuples, the result will contain 30 tuples.

  12. Sequences of Operations • Allows application of several operations • Produces a single relational algebra expression

  13. Sequence of Operations Example • Given the relation (table) named STUDENT: • The operation πNAME,AGE,SEXσAGE>21 OR SEX=‘M’(STUDENT) would return the new relation: • Where the OR implies a UNION operation

  14. Join • Simply a CARTESIAN PRODUCT of two relations (tables) followed by a SELECT • Combines tuples based upon the comparison of one or more attributes • Allows us to process relationships between relations

  15. Join • General form: • R |><|<JOIN CONDITION>S • Where… • R and S are relations • |><| symbolizes the JOIN operation • JOIN CONDITION is specified on attributes of both R and S and evaluated for each combination of tuples • Result • A new relation • Set of attributes is the union of all the attributes for R and S • Contains combinations of tuples that satisfy the join condition

  16. Join Example (Slide 1) • Given the relation (table) named STUDENT: • And the relation (table) named ADVISOR: • And the JOIN operation… πSNAME,ANAME,PHONE(STUDENT|><|AID=IDADVISOR)

  17. Join Example (Slide 2) • Returns the following relation:

  18. SQL equivalent statements • σAGE>19(STUDENT) • SELECT * FROM STUDENT WHERE AGE>19; • πYEAR,SEX(STUDENT) • SELECT YEAR,SEX FROM STUDENT; • πNAME,AGE,SEXσAGE>21 OR SEX=‘M’(STUDENT) • SELECT NAME,AGE,SEX FROM STUDENT WHERE AGE>21 or SEX=‘M’; • πSNAME,ANAME,PHONE(STUDENT|><|AID=IDADVISOR) • SELECT SNAME,ANAME,PHONE FROM STUDENT INNER JOIN ADVISOR ON ID=AID; --- OR --- • SELECT SNAME,ANAME,PHONE FROM STUDENT,ADVISOR WHERE ID=AID;

  19. Conclusions • Questions? • www.cs.unr.edu/~haddan Ted Codd, founder of relational algebra

More Related