Creating a structured type hierarchy

The following figure presents an illustration of a structured type hierarchy:

To create the BusinessUnit_t type, issue the following CREATE TYPE SQL statement:

   CREATE TYPE BusinessUnit_t AS 
      (Name VARCHAR(20), 
      Headcount INT)
      MODE DB2SQL;

To create the Person_t type hierarchy, issue the following SQL statements:

   CREATE TYPE Person_t AS 
      (Name VARCHAR(20), 
      Age INT,
      Address Address_t)
      REF USING VARCHAR(13) FOR BIT DATA
      MODE DB2SQL;

   CREATE TYPE Employee_t UNDER Person_t AS
      (SerialNum INT, 
      Salary DECIMAL(9,2), 
      Dept REF(BusinessUnit_t))
      MODE DB2SQL;

   CREATE TYPE Student_t UNDER Person_t AS 
      (SerialNum CHAR(6), 
      GPA DOUBLE)
      MODE DB2SQL;

   CREATE TYPE Manager_t UNDER Employee_t AS 
      (Bonus DECIMAL(7,2))
      MODE DB2SQL;

   CREATE TYPE Architect_t UNDER Employee_t AS 
      (StockOption INTEGER)
      MODE DB2SQL;

Person_t has three attributes: Name, Age and Address. Its two subtypes, Employee_t and Student_t, each inherit the attributes of Person_t and also have several additional attributes that are specific to their particular types. For example, although both employees and students have serial numbers, the format used for student serial numbers is different from the format used for employee serial numbers.

Finally, Manager_t and Architect_t are both subtypes of Employee_t; they inherit all the attributes of Employee_t and extend them further as appropriate for their types. Thus, an instance of type Manager_t will have a total of seven attributes: Name, Age, Address, SerialNum, Salary, Dept, and Bonus.