HP 3000 Manuals

Data Set Types and Relationships [ TurboIMAGE/XL Database Management System Reference Manual ] MPE/iX 5.0 Documentation

TurboIMAGE/XL Database Management System Reference Manual

Data Set Types and Relationships

A TurboIMAGE/XL data set is either a master or a detail data set; these
data sets are described in this section. Figure 2-2 illustrates the
relationships and the types of six data sets in the ORDERS database.
Master data sets are identified by triangles and detail data sets by
trapezoids.

Figure 2-2. Master and Detail Data Set Relationships

Master Data Sets

Master data sets have the following characteristics:

* They are used to keep information relating to a uniquely
identifiable entity. For example, the CUSTOMER data set contains
information describing customers.

* They allow for rapid retrieval of a data entry because one of the
data items in the entry, called the key item, determines the
location of the data entry. A key item cannot be a compound item.
In Figure 2-3 on the next page, the CUSTOMER data set contains
a key item named ACCOUNT. The location of each entry is determined
by the value of the customer's account number.

* They can serve as indexes to the detail data set (that is, they
can be related to one or more detail data sets). The ACCOUNT key
item in the CUSTOMER master data set is related to the ACCOUNT
search item in the SALES detail data set in Figure 2-3 . The
entry for a customer named Abigail Brighton with account number
95430301 serves as an index to two entries in the SALES detail
data set which contain information about purchases she made.

Although there are unused storage locations in the CUSTOMER master data
set, TurboIMAGE/XL disallows any attempt to add another data entry with
account number 95430301. The key item value of each entry must remain
unique. The values of other data items in the master data set are not
necessarily unique because they are not key items and are not used to
determine the location of the data entry.

Figure 2-3. Master and Detail Data Sets Example

Automatic and Manual Masters.

A master data set is defined as either automatic or manual. The
characteristics of both are described below:

---------------------------------------------------------------------------------------------
| | |
| Manual Master | Automatic Master |
| | |
---------------------------------------------------------------------------------------------
| | |
| Can be standalone (not related to any | Must serve as an index to one or more |
| detail data set) or can serve as an index | detail data sets. |
| for one or more detail data sets. | |
| | |
---------------------------------------------------------------------------------------------
| | |
| Must contain a key item and can contain | Must contain only one data item, the key |
| other data items. | item. |
| | |
---------------------------------------------------------------------------------------------
| | |
| The user must explicitly add or delete all | TurboIMAGE/XL automatically adds or deletes |
| entries. A related detail data set entry | entries when needed based on the addition |
| cannot be added until a manual master data | or deletion of related detail data set |
| set entry with matching key item value has | entries. When a detail entry is added with |
| been added. When the last detail entry | a search item value different from all |
| related to a master entry is deleted, the | current key item values, a master entry |
| manual master entry still remains in the | with matching key item value is |
| data set. Before a master entry can be | automatically added. Deletions of detail |
| deleted, all related detail entries must be | entries trigger an automatic deletion of |
| deleted. | the matching master entry if it is |
| | determined that all related detail entries |
| | have been deleted. |
| | |
---------------------------------------------------------------------------------------------
| | |
| The key item values of existing master | |
| entries serve as a table of legitimate | |
| search item values for all related detail | |
| data sets. A nonstandalone manual master | |
| can be used to prevent the entry of invalid | |
| data into the related detail data sets. | |
| | |
---------------------------------------------------------------------------------------------

In Figure 2-3 , CUSTOMER is a manual master data set, and DATE-MASTER
is an automatic master data set. Before the SALES entry for account
12345678 is added to SALES, CUSTOMER must contain an entry with the same
account number. However, the DATE-MASTER entries for DATE equal to 92789
and 92889 are automatically added by TurboIMAGE/XL when the detail entry
is added to SALES unless they are already in the DATE-MASTER data set.

DATE-MASTER, an automatic master, contains only one data item which is
the key item DATE. CUSTOMER, a manual master, contains several data items
in addition to the key item.

If the SALES entry with account number 95430301 and stock number 35624AB3
are deleted, and no other SALES entry contains a PURCH-DATE or DELIV-DATE
value of 90589, the DATE-MASTER entry with that value is automatically
deleted by TurboIMAGE/XL.

Manual versus Automatic Data Sets.

Database designers can choose a manual or automatic master data set
depending on the following:

* Manual masters help ensure that valid search item values are
entered for related detail entries. They can also serve as
indexes to detail data sets.

* Automatic masters serve as indexes to detail data sets and save
time when the search item values are unpredictable or so numerous
that manual addition and deletion of master entries is
undesirable.

Whenever a single data item is sufficient for a master data set, the
database designer must decide between the control of data entry available
through manual masters and the program simplicity offered by automatic
masters. For example, because DATE-MASTER is an automatic data set,
erroneous dates such as 331299 can be accidentally entered.

Detail Data Sets

Detail data sets have the following characteristics:

* They are used to record information about related events such as
information about all sales to the same account.

* They allow retrieval of all entries pertaining to a uniquely
identifiable entity. For example, account number 95430301 can be
used to retrieve information about all sales made to Ms.
Brighton.

* They can be defined with from zero to 16 search items (unlike a
master data set which contains at most one key item). The values
of a particular search item need not be unique. Generally, a
number of entries will contain the same value for a specific
search item.[REV BEG]

* They can be defined for automatic expansion of their capacity.
[REV END]

The SALES data set contains four search items: ACCOUNT, STOCK#,
PURCH-DATE, and DELIV-DATE. Two entries in the example in Figure 2-3
have identical values for the ACCOUNT item in the SALES data set.
TurboIMAGE/XL stores pointer information with each detail data entry that
links all entries with the same search item value. Entries linked in
this way form a chain. A search item is defined for a detail data set to
retrieve all entries with a common search item value (that is, all
entries in a chain). The SALES entries with ACCOUNT equal to 95430301
form a two-entry chain. The number of entries in a single chain is
limited only by the maximum number of entries in a data set.

Paths

A master data set key item can be related to a detail data set search
item of the same type and size. This relationship forms a path. A path
contains a chain for each unique search item value. In Figure 2-3 ,
the ACCOUNT key item in CUSTOMER and the ACCOUNT search item in SALES
form a path to link the CUSTOMER master data set to the SALES detail data
set. One chain links all SALES entries for account number 95430301. The
chain for account number 12345678 consists of one entry. Both chains
belong to the same path.

Because a detail data set can contain as many as 16 search items, it can
be related to at most 16 master data sets. Each master-to-detail
relationship must be relative to a different detail search item. The
SALES data set is related to the CUSTOMER, PRODUCT, and DATE-MASTER data
sets.

A detail data set can be multi-indexed by a single master data set. For
example, SALES is indexed twice by DATE-MASTER. The DATE search item
forms one path with the PURCH-DATE search item and one path with the
DELIV-DATE search item. Each master data set can serve as an index to
one or more detail data sets. No master data set can be related to more
than 16 detail data sets. For each such relationship, TurboIMAGE/XL
keeps independent chain information with each master entry. This
information consists of pointers to the first and last entries of the
chain whose search item value matches the master data set entry's key
item value and a count of the number of entries in the chain. This is
called a chain head. The format of chain heads is given in chapter 10.
For example, the DATE-MASTER data entries each contain two sets of
pointers, one for PURCH-DATE chains and one for DELIV-DATE chains.
TurboIMAGE/XL automatically maintain the chain heads.

Primary Paths.

One of the paths of each detail data set can be designated by the
database designer as the primary path. The main reason for designating a
primary path is to maintain the entries of each chain of the path in
contiguous storage locations. To maintain contiguous locations,
occasionally use the DBUNLOAD utility program to copy the database to
tape, the DBUTIL utility program to erase the database, and the DBLOAD
program to reload the database from the tape. When the database is
reloaded, contiguous storage locations are assigned to entries of each
primary path chain. Therefore, the database designer should designate
the path most frequently accessed in chained order as the primary path.
This type of access is discussed in chapter 4.

A primary path also serves as the default path when accessing a detail
data set if no path is specified by the calling program. This
characteristic of primary paths is described with the DBGET procedure in
chapter 4.

Sort Items.

For any path, it is possible to designate a data item other than the
search item as a sort item. If a sort item is specified, each of the
chains of the path are maintained in ascending sorted order based on the
values of the sort item. Different paths can have different sort items,
and one path's sort item can be another path's search item. Only data
items of type logical or character can be designated as sort items.

For example, chains in the SALES data set composed of entries with
identical ACCOUNT values are maintained in sorted order by PURCH-DATE.
When information about sales to a particular customer is required, the
SALES data entries for that customer's account can be retrieved in sorted
order according to purchase date. PURCH-DATE is a meaningful sort item
because the dates are stored in a properly form for collating
(year-month-day).

The sorted order of entries is maintained by logical pointers rather than
physical placement of entries in consecutive records. Figure 2-4
illustrates the way that TurboIMAGE/XL maintains sorted paths. When an
entry is added to a detail data set, it is added to or inserted in a
chain. If the path does not have a sort item defined, the entry follows
all existing entries in the chain. If the path has a sort item, the
entry is inserted in the chain according to the value of that item.

If the entry's sort item value matches the sort item values of other
entries in the chain, the position of the entry is determined by an
extended sort field consisting of the sort item value and the values of
all items following the sort item in the entry. If the extended sort
field matches another extended sort field, the entry is inserted
chronologically following the other entries with the same extended sort
field value. This also occurs if the sort item is the last item in the
entry and its value matches another entry's sort item value. Note that
Native Language Support does not support extended sort items. The only
database language that supports extended sort fields is Native-3000 which
uses US ASCII. If an extended sort field is used, the sort is done in
ASCII collating sequence (negative integers sort higher than positive).

If you depend on extended sort fields to sort a chain, do not call
DBUPDATE to modify any of the values in the extended sort fields because
the chain will not be automatically resorted according to the new
extended sort data values. Instead, call DBDELETE and DBPUT to re-enter
the records with modified values. DBUPDATE only recognizes extended sort
items when the actual search or sort item is changed. Chapter 5
describes DBUPDATE, DBDELETE, and DBPUT in detail.

If you do not want TurboIMAGE/XL to sort chains by extended sort fields,
structure the data record so that the sort item is in the last field of
the record.

When the database content is copied to magnetic tape using the
TurboIMAGE/XL utility program DBUNLOAD, the pointers that define an
entry's position in a chain are not copied to the tape. When the data is
loaded back into the database, the chains are re-created. Therefore,
entries that were previously ordered chronologically will not necessarily
be in that same order. The new chronological ordering is based on the
order the entries are read from the tape. The chains of a primary path
are an exception; the order of these chains is preserved if the tape was
created with DBUNLOAD in the chained mode. See chapter 8 for more
information about DBUNLOAD.

NOTE It is important to limit the use of sorted chains to paths
consisting of relatively short chains or chronological sort items
that are usually added to the end of chain (for example, date). It
is not intended that sorted paths be used for multiple key sorts or
for sorting entire data sets. These functions are handled more
efficiently by user-written routines or the MPE/iX HP Sort
subsystem.

Figure 2-4. Adding Entries to a Sorted Chain

The ORDERS Database

Figures 2-5 and 2-6 illustrate the complete ORDERS database. Figure 2-5
lists the data items within each data set as defined in the schematic
of the ORDERS database shown in Figure 2-2 . The data types (in
parentheses) are described in chapter 3 with the item part of the schema.
Paths are indicated by arrows. CUSTOMER, SUP-MASTER, PRODUCT, and
DATE-MASTER are master data sets and SALES and INVENTORY are detail data
sets. Figure 2-6 shows a sample entry from each data set and two
sample entries for DATE-MASTER.

Chains of the path formed by CUSTOMER and SALES are maintained in sorted
order according to the value of PURCH-DATE. The primary path for
INVENTORY is the one defined by SUP-MASTER and the primary path for SALES
is the one defined by PRODUCT.

Figure 2-5. ORDERS Data Sets and Paths

Figure 2-6. A Sample Entry for Each Data Set in the ORDERS Database

MPE/iX 5.0 Documentation