Let's consider managing the information for employees who are in sales. Salesperson can be considered a more specific and specialized (particular) concept in comparison to the abstract concept of employee. The information on a salesperson may contain information related to sales activities in addition to the information common to all employees. Therefore, attributes such as charge client and number of clients are assigned to a salesperson, in addition to being an employee.

Figure 3-37 shows a conceptual model for a salesperson based on both real-world information and an abstract data type.

Figure 3-37 Conceptual model based on real-world information and an abstract data type (for a salesperson)

HiRDB makes it possible to take a particular data type and tailor it so as to define an abstract data type as a subtype.

For example, the subtype clause of a CREATE TYPE definition SQL statement can be used to define t_salesperson based on t_employee, as shown below:

CREATE TYPE t_salesperson UNDER t_employee(
      charge client  VARCHAR(3000),
 
      FUNCTION number of clients (...........)
      RETURNS INTEGER
        ...........
      )

Note that a high-order abstract data type, such as t_employee, is called a super type.

Salesperson is also an employee. HiRDB can handle an abstract data type value that was specialized (low-order) in a subtype also as a high-order abstract data type value. For example, the SQL shown below can substitute the t_salesperson values as t_employee column values in STAFF_TABLE.

Note

To handle t_salesperson values in the same manner as t_employee values, it is necessary to re-create SQL objects by executing ALTER ROUTINE before executing this SQL.

 
INSERT INTO STAFF_TABLE VALUES ( 51, t_salesperson(:name AS CHAR(16),.....) )

The capability of a low-order abstract data type value to be considered as a high-order abstract data type value in this way is called substitutability.

Because salesperson is also an employee, it also has attributes, such as name and sex, just like employee, and it should be possible to calculate service years for a salesperson.

Figure 3-37 shows that a lower-order abstract data type in a subtype inherits the attributes and routines defined for the higher-order abstract data type.

This manner in which a lower-order abstract data type inherits the attributes and routines of the higher-order abstract data type is called inheritance.

For example, the attribute name and the operation service years become available for t_salesperson values through inheritance. Consequently, as shown below, the SQL shown above can be executed without any changes for STAFF_TABLE into whose columns t_salesperson values have been inserted.

Note

Before this SQL can be executed, it is necessary to re-create SQL objects by executing ALTER ROUTINE.

 
SELECT employee number, employee..name, service years (employee)
  FROM  STAFF_TABLE
  WHERE service years(employee) >= 10

Subtype and inheritance can provide the following benefits:

• The concept of substitutability (e.g., a salesperson is also an employee) can be expressed quickly and clearly.
• Attributes and routine definitions can be shared, and new definitions can be added based on existing definitions. Consequently, the overhead for database and application development can be reduced, and a system with expandability can be constructed.

Let's consider an operation for determining compensation for an employee. For example, compensation can be determined based on the information that is set up by salary, service years, and compensation rate based on service years and on work performance.

Meanwhile, the operation compensation to be defined for employee is inherited by salesperson, as explained above. However, the method of determining compensation for salespersons may be different from that used for ordinary employees, and may include such attributes as number of clients that are specific to salespersons.

Figure 3-38 shows operations related to employees and salespersons in the real world.

Figure 3-38 Operations related to employees and salespersons in the real world

Here, let's define routines, such as employee compensation and salesperson compensation, that have different names for each of the abstract data types.

In this case, despite the fact that employee values and salesperson values can be handled together because of substitutability, it will be necessary to have a different name for the routine to be called for each value type. Consequently, applications will not be able to execute the data manipulation SQL described as follows:

SELECT   employee number, employee..name,
         employee compensation(employee)
    FROM     STAFF_TABLE
... Determination of compensation specific to salesperson cannot be executed.
 
SELECT   employee number, employee..name,
         salesperson compensation (employee)
    FROM     STAFF_TABLE
... Determination of compensation specific to salesperson will be executed also for employees who are not salespersons.

In HiRDB, a routine that has the same name as a routine defined in a higher-order abstract data type can be overwritten during the definition of a lower-order abstract data type. This process of overwriting is called override.

Figure 3-39 shows an example of override.

Figure 3-39 Override

HiRDB automatically executes an overridden routine based on the definition appropriate to the type of value that is used as the argument.

Override eliminates the need to change the name of the routine to be called, according to the value type. Therefore, it is possible to execute the data manipulation SQL shown as follows.

Note

Before this SQL can be executed, it is necessary to re-create SQL objects by executing ALTER ROUTINE.

 
SELECT   employee_number, employee..name,
         compensation (employee)
    FROM     STAFF_TABLE

... The overriding routine compensation executes a routine that is appropriate to each argument value.

During execution of this SQL, the routine compensation defined for the t_employee type is executed for the t_employee type values, and the routine compensation defined for the t_salesperson type is executed for the t_salesperson type values.

An application can call a routine without being concerned with whether or not that routine has been overridden, as in the above SQL. Even when routines are added through override, it is possible to execute an SQL without changing the application.