From: Christian Brunschen on 24 Sep 2005 14:43
In article <0k59j1l3pbjg770u8rfl12lp5rjlh0b8aq(a)4ax.com>,
Chris Sonnack <Chris(a)Sonnack.com> wrote:
>Christian Brunschen writes:
>> Yes, a specific kind of table.
>Okay, let me just start by saying, "Okay, I see that a 'relation' is indeed
>a (specific kind of) 'table', and we can consider the issue closed."
>The rest of this is just in nitpicky programmer Friday afternoon fun....
Saturday afternoon for me.
>I'm not entirely sure I agree (at this point) that a 'relational database'
>does not consist of relations that are, in fact, um, related.
The 'relational' in 'relational database' comes solely from the 'relation'
that is a type of table, and has nothing to do with them being 'related',
regardless how similar the words are.
>that a single relation with no relationSHIPS isn't very useful, nor is it
>much of a database.
I am, in fact, using precisely a single-table database in a project I am
currently working on; and it doesn't contain any references to anything,
not even itself. Each row simply describes, with a small collection of
attributes (columns), a specific item that is being processed in the
application I am developing. It is simple and straightforward - and I am
using the relational aspect of the databas, because I am using both the
selection ('fetch me rows that match these criteria') and projection
('fetch me only thses columns') operations.
In fact, let me quote once more from C.J.Date ("An Introduction to
Database Systems, Volume 1, Fifth Edition", ), Chapter 11.4, bottom
pf page 266:
In the light of the discussion and explanations of the previous two
sections, it is now possible to give a slightly more formal definition -
more formal, that is, than the one given in section 4.2 - of the term
* A _relational database_ is a database that is perceived by the user as a
collection of time-varying, normalized relations of assorted degrees.
Thhat is the entirely of the definition; it expands on some of the phrases
used in the defnition (such as 'perceived by the user' and
'time-varying'), but it does not in any way suggest that is a requirement
of there being any specific minimum number of relations, or that they
should somehow be connected.
>(Not that that's very relevant to the definition of a relation. :-)
>However, will you agree that, while relations are tables, not all tables
>are relations. Can't a table--just a table--fail to follow the rules
>Codd laid down for tuples and therefore NOT be a relation?
Of course it can.
However, in the context of database systems where SQL is used, the tables
in question are sufficiently close to being relations, that that
effectively doesn't matter.
It should be noted that by 'table' in this discussion I am only referring
to 'tables as you would usually find in something generally considered a
relational database', not completely general 'tables' in the sense of
'some data arranged in what looks visually like a grid'. To reiterate, I
am talking about 'tables' in the context of talking about databases that
are generally considered to be relational databases, such as databases
that are SQL-compliant, etc. Oracle, MySQL, PostgreSQL, Sybase ...
>Thus is seems to me that--strictly speaking this is NOT true:
> table == relation
>Relations are, it seems to me, a subset of all tables.
Strictly speaking, there is indeed no precise equivalence between
'relation' in the pure mathematical sense and 'table' in particular as
used to describe the vastly more general subject of 'some collection of
data, visually laid out in rectangular-ish grid' - strictly speaking, in
fact, a 'table' is just _one possible visual representation_ of a
However, we are talking about databases (and specifically, the kind of
databases that are generally availanble today) and SQL as a language in
that context. Since those databases are generally referred to as
'relational databases' even though it is well-known that they depart from
the pure relational model, we must allow the same leeway when looking at
individual tables. In other words, we have to keep the same context around
when we are comparing things: We can't let Oracle get away with being
called a 'relational database' in spite of its departure from the pure
relational model, while at the same time nit-picking about a single table.
>> Indeed, anything that uses SQL is basically forced by the SQL
>> standard(s) to implement something that in some respects departs
>> from the pure relational model.
>I've heard DB theorists say this...I'm curious, what is it about the
>SQL standard that forces this?
SQL permits the creation of tables that don't have a primary key, and
which permit duplicate rows. Duplication of rows is forbidden in the
relational model (as relations are _sets_ of tuples), which means that in
the relational model, each row by definition always does have a primary
key (at the very least, the entire tupe can be used as a compound primary
SQL also doesn't allow the specification of domains for each column as
the relational model demands, but only of 'data types'.
However, the fundamental property of those databases that are generally
called 'relational databases' is that they offer the user a view of their
data that is fundamentally based on the relational model: data are
presented in tables, rows are generally unordered, each column has an
assocuated data type which all of its values (regardless of row) belong
to, etc. And they offer the usual relational operations, such as
selection, projection, joins, etc.
>And, ironically, this brings us back to the original point about
>whether SQL is a "relational language".
SQL is essentially a language based on Relational Calculus, which is just
as powerful as Relational Algebra, but of course somewhat different. SQL
further makes some deliberate decisions not to follow the pure, strict
relational model: for instance, in the pure relational model, operations
on relations giverelations as their result, and in other words they remove
duplicate rows from results. SQL, however, permits duplicate rows both in
its tables and in results from queries, among other things so that
aggregate functions operate correctly.
But generally, SQL is still essentially Relational Calculus, offer the
necessary Relational operations, and so on. It is definitely a relational
language - even when used with only a single table.
>> Yet we talk about 'relational databases' nonetheless, and we use
>> relational theory, and it all works.
>Indeed. (If I wanted to drag this out painfully, I might try to argue
>the case that 'relational' strongly implies more than a 'relation' and
>that a single 'relation' with no FKs would not, in fact, be 'relationAL',
>but would be just a lonely little 'relation'. But I don't want to drag
>this out, so I won't.)
(If you did, I would simply have to counter again that 'relational' only
means that it deals in being a collection of 1 or more _relations_ - no
more no less, and that thus a single lone table certainly *does* qualify.)
>> In other words, you were asserting that the term 'relational' _does_
>> have something to do with keys and their referencing stuff - which it
>Well,.... per my parenthetical thought above, I think one could at least
>try to argue the point. 'RelationAL' could certainly mean a database
>built on 'relationS' that are 'relatED'. (-:
It could, but it doesn't: 'Relational' simply means that the abstraction
used for representing data to the user is the relational one, with data in
tables made up of rows.
>>>You know, maybe we should just go to the source:
>> Maybe we should. Incidentally, why haven't you done just that prior to my
>Well, for one thing, I like debate. (-:
I prefer an actual debate to a flamewar, though, which in many ways this
debate had degenerated into.
>And I just found that link after reading your post, but didn't have time
>to read the entire paper. I did print it for reading later--this stuff
>doesn't apply to the work I do currently, but I find it interesting enough
>to pursue when time permits.
If you can find the time, I'd actually suggest that you buy the C.J.Date
book (currently in its eigth edition) and read that. It is very
well-written and easy to follow, and at the same time comprehensive.
I'll probably buy the eigth edition myself.
>> Or indeed, prior to making your inaccurate assertion - then this
>> whole flamewar would have been avoided.
>I've never claimed to be a database expert.
Neither am I. Yes, I work with them more or less daily, but the C.J.Date
book is one of the textbooks that one would encounter in an introductory
database class at university level, which is the context in which I
>I got into this rather
>wretched thread speaking out *against* what I saw as silly overuse of
>them. It spiraled down from there, and I'm still not sure I wasn't
>trolled. (But it was amusing enough to be engaging even so.)
I have been vaguely following this thread from a distance, and has noticed
that 'topmind' seemed to simply not be understanding some of the issuees
he was attempting to debate, even when things were pointed out to him.
However, on the point of relational databases being relational even in the
presence of only a single table, he was actually correct - and even soem
of the same people who were acccusing him of not researching what he was
debating, were attacking him on this point where he was correct and were
also not researching the issue, and thus guilty of precisely whhat they
were accusing him of. *shrugs*
>>>> In Chapter 11, 'Relation Structure', the book introduces formal
>>>> definition for each term, which I will not quite here;
>>> Aw, why not?
>> Because thay are *long*, and I only had a limited amount of time
>> available when composing my post.
>Exactly why I wasn't able to do more than skim Codd's paper.
Well, you've been participating in this discussion far longer than I have,
and have had a smidgen more opportunity to do some research on the issue.
>> Look - nothing here about keys or references to anything, inside or
>> outside the relation itself.
>> You'll eventually have to accept that the term 'relational' has
>> nothing to do with keys, or references, or similar; just with
>> each individual _relation_ itself.
>That's fine if we grant that 'relational' == 'consisting of relations'.
>And I will accept it as such....certainly if that's the convention.
>> The point that you missed is that even a single table is still a relation
>> in the 'relational database' sense. The fact that there is only one table,
>> or the fact that there are no foreign keys, does not make the database any
>> less 'relational'.
>Well.... I'm less convinced here, actually. Suppose you have a table
>that does not meet the requirements of a relation? And haven't you
>already suggested the SQL standard violates at least some aspects of
>the relational model?
The pure relational model is almost nowhere in use; yet enough of it is
that we are speaking of 'relational databases'. Basically, there is anough
of the pure relational model in the databases and in SQL that it would be
foolish to deny their 'relational'-ness simply because of the relatively
>> Fanned on by the fact that neither side apparently bothered to
>> properly research the issue.
>I don't generally do my opponent's work for him. (-:
I've found that, in a debate, if I come across an area where my opponent
makes assertions that I am certain are wrong, if I reserach the issue I
end up being better armed about _why_ he is wrong - or alternatively I
find out that he is right and I am wrong, and by doing it through my own
research I end up not having made a fool of myself in the debate by
insisting on something that turned out not to be the case.
>> A database with a single table that doesn't refer to anything else, is
>> *just as* relational as a database with lots of tables that refer to each
>> other using foreign keys. It's that simple.
>EVEN IF the table fails to meet the requirements of a relation?
My careful phrasing there was deliberate: If a database engine is
considered 'relational', then it doesn't really matter whether any
specific database contains only one or several tables. If you'd consider
the database relational if it contained more than one table, then you
should consider it just as relational if it contains only one.
Since, in general, we don't let the minor differences between tables in a
SQL-based database and the pure relational model's definition of a
'relation' bother us, this should remain the same regardless of the number
of tables involved in the database.
>> Well, guess what, a 'flat table' can still be a 'relation'. [ It also may
>> not be, if it contains duplicate rows, or breaks one of the other
>> requirements (none of which are really particularly onerous); but the
>> above table doesn't, so the above table certainly looks like it would
>> qualify as being a 'relation'. ]
>And if I added a duplicate row or other violation of the rules?
Then it may not be astrictlly a relation in the terms of the pure
relational model, but it will still be just as much a table in a
relational database. As I've said before, since the common usage has
relaxed the strictness of the terminology, we should maintain the same
lack of strictness.
[ The only context in which I would insist on the strict terminology would
be when discussing precisely the differences between a real-world
implementation and the pure relational model. But in any real-world
context, I would simply keep in mind the differences between the strict
and the more common definitions, but not let that get in my way of using
the terms in the way that they are being generally accepted. ]
>> In other words, there is no difference in how 'relational' a database is,
>> simply because of the number of tables in it, or the number of foreign
>> keys or other references between tables.
>Consider me slightly more educated about relational databases now!
Excellent! Please, do get the C.J.Date book (or another similar one) and
educate yourself further: I think you will find them very useful, as well
as a both theoretically and practically interesting thing to know more
about. And they really are very useful.
>> You may not recignise the 'relational-ness' of the table you have
>> skeitched above, but it's there.
>Heh.. .just means I SHOULD have done the research and come up with a
>better--non relational--table! (But gimme a little slack--I'm putting
>in 12-hour days at the moment (and per a cothread in this group, loving
>every minute of it).)
Even if you had come up with a table with duplicate rows, you would not
have invalidated the basic truth of the generalization that in common use,
'table == relation'.
>> Your table above, which you attempted to use as a counterexample, appears
>> to qualify as a relation. Of course, if you were to introduce multiple
>> identical rows, then you'd no longer have a relation - but then that's a
>> deliberate choice, and would be no different if this table was one of
>> several ones that were referencing each other.
>> The end result is that your original assertion remains incorrect, and that
>> the general statement 'relation == table' _is_ substantially correct, your
>> protestations to the contrary.
>Are you saying that because 'relation' comes first, so (to you) the
>statement reads, "all relations are tables"? By what you've written,
>it appears, perhaps, you would disagree that:
> table == relation
No. In common usage, and for all practical purposes, a table is a
relation, and a relation is a table. Strictly speaking, they are
different, but as I said, for practical use, the terms are
interchangeable. The only case when I wouldn't interchange the terms is
when looking precisely at the differences between tables in general and
relations as defined strictly according to the relational model, and then
only if there was a practical difference to the case at hand.
>To me, the statement reads that 'table and relation' are identical in
>all ways (regardless of the order of the terms).
They are, to all practical intents and purposes, identical as far as the
user is concerned, in the vast majority of cases. The remaining
differences matter on rare occasions, and on those occasions it makes
sense to point out that the equivalence is only a generalization, but
outside of those cases, the generalization holds.
>Anyway, thanks for stepping in and educating me. (-:
I'm glad you're sufficiently open to being educated - not everyone is.
// Christian Brunschen
From: dcb on 25 Sep 2005 03:15
A table is a collection of records that are related in that they have
the same columns.
The result of an SQL statement's execution is a table of rows that are
related in that they all satisfy the conditions of the SQL statement.
Rows of two tables may be releated to each other by a common key.
An SQL where clause expresses a relation between two table columns.
A calculated value is related to the operand fields through an
What's not relational?