III. Current Trends

1
III. Current Trends

• Chapter 24 Introduction to Object DBMSs

2
Advanced Database Applications

• Widespread acceptance of RDBMSs. But apps with
  different needs to traditional business apps
• Computer-Aided Design (CAD) Stores data relating
  to mechanical and electrical design
• Data has many types, each with a small number of
  instances.
• Designs may be very large.
• Computer-Aided Manufacturing (CAM) Stores similar
  data to CAD, plus data about discrete production.
• Computer-Aided Software Engineering (CASE) Stores
  data about stages of software development
  lifecycle
• Network Management Systems Coordinate delivery of
  communication services across a computer network.
  
• Systems handle complex data and require real-time
  performance and continuous operation

3
Advanced Database Applications

• Office Information Systems (OIS) and Multimedia
  Systems Stores data relating to computer control
  of information in a business, including email
  documents, invoices
• Digital Publishing Becoming possible to store
  books, journals, papers, and articles
  electronically and deliver them over high-speed
  networks to consumers
• As with OIS, digital publishing is being extended
  to handle multimedia documents consisting of
  text, audio, image, and video data and animation.
• Geographic Information Systems (GIS) GIS database
  stores spatial and temporal information, such as
  that used in land management and underwater
  exploration
• Interactive and Dynamic Web sites Need to handle
  multimedia content and to interactively modify
  display based on user preferences and user
  selections. Also have added complexity of
  providing 3D rendering.

4
Weaknesses of RDBMSs

• Poor Representation of Real World Entities
• Normalization leads to relations that do not
  correspond to entities in real world.
• Semantic Overloading
• Relational model has only one construct for
  representing data and data relationships the
  relation.
• Relational model is semantically overloaded.
• Poor Support for Integrity and Enterprise
  Constraints
• Homogeneous Data Structure
• Relational model assumes both horizontal and
  vertical homogeneity.
• Many RDBMSs now allow Binary Large Objects
  (BLOBs).
• Limited Operations
• RDBMs only have a fixed set of operations which
  cannot be extended.

5
Weaknesses of RDBMSs

• Difficulty Handling Recursive Queries
• Extremely difficult to produce recursive queries.
• Extension proposed to relational algebra to
  handle this type of query is unary transitive
  (recursive) closure operation.
• Impedance Mismatch
• Most DMLs lack computational completeness.
• To overcome this, SQL can be embedded in a
  high-level 3GL.
• This produces an impedance mismatch - mixing
  different programming paradigms. 30 of
  programming effort and code space is expended on
  this type of conversion.
• Other Problems with RDBMSs
• Transactions are generally short-lived and
  concurrency control protocols not suited for
  long-lived transactions.
• Schema changes are difficult.
• RDBMSs are poor at navigational access.

6
Object-oriented concepts

• To start with, a brief review of underlying
  themes
• Abstraction Process of identifying essential
  aspects of an entity and ignoring unimportant
  properties.
• - Concentrate on what an object is and what it
  does, before deciding how to implement it.
• Encapsulation Object contains both data
  structure and set of operations used to
  manipulate it.
• Information Hiding Separate external aspects of
  an object from its internal details, which are
  hidden from outside.
• Allows internal details of object to be changed
  without affecting apps that use it, provided
  external details remain same.
• Provides data independence.

7
Objects and Attributes

• Object Uniquely identifiable entity that
  contains both the attributes that describe the
  state of a real-world object and the actions
  associated with it.
• Definition very similar to entity, however,
  object encapsulates both state and behavior
• an entity only models state.
• Attribute Contain current state of an object.
• Attributes can be classified as simple or
  complex.
• Simple attribute can be a primitive type such as
  integer, string, etc., which takes on literal
  values.
• Complex attribute can contain collections and/or
  references.
• Reference attribute represents relationship.
• complex object contains one or more complex
  attributes

8
Object Identity

• Object identifier (OID) assigned to object when
  it is created that is
• System-generated.
• Unique to that object.
• Invariant.
• Independent of the values of its attributes (that
  is, its state).
• Invisible to the user (ideally).

• - RDBMS object identity is value-based, primary
  key provides uniqueness.
• - Primary keys do not provide type of object
  identity required in OO systems
• key only unique within a relation, not across
  entire system
• key chosen from atts of relation, making it
  dependent on object state.

• Advantages
• They are efficient.
• They are fast.

• They cannot be modified by the user.
• They are independent of content.

9
Methods and messages

• Method Defines behavior of an object, as a set
  of encapsulated functions.
• Message Request from one object to another
  asking second object to execute one of its
  methods.

(b)
(a) Object showing atts and methods (b) Example
of a method
(a)
10
Classes

• Class Blueprint for defining a set of similar
  objects.
• -Objects in a class are called
• instances.
• -Class may have its own
• class attributes and class methods.

11
Subclasses, Superclasses and inheritance

• Inheritance allows one class of objects to be
  defined as a special case of a more general
  class.
• Special cases are subclasses and more general
  cases are superclasses.

Generalization process of forming a superclass
Specialization forming a subclass

• 4 Types of
• inheritance
• single
• multiple
• repeated
• selective

• Subclass inherits all properties of its
  superclass
• and can define its own unique properties.
• Subclass can redefine inherited methods.
• All instances of subclass are instances of
  superclass.
• Principle of substitutability instance of
  subclass can be used whenever method/construct
  expects instance of superclass.
• A KIND OF (AKO) Name for relationship between
  subclass and superclass

12
Types of inheritance
(a)
(b)
(b)
(a) Single (b) Multiple (c) Repeated
(c)
13
Overriding and overloading

• Overriding Process of redefining a property
  within a subclass.
• Overloading Allows name of a method to be reused
  with a class or across classes.

• Overriding Example
• Might define method in Staff class to increment
  salary based on commission
• method void giveCommission(float branchProfit)
• salary salary 0.02 branchProfit
• May wish to perform different calculation for
  commission in Manager subclass
• method void giveCommission(float branchProfit)
• salary salary 0.05 branchProfit

14
Polymorphism and dynamic binding

• Polymorphism Means many forms.
• Three types
• operation
• Inclusion
• parametric.
• Dynamic Binding Runtime process of selecting
  appropriate method based on an objects type. Pg
  803

• Example With list consisting of an arbitrary no.
  of objects from the Staff hierarchy, we can
  write listi. print
• and runtime system will determine which print()
  method to invoke depending on the objects
  (sub)type.

15
Complex Objects

• Complex Objects An object that consists of
  subobjects but is viewed as a single object.

• Objects participate in a A-PART-OF (APO)
  relationship.
• Contained object can be encapsulated within
  complex object, accessed by complex objects
  methods.
• Or have its own independent existence, and only
  an OID is stored in complex object.

16
Storing Objects in Relational Databases

• One approach to achieving persistence with an
  OOPL is to use an RDBMS as the underlying storage
  engine.
• Requires mapping class instances (i.e. objects)
  to one or more
• tuples distributed over one or more relations.
• To handle class hierarchy, have two basics tasks
  to perform
• (1) design relations to represent class
  hierarchy
• (2) design how objects will be accessed.

17
Storing Objects in Relational Databases
Sample inheritance hierachy for staff
18
Mapping classes to relations

• No. of strategies for mapping classes to
  relations, although each results in a loss of
  semantic information.
• 1. Map each class or subclass to a relation
• Staff (staffNo, fName, lName, position, sex, DOB,
  salary)
• Manager (staffNo, bonus, mgrStartDate)
• SalesPersonnel (staffNo, salesArea, carAllowance)
• 2. Map each subclass to a relation
• Manager (staffNo, fName, lName, position, sex,
  DOB, salary, bonus, mgrStartDate)
• SalesPersonnel (staffNo, fName, lName, position,
  sex, DOB, salary, salesArea, carAllowance)
• 3. Map the hierarchy to a single relation
• Staff (staffNo, fName, lName, position, sex, DOB,
  salary, bonus, mgrStartDate, salesArea,
  carAllowance, typingSpeed, typeFlag)

19
Next Generation Database Systems

• First Generation DBMS Network and Hierarchical
• Required complex programs for even simple
  queries.
• Minimal data independence.
• No widely accepted theoretical foundation.
• Second Generation DBMS Relational DBMS
• Helped overcome these problems.
• Third Generation DBMS OODBMS and ORDBMS.

20
Next Generation Database Systems
History of data models
21
III. Current Trends

• Chapter 25
• Object-Oriented DBMSs Concepts and Design

22
Introduction to OO data models and OODBMSs

• No one agreed object data model
• OODM Object-Oriented Data Model. Data model that
  captures semantics of objects supported in
  object-oriented programming.
• OODB Object-Oriented Database. Persistent and
  sharable collection of objects defined by an ODM.
• OODBMS Object-Oriented DBMS. Manager of an ODB.

Zdonik Maiers threshold model that OODBMS
must, at a min - provide database
functionality. - support object identity. -
provide encapsulation. - support objects with
complex state.
Khoshafian Abnous OODBMS definition OO ADTs
Inheritance Object identity OODBMS OO
Database capabilities.
23
Persistent Programming Languages (PPLs)

• PPL Language that provides users with ability to
  (transparently) preserve data across successive
  executions of a program, and even allows such
  data to be used by many different programs.
• In contrast Database Programming Language (e.g.
  SQL) differs by its incorporation of features
  beyond persistence, such as transaction
  management, concurrency control, and recovery.

- PPLs eliminate impedance mismatch by extending
programming language with database
capabilities

• PPL Motivations
• Improving programming productivity by using
  simpler semantics
• Removing ad hoc arrangements for data translation
  and storage
• Providing protection mechanisms over the whole
  environment

The more encompassing term Persistent App System
(PAS) is sometimes used now.
24
Alternative Strategies for Developing OODBMSs

• Extend existing object-oriented programming
  language.
• - GemStone extended Smalltalk.
• Provide extensible OODBMS library.
• - Approach taken by Ontos, Versant, and
  ObjectStore.
• Embed OODB language constructs in a conventional
  host language.
• - Approach taken by O2,which has extensions for
  C.
• Extend existing database language with
  object-oriented capabilities.
• - Approach being pursued by RDBMS and OODBMS
  vendors.
• - Ontos and Versant provide a version of OSQL.
• Develop a novel database data model/language.

25
OODBMS Perspectives

• -Traditional programming languages lack built-in
  database features support
• -Increasing no. of apps require functionality
  from both database and PLs
• -Apps need to store/retrieve large amounts of
  shared, structured data.

• Traditional DBMS difficulties programmer has to
• -Decide when to read and update objects.
• -Write code to translate between apps object and
  DBMS data model
• -Perform additional type-checking when object is
  read back from database, to guarantee object will
  conform to its original type.

Two-level storage model (Conventional DBMSs)
storage model in memory, database storage model
on disk. Single-level storage model (OODBMSs)
illusion of, with similar representation in
both memory and in database stored on disk.
Requires clever management.
26
Pointer Swizzling Techniques

• Single-Level Storage Model requires clever
  management of representation of objects in memory
  and on disk (called pointer swizzling).

Pointer Swizzling action of converting OIDs to
main memory pointers. -Aim is to optimize access
to objects. -Should be able to locate any
referenced objects on secondary storage using
OIDs.

• Techniques
• No Swizzling
• - Easiest implementation is not to do any
  swizzling.
• - Objects faulted into memory, and handle passed
  to app containing OID.
• - OID is used every time the object is accessed.
• - System maintains lookup table so objects
  virtual memory pointer can be located and then
  used to access object.
• - Inefficient if same objects are accessed
  repeatedly.

27
Pointer Swizzling Techniques

• 2. Object referencing
• - Need to distinguish between resident and
  non-resident objects.
• - Most techniques variations of edge marking or
  node marking.
• - Edge marking marks every object pointer with a
  tag bit
• - if bit set, reference is to memory pointer
• - else, still pointing to OID and needs to be
  swizzled when object it refers
• to is faulted in.
• - Node marking requires that all object
  references are immediately
• converted to virtual memory pointers when object
  is faulted into memory.
• 3. Hardware based schemes
• - Use virtual memory access protection violations
  to detect accesses of
• non-resident objects.
• - Use standard virtual memory hardware to trigger
  transfer of
• persistent data from disk to memory.
• - Avoids overhead of residency checks incurred by
  software approaches.

28
Pointer Swizzling Techniques

• Three other issues that affect swizzling
  techniques
• 1. Copy versus In-Place Swizzling When faulting
  objects in, data can either be copied into apps
  local object cache or accessed in-place within
  object managers database cache .
• - Copy swizzling may be more efficient as, in
  the worst case, only modified objects have to be
  swizzled back to their OIDs.
• - In-place have to unswizzle entire page of
  objects if one modified
• 2. Eager versus Lazy Swizzling More relaxed
  definition restricts swizzling to all persistent
  OIDs within object the app wishes to access.
• - Eager swizzling swizzling all OIDs for
  persistent objects on all data pages used by app,
  before any object can be accessed.
• - Lazy swizzling only swizzles pointers as they
  are accessed

29
Pointer Swizzling Techniques

• 3. Direct versus Indirect Swizzling Only an
  issue when swizzled pointer can refer to object
  that is no longer in virtual memory.
• Direct swizzling virtual memory pointer of
  referenced object is placed directly in swizzled
  pointer.
• Indirect swizzling virtual memory pointer is
  placed in an intermediate object, which acts as a
  placeholder for the actual object.
• - Allows objects to be uncached without requiring
  swizzled pointers to be unswizzled.

30
Persistence schemes
DBMS must provide support for persistent objects,
ones that survive after creator program has
terminated.

• 1. Checkpointing Copy all/part programs address
  space to 2ndary storage.
• Two main drawbacks
• 1. Can only be used by program that created it.
• 2. May contain large amount of data that is of no
  use in subsequent executions.
• 2. Serialization Copy closure of a data
  structure to disk.
• Two inherent problems
• 1. Does not preserve object identity.
• 2. Not incremental, saving small changes to a
  large data structure is not efficient
• 3. Explicit Paging Object only made persistent
  if explicitly declared as such within the
  application program.
• By class class statically declared to be
  persistent, all instances made persistent when
  they are created.
• By explicit call object specified as persistent
  when created or at runtime.

31
Orthogonal Persistence
Alternative mechanism for providing persistence.
3 fundamental principles

• 1. Persistence independence persistence of
  object independent of how program manipulates
  that object.
• code fragment independent of persistence of data
  it manipulates.
• Programmer does not need to control movement of
  data between long-term and short-term storage.
• 2. Data type orthogonality All data objects
  should be allowed full range of persistence
  irrespective of their type.
• No special cases where object is not allowed to
  be long-lived/transient
• 3. Transitive persistence how to
  identify/provide persistent objects at language
  level independent of choice of data types in the
  language.
• Technique that is now widely used for
  identification is reachability-based.

32
Orthogonal Persistence

• Advantages
• Improved programmer productivity from simpler
  semantics.
• Improved maintenance.
• Consistent protection mechanisms over whole
  environment.
• Support for incremental evolution.
• Automatic referential integrity.
• Disadvantages
• Some runtime expense in a system where every
  pointer reference might be addressing persistent
  object.
• System required to test if object must be loaded
  in from disk-resident database.
• Although orthogonal persistence promotes
  transparency, system with support for sharing
  among concurrent processes cannot be fully
  transparent.

33
What the _at_ was that all about?
34
Issues in OODBMSs

• Previously problem areas for relational
  databases
• - Long duration transactions
• Versions
• - Schema evolution

• How these issues are addressed in OODBMSs
• Transactions unit of concurrency control and
  recovery is an Object.
• Locking based protocols most common type of CC
  mechanism
• Multiversion CC protocols advanced T models,
  such as sagas
• Versions Allow changes to properties of objects
  to be managed so that object references always
  point to correct object version.
• Itasca identifies 3 types of versions
• Transient Versions.
• Working Versions.
• Released Versions.

35
Issues in OODBMSs
3. Schema Evolution Some apps require
considerable flexibility in dynamically defining
and modifying database schema Typical schema
changes (1) Changes to class definition (a)
Modifying Attributes. (b) Modifying Methods. (2)
Changes to inheritance hierarchy (a) Making a
class S superclass of a class C. (b) Removing S
from list of superclasses of C. (c) Modifying
order of superclasses of C. (3) Changes to set
of classes, such as creating and deleting classes
and modifying class names. Changes must not leave
schema inconsistent.
36
Architecture

• Three basic Client-server architectures
• 1. Object Server distribute processing between
  the two components.
• Typically, client is responsible for T management
  interfacing to PL
• Server responsible for other DBMS functions.
• Best for cooperative, object-to-object processing
  in an open, distributed environment.
• 2. Page Server
• Most database processing is performed by client.
• Server responsible for secondary storage and
  providing pages at clients request.
• 3. Database Server
• Most database processing performed by server.
• Client passes requests to server, receives
  results and passes to app.
• Approach taken by many RDBMSs.

37
Storing and executing methods

• Two approaches
• (a) Store methods in external files.
• (b) Store methods in database.

• Benefits of (b)
• Eliminates redundant code.
• Simplifies modifications.
• Methods are more secure.
• Methods can be shared concurrently.
• Improved integrity.

38
The OO Database system manifesto

• Complex objects must be supported.
• Object identity must be supported.
• Encapsulation must be supported.
• Types or Classes must be supported.
• Types or Classes must be able to inherit from
  their ancestors.
• Dynamic binding must be supported.
• The DML must be computationally complete.
• The set of data types must be extensible.
• Data persistence must be provided.
• The DBMS must be capable of managing very large
  databases.
• The DBMS must support concurrent users.
• DBMS must be able to recover from
  hardware/software failures.
• DBMS must provide a simple way of querying data.
• optional features including type
  checking/inferencing, versions

39
Advantages/disadvantages of OODBMSs

• Advantages
• Enriched Modeling Capabilities.
• Extensibility.
• Removal of Impedance Mismatch.
• More Expressive Query Language.
• Support for Schema Evolution.
• Support for Long Duration Ts.
• Applicability to Advanced Database Apps.
• Improved Performance.

• Disadvantages
• Lack of Universal Data Model.
• Lack of Experience.
• Lack of Standards.
• Query Optimization compromises Encapsulation.
• Object Level Locking may impact Performance.
• Complexity.
• Lack of Support for Views.
• Lack of Support for Security.

40
III. Current Trends

• Chapter 27
• Object-Relational DBMSs

41
Introduction to O-R database systems

• RDBMSs currently dominant database technology
  with estimated sales 50 billion with tools sales
  included, and growing rate possibly 25 per yr.
• OODBMS market still small, with sales of 150
  million in 1996 and a 3 market share in 1997.

• Some expect OODBMS market to grow at over 50
  per year, but unlikely to overtake RDBMS
• Vendors of RDBMSs conscious of threat and
  promise of OODBMS.
• Agree that RDBMSs not currently suited to
  advanced database apps,
• Reject claim that extended RDBMSs will not
  provide sufficient functionality/be too slow to
  cope adequately with new complexity.
• Can remedy shortcomings of relational model by
  extending with OO features.

42
Introduction to O-R database systems

• OO features being added include
• user-extensible types,
• encapsulation,
• inheritance,
• polymorphism,

• dynamic binding of methods,
• complex objects including non-1NF objects,
• object identity.

• However, no single extended relational model.
• All models
• share basic relational tables and query language,
  
• all have some concept of object,
• some can store methods (or procedures or
  triggers).
• Some analysts predict ORDBMS will have 50 larger
  share of market than RDBMS.

43
Advantages/disadvantages of O-R database systems

• Advantages
• Resolves many known weaknesses of RDBMS.
• Reuse and sharing
• Reuse from ability to extend server to perform
  standard functionality centrally.
• increased productivity for developer and
  end-user.
• Preserves significant body of knowledge and
  experience gone into developing relational
  applications.

• Disadvantages
• Complexity.
• Increased costs.
• Proponents of relational approach believe
  simplicity and purity of relational model are
  lost.
• Some believe RDBMS is being extended for what
  will be a minority of applications.
• OO purists not attracted by extensions either.
• SQL now extremely complex.

44
The Third-Generation Database System Manifesto

• Selected features proposed by CADF
• 1. A 3rd generation DBMS must have a rich type
  system.
• 2. Inheritance is a good idea.
• 3. Functions, including database procedures,
  methods are a good idea.
• 4. DBMS assigns unique identifiers for records
  only if no user-defined PK
• 5. Rules (triggers, constraints) will become a
  major feature in future. They should not be
  associated with a specific function or
  collection.
• 6. all programmatic access to a database should
  be through a non-procedural, high-level access
  language.
• 7. Should be at least two ways to specify
  collections, one using enumeration of members and
  one using query language.
• 8. Updateable views are essential.
• 9. Performance indicators should not appear in
  data models
• 10. For better or worse, SQL is intergalactic
  dataspeak.

45
The Third Manifesto

• proposed by Darwen and Date (1995,2000), it
  attempts to defend the relational data model
• Acknowledged that certain OO features desirable,
  but believe features are orthogonal to RDM.
• Thus, RDM needs no extension, no correction, no
  subsumption, and, above all, no perversion.
• However, SQL is unequivocally rejected as a
  perversion of model.
• Instead a language called D is proposed.
• Primary object is domain - a named set of
  encapsulated values, of arbitrary complexity,
  equivalent to data type or object class.
• Domain values referred to as scalars, manipulated
  only by means of operators defined for domain.
• Both single and multiple inheritance on domains
  proposed.
• Nested transactions should be supported.

46
Postgres An early ORDBMS

• Postgres (Post Ingres) is a research DBMS
  designed to be potential successor to INGRES.
• Some of the objectives of the project were to
  provide
• better support for complex objects.
• user extensibility for data types, operators
  access methods.
• active database facilities (alerters triggers)
  inferencing support.
• Make as few changes as possible to the relational
  model.
• Postgres extended RDM to include
• Abstract Data Types,
• Data of type procedure,
• Rules.
• Supported OO constructs such as aggregation,
  generalization, complex objects with shared
  subobjects, and atts that reference tuples in
  other relations.

47
SQL3

• The SQL3 standard is extremely large.
• New OO Data Management Features we will cover
• Type constructors for row types and reference
  types.
• User-defined types (distinct types and structured
  types) that can participate in supertype/subtype
  relationships.
• User-defined procedures, functions, and
  operators.
• Type constructors for collection types (arrays,
  sets, lists, and multisets).
• Release of SQL3 fell significantly behind
  schedule and was only finalized in 1999 (SQL2 in
  1992). Some features have been deferred to SQL4.
  

48
SQL3 Row types

• Row type Sequence of field name/data type pairs
  that provides data type to represent types of
  rows in tables.
• Allows complete rows to be
• stored in variables,
• passed as arguments to routines,
• returned as return values from function calls.
• Also allows column of table to contain row
  values.

• Example
• CREATE TABLE Branch (branchNo CHAR(4),
• address ROW(street VARCHAR(25), city
  VARCHAR(15),
• postcode ROW(cityIdentifier VARCHAR(4),
• subPart VARCHAR(4))))
• INSERT INTO Branch VALUES (B005, (22 Deer Rd,
  London,ROW(SW1, 4EH)))

49
SQL3 User-defined Types (UDTs)

• May be used in same way as built-in types.
• Subdivided into two categories distinct types
  (simplest) structured types.
• - Distinct type allows differentiation between
  same underlying base types
• CREATE TYPE OwnerNoType AS VARCHAR(5) FINAL
• CREATE TYPE StaffNoType AS VARCHAR(5) FINAL
• - Would get error if attempt to treat as instance
  of wrong type
• - Not same as SQL domains, which constrains set
  of storable valid values
• - Generally, UDT definition consists of one or
  more att definitions.
• - Definition also consists of routine
  declarations
• - Can define equality/ordering relationships
  using CREATE ORDERING FOR.
• - Value of an att can be accessed using common
  dot notation p.fName
• - For each att, an observer (get) and a mutator
  (set) function are automatically defined, but can
  be redefined by user in UDT definition.
• - Similarly for a (public) constructor function.

50
SQL3 User-defined Routines (UDRs)

• UDRs define methods for manipulating data.
• - UDRs may be defined as part of a
  UDT/separately as part of a schema.
• An SQL-invoked routine may be a procedure or
  function
• May be externally provided in standard
  programming language or defined completely in
  SQL.
• - An SQL-invoked procedure is invoked from SQL
  CALL statement.
• - May have 0 params, each of which may be IN,
  OUT or INOUT, and a body if defined fully within
  SQL.
• - An SQL-invoked function returns a value.
• - Specified params are input params with 1
  designated as result param.
• - External routine defined by specifying an
  external clause that identifies compiled code
  in operating systems file storage.
• - ORDBMS provides method to dynamically link
  object file into DBMS
• - Procedure for this is outside bounds of SQL
  standard and is left as implementation-defined.

51
SQL3 Polymorphism

• Routine names may be overloaded, provided
• no two functions in same schema have same
  signature
• no two procedures in same schema have same name
  and number of parameters.
• SQL3 uses generalized object model,
• so types of all arguments considered when
  deciding which routine to invoke (left to right).
• Precedence lists used to determine closest match.

52
SQL3 Reference types and object identity

• Reference types can be used to define
  relationships between row types and uniquely
  identify a row within a table.
• Reference type provides similar functionality as
  OID of OODBMSs.
• Thus references allow
• - a row to be shared among multiple tables,
• - users to replace complex join defs with much
  simpler path expressions.
• Also give optimizer alternative way to navigate
  data instead of using value-based joins.
• REF IS SYSTEM GENERATED in CREATE TYPE indicates
  that actual values of associated REF type are
  provided by the system, as in the PersonType
  created above.

53
SQL3 Subtypes and Supertypes

• UDTs can participate in subtype/supertype
  hierarchy using UNDER clause.
• Multiple inheritance is not supported.
• Subtype inherits all the attributes and behavior
  of its supertypes.
• Can define additional atts and functions and can
  override inherited functions.
• Concept of substitutability supported whenever
  instance of supertype expected instance of
  subtype can be used in its place.

Example CREATE TYPE StaffType UNDER PersonType
AS (staffNo VARCHAR(5), position
VARCHAR(10) DEFAULT Assistant, salary
DECIMAL(7, 2), branchNo CHAR(4), CREATE FUNCTION
isManager (s StaffType) RETURNS BOOLEAN BEGIN IF
s.position Manager THEN RETURN TRUE ELSE
RETURN FALSE END IF END) INSTANTIABLE N
OT FINAL
54
SQL3 Creating tables

• UDT instance can only persist if stored as the
  column value in a table

• Example Creation of a table based on a UDT
• CREATE TABLE Staff (info StaffType, PRIMARY KEY
  (staffNo))
• Or CREATE TABLE Staff OF StaffType (
• REF IS staffID SYSTEM GENERATED,
• PRIMARY KEY (staffNo))

Example Using a reference type to define a
relationship CREATE TABLE PropertyForRent (
propertyNo PropertyNumber NOT NULL, street
Street NOT NULL, . staffID REF(StaffType)
SCOPE Staff REFERENCES ARE CHECKED ON DELETE
CASCADE, PRIMARY KEY (propertyNo))
55
SQL3 Querying data

• Afew of the extensions to SQL2s syntax for
  querying and updating tables

• Example Retrieve a specific column, specific
  rows
• Find the names of all Managers. SELECT s.lName
• FROM Staff s
• WHERE s.position Manager
• Uses implicitly defined observer function
  position.

• Example Invoking a User-defined function
• Find the names and ages of all Managers. SELECT
  s.lName, s.age
• FROM Staff s
• WHERE s.isManager
• - Uses UDF isManager as a predicate of WHERE
  clause (returns TRUE if member of staff is a
  manager).
• - Also uses inherited virtual observer function
  age.

56
SQL3 Collection types

• Collections type constructors used to define
  collections of other types.
• Store multiple values in single column. Can
  result in nested tables.
• SQL3 has parameterized ARRAY collection type.
• Parameter may be predefined type, UDT, row type,
  or another collection.

• Example Use of a collection SET
• Extend Staff table to contain details of a number
  of next of kin, and then find first and last
  names of John Whites next-of-kin.
• nextOfKin SET(PersonType)
• Query becomes SELECT n.fName, n.lName
• FROM Staff s, TABLE (s.nextOfKin) n
• WHERE s.lNameWhite and s.fName John

57
SQL3 Persistent stored modules

• SQL3 has some new statement types to make it
  computationally complete.
• Behavior (methods) can be stored/executed from
  within database as SQL statements.
• Can group statements into a compound statement
  (block), with its own local variables.
• Some of the new statements are
• An assignment statement.
• An IF THEN ELSE END IF statement.
• CASE statement.
• A set of statements for iteration FOR, WHILE,
  and REPEAT.
• A CALL statement to invoke procedures and a
  RETURN statement.

58
SQL3 Triggers

• Trigger An SQL (compound) statement executed
  automatically by DBMS as side effect of a
  modification to named table.
• Use of triggers include
• Validating input data and maintaining complex
  integrity constraints that otherwise would be
  difficult/impossible.
• Supporting alerts.
• Maintaining audit information.
• Supporting replication.

• Major advantage - standard functions can be
  stored within database and enforced consistently.
  disadvantages
• Complexity.
• Hidden functionality.
• Performance overhead.

Example CREATE TRIGGER TriggerName BEFORE
AFTER lttriggerEventgt ON ltTableNamegt
REFERENCING ltoldOrNewValuesAliasListgt
FOR EACH ROW STATEMENT WHEN
(triggerCondition) lttriggerBodygt
59
Enjoy Your Easter Break