Support for abstract data types gives the relational data model a foundation for object-based capabilities. The abstract data type can embody the representation of an object, and the values of its individual fields or subcolumns are its attributes. Another important feature of the object-oriented model is inheritance. With inheritance, new objects can be defined as being a particular type of an existing object type (class) and inherit the predefined attributes and behaviors of that type.
Figure 24-2 shows an example of how inheritance might work in a model of a company’s employee data. All employees are members of the class PERSONNEL, and they all have the attributes associated with being an employee (employee number, name, and address). Some employees are salespeople, and they have additional attributes (such as a sales quota and the identity of their sales manager). Other employees are engineers, with a different set of attributes (such as the academic degrees they hold or the current project to which they are assigned). Each of these employee types has its own class, which is a subclass of PERSONNEL. The subclass inherits all of the characteristics of the class above it in the hierarchy (we want to track all of the core personnel data for engineers and salespeople, too). However, the subclasses have additional information that is unique to their type of object. In Figure 24-2, the class hierarchy goes down to a third layer for engineers, differentiating between technicians, developers, and managers.
Informix Universal Server’s abstract data type inheritance mechanism provides an easy way to define abstract data types (Informix row types) that correspond to the natural hierarchy in Figure 24-2. Assume that the Informix PERS_TYPE row type has already been created, as in the example from the “Defining Abstract Data Types” section earlier in this chapter, and a typed table named PERSONNEL has been created based on this row type. Using the Informix inheritance capabilities, here are some CREATE ROW TYPE statements for other types in the hierarchy:
CREATE ROW TYPE SALES_TYPE (
SLS_MGR INTEGER, /* employee number of sales mgr */
SALARY MONEY(9,2), /* annual salary */
QUOTA MONEY(9,2))
UNDER 1PERS TYPE;
CREATE ROW TYPE ENGR_TYPE (
SALARY MONEY(9,2), /* annual salary */
YRS_EXPER INTEGER /* years of experience */
UNDER PERS TYPE;
CREATE ROW TYPE MGR_TYPE (
BONUS MONEY(9,2)) /* annual bonus */
UNDER ENGR TYPE;
CREATE ROW TYPE TECH_TYPE (
WAGE_RATE MONEY(5,2)) /* hourly wage rate */
UNDER ENGR_TYPE;
The type defined for technicians (TECH_TYPE) is a subtype (subclass) of the engineer type (ENGR_TYPE), so it inherits all of the fields for the personnel type (PERS_TYPE), plus the fields added at the ENGR_TYPE level, plus the additional field added in its own definition. An abstract type that is defined UNDER another type, and inherits its fields, is called a subtype of the higher-level type. Conversely, the higher-level type is a supertype of the lower-level types defined UNDER it.
With this type hierarchy defined, it’s easy to create Informix typed tables that use them. Here are some Informix statements that create a table for engineers, separate tables for managers and technicians, and another table to hold salesperson data:
CREATE TABLE ENGINEERS
OF TYPE ENGR_TYPE;
CREATE TABLE TECHNICIANS
OF TYPE TECH_TYPE;
CREATE TABLE MANAGERS
OF TYPE MGR_TYPE;
CREATE TABLE REPS
OF TYPE SALES_TYPE;
The type hierarchy has pushed the complexity into the data type definitions and made the table structure very simple and easy to define. All other characteristics of the table can (and must) still be defined within the table definition. For example, the salesperson table contains a column that is actually a foreign key to the personnel table, so its table definitions should probably include a FOREIGN KEY clause like this:
CREATE TABLE REPS
OF TYPE SALES_TYPE
FOREIGN KEY (SLS_MGR)
REFERENCES PERSONNEL(EMPL_NUM);
Type inheritance creates a relationship among the structure of the tables that are based on the defined row types, but the tables remain independent of one another in terms of the data that they contain. Rows inserted into the TECHNICIANS table don’t automatically appear in the ENGINEERS table nor in the PERSONNEL table. Each is a table in its own right, containing its own data. A different kind of inheritance, table inheritance, provides a very different level of linkage between the table’s contents, actually turning the tables into something much closer to object classes. It is described in the next section.
1. Table Inheritance: Implementing Object Classes
Informix Universal Server provides a capability called table inheritance that moves the table structure of a database away from the traditional relational model and makes it much closer to the concept of an object class. Using table inheritance, it’s possible to create a hierarchy of typed tables (classes), such as the one shown in Figure 24-3. The tables are still based on a defined type hierarchy, but now the tables themselves have a parallel hierarchy.
Here is a set of CREATE TABLE statements that implements this table inheritance:
CREATE TABLE ENGINEERS
OF TYPE ENGR_TYPE
UNDER PERSONNEL;
CREATE TABLE TECHNICIANS
OF TYPE TECH_TYPE
UNDER ENGINEERS;
CREATE TABLE MANAGERS
OF TYPE MGR_TYPE
ENDER ENGINEERS;
CREATE TABLE REPS
OF TYPE SALES_TYPE
UNDER PERSONNEL;
When a table is defined in this way (as under another table), it inherits many more characteristics from its supertable than just the column structure. It inherits the foreign key, primary key, referential integrity, and check constraints of the supertable, any triggers defined on the supertable, as well as indexes, storage areas, and other Informix-specific characteristics. It’s possible to override this inheritance by specifically including the overridden characteristics in the CREATE TABLE statements for the subtables.
A table type hierarchy has a profound impact on the way that the Universal Server DBMS treats the rows stored in the tables. The tables in the hierarchy now form a collection of nested sets of rows, as shown in Figure 24-4. When a row is inserted into the table hierarchy, it is still inserted into a specific table. Joe Jones, for example, is in the TECHNICIANS table, while Sam Wilson is in the ENGINEERS table and Sue Marsh is in the PERSONNEL table.
SQL queries behave quite differently, however. When you perform a database query on one of the tables in the hierarchy, it returns rows not only from the table itself, but from all of the included subtables of that table. This query:
SELECT *
FROM PERSONNEL;
returns rows from the PERSONNEL table and rows from the ENGINEERS, TECHNICIANS, and REPS tables. Similarly, this query:
SELECT *
FROM ENGINEERS;
returns rows from TECHNICIANS and MANAGERS in addition to ENGINEERS. The DBMS is now treating the tables as a nested collection of rows, and a query on a table (rowset) applies to all rows included in the set. If you want to retrieve only the rows that appear in the top-level table itself, you must use the ONLY keyword:
SELECT *
FROM ONLY(ENGINEERS);
The DBMS applies the same set-of-rows logic to DELETE operations. This DELETE statement:
DELETE FROM PERSONNEL
WHERE EMPL_NUM = 1234;
successfully deletes the row for employee number 1234 regardless of which table in the hierarchy actually contains the row. The statement is interpreted as “Delete any rows from the PERSONNEL set that match these criteria.” As with the queries, if you want to delete only rows that appear in the ENGINEERS table of the hierarchy, but not rows from any of its subtables, you can use this statement:
DELETE FROM ONLY(ENGINEERS)
WHERE EMPL_NUM = 1234;
The same logic holds for UPDATE statements. This one changes the employee number, regardless of which table in the hierarchy actually holds the row for the employee:
UPDATE PERSONNEL
SET L_NAME = ‘Harrison’
WHERE EMPL_NUM = 1234;
Again, the ONLY construct may be used to restrict the scope of the UPDATE operation to only rows that actually appear in the named table and not those that appear in its subtables.
Of course, when operating at a given level within the table hierarchy, your SQL statements can reference only columns that are defined at that level. You cannot use this statement:
DELETE FROM PERSONNEL
WHERE SALARY < 20000.00;
because the SALARY column doesn’t exist in the top-level PERSONNEL table (class).
It is defined only for some of its subtables (subclasses). You can use this statement:
DELETE FROM MANAGERS
WHERE SALARY < 20000.00;
because SALARY is defined at this level of the table (class) hierarchy.
As noted, table inheritance moves the operation of Informix Universal Server fairly far out of the relational database realm and into the object-oriented world. Relational purists point to examples like the previous ones to claim that object-relational databases bring with them dangerous inherent inconsistencies. They ask these typical kinds of questions: “Why should an INSERT of a row into one table cause it to suddenly appear in two other tables?” and “Why should a searched DELETE statement that doesn’t match any rows of a table cause other rows in other tables to disappear?” Of course, the table hierarchy has stopped behaving strictly as if it were a set of relational tables, and instead has taken on many of the characteristics of an object class and object class hierarchy. Whether this is good or bad depends on your point of view. It does mean that you must be very careful about applying relational database assumptions blindly to an object-relational implementation.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.