"Object Relational Databases"
Object Relational Databases Mike Newton Topics • Persistent objects • Options for using relational databases • SQL object-relational features • Issues Persistent objects • There is a broad adoption of object- oriented programming languages for the development of application programs • Programs need objects to persist: - durability of state - sharing of state • Object-oriented databases not generally accepted for large-scale use • Use relational databases Model architecture Object process SQL interface Relational DBMS Concepts • Object programs • Relational databases - classes - tables - subclasses + - set of rows with inheritance column values - encapsulated - value-based objects relationships - OID references - declarative queries Object programming options 1 • Use explicit SQL, such as JDBC results = stat.executeQuery( "select user_id, user_name" +"from users"); • Then process each row of the result set and get each value separately • Not getting objects – can create your own • Impedance mismatch Object programming options 2 • For those programmers who do not want to use SQL, there are object-relational mapping tools, such as Hibernate or JDO • Such a tool provides translation between objects in programs and data in tables, but it needs a mapping to specify how • There are many issues, such as mapping subclasses to tables, representing relationships and automating the mapping SQL object-relational features • Aiming to support 'objects' in an SQL database (warning: an SQL object can be anything in a database, such as a table) • Requires 'user defined types' (UDTs), which can be either: - a distinct type, which is a value of a predefined type, such as age defined as an integer but does not have its operations - a structured type, like a class SQL 'class' • Defined as a structured type • Specified with a name, attributes and method signatures • Has implicit observer and mutator methods for each attribute - encapsulation • Can have subtypes (single inheritance) • Each method body is specified separately, using either SQL or an external routine in another programming language Structured type example CREATE TYPE user AS ( user_id CHAR(5), user_name VARCHAR(20), METHOD user_string() RETURNS VARCHAR(27)); CREATE TYPE e_user UNDER user AS ( email VARCHAR(25), METHOD user_domain() RETURNS VARCHAR(20)); Storing 'objects' • A structured type can be used in two distinct ways for storing objects 1 - as a data type for a column of a table (the same as any other data type) 2 - as a template for creating a 'typed' table, where the attributes of the structured type define the columns of the typed table Using a structured type 1) CREATE TABLE equipment ( code CHAR(10), owner user, ………….) 2) CREATE TABLE users OF user (PRIMARY KEY user_id, REF IS r_id SYSTEM GENERATED, ……….) Column 'objects' • A column of a structured type holds values that are only accessible using the usual SQL queries – encapsulated, but no OID SELECT code, owner.user_id() FROM equipment • Subtypes are allowed – substitutability – but querying them needs special treatment • Such an SQL object is distinct from a program object – a data transfer is needed Typed table 'objects' • A typed table can be used just like ordinary tables for queries or update - no encapsulation • Rows of a typed table may be viewed as objects, and can be referenced from another table by a value of type REF (OID) • References can be used in queries instead of joins - navigating between tables • Subtables can be created Subtables • A subtable must be defined by a subtype of the type defining its supertable, and inherits all properties of its supertable • CREATE TABLE e_users OF e_user UNDER users • Usual class membership - all rows of subtables are included in their supertable, so the table users includes the rows from e_users but without column email Database issues • Object-relational features introduce a much wider range of options in designing a database – more difficult choices with no clear guidance how to make those choices • However, they do provide the capability of directly representing most parts of a UML class diagram within a database • There are still questions over issues such as flexibility; e.g. cannot alter typed tables Programming issues • Do object-relational features reduce the impedance mismatch for programmers? • There are many similar concepts but also significant differences • There is a need for some data transfer mechanism between the 'objects' in a database and those in a program • Still need to use SQL General issues • What DBMS products provide object- relational capabilities, and to what extent? • What support do the vendors offer? • What training is available? • What software uses these capabilities? • What tools support the development?