1 advanced database topics copyright © ellis cohen 2002-2005 objects and relational databases these...
TRANSCRIPT
1
Advanced Database Topics
Copyright © Ellis Cohen 2002-2005
Objects and Relational Databases
These slides are licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License.
For more information on how you may use them, please see http://www.openlineconsult.com/db
© Ellis Cohen 2002-2005 2
Topics
Object-Relational Mapping
OR Mapping Approaches & Issues
Object-Relational Databases
Referenced Objects & Navigation
Contained Objects
Nested Tables
Nested Collections
© Ellis Cohen 2002-2005 3
Drivers for OO & RDB Integration
• Persistent Object Storage– Need for a way to easily save and
restore computation state of programs built via OOPL's using standard RDBs
• OO Programming for RDB Data– Treat rows of a table (plus asssociated
data in other tables) like an object for programmatic access & modification
• OO Database Programming– Brings benefits of OO approach to RDB's
© Ellis Cohen 2002-2005 5
OR Mapping Motivations
• Want to persist OO data so it is queryable (e.g. w OQL), but using the standard RDB your organization is already using.
• Want to write OO programs that use and manipulate data stored in an RDB.– OO programmers did this anyway before
advent of technologies like JDO.
– Painful enough that the correspondence between relations & objects was referred to as an impedance mismatch.
© Ellis Cohen 2002-2005 6
Object Model & Actual RDB
30 SALES
10 ACCOUNTING
50 SUPPORT
deptno dname
Deptsempno name addr deptno
7499 ALLEN ... 30
7654 MARTIN … 30
7844 TURNER … 30
7212 LAVICH …
7698 BLAKE … 10
7986 STERN … 10
Emps
empno: 7499name: ALLENaddr: …dept:
empno: 7654name: MARTINaddr: …dept:
deptno: 30dname: SALES
© Ellis Cohen 2002-2005 7
OR Mapping Approaches
Programmatic Approach:Write the code to transform the object
• when loading it into the cache, and • when persisting it to the RDB,
Declarative/Automatic Approach:
Provide a description of how the OOPL & RDB models are mapped to one another.
The mapping layer uses the description to automatically transform the representations on object loading and persistence!
The mapping layer translates OO queries (e.g. OQL, HQL, JDOQL, OPath) automatically into SQL
The representation of an object in an OOPL may be quite different from its representation
when stored in an RDB
© Ellis Cohen 2002-2005 8
Current Systems (1)
JDO– Java standard– Uses transparent persistence– Many commercial implementations,
including Kodo & ObjectBridge– Uses JDOQL, a limited Java-based query
language (improved in 2.0)– JDO 2.0 adds significant support to
control the OR mapping
Castor– Java-based system using JDO API– Will use OQL as its query language
© Ellis Cohen 2002-2005 9
Current Systems (2)
Hibernate– Most popular Java-based system– Uses explicit persistence– Uses HQL, a limited query language
derived from OQL
ObjectSpaces– Microsoft's system for .NET– Uses transparent persistence– Use OPath query language, loosely
derived from XML's XPath
© Ellis Cohen 2002-2005 10
Mapping OQL to SQL
SELECT e.ename, e.dept.dnameFROM emps e
in OQL is mapped to the SQL:
SELECT e.ename,(SELECT d.dname FROM Depts d WHERE e.deptno = d.deptno)FROM Emps e
A good optimizer will treat this as equivalent to
SELECT ename, dname FROM Emps NATURAL JOIN Depts
© Ellis Cohen 2002-2005 11
Early OPathFilters placed in square brackets used instead of WHERE clauses
Employee[empno = 3417]• The employee whose empno is 3417
Employee[job = 'ANALYST']• The employees who are analysts
Navigation represented by a dot; can be applied to a collection
Employee[dept.dname = 'RESEARCH']Dept[dname = 'RESEARCH'].empls
• Employees in the research departmentEmployee[job = 'CLERK'].dept.dnameDept[empls.job = 'CLERK']
• Departments that have clerks
Early versions of OPath were very closely related to XML's XPath query language.
OPath has since evolved away from this model
© Ellis Cohen 2002-2005 12
Mapping Early OPath to SQL
Employee[job = 'CLERK'].dept.dname
in Early OPath is mapped to the SQL:
SELECT DISTINCT (SELECT d.dname FROM Depts d WHERE e.deptno = d.deptno)FROM Emps eWHERE job = 'CLERK'
A decent optimizer will treat this as equivalent to
SELECT DISTINCT dname FROM Emps NATURAL JOIN Depts WHERE job = 'CLERK'
© Ellis Cohen 2002-2005 14
OOPL RDB Mapping Approaches (1)
OOPL model(ODL / Java / C#) RDB Model
OOPL RDB Map
OOPL model(ODL / Java / C#)
RDB Model
RDB OOPL Map
Generate
Generate
Start with
Start with
Define
Define
© Ellis Cohen 2002-2005 15
OOPL RDB Mapping Approaches (2)
OOPL model(ODL / Java / C#) RDB Model
OOPL / RDB Map
OOPL model(ODL / Java / C#) RDB Model
ER RDB & OOPL
Map
ER Model
Generate Generate
Just Define
Start withStart with
Start with
Define
© Ellis Cohen 2002-2005 16
Mapping RDB OOPLGiven a legacy RDB
Design the corresponding OOPL.
•How are FK's treated– retain and/or convert to relationships– one or two-way relationships
•Specify field types (when not obvious)•Mapping enums•Generate OIDs
– Unique to class• Primary Key
– Unique to application (allows application to use OIDs to retrieve objects)
• TableName + PrimaryKey• ROWIDs
– Globally unique (OIDs can be used to retrieve objects across a global network)
• ip address + DB instance + app name + unique-to-application
© Ellis Cohen 2002-2005 17
Example RDB OOPL Spec<mappings OID="table" …>
--getOid will return tableName || '|' || primaryKey <class name="Employee" keyclass="Emps">
<usetable name="Emps">-- build Employee fields from Emps columns<field name="empno"/><field name="ename"/><field name="salary" column="sal"/>
-- Emps' sal column should become Employee's salary field<field name="job" type="int">
<mapval from="MANAGER" to="1"/>-- Emps job='MANAGER' Employee job=1
<mapval from="CLERK" to="2"/>…
</field><field name="mgr" map="ref"/>
-- Emps mgr is a foreign key for an Emps-- so in Emps, have it hold a reference to an Employee
<field name="deptno" field = "dept" map="FK"/></usetable>
</class>
© Ellis Cohen 2002-2005 18
Mapping OOPL/ER RDB
Given a new OO or ER modelDesign the corresponding RDB.
•Determining Primary Keys– Object class field– Automatically generated surrogate keys
• unique to class, database, GUIDs
•Specify column types & constraints•Generate OIDs
– Same as for RDB ER– Related to how PK's are determined
•Converting References– FK to primary key or other unique columns
•Mapping Enums
© Ellis Cohen 2002-2005 19
Example OOPL RDB SpecExample OOPL RDB Mapping (non-standard)
<mappings PK="GUID" OID="PK" …>--all tables will get globally unique surrogate PK's, which will be returned as OIDs
<table name="Emps"><useclass name="Employee">
-- build Emps column from Employee fields, with empno as PK<column name="empno" unique="T" len="5"/><column name="ename" len="20"/><column name="sal" field="salary" len="8.2"/>
-- Employee's salary field should become Emps' sal column<column name="job" type="varchar" len="12">
<mapval from="1" to="MANAGER"/>-- Employee job=1 Emps job='MANAGER'
<mapval from="2" to="CLERK"/>…
</column><column name="mgr" map="FK"/>
-- Employee's mgr is a ptr to an Employee-- so in Emps, have mgr instead hold a foreign key for an Emps
<column name="deptno" field = "dept" map="FK"/></useclass>
</table>
© Ellis Cohen 2002-2005 20
Using GUIDs as PKs
Initial Design: OOPL or ODLGoal: Design an RDB Schema which
associates OIDs with each tuple
Emps
304729… 20 Accounting
8B6B2A.. 30 Research
516123.. 50 IT
deptoid deptno deptnam Depts
empoid empno deptoid
A134E2… 20693 … 304729…
C0E11A… 19419 304729…
D44472… 82331 8B6B2A..
7C91EE… 30492 304729…
437A23… 93200 516123..
Includes deptoid instead of deptno
What are pros and cons of using OIDs as PK/FKs?
© Ellis Cohen 2002-2005 21
Object-Based Integration
OODB
RDB
Files
CORBA Service
Client
Issue OQL Query
OQL
CORBA R
equest
Read/Parse
SQL/SQLX
Object Integration Server
• Develop Object Model for each data source
• Maps OQL to distributed native queries/request
• Maps combined results to objects
MDDBMDX
© Ellis Cohen 2002-2005 22
Integrated Relational Querying
Use an RDB, View both as OO Data– User still sees OO data as OO. Its mapping to the
RDB is hidden– User sees an OO representation of the relational
data as well, based on a hidden mapping– User queries all data with OQL, automatically
mapped to SQL
Use an RDB, View both as Relational Data– User sees relational data as is– User sees OO data in terms of its relational
representation, based on a public mapping – User uses SQL to query all data
Use an Object-Relational Database– RDB w user-defined object data types + references– Incorporates navigation into SQL
Suppose we have both persistent OO and Relational data. How can we arrange to do queries joining both of
them?
© Ellis Cohen 2002-2005 24
ORDBs
Object-Relational Databases integrate an Object Model with the Relational Model.
• Users can extend the built-in data types (number, date, etc.) with arbitrary object types.
• Columns can hold references to objects.
• SQL is extended to support field access and navigation just as in OQL.
• Tables (containing objects or references to object) are the primary (or sometimes only) collection type.
• Moreover, objects are not stored independently as in OODBs; they must be placed in tables.
© Ellis Cohen 2002-2005 25
Rows as Objects/Entities
empno: 7654name: MARTINsal: 1250comm: 1400
an Employee Object
It can be useful to think of each row as an object or entity (i.e. an instance of an entity class) and the table as a collection of these objects
The columns of the table correspond to the instance variables for each object
It can be useful to think of each row as an object or entity (i.e. an instance of an entity class) and the table as a collection of these objects
The columns of the table correspond to the instance variables for each object
empno name sal comm
Emps
7499 ALLEN 1600 300
7654 MARTIN 1250 1400
7698 BLAKE 2850
7839 KING 5000
7844 TURNER 1500 0
7986 STERN 1500
© Ellis Cohen 2002-2005 26
Object TablesObject-Relational Databases actually allow rows to be objects (known as row objects), and allow cells to contain references to these row objects.– Referential Integrity constraints can be
replaced by actual references– Joins can be replaced by Navigation– Instead of automatically associating an
extent with an object, an object is explicitly stored in some table (multiple tables can hold the same type of object)
– Every row object has an OID (invisibly provided by the database)
© Ellis Cohen 2002-2005 27
Object Types & Tables
CREATE TYPE DeptObj AS OBJECT ( deptno number(3), dname varchar(20), loc varchar(15),… )
CREATE TABLE DeptTable OF DeptObj (deptno PRIMARY KEY);
DeptTable can be viewed as either • a table containing 3 columns: deptno, dname, loc• a table containing a single column: a DeptObj
* SQL Syntax loosely based on Oracle
© Ellis Cohen 2002-2005 28
Returning Column Valuesfrom Object Tables
SELECT d.dname FROM DeptTable dWHERE d.loc = 'HOUSTON'– returns the department names
SELECT d.* FROM DeptTable dWHERE d.loc = 'HOUSTON'– returns a row (just as from an ordinary table)
30 SALES NY
10 ACCOUNTING LA
50 SUPPORT HOUSTON
99 PARTY MIAMI
© Ellis Cohen 2002-2005 29
Returning Column Values vs. ObjectsSELECT d.* FROM DeptTable d
WHERE d.loc = 'HOUSTON'– returns rows of values (just as from an ordinary table)
30 SALES NY
10 ACCOUNTING LA
50 SUPPORT HOUSTON
99 PARTY MIAMI
SELECT d FROM DeptTable dWHERE d.loc = 'HOUSTON'– returns entire DeptObj objects!
30 SALES NY
10 ACCOUNTING LA
50 SUPPORT HOUSTON
99 PARTY MIAMI
A DeptObj Object
© Ellis Cohen 2002-2005 30
Row Objects Have OIDs
27 622 Auditing CHICAGO …
deptno dname loc …
A row object's OID can be represented as a ROWID + a generation #, which also
appears in the row object's header
AAAGDxAABAAAH9EAAD27
© Ellis Cohen 2002-2005 31
Object ViewsCREATE TABLE Depts (
deptno number( 5 ),dname varchar( 30 ),loc varchar(30) )
Given an existing table, create an object view of it,but requires OID for each tuple
CREATE TYPE DeptObj AS OBJECT (deptno number( 5 ),dname varchar( 30 ),loc varchar(30) )
CREATE VIEW DeptView OF DeptObjWITH OBJECT OID (deptno) ASSELECT * FROM Depts
Note: No objects really exist or are created, so even though DeptView looks like a table of objects, the OID is just (id of Depts) + deptno, which is better than the ROWID (which can be reused) if deptno's are not changed or reused.
© Ellis Cohen 2002-2005 32
Type InheritanceCREATE TYPE EmpObj UNDER PersonObj (
empno number(4),…… )
CREATE TABLE PersonTable OF PersonObj
PersonTable can hold plain PersonObjs
as well as EmpObjs & other subclasses
ssno name addr
202406129 ALLEN 20 B St, … empno sal
… JONES … 1427 2500
… CHU … University
… SONI … Myanard Wales Univ
… HALMA …
… BONI … empno sal
… STERN … 6041 3300
EmpObj
GradObj
EmpObj
PersonObj
© Ellis Cohen 2002-2005 33
Type Checking & Casting
Suppose we want to get the empno's of all the employees in PersonTable
SELECT p.empnoFROM PersonTable pWHERE p IS OF TYPE EmpObj
Almost works, except we also need to claim that p is an EmpObj rather than just a PersonObj before getting its empno attribute
SELECT TREAT(p AS EmpObj).empnoFROM PersonTable pWHERE p IS OF TYPE EmpObj
© Ellis Cohen 2002-2005 35
Object References
A column can contain a reference to a row object
30 SALES …
10 ACCOUNTING …
50 SUPPORT …
99 PARTY …
deptno dname loc
DeptTableempno name … dept
7499 ALLEN ...
7654 MARTIN …
7698 BLAKE …
7839 KING …
7844 TURNER …
7986 STERN …
EmpTable
© Ellis Cohen 2002-2005 36
Scoped ReferencesCREATE TYPE EmpObj AS OBJECT (
empno number(4), name varchar(30), …dept REF DeptObj SCOPE DeptTable )
– replaces the referential integrity constraint deptno number(3) REFERENCES DeptTable
CREATE TABLE EmpTable OF EmpObj(PRIMARY KEY empno)
REF(obj) creates a reference to the specified object, which can then be returned in a result set
UPDATE EmpTableSET dept = (SELECT REF(d) FROM DeptTable d WHERE d.dname = 'ACCOUNTING')WHERE name = 'SMITH'
This attribute holds a reference to a
DeptObj in DeptTable
Change SMITH's dept to ACCOUNTING
© Ellis Cohen 2002-2005 37
Comparing Queries
In a vanilla RDB, to find the department name in which JONES works, you might do
SELECT dnameFROM Emps NATURAL JOIN DeptsWHERE ename = 'JONES'
In an ORDB using EmpObj (which contains a REF DeptObj)
how would you get the dept name in which JONES works?
(Assume only one employee named JONES)
© Ellis Cohen 2002-2005 38
ORDBs Use Direct Navigation
CREATE TYPE EmpObj AS OBJECT ( empno number(4),name varchar(30),…,dept REF DeptObj SCOPE DeptObj
)
CREATE TABLE EmpTable OF EmpObj(PRIMARY KEY empno)
SELECT e. name, e.dept.dnameFROM EmpTable eWHERE ename = 'JONES'
List the names of employees whose departments are located in Denver.
© Ellis Cohen 2002-2005 39
Simple Query Solution
List the names of employees whose departments are located in Denver.
SELECT e.nameFROM EmpTable eWHERE e.dept.loc = 'DENVER'
© Ellis Cohen 2002-2005 40
Tables vs ExtentsObject types do not have a single
associated extentThere can be multiple tables which hold
objects of the same type
CREATE TABLE DeptTable1 OF DeptObj (deptno PRIMARY KEY);
CREATE TABLE DeptTable2 OF DeptObj (deptno PRIMARY KEY);
CREATE TABLE DeptTable3 OF DeptObj (deptno PRIMARY KEY);
The user chooses where to store an objectINSERT INTO DeptTable2
VALUES ( 50, 'SUPPORT', 'HOUSTON' );
Less signficant issue for DB's which support partitioned tables
© Ellis Cohen 2002-2005 41
Unscoped ReferencesCREATE TYPE EmpObj AS OBJECT (
empno number(4),name varchar(30),…dept REF DeptObj SCOPE DeptTable
)
CREATE TYPE EmpObj AS OBJECT (empno number(4),name varchar(30),…dept REF DeptObj
)
This DeptObj could be located in any table.
Must use a full-size OID to represent the reference
This DeptObj must be located in DeptTable.Could use a table-relative mini-OID
to represent the reference
© Ellis Cohen 2002-2005 42
Dereferencing Unscoped References
Even if departments are spread across multiple tables, an unscoped reference will find the department(by its OID!)
SELECT e.dept.dnameFROM EmpTable eWHERE e.ename = 'JONES'
Suppose in a vanilla RDB, depts were spread across Depts1, Depts2 & Depts3.
How would you get the name of the department in which JONES works?
© Ellis Cohen 2002-2005 43
Unions for Multi-Table NavigationSuppose in a vanilla RDB, depts were spread across
Depts1, Depts2 & Depts3. How would you get the name of the department in which JONES works?
SELECT dnameFROM Emps NATURAL JOIN Depts1WHERE ename = 'JONES'
UNIONSELECT dname
FROM Emps NATURAL JOIN Depts2WHERE ename = 'JONES'
UNIONSELECT dname
FROM Emps NATURAL JOIN Depts3WHERE ename = 'JONES';
In practice, tables are split for many reasonsdifferent divisions; pending vs active vs inactive, etc.
Using an Object Model with unscoped references allows simple navigation to replace unioned joins
© Ellis Cohen 2002-2005 44
DELETE & Dangling ReferencesDELETE deletes objects from tables of objects
Scoped References:provide referential integrity checking:– Can't delete referenced department (default)– ON DELETE SET NULL– ON DELETE CASCADE
Unscoped References:no referential integrity checking
– If a DeptObj is deleted,a REF DeptObj may hold an invalid OID (a dangling reference)
– Use IS DANGLING to check:
SELECT DISTINCT e.dept.dnameFROM EmpTable eWHERE e.job = 'SALESMAN' AND e.dept IS NOT DANGLING
© Ellis Cohen 2002-2005 45
Explicit DereferencingIf e denotes an EmpObj
then e.namedenotes the name attribute of that EmpObj
If re denotes a REF EmpObjthen DEREF(re)
denotes the EmpObj it references
and either DEREF(re).nameor re.namedenotes the name attribute of the EmpObj it references
© Ellis Cohen 2002-2005 47
ContainmentORDB's are non-1NF:
Cells may contain objects as well as primitive types of values. These are also called cell objects or column objects.
CREATE TYPE Address AS OBJECT( street varchar(100), city varchar(40), state char(2), zip char(5))
CREATE TYPE PersonObj AS OBJECT(name varchar(30),addr Address
)CREATE TABLE PersonTable OF PersonObj
© Ellis Cohen 2002-2005 48
Cell Objects
SMITH
JONES
GOMEZ
SONI
name addr
… … MA 02139
… … IL 51320
… … CA 93710
… … MA 02445
p p.name p.addr p.addr.state
© Ellis Cohen 2002-2005 49
Creating Cell Objects
INSERT INTO PersonTableVALUES ( 'STERN', Address(
'11 Global Way', 'Hereville', MS, 39452 ) )
Cell Objects do not have OIDsREFs to Cell Objects are not allowed
© Ellis Cohen 2002-2005 50
Object Type Methods
CREATE TYPE AddrObj AS OBJECT (street varchar(100),city varchar(40),state char(2),zip char(5),
MEMBER FUNCTION toString RETURN varchar …
)
CREATE TYPE PersonObj AS OBJECT(name varchar(30),addr Address
)CREATE TABLE PersonTable OF PersonObj
SELECT name, addr.toString()FROM PersonTable
© Ellis Cohen 2002-2005 51
Ordering by Object TypesSELECT name FROM PersonTable
ORDER BY addr
How is an order defined on addresses?Oracle (e.g.) allows a single MAP member function
to be defined for AddrObjs. It returns a primitive type,
used as the basis of ordering
MAP MEMBER FUNCTION getZip RETURN char(5)IS RETURN zip;END
Oracle instead allows the ORDER member function;it compare two objects, returning-1 if the first is smaller 0 if they are equal 1 if the first is larger
Order AddrObjs based on their
zip values
© Ellis Cohen 2002-2005 52
Selecting with Member Functions
CREATE TABLE Positions( posid int, – id of the position coord Coordinate, – location of the position … )
SELECT posid FROM PositionsWHERE coord.inRegion( 0, 0, 100, 100) > 0
CREATE TYPE Coordinate AS OBJECT x number, y number, MEMBER FUNCTION inRegion RETURN INT … – returns 1 if coordinate is within the
specified region …)
© Ellis Cohen 2002-2005 53
Type-Specific Indexing
ORDB databases generally support type-specific indices
InRegion queries can be implemented as the intersection of x and y range queries based on B-tree indices, however
Coordinate-specific indices such as quadtree and R-tree indices select inRegion tuples much more quickly
© Ellis Cohen 2002-2005 55
Nested Tables
23 HR NY
31 SALES LA
… … … …
deptno dname loc empls
3049 SMITH … …
6471 JONES … …
8187 GOMEZ … …
2069 SONI … …
8142 EMEBI … …
Purist approaches to ORDB's (e.g. Date) only allows Nested Tables (and other Contained Objects and Collections), but do NOT allow Row Objects and References.
© Ellis Cohen 2002-2005 56
Nested Tables of ObjectsCREATE TYPE DeptEmpsObj As OBJECT (
deptno number(4),dname varchar(30),loc varchar(30),empls TABLE OF EmpObj
)CREATE TABLE DeptEmpsTable OF DeptEmpsObj
PRIMARY KEY deptno
SELECT dname FROM DeptEmpsTable dWHERE EXISTS( SELECT * FROM d.empls e WHERE e.job = 'SALESMAN')
SELECT e.name FROMDeptEmpsTable d, d.empls eWHERE d.loc = 'BOSTON'
List names of depts which have salesmen
List employees of depts located in Boston
Correlated table
expressions allowed on
nested tables
Now, of those employees,
determine how many have each
kind of job
© Ellis Cohen 2002-2005 57
Correlation Grouping Solution
Of the employees in departments located in Boston, determine how many have each kind of job.
SELECT e.job, count(*)FROM DeptEmpsTable d, d.empls eWHERE d.loc = 'BOSTON'GROUP BY e.job
© Ellis Cohen 2002-2005 58
Nested Update Problem
Nested Tables can be very efficient for some operations
SELECT e.nameFROM DeptEmpsTable d, d.emplsWHERE d.loc = 'NY' AND e.job = 'ANALYST'
How do you add 100 to the salary of these employees?You must use views!
© Ellis Cohen 2002-2005 59
Nested Tables & Unnested Views
23 HR NY
31 SALES LA
… … … …
deptno dname loc empls
3049 SMITH … …
6471 JONES … …
8187 GOMEZ … …
2069 SONI … …
8142 EMEBI … …
23 HR NY 3049 SMITH … …
23 HR NY 6471 JONES … …
23 HR NY 8187 GOMEZ … …
31 SALES LA 2069 SONI … …
… … … … …
deptno dname loc DeptEmpView
DeptEmpsTable
© Ellis Cohen 2002-2005 60
Views for UpdatingCREATE VIEW DeptEmpView AS
SELECT d.deptno, d.name, d.loc, e.*FROM DeptEmpsTable d, d.empls e
UPDATE DeptEmpViewSET sal = sal + 100 WHERE loc = 'NY' AND job = 'ANALYST'
Can we update deptno?
UPDATE DeptEmpView SET deptno = 31 WHERE empno = 6471
Updating deptno means moving the employee from one nested table to another. Might be supported in an ORDB
system, but perhaps it would be better for Emps & Depts to be the actual tables, and to create a nested table view
© Ellis Cohen 2002-2005 61
Result Sets with Nested TablesGiven Depts (only dept info) and Emps (only
employee info, except that Emps.deptno references Depts)
Generate a result set: each tuple corresponds to each of the departments – it contains the name of the department, and a table of the names of each employee in the department.
SELECT d.dname, TABLE( SELECT ename FROM Emps e WHERE e.deptno = d.deptno )FROM Depts d
Given Depts and Emps, create a view that corresponds to DeptEmpsTable (with its nested table of employees)
© Ellis Cohen 2002-2005 62
Creating Nested Table Views
CREATE VIEW DeptEmpsTableView OF DeptObjWITH OBJECT OID (deptno) ASSELECT d.*, TABLE( SELECT empno, … FROM Emps e WHERE d.deptno = e.deptno) AS emplsFROM Depts d
Oracle also allows definition of the original DeptEmpsTable, with the combined nested tables of all the Employees
implemented as a single query-able table
Given Depts and Emps, create a view that corresponds to DeptEmpsTable (with its nested table of employees)
© Ellis Cohen 2002-2005 63
Nested Result Sets from References
Generate a result set: each tuple contains a department name and a table of the names of the dept employees
30 SALES …
10 ACCOUNTING …
50 SUPPORT …
99 PARTY …
deptno dname loc
DeptTableempno name … dept
7499 ALLEN ...
7654 MARTIN …
7698 BLAKE …
7839 KING …
7844 TURNER …
7986 STERN …
EmpTable
© Ellis Cohen 2002-2005 64
Result Set Solution
SELECT d.dname, TABLE( SELECT name FROM EmpTable e WHERE e.dept = REF(d) )FROM DeptTable d
Generate a result set: each tuple contains a department name and a table of the names of the dept employees
Generate the same result set, but givenDeptEmpsTable (each dept has empls, a nested table of employees)
© Ellis Cohen 2002-2005 65
Related Nested Result Set
SELECT d.dname, TABLE( SELECT name FROM d.empls )FROM DeptEmpsTable d
Notice that SELECT d.dname, d.empls.name
FROM DeptEmpsTable dis incorrect!
Generate a result set: each tuple contains a department name and a table of the names of the dept employees, given DeptEmpsTable
© Ellis Cohen 2002-2005 66
Cross Referencing
30 SALES
10 ACCOUNTING
50 SUPPORT
deptno dname empls
DeptTableempno name … dept
7499 ALLEN ...
7654 MARTIN …
7698 BLAKE …
7839 KING …
7844 TURNER …
7986 STERN …
EmpTable
Suppose we keep the employees in a separate table
And every department contains a table containing just references to the employees in that department
© Ellis Cohen 2002-2005 67
Nested Tables of RefsCREATE TYPE RefEmp AS REF EmpObj SCOPE EmpTable;
CREATE TYPE DeptObj AS OBJECT (deptno number(4),dname varchar(30),loc varchar(30),empls TABLE OF RefEmp
)CREATE TABLE DeptTable OF DeptObj
PRIMARY KEY deptno
SELECT dname FROM DeptTable dWHERE EXISTS( SELECT * FROM d.empls e WHERE e.job = 'SALESMAN')
SELECT e.name FROMDeptTable d, d.empls eWHERE d.loc = 'Boston'
© Ellis Cohen 2002-2005 68
ODL Modelclass Employee (extent emps) {
attribute int empno;attribute string name;attribute string job;attribute Address addr;relationship Dept dept inverse Dept::empls;
}
class Dept (extent depts) {attribute int deptno;attribute string dname;relationship Set<Employee> empls
inverse Employee::dept;
The ODL model for Employee and Dept is essentially equivalent to the previous ORDB model
• emps and depts correspond to EmpTable and DeptTable• Set<Employee> empls corresponds to a nested tables of refs
© Ellis Cohen 2002-2005 69
Collections and Relationships
deptsemps
dept1
dept2
dept3
emp1
emp3
emp5
emp4
emp2
empls
dept
© Ellis Cohen 2002-2005 71
Collection Types
ORDB's may support other collection types
SET OF TypBAG OF TypLIST OF TypARRAY[n] OF TypDICTIONARY[ndxtyp] OF Typ
They can– Be placed in cells
These are useful because– there are specialized functions & operations to
add/remove/get/set/test values
Oracle supports VARRAYS, resizeable
arrays with a fixed maximum size
© Ellis Cohen 2002-2005 72
Collections & MembershipIt may be possible to test collections for
membership
CREATE TYPE PersonObj AS OBJECT (name varchar(30),kidnames SET OF varchar(30)
)CREATE TABLE PersonTable OF PersonObj
List the names of the people who have a kid named 'Yael'
SELECT name FROM PersonTablewhere ('Yael' IN kidnames)
© Ellis Cohen 2002-2005 73
Collection OperationsIt may be possible to use specialized
collection operations
CREATE TYPE PersonObj AS OBJECT (name varchar(30),autos LIST OF AutoObj
)CREATE TABLE PersonTable OF PersonObj
List each person and the model of their primary car
SELECT name, autos[1].model FROM PersonTable
© Ellis Cohen 2002-2005 74
Querying CollectionsGenerally, collections cannot be queriedIt may be possible to convert collections to tables
in order to query them
CREATE TYPE PersonObj AS OBJECT (name varchar(30),autos LIST OF AutoObj
)CREATE TABLE PersonTable OF PersonObj
List the model, year & owner of all autos made before 1950.Order by model and year
SELECT a.model, a.year, p.name FROM PersonTable p, TABLE(p.autos) aWHERE a.year < 1950ORDER BY a.model, a.year
© Ellis Cohen 2002-2005 75
Collection ConversionsIt may be possible to convert
tables or collections to other collectionsso collection-specific operations may be used
CREATE TYPE PersonObj AS OBJECT (name varchar(30),autos TABLE OF AutoObj
)
SELECT name, LIST(autos ORDER BY position DESC)[1].model FROM PersonTable
List each person and the model of their primary car