ObjectRelational Databases

1
Object-Relational Databases

• David Nelson
• October 2002

2
Contents

• Background
• Extensions to Relational Model
• Database World
• Advantages and Disadvantages of ORDBMS
• Third Generation Database Models
• Postgres
• SQL3
• SQL3 and OQL
• Comparison of OO/OR Models
• Further Reading

3
Extensions to Relational Model

• Advanced Emerging Database Applications use
• user extensible type system, encapsulation,
  inheritance, polymorphism, dynamic binding,
  complex objects, object identity
• Extend relational model with OO features
• Extended Relational DBMS
• Object-Relational DBMS
• Universal Server
• Standard based on SQL - SQL3 (started 1991!)

4
The Database World

• Stonebraker proposed a four quadrant view of the
  database world

Search capabilities/ multi-user support
Data complexity/extensibility

• However distinction between OQL and SQL is
  becoming less clear

5
Object-Relational Advantages

• Weaknesses of RDBMS given last week
• Reuse and Sharing
• extending the DBMS server to perform standard
  functionality centrally
• functionality shared by all applications, e.g
  spatial data types
• Evolutionary rather than revolutionary
• SQL3 upwardly compatible with current SQL
  standard

6
Object-Relational Disadvantages

• Complexity and Associated Increased Costs
• simplicity and purity of relational model is lost
• majority of applications do not achieve optimal
  performance
• Semantic gap between object-oriented and
  relational
• OO applications not as data centric as Relational
• Objectives of Initial SQL standard were to
  minimise user effort and be easy to learn

7
Third Generation Database Manifesto, Stonebraker
1990

• 1. A third generation DBMS must have a rich type
  system
• 2. Inheritance is a good idea
• 3. Functions, including database procedures and
  methods and encapsulation, are a good idea
• 4. Unique identifiers for records should be
  assigned by the DBMS only if a user-defined
  primary key is not available
• 5. Rules (triggers, constraints) will become a
  major feature in future systems. They should not
  be associated with a specific function or
  collection

8
3rd Generation DBMS

• 6. Essentially, all programmatic access to a
  database should be through a non-procedural,
  high-level access language
• 7. There should be at least two ways to specify
  collections, one using enumeration of members,
  and one using the query language to specify
  membership
• 8. Updateable views are essential
• 9. Performance indicators have almost nothing to
  do with data models and must not appear in them
• 10. 3rd generation DBMS must be accessible from
  multiple high-level languages

9
3rd Generation DBMS

• 11. Persistent forms of a high-level language,
  for a variety of high-level languages, are a good
  idea. They will all be supported on top of a
  single DBMS by compiler extensions and a complex
  runtime system
• 12. For better or worse, SQL is intergalactic
  dataspeak.
• 13. Queries and their resulting answers must be
  the lowest level of communication between a
  client and a server

10
3rd Generation DBMS

• Atkinson, OODB Manifesto, 1989
• Stonebraker et al devised 3rd Generation DB
  System Manifesto in 1990
• Darwen and Date published a 3rd Manifesto in 1995
  in defense of the relational data model
• certain OO features are desirable, but should be
  orthogonal to the relational model
• SQL is a perversion of the model
• define a language D, but with a front-end layer
  that allows SQL to be used

11
The Third Manifesto

• D should be subject to
• RM Prescriptions
• Other Orthogonal (OO) Prescriptions
• RM Proscriptions
• OO Proscriptions
• RM and OO Very Strong Suggestions
• Primary object is domain
• a named set of encapsulated values, of arbitrary
  complexity (equivalent to data type or class)

12
RM Prescriptions

• 26, including
• Domains
• Typed scalars
• Tuples
• Relations
• Relational algebra
• Integrity constraints
• Candidate keys

• Scalar, tuple, relation variables
• Assignment, comparison, equality
• Databases
• Transactions
• catalog
• Base vs virtual relvars

13
OO Prescriptions

• Compile time type checking
• Single inheritance
• Multiple inheritance
• Computational completeness
• Explicit transactions boundaries
• Nested transactions
• Aggregates and empty sets

14
Proscriptions

• RM Proscriptions
• 10 including,
• No attribute ordering
• No tuple ordering
• No duplicate values
• No nulls
• Not SQL

• OO Proscriptions
• Relvars are not domains
• No object ids

15
Very Strong Suggestions

• RM, 9 including,
• System keys
• Foreign keys
• Candidate key inference
• Quota queries
• Transitive closure
• Special (default) values
• SQL migration

• OO
• Type inheritance
• Types and operators unbundled
• Collection type generators
• Conversion to/from relations
• Single level store

16
Postgres

• A research database system designed as a
  successor to Ingres
• Objectives
• 1. Provide better support for complex objects
• 2. Provide user extensibility for data types,
  operators and access methods
• 3. Provide active database facilities (alerters
  and triggers), and inferencing support
• 4. Simplify DBMS code for crash recovery
• 5. Take advantage of optical disks,
  multi-processor workstations, custom VLSI chips
• 6. Make as few changes as possible to relational
  model

17
Postgres

• Postgres extended the relational model to
  include
• Abstract Data Types - Rules
• Data of type procedure
• Attributes in a relation are atomic or structured
• all data types are defined as ADTs
• Relations can inherit
• but not abstract data types
• Each relation has an implicit OID
• created and maintained by the system

18
Oracle 8

• An object-relational extension to Oracle 7
• Object types can be used to create object tables
  with object identifiers
• attributes
• methods
• Does not support object hierarchies
• Oracle 9 does support object hierarchies
• New types
• VARRAYs and nested tables
• REFs
• LOBs

19
SQL3 (aka SQL99)

• The draft ANSI/ISO SQL3 standard includes new
  features including
• row and reference type constructors
• user defined types (UDTs)
• can participate in supertype/subtype
  relationships
• user defined procedures, functions and operators
• type constructors for collection types
• arrays, sets, lists, multisets
• support for large objects
• BLOBS and CLOBS

20
SQL3

• Row types
• a data type that can represent types of rows in
  tables
• e.g.
• CREATE TABLE branch(
• bno VARCHAR(3),
• address ROW(
• street VARCHAR(25),
• town VARCHAR(15),
• pcode ROW( city_id VARCHAR(4)
• subpart VARCHAR(4))))
• INSERT INTO branch
• VALUES(B5, (22 Deer Rd, Sidcup, (SW1,
  4EH)))

21
SQL3

• User Defined Types (UDT)
• abstract data types
• consists of one or more attribute defns
• encapsulation supported
• CREATE TYPE person_type AS (
• PRIVATE
• date_of_birth DATE CHECK(date_of_birth gt DATE
  1990-01-01)
• PUBLIC
• fname VARCHAR(15) NOT NULL,
• lname VARCHAR(15) NOT NULL,
• FUNCTION get_age (P person_type) RETURNS
  INTEGER
• RETURN / code to calc age /
• END ...
• END) NOT FINAL

22
SQL3

• User defined routines (UDR)
• may be defined as part of a UDT or as part of a
  schema
• can be a procedure, function or iterative routine
• Can be written in SQL or in an external
  programming language
• Polymorphism
• uses a generalised object model
• No 2 functions in the same schema allowed to have
  same signature (no. of arguments, same data
  types, same return type)
• No 2 procedures allowed to have same name and
  number of parameters

23
SQL3

• Subtypes/supertypes
• multiple inheritance is supported
• substitutability
• when an instance of a supertype is expected, an
  instance of the subtype can be used in place
• Tables
• A UDT instance can only persist if stored as a
  column in a table
• can use table inheritance
• Completely independent from UDT facility

24
SQL3

• Querying
• uses SQL92 syntax with extensions to handle
  objects
• e.g.
• SELECT s.lname, s.get_age
• FROM staff s
• WHERE s.is_manager
• SELECT p.lname, p.address
• FROM person p
• WHERE p.get_age gt 65
• SELECT p.lname, p.address
• FROM ONLY (person) p
• WHERE p.get_age gt 65

25
SQL3

• Reference Types and OID
• system generated, type REF
• Reference types can be used to define
  relationships between row types
• reference types uniquely identify rows
• allows rows to be shared across tables
• complex joins can be replaced by simple path
  expressions
• reference types do not provide referential
  integrity
• Collection types
• ARRAYs, LISTs, SETs, MULTISETs

26
SQL3

• Persistent Stored Modules
• SQL3 now computationally complete
• New statements added
• blocks
• Assignment
• IF .. THEN .. ELSE .. ENDIF, and CASE
• REPEAT BLOCKS
• CALL and RETURN for invoking procedures
• Condition handling

27
SQL 3

• Triggers
• An SQL statement that is automatically executed
  by the DBMS as a side effect of a modification to
  a table
• Triggering events include insertion, deletion and
  update of rows in a table
• Useful for
• Verifying input data
• Maintaining complex integrity constraints
• alerts

28
SQL and OQL

• ODMG and X3H2 consortium are trying to provide a
  common core query language.
• Differences between SQL/OQL
• only tables can persist
• objects are only persistent when stored in a
  table (potentially OID can change)
• SQL3 collection types can not be used in queries
  as freely as in OQL
• SQL queries apply only to tables and result in
  tables, so collection types must first be cast to
  tables, and then cast back to collections

29
Comparison of ORDBMS v OODBMS
30
Further Reading

• Connolly and Begg, chapter 23
• Stonebraker, Object-Relational DBMSs The Next
  Great Wave, 1996.
• Postgres papers - available through Postgres
  tutorial.