POSC Specifications
Version 2.3
Relational Implementation Methodology

Implementation of Data Types

Introduction

This section describes in detail how each data type in the logical model is projected. The table columns are defined using the set of ISO SQL 92 Entry Level column data types. The SQL types are not directly equivalent to the data types used in the definition of an Epicentre style model, but can be mapped to them. There are four groups, each with increasing complexity.

Foreign Keys

Foreign keys are implemented in one of three different ways, as shown in table Foreign Key Implementation. The suffix will be applied to the projected name of the attribute. Foreign keys will be generated as part of the projection methods associated with some data types.

Table: Foreign Key Implementation
Scenario Role SQL 92 Type Suffix
Relationship to an entity that (1) becomes a table and (2) is not flagged to use a Natural Key for foreign keys. Surrogate key of record. CHAR VARYING(19) '_S'
Relationship to an entity that (1) becomes a table, (2) has a single mandatory component to the secondary identifier of type string(n) and (3) is flagged to use a Natural Key for foreign keys. Secondary identifier of the record. CHAR VARYING(n)  
Relationship to an entity that is replicated. Surrogate key of record. CHAR VARYING(19) '_S'
The name of the table containing the record. MaxTabLen is the maximum length of a projected table name. CHAR VARYING(MaxTabLen) '_T'

Simple Types

The first group, shown in table Simple Type Mapping, has a close match to a single ANSI SQL 92 column type. The column name will not have a suffix. Also included, for comparison, is the Oracle type that is used when creating Oracle DDL.

Table: Simple Type Mapping
Epicentre Data Type Type Parameter SQL 92 Type Oracle Type
STRING(n) Fixed CHAR(n) CHAR(n)
Variable CHAR VARYING(n) VARCHAR2(n)
INTEGER   INTEGER INTEGER
REAL(n)   FLOAT(n) NUMBER
LOGICAL   CHAR(1) = T or F or U CHAR(1)
BOOLEAN   CHAR(1) = T or F CHAR(1)
BINARY(n) Fixed BIT(n) LONG RAW
Variable BIT VARYING(n) LONG RAW
DATE   DATE DATE
TIME(n)   TIME(n) DATE
YEARMONTHINTERVAL   INTERVALYEAR(5) TO MONTH INTEGER
DAYTIMEINTERVAL   INTERVALDAY(5) TO SECOND INTEGER
ENUMERATION(n)   CHAR VARYING(n) VARCHAR2(n)

Compound Types

The second group of Epicentre data types is represented as two or three columns, distinguished by a column name suffix. Each column within the group is required (i.e., if one column is populated then all columns must be populated). The optionality of the group is defined by the logical attribute. These types and their column types and suffices are shown in table Compound Type Mapping.

Table: Compound Type Mapping
Epicentre Data Type Column role SQL 92 Type Suffix
COMPLEX(n) Real part FLOAT(n) `_R'
Imaginary part FLOAT(n) `_I'
RATIONAL Numerator part INTEGER `_N'
Denominator part INTEGER `_D'
RATIO(n) Numerator part FLOAT(n) `_N'
Denominator part FLOAT(n) `_D'
QUANTITY(a,n) Numerical part FLOAT(n)  
Foreign key to the table that represents Ref_unit_of_measure. Projection dependent `_U'
ANGLE(a,n) Numerical part. FLOAT(n)  
Minute (angular) of degree INTEGER `_MA'
Second (angular) of minute FLOAT(n-5) `_SA'
Foreign key to the table that represents Ref_unit_of_measure (for numerical part). Projection dependent `_U'
MONEY Numerical part FLOAT(n)  
Foreign key to the table that represents Ref_unit_of_measure. Projection dependent `_U'
ANYQUANTITY(n) Numerical part FLOAT(n)  
Foreign key to the table that represents Ref_unit_of_measure. Projection dependent `_U'
Foreign key to the table that represents Ref_quantity_type. Projection dependent `_Q'

Note that, for foreign key columns generated as part of a column group, the foreign key suffix will be appended to the type suffix. For example, if the unit of measure entity was replicated into its subtypes then a QUANTITY type would result in three columns: one with no suffix, one with a suffix of '_U_S' and one with a suffix of '_U_T'. Thus, fewer columns will be generated if these required entities are projected as tables.

Complex Types

The remaining data types represent types with multiple components with optional parts within the group. The table, Complex Type Mapping, illustrates these data types.

Table: Complex Type Mapping
Epicentre Data Type Column role SQL 92 Type Suffix Optionality within group
POINT This column holds the representation type used for the instance. The column is always optional because all required information is retained within the supplemental library (SL). The associated aggregate values are held in binary structures defined as separate supplemental or frame tables. Access to this data is so complex that software methods should be used for access. The use of the supplemental or frame tables is described in the Data Access and Exchange Specification. CHAR VARYING(16) '_SL' NOT NULL
LINE
SURFACE
VOLUME
ELEMENT
SAMPLE
TIMESTAMP(n) Full precision value. The other components allow one to specify what was actually known. TIMESTAMP(n)    
Content code of timestamp. Must be one of "G", "C", "D", "M", "W" or "Q". CHAR VARYING(n) _CD  
Year. Depending on the value of the content code, this may be the geologic age, the century only or the year (including century). FLOAT(6) _YR  
Year Part. Depending on the value of the content code, this may be the quarter, month or week. INTEGER _YP  
Day. Depending on the value of the content code, this may be the day-of-year, day-of-month or day-of-week. INTEGER _DY  
Hour of Day. INTEGER _HR  
Minute (time) of Hour. INTEGER _MT  
Second (time) of Hour. FLOAT(n) _ST  
Time Zone Offset in hours (to the nearest minute) from UCT. FLOAT(n) _OF  
LOCATION(n) Coordinate system. Foreign key to the table that represents entity Coordinate_system. Projection dependent '_CS' NOT NULL
Origin of coordinate system. Foreign key to the table that represents entity Vertex. Projection dependent '_O'  
Axis "i" (i = 1 through 5) Coordinate value FLOAT(n) '_i' NOT NULL (i=1 only)
Coordinate minute (angular) value INTEGER '_i_MA'  
Coordinate second (angular) value FLOAT(n) '_i_SA'  
Coordinate unit. Foreign key to the table that represents Ref_unit_of_measure. If units of angular degree then minute and second can be specified. Projection dependent '_i_U' NOT NULL (i=1 only)

Exceptions

In addition to the above basic types, there a few exceptions that override the method assigned to the underlying type. Table Exceptions Mapping illustrates these data types.

Table: Exceptions Mapping
Exception Implementation
ndt_location_1d This is implemented exactly like LOCATION except that only one axis is represented (i.e., i = 1).
ndt_location_2d This is implemented exactly like LOCATION except that only two axes are represented (i.e., i = 1 and 2) and all "_i" and "_i_U" columns are required.
ndt_location_3d This is implemented exactly like LOCATION except that only three axes are represented (i.e., i = 1, 2 and 3) and all "_i" and "_i_U" columns are required.
LIST OF REAL(n) This is implemented by creating a table that has the attribute's projected name with an '_A' (for aggregate) suffix. For example, attribute XXX will result in table XXX_A. The table will contain:
  • A mandatory foreign key back to the record that owns the LIST OF REAL value. The base name of the column will be the projected name of the parent entity.
  • One mandatory column named VALUE of type FLOAT(n).
  • One mandatory column named VALUE_L of type INTEGER. This represents the list index for VALUE.
LIST OF STRING(n) This is implemented exactly as LIST OF REAL except that VALUE will be of type CHAR VARYING(n) instead of FLOAT(n). Note that for multiple attributes of type LIST OF REAL or type LIST OF STRING with the same name, an alternative projected name will need to be provided so there is not a name clash.

© Copyright 1997-2001 POSC. All rights reserved.