Relational AlgebraWhat is an “Algebra”What is Relational Algebra?closureSlide 5Basic ideaCore Relational AlgebraUnion, Intersection, Set difference operatorsSlide 9Union: R SSlide 11Intersection RSSlide 13Set difference: R SSlide 15RemarksSelect operation (selection)PowerPoint PresentationselectExampleSlide 21Slide 22Project operation (projection)Slide 24Slide 25Slide 26The Cartesian product operationCartesian ProductExample: R3 <- R1 X R2From Cartesian to joinTheta-JoinSlide 32Natural JoinSlide 34Slide 35Inner joins or outer joinsOuterjoinExample: OuterjoinThree different kinds of outerjoinExpressions in a Single AssignmentPrecedence of relational operatorsBuilding Complex ExpressionsSequences of operationsExpression TreesSlide 45As a Tree:Slide 47The TreeRelation Schemas ( i.e. relation structures) for Interior Nodes in expression treeSchema-Defining Rules 1Schema-Defining Rules 21Relational AlgebraBasic operatorsRelational algebra expression tree2What is an “Algebra”•A mathematical model consists of:–Operands --- variables or values from which new values can be constructed.–Operators --- symbols denoting procedures that construct new values from given values. i.e. the ability to produce new values.3What is Relational Algebra?•Simply speaking, the relational algebra is a collection of operators that –take relations as their operands –and return a relation as their result. (closure properties allows nested expression!)4closure•In mathematics, a set is said to be closed under some operation if the operation on members of the set produces a member of the set. For example, the real numbers are closed under subtraction.•Similarly, a set is said to be closed under a collection of operations if it is closed under each of the operations individually.•In relational algebra, we say the result of any operator is still a relation, thus in a closure, which is the foundation for sub-queries.5What is Relational Algebra?•Its operands are relations or variables that represent relations.•Comprises a basic set of relational model operations (represented by operators) which are designed to do the most common things that we need to do with relations in a database.–Sequences of relational algebra operations form relational algebra expressions whose result is also a relation.•The result is an algebra that can be used as but not limited to a query language (at abstract level) for relations.6Basic idea•There is a core relational algebra that has traditionally been thought of as the relational algebra.•But there are several other operators we shall add to the core in order to support or model better the language SQL --- the principal language used in relational database systems.7Core Relational Algebra•Two groups of operations–Set theoretic operations: •Union, intersection, and difference.–Usual set operations, but require both operands have the same relation schema.–Relational model specific operations:•Selection: picking certain rows. σ•Projection: picking certain columns. π•Products: cartesian product. * (also a kind of set operation, but more related to joins.)•joins: compositions of relations.•Division: /Pronounced Bow tie8Union, Intersection, Set difference operators•These operations are binary: each is applied to two relations.•In order to apply these operators to relations, the relations have to be union compatible.•Two relations R(A1, …, An) and S(B1,…,Bn) are union compatible if they have same degree n and domain(Ai)=domain(Bi), 1<=1<=n.9Union, Intersection, Set difference operators•In this part, we assume R and S are union compatible.•Notation: –Union: R S –Intersection RS–Set difference: R S10Union: R S•The result of R S is a relation that includes all the tuples that are either in R or S or in both R and S.•Duplicates are eliminated.11Union: R SFN LNSusan ShashaBill BushFname LnameSussan ShashaMike FordstudentinstructorFN LNSusan ShashaBill BushMike FordStudent  instructor12Intersection RS•The result of RS is a relation that includes all the tuples that are in both R and S.13Intersection RSFN LNSusan ShashaBill BushFname LnameSussan ShashaMike FordstudentinstructorFN LNSusan ShashaStudent  instructor14Set difference: R S•The result of R-S is a relation that includes all the tuples that are in R but not in S.15Set difference: R SFN LNBill BushFname LnameMike FordInstructor - studentStudent - instructor16Remarks•Intersection and Union are commutative operations.–R  S = S  R –R  S = R  S •Intersection and Union are associative operations.–R  (S  T) =( R  S)  T–R  (S  T) =( R  S)  T–Set difference is not a commutative operation.–In general, R-S !=S-R17Select operation (selection)•The SELECT operation is used to select a subset of tuples from a relation that satisfy a selection condition.•Form of the operation: σC (R)–Where R is a relational algebra expression. The simplest expression is the name of a database relation.–The condition C is an arbitrary Boolean expression on the attributes of R.18•The boolean expression is formed using clauses (atomic comparisons) of the form A op B or A op c, where A, B are attribute names, c is a constant, and op is one of the comparison operators {=, <,>=,>,>=,!=}•The resulting relation has the same attributes as R.•The resulting relation includes only tuples in R whose attribute values satisfy the condition C.19select•R1 <-σC (R2)–C is a condition (as in “if” statements) that refers to attributes of R2.–R1 is all those tuples of R2 that satisfy C.20ExampleRelation tinyemployee:fname lname salaryJoe Reale 250Joe Bush 275Sue Gates 250Bob Bush 300AllBush<- σ lname=“Bush”(tinyemployee):fname lname salaryJoe Bush 275 Bob Bush 30021Exampleσ (DNO=4 and SALARY>2500) or (DNO=5 and salary>30000) (employee):22Remarks•The degree (i.e. the number of attributes) of the resulting relation is the same as that of the input relation.•The resulting relation is empty if no tuple satisfies the selection condition.•The number of tuples of the resulting relation is less than or equal to that of the input relation.•The select operation is commutative:•σC 2(σC1 (R)) =σC 1(σC2 (R))•A sequence of select operations can be combined into a single select operation•σC 1(σC2 (R)) =σC 1 and C2 (R)23Project operation (projection)•The project