ILLUSTRATIVE Application System EAS DOCUMENTATION

Excel Spreadsheet with Illustrative EAS Documentation:

• EAS Documentation File - Left Click To View. You may wish to copy to a new Excel book

The above Excel file contains an extract from the Entity-Attribute-Set (EAS) documentation of a complex database system used in the management of 401(k) retirement plans. A previous attempt to document an earlier version of this application system, using arrows to represent 1-1, 1-n, n-1 and m-n relationships, was exceedingly cumbersome because of a large number of criss-crossing arrows. For our present purposes we have extracted four (of 20) pages worth of entities represented in the system, to illustrate the use of the EAS table format. We will not attempt to explain all entries in the EAS table. Our objective, rather, is to explain enough to illustrate the modeling of a complex system in terms of Entities, Attributes and Sets, and the use of an EAS table to record such a view of the application system.

This EAS table (from which our example was extracted) proved extremely valuable for reviewing precisely how a particular aspect of the application system was modeled during the design of the system, its implementation, and subsequent review and augmentation. Considerable discipline, however, is required to keep the documentation and implementation synchronized, as compared to the SIMSCRIPT [I] Definition Form which was both documentation and “program.”

While the application system was modeled in EAS terms (sometimes also referred to as the “network” view of database programming; see Date ( ) the application was implemented using a relational database manager, since that was what was commercially available for large, critical applications. In such an implementation, entity types are represented by relational tables, individual entities by rows in tables, and attributes by columns.   Sets can be represented in different ways depending on the need to retrieve members efficiently. The simplest (and least efficient) is to write the owner’s name or ID as an attribute (datafield) of the member. To obtain all members of the set associated with a given owner, select all members with the particular attribute equal to the owner’s name or ID.A more efficient way is to store a list of the primary key IDs of the members of the set in a long text attribute of the owner.

The first column of the EAS Documentation File indicates the contents of each row. Specifically, in each row the entry in the first column is either:

   ENT  the row presents an entity type;
   
   ATT  row describes an attribute of the previously mentioned entity type;

   SET  the row describes a set owned by the entity type;

   COM  the row contains a comment.

Names of entity types start in the second column of the table, and may spread over subsequent columns. Names of attributes start in the third column of the table and may spread to subsequent columns. Set names are listed in the fourth column of the table.

The fifth column of the table contains the entity types – either user defined or system defined types – of the members of the set listed in Column 4, or of the value of the attribute listed in Column 3.   For example, the first entity type listed in the table is “The_SYSTEM”   representing the application system as a whole.   The last line of information noted for The_SYSTEM is that it owns a set called Fed_tax_brackets. Column 5 indicates that the members of this set are entities of type Inc_tax_bracket.   Glance down column 2 to find Inc_tax_bracket, the indicated entity type. We see that attributes of Inc_tax_bracket include From_income, To_income and Marginal_rate_pct. yes">  These attributes record the start and end of the tax bracket and the marginal rate within it.

The_SYSTEM owns 16 sets.   Some of these contain types of entities which are not included in the extract. Examples of types of entities in sets owned by The_SYSTEM include Sponsors (companies that provide the 401(k) plans), Record_Keepers who keep track of the official information in the plan for the individual participants, and Event_log_entry contained in The_SYSTEM owned set, Transaction_log_book.   The eighth set owned by The SYSTEM is named Partcpnt_auth_memos for “participant authorization memos”, and contains entities of type “Authorization_memo”. Authorization_memo is the fourth entity type listed in the extract.

It is often convenient to know which other entity types refers to a given entity type, either as the value of an attribute or the member of a set. Towards this end, the last column of the documentation table, under the heading “comment/cross references,” includes the entity types which reference a given type.   In particular, we see on the ENT line of Authorization_memo, that entities of this type are members of a set owned by The_SYSTEM and by Person.   In Column 3 we see that attributes of Authorization_memo include its own ID, a Social Security number, a participant ID, the participant’s PIN number, a question which the participant has supplied and the participant’s answer to this question, a flag indicating whether the PIN can be changed interactively (permitted by some plan sponsors and not others), and the date of the last PIN change.

The number of characters in text variables is indicated in the next to the last column of the documentation table.

The fifth type of entity listed (on page 2 of the extract) is Person. Under cross references we see that Person is pointed to (as the value of an attribute) by entities of type Person, Authorization_memo, GC_case, Session, Transaction_log_entry, Event_log_entry, Error_log_entry, and Expenses_worksheet.   “II” indicates the start of cross references due to attributes pointing rather than sets containing.   “>” on lines following the ENT line indicate that cross references are continued.

Attributes of Person include an internal ID, first name, last name, middle initial, birth date, Social Security number, gender, Contact_info (a many-attribute entity type not shown here), country of legal residence, etc. “Spouse” is an attribute of Person whose value, if not null, is another Person.   Person owns a set named Sign_ons.   This set contains entities of type Authorization_memo, discussed above.   A second set, named Dependents, includes entities of type Dependent.   The attributes of Dependent are listed as the next entity-type. Other sets owned by Person include, as members, entities of type Account, Portfolio and Planned_Disbursement.

On the same page of the extract we see that a planned disbursement has, as one of its attributes, the Dependent for which this disbursement is to be made. The comment indicates that the entry should be zero if not for college.   Other attributes indicate Period (monthly, quarterly, semi-annual or annual), Amount, Start_date and End_date for the planned disbursement.

On page 3 we see that entities of type Account own eight sets including ones named Beneficiaries, Portfolios and Archived_cases.   The members of these sets are, respectively, entities of type Beneficiary, Portfolio and GC_case.

Entities of type Portfolio are described next in the extract. yes">  Entities of this type own a set called Positions whose members are of type Position.   Entities of type Position are described next.   Each Position remembers, as values of its attributes, its own ID and those of the Security, Portfolio and Person involved.   Attributes also remember quantity and value.

SIMSCRIPT [I] and II and [TJW]EAS-E refer to the Cartesian product of two or more entity types as a “compound entity”.   Compound entities appear frequently in the total documentation for the application system discussed here, but not often in the extract. yes">  The only instance is that of a (Person, Comp_type) combination referred to in the extract as Person_Comp_type. yes">  This compound entity remembers, as values of its attributes, the account and the compensation type involved, as well as other information. Compensation type, documented just below in the extract, belongs to sets owned by three entity types, and pointed at as attributes of two entity types.   The entity type Eligible_comp_type (ECT, for short) is used to remember which compensation types are included in some aspect of a 401(k) plan. yes">  The Eligible_comp_type is itself pointed at by a compound entity PCH_ECT that represents a combination of a plan, a tax category, a “highly-compensated-employee-or-not” flag, and an ECT. This compound entity, not shown in the extract, includes plan information such as contribution limits and company matching rules.

A 401(k) system with its sponsors, record keepers, participants, accounts, portfolios, positions, and so on is a complex application system. yes">  We found that an EAS table, part of which is presented here, is quite serviceable in keeping track of the database design, and guiding its implementation.   In particular, this documentation was vastly clearer than the entity-relationship m-n diagrams used to document an earlier draft of the system.