SQL and Encapsulation Violation Oracle Database Tips by Donald Burleson

Encapsulation is used in non-database object-oriented applications to insure that  all operations are performed through the programmer-defined interface, and that data will never be modified outside of the application shell.  But what about ad-hoc query and update?  It appears that any declarative database language such as SQL, which allows "external" retrieval and update, does not follow the dictates of encapsulation, and is therefore inconsistent with object-oriented database management.

For example, a relational database could be defined to have a behavior called ADD_LINE_ITEM which serves to check inventory levels for an item, and add an item to an order only if sufficient stock is available.  This behavior insures that orders are not entered for out-of-stock items.  With a language such as SQL, the object-oriented behavior could be bypassed, and LINE_ITEM records could be added without any regard for inventory levels. 

Because encapsulation and SQL are incompatible, the only conclusion that can be reached is that encapsulation does not apply to object-oriented databases because declarative languages violate the principle.  In addition we might also conclude that declarative languages cannot be used within a true object-oriented database because all objects must be their methods to gain access.

The most important feature of the relational database is the ability to isolate the data from the data relationships, and to eliminate the "pointers" which were used by hierarchical and network databases to establish relationships.  In a relational database, two tables which have a relationship are defined with a primary key and a foreign key.  This key can be used at run-time to dynamically join the tables. 

SQL was a tremendous benefit to programmers because it removed the requirements of the database that the use of the system "navigate" the data structures.  As we know, SQL is called "declarative" in the sense that the user need only specify the desired data, and the database engine will take care of the navigation.  Hierarchical, Network and object-oriented databases require the programmer to navigate the data  structures to get their selected data.  With SQL the age of end-user access became a reality and users began to use SQL to access their information without programmer intervention.  The term "Declarative", in this context, means that the actual navigation path to the data is hidden from the user, and the user "declares" a solution set which meets their selection criteria.  The SQL optimizer would determine the proper access for the data and handle all of the database navigation. 

Problems with SQL and Objects

There are several constructs within the SQL language which conflict with object-oriented databases.  The most obvious is the requirement that SQL serves as an ad-hoc query facility.

These problems fit into the categories of abstract data typing, encapsulation and methods, and the realm of pointers.  All of these constructs are very foreign to SQL and special extensions have been created to allow for the implementation of these constructs.

SQL and abstract data types

Another problem relates to the SQL "CREATE TABLE" statement.  Object-oriented systems allow the concept of abstract data typing, and the programmer may create their own data types which become indistinguishable  from the system-defined data types.  For example, an object-oriented programmer could define a data type of "BOOLEAN", which would be treated by the system just a CHAR or INTEGER data type.  Relational technology does not have a facility for self-defining new data types, but the new object/relational databases do allow for data items to be created and used with the CREATE TYPE constructs which were discussed in earlier chapters.

Of course, there are also SQL extensions to allow for the creation and implementation of these abstract data types.  The following SQL is used to create a customer table.  Note that two of the components, full_name and full_address are abstract data types, and the entire customer table has been encapsulated into an ADT called customer_stuff;

CREATE TYPE

   full_address (
        street_address    varchar(20),
        city_name         varchar(20),
        state_name        char(2),
        zip_code          number(9));

CREATE TYPE

    full_name (
        first_name        varchar(20),
        MI                char(1),
        last_name         varchar(30));

CREATE TYPE

     customer_stuff (
        customer_ID       number(6),
        cust_full_name    full_name,
        cust_full_address full_address));

CREATE TABLE CUSTOMER of  customer_stuff;

Once the ADTs have been defined, SQL has been extended to allow for the use of sub-typing to address those data components that are nested within larger data types.  For example, we could use the following SQL to select the zip code for a particular customer:

SELECT
   customer_stuff.full_address.zip_code
FROM
   CUSTOMER
WHERE
customer_ID = 764645;

While dealing with ADTs is a relatively trivial extension to SQL there are many other new constructs that need to be addressed.

 

---