Transcript Chapter 18 of Database Design, Application Development

Chapter 18
Object Database Management Systems
Outline
Motivation for object database
management
 Object-oriented principles
 Architectures for object database
management
 Object database definition and
manipulation in SQL:1999
 Object database definition and
manipulation in Oracle 9i

Motivation: Complex Data
Most relational DBMSs support only a few
data types.
 Many business applications require large
amounts of complex data such as images,
audio, and video.
 The need to integrate complex data with
simple data drives the demand for object
database technology.

Motivation: Type System
Mismatch
Increasing use of database access in
procedural code
 Different data types used in programming
languages versus DBMSs
 Data type mismatch makes software more
difficult to develop.
 A relational DBMS cannot perform
elementary operations on complex data.

Application Examples
Dental Office Support
 Real Estate Listing Service
 Auto Insurance Claims

Object-Oriented Principles
An object is a combination of data and
procedures.
 A class is a prototype that defines the
variables and methods common to all
objects of the class.
 Three underlying principles:
encapsulation, inheritance and
polymorphism.

Encapsulation
Objects can be accessed only through their
interfaces.
 Classes can be reused rather than just
individual procedures.
 More complex classes can be defined
using simpler classes.
 Provides a form of data independence.

Bond Class Example
CLASS Bond {
// VARIABLES:
ATTRIBUTE Float IntRate;
ATTRIBUTE Date Maturity;
// METHODS:
Float Yield();
// Computes the Bond’s Yield
};
Inheritance
Sharing of data and code among similar
classes (classes and subclasses).
 Inherit variables and methods from parent
classes
 When using the subclasses, the methods in
the parent classes can be used.
 Inheritance provides an improved
organization of software and incremental
reusability.

Inheritance Examples
Bond
IntRate
Maturity
Yield
Point
x,y
Distance
Equals
Inheritance
Relationships
ColorPoint
Color
Brighten
SubClasses
Corporate
Rating
TheCompany
Junk
Multiple Inheritance Example
Security
Symbol
SecName
LastClose
Stock
OutShares
IssuedShares
Yield
Inheritance
conflict
Bond
IntRate
Maturity
Yield
Convertible
ConvPrice
ConvRatio
Polymorphism
Ability of a computing system to choose
among multiple implementations
 Benefits

– Fewer, more reusable methods
– incremental modification of code
Requesting a method execution involves
sending a message to an object
 Client-server processing and objectoriented computing are closely related.

Processing a Message
Point class
Distance code
Message forwarded
to parent class
Message sent to ColorPoint
object (CP1) to compute
distance
ColorPoint class
CP1 object
Binding
Associating an implementation with a
message
 Static binding

– Performed at compile-time
– More efficient but less flexible

Dynamic binding
– Performed at run-time (late binding)
– More flexible but less efficient
Strong Type Checking
Complex expressions can involve many
methods and objects
 Incompatibility errors common in code
 Ability to ensure that programming code
contains no incompatibility errors
 An important kind of error checking for
object-oriented coding

Programming Languages
versus DBMSs
Programming languages have used objectoriented principles for many years.
 Programming languages emphasize
software maintenance and code reusability.
 Object DBMSs are more recent.
 Encapsulation usually is relaxed so that an
object’s data can be referenced in a query.
 Inheritance mechanisms usually are simpler
in DBMSs

Architectures for Object
Database Management





Adding object-oriented features to a DBMS is a
good idea
Many approaches about the features to add and
how features should be added.
Some approaches provide small extensions that
leave object features outside the DBMS.
Other approaches involve a complete rewrite of
the DBMS to accommodate objects
Marketplace will determine best approaches
Large Objects and External
Software
Storage of large objects in a database along
with external software to manipulate large
objects.
 Complex data are stored in a field using
the BLOB (binary large object) data type.
 The large object approach is simple to
implement and universal.
 The large object approach suffers from
serious performance drawbacks.

Large Object Architecture
External software for
manipulating complex data
SQL statem ents
Simple and
complex data
Simple
data
Complex
data
Database server
Relational database
Specialized Media Servers
The use of a dedicated server to manage
complex data outside of a database.
 Programmers use an application
programming interface (API) to access
complex data.
 Provide good performance for specific
kinds of complex data.
 The range of operations may be limited.
 May perform poorly when combining
simple and complex data.

Specialized Media Server
Architecture
SQL statements
and results
API calls and results
Database
Database server
Media server
Media base
Object database middleware
The use of middleware to manage complex
data stored outside of a database along
with traditional data stored in a database.
 Provides a way to integrate complex data
stored on PCs and remote servers with
relational databases.
 Object middleware can suffer performance
problems because of a lack of integration
with a DBMS.

Object Middleware Approach
SQL statements and results
Object middleware
SQL statements
and results
Database
Database server
API calls
and results
Media server
Media base
Object Relational DBMS for
User-Defined Types
A relational DBMS extended with an object
query processor for user-defined data types.
 Complex data is added as a user-defined
type.
 User-defined functions can be defined and
then used in SQL statements.
 SQL:1999 provides the standard for object
relational DBMSs.
 Provide good integration of complex data
but reliability may be a concern.

Component Architecture for
Object Relational DBMSs
SQL statements
and results
Object query processor
(parser, optimizer, display
manager)
API calls and results
Relational kernel
(transaction processing,
storage management, buffer
management)
Database
Object-Oriented DBMS
A new kind of DBMS designed especially
for objects.
 Object-oriented DBMSs have an object
query processor and an object kernel.
 The Object Data Management Group
(ODMG) provides the standard for objectoriented DBMSs.

Component Architecture for
Object-Oriented DBMSs
OQL statements
and results
Object query processor
(parser, optimizer, display
manager)
API calls and results
Relational kernel
(transaction processing,
storage management, buffer
management)
Database
Summary of Architectures
Architecture
Large objects
Example Products Comments
Most SQL-92
DBMSs
Media servers
Oracle 7.3 with text
and spatial data
servers
Object database Microsoft
middleware
Universal Data
Access
Object relational IBM UniData Data
(SQL3)
Blades, IBM DB2
Extenders, Oracle
9i
Object-oriented
(ODMG)
Poor performance; no query language
support; universal storage capability
No query language support; poor
performance involving simple and complex
data; good performance on complex data
Uncertain performance when combining
simple and complex data; Ability to combine
diverse data sources
Uncertain reliability; good query language
support; some type mismatch with
programming languages; good support with
specialized storage structures
ObjectStore,
Good query language support; uncertain
UniSQL, O2,
performance for traditional applications;
Versant, Gemstone good type match with programming
languages
Object Database Features in
SQL:1999
Very large standard
 Core language part
 Packages
 Details about basic and enhanced object
support
 Two levels of conformance

SQL:1999 Packages
SQL3 Package
Core
Scope
SQL environment, SQL implementation, tables, views,
predefined data types, SQL statements, conformance
specifications
Persistent Stored Modules Computational completeness, stored modules, function
overloading
Call Level Interface
Call interface for SQL
Enhanced Datetime
Time zone specification, interval data type
Facilities
Enhanced Integrity
Assertions, triggers, constraint management
Management
OLAP Facilities
Cube and roll-up operators, row and table constructors, FULL
JOIN and INTERSECT operators
Basic Object Support
User-defined data types, single inheritance, reference types,
arrays
Enhanced Object Support Path expressions, subtable definition, subtable search, subtypes
User-Defined Types
Bundles data and procedures
 Support definition of structured types, not
just extensions of standard types
 User-defined types can be used as data types
for columns in tables, passed as parameters,
and returned as values.
 User-defined functions can be used in
expressions in the SELECT, the WHERE,
and the HAVING clauses.

User-Defined Type Example
Example 1: Point Type
CREATE TYPE Point AS
( x FLOAT,
-- X coordinate
y FLOAT )
-- Y coordinate
METHOD Distance(P2 Point) RETURNS FLOAT,
-- Computes the distance between 2 points
METHOD Equals (P2 Point) RETURNS BOOLEAN
-- Determines if 2 points are equivalent
NOT FINAL
INSTANTIABLE;
Explicit Methods
Return single values and use input
parameters
 Implicit first parameter: part of userdefined type
 CREATE METHOD statement for method
body
 Mutation methods: change values
 Procedures and functions not associated
with types

Implicit Methods
Automatically exist for all user-defined
types
 Constructor method: creates an empty
instance
 Observer methods: retrieve values
 Mutation methods: change values

User-Defined Type using an
Array
Example 2: Polygon type using an ARRAY
CREATE TYPE Polygon AS
( Corners Point ARRAY[10],
Color INTEGER )
METHOD Area() RETURNS FLOAT,
-- Computes the area
METHOD Scale (Factor FLOAT) RETURNS Polygon
-- Computes a new polygon scaled by factor
NOT FINAL;
Table Definitions
Traditional style: foreign keys to link
tables
 Typed tables: supports object identifiers
and object references
 Row type constructor: supports rows as
variables and parameters

Example of table definition with
a row type
Example 3: Property table definition with a row type
CREATE TABLE Property
(PropNo INTEGER,
Address ROW (Street VARCHAR(50),
City
VARCHAR(30),
State
CHAR(2),
Zip
CHAR(9) ),
SqFt
INTEGER,
View
BLOB,
AgentNo INTEGER,
Location Point,
CONSTRAINT PropertyPK PRIMARY KEY(PropNo),
CONSTRAINT AgentFK FOREIGN KEY(AgentNo) REFERENCES
Agent );
Table Definition with a UserDefined Type
Example 4: Definition of AgentType, followed by
the Agent table based on AgentType
CREATE TYPE AddressType AS
(Street VARCHAR(50),
City
VARCHAR(30),
State
CHAR(2),
Zip
CHAR(9) )
NOT FINAL;
CREATE TYPE AgentType AS
(AgentNo INTEGER,
Name
VARCHAR(30),
Address AddressType,
Phone
CHAR(13),
Email
VARCHAR(50) )
NOT FINAL;
CREATE TABLE Agent OF AgentType
(REF IS AgentOId SYSTEM GENERATED,
CONSTRAINT AgentPK PRIMARY KEY(AgentNo) );
Subtable Families
A table can be declared as a subtable of
another table.
 A subtable inherits the columns of its
parent tables.
 SQL:1999 limits inheritance for tables to
single inheritance.
 Set inclusion determines the relationship of
a table to its subtables.

Subtable Example
Example 5: Subtable of Property Table
CREATE TYPE ResidentialType UNDER PropertyType
(BedRooms
INTEGER,
BathRooms INTEGER,
Assessments DECIMAL(9,2) ARRAY[6] )
NOT FINAL
INSTANTIABLE;
CREATE TABLE Residential OF ResidentialType UND
Property;
CREATE TYPE IndustrialType UNDER PropertyType
(Zoning
VARCHAR(20),
AccessDesc VARCHAR(20),
RailAvailable BOOLEAN,
Parking VARCHAR(10) )
NOT FINAL
INSTANTIABLE;
CREATE TABLE Industrial OF IndustrialType UNDER
Property;
Side effects when manipulating
rows in subtable families
On insert into a subtable, a corresponding
row is inserted into each parent table.
 On update in a parent table, the column is
also updated in all direct and indirect
subtables that inherit the column.
 On update of an inherited column, the
column is changed in the corresponding
rows of direct and indirect parent tables.
 On delete, every corresponding row in both
parent and subtables is also deleted.

Manipulating Complex Objects
and Subtable Families
Path expressions to manipulate columns
with row references.
 References to methods in expressions
using the dot notation
 Testing membership in a specific table
without being a member of any subtables.

Using the ROW Keyword
Example 6: Using the ROW keyword in an INSERT statement.
INSERT INTO Agent
(AgentNo, Name, Address, Email, Phone)
VALUES (999999, 'Sue Smith',
ROW('123 Any Street', 'Denver', 'CO',
'80217'), '[email protected]',
'13031234567')
Example 7: Using a type name in an INSERT statement.
INSERT INTO Agent
(AgentNo, Name, Address, Email, Phone)
VALUES (999999, 'Sue Smith',
AddressType('123 Any Street', 'Denver',
'CO', '80217'), '[email protected]',
'13031234567');
Obtaining Object Identifiers
Example 8: Using a SELECT statement to retrieve the
object identifier of the related Agent row.
INSERT INTO Residential
(PropNo, Address, SqFt, AgentRef,
BedRooms, BathRooms, Assessments)
SELECT 999999, AddressType('123 Any
Street', 'Denver', 'CO', '80217'),
2000, AgentOID, 3, 2,
ARRAY[190000, 200000]
FROM Agent
WHERE AgentNo = 999999;
Example of path expression
versus traditional expression
Example 9: SELECT statement with path expressions and the
dereference operator
SELECT PropNo, P.Address.City,
P.AgentRef->Address.City
FROM Property P
WHERE AgentRef->Name = 'John Smith'
Oracle 9i Object Features
Supports most parts of the SQL:1999
object packages
 User-defined types
 Typed tables
 Other object features

User-Defined Type Example
Example 10: Point type in Oracle 9i
CREATE TYPE Point AS OBJECT
( x FLOAT(15),
y FLOAT(15),
MEMBER FUNCTION Distance(P2 Point) RETURN
NUMBER,
-- Computes the distance between 2 points
MEMBER FUNCTION Equals (P2 Point) RETURN
BOOLEAN,
-- Determines if 2 points are equivalent
MEMBER PROCEDURE Print )
NOT FINAL
INSTANTIABLE;
Inheritance
Example 11: ColorPoint type in Oracle 9i
CREATE TYPE ColorPoint UNDER Point
(Color INTEGER,
MEMBER FUNCTION Brighten (Intensity
INTEGER) RETURN INTEGER,
-- Increases color intensity
MEMBER FUNCTION Equals (CP2 ColorPoint)
RETURN BOOLEAN,
-- Overriding is not used because the two
-- Equals methods have different
signatures.
OVERRIDING MEMBER PROCEDURE Print )
NOT FINAL
INSTANTIABLE;
Typed Tables
Example 12: Typed table example
CREATE TYPE PropertyType AS OBJECT
(PropNo INTEGER,
Address AddressType,
SqFt
INTEGER,
AgentRef REF AgentType,
Location Point )
NOT FINAL
INSTANTIABLE;
CREATE TABLE Property OF PropertyType
( CONSTRAINT PropertyPK PRIMARY
KEY(PropNo),
CONSTRAINT AgentRefFK FOREIGN
KEY(AgentRef) REFERENCES Agent )
OBJECT IDENTIFIER IS SYSTEM GENERATED ;
Inheritance for Typed Tables
Example 13: Typed table example with inheritance
CREATE TYPE AssessType AS VARRAY(6) OF
DECIMAL(9,2);
CREATE TYPE ResidentialType UNDER PropertyType
(BedRooms INTEGER,
BathRooms INTEGER,
Assessments AssessType )
NOT FINAL INSTANTIABLE;
CREATE TABLE Residential OF ResidentialType
(CONSTRAINT ResidentialPK PRIMARY KEY(PropNo),
CONSTRAINT AgentRefFK1 FOREIGN KEY(AgentRef)
REFERENCES Agent )
OBJECT IDENTIFIER IS SYSTEM GENERATED ;
Inserting into Typed Tables
Example 14: Insert rows into the residential and property tables
INSERT INTO Residential
(PropNo, Address, SqFt, AgentRef, BedRooms,
BathRooms, Assessments)
SELECT 999999, AddressType('123 Any Street',
'Denver', 'CO', '80217'),
2000, REF(A), 3, 2, AssessType(190000,
200000)
FROM Agent A
WHERE AgentNo = 999999;
INSERT INTO Property
(PropNo, Address, SqFt, AgentRef)
SELECT 999999, AddressType('123 Any Street',
'Denver', 'CO', '80217'), 2000, REF(A)
FROM Agent A
WHERE AgentNo = 999999;
Path Expressions
Example 15: Path expression using the DEREF function
SELECT PropNo, P.Address.City,
DEREF(AgentRef).Address.City
FROM Property P
WHERE DEREF(AgentRef).Name =
'John Smith';
Example 16: Path expression using the dot operator
SELECT PropNo, P.Address.City,
P.AgentRef.Address.City
FROM Property P
WHERE P.AgentRef.Name = 'John Smith';
Other Object Features
Type substitutability for subtables
 Hierarchical views
 Nested tables

Summary




Three principles of object-oriented computing
guide the development of object DBMSs.
A number of object DBMS architectures are
commercially available.
SQL:1999 supports definition and manipulation
of object relational databases.
Oracle 9i is a significant implementation of the
SQL:1999 object packages