Designing a Hierarchy of Data Types

Oracle Database Tips by Donald Burleson

Displaying data type usage

In object-oriented data models, the ability to "nest", or embed data definition within other definitions has tremendous benefits for the database administrators who must manage the data definitions. In pre-relational databases it was possible to see "where-used" information for any data item in the enterprise.

A bill-of-material relationship existed in the data dictionary to relate both the sub-data items of a data type as well as the higher-level components where the data item is used. Also, since each any every method is now stored in the database, it is possible to see where a data item is referenced. For some relational databases that still allow external programs, the database pre-compiler will record all external programs that reference the data type. Hence, user-defined data types with a robust dictionary will allow the DBA to see every place where a data type is used so that they can identify every place where a data definition needs to be changed.

For example, consider the following data dictionary report for our full_address datatype:

Full_address data type

Consists of:

(street_address, city_address, zip_code)

Part of:

(customer_address, employee_address, vendor_address)

Used in methods:

            customer.insert
            customer.update
            customer.select
            customer.produce_mailing_label
            employee.insert
            employee.update
            employee.select
            employee.produce_pink_slip
            employee.mail_paycheck
            vendor.insert
            vendor.update
            vendor.select
            vendor.produce_mailing_label

Used in C Programs:

            PSSC102;
            SPSH464
            PJUY775

In this example listing from a data dictionary, we can see that the data dictionary has provided everything that we need to know about the full_address data type. This can be an extremely useful feature for database objects, especially when data types change their definition. Consider how simple it would be to change a zip code from 5 digits to 9 digits, or change a year field from 2 digits to 4 digits. Every place where the data type exists can be easily identified.

Designing a hierarchy of data types

One of the challenges of abstract data typing is the definition of a hierarchy of related data types. In order to get the benefit of re-usable data structures, it is necessary to define a hierarchy of data types.

As we know by intuition, data types naturally form a recursive many-to-many relationship with other data types, such that a data type may be composed of data types, while at the same time being a part of a larger data type. (Figure 5.2)

Figure 5.2 A Model for a recursive data type hierarchy

This relationship has be expressed in a set occurrence diagram, where we can see the linkages between the has_parts and the is_a_part relationship. (Figure 5.3) Here wee see that the full_address data types has the components, street_address, city_address and zip_code, while at the same time, full_address is cast as the customer_address and the employee_address datatypes, participating in the customer_stuff and the employee_stuff data types.

Figure 5.3 A set occurrence diagram for data types

In the relational model, the relationship between data types is expressed by creating an intersection record to establish the many-to-many relationship. (Figure 5.4)

Figure 5.4 A tabular representation of data types

Here wee see that the junction table has only two columns, HAS-PARTS and IS-A-PART. By issuing a relational JOIN operation, we can build the data structures for any data item. For example, the following SQL will display all of the components within the full_address datatype. In this example, we join using the has_parts relationship to see the sub-components of the full_address:

SELECT
        has_part
FROM
        component_table
WHERE
        is_a_part = 'full_address';

HAS_PART
---------------------------------------------
street_address
city_address
zip_code

Conversely, we can also display where a data type appears as a sub-part in a larger data type. In the following example, we can display where the full_address participates:

SELECT
        is_a_part
FROM
        component_table
WHERE
        has_parts = 'full_address';

IS-A-PART
---------------------------------------------
customer
employee

Now that we understand the internal representation of user-defined data types within the databases dictionary, let's move on to take a look at how they are used and manipulated within database methods.

��