Relational algebra

1
Object-Oriented Databases
2

• Relational databases
• single standard (most recently SQL1999)
• Object databases
• multiple standards
• Object Data Management Group (ODMG)
• SQL1999
• CORBA

3

• Historical sketch
• Early 1980s nested relations
• also called unnormalized databases or non-1NF
  databases
• tuple component can be another table
• Mid 1980s persistent objects
• extend programming language (e.g. Smalltalk or
  C) to allow declaration of persistent objects
• operating system responds to object faults in a
  manner similar to that employed for page faults
• Early 1990s object-relational databases
• tuple components can be objects
• still requires top level component to be a table
  (see SQL1999)
• Mid 1990s proliferation of object DBMSs
• O2, GemStone, ObjectStore, Poet, Versant
• Late 1990s object data management standards
• ODMG, SQL1999, CORBA

4

• We will
• investigate concepts in context of an
  object-oriented database system (GemStone),
  which uses extensions of Smalltalk
• then examine the main standards (ODMG, SQL1999,
  CORBA)

5
The object-oriented viewpoint
Entity instances table gt class row gt
object table attribute gt object attribute
table cell gt object attribute value
simple number, string, date, time
complex object, set Relationships via logical
inclusion, e.g., Fish object contains its
associated tank and species in attributes home
and breed.
name flipper color black weight
122 home breed
a Fish object
a Tank object
a Species object

• For application modeling, have object-oriented
  mechanisms
• hierarchical (incremental) class definitions
• encapsulation (protected access to object
  attributes)
• tailored access procedures (class-specific
  functions)
• polymorphism (same function name, different
  action, depending on class)
• Queries require object-oriented language or an
  overlay to emulate an enhanced relational scheme
• In OO language, database object differs from
  traditional data object only in persistence and
  shareability.

6
Class diagram for expanded aquarium example
Aquarium
Tank
Species
Fish
Event
ScientificTank
DisplayTank
ExperimentalFish
DisplayFish

• "is-a" diagram
• a DisplayTank object is a particular flavor of
  Tank object, which is a certain kind of Aquarium
  object
• a DisplayTank object contains its own special
  attributes, which distinguish it from
  other kinds of tanks, but also contains the
  generic tank attributes (inheritance) and the
  generic aquarium attributes
• does not show relationship topology

7

• Define schema
• establish class structure
• text emphasizes Smalltalk syntax (GemStone
  database)
• can also use C

Smalltalk syntax native hierarchy already
present in environment
Object Boolean Collection
SequenceableCollection Array
String OrderedCollection
SortedCollection Bag Set
Dictionary Magnitude Character
DateTime Number Float
Fraction Integer Stream
ReadStream WriteStream
ReadWriteStream
8
How to instantiate the schema?

• objects respond to signals tailored procedures
  (methods) part of class definition
• syntax x action. x action parameter. x
  action parameter1 keyword parameter2. x
  action parameter1 keyworda parameter2
  keywordb parameter3.
• precedence unary, binary, keyword
• binary provides infix notation for arithmetic
  and comparisons, e.g., x plus 4 x 4 x
  greaterthan y x gt y

9
A class is a meta-object responds to the keyword
signal subclass instanceVariables
constraints
Object subclass "Aquarium" instanceVariables
("creator", "creationDate", "modificationDate") c
onstraints (("creator", String),
("creationDate", String), ("modificationDate",
String)). Aquarium subclass "Species" instanceV
ariables ("name", "food", "reps") constraints
(("name", String), ("food", String), ("reps",
FishSet)). Aquarium subclass "Tank" instanceVar
iables ("name", "color", "volume",
"population") constraints (("name", String),
("color", String), ("volume", Number),
("population", FishSet)). Tank subclass
"ScientificTank" instanceVariables
("temperature", "salinity") constraints
(("temperature", Number), ("salinity",
Number)). etc. Set subclass "AquariumSet". Aqua
riumSet subclass "SpeciesSet" constraint
Species. AquariumSet subclass "TankSet"
constraint Tank. AquariumSet subclass "FishSet"
constraint Fish. FishSet subclass
"ExperimentalFishSet" constraint
ExperimentalFish. etc.
10
Schema must also describe tailored functions
(methods) for each new class
Aquarium method "creator" code
creator. Aquarium method "creator" code
param creator param. self. etc. Aqu
arium method "initialize" code self
creator (System user) creationDate (System
today) modificationDate (System
today). self. etc. Species method "name"
code name. Species method "name" code
param name param. self. etc.

• period is statement separator
• caret is return operator
• semi-colon indicates sequence of signals to same
  target object
• self refers to object that received the signal
  invoking the code
• schema needs to define access methods to
  retrieve and set attributes (instance variables)
• only these tailored methods can access
  attributes by name external code must use the
  access methods
• note assignment operator ()

11
Finally, schema must establish global objects to
receive query signals
TheSpecies SpeciesSet new. TheTanks
TankSet new. TheFish FishSet
new. TheEvents EventSet new.

• assume database populated with objects, all
  interconnected through logical inclusion
• assume global sets contain all objects of the
  specified type
• how to query for information?

12
Query Find the names of blue fish.
TheFish do aFish aFish color "blue"
ifTrue aFish name display. NewLine
display

• boolean responds to signals ifTrue ifTrue
  ifFalse ifFalse
• strings and Newline system object respond to
  display signal numbers also respond to display
  and to asString
• sets responds to iterators
• do parameterized code block executes
  designated code for each set member
• select parameterized code block returns
  subset for which code block evaluates to true
• collect parameterized code block returns
  subset of items gathered from set members

13
Approach applies to all single-class queries,
e.g., Find names and weights of blue fish
heavier than 10 pounds.
TheFish do aFish (aFish color
"blue") (aFish weight gt 10) ifTrue
aFish name display. Tab
display. aFish weight display. Newline
display
14
Multiclass queries Find names of tanks
containing an orange fish heavier than 10
pounds.
answer answer Set new. TheTanks do aTank
aTank population do aFish (aFish color
"orange") (aFish weight gt 10) ifTrue
answer add aTank name . answer
do aString aString display. Newline display
15
Same query Find names of tanks containing an
orange fish heavier than 10 pounds.
TheFish
aFish
name ------ color ------ volume
------ population
name ------ color "orange" weight
25 home breed
16
Extended multiclass queries Find names of blue
tanks with a species that also appears in a green
tank.
answer answer Set new. TheTanks do aTank
aTank color "blue" ifTrue aTank
population do aFish aFish breed reps do
bFish bFish home color "green" ifTrue
answer add aTank name
. answer do aString aString display.
Newline display
17
Simplified syntax instrument SpeciesSet,
TankSet, FishSet classes to respond to same
attribute observer signals add to schema
TankSet method "name" code self collect
aTank aTank name . TankSet method
"color" code self collect aTank aTank
color . TankSet method "population" code
temp temp FishSet new. self do aTank
aTank population do aFish temp add
aFish temp etc.
18
Database paths Revisit query Find names of blue
tanks with a species that also appears in a
green tank.
answer answer Set new. TheTanks do aTank
aTank color "blue" ifTrue (aTank
population breed reps home color includes
"green") ifTrue answer add aTank
name . answer do aString aString
display. Newline display
19
Database paths Revisit query Find names of blue
tanks with a species that also appears in a
green tank.
answer answer Set new. TheTanks do aTank
aTank color "blue" ifTrue (aTank
population breed reps home color includes
"green") ifTrue answer add aTank
name . answer do aString aString
display. Newline display
need parenthesis else String (color)
receives a includes ifTrue signal
which it is not equipped to handle
20
Database path constructs a tree of objects and
returns yield of tree
answer answer Set new. TheTanks do aTank
aTank color "blue" ifTrue (aTank
population breed reps home color includes
"green") ifTrue answer add aTank
name . answer do aString aString
display. Newline display
21
Pattern forms a template for existential
queries More complex existential query Find
names of species represented in the same
tank with a shark, which in turn is represented
in a green tank.
? Species
Tank
Species (shark)
Tank (green)
Fish
Fish
Fish
answer answer Set new. TheSpecies do
aSpecies ((aSpecies reps home population
breed select aSpecies aSpecies name
"shark") reps home color includes
"green") ifTrue answer add aSpecies
name . answer do aString
aString display. Newline display
22
? Species
Tank
Species (shark)
Tank (green)
Fish
Fish
Fish

• Variations
• store intermediate species

answer tempSet answer Set new. TheSpecies
do aSpecies tempSet aSpecies reps home
population breed. ((tempSet select aSpecies
aSpecies name "shark") reps home color
includes "green") ifTrue answer add
aSpecies name . answer do aString
aString display. Newline display
23
? Species
Tank
Species (shark)
Tank (green)
Fish
Fish
Fish

• Variations
• start from anchor tank, rather than candidate
  species

answer tempSet answer Set new. TheTanks do
aTank aTank color "green" ifTrue
tempSet aTank population breed select
aSpecies aSpecies name "shark".
answer addAll tempSet reps home population breed
name . answer do aString
aString display. Newline display
24
Same template for all existential
queries Library database

• Envision schema
• Library (simple attributes myCopies)
• Author (simple attributes myBooks)
• Book (simple attributes myAuthor myCopies)
• Copy (simple attributes myLibrary myBook
  myLoans)
• Loan (simple attributes myCopy myPatron)
• Patron (simple attributes myLoans)
• Also global access sets
• TheLibraries
• TheAuthors
• TheBooks
• TheCopies
• TheLoans
• ThePatrons

25
Author (A)
Find names of libraries that serve a patron who
also uses a Seattle library.
Library (L)
Book (B)
Copy (C)
Patron (P)
Loan (U)
answer answer Set new. TheLibraries do
aLibrary aLibrary location "Seattle"
ifTrue answer addAll aLibrary myCopies
myLoans myPatron myLoans myCopy myLibrary name
. answer do aString aString
display. Newline display
26
Author (A)
Find names of libraries that serve a patron who
also uses a Seattle library.
Library (L)
Book (B)
Copy (C)
Patron (P)
Loan (U)
Can also start with candidate objects (rather
than anchor objects)
answer answer Set new. TheLibraries do
aLibrary (aLibrary myCopies myLoans
myPatron myLoans myCopy myLibrary location
includes "Seattle") ifTrue answer add
aLibrary name . answer do aString
aString display. Newline display
27
Following question introduced universal queries
and their templates Find names of species that
appear in all green tanks.
Species ??
Fish
Tank
Path bundle must embrace all green tanks
answer greenTanks myTanks answer Set
new. greenTanks TheTanks select aTank
aTank color "green". TheSpecies do aSpecies
myTanks aSpecies reps home. (myTanks
contains greenTanks) ifTrue answer add
aSpecies name . answer do aString
aString display. Newline display
28
Again, pattern constitutes a template for
universal queries Another example Find names of
red tanks that contain all species that eat trout.
Fish
Species
Tank ?? (red)
trout-eating species
answer trouties mySpecies answer Set
new. trouties TheSpecies select aSpecies
aSpecies food "trout". TheTanks do aTank
mySpecies aTank population breed. (aTank
color "red") (mySpecies contains trouties)
ifTrue answer add aTank name . answer
do aString aString display. Newline
display
29
Longer universal connection Find names of red
tanks that contain all species that appear
in all tanks of volume 1000 or greater.
Tank
Fish
Species
Fish
Tank ?? (red)
large tanks
answer goodCandidates redTanks eliteSpecies
largeTanks largeTanks TheTanks select
aTank aTank volume gt 1000. eliteSpecies
TheSpecies select aSpecies aSpecies reps
home contains largeTanks. redTanks TheTanks
select aTank aTank color
"red". goodCandidates redTanks select
aTank aTank population breed contains
eliteSpecies. answer goodCandidates collect
aTank aTank name. answer do aString
aString display. Newline display
30
Double negation is a characteristic theme of
universal queries
In SQL select S.sname from Species S where not
exists (select from Tank T where T.tcolor
"green" and not exists (select from Fish F
where S.sno F.sno and F.tno T.tno))
31
Double negation is a characteristic theme of
universal queries
32
Mixed universal-existential query Find names
of red tanks that contain all species that appear
in some tank of volume 1000 or greater.
Tank
Fish
Species
Fish
Tank ?? (red)
large tanks
answer goodTanks eliteSpecies
largeTanks largeTanks TheTanks select aTank
aTank volume gt 1000. eliteSpecies
TheSpecies select aSpecies (aSpecies reps
home intersect largeTanks) isEmpty
not. goodTanks TheTanks select aTank
(aTank color "red") (aTank population
breed contains eliteSpecies). answer
goodTanks collect aTank aTank
name. answer do aString aString display.
Newline display

• sets respond to union, intersection,
  difference signals
• sets respond to isEmpty signal
• boolean responds to not signal
• can use to emulate SQL double-negation template

33
Prototypical universal query Find species in all
red tanks.
SQL double-negation solution select
S.sname from Species S where not exists (select
from Tank T where T.tcolor "red" and not
exists (select from Fish F where S.sno
F.sno and F.tno T.tno
O-O emulation answer goodSpecies
missingTanks connectingFish goodSpecies
TheSpecies select aSpecies missingTanks
TheTanks select aTank connectingFish
TheFish select aFish (aFish breed
aSpecies) (aFish home aTank). (aTank color
"red") connectingFish isEmpty. missingTanks
isEmpty. answer goodSpecies collect
aSpecies aSpecies name. answer do
aString aString display. Newline display
34
Universal queries, regardless of database, follow
same template Library database
Author (A)
Library (L)
Book (B)
Copy (C)
Patron (P)
Loan (U)
Find names of libraries that serve all patrons
who weigh less than 100 pounds.
U
P
C
L ?
light patrons
35
U
P
C
L ?
light patrons
answer goodLibs lightPatrons lightPatrons
ThePatrons select aPatron aPatron weight
lt 100. goodLibs TheLibraries select aLib
aLib myCopies myLoans myPatron contains
lightPatrons. answer goodLibs collect aLib
aLib name. answer do aString aString
display. Newline display
36
Another example a university database assume
myX attributes in each entity, e.g., in
Section have myProf, myCourse, and myGrades
Department (D)
offers
Professor (P)
majors
Course (C)
teaches
deploys
Student (St)
Section (S)
receives
delivers
Grade (G)
Find names of professors who have taught all
students who have taken a database course.
S
G
St
G
S
C (database)
P ?
37
Department (D)
Find names of professors who have taught all
students who have taken a database course.
offers
Professor (P)
majors
Course (C)
teaches
deploys
Student (St)
Section (S)
receives
delivers
Grade (G)
answer dbAwareStudents goodProfs dbAwareStuden
ts TheStudents select aStudent
aStudent myGrades mySection myCourse name
includes "Database". goodProfs TheProfs
select aProf aProf mySections myGrades
myStudent contains dbAwareStudents. answer
goodProfs collect aProf aProf
name. answer do aString aString display.
Newline display
38
Following example illustrated generalized
relational division
Find the (species-name, worker-name) pairs such
that the species appears in all tanks that the
worker cleans.
Species (S)
Tank (T)
Worker (W)
Fish (F)
Cleaning (C)
S ?
F
T
C
W ?
tanks cleaned by worker tanks where species
appears
39
Species (S)
Tank (T)
Worker (W)
Fish (F)
Cleaning (C)
answer tanksCleaned tanksHousing answer
Set new. TheSpecies do aSpecies
tanksHousing aSpecies reps
home. TheWorkers do aWorker tanksCleaned
aWorker myCleanings myTank. (tanksHousing
contains tanksCleaned) ifTrue answer add
aSpecies name, Tab, aWorker name answer
do aString aString display. Newline
display
40
Department (D)
Another example a university database
offers
Professor (P)
Find names of professors who taught a section in
which all students received As.
majors
Course (C)
teaches
deploys
Student (St)
Section (S)
receives
Grade (G)
answer goodProfs goodMarks easySections
TheSections select aSection goodMarks
aSection myGrades select aGrade aGrade mark
"A". goodMarks contains aSection
myGrades. goodProfs TheProfs select aProf
(aProf mySections intersect easySections)
empty not. answer goodProfs collect aProf
aProf name. answer do aString aString
display. Newline display
41
Aggregate queries --- have full power of
general-purpose programming language
Example Find average weight of the fish.
fishWeight fishWeight 0. TheFish do
aFish fishWeight fishWeight aFish
weight. TheFish size gt 0 ifTrue (fishWeight /
TheFish size) display ifFalse 0 display
42
multiple equivalence classes Find average fish
weight by species.
fishWeight TheSpecies do aSpecies
fishWeight 0. aSpecies reps do aFish
fishWeight fishWeight aFish
weight. aSpecies name display. Tab
display. aSpecies reps size gt 0 ifTrue
(fishWeight / aSpecies reps size) display
ifFalse 0 display. Newline display
43
Possible over-representation in sum or average
as in relational algebra or SQL? Find the
average volume of tanks by species
TheSpecies do aSpecies totVol totVol
0. aSpecies reps home do aTank totVol
totVol aTank volume. aSpecies name
display. Tab display. aSpecies reps home size gt
0 ifTrue (totVol / aSpecies reps home size)
display ifFalse 0
display. Newline display
44
Having clause equivalents Find average tank
volume by species, for those species that appear
in two or more tanks.
TheSpecies do aSpecies totVol totVol
0. aSpecies reps home size gt 2 ifTrue
aSpecies reps home do aTank totVol
totVol aTank volume. aSpecies name
display. Tab display. (totVol / aSpecies reps
home size) display. Newline display
same partition for rejecting equivalence
class as for reporting
45
Multiple partitions Find total tank volume by
species, for those species having 1000 or more
fish representatives.
TheSpecies do aSpecies totVol totVol
0. aSpecies reps home do aTank totVol
totVol aTank volume. aSpecies reps size gt
1000 ifTrue aSpecies name display. Tab
display. totVol display. Newline display
46
Arithmetic expressions By species, find the
average water volume per fish.
TheSpecies do aSpecies totVol totVol
0. aSpecies reps home do aTank totVol
totVol aTank volume. aSpecies name
display. Tab display. aSpecies reps size gt 0
ifTrue (totVol / aSpecies reps size)
display ifFalse 0 display. Newline
display
nothing new have been using arithmetic
expressions for emulating SQL aggregates sum,
count, max, min, average
47
Find names of patrons who meet the criterion the
average page count of the books he has read
exceeds the total number of rooms in the
libraries he has used.
Author (A)
Library (L)
Book (B)
Copy (C)
Patron (P)
Loan (U)
goodPatrons answer goodPatrons ThePatrons
select aPatron totPages avgPages
totRooms totPages 0. aPatron myLoans myCopy
myBook do aBook totPages totPages aBook
pages. totRooms 0. aPatron myLoans myCopy
myLibrary do aLib totRooms totRooms aLib
rooms. totPages gt 0 ifTrue avgPages
totPages / aPatron myLoans myCopy myBook size
ifFalse avgPages 0. avgPages gt
totRooms . answer goodPatrons collect
aPatron aPatron name. answer do aString
aString display. Newline display.
48
Ordering output
Find names and weights of orange fish in green
tanks. Display by decreasing fish weight.
goodFish goodFish TheFish select aFish
(aFish color "orange") (aFish home color
"green"). (goodFish asSortedCollection a b a
weight gt b weight) do aFish aFish name
display. Tab display. aFish weight
display. Newline display
49
General programming interface removes problems
with noncompatible unions and the like...
For example, find names of orange aquarium objects
SQL select F.fname as brightname from Fish
F where F.fcolor "orange" orderby
brightname union select T.tname from Tank
T where T.tcolor "orange"
OO syntax answer answer Set new. TheFish
do aFish aFish color "orange" ifTrue
answer add aFish name. TheTanks do aTank
aTank color "orange" ifTrue answer add aTank
name. answer do aString aString display.
Newline display
50
Recursive classes
worker role
anEmployee
Employee
name ----- mySuper myWorkers
w
w
supervisor role
w
w
w
w
Find names of workers reporting to a "dennis" at
any level of indirection.
answer inChain answer Set new. inChain
TheEmployees select anEmployee anEmployee
name "dennis". inChain isEmpty whileFalse
anEmployee anEmployee inChain
choose. inChain remove anEmployee. inChain
addAll anEmployee myWorkers. answer add
anEmployee name . answer do aString
aString display. Newline display
51
Data editing operations insert, delete, update

• Assume
• species names and tank names are unique
• all species and tanks already in database
• want to load a new fish with name
  "calvin" color "green" weight 8.5 species
  "shark" tank "lagoon"

aSpecies aTank aFish (aSpecies TheSpecies
select bSpecies bSpecies name "shark")
choose. (aTank TheTanks select bTank
bTank name "lagoon") choose. aFish Fish
new initialize breed aSpecies home
aTank name "calvin" color "green" weight
8.5 experience (EventSet new). TheFish add
aFish. aSpecies reps (aSpecies reps add
aFish). aTank population (aTank population add
aFish).
52
Loading from a flat file

• Assume
• each fishFile record represents one object
• each starts with the string Species, Tank, Fish,
  or Event as appropriate
• a fish record references only a species and a
  tank that precede it
• all event records for a given fish immediately
  follow the fish record
• no duplicate definitions (one record per
  species, per tank, etc.)
• species data are name and food -- both strings
• tank data are name, color, volume -- all strings
• fish data are name, color, weight, species name,
  tank name -- all strings
• event data are note, date -- both strings

aFile aFile File open "fishFile". aFile
endOfFile whileFalse . aFile close.
53
Loading from a flat file (continued)
aFile aFile File open "fishFile". aFile
endOfFile whileFalse aFish aSpecies aTank
anEvent aString aString aFile
readString. aString "Species" ifTrue
aSpecies Species new. aSpecies
initialize. aSpecies name aFile readString
food aFile readString reps FishSet
new. TheSpecies add aSpecies. aString
"Tank" ifTrue . aString "Fish"
ifTrue . aString "Event" ifTrue
. aFile close.
54
Loading from a flat file (continued)
aFile aFile File open "fishFile". aFile
endOfFile whileFalse aFish aSpecies aTank
anEvent aString aString aFile
readString. aString "Species" ifTrue
. aString "Tank" ifTrue aTank
Tank new. aTank initialize aTank name aFile
readString color aFile readString volume
aFile readString asNumber population FishSet
new. TheTanks add aTank. aString "Fish"
ifTrue . aString "Event" ifTrue
. aFile close.
55
Loading from a flat file (continued)
aFile aFile File open "fishFile". aFile
endOfFile whileFalse aFish aSpecies aTank
anEvent aString aString aFile
readString. aString "Species" ifTrue
. aString "Tank" ifTrue
. aString "Fish" ifTrue aFish
Fish new. aFish initialize. aFish name aFile
readString color aFile readString weight
aFile readString as Number experience EventSet
new. aString aFile readString. aSpecies
(TheSpecies select bSpec bSpec name
aString) choose. aFish breed
aSpecies. aSpecies reps (aSpecies reps add
aFish). aString aFile readString. aTank
(TheTanks select bTank bTank name aString)
choose. aFish home aTank. aTank population
(aTank population add aFish). TheFish add
aFish. . aString "Event" ifTrue
. aFile close.
56
Loading from a flat file (continued)
aFile aFile File open "fishFile". aFile
endOfFile whileFalse aFish aSpecies aTank
anEvent aString aString aFile
readString. aString "Species" ifTrue
. aString "Tank" ifTrue
. aString "Fish" ifTrue
. aString "Event" ifTrue anEvent
Event new. anEvent initialize. anEvent note
aFile readString date aFile readString
subject aFish. aFish experience (aFish
experience add anEvent) . aFile close.
57
Deletions

• Assume
• want to delete the unique tank named lagoon
• want to cascade deletion to fish in lagoon and
  to all their events
• can accomplish cascade with a trigger.... later

aTank aFish anEvent aTank (TheTanks
select bTank bTank name "lagoon")
choose. TheEvents removeAll aTank population
experience. aTank population do aFish aFish
experience EventSet new. aFish breed reps
(aFish breed reps remove aFish). TheFish
removeAll aTank population. aTank population
FishSet new. TheTanks remove aTank
58
Updates

• updates cannot change object identity
• updates cannot compromise referential integrity
• effect on connected objects is same as SQL's
  cascade

Example Change the tank color to red for all
tanks that contain a dolphin.
markedTanks markedTanks TheTanks select
aTank aTank population breed includes
"dolphin". markedTanks do aTank aTank
color "red"
59
Constraints

• constraints specified in schema
• domain constraints already illustrated

Aquarium subclass "Tank" instanceVariables
("name", "color", "volume", "population") constra
ints (("name", String), ("color", String),
("volume", Number), ("population", FishSet)).

• global and class constraints are enforced by
  intercepting attribute signals
• suppose want the functional dependency volume ?
  color in the tank class

Tank method "volume" code param
violators violators TheTanks select
aTank ((aTank self) not
(aTank volume param) (aTank color
color) not). violators isEmpty ifTrue volume
param .
Tank method "color" code param
violators violators TheTanks select
aTank ((aTank self) not
(aTank volume volume) (aTank color
param) not). violators isEmpty ifTrue color
param .
60
Constraints (continued)
Another example The aquarium can contain at
most 10 tanks

• constraint on size of TheTanks
• can apply to all instances of class TankSet
• write add method in TankSet class, which
  overrides parent method

TankSet method "add" code param self
size lt 10 ifTrue super add param
61
Constraints (continued)
When a constraint violation occurs

• examples for far silently reject the update
• can display a warning message
• put code in Object class, so it is available for
  any application object or set

Object method "constraintViolation" code
param param display. Newline display
TankSet method "add" code param self
size lt 10 ifTrue super add param
ifFalse self constraintViolation "A tank
set is limited to 10 members."
62
Triggers
Example maintain referential integrity
63
Triggers (continued)
64
Object Query Language (OQL)
Notation Smalltalk syntax OQL
syntax attribute signals t name t.name datab
ase paths f breed name f.breed.name But, O
QL database paths can proceed only from
many-to-one, e.g., f.breed.name acceptable
syntax t.population.breed unacceptable
65
Single class queries
Find the names of blue fish.
Smalltalk syntax answer goodFish goodFish
TheFish select aFish aFish color
"blue". answer goodFish collect aFish
aFish name. answer do aString aString
display. Newline display
OQL select f.name from f in TheFish where
f.color "blue"
Compare relational SQL select f.fname from Fish
f where f.color "blue"
66
Multiclass queries Find names of tanks
containing an orange fish heavier than 10 pounds.
answer goodTanks goodTanks TheTanks
select aTank (aTank population select
aFish (aFish color "orange") (aFish
weight gt 10)) isEmpty not. goodTanks collect
aTank aTank name. answer do aString
aString display. Newline display
select t.name from t in TheTanks where
exists (select f from f in TheFish where
f.home t and t.color "orange" and f.weight gt
10)
select t.name from t in TheTanks where
exists (select f from f in t.population where
t.color "orange" and f.weight gt 10)
67
Can start from TheFish collection
Same query Find names of tanks containing an
orange fish heavier than 10 pounds.
answer goodFish goodFish TheFish select
aFish (aFish color "orange" ) (
aFish weight gt 10). answer goodFish
collect aFish aFish home name. answer do
aString aString display. Newline display
OQL select f.home.name from f in
TheFish where f.color "orange" and f.weight gt
10
68
Extended multiclass queries Find names of blue
tanks with a species that also appears in a
green tank.
answer goodTanks goodTanks TheTanks select
aTank (aTank color "blue") (aTank
population breed reps home color includes
"green"). answer goodTanks collect aTank
aTank name. answer do aString aString
display. Newline display
69
Tank ??
Species
Tank (green)
Fish
Fish
shorter syntax if directly address only classes
at low points in relationship hierarchy
select f.home.name from f in TheFish where
f.home.color "blue" and exists (select
g from g in f.breed.reps where g.home.color
"green"))
70
As expected, pattern forms a template for
existential queries in OQL More complex
existential query Find names of species
represented in the same tank with a shark,
which in turn is represented in a green tank.
? Species
Tank
Species (shark)
Tank (green)
Fish
Fish
Fish
select f.breed.name from f in TheFish where
exists (select g from g in f.home.population w
here g.breed.name "shark" and exists (select
h from h in g.breed.reps where h.home.color
"green"))
71
Author (A)
Library database with usual attributes,
i.e., myCopies, myLoans, myPatrons... Find names
of libraries that serve a patron who also uses a
Seattle library.
Library (L)
Book (B)
Copy (C)
Patron (P)
Loan (U)
select u.myCopy.myLibrary.name from u in
TheLoans where exists (select v from v in
u.myPatron.myLoans where v.myCopy.myLibrary.locat
ion "Seattle")
72
Universal queries prototypical query find
names of species represented in all green tanks
Species ??
Fish
Tank
Path bundle must embrace all green tanks
select s.name from s in TheSpecies where not
exists (select t from t in TheTanks where
t.color "green" and not exists (select
f from f in t.population where f.breed s))
select s.name from s in TheSpecies where (select
f.home from f in s.reps) contains (select
t from t in TheTanks where t.color "green")
73
Again, pattern constitutes a template for
universal queries Another example Find names of
red tanks that contain all species that eat trout.
Fish
Species
Tank ?? (red)
trout-eating species
select t.name from t in TheTanks where t.color
"red" and (select f.breed from f in
t.population) contains (select s from s in
TheSpecies where s.food "trout")
select t.name from t in TheTanks where t.color
"red" and not exists (select s from s in
TheSpecies where s.food "trout" and not
exists (select f from f in s.reps where
f.home t))
74
Longer universal connection Find names of red
tanks that contain all species that appear
in all tanks of volume 1000 or greater.
Tank
Fish
Species
Fish
Tank ?? (red)
large tanks
select t.name from t in TheTanks where t.color
"red" and (select f.breed from f in
t.population) contains (select s from s in
TheSpecies where (select g.home from g in
s.reps) contains (select q from q in
TheTanks where q.volume gt 1000))
75
Mixed universal-existential query Find names
of red tanks that contain all species that appear
in some tank of volume 1000 or greater.
Tank
Fish
Species
Fish
Tank ?? (red)
large tanks
select t.name from t in TheTanks where t.color
"red" and (select f.breed from f in
t.population) contains (select g.breed from g
in TheFish where g.home.volume gt 1000)
76
Universal queries, regardless of database, follow
same template Library database
Author (A)
Library (L)
Book (B)
Copy (C)
Patron (P)
Loan (U)
Find names of libraries that serve all patrons
who weigh less than 100 pounds.
U
P
C
L ?
light patrons
77
U
P
C
L ?
light patrons
select L.name from L in TheLibraries where
(select u.myPatron from u in TheLoans where
u.myCopy.myLibrary L) contains (select p from
p in ThePatrons where p.weight lt 100)
78
Another example a university database assume
myX attributes in each entity, e.g., in
Section have myProf, myCourse, and myGrades
Department (D)
offers
Professor (P)
majors
Course (C)
teaches
deploys
Student (St)
Section (S)
receives
delivers
Grade (G)
Find names of professors who have taught all
students who have taken a database course.
S
G
St
G
S
C (database)
P ?
79
Department (D)
Find names of professors who have taught all
students who have taken a database course.
offers
Professor (P)
majors
Course (C)
teaches
deploys
Student (St)
Section (S)
receives
delivers
Grade (G)
select p.name from p in TheProfs where (select
g.myStudent from g in TheGrades where
g.mySection.myProf p) contains (select
h.myStudent from h in TheGrades where
h.mySection.myCourse.name "database")
80
Following example illustrated generalized
relational division
Find the (species-name, worker-name) pairs such
that the species appears in all tanks that the
worker cleans.
Species (S)
Tank (T)
Worker (W)
Fish (F)
Cleaning (C)
S ?
F
T
C
W ?
tanks cleaned by worker tanks where species
appears
81
Species (S)
Tank (T)
Worker (W)
Fish (F)
Cleaning (C)
select s.name, w.name from s in TheSpecies, w in
TheWorkers where (select f.home from f in
s.reps) contains (select g.myTanks from g in
w.myCleanings)
82
Department (D)
Finally, for completeness
offers
Professor (P)
Find names of professors who taught a section in
which all students received As.
majors
Course (C)
teaches
deploys
Student (St)
Section (S)
receives
Grade (G)
select s.myProf.name from s in TheSections where
(select g from g in s.myGrades where g.mark
"A") contains (select g from g in s.myGrades)
83
The ODMG standard

• Will present an overview
• For more detail
• Cattell, R. and Barry, D. (eds.). The Object
  Database Standard ODMG 3.0, Morgan Kaufmann,
  San Francisco, 2000.
• Pope, A. The CORBA Reference Guide,
  Addison-Wesley, Reading, MA, 1998.
• Henning, M. and Vinoski, S. Advanced CORBA
  Programming with C, Addison-Wesley, Reading,
  MA, 1999.

84
The ODMG standard
Schema Specification (ODL embedded in
Smalltalk, C, Java, ...)
Source Code for Class Methods (also in Smalltalk,
C, Java, ...)
ODL processor
Host language compiler
Method Implementation Object Code
ODBMS Libraries
Object Code Linker
Method binaries stored in DBMS
Metadata
Object Data
85
The ODMG standard
Application Source Code (Smalltalk, C, Java,
...)
Schema Specification (ODL embedded in
Smalltalk, C, Java, ...)
Source Code for Class Methods (also in Smalltalk,
C, Java, ...)
Host language compiler
Host language compiler
ODL processor
Application Object Code
Method Implementation Object Code
Metadata
Object Data
Object Code Linker
ODBMS Libraries
Object Code Linker
Method binaries stored in DBMS
Application binary
86
The ODMG standard
ODL Object Definition Language

• somewhat superfluous --- can define persistent
  classes and objects in host language
• but, C, Smalltalk, Java all have different
  approaches --- how to access objects
  declared in another language ?
• ODL describes attributes, methods, and
  inheritance properties of objects in a
  language independent manner
• ODL is an extension of the Interface Definition
  Language (IDL) used to specify objects in
  CORBA
• ODL distinguishes two kinds of classes
  interfaces and classes (similar to Java) Why?
  (1) compatibility ODL is superset of IDL
  (CORBA) (2) sidesteps some multiple
  inheritance problems
• each kind specifies a class name, its location
  in the inheritance hierarchy, and a type
• collection of such specifications describes an
  entire ODMG database
• restrictions on interfaces
• no method bodies, just signatures no
  attributes no relationships
• no objects minted from an interface (extent is
  objects belonging to its subclasses)
• cannot inherit from a class, only from other
  interfaces (class can inherit from multiple
  interfaces, but can have only one superclass)

87
colon to inherit from interface keyword "extends"
to inherit from a class
// An interface interface Person_Interface
Object String Name() String SSN() enum
Genders m, f Sex() // A class class
Person Person_Interface (extent Person_ext
keys (SSN, (Name, PhoneN) PERSISTENT) attrib
ute Address_type Address attribute SetltStringgt
PhoneN relationship Person Spouse
relationship SetltPersongt Child void
add_phone_number (in String phone) // A
literal struct Address_type String
StNumber String StName
Object is top-level interface in ODMG provides
common methods for delete(), copy(), same_as()
only method signatures in interface
extent is the set of objects minted from class
attribute must be a literal -- stored in
object relationship is an object -- stored
separately
could specify method body here must use host
language execution can be commanded
from another host language
88
name overloading forbidden in ODMG cannot inherit
same method from more than one interface or class
// A subclass class Student extends Person
(extent Student_ext) attribute SetltStringgt
Major relationship SetltCoursegt
Enrolled inverse CourseEnrollment // A
base class class Course Object (extent
Course_ext) attribute SetltStudentgt
Enrollment inverse StudentEnrolled
relationship matching stronger than referential
integrity referential integrity student
courses must be legitimate courses relationshi
p matching student courses must be legitimate
courses, each of which acknowledges the
student in its Enrollment relationship
89
The ODMG standard
OQL Object Query Language
can access via command-line interface or
embedded in host language
Select P.Address from Person_ext P where P.Name
"Jones"
specify extents where objects reside
returns a literal of type SetltAddress_typegt class
Set has built-in iterators to examine individua
l entries
90
Can use subqueries, even in select-clause Find
species and the tanks in which they appear for
those species that appear in a tank that contains
all trout-eating species select struct
species_name S.name, related_tanks
(select F.home.name from S.representatives
F) from Species_ext S where
exists (select from S.representatives F
where F.home in (select T from Tank_ext
T where (select G.breed from
T.population G) contains
(select S' from Species_ext
S' where S'.food
"trout")))
91
Aggregates Count fish by species select
struct species_name S.name, fishCnt
count(select from S.representatives F)
from Species_ext S
data is already stratified by species don't need
groupby clause
Find average tank volume by species select
struct species_name S.name, avgVol
average(select T.volume from Tank_ext
T where exists (select
from T.population F where F.breed
S) ) from Species_ext S
92
Can force side-effects select
P.add_phone_number("123-4569") from Person_ext
P where P.SSN "123-45-6789"
add_phone_number was void method nothing
returned by query phone number added to
PhoneN attribute as side-effect
93
The ODMG standard

• Language bindings
• Can pass OQL to DBMS using CLI, such as ODBC or
  JDBC
• But, major goal of ODMG model is to reduce or
  eliminate "impedance mismatch" that is, treat
  database objects and transient program objects
  with same tools
• ODL processor defines database classes and
  interfaces in each supported host language in
  libraries that are included in application
  program
• Java program, for example, can define a subclass
  of a database class
• public class Student extends Person
• public DSet Major
• .
• .
• .
• Student X
• X.Major.add("Computer Science")

probably need a new Set type to implement
all set functions expected for ODMG set not
possible in all languages
94
The ODMG standard
Some difficulties with languages bindings

• How to distinguish persistent from transient
  objects? in C, use a special form of new( ) to
  create a persistent object in Java, use special
  method, makePersistent( )
• How do bindings represent and implement
  relationships? in C and Smalltalk, use special
  classes and methods in Java, not supported?
• How are ODMG literals represented? in C, as
  struct in Java and Smalltalk, map into objects
• How are OQL queries executed? use CLI,
  associating query string with statement
  object use special methods to associate
  parameters with host objects use an execute( )
  method directed at the statement object
• How are databases connections established? each
  language has a special collection of methods

95
Java Binding Example
use OQLQuery class to instantiate an statement
object class OQLQuery public OQLQuery
(String query) // constructor public bind
(Object parameter) public Object
execute() ... ... ...
What is a DSet?
96
Java Binding Example (continued)
DSet is subclass of DCollection .... provided by
the Java Language Bindings package public
interface DCollection extends java.util.Collection
public DCollection query (String
condition) public Object selectElement (String
condition) public Boolean existsElement (String
condition) public java.util.Iterator select
(String condition) ... ... ...
97
SQL1999 Objects

• Will present an overview
• For more detail
• Lewis, Philip M., Bernstein, Arthur, and Kifer,
  Michael. Databases and Transaction Processing
  An Application-Oriented Approach,
  Addison-Wesley, Reading, MA, 2002.
• Fuh, Y.-C. Dessloch, S. Chan, W Mattos, N
  Tran, B Lindsay, B DeMichiel, L Fielau, S.
  and Mannhaupt, D. Implementation of
  SQL3 structured types with inheritance and value
  substitutability, Proceedings of the
  International Conference on Very Large Data
  Bases, pp. 565-574, Edinburgh, Scotland, 1999.
• Gulutzan, P. and Pelzer, T. SQL-99 Compete,
  Really, RD Books, Gilroy, CA., 1999.

98
SQL1999 Objects

• SQL1999 database is a set of relations
• Each relation is a set of tuples or a set of
  objects
• An object is, at the highest level, a tuple ....
  but with an OID
• A tuple-value has the following form, consisting
  of distinct attribute names and values
• The following values are allowed
• primitive types, e.g., integer, float, boolean,
  char(20)
• reference types, i.e., object IDs
• a nested tuple
• an array of one of the above types (sets and
  lists did not make it into the standard)

99
Row construction
Use row construction to specify and populate
nested tuples create table Person ( name
char(20), address ROW (number integer, street
char(20), zip char(5)) ) insert into Person
(name, address) values ("John Q. Student", ROW
(124, "Maple Street", "98225"))
Use path notation to access nested
fields select P.name from Person P where
P.address.zip "98225" update Person P set
address.zip "98226" where address.zip "98225"
100
User-defined types (UDT)
similar to person table DBMS defines observer and
mutator methods ...
create type personType as ( name
char(20), address row (number integer, street
char(20), zip char(5)) ) create type
studentType under personType as ( Id
integer, status char(2)) method award_degree( )
returns boolean create method award_degree ( )
for studentType language C external name
'file/home/admin/award_degree'
can specify language SQL and inline the
implementation in a BEGIN .... END block of
SQL/PSM
OS file contains executable image must
separately compile with specified language DBMS
must still know language to know how to link to
executable image
101
User-defined types (UDT)
Two main uses for UDTs (1) to establish the
domain for a table attribute, or
(2) to create a table of objects create table
transcript ( student studentType, courseCode
char(6), semester char(6), grade
char(1) ) create table student_class of
studentType
rows of this table are tuple-values rows of this
table are objects it is a typed table

• The only way to create an object in SQL1999 is
  to insert a row into a typed table
• each row is a separate object with a distinct
  oid
• the table becomes a class
• the collection of rows is the extent of the class

102
create type personType as ( name
char(20), address row (number integer, street
char(20), zip char(5)) ) create type
studentType under personType as ( Id
integer, status char(2)) method award_degree( )
returns boolean create table transcript
( student studentType, courseCode
char(6), semester char(6), grade
char(1) ) create table student_class of
studentType
student has many transcript entries each
contains all studentType fields want to embed an
oid from student_class instead recall that an
attribute value can be an oid
103
create type personType as ( name
char(20), address row (number