Choosing Physical Tuning Options › Physical Tuning Options for Commonweather Corporation › Refined Commonweather Corporation Database Design (For Non-SQL Implementation)

Refined Commonweather Corporation Database Design (For Non-SQL Implementation)

Additional non-SQL physical tuning options chosen for the Commonweather Corporation database design are discussed below.

Create a multimember relationship

Since the COVERAGE, HOSPITAL CLAIM, NON-HOSPITAL CLAIM, and DENTAL CLAIM entities are usually processed together, we can create a multimember relationship to relate these entities. Let's call this relationship COVERAGE-CLAIMS.

Variable-length entities

Several entities in the Commonweather database should be converted to repeating elements in variable-length entities. The NON-HOSPITAL PROCEDURE and DENTAL PROCEDURE entities should be made variably-repeating elements because they each participate in only one relationship.

• The NON-HOSPITAL PROCEDURE entity can be converted to a repeating element in the NON-HOSPITAL CLAIM entity.
• The DENTAL PROCEDURE entity can be converted to a repeating element in the DENTAL CLAIM entity.

Add new entity

Because an employee must be managed by another existing employee, the integrity of the MANAGES-REPORT TO relationship must be ensured. In order to accomplish this in a non-SQL implementation, a new entity (STRUCTURE) and two relationships (REPORT-TO and MANAGES) must be created. Indicate the appropriate relationship options to ensure that an employee is associated with an existing employee. The MANAGES relationship is sorted to enforce unique constraints. (If it were not a sorted relationship, an index would have to be created to enforce uniqueness.)

Remove unnecessary keys

Remove foreign keys if SQL access is not a priority. If you choose to remove unnecessary keys, adjust the entity lengths accordingly.

Relationship options

Choose linkage and membership options for linked relationships. Choose ordering option of each nonsorted relationship.

Duplicates options

Duplicates options for indexes and sorted relationships were chosen based on application requirements.

The diagram below could be used to implement the database using a non-SQL definition.

The diagram shows:

• A multimember set
• Variable-length entities
• Removal of foreign keys as reflected in new entity lengths

  

  

Since the design shown above will satisfy the performance requirements of the Commonweather Corporation, this diagram will be used in later chapters of this manual as the basis for performing sizing calculations and a final database design review.