Multi-valued Database (UniVerse) -- SM (MV) vs SM (VS) and ASSOC()

Question

I have a question taken from pg 16 of IBM's Nested Relational Database White Paper, I'm confused why in the below CREATE command they use MV/MS/MS rather than MV/MV/MS, when both ORDER_#, and PART_# are one-to-many relationships.. I don't understand what value, vs sub-value means in non-1nf database design. I'd also like to know to know more about the ASSOC () clause.

Pg 16 of IBM's Nested Relational Database White Paper (slight whitespace modifications)

    CREATE TABLE NESTED_TABLE (
      CUST# CHAR (9) DISP ("Customer #),
      CUST_NAME CHAR (40) DISP ("Customer Name"),
      ORDER_# NUMBER (6) DISP ("Order #") SM ("MV") ASSOC ("ORDERS"),
      PART_# NUMBER (6) DISP (Part #") SM ("MS") ASSOC ("ORDERS"),
      QTY NUMBER (3) DISP ("Qty.") SM ("MS") ASSOC ("ORDERS")
    );

The IBM nested relational databases implement nested tables as repeating attributes and repeating groups of attributes that are associated. The SM clauses specify that the attribute is either repeating (multivalued--"MV") or a repeating group (multi-subvalued--"MS"). The ASSOC clause associates the attributes within a nested table. If desired, the IBM nested relational databases can support several nested tables within a base table. The following standard SQL statement would be required to process the 1NF tables of Figure 5 to produce the report shown in Figure 6:

    SELECT CUSTOMER_TABLE.CUST#, CUST_NAME, ORDER_TABLE.ORDER_#, PART_#, QTY
    FROM CUSTOMER_TABLE, ORDER_TABLE, ORDER_CUST
    WHERE CUSTOMER_TABLE.CUST_# = ORDER_CUST.CUST_# AND ORDER_CUST.ORDER_# =
    ORDER _TABLE.ORDER_#;

                                       Nested Table
                Customer #       Customer Name Order #        Part #   Qty.
                AA2340987        Zedco, Inc.      93-1123     037617   81
                                                              053135   36
                                                  93-1154     063364   32
                                                              087905   39
                GV1203948        Alphabravo       93-2321     006776   72
                                                              055622   81
                                                              067587   29
                MT1238979        Trisoar          93-2342     005449   33
                                                              036893   52
                                                              06525    29
                                                  93-4596     090643   33

Answer 1

I'll go ahead and answer my own question, while pursuing IBM's UniVerse SQL Administration for DBAs I came across code for CREATE TABLE on pg 55.

ACT_NO INTEGER FORMAT '5R' PRIMARY KEY
BADGE_NO INTEGER FORMAT '5R' PRIMARY KEY
ANIMAL_ID INTEGER FORMAT '5L' PRIMARY KEY

(see distracting side note below) This amused me at first, but essentially I believe this to be a column directive the same as a table directive like PRIMARY ( ACT_NO, BADGE_NO, ANIMAL_ID )

Later on page 5-19, I saw this

ALTER TABLE LIVESTOCK.T ADD ASSOC VAC_ASSOC (
    VAC_TYPE KEY, VAC_DATE, VAC_NEXT, VAC_CERT
);

Which leads me to believe that tacking on ASSOC (VAC_ASSOC) to a column would be the same... like this

CREATE TABLE LIVESTOCK.T (
    VAC_TYPE ... ASSOC ("VAC_ASSOC")
    VAC_DATE ... ASSOC ("VAC_ASSOC")
    VAC_NEXT ... ASSOC ("VAC_ASSOC")
    VAC_cERT ... ASSOC ("VAC_ASSOC")
);

Anyway, I'm not 100% sure I'm right, but I'm guessing the order doesn't matter, and that rather than these being an intransitive association they're just a order-insensitive grouping.

Onward! With the second part of the question pertaining to MS and MV, I for the life of me can not figure out where the hell IBM got this syntax from. I believe it to be imaginary. I don't have access to a dev machine I can play on to test this out, but I can't find it (the term MV) in the old 10.1 or the new UniVerse 10.3 SQL Reference

side note for those not used to UniVerse the 5R and 5L mean 5 characters right or left justified. That's right a display feature built into the table meta data... Google for UniVerse FORMAT (or FMT) for more info.

Answer 2

Just so you know, Attribute, Multivalue and Sub-Multivalue comes from the way they structure their data.

Essentially, all data is stored in a tree of sorts. UniVerse is a Multivalue Database. Generally, it does not work in the say way as Relational DBs of the SQL work function.

Each record can have multiple attributes.

Each attribute can have multiple multivalues.

Each multivalue can have multiple sub-multivalues.

So, if I have a record called FRED

Then, FRED<1,2,3> refers to the 1st attribute, 2 multivalue position and 3 subvalue position.

To read more about it, you need to learn more about how UniVerse works. The SQL section is just a side part of it. I suggest you read the other manuals to understand what you are working with.

EDIT

Essentially, the code above is telling you that:

There may be multiple orders per client. These are stored at an MV level in the 'table'

There may be multiple parts per order. These are stored at the MS level in the 'table'

There may be multiple qtys per order. These are stored at the MS level in the 'table'. since they are at the same level, although they are 1-n for orders, they are 1-1 in regards to parts.