A repetition column is a column whose data values might consist of multiple elements. An element means each item that is stored in the same row of the repetition column. A column is defined as a repetition column in CREATE TABLE; the number of elements must also be defined (however, the number of elements can be increased later with the ALTER TABLE).
Defining a table to contain repetition columns offers the following benefits:
The following figure shows an example of a table containing repetition columns. For details about repetition columns, see the HiRDB Version 9 Installation and Design Guide.
Figure 3-19 Example of a table containing repetition columns
The following is the CREATE TABLE SQL statement that defines the table containing the repetition column in Figure 3-19:
CREATE TABLE employee list
NAME NVARCHAR(10),
QUALIFICATION NVARCHAR(20) ARRAY[10],
SEX NCHAR(1),
FAMILY NVARCHAR(5) ARRAY[10],
RELATIONSHIP NVARCHAR(5) ARRAY[10],
SUPPORT SMALLINT ARRAY[10]);
The operations listed below can be performed on a table containing repetition columns.
SELECT NAME FROM STAFF_TABLE WHERE
ARRAY(RELATIONSHIP, SUPPORT) [ANY]
(RELATIONSHIP='Father' AND SUPPORT=1)
UPDATE STAFF_TABLE SET QUALIFICATION[2]=N'Accounting-II'
WHERE NAME=N'Tom Jones'
UPDATE STAFF_TABLE ADD QUALIFICATION[*]=ARRAY{N'Systems analysis'}
WHERE NAME=N'Tom Jones'
UPDATE STAFF_TABLE DELETE QUALIFICATION[2]
WHERE NAME=N'Tom Jones'
For details about the operations for tables containing repetition columns, see the HiRDB Version 9 UAP Development Guide.