Databases Reference
In-Depth Information
effectively a user-defined data type with a nonencapsulated internal structure
(consisting of its fields). A named row type can be used to specify the types of
rows in table definitions. A named row type can also be used to define a refer-
ence type.
A value for a reference type defined for a specific row type is a unique
value that identifies an instance of the row type in question within some base
(top-level) DB table. A reference-type value can be stored in one table and
used as a direct reference (pointer) to a specific row in another table. That
translates directly to navigational access as represented in the object paradigm
whereby links are initiated. The same reference-type value can be stored in
multiple rows, thus allowing the referenced row to be shared by those rows.
For example, an accounting table with account_t as a row type contains a cust
column with the reference type REF(customer_t). A value of this column
identifies a specific row of type customer_t. The reference type has three
important characteristics: (1) The value of a reference type is unique within
the DB; (2) it never changes as long as the corresponding row exists in the
DB; and (3) the reference type value is never reused.
Reference types are an important functionality in the object-relational
system for the following reasons:
Set referencing. The first normal form can be violated, and a tabular
column cell can contain a pointer to repeating values. The true
benefit of this approach is that prebuilt aggregations can be created,
simplifying DB design in the long run.
·
Accessing nondatabase objects in a flat file. The importance of multi-
media objects in new application systems was discussed earlier in
this chapter. Large object (LOB) data types can be stored in the DB
or on the file server. Pointers supply the means to effectively access
these constructs.
·
Data relationships without referential foreign keys. Utilization of
pointers obviates the need for conventional SQL JOIN operations
because each column instance references the object table containing
the necessary aggregate data.
·
The introduction of reference types allows us to employ collection types.
Collection types are aggregations that appear as sets, lists, and multisets.
Using these types, columns of tables can contain sets, lists, or multisets, in
addition to individual values. Currently in SQL: 1999, a table can be defined
