An example of managing and manipulating employee information in a database using an abstract data type is explained below.

Let's assume that employee information consists of such items as name, sex, date of employment, position, and salary. Let' also assume that image information such as an ID photo is also part of the employee information. Calculation of service years is one possible operation on employee information.

When this information is handled in a database, the abstract concept of employee in a data model can be considered to consist of attributes such as name, sex, employment date, position, ID photo, and salary, all of which are common to this concept. An operation such as calculation of service years for an employee can also be considered to be an operation that indicates an employee characteristic.

Employee can capture all these characteristics (attributes and operations) in a single concept.

In HiRDB, you can address as abstract concepts and handle in a database objects that exist in the real world, and then use abstract data types to define such concepts as data types.

Figure 3-32 shows a conceptual model based on real-world information and an abstract data type.

Figure 3-32 Conceptual model based on real-world information and an abstract data type

An abstract data type can be defined in a database using the CREATE TYPE definition SQL shown as follows.

CREATE TYPE t_employee (
    Name        CHAR(16),
    Sex         CHAR(1),
    employment_date   DATE,
    position    CHAR(10),
    id_photo    BLOB(64K),
    salary      INTEGER,
    ....
    FUNCTION    service-years ( p t_employee )
       RETURNS  INTEGER
     BEGIN
      DECLARE service_years INTERVAL YEAR TO DAY;
      SET service_years = CURRENT_DATE -
p..employment_date;
      RETURN YEAR(service_years);
     END,
    ....
 )

In this way, the user can use an abstract data type to define new data types by specifying attributes and operations.

An abstract data type can be handled in the same way as the HiRDB system default data types, such as the numeric type and the character type. For example, the table STAFF_TABLE can be defined by the definition SQL shown below using the abstract data type t_employee as a column data type:

CREATE TABLE STAFF_TABLE (
     employee_number   INTEGER,
     employee          t_employee
                        ALLOCATE(id_photo IN(lobarea))
     )

When the BLOB type is to be used as one of the attributes of an abstract data type, the user LOB RDAREA in which the data is to be stored must be specified with the ALLOCATE option in CREATE TABLE. In the example above, id_photo, which is an attribute of t_employee, is of the BLOB type, so ALLOCATE is used to store it in user LOB RDAREA lobarea.

Figure 3-33 shows a table called STAFF_TABLE, for which an abstract data type is defined.

Figure 3-33 STAFF_TABLE for which an abstract data type is defined

When an abstract data type is used, an application can handle the abstract data type values without knowing the detailed configuration of the individual attributes or the installed routines; it does this by using routines declared in that abstract data.

For example, it is possible to manipulate t_employee type values using the following data manipulation SQL:

SELECT employee_number, employee_name,
       service-years(employee)
  FROM  STAFF_TABLE

Handling of values based on an abstract data type and using only an external interface without being concerned about the information in the values is called encapsulation.

Figure 3-34 shows encapsulation.

Figure 3-34 Encapsulation

(a) Value generation

By executing a function without arguments that is recognized by the same name as an abstract data type, HiRDB can generate values for that abstract data type.

For example, for the t_employee type, t_employee type values can be generated using the function t_employee().

A function that generates abstract data type values is called a constructor function.

BEGIN                     ... Start of SQL procedure.
   DECLARE p t_employee;  ... Declares a t_employee type variable.
   SET p = t_employee();  ... Generates a t_employee type value
                            and substitutes in the variable.
   SET p..name = 'Michael Brown' ... Setting of attribute value
                                   through component
                                   specification.
   RETURN p;              ... Returns the function's return value
                            (returns a t_employee type value).
END                       ... End of SQL procedure.

When CREATE TYPE is used to define an abstract data type in the database, HiRDB automatically defines a function such as t_employee() that has the same name as the data type but has no argument. Such a function is called the default constructor function.

(b) User-defined constructor function

The user may also define a constructor function.

A constructor function is defined by defining in a CREATE TYPE routine declaration a function that has the same name as the abstract data type to be defined and that uses the abstract data type as the return value type.

CREATE TYPE t_employee (
      name                CHAR(16),
      sex                 CHAR(1),
      employment_date     DATE,
      position            CHAR(10),
      id_photo            BLOB(64K),
      salary              INTEGER,
 
  FUNCTION t_employee(
      p_name              CHAR(16),
      p_sex               CHAR(1),
      p_employment_date   DATE,
      p_position          CHAR(10),
      p_id_photo          BLOB(64K),
      p_salary            INTEGER)
      RETURNS             t_employee
  BEGIN
      DECLARE    d_employee t_employee;
        SET      d_employee = t_employee();
        SET      d_employee..name       = p_name;
        SET      d_employee..sex        = p_sex;
        SET      d_employee..employment_date
                                        = p_employment_date;
        SET      d_employee..position   = p_position;
        SET      d_employee..id_photo   = p_id_photo;
        SET      d_employee..salary     = p_salary;
 
      RETURN d_employee;
  END,
  ...
  )

For example, using the user-defined constructor function t_employee() and the following data manipulation SQL, values can be generated and stored in a database:

INSERT INTO STAFF_TABLE
  VALUES (
          650056, t_employee(:name AS CHAR(16),
                             :sex AS CHAR(1),
                             :yrs AS DATE,
                             :post AS CHAR(10),
                             :picture AS BLOB(64K),
                             :salary AS INTEGER)
          )

Figure 3-35 shows a table called STAFF_TABLE for which values are generated by constructor function t_employee() and inserted as column values.

Figure 3-35 STAFF_TABLE for which values are generated using a constructor function

Null values can also be applied to an abstract data type, in the same manner as with the HiRDB system default types. For example, executing the following data manipulation SQL for the aforementioned STAFF_TABLE places null values in the EMPLOYEE column:

INSERT INTO STAFF_TABLE(employee_number)
                  VALUES(650056)

On the other hand, executing the following data manipulation SQL changes all t_employee type attribute values to null values (the column values of the abstract data type in which all attribute values are null values are regarded as values other than null values):

INSERT INTO STAFF_TABLE (900123, t_employee ())

Figure 3-36 shows the handling of null values in STAFF_TABLE for which an abstract data type is defined.

Figure 3-36 Handling of null values in STAFF_TABLE for which an abstract data type is defined

For example, executing the following data manipulation SQL retrieves the employee numbers of those employees whose values in the EMPLOYEE column are not null values (therefore, the employee numbers of those employees who have all of the t_employee type attributes as null values will not be retrieved):

SELECT employee_number FROM STAFF_TABLE
    WHERE employee IS NOT NULL

Retrieval result:

900123

Let's plan an operation for calculating an employee's service years with the company.

Operations for abstract data type values can be defined in HiRDB by using a routine declaration in CREATE TYPE. For example, an operation such as for calculating service years and compensation rate can be defined with the following definition SQL:

CREATE TYPE t_employee (
        name              CHAR(16),
        sex               CHAR(1),
        employment date   DATE,
        position          CHAR(10),
        id-photo          BLOB(64K),
        salary            INTEGER,
      ........
        FUNCTION  service years ( p t_employee )
           RETURNS INTEGER
         BEGIN
          DECLARE service_years INTERVAL YEAR TO DAY;
           SET service_years
           = CURRENT_DATE - p..employment date;
          RETURN YEAR(service_years);
         END,
       .......
  )

In this way, routines defined for an abstract data type can be used for abstract data type values. For example, an SQL for retrieving the employee number and employee name of each employee with a service years value of 10 or more years can be described as follows:

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