Download Object-Relational Systems: Understanding the Role of Objects in Relational Databases and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
Ob ject-Relatio nal Systems
Ob ject-oriented ideas enter the relational world. F Keep relation as the fundamental abstraction.
Compare with \ob ject-oriented DBMS," which uses the class as the fundamental abstraction and tacks on relations as one of many typ es.
Motivatio ns
Allow DBMS's to deal with sp eciali zed typ es | maps, signals, images, etc. | with their own sp ecialize d metho ds.
Supp orts sp ecializ ed metho ds even on conventional relational data.
Supp orts structure more complex than \ at les."
Plan
- Basic ideas from SQL standards do cuments.
- Use Oracle 8i/9i notation when similar.
- Intro duce some new concepts from Oracle.
F On-line do cument: or-objects.html.
De ning UDT's | Example and Oracle Syntax
CREATE TYPE BarType AS OBJECT ( name CHAR(20) UNIQUE, addr CHAR(20) ); / CREATE TYPE BeerType AS OBJECT ( name CHAR(20) UNIQUE, manf CHAR(20) ); /
CREATE TYPE MenuType AS OBJECT ( bar REF BarType, beer REF BeerType, price FLOAT ); /
Notes
In Oracle, typ e de nitions must b e followed by a slash (/) in order to get them to compile.
The SQL standard is similar, but \OBJECT" is not used after \AS."
Values of User-De ned Typ es | Oracle Approach
Each UDT has a typ e constructor of the same name.
Values of that typ e are the values of its elds wrapp ed in the constructor.
Example
SELECT * FROM Bars;
pro duces values such as
BarType('Joe''s Bar', 'Maple St.')
Accessing Fields of an Ob ject | Oracle Approach
The dot op erator works as exp ected.
Thus, if we want the bar name and address without the constructor: SELECT bb.name, bb.addr FROM Bars bb;
The alias bb is not technicall y necessary, but there are other places where we must use an alias in order to access ob jects, and it is a go o d habit to use an alias always.
SQL standard: Same idea, but the attribute is treated as a generator metho d, with parentheses, e.g., bb.name().
Typ es for Columns
A UDT can also b e the typ e of a column.
Example | Oracle Syntax
Let's create an address typ e for use with bars and drinkers.
CREATE TYPE AddrType AS OBJECT ( street CHAR(30), city CHAR(20), zip INT );
We can then create a table of drinkers that includes their name, address, and favorite b eer.
The b eer is included as a b eer ob ject, which \unnormalizes" the relation but is legal. CREATE TABLE Drinker ( name CHAR(30), addr AddrType, favBeer BeerType );
Need to Use Aliases
If you access an attribute whose typ e is an ob ject typ e, you must use an alias for the relation. E.g.,
SELECT favBeer.name FROM Drinker;
will not work in Oracle; neither will:
SELECT Drinker.favBeer.n ame FROM Drinker;
You have to say:
SELECT dd.favBeer.name FROM Drinker dd;
Dereferencing in SQL
A! B = the B attribute of the ob ject referred to by reference A.
Example
Find the b eers served by Jo e.
SELECT beer -> name FROM Sells WHERE bar -> name = 'Joe''s Bar';
Dereferencing in Oracle
Dereferencing automatic, using dot op erator.
Example
Same query in Oracle syntax:
SELECT ss.beer.name FROM Sells ss WHERE ss.bar.name = 'Joe''s Bar';
Metho ds
Real reason ob ject-relational isn't just nested structures in relations.
We'll follow Oracle syntax.
Declared in a CREATE TYPE statement, de ned in a CREATE TYPE BODY statement.
Metho ds are functions or pro cedures; in Oracle they are de ned like any PL/SQL pro cedure or function. F But, there is a sp ecial tuple variable SELF that refers to that ob ject to which the metho d is applied.
Example
Let's add a metho d priceInYen to the MenuType and thus to the Sells relation.
CREATE TYPE MenuType AS OBJECT ( bar REF BarType, beer REF BeerType, price FLOAT, MEMBER FUNCTION priceInYen( rate IN FLOAT) RETURN FLOAT, PRAGMA RESTRICT REFERENCES(priceI nYen, WNDS) ); / CREATE TYPE BODY MenuType AS MEMBER FUNCTION priceInYen(rate FLOAT) RETURN FLOAT IS BEGIN RETURN rate * SELF.price; END; END; / CREATE TABLE Sells OF MenuType;
Example of Metho d Use
Follow a designator for the ob ject to which you want to apply the metho d by a dot, the name of the metho d, and argument(s).
SELECT ss.beer.name, ss.priceInYen(120.0) FROM Sells ss WHERE ss.bar.name = 'Joe''s Bar';
Built-In Comparison Functions (SQL)
We can de ne for each UDT two functions EQUAL and LESSTHAN.
Allow values of this UDT to participate in WHERE clauses involving =, <=, etc. and in ORDER-BY sorting.
Order Metho ds in Oracle
We can declare one metho d for a typ e to b e an ORDER metho d.
De nition of this metho d must return <0, 0,
0, if \self " is less than, equal to, or greater than the argument ob ject.
Also used in comparisons for WHERE and ORDER BY.
