Database hierarchy examples

Medical hierarchy example

The medical database shown in following figure contains information that a medical clinic keeps about its patients. The hierarchies used in the medical hierarchy example are used with full-function databases and Fast Path data entry databases (DEDBs).

Figure 1. Medical hierarchy

Each piece of data represented in the figure above is called a segment in the hierarchy. Each segment contains one or more fields of information. The PATIENT segment, for example, contains all the information that relates strictly to the patient: the patient's identification number, name, and address.

Definitions: A segment is the smallest unit of data that an application program can retrieve from the database. A field is the smallest unit of a segment.

The PATIENT segment in the medical database is the root segment. The segments below the root segment are the dependents, or children, of the root. For example, ILLNESS, BILLING, and HOUSHOLD are all children of PATIENT. ILLNESS, BILLING, and HOUSHOLD are called direct dependents of PATIENT; TREATMNT and PAYMENT are also dependents of PATIENT, but they are not direct dependents, because they are at a lower level in the hierarchy.

A database record is a single root segment (root segment occurrence) and all of its dependents. In the medical example, a database record is all of the information about one patient.

Definitions: A root segment is the highest-level segment. A dependent is a segment below a root segment. A root segment occurrence is a database record and all of its dependents.

Each database record has only one root segment occurrence, but it might have several occurrences at lower levels. For example, the database record for a patient contains only one occurrence of the PATIENT segment type, but it might contain several ILLNESS and TREATMNT segment occurrences for that patient.

The tables that follow show the layouts of each segment in the hierarchy.

The segment’s field names are in the first row of each table. The number below each field name is the length in bytes that has been defined for that field.

• PATIENT Segment

  The following table shows the PATIENT segment.

  It has three fields:

  • The patient’s number (PATNO)
  • The patient’s name (NAME)
  • The patient's address (ADDR)

  PATIENT has a unique key field: PATNO. PATIENT segments are stored in ascending order based on the patient number. The lowest patient number in the database is 00001 and the highest is 10500.

  Table 1. PATIENT segment

  Field name	Field length
  PATNO	10
  NAME	5
  ADDR	30

• ILLNESS Segment

  The following figure shows the ILLNESS segment.

  It has two fields:

  • The date when the patient came to the clinic with the illness (ILLDATE)
  • The name of the illness (ILLNAME)

  The key field is ILLDATE. Because it is possible for a patient to come to the clinic with more than one illness on the same date, this key field is non-unique, that is, there may be more than one ILLNESS segment with the same (an equal) key field value.

  Usually during installation, the database administrator (DBA) decides the order in which to place the database segments with equal or no keys. The DBA can use the RULES keyword of the SEGM statement of the DBD to specify the order of the segments.

  For segments with equal keys or no keys, RULES determines where the segment is inserted. Where RULES=LAST, ILLNESS segments that have equal keys are stored on a first-in-first-out basis among those with equal keys. ILLNESS segments with unique keys are stored in ascending order on the date field, regardless of RULES. ILLDATE is specified in the format YYYYMMDD.

  Table 2. ILLNESS segment

  Field name	Field length
  ILLDATE	8
  ILLNAME	10

• TREATMNT Segment

  The following table shows the TREATMNT segment.

  It contains four fields:

  • The date of the treatment (DATE)
  • The medicine that was given to the patient (MEDICINE)
  • The quantity of the medicine that the patient received (QUANTITY)
  • The name of the doctor who prescribed the treatment (DOCTOR)

  The TREATMNT segment’s key field is DATE. Because a patient may receive more than one treatment on the same date, DATE is a non-unique key field. TREATMNT, like ILLNESS, has been specified as having RULES=LAST. TREATMNT segments are also stored on a first-in-first-out basis. DATE is specified in the same format as ILLDATE—YYYYMMDD.

  Table 3. TREATMNT segment

  Field name	Field length
  DATE	8
  MEDICINE	10
  QUANTITY	4
  DOCTOR	10

• BILLING Segment

  The following table shows the BILLING segment. It has only one field: the amount of the current bill. BILLING has no key field.

  Table 4. BILLING segment

  Field name	Field length
  BILLING	6

• PAYMENT Segment

  The following table shows the PAYMENT segment. It has only one field: the amount of payments for the month. The PAYMENT segment has no key field.

  Table 5. PAYMENT segment

  Field name	Field length
  PAYMENT	6

• HOUSHOLD Segment

  The following table shows the HOUSHOLD segment.

  It contains two fields:

  • The names of the members of the patient's household (RELNAME)
  • How each member of the household is related to the patient (RELATN)

  The HOUSHOLD segment’s key field is RELNAME.

  Table 6. HOUSHOLD segment

  Field name	Field length
  RELNAME	10
  RELATN	8

Bank account hierarchy example

The bank account hierarchy is an example of an application program that is used with main storage databases (MSDBs). In the medical hierarchy example, the database record for a particular patient comprises the PATIENT segment and all of the segments underneath the PATIENT segment. In an MSDB, such as the one in the bank account example, the segment is the whole database record. The database record contains only the fields that the segment contains.

The two types of MSDBs are related and nonrelated. In related MSDBs, each segment is owned by one logical terminal. The "owned" segment can only be updated by the terminal that owns it. In nonrelated MSDBs, the segments are not owned by logical terminals. The following examples of a related MSDB and a nonrelated MSDB illustrate the differences between the two types of databases.

The following table shows what the segment for this type of application program looks like.

Some of the characteristics of fixed related MSDBs include:

• You can only read and replace segments. You cannot delete or insert segments. In the bank teller example, the teller can change the number of transactions processed, but you cannot add or delete any segments. You never need to add or delete segments.
• Each segment is assigned to one logical terminal. Only the owning terminal can change a segment, but other terminals can read the segment. In the bank teller example, you do not want tellers to update the information about other tellers, but you allow the tellers to view each other’s information. Tellers are responsible for their own transactions.
• The name of the logical terminal that owns the segment is the segment's key. Unlike non-MSDB segments, the MSDB key is not a field of the segment. It is used as a means of storing and accessing segments.
• A logical terminal can only own one segment in any one MSDB.

The following table shows what the branch summary segment looks like in a dynamic related MSDB.

How dynamic related MSDBs differ from fixed related MSDBs:

• The owning logical terminal can delete and insert segments in a dynamic related MSDB.
• The MSDB can have a pool of unassigned segments. This kind of segment is assigned to a logical terminal when the logical terminal inserts it, and is returned to the pool when the logical terminal deletes it.

The following table shows what the account segment in a nonrelated MSDB application program looks like.

The characteristics of nonrelated MSDBs include:

• Segments are not owned by terminals as they are in related MSDBs. Therefore, IMS programs and Fast Path programs can update these segments. Updating segments is not restricted to the owning logical terminal.
• Your program cannot delete or insert segments.
• Segment keys can be the name of a logical terminal. A nonrelated MSDB exists with terminal-related keys. The segments are not owned by the logical terminals, and the logical terminal name is used to identify the segment.
• If the key is not the name of a logical terminal, it can be any value, and it is in the first field of the segment. Segments are loaded in key sequence.