Object and User-Defined Data Types

You are already familiar with the concept of complex data types (date and time), but their complexity is hidden from the users. Now, we are going to talk about some complex data types that do require more user involvement. Understanding these data types requires some background in object-oriented programming and design.

SQL99

In general, SQL99 defines two new groups of complex data types: Abstract types (ADT) and collections.

ADT

ADT provides means to explicitly define a structural type within the larger context of the database structure. It is a series of attribute names paired with system data types plus optional methods to manipulate those properties. ADT can virtually be anything one can imagine and describe by the means of the language — a car, a person, an invoice.

Note 

We already mentioned in Chapter 1 that object-oriented design and principles are very popular today. All modern computer languages (Java, C#, VB.NET) are strictly based on those principles. Nevertheless, we would like to remind you that object-oriented databases are hardly mainstream; their market share is marginal at best, and their future existence is still questionable. Major database vendors seem to prefer embedding some object-oriented features into their existing RDBMS engines (OORDBMS approach). We are not going to list all pros and contrast once again, but in our opinion the major advantage of traditional relational databases is they are based on strong mathematical and theoretical platforms and time-proven standards.

Collections

Collections can be of reference type, set type, and list type.

Reference type

Reference type is a special type through which an instance of another type can be referenced. The reference values are read-only and unique in database system catalog.

Set and list types

These types extend a field's capabilities by storing a collection of homogeneous data. For instance, we could make a good use of array data type for a phone number field.

Note 

Until recently, phone numbers didn't cause database programmers and designers any troubles. It was conventional for a person to have only one home number and a work number. The situation has dramatically changed in recent years. Cell phones are now as popular as TVs, VCRs, and watches. Many people even have more than one cell phone! That is not such a big deal for a correctly designed relational database — we simply can store phone numbers in a separate table with pointers to their parent. But using an array data type would be another elegant solution in this situation.

Sets and lists can consist of built-in data types (for example, array of strings), abstract data types, reference data types, or named row data types. The only difference between the two collection types is that the list has to be ordered.

Oracle 9i

Oracle supports ADT in the form of object types. Collections are supported as nested tables, varying arrays, and ref types.

Objects

Objects in Oracle can either simply describe an entity or have some methods (functions) to manipulate it in addition to that. We can create an object in Oracle using this syntax:

CREATE TYPE addr_type AS OBJECT   ( street VARCHAR(30), city VARCHAR(30), state CHAR(2), zip VARCHAR(10), country   VARCHAR(30) type CHAR(1));

We can now create table CUST1 with address field of type ADDR_TYPE:

CREATE TABLE cust1 ( id NUMBER,   name VARCHAR(30), address addr_type);

Figure 3-5 is the illustration of what we've just created, assuming some records have been populated. Now we can access the attributes of address field using dot notation:

Figure 3-5: Table with column declared as an object type

address.street address.city   address.state address.zip address.country address.type

Not much benefit so far — basically we just store multiple address fields in one column — but we'll take advantage of our new custom type in later examples.

Nested tables

A nested table in Oracle corresponds to SQL99 set type. It can be defined as a table that is a column of another table with unlimited number of rows. The actual data for a nested table is physically stored in another table, but from a user's viewpoint it looks like an integral part of the main table.

For example, we have a parent-child relationship between tables CUST2 and ADDRESS and want to eliminate it using a nested column instead. Assuming we already have ADDR_TYPE created, what we do next is

CREATE TYPE addr_type_tab AS   TABLE OF addr_type

This creates a new user-defined type ADDR_TYPE_TAB of nested table type. Now, we can create the actual table CUST2 so that each row may contain a table of addresses:

CREATE TABLE cust2 ( id NUMBER,   name VARCHAR(30), address addr_type_tab) NESTED TABLE address STORE AS   addr_storage;

The last line of code specifies the name of the storage table where the rows of all the values of the nested table reside. Figure 3-6 shows how addresses for CUST2 are stored in a nested table.

Figure 3-6: Nested tables in Oracle

Varying arrays

VARRAY data type is very similar to NESTED TABLE. The main difference is that VARRAY has to be ordered. Also, you have to specify the maximum number of elements for VARRAY and don't have to indicate the name for the storage:

CREATE TYPE addr_type_varray AS   VARRAY(50) OF addr_type; 

create table cust3 ( id NUMBER,   name VARCHAR(30), address addr_type_varray); 

DB2 UDB 8.1

DB2 supports user-defined structured types, reference types, and user-defined distinct types.

User-defined types

User-defined types are very similar to Oracle's object types. This example shows how to create an address structure:

CREATE TYPE addr_type AS (  street VARCHAR(30), city VARCHAR(30), state CHAR(2), zip VARCHAR(10), country  VARCHAR(30) type CHAR(1)) MODE DB2SQL

Now we can create a table that uses the new user-defined data type ADDR_TYPE:

CREATE TABLE cust1 ( id  INTEGER, name VARCHAR(30), address addr_type)

Reference types

Reference types are used to define references to a row in another table (or user-structured type). The references are similar to referential integrity constraints but do not enforce relationships between the tables:

CREATE TABLE cust2 ( id  INTEGER, name VARCHAR(30), address REF(addr_type))

The address column of the CUST2 table references the user-defined ADDR_TYPE.

Distinct types

Distinct types are defined using existing data types and have the same features of the built-in type. For example, we can create a new data type usd for U.S. dollars:

CREATE DISTINCT TYPE usd AS  DECIMAL(12,2) WITH COMPARISONS 

Now we can use it throughout the whole database in a way similar to the way MS SQL Server uses its MONEY data type. For example, we can create tables with column of a type USD:

CREATE TABLE employee ( id  INTEGER, same VARCHAR(30), salary USD);