Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.media.kyoto-u.ac.jp!newsfeed.icl.net!newsfeed.cw.net!cw.net!news-FFM2.ecrc.de!uio.no!ntnu.no!not-for-mail From: Jon Heggland Newsgroups: comp.databases.theory Subject: Re: Foreign superkey support Date: Tue, 08 Aug 2006 17:17:38 +0200 Organization: Norwegian university of science and technology Lines: 32 Message-ID: References: <4q%Bg.36766$pu3.483816@ursa-nb00s0.nbnet.nb.ca> NNTP-Posting-Host: coleburn.idi.ntnu.no Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Trace: orkan.itea.ntnu.no 1155050381 20346 129.241.111.99 (8 Aug 2006 15:19:41 GMT) X-Complaints-To: usenet@itea.ntnu.no NNTP-Posting-Date: Tue, 8 Aug 2006 15:19:41 +0000 (UTC) User-Agent: Thunderbird 1.5.0.5 (Windows/20060719) In-Reply-To: <4q%Bg.36766$pu3.483816@ursa-nb00s0.nbnet.nb.ca> Xref: dp-news.maxwell.syr.edu comp.databases.theory:44069 Bob Badour wrote: > Jon Heggland wrote: >> Bob Badour wrote: >>> Why include them in the referencing relation at all? >> >> Pragmatic reasons. For instance as an alternative to an ASSERTION-type >> join-based constraint, which many(? most? Does anybody know?) DMBSs >> don't support. > > I don't follow your reasoning. If you simply omitted the dependent > attributes from the referencing relation, normal foreign key constraints > would suffice. An example: We have Items, each with a Type, and want to arrange them in Groups. Each Group also has a Type, and an Item may only be in Groups with the same Type. (Whether the relationship between Items and Groups is many-to-one or many-to-many doesn't really matter; I'll assume many-to-many. I'll also assume, in case it matters, that a Group may exist without any Items.) Straightforward relational modelling of this produces the relvars Item{_I#_, Type}, Group{_G#_, Type} and ItemGroup{_I#, G#_}---with foreign keys from ItemGroup to Item and Group. However, in order to (declaratively) support the constraint that Items can only be in Groups of the same Type, we would need an ASSERTION-type constraint involving the join of all three. An alternative is to add a Type attribute to ItemGroup, i.e. ItemGroup{_I#, G#_, Type}, and use foreign superkeys {I#, Type} and {G#, Type} to Item and Group. -- Jon