PowerPoint PresentationChapter 6 OutlineChapter 6 Outline (cont’d.)The Relational Algebra and Relational CalculusUnary Relational Operations: SELECT and PROJECTUnary Relational Operations: SELECT and PROJECT (cont’d.)Slide 7The PROJECT OperationSequences of Operations and the RENAME OperationRelational Algebra Operations from Set TheoryRelational Algebra Operations from Set Theory (cont’d.)The CARTESIAN PRODUCT (CROSS PRODUCT) OperationBinary Relational Operations: JOIN and DIVISIONBinary Relational Operations: JOIN and DIVISION (cont’d.)Variations of JOIN: The EQUIJOIN and NATURAL JOINVariations of JOIN: The EQUIJOIN and NATURAL JOIN (cont’d.)A Complete Set of Relational Algebra OperationsThe DIVISION OperationOperations of Relational AlgebraOperations of Relational Algebra (cont’d.)Additional Relational OperationsAdditional Relational Operations (cont’d.)Slide 23Notation for Query TreesSlide 25Examples of Queries in Relational AlgebraSlide 27Examples of Queries in Relational Algebra (cont’d.)Slide 29Slide 30Slide 31Slide 32Slide 33Slide 34Slide 35The Tuple Relational CalculusTuple Variables and Range RelationsExpressions and Formulas in Tuple Relational CalculusExistential and Universal QuantifiersSample Queries in Tuple Relational CalculusNotation for Query GraphsTransforming the Universal and Existential QuantifiersUsing the Universal Quantifier in QueriesSafe ExpressionsThe Domain Relational CalculusThe Domain Relational Calculus (cont’d.)SummaryCopyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-WesleyChapter 6The Relational Algebra and Relational CalculusCopyright © 2011 Ramez Elmasri and Shamkant NavatheChapter 6 OutlineUnary Relational Operations: SELECT and PROJECTRelational Algebra Operations from Set TheoryBinary Relational Operations: JOIN and DIVISIONAdditional Relational OperationsCopyright © 2011 Ramez Elmasri and Shamkant NavatheChapter 6 Outline (cont’d.)Examples of Queries in Relational AlgebraThe Tuple Relational CalculusThe Domain Relational CalculusCopyright © 2011 Ramez Elmasri and Shamkant NavatheThe Relational Algebra andRelational CalculusRelational algebraBasic set of operations for the relational modelRelational algebra expressionSequence of relational algebra operationsRelational calculus Higher-level declarative language for specifying relational queriesCopyright © 2011 Ramez Elmasri and Shamkant NavatheUnary Relational Operations:SELECT and PROJECTThe SELECT OperationSubset of the tuples from a relation that satisfies a selection condition:•Boolean expression contains clauses of the form <attribute name> <comparison op> <constant value>or•<attribute name> <comparison op> <attribute name>Copyright © 2011 Ramez Elmasri and Shamkant NavatheUnary Relational Operations:SELECT and PROJECT (cont’d.)Example:<selection condition> applied independently to each individual tuple t in RIf condition evaluates to TRUE, tuple selectedBoolean conditions AND, OR, and NOTUnaryApplied to a single relationCopyright © 2011 Ramez Elmasri and Shamkant NavatheUnary Relational Operations:SELECT and PROJECT (cont’d.)SelectivityFraction of tuples selected by a selection conditionSELECT operation commutativeCascade SELECT operations into a single operation with AND conditionCopyright © 2011 Ramez Elmasri and Shamkant NavatheThe PROJECT OperationSelects columns from table and discards the other columns:Degree Number of attributes in <attribute list>Duplicate eliminationResult of PROJECT operation is a set of distinct tuplesCopyright © 2011 Ramez Elmasri and Shamkant NavatheSequences of Operations and the RENAME OperationIn-line expression:Sequence of operations:Rename attributes in intermediate resultsRENAME operationCopyright © 2011 Ramez Elmasri and Shamkant NavatheRelational Algebra Operationsfrom Set TheoryUNION, INTERSECTION, and MINUS Merge the elements of two sets in various waysBinary operationsRelations must have the same type of tuplesUNIONR ∪ SIncludes all tuples that are either in R or in S or in both R and SDuplicate tuples eliminatedCopyright © 2011 Ramez Elmasri and Shamkant NavatheRelational Algebra Operationsfrom Set Theory (cont’d.)INTERSECTIONR ∩ SIncludes all tuples that are in both R and SSET DIFFERENCE (or MINUS)R – SIncludes all tuples that are in R but not in SCopyright © 2011 Ramez Elmasri and Shamkant NavatheThe CARTESIAN PRODUCT (CROSS PRODUCT) OperationCARTESIAN PRODUCT CROSS PRODUCT or CROSS JOINDenoted by ×Binary set operationRelations do not have to be union compatibleUseful when followed by a selection that matches values of attributesCopyright © 2011 Ramez Elmasri and Shamkant NavatheBinary Relational Operations:JOIN and DIVISIONThe JOIN OperationDenoted by Combine related tuples from two relations into single “longer” tuplesGeneral join condition of the form <condition> AND <condition> AND...AND <condition>Example:Copyright © 2011 Ramez Elmasri and Shamkant NavatheBinary Relational Operations:JOIN and DIVISION (cont’d.)THETA JOINEach <condition> of the form Ai θ BjAi is an attribute of RBj is an attribute of SAi and Bj have the same domainθ (theta) is one of the comparison operators:•{=, <, ≤, >, ≥, ≠}Copyright © 2011 Ramez Elmasri and Shamkant NavatheVariations of JOIN: The EQUIJOIN and NATURAL JOINEQUIJOINOnly = comparison operator usedAlways have one or more pairs of attributes that have identical values in every tupleNATURAL JOINDenoted by *Removes second (superfluous) attribute in an EQUIJOIN conditionCopyright © 2011 Ramez Elmasri and Shamkant NavatheVariations of JOIN: The EQUIJOIN and NATURAL JOIN (cont’d.)Join selectivityExpected size of join result divided by the maximum size nR * nSInner joinsType of match and combine operation Defined formally as a combination of CARTESIAN PRODUCT and SELECTIONCopyright © 2011 Ramez Elmasri and Shamkant NavatheA Complete Set of Relational Algebra OperationsSet of relational algebra operations {σ, π, , ρ, –, ×} is a ∪ complete setAny relational algebra operation can be expressed as a sequence of operations from this setCopyright © 2011 Ramez Elmasri and Shamkant NavatheThe DIVISION OperationDenoted by ÷Example: retrieve the names of employees who work on all the
View Full Document