13.15 Table containing a repetition column

HiRDB permits definition of a table that contains a column in which multiple elements can be stored in each row. In other words, a table can be defined with repetition columns.

Elements are the items that are repeated in the rows of a repetition column. To define such a table, it must be created conventionally as shown in Figure 13-23. Figure 13-23 shows an example of tables defined without repetition columns.

Figure 13-23 Example of tables defined without repetition columns

[Figure]

To access these two tables, they must first be joined. Joining tables results in disadvantages, such as complicating the SQL syntax. If repetition columns are used, one table containing all the information in two tables can be created without having to join them.

Figure 13-24 shows an example of a table containing repetition columns.

Figure 13-24 Example of table containing repetition columns

[Figure]

Explanation:
QUALIFICATION, FAMILY, RELATIONSHIP, and SUPPORT are repetition columns.
Organization of this section
(1) Effects of defining repetition columns
(2) Specification
(3) Notes

(1) Effects of defining repetition columns

A table with multiple values and multiplicity can be expressed in rows. Therefore, the following effects can be expected:

(2) Specification

To specify a repetition column, specify the ARRAY option in the CREATE TABLE definition SQL statement.

An example of defining a table containing repetition columns is shown as follows. This definition is based on the STAFF_TABLE shown in Figure 13-24. This example assumes that a multicolumn index has been defined for RELATIONSHIP and SUPPORT.

Example

CREATE TABLE STAFF_TABLE
(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]);

CREATE INDEX SUPPORTIDX ON STAFF_TABLE (RELATIONSHIP,SUPPORT);

Note
SUPPORTIDX is an index name assigned to STAFF_TABLE.

(3) Notes