Object-Relational Features_ Object Database Extensions to SQL

In DBMS, there are object-relational features that combine elements of object databases with SQL. SQL language is used for relational databases, and it has evolved over time. In the latest standard called SQL:2008, object features have been incorporated, although not all commercial DBMSs have implemented these features yet.

Object-relational model refers to the combination of the relational model and object database enhancements. SQL has undergone revisions to include features related to XML as well.

Here are some of the object database features included in SQL:

Type Constructors

New type constructors have been added to specify complex objects. For example, the row type corresponds to a tuple or struct constructor, while the array type allows specifying collections. Other collection type constructors like set, list, and bag were not originally part of SQL/Object but were later included in the standard.

Object Identity

A mechanism for specifying object identity is provided through the use of reference types. This helps distinguish different objects within the database.

Encapsulation

Operations can be encapsulated within user-defined types (UDTs). These types can include operations as part of their declaration. UDTs are similar to abstract data types in programming languages. User-defined routines (UDRs) has definition of general methods.

Inheritance

Inheritance mechanisms are supported using the keyword "UNDER." Objects can inherit properties and behaviors from other objects. These create hierarchical structure.

These object-relational features extend the capabilities of SQL. These are flexible model data structures and support advanced data manipulation operations.

User-Defined Types and Complex Structures for Objects in DBMS

In order to create complex-structured objects and separate type declaration from table creation, DBMS now supports user-defined types (UDTs). Four collection types have been included to handle multivalued types and attributes, allowing for the specification of complex-structured objects instead of just simple records.

To create a UDT, the user can define it as part of the database schema using the following syntax:

CREATE TYPE TYPE_NAME AS (<component declarations>);

Object Identifiers Using Reference Types in DBMS

Unique object identifiers can be created using reference types in SQL. These identifiers are system-generated and ensure that each object has distinct identifier. Traditional keys from the basic relational model can also be used instead of system-generated identifiers.

To create system-generated object identifiers for individual rows in a table. Syntax is:

REF IS <OID_ATTRIBUTE> <VALUE_GENERATION_METHOD>;

Here, <OID_ATTRIBUTE> refers to the attribute. It will be used to identify each tuple in the table. The <VALUE_GENERATION_METHOD> options are "SYSTEM GENERATED" or "DERIVED." When "SYSTEM GENERATED" is used, the system automatically generates unique identifier for each tuple. On the other hand, when "DERIVED" is used, the traditional method of using user-provided primary key value to identify tuples is applied.

Creating Tables Based on User-Defined Types (UDTs) in DBMS

When a user defines a UDT, one or more tables can be created based on that UDT. UDTs that are noninstantiable can only be used as attribute types and cannot be used as a basis for table creation.

Encapsulation of Operations

User-defined type can have its own behavioral specification by defining methods in SQL. The syntax for specifying a UDT with methods is as follows:

CREATE TYPE <TYPE-NAME> (
   <LIST OF COMPONENT ATTRIBUTES AND THEIR TYPES>
   <DECLARATION OF FUNCTIONS (METHODS)>
);

SQL provides built-in functions for user-defined types. A constructor function creates a new object of the UDT type, initializing each attribute to its default value. An observer function is created for each attribute to read its value. A mutator function updates an attribute with a new value. These functions can be blocked from public use, requiring an EXECUTE privilege to access them.

UDTs can have multiple user-defined functions associated with them. Functions can be internal SQL functions written in the SQL programming language or external functions written in a host language. The attributes and functions in UDTs can be categorized as public (visible at the UDT interface), private (not visible at the UDT interface), and protected (visible only to subtypes). Virtual attributes can also be defined in UDTs, which are computed and updated using functions.

Specifying Inheritance and Overloading of Functions:

SQL supports type inheritance using the UNDER keyword. Both attributes and instance methods are inherited from supertypes. The order of supertypes in the UNDER clause determines the inheritance hierarchy. A subtype can redefine any function defined in its supertype, as long as the signature remains the same. When a function is called, the best match is selected based on the types of the arguments. SQL also considers the runtime types of parameters for dynamic linking.

Table inheritance is another feature in SQL, allowing subtables to inherit from supertables. This is specified using the UNDER keyword. When a record is inserted into a subtable, it is also inserted into its supertables, and operations like INSERT, DELETE, and UPDATE are propagated appropriately.

Specifying Relationships via Reference

A component attribute of one tuple can be a reference to a tuple in another table using the REF keyword. The SCOPE keyword specifies the name of the table whose tuples can be referenced by the reference attribute. This is similar to a foreign key but uses the system-generated value instead of the primary key value.

To refer to component attributes of tuples and row types, SQL uses dot notation. For attributes with the REF type, the dereferencing symbol "->" is used. For example, the query SELECT E.Employee->NAME retrieves the names of employees working in the company named 'ABCXYZ' from the EMPLOYMENT table.

SQL provides the SCOPE keyword to make a reference attribute point to a tuple within a specific table of the same type, allowing for referencing tuples in multiple relations of the same type.