Transcript Chapter 18 of Database Design, Application Development

Chapter 18
Object Database Management
Systems
McGraw-Hill/Irwin
Copyright © 2007 by The McGraw-Hill Companies, Inc. All rights reserved.
Outline
 Motivation for object database
management
 Object-oriented principles
 Architectures for object database
management
 Object database definition and
manipulation in SQL:2003
 Object database definition and
manipulation in Oracle 10g
18-2
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.
 Integration of complex data with simple
data drives the demand for object
database technology
18-3
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.
18-4
Application Examples
 Dental Office Support
 Real Estate Listing Service
 Auto Insurance Claims
18-5
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.
18-6
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.
18-7
Bond Class Example
CLASS Bond {
// VARIABLES:
ATTRIBUTE Float IntRate;
ATTRIBUTE Date Maturity;
// METHODS:
Float Yield();
// Computes the Bond’s Yield
};
18-8
Inheritance
 Sharing of data and code among similar
classes (classes and subclasses).
 Inherit variables and methods from parent
classes
 Use methods and variables of ancestor
classes
 Provides an improved organization of
software and incremental reusability.
18-9
Inheritance Examples
Bond
IntRate
Maturity
Yield
Poin t
x,y
Dist ance
Equals
Inheritance
Relationships
C ol orPoin t
Color
Brighten
SubClasses
C orporate
Rat ing
T heCompany
Junk
18-10
Multiple Inheritance Example
Se curity
Symbol
SecName
LastClose
Stock
OutShares
IssuedShares
Yield
Inheritance
conf lict
Bond
IntRate
Maturity
Yield
C onve rtibl e
ConvPrice
ConvRatio
18-11
Polymorphism
 Ability to choose among multiple
implementations
 Benefits
 Incremental modification of code
 Smaller vocabulary of method names
 Requesting a method execution involves
sending a message to an object
 Client-server processing and objectoriented computing are closely related.
18-12
Processing a Message
Point class
Distance code
Message f orwarded
to parent class
Message sent toColorPoint
object (CP1) to compute
distance
ColorPoint class
CP1 object
18-13
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
18-14
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
18-15
Programming Languages
versus DBMSs
 Programming languages have used
object-oriented principles for many years.
 Programming languages emphasize
software maintenance and code
reusability.
 Object DBMSs are more recent.
 Relax encapsulation to reference an
object’s data in a query.
 Simpler inheritance mechanisms in
DBMSs
18-16
Object Database Architectures
 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
18-17
Large Objects and External
Software
 Large object storage along with external
software to manipulate large objects
 BLOB and CLOB data types
 The large object approach is simple to
implement and universal.
 The large object approach suffers from
serious performance drawbacks.
18-18
Large Object Architecture
External sof tware f or
manipulating complex data
SQL statements
Simple and
complex data
Simple
data
Complex
data
Database serv er
Relational database
18-19
Specialized Media Servers
 Dedicated server manages complex data
 Application programming interface (API) to
access complex data.
 Good performance for specific kinds of
complex data
 Limited range of operations supported
 Poor performance when combining simple
and complex data
18-20
Specialized Media Server
Architecture
SQL statements
and results
API calls and results
Database
Database server
Media server
Media base
18-21
Object database middleware
 Middleware to manage complex data
stored outside of a database along with
traditional data stored in a database
 Integrate complex data stored on PCs and
remote servers with relational databases
 Possible performance problems because
of a lack of integration with a DBMS.
18-22
Object Middleware Approach
SQL statements and results
Obje ct m iddle w are
SQL statements
and results
Database
API calls
and results
Database server Media server
Media base
18-23
Object-Relational DBMS
 A relational DBMS extended with an object
query processor for user-defined data
types
 User-defined types for complex data
 User-defined functions in SQL statements
 SQL:2003 standard
 Good integration of complex data with
reliability of relational DBMS
18-24
Component Architecture for
Object Relational DBMSs
SQL statements
and results
Object query processor
(parser, optimizer, display
manager)
API calls and results
Relational kerne l
(transaction processing,
storage management, buffer
management)
Database
18-25
Typical User-Defined Types







Audio
Video
Text
Image
Spatial
Time series
XML
18-26
Other Object Features
 Subtable families
 Reference and row data types
 Nested tables
18-27
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.
18-28
Component Architecture for
Object-Oriented DBMSs
OQL statements
and results
Object query processor
(parser, optimizer, display
manager)
API calls and results
Relational kerne l
(transaction processing,
storage management, buffer
management)
Database
18-29
Summary of Architectures
 Market niche for each architecture
 Simpler architectures less popular over
time
 Object database middleware coexist with
other approaches
 Object-relational approach dominates
object-oriented approach
18-30
SQL:2003 Object Features




Very large standard
Core language part
Parts and packages for non core features
Details about basic and enhanced object
support
 Two levels of conformance
18-31
SQL:2003 Parts
 Core parts (1,2,11): framework,
foundation, schemas
 Non core parts





Call level interface
Persistent stored modules
Management of external data
Object level bindings
XML specifications
18-32
SQL:2003 Packages






Enhanced data-time facilities
Enhanced integrity management
Basic object support
Enhanced object support
Active databases
OLAP facilities
18-33
User-Defined Types
 Bundles data and procedures
 Support definition of structured types, not
just extensions of standard types
 Use 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.
18-34
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;
18-35
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
18-36
Implicit Methods
 Automatically exist for all user-defined
types
 Constructor method: creates an empty
instance
 Observer methods: retrieve values
 Mutation methods: change values
18-37
User-Defined Type Using
an Array
Example 2: Triangle type using an ARRAY
CREATE TYPE Triangle AS
( Corners Point ARRAY[3],
Color INTEGER )
METHOD Area() RETURNS FLOAT,
-- Computes the area
METHOD Scale (Factor FLOAT) RETURNS Triangle
-- Computes a new triangle scaled by factor
NOT FINAL;
18-38
User-Defined Type Using a
Multiset
Example 3: Polygon type using an ARRAY
CREATE TYPE Triangle AS
( Corners Point MULTISET,
Color INTEGER )
METHOD Area() RETURNS FLOAT,
-- Computes the area
METHOD Scale (Factor FLOAT) RETURNS
Polygon
-- Computes a new polygon scaled by factor
NOT FINAL;
18-39
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
18-40
Row Type Usage
Example 4: 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 );
18-41
User-Defined Type Usage
Example 5: 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) );
18-42
Subtable Families
 A table can be declared as a subtable of
another table.
 A subtable inherits the columns of its
parent tables.
 SQL:2003 limits inheritance for tables to
single inheritance.
 Set inclusion determines the relationship
of a table to its subtables.
18-43
Subtable Example
Example 6: 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 UNDER
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;
18-44
Subtable Side Effects
 Subtable insert: corresponding row is
inserted into each ancestor table.
 Parent update: column updated in all
direct and indirect subtables
 Inherited column update: column is
changed in each ancestor table
 Deletion: every corresponding row in both
parent and subtables is also deleted.
18-45
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.
18-46
Using the ROW Keyword
Example 7: 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 8: 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');
18-47
Obtaining Object Identifiers
Example 9: 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;
18-48
Path Expression Example
Example 10: 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'
18-49
Oracle 10g Object Features
 Supports most parts of the SQL:2003
object packages
 User-defined types
 Typed tables
 Other object features
18-50
User-Defined Type Example
Example 11: Point type in Oracle 10g
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;
18-51
Inheritance
Example 12: ColorPoint type in Oracle 10g
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;
18-52
Typed Tables
Example 13: 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 ;
18-53
Inheritance for Typed Tables
Example 14: 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 ;
18-54
Inserting into Typed Tables
Example 15: 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;
18-55
Path Expressions
Example 16: 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 17: Path expression using the dot operator
SELECT PropNo, P.Address.City,
P.AgentRef.Address.City
FROM Property P
WHERE P.AgentRef.Name = 'John Smith';
18-56
Other Object Features




Type substitutability for subtables
Hierarchical views
Nested tables
XML support
18-57
Type Substitution and
Hierarchical Views
 Type substitution: row or column can
contain type X or subtypes of X
 Hierarchical views: views with inheritance
 Limitations for managing set inclusion
relationships
 Must manage storage model for hierarchical
views
 Difficult to use reference types and subtype
columns
18-58
Nested Table Support
 Utility of nested tables is not clear for
business databases
 Oracle 10g features





TABLE and NESTED TABLE constructors
Query results can include nested tables.
Comparison operators for nested tables
Set operators for nested tables
Views with nested tables
18-59
XML Document Support
 Part 14 of SQL:2003
 Oracle 10g features





XMLType in columns
XML schema support
XML/SQL duality
XML query operators
Optimization with query rewrite
18-60
Summary
 Three principles of object-oriented computing
guide the development of object DBMSs.
 A number of object DBMS architectures are
commercially available.
 SQL:2003 supports definition and manipulation
of object relational databases.
 Oracle 10g is a significant implementation of the
SQL:2003 object packages
18-61