SQL Object Features
SQL-1999 introduced object support into the SQL standard. The SQL-1999 standard had to be backward compatible with the existing SQL-1992 standard, so object support was implemented as an extension to the existing standard. The types defined by SQL-1992 were retained, and the standard modified to support user defined types (UDT) with object-like features.
Several new types were introduced by the SQL-1999 standard:
- A reference type,
- Distinct types,
- Structured types.
A reference type is essentially an object identifier (OID) which can be used to uniquely identify an instance of an object, and is used to point to another type. This infers the use of a structured type being pointed to.
Distinct types were an ability to ‘rename’ an existing pre-defined type. For example, you could define a type METERS to be an INTEGER. You could similarly define FEET as INTEGER also. The benefit of this is that the database does not allow type mixing, so adding FEET to METERS without conversion would result in an error.
Structured types are more interesting, as a set of data and associated methods can be grouped into a user defined type. For example, you could define an address type, which would contain street number, street, state, zip, country, etc. You could also, depending on the application, define associated functionality.
An example of creating a structured type would be:
CREATE TYPE address AS
(street CHAR (30),
city CHAR (20),
state CHAR (2),
zip INTEGER) NOT FINAL
Not only can structured types be composed of pre-defined types, but can also aggregate other UDTs. Structured types also support inheritance, where a type can be defined as a specialisation of an existing structured type, to add or refine data and/or functionality.
Now, with SQL-1999, you have several ways to model your data:
- Regular SQL tables and columns of pre-defined types.
- Regular SQL tables with some columns of pre-defined types and some UDTs.
- Regular SQL tables with one column whose data type is a UDT.
- Typed tables (where each row represents a structured data type, and each column maps to a member element)
Oracle 9i supports these types by the use of the CREATE TYPE and CREATE TYPE BODY statements.
Oracle 10g also has object support similar to that described earlier. It can store UDTs as field types, or within typed tables, referred to as “Object Tables” within the Oracle documentation. The underlying implementation of object support in Oracle is the use of nested tables.
Object-Relational database systems have the advantage that application programmers can store data in the database in a more natural ‘object oriented’ fashion. The majority of software applications group data into meaningful objects.
Software objects support inheritance, where an object can be specialised. For example, a software application may not care that a person is a customer, book author, or book publisher, when it comes to sending them an email. So an application programmer may design the system with a “Person” object which supports a “sendEmail” function. When it comes to the specialist functions for customers (e.g. sendInvoice) or publisher (e.g. orderBook), these are defined in specialised “Customer” or “Publisher” objects which inherit data and behaviour of the Person object.
This modelling metaphor has to be adapted to the relational layout of a traditional relational database. There is an inherent mismatch between objects hierarchies and flat tables of rows and columns. Object-relational databases go some distance to closing the metaphor mismatch.
The use of an object relational database can be an advantage to an application programmer, possibly above any other database user type. Object relational databases simplify the mapping between software objects and the underlying storage tables.
For example, selecting all customers with a particular date of birth using a relational database would be similar to:
SELECT InitCap(Surname) || ', ' || InitCap(FirstName)
WHERE Month(DOB) = Month(getdate())
AND Day(DOB) = Day(getdate())
Whereas using an object-relational database, with the addition of user defined objects and functions, the same query is much simplified to:
FROM Customers C
WHERE C.Birthday() = Today()
Barry & Associates Inc. SQL:1999, 2007, Retrieved on June 13, 2007 from http://www.service-architecture.com/database/articles/sql1999.html.
Oracle Corp. Oracle 9i SQL Reference: CREATE TYPE. 2002. Retrieved on June 13, 2007 from http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_82a.htm#SQLRF01506
Oracle Corp. Oracle® Database Application Developer’s Guide – Object-Relational Features 10g Release 1 (10.1). “Design Considerations for Oracle Objects”, 2003. Retrieved on June 13, 2007 from http://download-uk.oracle.com/docs/cd/B14117_01/appdev.101/b10799/adobjdes.htm#ADOBJ008
Wikipedia Contributors. Object-Relational Database. 2007. Retrieved on June 14, 2007 from http://en.wikipedia.org/wiki/Object-relational_database