sjohnson@cetc.Wichita.NCR.COM (Steve Johnson) (04/05/89)
I am using Oracle RDBMS to develop a relational database.
I am looking for a solution to the following entity
supertype/subtype problem.
AN EXAMPLE:
Purchase_Order Table
PO_No PO_Date Terms Confirming_to Dept_No Acct_No
PK
Line_Item Table
PO_No Line_Item_No LINE_ITEM_TYPE Quantity Description
PK-----------> [entity_supertype]
Entity_subtypes for [LINE_ITEM_TYPE] {Animal, Vegetable, Mineral}
Animal PO Table {Rows containing: reptile, mammal, bird, etc.}
PO_No Line_Item_No Animal_name Number_of_legs Carnivorous
PK-------->
FK-------->
Vegetable PO Table {carrot, celery, tomato, etc.}
PO_No Line_Item_No Vegetable_name Color Texture Root_plant
PK-------->
FK-------->
Mineral PO Table {quartz, iron, phosphorous, etc.}
PO_No Line_Item_No Mineral_name Hardness Translucence Organic
PK-------->
FK-------->
ANIMAL could be further broken down into:
[entity_subtypes for ANIMAL]
Reptile PO Table {snake, lizard, turtle, crocodile, etc.}
PO_No Line_Item_No Reptile_name Skin_texture Poisonous
PK-------->
FK-------->
Bird PO Table {parrot, pigeon, penguin, ostrich, etc.}
PO_No Line_Item_No Bird_name Color Beak_length Feathers Fly
PK-------->
FK-------->
Further subtyping of Reptiles, birds, etc. could be performed.
NOTE: I realize that the above tables Bird PO table, etc. would
have a bird_table with Bird_name as a PK and Bird PO Table
using Bird_name as a foreign key. This is merely an example
of a supertype/subtype problem.
Now, say you had a purchase order with 10 line_items.
1. How can you using SQL perform a query that could pull up
all 10 lineitems including at least 1 column in the
subtyped table.
I see the problem in that the column LINE_ITEM_TYPE in the
Line_Item table refers to another TABLE (one of several tables),
instead of being a FK into a row in 1 table.
I realize that in supertyped/subtyped tables the columns in the
various subtyped tables are different. But, the question is
how can you select each lineitem with its appropriate
attributes that happen to be subtyped.
Any comments, suggestions, or reference to articles, books, etc.
would be very much appreciated!
--
Steve Johnson | Information Systems & Services, NCR-CETC 530-4726
(316)636-4726 | <S.Johnson@Wichita.NCR.COM>
3450 N. Rock Rd. | <{ece-csc,hubcap,gould,rtech}!ncrcae!ncrwic!sjohnson>
Wichita, KS 67226 | <{ucsd,pyramid,nosc.ARPA}!ncr-sd!ncrwic!sjohnson>DMasterson@cup.portal.com (David Scott Masterson) (04/06/89)
In message <1011@cetc.Wichita.NCR.COM>, sjohnson@cetc.Wichita.NCR.COM writes: > >I am looking for a solution to the following entity >supertype/subtype problem. > [Example of Purchase Orders dealing with <Animal, Vegetable, Mineral> items - seems a variation on the Factory Model - see below] > >Now, say you had a purchase order with 10 line_items. > >1. How can you using SQL perform a query that could pull up > all 10 lineitems including at least 1 column in the > subtyped table. > As is, this seems to be a factory tracking type of problem which the relational model cannot directly handle (the factory tracking model is one that given an end product produce a list of resources that went into it - a very complex outer join with incomplete information). Perhaps the model you are working with can be rethought into something more appropriate for the relational model. The problem requires some tradeoffs. With the relations as defined, it is fairly easy to design applications that can determine PO for minerals, etc., but it is very difficult to get information about a PO of a certain date. The connections between relations are dependent upon values of tuples within relations. The relational model supports fixed relationships. > >I see the problem in that the column LINE_ITEM_TYPE in the >Line_Item table refers to another TABLE (one of several tables), >instead of being a FK into a row in 1 table. > And therein, lies your problem. Because of this, your reports will probably become a super outer-join where all tables are queried, but not all tables have important information. Perhaps Oracle can allow the nested selection with a program, but I doubt there is a direct solution to this problem within SQL. > >I realize that in supertyped/subtyped tables the columns in the >various subtyped tables are different. But, the question is >how can you select each lineitem with its appropriate >attributes that happen to be subtyped. > Write a program that retrieves POs and then for each PO retrieves the necessary information from the Animal Table, etc. > >Any comments, suggestions, or reference to articles, books, etc. >would be very much appreciated! > I heard recently about work being done on a relational system that supported the definition of relations as attributes to other relations. Sounds like it has potential for what your talking about, but I've forgotten the reference. > >-- >Steve Johnson | Information Systems & Services, NCR-CETC 530-4726 >(316)636-4726 | <S.Johnson@Wichita.NCR.COM> >3450 N. Rock Rd. | <{ece-csc,hubcap,gould,rtech}!ncrcae!ncrwic!sjohnson> >Wichita, KS 67226 | <{ucsd,pyramid,nosc.ARPA}!ncr-sd!ncrwic!sjohnson> David Masterson DMasterson@cup.portal.com
mitchell@wdl1.UUCP (Jo Mitchell) (04/07/89)
Why are you considering one query rather than using cursors and doing other queries based on the subsequent result? I haven't used Oracle in three years but that's what we use to do for this type of thing, I believe.
sjohnson@cetc.Wichita.NCR.COM (Steve Johnson) (04/14/89)
>In message <1011@cetc.Wichita.NCR.COM>, sjohnson@cetc.Wichita.NCR.COM writes: >>I am looking for a solution to the following entity >>supertype/subtype problem. >[Example of Purchase Orders dealing with <Animal, Vegetable, Mineral> > items - seems a variation on the Factory Model - see below] >As is, this seems to be a factory tracking type of problem which the >relational model cannot directly handle (the factory tracking model is >one that given an end product produce a list of resources that went into >it - a very complex outer join with incomplete information). Perhaps the >model you are working with can be rethought into something more >appropriate for the relational model. Your right in that the application I am working on involves the tracking of integrated circuits through various workstations collecting various test data. >The problem requires some tradeoffs. With the relations as defined, it >is fairly easy to design applications that can determine PO for minerals, >etc., but it is very difficult to get information about a PO of a certain >date. The connections between relations are dependent upon values of >tuples within relations. The relational model supports fixed >relationships. ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^ Isn't this rather large problem with the relational model considering that Entity Supertypes/Subtypes are as I see it rather common. Or was the relational model intended to be this way? >>Steve Johnson | Information Systems & Services, NCR-CETC 530-4726 >David Masterson >DMasterson@cup.portal.com Does anyone have any neat routines written out there in Oracle using cursors that handle querying different tables based on the result of a column in a table? -- Steve Johnson | Information Systems & Services, NCR-CETC 530-4726 (316)636-4726 | Component Evaluation Technology Center 3450 N. Rock Rd. | <S.Johnson@Wichita.NCR.COM> Wichita, KS 67226 | <uunet!ncrlnk!ncrwic!s.johnson>
bobd@bloom.UUCP (Bob Donaldson) (04/14/89)
In article <1015@cetc.Wichita.NCR.COM>, sjohnson@cetc.Wichita.NCR.COM (Steve Johnson) writes: > >In message <1011@cetc.Wichita.NCR.COM>, sjohnson@cetc.Wichita.NCR.COM writes: > > >>I am looking for a solution to the following entity > >>supertype/subtype problem. > > >[Example of Purchase Orders dealing with <Animal, Vegetable, Mineral> > > items - seems a variation on the Factory Model - see below] > > >The problem requires some tradeoffs. With the relations as defined, it > >is fairly easy to design applications that can determine PO for minerals, > >etc., but it is very difficult to get information about a PO of a certain > >date. The connections between relations are dependent upon values of > >tuples within relations. The relational model supports fixed > >relationships. ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > ^^^^^^^^^^^^^^ > Isn't this rather large problem with the relational model > considering that Entity Supertypes/Subtypes are as I see it > rather common. > Or was the relational model intended to be this way? > > Does anyone have any neat routines written out there in Oracle using > cursors that handle querying different tables based on the result > of a column in a table? > Me Too!! I would be interested in such a routine for any SQL-based DBMS, especially Oracle or Empress. -=- Bob Donaldson ...!cs.utexas.edu!natinst!radian!bobd Radian Corporation ...!sun!texsun!radian!bobd PO Box 201088 Austin, TX 78720 (512) 454-4797 Views expressed are my own, not necessarily those of my employer. I H a v e t o s a t i s f y t h e n e w t e x t c o u n t e r