Objects

OOP Paradigm

Object-oriented approach treats everything as objects — i.e. programming structures that are capable of containing data and have some relevant methods. Usually, objects are representations of real-life entities, reduced in their complexity to a few well defined features and tasks they should be able to perform. A person, a tree, a car, an organization — all can be represented as objects; the same goes for some abstract objects like 'bank account', or 'data access object'. Consider an object that models, say, a bank account: it might to have an attribute 'balance' and 'account ID', methods 'withdraw' and 'deposit' — all representing some functionality that is expected of an object of this type. The main principles of Object Orientation are encapsulation, inheritance, polymorphism, and identity, all of which will be discussed in the next paragraphs.

Oracle 9i support

Oracle allows you to create object types — special structures with object attributes and methods. The syntax consists of two parts. First, you declare the object type itself:

CREATE [OR REPLACE] TYPE
		  <object_name> AS OBJECT ( <attribute_name>
		  <attribute_datatype>,... [MEMBER FUNCTION <function_name>
		  [<function_parameter> <parameter_datatype>,... RETURN
		  <datatype>]... [MEMBER PROCEDURE <procedure_name>
		  [<procedure_parameter> <parameter_datatype>,...]...);

Then you define the object type body, where all the functionality implementation code would be contained. This part is optional unless you had any member functions/procedures in the object type declaration. The syntax is as follows (assuming one member function and one member procedure were declared in the CREATE OBJECT TYPE section):

CREATE OR REPLACE OBJECT TYPE
		  BODY <object_name> AS MEMBER FUNCTION <function_name>
		  [<function_parameter> <parameter_datatype>,... RETURN
		  <datatype>]... IS [<declaration_section>] BEGIN
		  <function_body> [EXCEPTION <exception_body>] END; MEMBER PROCEDURE
		  <procedure_name> [<procedure_parameter>
		  <parameter_datatype>,...]... IS [<declaration_section>] BEGIN
		  <procedure_body> [EXCEPTION <exception_body>] END;
		  END;

The instances of this object could be stored in the Oracle tables, and invoked in Oracle's PL/SQL stored procedures.

IBM DB2 UDB 8.1 support

DB2 allows you to create objects in a similar way to that of Oracle, but the syntax is quite different and more complex. Here is the simplified version adopted from IBM DB2 UDB:

CREATE TYPE <type_name>
		  [UNDER <supertype_name>] AS ( <attribute_name>
		  <attribute_datatype>,...) INSTANTIABLE | NOT INSTANTIABLE [WITHOUT
		  COMPARISSONS] [NOT FINAL] MODE DB2SQL METHOD <method_name>
		  (<attribute_name> <attribute_datatype>,...) RETURNS
		  <datatype> <method_specification>

In addition to optional user-defined types, the execution of the CREATE TYPE statement generates methods for retrieving and updating the values of attributes, as well as for constructing instances of a structured type and for supporting the comparison operators (=, <>, <, <=, >, and >=).

For each method declared with the CREATE TYPE statement, you can use the CREATE METHOD statement to associate a method body with a method specification. Here is the syntax:

CREATE METHOD
		  <method_name> RETURNS <datatype> FOR"TYPE <type_name> RETURN
		  {<value> | <sql_statement>}

Microsoft SQL Server 2000

Microsoft SQL Server 2000 does not provide object-oriented features with its RDBMS product; the next closest thing would be the TABLE system data type that allows you to arrange data returned by the SQL query as a set of rows. It cannot be used as a column data type, and therefore cannot be used as permanent storage. An instance of an object created by some external application could be saved in the binary format in the database, but it is no different from any other binary data stored in the RDBMS.

The user-defined data type in MS SQL Server does not allow for creation of a structure data type (like a record field), and cannot contain methods.

Compensating the lack of OOP features, SQL Server 2000 provides the ability to invoke and use ActiveX/OLE objects from within Transact-SQL code through system stored procedures.

SQL Server 2000 also provides Meta Data Services that allow for storage and management of meta data about information systems and applications. It serves as a central repository for data and component definitions, development and deployment models, reusable software components, and data warehousing descriptions; it facilitates development and modeling processes through establishing a common framework for developers and analysts, borrowing heavily from UML (Universal Modeling Language).

Abstract data types

SQL99 introduced the abstract data type (ADT) that is in fact undistinguishable from the definition of class. ADT specification consists of two parts: attribute specification and method specification. ADT behavior is completely encapsulated within the type definition. The ADT enables database programmers to construct application-specific data types in the database with built-in methods to manipulate these data types. As usual, implementation of the standard lags behind — none of the three major vendors have implemented this feature.

Collections and Arrays

Long-time standard tools of every programming language, collections and arrays have made inroads into the domain of RDBMS. They mostly facilitate programming process, eliminating need for additional tables. Both arrays and collections contain data elements, the main difference between these data types being that, as a rule, an array contains elements of the same data type, whereas collections can accommodate different data types.

Imagine that you have decided, in spite of all the talk about good database design (see Appendix C for more information on RDBMS design), to store an employee's educational credentials inside the same table where you store other employee information. If your employees generally have three diplomas, you might consider declaring an EDUCATION_CREDENTIALS column as a VARRAY(5) OF VARCHAR(25) — using Oracle 9i features. Now for each employee there will be five potential records to store information.

Employee ID	Educational Institution
1	Stanford University
	Massachusetts Institute of Technology
	Kalamazoo Community College

To accommodate the case, when more than five educational credentials are acquired, you may want to use a nested table (i.e., a column is of table type, and could therefore contain unrestricted amount of data — Oracle's feature). Both Oracle and IBM DB2 UDB support arrays and collections, while Microsoft does not.

Object-oriented databases

The topic of object-oriented databases is clearly out of scope for this book; we are going to give you only a high-level overview of the current state of affairs.

With the advent of the object-oriented programming came the idea of object storage. Relational databases, in spite of all modernization, new data types, and functionality, remain by and large text-based — that is, they parse, store, and search textual data. While various new media formats — video, sound, PowerPoint presentations, Microsoft Word structured storage — were accommodated by inclusion of the new data types, essentially it remains the same text-based approach, somewhat expanded.

As OO languages (C++, Java, C#, Visual Basic.NET, Delphi, Smalltalk, Eiffel, etc.) become increasingly popular, it begins to make sense to store information in objects as they are defined by the classes implemented in these languages. Imagine a Java class EMPLOYEE, which has its properties (attributes) and methods defined; when an application creates an instance of this class to add a new employee, it populates its properties and then saves the whole object into a database. For an RDBMS that would mean populating a number of tables with textual information; for an object-oriented database, it would mean saving the object as it is — either as a binary, byte-code, or text description version, and still being able to track this object by, say, employee ID. This is what OODBMS and OORDBMS are all about.

While pure object-oriented databases try to implement their own "pure OO" method of storing information, OORDBMS rely on proven relational technology to establish a hierarchy of the objects. There are new standards emerging (Table 17-1), and new ideas taking hold.

Because of a tight coupling of the OO database with the programming language, the application written for one database might be impossible to use with some other products. There are several initiatives to establish a standard for OODBMS, which so far have resulted in the ODMG 3.0 standard, in addition to a number of proprietary ways to do things.

While still a novelty, OODBMS are making it into the mainstream of the academic and corporate worlds, fueled mainly by adoption of object-oriented technologies like Java (and EJB — Enterprise Java Beans) and Smalltalk.

Some object-oriented databases available on the market today are shown in Table 17-2.

The main advantage of the OODBMS over OORDBMS comes from eliminating the mapping of the objects from the application (client) to RDBMS structure. In the current environment, in which data is still coming as text or numbers, RDBMS are much faster than comparable OODBMS/OORDBMS; at the same time there are situations when the object approach might prove to be superior to the "old" relational model. The jury is still out.