Review of the Relational Algebra 5DV120 Database System Principles - PowerPoint PPT Presentation

Review of the Relational Algebra 5DV120 — Database System Principles Ume˚ a University Department of Computing Science Stephen J. Hegner hegner@cs.umu.se http://www.cs.umu.se/~hegner Review of the Relational Algebra 20130526 Slide 1 of 13

About these Slides • These slides are mostly adapted from those for 5DV119 by the same instructor. • The adaptations use the schema of the Silberschatz-Korth-Sudarsham textbook, rather than that of the Elmasri-Navathe textbook. • These slides will be used as a refresher/review, and will be covered much more rapidly than in the introductory course. • They are intended for those who already know/knew the relational algebra, but need a quick refresher. Review of the Relational Algebra 20130526 Slide 2 of 13

Overview of the Relational Algebra • The relational algebra is defined in terms of three kinds of operations on relations: Operations specific to relations: Projection: Trim some columns from a relation. Selection: Trim some rows from a relation. Join: Combine two relations by matching values. The three fundamental set-theoretic operations: Union: X ∪ Y = all elements in either X or Y . Intersection: X ∩ Y = all elements in both X and Y . Difference: X \ Y or X − Y = all elements in X which are not in Y . A special operation: Attribute renaming: Change the names of some attributes of a relation. Review of the Relational Algebra 20130526 Slide 3 of 13

Projection • The projection operation takes a “vertical” slice of a relation by dropping some columns while retaining others. • The projection operator is represented by the lowercase Greek letter π , with the subscript identifying the columns to be retained. π { A 1 , A 2 ,..., A k } ( R ) • The semantics of this expression are exactly those of the following SQL query. SELECT DISTINCT A 1 , A 2 , . . . , A k R ; FROM • This is a formal operation on sets; duplicates are not part of the model. • Often, the set brackets are dropped in the subscript. π A 1 , A 2 ,..., A k ( R ) • If the attribute names are single letters, even the commas are sometimes dropped. π A 1 A 2 ... A k ( R ) Review of the Relational Algebra 20130526 Slide 4 of 13

Selection • The selection operation takes a “horizontal” slice of a relation by dropping some rows while retaining others. • The selection operator is represented by the lowercase Greek letter σ , with the subscript containing an expression which identifies the rows to be retained. σ ϕ ( R ) • The semantics of this expression are exactly those of the following SQL query. SELECT DISTINCT * R FROM WHERE ϕ ; • The expression ϕ is often written in a more formal, logical style than that used by SQL. Example: σ ((dept name= ′ Comp . Sci . ′ ) ∧ (salary ≥ 70000)) (instructor); Review of the Relational Algebra 20130526 Slide 5 of 13

Combining Expressions in the Relational Algebra • The operations in the relational algebra themselves produce relations as results. • Therefore, they may be composed. Example: π A 1 , A 2 ,..., A k ( σ ϕ ( R )) has the same meaning as SELECT DISTINCT A 1 , A 2 , . . . , A k FROM R WHERE ϕ ; • Typing rules must be observed, since it is the composition of two distinct operations. Example: While π name , dept name ( σ salary ≥ 70000 (instructor)) makes perfect sense, σ salary ≥ 30000 ( π name , dept name (instructor)) does not. Review of the Relational Algebra 20130526 Slide 6 of 13

Assignment Programs in the Relational Algebra • Instead of composing operations in functional notation, queries in the relational algebra may be expressed as a sequence of assignment statements. Example: The functional composition π name , dept name ( σ salary ≥ 70000 (instructor)) may also be expressed as the program of assignments X 1 ← − σ salary ≥ 70000 (instructor) X 2 ← − π name , dept name ( X 1 ) with X 2 as the final result. • It is often easier to read and follow such sequence of assignments than to read and follow a complex functional composition. Review of the Relational Algebra 20130526 Slide 7 of 13

Join • The join is a binary operation represented by the “bowtie” symbol � . • It is basically the inner join of SQL. • There are, however, a number of variants depending upon the subscript (or lack thereof). • The expression R 1 � ϕ R 2 has the semantics of the SQL expression SELECT * FROM R_1 JOIN R_2 ON ( ϕ ); provided ϕ is represented in the correct way. Example: instructor � (instructor.dept name=department.dept name) department has the meaning of SELECT * FROM instructor JOIN department ON (instructor.dept_name=department.dept_name ); Review of the Relational Algebra 20130526 Slide 8 of 13

Further Join Conventions • Multiple conditions may be shown in various ways: instructor � (instructor.dept name=department.dept nme) ∧ (salary ≥ budget) department instructor � { (instructor-dept name=department.dept name) , (salary ≥ budget) } department instructor � (instructor.dept name=department.dept name) , (salary ≥ budget) department • These all have the meaning of SELECT * FROM instructor JOIN department ON (( instructor .dept_name= department.dept_name) AND (salary >= budget )); • Other logical connectives: instructor � (instructor.dept name=department.dept nme) ∨ (salary ≥ budget) department has the following meaning. SELECT * FROM instructor JOIN department ON (( instructor .dept_name= department= department .dept_name) OR (salary >= budget )); Review of the Relational Algebra 20130526 Slide 9 of 13

Natural and Cross Joins • The natural join is indicated by the absence of any subscripts on � . • The textbook of Elmasri and Navathe uses the ∗ symbol for natural join, although this notation is rather dated. • The expression department � teaches has the same meaning as SELECT * FROM department NATURAL JOIN teaches; • Note that � ∅ is the cross join , with no matches. ( ∅ = {} = empty set.) • Thus, department � ∅ teaches has the meaning of SELECT * FROM department JOIN teaches ON (TRUE ); • This cross join (or Cartesian product ) is also denoted department × teaches. Review of the Relational Algebra 20130526 Slide 10 of 13

Renaming • Recall that it is sometimes necessary to have multiple copies of the same relation. Query: Find the pairs of ID s for instructors who work in the same department. SELECT A.ID , B.ID FROM instructor as A JOIN instructor as B ON (A.dept_name=B.dept_name ); • In the relational algebra, there is a rename operation to support this. • There are two main formats: • ρ R ′ ( R ) returns a copy of R named R ′ , with the same attribute names. k ) ( R ) returns a copy of R named R ′ , with the attributes • ρ R ′ ( A ′ 1 , A ′ 2 ,..., A ′ renamed to A ′ 1 , A ′ 2 , . . . , A ′ k . • Name qualifiers are used as in SQL. • However, the original relation does not require a qualifier. Review of the Relational Algebra 20130526 Slide 11 of 13

Renaming Example Query: Find the pairs of ID s for instructors who work in the same department. • The above query as a sequence of steps in the relational algebra, with X 3 the answer, using each of the renaming conventions: X 1 ← − ρ A (instructor) X 2 ← − ρ B (instructor) X 3 ← − X 1 � (A.dept name=B.dept name) X 2 X 4 ← − π A.ID , B.ID ( X 3) X 1 ← − ρ B(ID ′ , name ′ . dept name ′ , salary ′ ) (instructor) X 2 ← − instructor � (dept name=dept name’) ( X 1 ) X 3 ← − π ID , ID ′ ( X 2) Review of the Relational Algebra 20130526 Slide 12 of 13

Set Operations • The following set operations are considered part of the relational algebra: Union: X ∪ Y = all elements in either X or Y . Intersection: X ∩ Y = all elements in both X and Y . Difference: X \ Y or X − Y = all elements in X which are not in Y . • They may only be applied when the elements in each set are of the same type. • If they are tuples, they have the same number of columns. • The attributes for matching columns must be of the same type. Review of the Relational Algebra 20130526 Slide 13 of 13