Object Oriented Database Management

1
Object Oriented Database Management
2
Outline

• Motivation
• Embedding SQL in host language
• Object Data Model
• Persistent Programming Languages
• Object Query Language
• Object-orientation in SQL

3
Motivation of ODBMSs
Application data structures
Copy and translation
Transparent ODBMS data transfer
Relational representation
RDBMS

• Complex objects in emerging DBMS applications
  cannot be effectively represented as records in
  relational model.
• Representing information in RDBMSs requires
  complex and inefficient conversion into and from
  the relational model to the application
  programming language
• ODBMSs provide a direct representation of objects
  to DBMSs overcoming the impedance mismatch
  problem

4
Embedded SQL

• Access to database from a general purpose
  programming language required since
• Not all queries can be expressed in SQL --e.g.,
  recursive queries cannot be written in SQL.
• Non declarative actions -- e.g., printing reports
  cannot be done from SQL.
• General purpose language in which SQL is embedded
  called host language.
• SQL structures permitted in host language called
  embedded SQL.

C compiler
SQL library calls C
SQL C
pre- compiler
.o file
loader
SQL library
object code
Embedded SQL Compilation
5
Embedded SQL

• SQL commands embedded in the host programming
  language
• Data exchanged between host language and DBMS
  using cursors
• SQL query passed from host language to DBMS which
  computes the answer set
• A cursor can be viewed as a pointer into the
  answer set
• DBMS returns the cursor to the programming
  language
• Programming language can use the cursor to get a
  record at a time access to materialized answer.

6
Example of Embedded SQL

• dname toy
• raise 0.1
• EXEC SQL SELECT dnum into dnum
• FROM Department
• WHERE dname dname
• EXEC SQL DECLARE Emp CURSOR FOR
• SELECT FROM Employee
• WHERE dno dnum
• FOR UPDATE
• EXEC SQL OPEN Emp
• EXEC SQL FETCH Emp INTO E.ssn, E.dno, E.name,
  E.sal
• while (SQLCODE 0)
• EXEC SQL UPDATE WHERE CURRENT OF CURSOR
• SET sal sal (1 raise)
• EXEC SQL FETCH Emp INTO E.ssn, E.dno,
  E.name, E.sal
• EXEC SQL CLOSE CURSOR Emp
• / SQL embedded in C to read the list of
  employees who work for

7
Object Oriented Database Management

• Object Oriented databases have evolved along two
  different paths
• Persistent Object Oriented Programming Languages
  (pure ODBMSs)
• Start with an OO language (e.g., C, Java,
  SMALLTALK) which has a rich type system
• Add persistence to the objects in programming
  language where persistent objects stored in
  databases
• Object Relational Database Management Systems
  (SQL3 Systems)
• Extend relational DBMSs with the rich type system
  and user-defined functions.
• Provide a convenient path for users of relational
  DBMSs to migrate to OO technology
• All major vendors (e.g., Informix, Oracle)
  will/are supporting features of SQL3.

8
Object Database Management Group (ODMG)

• Special interest group to develop standards that
  allow ODBMS customers to write portable
  applications
• Standards include
• Object Model
• Object Specification Languages
• Object Definition Language (ODL) for schema
  definition
• Object Interchange Format (OIF) to exchange
  objects between databases
• Object Query Language
• declarative language to query and update database
  objects
• Language Bindings (C, Java, Smalltalk)
• Object manipulation language
• Mechanisms to invoke OQL from language
• Procedures for operation on databases and
  transactions

9
Object Model

• Object
• observable entity in the world being modeled
• similar to concept to entity in the E/R model
• An object consists of
• attributes properties built in from primitive
  types
• relationships properties whose type is a
  reference to some other object or a collection of
  references
• methods functions that may be applied to the
  object.

10
Class

• Similar objects with the same set of properties
  and describing similar real-world concepts are
  collected into a class.
• Class definition
• interface Employee
• attribute string name
• attribute integer salary
• attribute date date-of-birth
• attribute integer empid
• relationship Projects works-for
• inverse Projectsteam
• age-type age()

Interface Projects attribute string
name attribute integer projid relationship
Employee team inverse Emplolyee works-for int
number-of-employees()
11
Class Extents

• For each ODL class, an extent may be declared.
• Extent is the current set of objects belonging to
  the class.
• Similar notion to the relation in the relational
  model.
• Queries in OQL refer to the extent of a class and
  not the class directly.
• interface Employee (extent Emp-set)
• attribute string name
• attribute integer salary
• attribute date date-of-birth
• attribute integer empid
• relationship Projects works-for
• inverse Projectsteam
• age-type age()

12
Subclasses and Inheritance

• A class can be declared to be a subclass of
  another class.
• Subclasses inherit all the properties
• attributes
• relationships
• methods
• from the superclass.
• Interface Married-Employee Employees
• string spouse-name
• Substitutability any method of superclass can be
  invoked over objects of any subclass (code reuse)

13
Class Hierarchy
person
student
employee
undergrad
student assistant
grad
staff
faculty
RA
TA
14
Multiple Inheritance

• A class may have more than one superclass.
• A class inherits properties fromeach of its
  superclasses.
• There is a potential of ambiguity -- variable
  with same name inherited from two superclasses
• flag and error
• rename variable
• choose one

15
Object Identity

• Each object has an identity which it maintains
  even if some or all of its attributes change.
• Object identity is a stronger notion of identity
  than in relational DBMSs.
• Identity in relational DBMSs is value based
  (primary key).
• Identity in ODBMSs built into data model
• no user specified identifier is required
• OID is a similar notion as pointer in programming
  language
• Object identifier (OID) can be stored as
  attribute in object to refer to another object.
• References to other objects via their OIDs can
  result in a containment hierarchy
• Note containment hierarchy different from class
  hierarchy

16
Containment Hierarchy
bicycle
wheel
brake
gear
frame
rim
spoke
tire
lever
pad
Links in containment hierarchy should be read as
is-part-of instead of is-a
17
Persistence

• Objects created may have different lifetimes
• transient allocated memory managed by the
  programming language run-time system.
• E.g., local variables in procedures have a
  lifetime of a procedure execution
• global variables have a lifetime of a program
  execution
• persistent allocated memory and stored managed
  by ODBMS runtime system.
• Classes are declared to be persistence-capable or
  transient.
• Different languages have different mechanisms to
  make objects persistent
• creation time Object declared persistent at
  creation time (e.g., in C binding) (class must
  be persistent-capable)
• persistence by reachability object is persistent
  if it can be reached from a persistent object
  (e.g., in Java binding) (class must be
  persistent-capable).

18
Persistent Object-Oriented Programming Languages

• Persistent objects are stored in the database and
  accessed from the programming language.
• Classes declared in ODL mapped to the programming
  language type system (ODL binding).
• Single programming language for applications as
  well as data management.
• Avoid having to translate data to and from
  application programming language and DBMS
• efficient implementation
• less code
• Programmer does not need to write explicit code
  to fetch data to and from database
• persistent objects to programmer looks exactly
  the same as transient objects.
• System automatically brings the objects to and
  from memory to storage device. (pointer
  swizzling).

19
Disadvantages of ODBMS Approach

• Low protection
• since persistent objects manipulated from
  applications directly, more changes that errors
  in applications can violate data integrity.
• Non-declarative interface
• difficult to optimize queries
• difficult to express queries
• But ..
• Most ODBMSs offer a declarative query language
  OQL to overcome the problem.
• OQL is very similar to SQL and can be optimized
  effectively.
• OQL can be invoked from inside ODBMS programming
  language.
• Objects can be manipulated both within OQL and
  programming language without explicitly
  transferring values between the two languages.
• OQL embedding maintains simplicity of ODBMS
  programming language interface and yet provides
  declarative access.

20
OQL Example

• interface Employee
• attribute string name
• relationship
• setof(Projects) works-for
• inverse Projectsteam

Interface Projects attribute string
name relationship setof(Employee) team inverse
Emplolyee works-for int number-of-employees()
Select number-of-employees() From Employee e,
e.works-for where name sharad
Find number of employees working on each project
sharad works on
21
Migration of RDBMSs towards OO Technologies

• SQL3 standard incorporates OO concepts in the
  relational model.
• A row in a table considered as an object
• SQL3 allows a type to be declared for tuples
  (similar to class in ODBMSs)
• Relations are collection of tuples of a row type
  (similar to extent in ODBMSs)
• Rows in a relation can refer to each other using
  a reference type (similar to object identity in
  ODBMSs)
• A reference can be dereferenced to navigate among
  tables
• Attributes in a relation can belong to abstract
  data types
• Methods and functions (expressed in SQL as well
  as host programming language) can be associated
  with abstract data types

22
SQL-3 Example

• CREATE ROW TYPE Employee-type
• name CHAR(30)
• works-for REF(Projects-type)
• CREATE ROW TYPE Projects-type
• name CHAR(30)
• team setof(REF(Employee-type))
• CREATE TABLE Emp OF TYPE Employee-type
• CREATE TABLE Project of TYPE Project-type
• Select works-for --gt name
• From Emp
• Where name sharad

Return name of the project sharad works for
23
OQL

• CMSC-461
• Database Management Systems

24
OQL -- Motivation

• Relational languages suffer from impedance
  mismatch when we try to connect them to
  conventional languages like C or C.
• The data models of C and SQL are radically
  different, e.g. C does not have relations, sets,
  or bags as primitive types C is tuple-at-a-time,
  SQL is relation-at-a-time.

25
OQL -- Motivation (II)

• OQL is an attempt by the OO community to extend
  languages like C with SQL-like,
  relation-at-a-time dictions.
• OQL is query language paired with
  schema-definition language ODL.

26
OQL Types

• Basic types strings, ints, reals, etc., plus
  class names.
• Type constructors
• Struct for structures.
• Collection types set, bag, list, array.
• Like ODL, but no limit on the number of times we
  can apply a type constructor.
• Set(Struct()) and Bag(Struct()) play special
  roles akin to relations.

27
OQL Uses ODL as its Schema-Definition Portion

• For every class we can declare an extent name
  for the current set of objects of the class.
• Remember to refer to the extent, not the class
  name, in queries.

28
Example

• interface Bar (extent Bars) attribute
  string name attribute string addr
  relationship SetltSellgt beersSold inverse
  Sellbar

29
Example (II)

• interface Beer (extent Beers) attribute
  string name attribute string manf
  relationship SetltSellgt soldBy inverse
  Sellbeer

30
Example (III)

• interface Sell (extent Sells) attribute
  float price relationship Bar bar
  inverse BarbeersSold relationship Beer
  beer inverse BeersoldBy

31
Path Expressions

• Let x be an object of class C.
• If a is an attribute of C, then x.a the value
  of a in the x object.
• If r is a relationship of C, then x.r the value
  to which x is connected by r.
• Could be an object or a collection of objects,
  depending on the type of r.
• If m is a method of C , then x.m (...) is the
  result of applying m to x.

32
Examples

• Let s be a variable whose type is Sell.
• s.price the price in the object s.
• s.bar.addr the address of the bar mentioned in
  s .
• Note cascade of dots OK because s.bar is an
  object, not a collection.

33
Example of Illegal Use of Dot

• b.beersSold.price, where b is a Bar object.
• Why illegal? Because b.beersSold is a set of
  objects, not a single object.

34
OQL Select-From-Where

• SELECT lt list of values gtFROM lt list of
  collections and typical members gtWHERE lt
  condition gt

35
OQL Select-From-Where (II)

• Collections in FROM can be1. Extents.2.
  Expressions that evaluate to a collection.
• Following a collection is a name for a typical
  member, optionally preceded by AS.

36
Example

• Get the menu at Joe's. SELECT s.beer.name,
  s.price FROM Sells s WHERE s.bar.name
  "Joe's Bar"
• Notice double-quoted strings in OQL.
• Result is of type Bag(Struct(name string,
  price float))

37
Example

• Another way to get Joe's menu, this time focusing
  on the Bar objects. SELECT s.beer.name,
  s.price FROM Bars b, b.beersSold s WHERE
  b.name "Joe's Bar"
• Notice that the typical object b in the first
  collection of FROM is used to help define the
  second collection.
• Typical usage if x.a is an object, you can
  extend the path expression if x.a is a
  collection, you use it in the FROM list.

38
Tailoring the Type of the Result

• Default bag of structs, field names taken from
  the ends of path names in SELECT clause.
• Example SELECT s.beer.name, s.price FROM
  Bars b, b.beersSold s WHERE b.name "Joe's
  Bar"has result type Bag(Struct( name
  string, price real))

39
Rename Fields

• Prefix the path with the desired name and a
  colon.
• Example SELECT beer s.beer.name, s.price
  FROM Bars b, b.beersSold s WHERE b.name
  "Joe's Bar"

40
Change the Collection Type

• Use SELECT DISTINCT to get a set of structs.

41
Example

• SELECT DISTINCT s.beer.name, s.priceFROM Bars b,
  b.beersSold sWHERE b.name "Joe's Bar"
• Use ORDER BY clause to get a list of structs.

42
Example

• joeMenu SELECT s.beer.name, s.priceFROM Bars
  b, b.beersSold sWHERE b.name "Joe's Bar"ORDER
  BY s.price ASC
• ASC ascending (default) DESC descending.
• We can extract from a list as if it were an
  array, e.g. cheapest joeMenu1.name

43
Subqueries

• Used mainly in FROM clauses and with quantifiers
  EXISTS and FORALL.

44
Example Subquery in FROM

• Find the manufacturers of the beers served at
  Joe's.SELECT b.manfFROM (SELECT s.beerFROM
  Sells sWHERE s.bar.name "Joe's Bar") b

45
Quantifiers

• Boolean-valued expressions for use in
  WHERE-clauses.FOR ALL x IN lt collection gt
  lt condition gtEXISTS x IN lt collection gt lt
  condition gt
• The expression has value TRUE if the condition is
  true for all (resp. at least one) elements of the
  collection.

46
Example

• Find all bars that sell some beer for more than
  5. SELECT b.name FROM Bars b WHERE
  EXISTS s IN b.beersSold s.price gt 5.00
• ProblemHow would you find the bars that only
  sold beers for more than 5?

47
Example

• Find the bars such that the only beers they sell
  for more than 5 are manufactured by Pete's.
  SELECT b.name FROM Bars b WHERE FOR ALL
  be IN ( SELECT s.beer
  FROM b.beersSold s WHERE s.price gt
  5.00 ) be.manf "Pete's"

48
Extraction of Collection Elements

• a) A collection with a single member Extractthe
  member with ELEMENT.

49
Example

• Find the price Joe charges for Bud and put the
  result in a variable p.
• p ELEMENT( SELECT s.price
  FROM Sells s WHERE s.bar.name "Joe's
  Bar" AND s.beer.name "Bud" )

50
Extraction of Collection Elements (II)

• b) Extracting all elements of a collection, one
  at a time
• 1. Turn the collection into a list.
• 2. Extract elements of a list with ltlist namegti.

51
Example

• Print Joe's menu, in order of price, with beers
  of the same price listed alphabetically.

52
Example (II)

• L SELECT s.beer.name, s.price FROM
  Sells s WHERE s.bar.name "Joe's Bar"
  ORDER BY s.price, s.beer.nameprintf("Beer\tPrice
  \n\n")for(I 1 I lt COUNT(L) i)
  printf("s\tf\n", Li.name, Li.price )

53
Aggregation

• The five operators avg, min, max, sum, count
  apply to any collection, as long as the operators
  make sense for the element type.

54
Example

• Find the average price of beer at Joe's.
• x AVG( SELECT s.price FROM
  Sells s WHERE s.bar.name "Joe's Bar"
  )
• Note coercion result of SELECT is technically a
  bag of 1-field structs, which is identified with
  the bag of the values of that field.

55
Grouping

• Recall SQL grouping, for exampleSELECT bar,
  AVG(price)FROM SellsGROUP BY bar
• Is the bar value the "name" of the group, or the
  common value for the bar component of all tuples
  in the group?

56
Grouping (II)

• In SQL it doesn't matter, but in OQL, you can
  create groups from the values of any function(s),
  not just attributes.
• Thus, groups are identified by common values, not
  \name."
• Example group by first letter of bar names
  (method needed).

57
Outline of OQL Group-By
Collection Defined by FROM, WHERE
Group by values of function(s)
Collection with function values and partition
Terms from SELECT clause
Output collection
58
Example

• Find the average price of beer at each
  bar.SELECT barName, avgPrice AVG( SELECT
  p.s.price FROM partition p)FROM Sells
  sGROUP BY barName s.bar.name

59
Example (II)

• 1. Initial collection Sells.
• But technically, it is a bag of structs of the
  form Struct(s s1)Where s1 is a Sells
  object. Note, the lone field is named s in
  general, there are fields for all of the tuple
  variables in the FROM clause.

60
Example (II)

• 2. Intermediate collection
• One function s.bar.name maps Sells objects s to
  the value of the name of the bar referred to by
  s.
• Collection is a set of structs of
  typeStructbarName string, partition Setlt
  Structs Sell gt

61
Example (III)

• For exampleStruct(barName "Joe's Bar",
  partition s1,, sn)where s1,, sn are all
  the structs with one field, named s, whose value
  is one of the Sells objects that represent Joe's
  Bar selling some beer.

62
Example (IV)

• 3. Output collection consists of beer-average
  price pairs, one for each struct in the
  intermediate collection.
• Type of structures in the outputStructbarName
  string, avgPrice real

63
Example (V)

• Note that in the subquery of the SELECT
  clauseSELECT barName, avgPrice AVG( SELECT
  p.s.price FROM partition p)We let p range
  over all structs in partition. Each of these
  structs contains a single field named s and has a
  Sells object as its value. Thus, p.s.price
  extracts the price from one of the Sells tuples.
• Typical output structStruct(barName "Joe's
  Bar", avgPrice 2.83)

64
Another, Less Typical Example

• Find, for each beer, the number of bars that
  charge a "low" price ( 2.00) and a "high" price (
  4.00) for that beer.
• Strategy group by three things
• 1. The beer name,
• 2. A boolean function that is true iff the price
  is low.
• 3. A boolean function that is true iff the price
  is high.

65
The Query

• SELECT beerName, low, high, count
  COUNT(partition)FROM Beers b, b.soldBy sGROUP
  BY beerName b.name, low s.price lt 2.00, high
  s.price gt 4.00

66
The Query (II)

• 1. Initial collection Pairs (b s), where b is a
  beer, and s is a Sells object representing the
  sale of that beer at some bar.
• Type of collection members Structb Beer, s
  Sell

67
2. Intermediate collection

• Quadruples consisting of a beer name, booleans
  telling whether this group is for high, low, or
  neither prices for that beer, and the partition
  for that group.
• The partition is a set of structs of the
  typeStructb Beer, s SellA typical
  valueStruct(b "Bud" object, s a Sells object
  involving Bud)

68
2. Intermediate collection (II)

• Type of quadruples in the intermediate
  collectionStructbeerName string,low
  boolean,high boolean,partition SetltStructb
  Beer,s Sellgt

69
2. Intermediate collection (III)

• BeerName low high partition
• Bud TRUE FALSE SlowBud
  FALSE TRUE ShighBud
  FALSE FALSE Smid
• where Slow Shigh, and Smid are the sets of
  beer-sells pairs (b s) where the beer is Bud and
  s has, respectively, a low ( 200), high ( 400)
  and medium (between 2.00 and 4.00) price.
• Note the partition with low high TRUE must
  be empty and will not appear.

70
3. Output collection

• The first three components of each group's struct
  are copied to the output, and the last
  (partition) is counted.The resultbeerName low
  high countBud TRUE
  FALSE 27Bud FALSE TRUE
  14Bud FALSE FALSE 36

71
SQL3 Objects
72
Objects in SQL3

• OQL extends C with database concepts, while
  SQL3 extends SQL with OO concepts.

73
Objects in SQL3 (II)

• Ullman's personal opinion the relation is so
  fundamental to data manipulation that retaining
  it as the core, as SQL3 does, is "right."
• Systems using the SQL3 philosophy are called
  object-relational.

74
Objects in SQL3 (III)

• All the major relational vendors have something
  of this kind, allowing any class to become the
  type of a column.
• Informix Data Blades
• Oracle Cartridges
• Sybase Plug-Ins
• IBM/DB2 Extenders

75
Two Levels of SQL3 Objects

• 1. For tuples of relations "row types."
• 2. For columns of relations "types."
• But row types can also be used as column types.

76
References

• Row types can have references.
• If T is a row type, then REF(T) is the type of a
  reference to a T object.
• Unlike OO systems, refs are values that can be
  seen by queries.

77
Example of Row Types

• CREATE ROW TYPE BarType ( name CHAR(20)
  UNIQUE, addr CHAR(20))
• CREATE ROW TYPE BeerType ( name CHAR(20)
  UNIQUE, manf CHAR(20))

78
Example of Row Types (II)

• CREATE ROW TYPE MenuType ( bar REF(BarType),
  beer REF(BeerType), price FLOAT)

79
Creating Tables

• Row-type declarations do not create tables.
• They are used in place of element lists in CREATE
  TABLE statements.
• Example
• CREATE TABLE Bars OF TYPE BarType
• CREATE TABLE Beers OF TYPE BeerType
• CREATE TABLE Sells OF TYPE MenuType

80
Dereferencing

• A ? B the B attribute of the object referred to
  by reference A.
• Example
• Find the beers served by Joe.SELECT beer -gt
  nameFROM SellsWHERE bar -gt name 'Joe''s Bar'

81
OID's as Values

• A row type can have a reference to itself.
• Serves as the OID for tuples of that type.
• ExampleCREATE ROW TYPE BarType ( name
  CHAR(20), addr CHAR(20), barID
  REF(BarType))CREATE TABLE Bars OF TYPE
  BarTypeVALUES FOR barID ARE SYSTEM GENERATED

82
OID's as Values (II)

• VALUES... clause forces the barID of each tuple
  to refer to the tuple itself.Name addr barID
  Joe's Maple St.

83
Example Using References as Values

• Find the menu at Joe's.SELECT Sells.beer-gtname,
  Sells.priceFROM Bars, SellsWHERE Bars.name
  'Joe''s Bar' AND Bars.barID Sells.bar

84
ADT's in SQL3

• Allows a column of a relation to have a type that
  is a "class," including methods.
• Intended application data that doesn't fit
  relational model well, e.g., locations, signals,
  images, etc.
• The type itself is usually a multi-attribute
  tuple.

85
ADT's in SQL3 (II)

• Type declarationCREATE TYPE ltnamegt (
  attributes method declarations or
  definitions)
• Methods defined in a PL/SQL-like language.

86
Example
CREATE TYPE BeerADT ( name CHAR(20), manf
CHAR(20),FUNCTION newBeer( n CHAR(20), m
CHAR(20))RETURNS BeerADT b BeerADT /
local decl. /BEGIN b BeerADT() /
built-in constructor / b.name n
b.manf m RETURN bENDFUNCTION
getMinPrice(b BeerADT) RETURNS FLOAT )
87
Example (II)

• getMinPrice is declaration only newBeer is
  definition.
• getMinPrice must be defined somewhere where
  relation Sells is available.

88
Example (III)

• FUNCTION getMinPrice(b BeerADT) RETURNS
  FLOAT p FLOATBEGIN SELECT MIN(price)
  INTO p FROM Sells WHERE beer-gtname
  b.name RETURN pEND

89
Built-In Comparison Functions

• We can define for each ADT two functions EQUAL
  and LESSTHAN that allow values of this ADT to
  participate in WHERE clauses involving , lt, etc.

90
Example A "Point" ADT

• CREATE TYPE Point ( x FLOAT, y FLOAT,FUNCTION
  EQUALS( p Point, q Point )RETURNS
  BOOLEANBEGIN IF p.x q.x AND p.y q.y
  THEN RETURN TRUE ELSE RETURN
  FALSEEND

91
Example A "Point" ADT (II)

• FUNCTION LESSTHAN( p Point, q Point )
  RETURNS BOOLEANBEGIN IF p.x gt q.x THEN
  RETURN FALSE ELSIF p.x lt q.x THEN
  IF p.y lt q.y THEN RETURN TRUE
  ELSE RETURN FALSE ELSE / p.x q.x
  IF p.y lt q.y THEN RETURN
  TRUE ELSE RETURN FALSEEND)

92
Using the Comparison Functions

• Here is a query that computes the lower convex
  hull of a set of points.
• Assumes MyPoints(p) is a relation with a single
  column p of type Point.
• SELECT pFROM MyPointsWHERE NOT p gt ANY MyPoints

93
Using the Comparison Functions (II)