Re: how to update an inherited subtype object's attributes in oracle 9i

Originally posted by Bo Wang
> hi, there:
> I have two tables, UNIT_TAB and PERSON_TAB, unit_tab will keep all
> the information of university, department and faculty, university_t,
> department_t and faculty_t are subtype of unit_t; person_tab will
> store all the info about president, professor and staff, and
> president_t, professor_t and staff_t are subtype of person_t.
> when I try to populate the database, I find I need to update the
> attributes of the objects of the subtype, but I cannot find a way to
> update them, since all the attributes seem to be hidden from the
> supertype object. I paste part of the codes I have now, hopefully
> someone can give me a clue how to update it.
> for example, if I want to update the unit_v() or person_v() of
> unit_tab, which command can do this.
> BTW, when I use select * from unit_tab, I could only see the
> attributes of supertype, is a way I can see all the attibutes of the
> subtype, so i can check if I have succefully inserted all the
> infomations.
>
>
> thanks a lot!
>
> Bo
>
> ----------------------------------------------------------------------
> --------
>
> drop table UNIT_TAB;
> drop table PERSON_TAB;
>
> drop type person_t force;
> drop type person_v force;
> drop type president_t force;
> drop type staff_t force;
> drop type professor_t force;
>
> drop type unit_t force;
> drop type unit_v force;
> drop type department_t force;
> drop type faculty_t force;
> drop type university_t force;
>
> create type person_t as object (
> name varchar2(30)
> ) not final
> /
>
> create or replace type unit_t as object (
> name varchar2(30)
> ) not final
> /
>
> create type person_v as varray(40) of ref person_t
> /
>
> create type unit_v as varray(40) of ref unit_t
> /
>
> create type university_t under unit_t (
> sub_units unit_v,
> president ref person_t,
> univ_staffs person_v
> )
> /
>
> create type faculty_t under unit_t (
> faculty_univ ref unit_t,
> faculty_depts unit_v,
> faculty_profs person_v,
> faculty_staffs person_v
> )
> /
>
> create type department_t under unit_t (
> dept_unit ref unit_t,
> dept_profs person_v,
> dept_staffs person_v
> )
> /
>
> create type president_t under person_t(
> title varchar2(30),
> university ref unit_t
> )
> /
>
> create type staff_t under person_t(
> staff_rank varchar2(30),
> staff_unit ref unit_t
> )
> /
>
> create type professor_t under person_t(
> research varchar2(30),
> prof_unit ref unit_t
> )
> /
>
>
> create table UNIT_TAB of unit_t;
> --nested table unit_nt store as unit_nt_tab
> --nested table person_nt store as person_nt_tab;
>
> create table PERSON_TAB of person_t;
> --nested table unit_nt store as unit_nt_tab
> --nested table person_nt store as person_nt_tab;
>
>
> insert into unit_tab values( university_t('Queens University',
> unit_v(), null, person_v() ) );
> --insert into unit_tab values( unit_t('Queens University') );
>
> insert into person_tab values( president_t('Edward', 'President',
> ( select ref(u) from unit_tab u where u.name='Queens
> University' )
> ) );
>
> --update unit_tab
> --set values = ( university_t('Queens University', unit_v(), (select
> ref(p) from person_tab where p.name='Edward' ), person() ))
> --where name = 'Queens University';
>
>
> select * from unit_tab;
>
> select * from person_tab;

Presumably this is a college assignment, because no real database should be designed this way. This is an exercise in using Oracle OO features "because they are there", and thereby turning a fairly simple database of about 6-8 tables into a lot of OO spaghetti. Consider these points:

1. Universities can have a maximum of 40 "sub units" and 40 staff, because of the use of VARRAYs instead of a 1:many relationship between tables.
2. Are Faculties and Departments types of "sub unit"? It is not at all clear.
3. A Faculty has a "ref" to the University to which it belongs. Does this duplicate the information held in the "sub units" of University above?
4. A Faculty can have a maximum of 40 Departments, Professors and Staff.
5. Are the Staff belonging to a Faculty a subset of the Staff belonging to the University, or a different set of people? Unclear.
6. Are the Professors and Staff belonging to a Department subsets of the Professors and Staff belonging to the Faculty to which the Department belongs?
7. A President has a title which in your example is 'President'...!
8. A president has a "ref" to a University. University has a "ref" to a Person who is the President (but is not necessarily of type President_t!!!). Duplication of information???
9. Staff has a "ref" to Unit. Does this duplicate the Varrays of Staff in University, Faculty and Department?

Like I said, spaghetti. A simple relational model would be simpler, cleaner, more robust, etc.

Sorry, I don't know the answer to your question as I don't use these features. Hopefully you won't either, once you have passed your course and begin designing real databases.

--
Posted via http://dbforums.com