17.6.1 User-Defined Types and Complex Structures for Objects
To allow the creation of complex-structured objects, and to separate the declaration of a type from the creation of a table, SQL now provides user-defined types (UDTs). In addition, four collection types have been included to allow for multivalued types and attributes in order to specify complex-structured objects rather than just simple (flat) records. The user will create the UDTs for a particular application as part of the database schema. A UDT may be specified in its simplest form using the following syntax:
CREATE TYPE TYPE_NAME AS (<component declarations>);
Figure 17.8 illustrates some of the object concepts in SQL. We will explain the examples in this figure gradually as we explain the concepts. First, a UDT can be used as either the type for an attribute or as the type for a table. By using a UDT as the type for an attribute within another UDT, a complex structure for objects (tuples) in a table can be created, much like that achieved by nesting type constructors. This is similar to using the struct type constructor of Section 17.5.3. For example, in Figure 17.8(a), the UDT STREET_ADDR_TYPE is used as the type for the STREET_ADDR attribute in the UDT USA_ADDR_TYPE. Similarly, the UDT USA_ADDR_TYPE is in turn used as the type for the ADDR attribute in the UDT PERSON_TYPE. If a UDT does not have any operations, as in the examples in Figure 17.8, it is possible to use the concept of ROW TYPE to directly create a structured attribute by using the keyword ROW. For example, we could use the following instead of declaring STREET_ADDR_TYPE as a separate type as in Figure 17.8:
- CREATE TYPE STREET_ADDR_TYPE AS (
NUMBER VARCHAR (5),
STREET_NAME VARCHAR (25),
APT_NO VARCHAR (5),
SUITE_NO VARCHAR (5)
);
CREATE TYPE USA_ADDR_TYPE AS (
STREET_ADDR STREET_ADDR_TYPE,
CITY VARCHAR (25),
ZIP VARCHAR (10)
);
CREATE TYPE USA_PHONE_TYPE AS (
PHONE_TYPE VARCHAR (5),
AREA_CODE CHAR (3),
PHONE_NUM CHAR (7)
);
- CREATE TYPE PERSON_TYPE AS (
NAME VARCHAR (35),
SEX CHAR,
BIRTH_DATE DATE,
PHONES USA_PHONE_TYPE ARRAY [4],
ADDR USA_ADDR_TYPE
INSTANTIABLE
NOT FINAL
REF IS SYSTEM GENERATED
INSTANCE METHOD AGE() RETURNS INTEGER;
CREATE INSTANCE METHOD AGE() RETURNS INTEGER
FOR PERSON_TYPE
BEGIN
RETURN /* CODE TO CALCULATE A PERSON’S AGE FROM TODAY’S DATE AND SELF.BIRTH_DATE */
END;
);
- CREATE TYPE GRADE_TYPE AS (
COURSENO CHAR (8),
SEMESTER VARCHAR (8),
YEAR CHAR (4),
GRADE CHAR
);
CREATE TYPE STUDENT_TYPE UNDER PERSON_TYPE AS (
MAJOR_CODE CHAR (4),
STUDENT_ID CHAR (12),
DEGREE VARCHAR (5),
TRANSCRIPT GRADE_TYPE ARRAY [100]
INSTANTIABLE
NOT FINAL
INSTANCE METHOD GPA() RETURNS FLOAT;
CREATE INSTANCE METHOD GPA() RETURNS FLOAT
FOR STUDENT_TYPE
BEGIN
RETURN /* CODE TO CALCULATE A STUDENT’S GPA FROM SELF.TRANSCRIPT */
END;
);
CREATE TYPE EMPLOYEE_TYPE UNDER PERSON_TYPE AS (
JOB_CODE CHAR (4),
SALARY FLOAT,
SSN CHAR (11)
INSTANTIABLE
NOT FINAL
);
CREATE TYPE MANAGER_TYPE UNDER EMPLOYEE_TYPE AS (
DEPT_MANAGED CHAR (20)
INSTANTIABLE
);
- CREATE TABLE PERSON OF PERSON_TYPE
REF IS PERSON_ID SYSTEM GENERATED;
CREATE TABLE EMPLOYEE OF EMPLOYEE_TYPE
UNDER PERSON;
CREATE TABLE MANAGER OF MANAGER_TYPE
UNDER EMPLOYEE;
CREATE TABLE STUDENT OF STUDENT_TYPE
UNDER PERSON;
- CREATE TYPE COMPANY_TYPE AS (
COMP_NAME VARCHAR (20),
LOCATION VARCHAR (20)
);
CREATE TYPE EMPLOYMENT_TYPE AS (
Employee REF (EMPLOYEE_TYPE) SCOPE (EMPLOYEE),
Company REF (COMPANY_TYPE) SCOPE (COMPANY) );
CREATE TABLE COMPANY OF COMPANY_TYPE (
REF IS COMP_ID SYSTEM GENERATED,
PRIMARY KEY (COMP_NAME) );
CREATE TABLE EMPLOYMENT OF EMPLOYMENT_TYPE;
CREATE TYPE USA_ADDR_TYPE AS (
STREET_ADDR ROW (
NUMBER VARCHAR (5),
STREET_NAME VARCHAR (25),
APT_NO VARCHAR (5),
SUITE_NO VARCHAR (5) ),
CITY VARCHAR (25),
ZIP VARCHAR (10)
);
Figure 17.8 Illustrating some of the object features of SQL. (a) Using UDTs as types for attributes such as Address and Phone, (b) Specifying UDT for PERSON_TYPE, (c) Specifying UDTs for STUDENT_TYPE and EMPLOYEE_TYPE as two subtypes of PERSON_TYPE (d) Creating tables based on some of the UDTs, and illustrating table inheritance, (e) Specifying relationships using REF and SCOPE.
To allow for collection types in order to create complex-structured objects, four constructors are now included in SQL: ARRAY, MULTISET, LIST, and SET. These are similar to the type constructors discussed in Section 17.5.3. In the initial specification of SQL/Object, only the ARRAY type was specified, since it can be used to simulate the other types, but the three additional collection types were included in the latest version of the SQL standard. In Figure 17.8(b), the PHONES attribute of PERSON_TYPE has as its type an array whose elements are of the previously defined UDT USA_PHONE_TYPE. This array has a maximum of four elements, meaning that we can store up to four phone numbers per person. An array can also have no maximum number of elements if desired.
An array type can have its elements referenced using the common notation of square brackets. For example, PHONES[1] refers to the first location value in a PHONES attribute (see Figure 17.8). A built-in function CARDINALITY can return the current number of elements in an array (or any other collection type). For example, PHONES[CARDINALITY (PHONES)] refers to the last element in the array.
The commonly used dot notation is used to refer to components of a ROW TYPE or a UDT. For example, ADDR.CITY refers to the CITY component of an ADDR attribute (see Figure 17.8).
17.6.2 Object Identifiers Using Reference Types
Unique system-generated object identifiers can be created via the reference type in the latest version of SQL. For example, in Figure 17.8(b), the phrase:
REF IS SYSTEM GENERATED
indicates that whenever a new PERSON_TYPE object is created, the system will assign it a unique system-generated identifier. It is also possible not to have a system-generated object identifier and use the traditional keys of the basic relational model if desired. In general, the user can specify that system-generated object identifiers for the individual rows in a table should be created. By using the syntax:
REF IS <OID ATTRIBUTE><VALUE GENERATED METHOD> ;
the user declares that the attribute named <OID ATTRIBUTE> will be used to identify individual tuples in the table. The options for are SYSTEM GENERATED or DERIVED. In the former case, the system will automatically generate a unique identifier for each tuple. In the latter case, the traditional method of using the user-provided primary key value.
Extracts of this topic can be found in FUNDAMENTALS TO STRUCTURED SYSTEMS DESIGN AND IMPLEMENTATION by Valerie Njee.
JAVA PROGRAMMING 