By default, SQL-CREATE-VIEW forms nested tables based on controlling/dependent sets of attributes. Although this provides good results for simple files, there may be single attributes that are not part of a controlling/dependent set, but are MultiValue. Furthermore, there may be multi-subvalued sets that are not indicated in the dictionary. Therefore, except for the simplest dictionaries, it is best for the user to explicitly provide a command line specifying data types as well as MultiValue attributes when using SQL-CREATE-VIEW.
To define these relationships between attributes, the associated attributes can be provided to SQL-CREATE-VIEW on the command line surrounded by either [] or {} character. The first column listed within the brackets, either [] or {}, will be used to uniquely identify the rows, so it is important to determine whether the values (or sub-values) are unique amongst the other values (or sub-values) in that attribute (or value).
The [] characters indicate an attribute or set of attributes where the first attribute must be unique.
Example:
The ORDERS file contains attributes ORDERID and CUSTOMERID, as well as two MultiValue attributes called PRODUCTID and QUANTITY. Assume that each order is made up of multiple products but each PRODUCTID appears only once per order. Thus, PRODUCTID is unique for each ORDERID; the [] can be used to specify this relationship:
SQL-CREATE-VIEW ORDERS ORDERID CUSTOMERID [PRODUCTID QUANTITY]
SQL-CREATE-VIEW ORDERS ORDERID [PRODUCTID QUANTITY] CUSTOMERID
The {} characters indicate a relationship where the first attribute mentioned may not be unique. In this case, a pseudo column is generated to indicate the row’s value or sub-value count so that each row can be uniquely identified. See Pseudo Columns below for more information about pseudo columns. For example, the PATIENTS file contains attributes PATIENT.ID and NAME, as well as one MultiValue attribute called VISIT.DATE. Since a patient might be seen more than once on the same day, VISIT.DATE is not unique for each PATIENT.ID; the {} must be used to specify the relationship:
SQL-CREATE-VIEW PATIENTS PATIENT.ID NAME {VISIT.DATE}
The nested relationships may be nested up to two levels deep to handle multi-subvalued attributes. The example below demonstrates such an example.
Example:
In this example, the INVENTORY file has these attributes: ID, PRODUCTNAME, VENDOR_ID, MODEL, COLOR, INSTOCK. The attributes have these characteristics:
VENDOR_ID is a MultiValue attribute, where multiple vendors supply this product.
MODEL is a MultiValue attribute, where the inventory contains different models of the same product. It is the controlling field for COLOR and INSTOCK.
COLOR is a multi-subvalued attribute, dependent on MODEL, where each model may be available in many colors.
INSTOCK is a multi-subvalued attribute, dependent on MODEL, where each model in each color may be stocked with a different quantity.
One item in the D3 file, INVENTORY might look like this:
| Product_id | Productname | Vendor_id | Model | Color | Instock |
| 123*r5*13 | Wing-a-Ding | 842 | 6.0 | Red | 521 |
| 76 | Blue | 342 | |||
| 1482 | 7.0 | Red | 134 | ||
| Blue | 98 | ||||
| 7.1 | Red | 453 | |||
| Blue | 256 | ||||
| Green | 23 |
These relationships can be described in an SQL-CREATE-VIEW statement this way:
SQL-CREATE-VIEW INVENTORY ID PRODUCTNAME [VENDOR-ID] [MODEL {COLOR INSTOCK}] (cz
This SQL-CREATE-VIEW statement creates four SQL tables:
The table, INVENTORY contains the columns, ID, and PRODUCTNAME.
The nested table, INVENTORY_VENDOR_ID contains the nested key column, ID (needed to tie this table back to the parent INVENTORY) and the column, VENDOR_ID.
The nested table, INVENTORY_MODEL contains the nested key column, ID (again needed to indicate the parent table), and the column, MODEL.
The nested table, INVENTORY_MODEL_COLOR contains two nested key columns, ID and MODEL, the columns, COLOR and INSTOCK, and a pseudo-column, INVENTORY_SVC. See Pseudo Columns below for more information about pseudo-columns.
The four SQL tables created by the SQL-CREATE-VIEW command provide a flattened view of the data in the INVENTORY file. By using the SQL SELECT statement, the four SQL tables corresponding to the D3 file, INVENTORY can be displayed.
SELECT * FROM INVENTORY
| Product_id | Productname |
| 123*r5*13 | Wing-a-Ding |
SELECT * FROM INVENTORY_VENDOR_ID
| Product_id | Vendor-id |
| 123*r5*13 | 842 |
| 123*r5*13 | 76 |
| 123*r5*13 | 1482 |
SELECT * FROM INVENTORY_MODEL
| Product_id | Model |
| 123*r5*13 | 6.0 |
| 123*r5*13 | 7.0 |
| 123*r5*13 | 7.1 |
SELECT * FROM INVENTORY_MODEL_COLOR
| Product_id | Model | Color | Instock | Inventory_svc |
| 123*r5*13 | 6.0 | Red | 521 | 1 |
| 123*r5*13 | 6.0 | Blue | 342 | 2 |
| 123*r5*13 | 7.0 | Red | 134 | 1 |
| 123*r5*13 | 7.0 | Blue | 98 | 2 |
| 123*r5*13 | 7.1 | Red | 453 | 1 |
| 123*r5*13 | 7.1 | Blue | 256 | 2 |
| 123*r5*13 | 7.1 | Green | 23 | 3 |