*Union, Intersection, and Difference of Bags*

When we take the union of two bags, we add the number of occurrences of each tuple. That is, if R is a bag in which the tuple t appears n times, and S is a bag in which the tuple t appears m times, then in the bag R U S tuple t appears n + m times. Note that either n or m (or both) can be 0.

When we intersect two bags R and S, in which tuple t appears n and m times, respectively, in R ∩ S tuple t appears min(n, m) times. When we calculate R - S, the difference of bags R and S, tuple t appears in R - S max(0,n - m) times. That is, if t appears in R more times than it appears in S, then in R - S tuple t appears the number of times it appears in R. minus the number of times it appears in S. On the other hand, if t appears at least as many times in S as it appears in R. then t does not appear at all in R - S. Intuitively, occurrences of t in S each "cancel" one occurrence in R.**Example (a) :** Let R be the relation of "Relational Operations on Bags" Figure (a), that is, a bag in which tuple (1,2) appears three times and (3,4) appears once. Let S be the bag

Then the bag union R U S is the bag in which (1,2) appears four times (three times for its occurrences in R and once for its occurrence in S); (3,4) appears three times, and (5,6) appears once.

The bag intersection R ∩ S is the bag

The bag difference R - S is the bag

To see why, notice that (1,2) appears three times in R and once in S, so in R - S it appears max(0,3 - 1) = 2 times. Tuple (3,4) appears once in R and twice in S, so in R - S it appears max(0,1 - 2) = 0 times. No other tuple appears in R, so there can be no other tuples in R - S.

As another example, the bag difference S - R is the bag

Tuple (3,4) appears once because that is the difference in the number of times it appears in S minus the number of times it appears in R. Tuple (5,6) appears once in S - R for the same reason. The resulting bag happens to be a set in this case.

### Projection of Bags

We have already exemplified the projection of bags. As we saw in "Relational Operations on Bags" Example (b), each tuple is processed separately during the projection. If R is the bag of "Relational Operations on Bags" Figure (b) and we calculate the bag-projection πA,B (R), then we get the bag of "Relational Operations on Bags" Figure (a).If the removal of one or more attributes during the projection causes the same tuple to be created from various tuples, these duplicate tuples are not removed from the result of a bag-projection. As a result, the three tuples (1, 2, 5), (1, 2, 7) and (1, 2, 8) of the relation R from "Relational Operations on Bags" Figure (b) each gave rise to the same tuple (1, 2) after projection onto attributes A and B. In the bag result, there are three occurrences of tuple (1, 2), while in the set-projection, this tuple appears only once.

### Tags

- tuples
- attributes
- 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
- 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
- Extending the Projection Operator
- Grouping
- Extended Operators of Relational Algebra
- Selection on Bags / Product of Bags / Joins 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 Design
- Multimedia Data
- Relational Database Systems