Object-Relational Features_ Object Database Extensions to SQL

The object-relational model combines relational databases with object database features. SQL (starting from SQL:1999 and refined in SQL:2008) incorporates object extensions including user-defined types, object identity, encapsulation, and inheritance.

Key Object Features in SQL

User-Defined Types (UDTs)

UDTs allow creating complex-structured objects with custom attributes and methods ?

CREATE TYPE PersonType AS (
    name VARCHAR(100),
    dob DATE,
    address ROW(street VARCHAR(50), city VARCHAR(30), zip VARCHAR(10))
);

Object Identity (REF Types)

System-generated unique identifiers for each row ?

CREATE TABLE Person OF PersonType (
    REF IS oid SYSTEM GENERATED
);

SYSTEM GENERATED creates automatic unique IDs. DERIVED uses traditional primary key values instead.

Encapsulation of Operations

UDTs can include methods (constructor, observer, mutator) ?

CREATE TYPE EmployeeType AS (
    emp_id INT,
    name VARCHAR(100),
    salary DECIMAL(10,2)
)
METHOD annual_salary() RETURNS DECIMAL(10,2);

Built-in functions include: constructor (creates new object), observer (reads attribute), mutator (updates attribute). Attributes can be PUBLIC, PRIVATE, or PROTECTED.

Inheritance

Type inheritance uses the UNDER keyword. Subtypes inherit all attributes and methods from supertypes ?

CREATE TYPE ManagerType UNDER EmployeeType AS (
    department VARCHAR(50),
    bonus DECIMAL(10,2)
);

Table inheritance also uses UNDER inserting into a subtable automatically inserts into supertables, and DELETE/UPDATE propagate accordingly.

Relationships via Reference

The REF type creates references between tuples (similar to foreign keys but using OIDs). SCOPE specifies which table can be referenced ?

CREATE TYPE EmploymentType AS (
    employee REF(EmployeeType) SCOPE Employee,
    company REF(CompanyType) SCOPE Company
);

-- Dereference using -> operator
SELECT E.employee->name
FROM Employment E
WHERE E.company->name = 'ABCXYZ';

Conclusion

Object-relational SQL extensions add UDTs, REF types, encapsulation, and inheritance to standard SQL. This bridges the gap between object-oriented programming and relational databases, allowing complex data structures while maintaining SQL's query capabilities and ACID compliance.