| Previous | Table of Contents | Next |
The [SQL] standard adds object-oriented notions to SQL with structured types.
A structured type is defined in terms of columns, as illustrated in the following example: CREATE TYPE person_t AS(name varchar(20),
birthyear integer). Since a SQLStructuredType is a Classifier that owns Attributes, it is natural to associate an SQLStructuredType
to a set of Columns. Similarly, to represent a type created by CREATE TYPE emp_t UNDER person_t AS(salary integer). We use
the ObjectModel Generalization to associate the two types. As a result, the following instances are created to represent the
above two examples.
Person_t : S Q LS truc turedTy pe
name : Column varchar : S Q LS im pleTy pe
birthyear : Colum n
: G eneraliz ation
chil d
integer : SQ LS im pleTy pe Emp_t : S Q LS truc turedTy pe
salary : Column
Figure 6-4 Instance diagram for two structured types
An association between Column and SQLStructuredType (ColumnRefStructuredType) has been added to represent structured type
attributes that reference another type, as in CREATE TYPE dept_t AS (name varchar(40), mgr REF (emp_t). This leads to the
following instance diagram:
Dept_t :SQLStructuredType
name : Column varchar : SQLSimpleType
mgr : Column Emp_t : SQLStructuredType
Figure 6-5 Instance diagram for a structured type containing a REF clause
A structured type can be used as the data type of a column, but also as a template for a table, as in CREATE TABLE person
OF person_t(ref is oid user generated) or CREATE TABLE emp OF emp_t UNDER person. In these cases, the table will be created
with columns that copy the content of the structured type, as described in the [SQL] standard. This allows programs that do
not understand the object extensions to still work with the table, both at the data and metadata level. However, an association
between the Table (this applies to views as well) and the SQLStructuredType allows the user of the model to remember which
template was used to create the table. It is the responsibility of the application using the model to keep the SQLStructuredType
and
the Table list of columns synchronized. Figure 6-6 represents the examples above:
Person : Table ColumnSetOfStructuredType Person_t : SQLStructuredType
salary : Column
parent parent
birthyear : birthyear : :: Column
Generalization Column child
child Generalization
ColumnSetOfStructuredType Emp_t :Emp : Table SQLStructuredType
salary : Column
salary : Column
Figure 6-6 Instance diagram for typed tables
Finally, when a table (or a column) uses a structured type with a reference to another structured type, the reference is mapped
to a table or view of the corresponding structured type, using the options scope clause. This represents an association between
the column of the table or view with another table or view. This is modeled by the ColumnOptionsTable between a Column and
a NamedColumnSet in CWM. For example, the statement CREATE TABLE dept OF dept_t (ref is oid user generated, mgr WITH OPTIONS
SCOPE emp) would be represented by the following:
Dept : Table ColumnSetOfStructuredType Dept_t :
SQLStructuredType
oid : Column name : Column name : Column
mgr : Column mgr : Column ColumOptionsTable
ColumnSetOfStructuredType Emp_t :
Emp : Table
SQLStructuredType
Figure 6-7 Instance diagram showing the use of Options Scope clauseIn summary, the SQLStructuredType has the following associations:
Column
optionScopeColumnSet NamedColumnSet optionScopeColumn
/feature columnSet
referencingColumn
{ordered}
/owner SQLStructuredType type
ref erencedTableType
Figure 6-8 SQLStructuredType and its associations
