Embedded data sets and occurring items are both modeled as embedded tables.

Modeling Rules

Modeling embedded data sets is similar to the method Relational Design Center uses to model disjoint data sets as tables. All modeling rules described earlier in Disjoint Data Set Defaults apply to embedded data sets. In addition, embedded partitioned data sets are not modeled.

A set spanning an embedded data set is not usable by the SQL query optimizer and cannot be modeled as an index or used as an index during query processing. However, the set is used by the system to access the embedded data set.

Limitations on Levels of Embedding

For occurring items, a limit is imposed on the number of rows that can exist in the embedded table for each row in the master table. This limit is equal to the maximum number of occurrences. Embedded data sets are modeled as separate tables with no limit on the number of rows for each row in the master table.

One or more additional columns are logically added to the SQL description and are used to join the embedded table to its master table. These additional columns are copies of the primary key columns in the master table.

Modeling embedded tables is limited to two embedded levels within a disjoint data set. For example, assume that data set DS1 is disjoint, data set DS2 is embedded in DS1, data set DS3 is embedded in DS2, and data set DS4 is embedded in DS3. In this example, DS1, DS2, and DS3 are modeled since DS1 is disjoint; DS2 is the first embedded level, and DS3 is the second embedded level. DS4 is not modeled because it is the third embedded level.

Each occurring item also adds an embedded level. If data set DS1 contains occurring item DS1-G1, which in turn contains occurring item DS1-G2, both DS1-G1 and DS1-G2 are modeled because they are the first two embedded levels. If embedded data set DS2 contains occurring item DS2-G1, which in turn contains occurring item DS2-G2, then DS2-G1 is modeled because it is the second embedded level. However, DS2-G2 is not modeled because it is the third embedded level. Within DS3, no occurring items would be modeled because it is already the second embedded level. Modeling is further limited by the absence of primary keys.

Deleting Embedded Tables

For a first-level embedded table, the master table is the disjoint data set that contains the embedded table. For a second-level embedded table, the master table is the first-level embedded table that contains the second-level embedded table.

If you want to delete a master row that contains an embedded data set, you must first delete all the embedded data set rows contained in the master row.

If a master table contains embedded occurring tables, you can delete the master rows at any time. When you delete a master row, all the embedded occurring rows contained in that master row are automatically deleted at the same time.

Occurring Item Mapping

Relational Design Center processes occurring items in one of the following ways:

Occurrence Index Mapping

Relational Design Center represents the occurrence index of an occurring item as a column of type INTEGER with the STATIC option defined. The STATIC option is used because SQL does not support the modification of the occurrence index. Modifying the occurrence index would involve moving the contents of one row to another row and deleting the previous row. Relational Design Center defines a check constraint for the column to ensure that the value assigned to the occurrence index falls within the allowed bounds. The occurrence index can be retrieved as a target item, tested in a selection expression, or assigned when inserting an occurrence of the row into its embedded table.

By default, the name of the occurrence index column is <occurring item name>_INX. In Relational Design Center, you can modify the schema to specify a more meaningful name for the occurrence index column.

Maintenance Options

By default, the system maintains the occurrence index column. However, in Relational Design Center, you can specify whether the column is system-maintained or user-maintained as follows:

How Embedded Tables Are Modeled

If a table has a primary key, the contained occurring items and embedded data sets are modeled as embedded tables at the first or second embedded level. The primary key columns of the containing (master) table are logically copied to the embedded table columns with the options NOT NULL and STATIC. These copied columns constitute the foreign key in the embedded table and can be used to join the embedded table to its master table.

For a SELECT statement, failure to join the embedded table to a particular master tuple results in a linear search of the master table. For an INSERT statement, the values specified for the copied columns much match the existing values of some rows in the master table or a "Matching Primary Key Not Found" error occurs. If an attempt is made to delete a master table entry when a corresponding entry exists in the embedded table, the system issues an error message related to the foreign key constraint violation.

For an embedded table mapped from an embedded data set, the user-specified or default primary key columns constitute a partial primary key, which is appended to the columns copied from its master table to form the full primary key for the embedded table.

For an embedded table mapped from an occurring item, the occurrence index is the partial primary key, which is appended to the columns copied from its master table to form the full primary key for the embedded table.

For a second-level embedded table, the master table is the containing embedded table at the first embedded level, and the copied columns are the columns of the full primary key at the first embedded level.

For an occurring item within an occurring item within a disjoint data set, the full primary key consists of the disjoint data set primary key, the first occurring item occurrence index, and the second occurring item occurrence index. The first two of these three keys are the copied primary key from the master table (the higher level occurring item).

Occurring Item and Embedded Data Set Examples

The following examples illustrate how a DASDL occurring item and a DASDL embedded data set are modeled in SQL:

DASDL Example

PERSON DATA SET
   (NAME   ALPHA(20);
     CHILD  ALPHA(20)
      OCCURS 10 TIMES);

PERSON-SET SET OF PERSON
   KEY NAME NO DUPLICATES;

PERSON DATA SET
   (NAME   ALPHA(20);
     CHILD  DATA SET
     (CHILDNAME ALPHA (20))
   ;CHILD-SET SET OF CHILD KEY CHILD NAME);

 PERSON-SET SET OF PERSON
   KEY NAME NO DUPLICATES;

SQL Example

CREATE TABLE PERSON
   (NAME CHARACTER (20) NOT NULL
    PRIMARY KEY  (NAME)
   )

CREATE TABLE CHILD
   (NAME CHARACTER (20) 
    NOT NULL, STATIC
   ,CHILD CHARACTER (20)
   ,CHILD_INX INTEGER STATIC
   ,PRIMARY KEY  (NAME, CHILD_INX
)
   ,CONSTRAINT CHILD_INX_CHECK
   CHECK (CHILD_INX >= 1 AND
      CHILD_INX <= 10)
   )

CREATE VIEW PERSON_SET AS
   SELECT * FROM PERSON
   --ORDER BY: NAME

CREATE TABLE PERSON
   (NAME CHARACTER (20) NOT NULL
    PRIMARY KEY (NAME)
   )

CREATE TABLE CHILD
   (NAME CHARACTER (20) NOT NULL 
STATIC
   ,CHILDNAME CHARACTER (20)
   ,PRIMARY KEY  (NAME, CHILDNAME)
   )

CREATE VIEW PERSON_SET AS
   SELECT * FROM PERSON
   ORDER BY: NAME

CREATE VIEW PERSON_SET AS
   SELECT * FROM PERSON
   --ORDER BY: NAME

In the preceding example, SQL constructs for the DASDL occurring item and embedded data set are nearly identical. The only difference is that the number of rows in the CHILD table for the occurring item is limited to 10 for each master row. The number of rows in the CHILD table for the embedded data set is unlimited. In both cases, the NAME column is the primary key and is copied as the foreign key.

Queries for the Occurring Item

In the following example, a query to look at the name of Fred's fifth child might be

SELECT CHILD FROM CHILD
WHERE CHILD_INX = 5 AND NAME = 'Fred'

SQL recognizes that the NAME column is the foreign key link to the master record and it optimizes accordingly. Any query on an embedded table that does not have a selection condition on the foreign key searches sequentially through all the master table records in the database.

A query to insert into the CHILD table a sixth child for Fred might be as follows:

INSERT INTO CHILD (CHILD, CHILD-INX, NAME)
VALUES ('ETHEL', 6, 'FRED')

A query to modify Fred's fourth child's name might be as follows:

UPDATE CHILD SET CHILD = 'Diane'
WHERE NAME = 'Fred' AND CHILD_INX = 4

In the occurring item example, a query to look at the third child for all persons might be as follows:

SELECT CHILD FROM CHILD
WHERE CHILD_INX = 3

In the embedded table example, a query to find all persons who have a child with the same name as the parent might be as follows:

SELECT PERSON.NAME FROM CHILD, PERSON
WHERE PERSON.NAME = CHILD.CHILD

PERSON_SET appears as an index in the Enterprise Database Server options of the SQL description. PERSON_SET is still in the database and is available to the SQL query optimizer to improve the performance of queries.

If the PERSON_SET index is declared so that duplicates are allowed in the DASDL description, and if no primary key is specified, then Relational Design Center models the PERSON data set with the following SQL constructs:

DASDL Example

PERSON DATA SET
   (NAME   ALPHA(20);
     CHILD  ALPHA(20)
      OCCURS 10 TIMES);

PERSON-SET SET OF PERSON
   KEY NAME DUPLICATES;

PERSON DATA SET
   (NAME   ALPHA(20);
     CHILD  DATA SET
      (CHILDNAME ALPHA (20)));

PERSON-SET SET OF PERSON
   KEY NAME DUPLICATES;

SQL Example

CREATE TABLE PERSON
   (NAME CHARACTER (20) NOT NULL)

CREATE VIEW PERSON_SET AS
   SELECT * FROM PERSON
   --ORDER BY: NAME

CREATE TABLE PERSON
   (NAME CHAR(20) NOT NULL)

CREATE VIEW PERSON_SET AS
   SELECT * FROM PERSON
   --ORDER BY: NAME

Because the PERSON data set does not have a suitable primary key, CHILD must be ignored.

The following examples illustrate how a DASDL occurring item embedded in another occurring item is modeled in SQL:

DASDL Example

STORE-SALES DATA SET
  (STORE-NUM                  NUMBER (4) REQUIRED;
   SALESPERSON                GROUP
       (NAME                       ALPHA (20);
        MONTHLY-SALES              NUMBER (5,2) OCCURS 12 TIMES
       ) OCCURS 7 TIMES
   );

STORES SET OF STORE-SALES KEY IS STORE-NUM NO DUPLICATES;

SQL Example

CREATE TABLE STORE_SALES
  (STORE_NUM          NUMERIC (4) NOT NULL
  ,PRIMARY KEY (STORE_NUM)
  )
CREATE TABLE SALESPERSON
  (STORE_NUM          NUMERIC (4) NOT NULL STATIC
  ,NAME               CHARACTER (20)
  ,SALESPERSON_INX    INTEGER STATIC
  ,PRIMARY KEY (STORE_NUM, SALESPERSONS_INX)
  ,CONSTRAINT SALESPERSON_CHECK
   CHECK (SALESPERSON_INX >= 1 AND SALESPERSON_INX <= 7)
  )
CREATE TABLE MONTHLY_SALES
  (STORE_NUM          NUMERIC (4) NOT NULL STATIC
  ,SALESPERSON_INX    INTEGER NOT NULL STATIC
  ,MONTHLY_SALES      NUMERIC (5,2)
  ,MONTHLY_SALES_INX  INTEGER STATIC
  ,PRIMARY KEY (STORE_NUM, SALESPERSON_INX, MONTHLY_SALES_INX)
  ,CONSTRAINT MONTHLY_SALES_CHECK
   CHECK (MONTHLY_SALES_INX >= 1 AND MONTHLY_SALES_INX <= 12)
  )
CREATE VIEW STORES AS
  SELECT * FROM STORE_SALES
  --ORDER BY: STORE_NUM

A query to look at the May sales for the first salesperson of all stores might be as follows:

SELECT MONTHLY_SALES FROM MONTHLY_SALES
WHERE MONTHLY_SALES_INX = 5 AND SALESPERSON_INX = 1

A query to find those salespersons who made more than $100 in June at store 2 might be as follows:

SELECT NAME
FROM SALESPERSON, MONTHLY_SALES
WHERE MONTHLY_SALES > 100 AND MONTHLY_SALES_INX = 6 AND
SALESPERSON.STORE_NUM = 2 AND
(SALESPERSON.STORE_NUM = MONTHLY_SALES.STORE_NUM AND
SALESPERSON.SALESPERSON_INX = MONTHLY_SALES.SALESPERSON_INX)

The following is the same example except one of the occurring items is changed to an embedded data set. The check constraint on MONTHLY_SALES_INX limits the number of entries but does not limit the number of salespersons allowed.

DASDL Example

STORE-SALES DATA SET
   (STORE-NUM                   NUMBER (4) REQUIRED
   ;SALESPERSON DATA SET
       (NAME                    ALPHA (20)
       ;MONTHLY-SALES           NUMBER (5,2) OCCURS 12 TIMES
       )
   ;PERSONS SET OF SALESPERSON KEY NAME
   );
STORES SET OF STORE-SALES KEY STORE-NUM NO DUPLICATES;

SQL Example

CREATE TABLE STORE_SALES
   (STORE_NUM                       NUMERIC (4) NOT NULL
   ,PRIMARY KEY (STORE_NUM)
   )
CREATE TABLE SALESPERSON
   (STORE_NUM                     NUMERIC (4) NOT NULL STATIC
   ,NAME                          CHARACTER (20) NOT NULL
   ,PRIMARY KEY (STORE_NUM, NAME)
   )
CREATE TABLE MONTHLY_SALES
   (MONTHLY_SALES_INX             INTEGER STATIC
   ,STORE_NUM                     NUMERIC (4) NOT NULL STATIC
   ,NAME                          CHARACTER (20) NOT NULL STATIC
   ,MONTHLY_SALES                 NUMERIC (5,2)
   ,PRIMARY KEY (STORE_NUM, NAME, 
    MONTHLY_SALES_INX)
   ,CONSTRAINT MONTHLY_SALE_CHECK CHECK 
    (MONTHLY_SALES_INX >= 1 AND
   MONTHLY_SALES_INX <= 12)
   )
CREATE VIEW STORES AS
  SELECT * FROM STORE_SALES
  --ORDER BY: STORE_NUM

The default rules described under Disjoint Data Set Defaults earlier in this section also apply to embedded data sets.