POSC Specifications Version 2.3 |
Relational Implementation Methodology |
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 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.
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.
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) |
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.
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.
The remaining data types represent types with multiple components with optional parts within the group. The table, Complex Type Mapping, illustrates these data types.
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) |
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.
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:
|
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. |