Re: Object Table

Jiraiya Sama wrote:

> Hi,
>
> i am learning the object-relational sql in oracle and i have some questions
> about that.
>
> assume that i have two objects
>
> emp_t (the employee type)
> dept_t (department type)
>
> so, first i do this
>
> create type dept_t
> /
>
> create type emp_t as object (
> id number (3),
> workdept ref dept_t,
> )
> /
>
> create type dept_t as object (
> id number (3),
> mgrno ref emp_t, -- department manager employee no
> admrdept ref dept_t -- ID of administrative dept
> )
> /
>
> so, i'm wondering, because dept_t ref to emp_t and emp_t ref to dept_t, what
> is the correct and the best way to create object table for those two objects
> ?
> is it like this ...
>
> create table ordept of dept_t (
> id primary key
> admrdept references ordept
> ) ;
>
> create table oremp of emp_t (
> id primary key,
> workdept references ordept
> ) ;
>
> alter table ordept
> add constraint fk_mgrno foreign key (mgrno) references oremp ;
>
> thanks a lot.

I like the OO aspects of Oracle but find your example confusing. Perhaps you made a mistake in what you typed as you have:

create type dept_t
/

and a little later typed:

create type dept_t as object (
id number (3),
mgrno ref emp_t, -- department manager employee no admrdept ref dept_t -- ID of administrative dept )
/

Something here obviously doesn't work.

The correct implementation is that employees belong to departments. Therefore you first create departments as an object and then nest that into employee. But not as you seem to want to do it as you only need a single column referential constraint from an employee record to their associated department. For what you are trying to do a relational model is correct.

A better use of an object would be something like this:

CREATE OR REPLACE TYPE address_t AS OBJECT ( address_1 VARCHAR2(40),
address_2 VARCHAR2(40),
city VARCHAR2(30),
state_prov VARCHAR2(2),
postal_code VARCHAR2(10),
country VARCHAR2(25))
/

CREATE TABLE person (
first_name VARCHAR2(30),
last_name VARCHAR2(30),
person_add address_t);

CREATE TABLE organization (

org_name   VARCHAR2(30),
org_type     VARCHAR2(5),
org_add      address_t);

Notice that the 'object' is leveraged to assure that all addresses are stored identically.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)