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