Path: utzoo!mnetor!uunet!lll-winken!lll-tis!ames!pasteur!ucbvax!ucsfcgl!cca.ucsf.edu!daedalus!brianc
From: brianc@daedalus (Brian Colfer)
Newsgroups: comp.databases
Subject: Re: Informix 4GL Question?
Message-ID: <1169@ucsfcca.ucsf.edu>
Date: 3 Mar 88 22:43:37 GMT
References: <714@uel.uel.co.uk> <2314@geac.UUCP>
Sender: root@cca.ucsf.edu
Reply-To: brianc@daedalus.UUCP (Brian Colfer)
Followup-To: brianc@daedalus.ucsf.edu
Organization: UCSF Dept. of Lab Med
Lines: 67
Keywords: Informix 4GL, SQL, Relational theory
Summary: Relational theory assumes abstract data model

In article <2314@geac.UUCP> daveb@geac.UUCP (David Collier-Brown) writes:
>In article <714@uel.uel.co.uk> andrew@uel.uel.co.uk (Andrew Josey) writes:
>>When deleting records in my database I check whether they are
>>referenced by any other records. This is currently done by code
>>similar to the following :
>>
>>let counter = 0
>>select count (*) into counter from table
>>  where table.column = key
>>if counter > 0
>>...
>>As some of my database tables are large, I would like to find
>>an alternative technique that terminates after finding the first
>>match instead of searching the whole table.
>
>   This is interesting, in that it is a perfectly plausible request
>that does not **seem** to be well-defined in the relational
>algebra/calculus (and perhaps even less well-defined in the *#%!@?&&
>sublanguages we get with them).

      Relational theory explicitly assumes that data is abstract and
therefore the sublanguages (SQL,etc.) should adhere to this assumption.
From E.F.Codd in Database programming and design vol. 1,no 2 (Feb. 88)

"What is important is that you can get at data in different relations 
by specifying conditions on the relationship between them.  It's true to 
say that a relation of the relationship model and a table are not the 
same thing.  A relation is a special kind of table in which ... it is possible
to shuffle the rows without changing information content....
[Here's the big one!]
In a relational database there is no notion of "nextness"..."

Following there is no such thing as the first or last
	so how can SQL support it?!

>
>   Can someone with a deeper knowledge of relational theory comment
>on this, especially with respect to saying
>	1) "give me one of"  -- the current problem

SELECT tbl1.col1
	INTO temptbl.col1
	FROM tbl1
	WHERE tbl1.col1 = unique_key OR tbl1.coln = unique_key

NOTE you can eleminate the INTO line and the value would be readinto
the virtual table known as the output device.

>	2) "give me the first n of" -- from a problem in bibliographic
>					search
>	3) "give me any n of"

Non-sensical from a relationship model perspective.  Plus from a
application perspective to arbitrarialy select a set of row(s) because of 
its position is illogical.  The better task formulation is:

Give me all the rows with these meaningful (date, author, publisher,
title) limtations. 

Or output the whole query to a file and then use some utility head/tail
to browse the content.


==========================================================================
             : UCSF Dept. of Lab. Medicine : brianc@cgl.ucsf.edu 
Brian Colfer : PH. 415-476-2325            : brianc@daedalus.ucsf.edu 
             :                             : brianc@ucsfcca.bitnet
==========================================================================