Object types were first introduced into the Oracle8 RDBMS the "objectrelational" model

1
More about ORACLE OBJECT TYPES

• Object types were first introduced into the
  Oracle8 RDBMS (the "object-relational" model)
• Oracle uses object types in many of its new
  features (e.g., Oracle AQ, the XML datatype)
• Few development shops work with object types
• The implementation is weak
• Not really object oriented
• Advantages are not persuasive to developers and
  DBAs with relational and procedural backgrounds
• Oracle9i 10g support for inheritance may well
  change this situation

2
AN OBJECT TYPE EXAMPLE
CREATE TYPE food_t AS OBJECT ( name
VARCHAR2(100), food_group VARCHAR2 (100),
grown_in VARCHAR2 (100) )
Attributes

• The food type contains three attributes and no
  methods or programs
• It is very similar to a CREATE TABLE statement,
  but it does not create a "container" for data.
  Rather it is a "template" for data or instances

3
WORKING WITH SIMPLE OBJECTS
DECLARE my_favorite_vegetable food_t
food_t ('Brussel Sprouts', 'VEGETABLE',
'Farm,Greenhouse,Backyard') BEGIN
DBMS_OUTPUT.put_line ( my_favorite_vegetable
.name) my_favorite_vegetable.food_group
'SATISFACTION' IF INSTR (
my_favorite_vegetable.grown_in, 'yard') gt
0 THEN order_seeds (my_favorite_vegetable
) END IF END
Create a new object with a constructor
Read and print an attribute value
Modify an attribute value
Pass an object as a parameter
objtype.sql
4
ANOTHER OBJECT TYPE EXAMPLE

• The timer object calculates elapsed time
• It consists of four attributes and five methods

CREATE TYPE tmr_t AS OBJECT ( startTime
INTEGER, endTime INTEGER, repetitions
INTEGER, name VARCHAR2(2000), MEMBER
PROCEDURE go , MEMBER PROCEDURE stop (
show_timing IN BOOLEAN TRUE), MEMBER
FUNCTION timing RETURN INTEGER, MEMBER
PROCEDURE reset ( name IN VARCHAR2
NULL), STATIC FUNCTION make ( name
IN VARCHAR2, repetitions IN INTEGER 1)
RETURN tmr_t )
Attributes
Methods
tmr81.ot
5
USING THE TIMER OBJECT
DECLARE v VARCHAR2(30) i integer
func_tmr tmr_t tmr_t (NULL, NULL,1000,
'Constant) const_tmr tmr_t
tmr_t.make ('Constant', 1000) BEGIN
func_tmr.go() FOR i IN 1 .. 1000 LOOP
v thisuser.name END LOOP
func_tmr.stop()
Declare multiple object types instances
Use default and pseudo-constructors functions to
initialise the instances
Invoke object type methods using dot notation
thisuser.tst
6
NEW FOR ORACLE 9i 10G SUPPORT FOR INHERITANCE

• You can now define a hierarchy of subtypes of
  object types
• A subtype contains all the attributes and methods
  of the parent type (or supertype)
• The subtypes can also contain additional
  attributes and additional methods, and can
  override methods from the supertype

7
NOW WE CAN BUILD A TYPLE HIERARCHY

• In Oracle10, an object type can be a supertype,
  from which other object types are derived
• Here is a three level hierarchy
• food is the root type
• desserts are a type of food
• cakes are a type of dessert
• We will make cake the most specialized type of
  food allowed in the hierarchy

food
"root", supertype of dessert
subtype of food, supertype of cake
dessert
cake
subtype of dessert
food.ot
8
CREATING A SIMPLE OBJECT TYPE HIERARCHY
CREATE TYPE food_t AS OBJECT ( name
VARCHAR2(100), food_group VARCHAR2 (100),
grown_in VARCHAR2 (100)) NOT FINAL CREATE
TYPE dessert_t UNDER food_t (
contains_chocolate CHAR(1), year_created
NUMBER(4)) NOT FINAL CREATE TYPE cake_t
UNDER dessert_t ( diameter NUMBER,
inscription VARCHAR2(200))

• NOT FINAL indicates that this type can be a
  supertype
• UNDER denotes that this type is a subtype

food.ot
9
POPULATE AN OBJECT TABLE

• Create a table of objects of type food (root
  type)
• Populate it with objects at different levels in
  the hierarchy

CREATE TABLE sustenance OF food_t
DECLARE my_favorite_vegetables food_t
food_t ('Brussel Sprouts', 'VEGETABLE', 'farm'
) BEGIN INSERT INTO sustenance VALUES
(my_favorite_vegetables) INSERT INTO
sustenance VALUES (dessert_t ('Jello',
'PROTEIN', 'bowl', 'N', 1887 ) ) INSERT INTO
sustenance VALUES (cake_t (
'Marzepan Delight', 'CARBOHYDRATE', 'bakery',
'N', 1634, 8, 'Happy Birthday!' ) ) END
Use of constructor to initialize a variable
Substitution of subtypes
food.ot
10
ACCESSING ATTRIBUTES IN SUBSTITUTED TYPES

• You can substitute a subtype in a supertype
  column or attribute, but subtype-specific
  attributes and methods are by default not visible

SQLgt DECLARE 4 mmm_good food_t 5
dessert_t ('Super Brownie', 'CARBOHYDRATE', 6
'my oven', 'Y', 1994) 7 BEGIN 8
DBMS_OUTPUT.PUT_LINE (mmm_good.contains_chocolat
e) 9 END 10 / DBMS_OUTPUT.PUT_LINE
(mmm_good.contains_chocolate)
ERROR at line 8 PLS-00302
component 'CONTAINS_CHOCOLATE' must be declared
11
USE TREAT in TYPES
TREAT function explicitly changes the declared
type of the source in an assignment to a more
specialised target type or subtype in the
hierarchy
/ Show all the meals in which a main course is a
dessert / SELECT FROM meal WHERE TREAT
(main_course AS dessert_t) IS NOT NULL /
Will fail, since main_course is of food_t type
/ SELECT main_course.contains_chocolate FROM
meal WHERE TREAT (main_course AS dessert_t) IS
NOT NULL / Now works, since I am treating
main_course as a dessert / SELECT TREAT
(main_course AS dessert_t).contains_chocolate
FROM meal WHERE TREAT (main_course AS dessert_t)
IS NOT NULL / Set to NULL any desserts that
are not cakes... / UPDATE meal SET dessert
TREAT (dessert AS cake_t)
treat.sql
12
CREATING AND OVERRIDING METHODS

• Most real-world object types will have both
  attributes and methods, programs that perform
  operations on attributes
• With inheritance, you can
• inherit supertype methods
• override or replace supertype methods with
  subtype implementations
• add completely new methods

13
OVERRIDING TO PROVIDE SPECIFICITY FOR SUBTYPES
CREATE OR REPLACE TYPE BODY dessert_t IS
OVERRIDING MEMBER FUNCTION price RETURN
NUMBER IS mult NUMBER 1 BEGIN
IF SELF.contains_chocolate 'Y' THEN
mult 2 END IF IF SELF.year_created lt
1900 THEN mult mult 0.5 END IF
RETURN (10.00 mult ) END END

• Two different calculations for desserts and cakes

food2.ot
CREATE OR REPLACE TYPE BODY cake_t IS
OVERRIDING MEMBER FUNCTION price RETURN NUMBER
IS BEGIN RETURN ( 5.00
0.25 (LENGTH (SELF.inscription))
0.50 diameter) END END
Generic dessert prices are determined by
chocolate content and age. Cake prices are driven
by inscription length and size..
14
ABOUT POLYMORPHISM

• The ability to choose from multiple methods of
  the same name and execute the appropriate method
• Static polymorphism the decision about which
  method to execute is made at the time the code is
  compiled. Static polymorphism is also known as
  overloading, and is supported in declaration
  sections of PL/SQL blocks
• Dynamic polymorphism the decision about which
  method to execute is made at the time the code is
  executed, at run-time. This is also known as
  "dynamic method dispatch", and is available for
  the first time in PL/SQL with support for object
  type inheritance

15
EXPLORING DYNAMIC POLYMORPHISM
CREATE TYPE food_t AS OBJECT (
...attributes... MEMBER FUNCTION price RETURN
NUMBER ) NOT FINAL CREATE TYPE dessert_t
UNDER food_t ( ...attributes... OVERRIDING
MEMBER FUNCTION price RETURN NUMBER )
NOT FINAL) CREATE TYPE cake_t UNDER
dessert_t ( ...attributes... -- No price
method of its own. )

• The food and dessert types each have a price
  method, but cake does not. It simply inherits the
  dessert method

16
A VISUAL REPRESENTATION
Food

• The root price function is over-ridden in the
  dessert subtype
• The cake subtype now simply inherits its price
  calculation from its dessert supertype

Price
the "original"
Dessert
Price
An override
Cake
Inheritedcalculation
17
DYNAMICALLY CHOOSING THE RIGHT METHOD
DECLARE TYPE foodstuffs_nt IS TABLE OF
food_t fridge foodstuffs_nt
foodstuffs_nt ( food_t ('Eggs benedict',
...), dessert_t ('Strawberries and cream',
...), cake_t ('Chocolate Supreme',
...)) BEGIN FOR indx IN fridge.FIRST
.. fridge.LAST LOOP
DBMS_OUTPUT.put_line ( 'Price of '
fridge (indx).NAME ' '
fridge (indx).price) END LOOP END
A collection of foods is populated with three
different object types.
food3.ot
The price invocation is resolved at run-time, and
not necessarily as the food_t.price method.
18
Object Database Standards, Languages, and
DesignODMG
19
Introduction

• Why a standard is needed?
• Portability execute an application program on
  different systems with minimal modifications to
  the program.
• Interoperability The ability of application to
  access multiple distinct systems
• Object Data Modeling Group (ODMG 2.0) standard
  object model, object definition language (ODL),
  object query language (OQL), and bindings to
  object-oriented programming languages.

20
Object Model of ODMG

• The data model upon which ODL and OQL are based.
• Provides data type, type constructors
• SQL report describes a standard data model for
  relational database.

21
Object Model of ODMG

• Object and literal
• Literal has only a value but no object identifier
• Object has four characteristics
• identifier
• Name
• life time (persistent or not)
• Structure (how to construct)

22
Object Model of ODMG

• Atomic object not a collection, includes
  structured objects created using the struct
  constructor
• Three types of literals
• Atomic
• Collection
• Structured

23
Object Model of ODMG

• Atomic literals correspond to the values of
  basic data types, predefined
• Collection literals specify a value that is a
  collection of objects or values but the
  collection itself does not have an OID.
• Structured literals values that are constructed
  using the tuple constructor (Date, Interval,
  Time, Timestamp), user defined are created using
  the struct keyword in ODL.

24
Basic Component of ODMG Model

• Interface Describes objects visible attributes,
  relationships and operation (non instantiable).
• Interfaces are mainly used to define operations
  that can be inherited by user-defined objects.
• There are build-in interfaces such as Object,
  Set, List, etc
• //Use Final for Interface in the CREATE TYPE
  statement
• Class User specified class declaration that form
  a database schema and is used to create
  application objects (instantiable).
• //Use NOT Final for Interface in the CREATE TYPE
  statement

25
Example on a class and interface
Class
Interface

• Interface Object
• ..
• Object copy()
• Void delete()

Class Person ( extent all_persons Key p_id) attr
ibute string name Attribute
string address Attribute date DOB Attribute
sex character short age(in date v_dob)
26
Extents, Keys and Factory Objects

• Dictionaryltk, vgt a collection of associations
  ltk, vgt, k is a key (a unique search value)
  associated with a value v.
• Extent Extent of a class can serve as a set of
  persistent objects of that class.
• Key a class with extent can have more than one
  keys. For example, the Person class has a p_id as
  a primary key.
• Relationship is property that specifies that two
  objects in the database are related together.
  ODMG uses the terms relationships and inverse to
  define relationships between objects.
• Factory object an object that can be used to
  generate or create individual objects via its
  operations.

27
Example
Class Employee
Class Dept

• Class Employee
• (extent all_emps
• Key nin)
• Attribute string name
• Attribute string nin
• Attribute date dob
• Attribute date hire_date
• Relationship dept works_for
• Inverse depthas_emps
• Void reassign_emp (in string new_dname)
• raise (dname_not_valid)

Class Dept (extent all_depts Key (dnum,
dname) Attribute string dname Attribute
short dnum Attribute struct d_mgr Employee
mgr, date start_date mgr Relatiohsip
setltEmployeegt has_emps Inverse Employeeworks_for
Attribute setltstringgt locations Void add_emp
(in string new_ename) raises
(ename_not_valid)
28
Object Definition Language (ODL)

• Designed to support the semantic constructs of
  the ODMG data model.
• Independent of any programming language.
• Create object specifications classes and
  interfaces
• Specify a database schema
• In ODL
• Use the specific language bindings to specify how
  ODL constructs can be mapped to constructs in
  specific programming language.
• See previous slide for a ODL example

29
Object Query Language (OQL)

• Embedded into one of these programming languages
• Return objects that match the type system of that
  language
• Similar to SQL with additional features (object
  identity, complex objects, operations,
  inheritance, polymorphism, relationships)

30
Object Query Language (OQL)-Entry Points and
Iterator Variables

• Entry point named persistent object (for many
  queries, it is the name of the extent of a
  class).
• Iterator variable when a collection is
  referenced in OQL query.
• d in departments
• departments d
• departments as d

31
Examples using (OQL)
Employee NIN varchar2 Lname varchar2 Fname varcha
r2 DOB Date Salary float HireDate Date Address var
char2 Tel varchar2 Deptno Number
Department Deptno number Dname varchar2 Dphone va
rchar2 Office varchar2 MGR varchar2 chair Lecturer
Relationship has_faculty inverse Lecturer
works_in Relationship setltGradStudgt advises
inverse GradStdcommittee
Lecturer OfficeNum char(5) Rank
varchar2 Salary float Phone
integer HireDate date Relationship
setltLecturergt works_in inverse Department
has_faculty
See page 681 of (Elmasri Navathe book) for
complete list of classes
32
Object Query Language (OQL)-Entry Points and
Iterator Variables

• List all departments in Engineering college?
• SELECT d.dname
• FROM d in departments
• WHERE d.college Engineering
• Three ways of using the iterator variable in OQL
• d in department
• departments d
• departments as d

33
Object Query Language (OQL)-Query Results and
Path Expressions

• Any persistent object is a query, result is a
  reference to that persistent object.
• Path expression once an entry point is
  specified, it can be used to specify a path to
  related attributes and objects.
• E.g.
• Q1 departments
• Q2 csdepartment.HireDate
• Q2a csdepartment.rank

Persistent object for the computer department
Path expression
34
Object Query Language (OQL)-Query Results and
Path Expressions

• select f.rank
• from f in csdepartments.has_faculty
• select distinct f.rank
• from f in csdepartment.has_faculty

35
Object Query Language (OQL)

• OQL query can return a result with a complex
  structure specified in the query itself by
  utilising the struct keyword. E.g.
• select struct
• (namestruct(last_name s.name.lname,
• first_name s.name.fname),
• degree(select struct (degd.degree,
• yr d.year,
• colleged.college)
• from d in s.degrees)
• from s in csdepartment.chair.advises

36
Object Query Language (OQL)-Specifying Views as
Named Queries

• define keyword specify an identifier of the
  named query, unique among all named objects,
  class names, method names, or function names in
  the scheme.
• A query definition is persistent until it is
  redefined or deleted.

37
Object Query Language (OQL)-Specifying Views as
Named Queries

• A view can have parameters.
• Example
• define has_minors(deptname) as
• select s
• from s in students
• where s.minors_in.dname deptname
• The user can utilise the above query as
• Has_inors(computer science)

38
Object Query Language (OQL)-Extracting Single
Elements from Singleton Collections

• element operator guaranteed to return a single
  element from a singleton collection that contains
  only one element.
• Example
• element (select d
• from d in departments
• where d.dname Computer Science)

39
Object Query Language (OQL)-Collection Operators

• Aggregate operators min, max, count, sum, and
  avg.
• Membership and qualification expressions returns
  a Boolean type.
• Example
• count (s in has_minors(Computer Science))

40
Object Query Language (OQL)-Ordered (Indexed)
Collection Expressions

• first (select struct(facultyf.name.lname,
  salaryf.salary)
• from f in faculty
• order by f.salary desc)

41
Object Query Language (OQL)-Sub Queries

• The In operator
• Select s.name.lname, s.name.fname
• From s in students
• Where Advanced DB in
• (select c.name
• From c in s.completed_sections.section.of_cours
  e)

42
Object Query Language (OQL)-Grouping Operator

• group by clause provides explicit reference to
  the collection of objects within each group or
  partition.
• Suppose we want to retrieve number of majors per
  department
• select struct (deptname, number_of_majors
  count (partition))
• from s in students
• group by deptname s.majors_in.dname
• A having can be used to filter the partitioned
  sets (that is select only some of the groups
  based on group conditions).

43
Object Database Conceptual Design

• ODB relationships are handled by OID references
  to the related objects.
• RDB relationships among tuples are specified by
  attributes with matching values (value
  references).

44
Object-Relational and Extended-Relational Systems

• ORDBMS enhancing the capabilities of RDBMS with
  some of the features in ODBMS.
• Object-oriented features in SQL-99

45

• More OQL examples can be found in
• Object Data Standard Book by Cattell (Chapter 4)
• Fundamentals of database systems by Elmasri and
  Navathe (Chapter 21)