Oracle Database Architectures  Oracle Tips by Burleson Consulting

High Performance Data Warehousing

Database Architectures For The 1990s

In this manner, aggregate datatypes can be defined and addressed in a table definition just like any other relational datatype. In the following example, we see the phone_nbr and address datatypes being used in a table definition:

create table CUSTOMER (
cust_name varchar(40),
cust_phone phone_nbr,
cust_address address);

Here, we see that a single data field in a table may be a range of values or an entire table. This concept is called complex, or unstructured, data typing (see Figure 1.11). The domain of values for a specific field in a relational database may be defined with this approach. This ability to nest data tables allows for relationship data to be incorporated directly into the table structure. For example, the occupations field in the table establishes a one-to-many relationship between an employee and his or her valid occupations. Also, note the ability to nest the entire SKILLS table within a single field. In this example, only valid skills may reside in the skills field, and this implements the relational concept of domain integrity.

Figure 1.11 An example of abstract datatypes (ADTs).