*Extended Operators of Relational Algebra*

"An Algebra of Relational Operations" presented the classical relational algebra, and "Relational Operations on Bags" introduced the modifications required to treat relations as bags of tuples rather than sets. The ideas of these two sections serve as a base for most of modern query languages. On the other hand, languages such as SQL have many other operations that have proved quite important in applications. Thus, a full treatment of relational operations must contain a number of other operators, which we introduce in this section. The additions:

1. The duplicate-elimination operator δ turns a bag into a set by eliminating all but one copy of each tuple.

2. Aggregation operators such as sums or averages, are not operations of relational algebra, but are used by the grouping operator. Aggregation operators apply to attributes (columns) of a relation. e.g. the sum of a column produces the one number that is the sum of all the values in that column.

3. Grouping of tuples according to their value in one or more attributes has the effect of partitioning the tuples of a relation into "groups". Aggregation can then be applied to columns within each group, giving us the ability to express a number of queries that are impossible to express in the classical relational algebra. The grouping operator γ is an operator that combines the effect of grouping and aggregation.

4. The sorting operator T turns a relation into a list of tuples, sorted according to one or more attributes. This operator should be used sensibly, because other relational-algebra operators apply to sets or bags, but never to lists. Thus, T only makes sense as the final step of a series of operations.

5. Extended projection gives additional power to the operator π. In addition to projecting out some columns, in its generalized form π can perform computations involving the columns of its argument relation to produce new columns.

6. The outerjoin operator is a variant of the join that avoids losing dangling tuples. In the result of the outerjoin, dangling tuples are "padded" with the null value, so the dangling tuples can be represented in the output.

### Duplicate Elimination

Sometimes, we need an operator that converts a bag to a set. For that purpose, we use δ(R) to return the set consisting of one copy of every tuple that appears one or more times in relation R.**Example 1 :**If R is the relation

from "Relational Operations on Bags" Figure (a) then δ(R) is

Note that the tuple (1,2), which appeared three times in R, appears only once in δ(R).

### Aggregation Operators

There are various operators that apply to sets or bags of atomic values. These operators are used to summarize or "aggregate" the values in one column of a relation, and thus are referred to as aggregation operators. The standard operators of this type are:1. SUM produces the sum of a column with numerical values.

2. AVG produces the average of a column with numerical values.

3. MIN and MAX, applied to a column with numerical values, produces the smallest or largest value, respectively. When applied to a column with character-string values, they produce the lexicographically (alphabetically) first or last value, respectively.

4. COUNT produces the number of (not necessarily distinct) values in a column. Equivalently, COUNT applied to any attribute of a relation produces the number of tuples of that relation, including duplicates.

**Example 2 :**Consider the relation

Some examples of aggregations on the attributes of this relation are:

1. SUM(B) = 2 + 4 + 2 + 2 = 10.

2. AVG(A) = (1 + 3 + 1 + 1)/4= 1.5.

3. MIN(A) = 1.

4. MAX(B) = 4.

5. COUNT(A) = 4.

### Tags

- relational algebra
- tuples
- attributes
- duplicate elimination
- aggregation operators
- Scrolling Cursors
- Protecting Against Concurrent Updates
- Modifications by Cursor
- Cursors
- Instead-Of Triggers
- Triggers in SQL
- Schema-Level Constraints and Triggers
- Tuple-Based CHECK Constraints
- Constraints on Attributes and Tuples
- Declaring Foreign-Key Constraints
- Keys Declared With UNIQUE
- Constraints and Triggers
- Interpreting Queries Involving Views
- Modifying Views
- View Definitions
- Introduction to Selection of Indexes
- Default Values / Indexes
- Simple Table Declarations
- Defining a Relation Schema in SQL
- Deletion / Updates
- Database Modifications
- Grouping / HAVING Clauses
- Full-Relation Operations
- Natural Joins / Outerjoins
- Subqueries in FROM Clauses
- Conditions Involving Tuples
- Subqueries
- Union, Intersection, and Difference of Queries
- Interpreting Multirelation Queries
- Tuple Variables
- Disambiguating Attributes
- Queries Involving More Than One Relation
- Null Values and Comparisons Involving NULL
- Selection in SQL
- Projection in SQL
- The Database Language SQL
- Additional Constraint Examples
- Referential Integrity Constraints
- Constraints on Relations
- Extending the Projection Operator
- Grouping
- Selection on Bags / Product of Bags / Joins of Bags
- Union, Intersection, and Difference of Bags
- Relational Operations on Bags
- A Linear Notation for Algebraic Expressions
- Dependent and Independent Operations
- Renaming
- Combining Operations to Form Queries
- Selection / Cartesian Product
- Set Operations on Relations
- An Algebra of Relational Operations
- Relational Algebra
- Attribute Lists
- Information Integration Via Semistructured Data
- Semistructured Data Representation
- Object-Oriented Versus Object-Relational
- Nested Relations
- The Object-Relational Model
- What If There Is No Key
- Representing ODL Relationships
- Representing Other Type Constructors
- Representing Set-Valued Attributes
- Nonatomic Attributes in Classes
- Declaring Keys in ODL
- Subclasses in ODL / Multiple Inheritance in ODL
- Types in ODL
- Methods in ODL
- Multiplicity of Relationships
- Relationships in ODL / Inverse Relationships
- Attributes in ODL
- Introduction to ODL
- The Type System
- Decomposition into Fourth Normal Form
- Reasoning About Multivalued Dependencies
- Definition of Multivalued Dependencies
- Multivalued Dependencies
- Third Normal Form
- Boyce-Codd Normal Form
- Decomposing Relations
- Projecting Functional Dependencies
- Closing Sets of Functional Dependencies
- The Transitive Rule
- Why the Closure Algorithm Works
- Computing the Closure of Attributes
- Trivial Functional Dependencies
- The Splitting/Combining Rule
- Rules About Functional Dependencies
- Keys of Relations
- Using Null Values to Combine Relations - Comparison of Approaches
- An Object-Oriented Approach
- Converting Subclass Structures to Relations
- Handling Weak Entity Sets
- Combining Relations
- From E/R Relationships to Relations
- From Entity Sets to Relations
- From E/R Diagrams to Relational Designs
- Relation Instances
- Equivalent Representations of a Relation
- Tuples / Domains
- Attributes / Schemas
- The Relational Data Model
- Summary of The Entity-Relationship Data Model
- Weak Entity Set Notation
- Requirements for Weak Entity Sets
- Representing Keys in the E/R Model
- Keys in the E/R Model
- The Modeling of Constraints
- Picking the Right Kind of Element
- Design Principles
- Subclasses in the E/R Model
- Converting Multiway Relationships to Binary
- Attributes on Relationships
- Multiway Relationships
- Elements of the E/R Model
- Database System Implementation
- Database Programming
- Database Design
- The Query Processor
- Multimedia Data
- Relational Database Systems