Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!csd4.milw.wisc.edu!lll-winken!uunet!ncrlnk!ncrwic!cetc!sjohnson From: sjohnson@cetc.Wichita.NCR.COM (Steve Johnson) Newsgroups: comp.databases Subject: Entity Supertypes/Subtypes & SQL Keywords: supertype, subtype, SQL, Oracle Message-ID: <1011@cetc.Wichita.NCR.COM> Date: 4 Apr 89 17:22:31 GMT Reply-To: sjohnson@cetc.UUCP (Steve Johnson) Distribution: all Organization: NCR Corporation, Wichita, Kansas Lines: 87 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 | 3450 N. Rock Rd. | <{ece-csc,hubcap,gould,rtech}!ncrcae!ncrwic!sjohnson> Wichita, KS 67226 | <{ucsd,pyramid,nosc.ARPA}!ncr-sd!ncrwic!sjohnson>