MULTISET collection types

A MULTISET is a collection of elements in which the elements can have duplicate values. For example, a MULTISET of integers might contain the collection {1,3,4,3,3}, which has duplicate elements. You can define a column as a MULTISET collection type when you want to store collections whose elements have the following characteristics:

The elements might not be unique.
The elements have no specific order associated with them.

To illustrate how you might use a MULTISET, suppose that your human resources department wants to keep track of the bonuses awarded to employees in the company. To track each employee's bonuses over time, you can use a MULTISET to define a column in a table that records all the bonuses that each employee receives. In the following example, the bonus column is a MULTISET:

CREATE TABLE employee
(
   name      CHAR(30),
   address   CHAR (40),
   salary    INTEGER,
   bonus     MULTISET(MONEY NOT NULL)
);

You can use the bonus column in this statement to store and access the collection of bonuses for each employee. A query against the bonus column for any given row returns the dollar amount for each bonus that the employee has received. Because an employee might receive multiple bonuses of the same amount (resulting in a collection whose elements are not all unique), the bonus column is defined as a MULTISET, which allows duplicate values.