Comparison of hierarchical and relational databases

The following information describes the differences between the hierarchical model for IMS databases and the standard relational database model.

A database segment definition defines the fields for a set of segment instances similar to the way a relational table defines columns for a set of rows in a table. In this way, segments relate to relational tables, and fields in a segment relate to columns in a relational table.

The name of an IMS segment becomes the table name in an SQL query, and the name of a field becomes the column name in the SQL query.

A fundamental difference between segments in a hierarchical database and tables in a relational database is that, in a hierarchical database, segments are implicitly joined with each other. In a relational database, you must explicitly join two tables. A segment instance in a hierarchical database is already joined with its parent segment and its child segments, which are all along the same hierarchical path. In a relational database, this relationship between tables is captured by foreign keys and primary keys.

This section compares the Dealership sample database, to a relational representation of the database. The Dealership sample DBDs are available with the IMS Enterprise Suite Explorer for Development, in the <installation location>\IMS Explorer samples directory.

Important: This information provides only a comparison between relational and hierarchical databases.

The Dealership sample database contains five segment types, which are shown in the following figure. The root segment is the Dealer segment. Under the Dealer segment is its child segment, the Model segment. Under the Model segment are its children: the segments Order, Sales, and Stock.

The following figure shows the structure and each segment of the Dealership sample database.

Figure 1. Segments of the Dealership sample database
Begin figure description. This figure is described in the surrounding text. End figure description.

The Dealer segment identifies a dealer that sells cars. The segment contains a dealer name in the field DLRNAME, and a unique dealer number in the field DLRNO.

Dealers carry car types, each of which has a corresponding Model segment. A Model segment contains a type code in the field MODTYPE.

Each car that is ordered for the dealership has an Order segment. A Stock segment is created for each car that is available for sale in the dealer's inventory. When the car is sold, a Sales segment is created.

The following figure shows a relational representation of the IMS database record shown in Figure 1.

Figure 2. Relational representation of the Dealership sample database
Begin figure description. This figure is described in the surrounding text. End figure description.

If a segment does not have a unique key, which is similar to a primary key in relational databases, view the corresponding relational table as having a generated primary key added to its column (field) list. An example of a generated primary key is in the Model table (segment) of the figure above. Similar to referential integrity in relational databases, you cannot insert, for example, an Order (child) segment to the database without it being a child of a specific Model (parent) segment.

Also note that the field (column) names have been renamed. You can rename segments and fields to more meaningful names by using the IMS Explorer for Development.

An occurrence of a segment in a hierarchical database corresponds to a row (or tuple) of a table in a relational database.

The following figure shows three Dealership database records.

Figure 3. Segment occurrences in the Dealership sample database
Begin figure description. Dealer segments, which have the fields DealerNumber and DealerName, have child segments named Model. The Model segment has the field ModelTypeCode. End figure description.

The Dealer segment occurrences have dependent Model segment occurrences.

The following figure shows the relational representation of the dependent model segment occurrences.

Figure 4. Relational representation of segment occurrences in the Dealership database
Begin figure description. A Dealer table, which has the columns DealerNumber and DealerName, has a related table named Model. The Model table has the column ModelTypeCode. End figure description.

In the following example that shows the SELECT statement of an SQL call, Model is a segment name that is used as a table name in the query:

SELECT * FROM Model

The following example, ModelTypeCode is the name of a field that is contained in the Model segment and it is used in the SQL query as a column name:

SELECT * FROM Model WHERE ModelTypeCode = '062579'

In the two preceding examples, Model and ModelTypeCode are alias names that are assigned with the EXTERNALNAME parameter of the SEGM and FIELD statements in the DBD, respectively. The EXTERNALNAME parameter is an optional parameter that specifies an external alias name for client applications to use when referencing the field or segment, and does not need to conform to the 8-character limit for host resource names. External alias names are only used when the IMS catalog is active. If the IMS catalog is active but no alias name is specified on the EXTERNALNAME parameter for a segment or field, use the 8-character IMS name for the resource instead.