Object Relational Database Systems 1 Introduction 2 Objects

Object Relational Database Systems: 1. Introduction 2. Objects in SQL 3 3. Comparison of ODL/OQL and SQL 3 Approaches CIS 671 ORDBS 1

Object Query Language (OQL) • • “Bring the best of SQL to the object world. ” Syntax similar to SQL Plus additional features Works with programming languages where ODMG has defined bindings – Java, C++, Smalltalk – Returns object matching type system of that language – May implement class operations in these languages • SQL 3 – “Bring the best of the object-oriented world to the relational world. ” ORDBS 2

Overview: Systems SQL 3 Object Relational DB • Interfacing SQL to a programming language. – Cursors • Data Types – Built-in – User Defined • Operations ORDBS 3

Data Types – Built-in • smallint – 16 -bit integer • integer – 32 -bit integer • decimal(p, s) – decimal number, precision p, scale s • float – 32 -bit float • double – 64 -bit float • char(n) – fixed length, n ≤ 254 • varchar(n) – varying length • date – year, month, day • time – hour, minute, second • timestamp - year, month, day, hour, minute, second, microsecond • interval – year/month or day/time – blob(binary large object) ORDBS – n ≤ 254 – 254 ≤ n ≤ 4000; no group by, etc. • • bit(n) varbit(n) boolean [SQL 3] Large Objects [SQL 3] • ≤ 231 – 1 bytes – clob(character large object) • ≤ 231 – 1 bytes Domain e. g. , create domain SSN_TYPE as char(9) not used much. 4

Objects in SQL 3 • Row objects, tuples or structs, and • Abstract data types (ADT), general objects, used as components of tuples. ORDBS 5

Movies Example: Movies, Stars, Studios ODL Version Movies title year length /* in minutes */ film. Type: {color, black. And. White} length. In. Hours star. Names other. Movies stars owned. By Stars name starred. In address street city owns Studios name Set of stars of this movie. Other movies by star of this movie. ORDBS 6

Movies Example: Movies, Stars, Studios ER Version Movie ORDBS N Stars. In M Movie. Star 7

Row Types [SQL 3 or SQL: 1999] • Row-type Definition: Double dot create row type Path Exp. T (<component-declaration-list>) • Examples: create row type Address. Type( street char(50), city char(20)); create row type Star. Type( name char(30), Cannot directly address. Address. Type represent set of movies starred-in. ); create table Movie. Star of type Star. Type; ORDBS select Movie. Star. . name, Movie. Star. . address. . street from Movie. Star Where Movie. Star. . address. . city = ‘Columbus’; 8

References • Movie SQL 3’s way to represent “objects”. N Stars. In create row type Star. Type( name char(30), address Address. Type ); M create row type Movie. Type( title char(30), year integer ); create row type Stars. In. Type( star ref(Star. Type), movie ref(Movie. Type) ); create table Movie of type Movie. Type; create table Movie. Star of type Star. Type; create table Stars. In of type Stars. In. Type; ORDBS Movie. Star Dereferencing operator (->) select movie -> title from Stars. In where star->name = ‘Mel Gibson’ 9

Object Identifiers • May be explicitly declared and accessed. • Can serve as both object ID and primary key. create row type Star. Type( star_id ref(Star. Type), name char(30), address Address. Type ); create row type Movie. Type( movie_id ref(Movie. Type), title char(30), year integer ); create table Movie of type Movie. Type values for movie_id are system generated; create table Movie. Star of type Star. Type values for star_id are system generated; ORDBS 10

Specifying Scope to Aid Performance create row type Stars. In. Type( star ref(Star. Type), movie ref(Movie. Type) ); Index on name attribute. How to find movies? Examine every Stars. In tuple. select movie -> title from Stars. In where star->name = ‘Mel Gibson’ Could have another relation with Star. Type. create table Stars. In of type Stars. In. Type scope for star is Movie. Star, scope for movie is Movie; ORDBS 11

Objects in SQL 3 • Row objects, tuples or structs (REVIEW), and • Abstract data types (ADT), general objects, used as components of tuples. ORDBS 12

Abstract data type (ADT) • Used as components of tuples, – Not as tuples themselves. • Have tuple structure. ORDBS 13

ADT - “Built-in” Functions • Constructor functions returning new object of type. – All attributes initially null. – If T is name of ADT, then T() is constructor. • Observer functions for each attribute. – If A is attribute name & X is variable whose value is an object of the ADT, then A(X) (or X. A) is the value of attribute A of object X. • Mutator functions for each attribute. – Sets the value of that attribute to a new value. – Normally used on left side of assignment ORDBS 14

Example: Address ADT create type Address. ADT ( street char(50), city char(20), equals addr. Eq, less than addr. LT other functions could be declared here ); Addresses will be encapsulated • Access to street & city allowed only if observer and mutator functions made public. • Functions addr. EQ and addr. LT defined later. ORDBS 15

More interesting example: ADT for Mpegs create type Mpeg ( video blob, length integer, copyright varchar(255), equals default, /* i. e. , identity */ less than none definitions of MPEG functions go here ); ORDBS 16

Defining Methods for ADT’s Function <name> ( <arguments> ) returns <type> ; • Function types – Internal • Written in SQL. – External • Written in C++, Java, etc. • Only signature appears in definition of the ADT. ORDBS 17

Extended SQL • : = used as assignment operator. • Variable local to the function can be declared by giving its name, preceded by a colon and followed by its type. : s char(50) /* s will be a street */ • Dot operator used to access components of a structure : a. street : = : s /* a, an address */ • Boolean values can be expressed as in where clauses. • begin and end are used to collect several statements into the body of a function. ORDBS 18

Some Functions for the ADT Address. ADT: 1. Constructor Function function Address. ADT (: s char(50), : c char(20)) returns Address. ADT; : a Address. ADT; /* declare local variable */ begin : a : = Address. ADT(); /* use built-in constructor */ : a. street : = : s; : a. city : = : c; return : a; end; Note: We can use the same name, Address. ADT, as the default constructor. ORDBS 19

More Functions for the ADT Address. ADT: 2. equals (addr. Eq )and less than (addr. LT) Functions function addr. Eq (: a 1 Address. ADT, : a 2 Address. ADT) returns boolean; return ( : a 1. street = : a 2. street and : a 1. city = : a 2. city); function addr. LT (: a 1 Address. ADT, : a 2 Address. ADT) returns boolean; return ( (: a 1. city < : a 2. city) or (: a 1. city = : a 2. city and : a 1. street < : a 2. street)); ORDBS 20

Another Function for the ADT Address. ADT: 3. full. Addr, function returning the full address, including zip code, as a single character string function full. Addr (: a Address. ADT) returns char(82); : z char(10); /* for the zip code nnnnn-nnnn*/ begin : z : = find. Zip(: a. street, : a. city ); return (: a. street || ‘ ‘ || : a. city || ‘ ‘ || : z); end; find. Zip, an externally defined function which looks up the zip code for a given city and street. ORDBS 21

External Functions • ADT Definition must include: – Signature. – Specification of programming language in which function is written. declare external find. Zip char(50), char(20) returns char(10) language Java; Arguments passed according to Java conventions. ORDBS 22

Comparison of ODL/OQL and SQL 3 Approaches • Similarities outweigh differences, even though origins different: – ODL/OQL: object-oriented programming languages. – SQL 3: relational database languages. • Have effectively adopted ideas from each other. ORDBS 23

Comparing ODL/OQL vs. SQL 3 actually ODL/OQL vs. SQL 3 row types vs. SQL 3 ADT types 1. 2. 3. 4. 5. 6. ORDBS Programming environment. Role of relations. Encapsulation. Extents of classes. Mutability of objects. Object identity. 24

ODL/OQL vs. SQL 3 row types vs. SQL 3 ADT types 1. Programming environment • OQL – Assumes statements embedded in OO programming language, C++, Java, … • SQL 3 – Objects not objects of surrounding programming language. – External functions in SQL 3 ADT’s provide additional flexibility. ORDBS 25

ODL/OQL vs. SQL 3 row types vs. SQL 3 ADT types 2. Role of relations • OQL – Sets and bags of objects or structures are central. • SQL 3 – Relations are central. – Row types describe relations. – ADT’s describe new types for attributes. • Collections of structures in ODL/OQL similar to Relations in SQL 3. ORDBS 26

ODL/OQL vs. SQL 3 row types vs. SQL 3 ADT types 3. Encapsulation • SQL 3 Row Types – Not encapsulated. – Querying & modifying relations, tuples, and components allowed. • SQL 3 ADT’s – Encapsulated in the usual sense. • ODL Classes – Similar to SQL 3 ADT’s in encapsulation ORDBS 27

ODL/OQL vs. SQL 3 row types vs. SQL 3 ADT types 4. Extents for Classes • OQL – Single extent maintained for each class. – Thus references (relationships in OQL) always refer to some member or members of this extent. • SQL 3 – An extent for a row type allowed, but not required. • If no extent for a row type, then may be problem finding relation containing referenced tuple. ORDBS 28

ODL/OQL vs. SQL 3 row types vs. SQL 3 ADT types 5. Mutability of Objects • Immutable Object: once created, no part of its values can change. – Objects of elementary type, e. g. , integers or strings, are immutable. • Mutable Object: components may change, while object retains its identity. • ODL Classes & SQL 3 Row Types – Define classes of mutable objects. – ODL/OQL: modification occurs through surrounding programming language, not through OQL. • SQL 3 ADT’s – Mutator functions applied to their values result in new value, which may replace old one. • Similar to SQL update statement on integer-valued attribute produces a new integer that might replace the old integer in the tuple. ORDBS 29

ODL/OQL vs. SQL 3 row types vs. SQL 3 ADT types 6. Object Identity • OQL & SQL 3 ADT’s – “Standard” OID: system generated, which cannot be stored or manipulated by user. • SQL 3 Row Type – User can create “primary key”. – Without this relations would usually have two “keys”: • OID • Surrogate value, e. g. , Employee_ID. ORDBS 30