Chapter 9 - Object-Relational DBMSs

RDBMS weaknesses

- Poor representation of "real world" entities - Semantic overloading - Poor support for integrity and general constraints - Homogeneous data structure - Limited operations - Difficulty handling recursive queries - Impedance mismatch

Subtypes and Supertypes

- SQL:2011 allows UDTs to participate in a subtype/supertype hierarchy using the UNDER clause (user must have UNDER privilege) - A type can have more than one subtype - but currently only one supertype (that is, multiple inheritance is not supported). - A subtype inherits all the attributes and behavior (methods) of its supertype , can define additional attributes and methods, can override inherited methods.

A method can be invoked in one of three ways

- a constructor method is invoked using the NEW expression - an instance method is invoked using the standard dot notation; for example, p.fName, or using the generalized invocation format, for example, (p AS StaffType).fName() - a static method is invoked using ::, for example, if totalStaff is a static method of StaffType, we could invoke it as StaffType::totalStaff().

large object

- a data type that holds a large amount of data, such as a long text file or a graphics file - Binary Large Object (BLOB), a binary string that does not have a character set or collation association - Character Large Object (CLOB) and National Character Large Object (NCLOB), both character strings.

user-defined types (UDTs)

- also referred to as abstract data types (ADTs) Two categories: - distinct types (allows differentiation between the same underlying base types) CREATE TYPE OwnerNumberType AS VARCHAR(5) FINAL CREATE TYPE StaffNumberType AS VARCHAR(5) FINAL; - structured types

Advanced database application examples

- computer-aided design (CAD) - computer-aided manufacturing (CAM) - computer-aided software engineering (CASE) - network management systems - office information systems (OIS) - multimedia systems - digital publishing - geographic information systems (GIS)

User-defined constructor methods

- must be invoked in the context of the NEW expression. For example, we can create a new instance of PersonType and invoke the previous user-defined constructor method as follows: SET p = NEW PersonType('John', 'White', 'M'); This is effectively translated into: SET p = PersonType().PersonType('John', 'White', 'M')

1999 SQL standard object management extension

- row types - user-defined types (UDTs) - user-defined routines (UDRs) - polymorphism - inheritance - reference types and object identity - collection types (ARRAYs) - new language constructs that make SQL computationally complete - triggers - support for large objects—Binary Large Objects (BLOBs) and Character Large Objects (CLOBs)—and recursion.

typed views

- sometimes called object views or referenceable views - created based on a particular structured type and a subview can be created based on this typed view

stored and virtual attributes

- stored attribute is the default type with an attribute name and data type - virtual attributes do not correspond to stored data, but to derived data

Stonebraker's view

A four-quadrant view of the database world: Relational Object-Relational File Systems Object-Oriented ^search capable, >complexity

row type constructor

A sequence of field name/data type pairs that provides a data type to represent the types of rows in tables, so that complete rows can be stored in variables, passed as arguments to routines, and returned as return values from function calls. A row type can also be used to allow a column of a table to contain row values. In essence, the row is a table nested within a table.

collection types

ARRAY—one-dimensional array with a maximum number of elements MULTISET—unordered collection that does allow duplicates LIST—ordered collection that allows duplicates SET—unordered collection that does not allow duplicates

Mapping Classes to Relations

Accomplished via mapping each class, each subclass or the entire hierarchy to a relation

trigger

An SQL (compound) statement that is executed automatically by the DBMS as a side effect of a modification to a named table.

Object identity

An aspect of an object that never changes and that distinguishes the object from all other objects. Ideally, an object's identity is independent of its name, structure, and location, and persists even after the object has been deleted

Oracle system privileges for user-defined types

CREATE TYPE - to create user-defined types in the user's schema CREATE ANY TYPE - to create user-defined types in any schema ALTER ANY TYPE - to alter user-defined types in any schema DROP ANY TYPE - to drop named types in any schema EXECUTE ANY TYPE - to use and reference named types in any schema UNDER ANY TYPE - to create subtypes under any non-final object types UNDER ANY VIEW - to create subviews under any object view

Trigger disadvantages

Complexity Hidden functionality Performance overhead

external routine

Defined by specifying an external clause that identifies the corresponding "compiled code" in the operating system's file storage CREATE FUNCTION thumbnail(IN myImage ImageType) RETURNS BOOLEAN EXTERNAL NAME '/usr/dreamhome/bin/images/thumbnail' LANGUAGE C PARAMETER STYLE GENERAL DETERMINISTIC NO SQL

Encapsulation happens via

Happens via observer (get) function (returns the current value of the attribute) and a mutator (set) function (sets the value of the attribute to a value specified as a parameter)

INSTANTIABLE

Indicates that instances can be created for this type. If NOT INSTANTIABLE had been specified, we would not be able to create instances of this type, only from one of its subtypes

NOT FINAL

Indicates that we can create subtypes of this user-defined type

OODBMSs

Object-Oriented Database Management Systems

ORDBMSs

Object-Relational Database Management Systems Hybrid of RDBMS and OODBMS

ONLY

Produces only the details of the specific instances of a table, excluding any subtables SELECT p.lName, p.fName FROM ONLY (Person) p WHERE p.age > 65;

dereference operator

References can be used in path expressions that permit traversal of object references to navigate from one row to another. To traverse a reference, the (->) is used SELECT p.staffID->fName AS fName, p.staffID->lName AS lName FROM PropertyForRent p WHERE p.propertyNo = 'PG4';

ORDBMSs advantages

Reuse, sharing

typed tables

The rows of a typed table are considered to be objects, and the rows in the first version are not, even though the same UDT has been used in both cases. A typed table has a column for every attribute of the structured type on which it is based. In addition, a typed table has a self-referencing column that contains a unique OID (known as a reference) for each row of the table CREATE TABLE Person ( info PersonType CONSTRAINT DOB_Check CHECK(dateOfBirth > DATE '1900-01-01')); or (typed) CREATE TABLE Person OF PersonType ( dateOfBirth WITH OPTIONS CONSTRAINT DOB_Check CHECK (dateOfBirth > DATE '1900-01-01') REF IS PersonID SYSTEM GENERATED);

Transitive closure

The transitive closure of a relation R with attributes (A1, A2) defined on the same domain is the relation R augmented with all tuples successively deduced by transitivity; that is, if (a, b) and (b, c) are tuples of R, the tuple (a, c) is also added to the result

extended relational data model

There is no single extended relational data model; rather, there are a variety of these models, whose characteristics depend upon the way and the degree to which extensions were made. However, all the models do share the same basic relational tables and query language, all incorporate some concept of "object," and some have the ability to store methods or procedures/triggers as well as data in the database.

Collections

Type constructors that are used to define collections of other types. Collections are used to store multiple values in a single column of a table and can result in nested tables where a column in one table actually contains another table. The result can be a single table that represents multiple master-detail levels.

UDRs

User-defined routines define methods for manipulating data

object view

Virtual object table. Object views allow the data to be customized for different users.

substitutability

Whenever an instance of a supertype is expected an instance of the subtype can be used in its place

ORDBMSs disadvantages

complexity increased costs

There are three types of methods:

constructor methods: initialize a newly created instance of a UDT instance methods: operate on specific instances of a UDT static methods: which are analogous to class methods in some object-oriented programming languages and operate at the UDT level rather than at the instance level

Object orientation

software should be constructed out of standard, reusable components wherever possible