POSC Specifications
Version 2.3
Relational Implementation Methodology

Projection Operations

Introduction

Projection operations are those operations that transform a logical model, either full or subset, into a physical data base schema (creating DDL in the case of a relational implementation).

At the most basic level, taking a logical data model described in terms of entities, attributes and relationships and converting it into DDL is a fairly standard operation that can be done by many CASE tools. In the standard CASE tool approach, an entity becomes a table, an attribute becomes a column, a one-to-one or one-to-many relationship becomes a foreign key column and a many-to-many relationship becomes an intersection table. In most cases, the DDL produced in this way is then edited by a database administrator to produce a physical schema that will perform acceptably for the suite of applications supported.

This manual approach to producing the DDL is not sufficient in Epicentre's case for three reasons.

  1. The large size of Epicentre precludes hand editing operations.
  2. The mapping of the logical model into the DDL needs to be recorded as meta data in a meticulous, error-free way to build a data access layer that works on the physical schema and conforms to the POSC Data Access and Exchange (DAE) specifications.
  3. Epicentre has constructs (e.g., multiple class inheritance and complex data types) that are not common to standard E-R modeling methodology.

Thus, it is necessary to create projection algorithms that can be applied to the Epicentre logical model in an automatic, reproducible manner to guarantee that:

  1. the DDL produced correctly represents the logical model semantics;
  2. the physical schema's mapping to the logical model is recorded as valid meta data.

The remainder of this section defines the fundamental operations of the POSC Projection Tool that can be applied to any model that conforms to the POSC Epicentre Modeling Methodology.

Definition of Model Levels

Within this process there is only one logical model; no intermediate logical model is produced.

The Deliverable

The result of the projection is a set of data definition language statements (DDL), projection meta data (conforming to the POSC Projection Meta Model) that defines the mapping from the logical model to the physical schema and the table documentation files.

Fundamental Operations

Standard Operations

The traditional projection approach of an entity becoming a table and an attribute becoming a column, a one-to-many relationship becoming a foreign key column and a many-to-many relationship becoming an intersection table is followed where appropriate.

Supertypes and Subtypes

The logical type hierarchy in an Epicentre style model has to be managed in the projection. As a result, not every entity is projected into a relational table.

At the top of the hierarchy, some supertypes are not converted into tables and, therefore, disappear. Nevertheless, their behavior (attributes and relationships) is transferred by inheritance to their subtypes. This operation is replication.

At the bottom of the hierarchy, some entities are not converted into tables. In such cases, their behavior is consolidated in their supertypes. This operation is consolidation. Mandatory attributes that are consolidated will be changed to optional in the projected model. Note: even though the resulting columns are optional, applications and users are still obligated to treat them as mandatory when creating or modifying an instance which logically requires them to be mandatory. If multiple attributes from consolidated entities have the same data type and the same projected name, they will be merged under the assumption that they have the same semantics. That is, a single column or a single column group will represent all of the duplicate attributes. During consolidation, a mandatory column (ENTITY_TYPE_NM) is created whose value is constrained be the name of a consolidated subtype.

The decisions about the projection of the hierarchy are very data dependent and are made for each specific model. The projection of the hierarchy, as illustrated in figure 3, is one of the adjustable parameters in the projection process.

Projection of Supertype and Subtype Behavior

Primary and Secondary Keys

Surrogate keys are used as the primary keys to uniquely identify a record. The use of surrogate keys is very important in a complex model because the natural key can be complex. Surrogate keys, however, are not intended to be written into external files or be used for data exchange between data stores. The following rules apply to the natural and surrogate keys:

Surrogate Key Structure

The surrogate key must be unique for each table and is intended to be unique for a data store. The key structure is alphanumeric and has a maximum size of 19 characters. It is strongly suggested that a surrogate key generator be utilized by each data store. The recommended process of generating surrogate keys is a combination of a unique table reference and a sequence number.

Foreign Key

Relationships are implemented using a foreign key. A foreign key will be defined using a surrogate key with the following exceptions:

Relationships defined by a single column foreign key are enforced by referential integrity constraints.

Naming Conventions

Names are specific to each logical model and are not controlled by this document, with the following exceptions:

This document does not specify how names are shortened. It is recommended that names be shortened in a consistent manner. Alternate projected names are a projection parameter for both entities and attributes. If a name is longer than the above constrained lengths, then a shorter name will automatically be generated by truncating the name and adding "_0001", "_0002", etc. It is not recommended that these names be used (i.e., an alternate name should be specified).

Aggregates

One-to-many relationships are handled in the traditional manner by adding a foreign key column(s) in the table on the many side. Note that if an inverse attribute is not defined then an aggregate of SET[0:?] will be assumed for the inverse.

Many-to-many relationships are handled by creating an intersection table that has mandatory foreign key columns to the related tables. The default name for a generated intersection table is the projected name of the forward relationship in the logical model plus a suffix of "_X".

Ordered aggregates such as LIST and ARRAY will result in the creation of a column to capture the order. The name of this column will have a suffix of "_L".


© Copyright 1997-2001 POSC. All rights reserved.