Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!shadooby!accuvax.nwu.edu!tank!ncar!unmvax!pprg.unm.edu!hc!lll-winken!uunet!portal!cup.portal.com!DMasterson
From: DMasterson@cup.portal.com (David Scott Masterson)
Newsgroups: comp.databases
Subject: Re: Entity Supertypes/Subtypes & SQL
Message-ID: <16750@cup.portal.com>
Date: 6 Apr 89 05:24:52 GMT
References: <1011@cetc.Wichita.NCR.COM>
Organization: The Portal System (TM)
Lines: 62

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