Re: sql tables

"paul c" <toledobythesea_at_ooyah.ac> wrote in message news:EbrPi.13483$Da.2157_at_pd7urf1no...
> (note, I changed the thread subject)
>
> Bob Badour wrote:
> > David Cressey wrote:
> > ...
> >> An SQL DBMS manipulates tables, not relations or relational variables.
> >
> > Exactly.
>
> I presume David could just as well have said "an SQL DBMS manipulates
> SQL tables". Found (finally) a copy of some draft or other of the SQL
> standard at http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt .
>
> Not sure how this copy might differ from the official ones, but anyway,
> here's some of what it says in section 4.9 which seems to be about
> "Concepts":
>
> > 4.9 Tables
> >
> > A table is a multiset of rows. A row is a nonempty sequence of
> > values. Every row of the same table has the same cardinality
and
> > contains a value of every column of that table. The i-th value
in
> > every row of a table is a value of the i-th column of that
table.
> > The row is the smallest unit of data that can be inserted into
a
> > table and deleted from a table.
>
>
> I'm wondering if there are popular SQL dbms's that follow this. For
> example, do any of them let me "insert", say, two "rows" that would be
> considered the same row if a table were a set of rows rather than a
> multiset of rows, giving, eg.:
>
> TableA:
> ColumnA
> 1
> 1
>

All of the "major" SQL DBMS products permit storing more than one identical row in a table. However, they provide several ways the database manager can protect the database from that event. The simplest is to declare a primary key for the table. This will also, however, protect against inserting two rows that differ, but have identical primary key values. Most often, that coincides with the intent of the manager.

> Also wondering about "i-th" values in rows. Does the above also mean that
>
> TableB:
> ColumnA ColumnB
> 1 2
>
> is not equal to
>
> TableC:
> ColumnB ColumnA
> 2 1
>
> (all other things being equal)?

The question is moot. Table B and Table C have different headers, even though they contain the same columns. a row with values {2, 1} inserted into TableB would be different from the row that's already there.

I'm not nearly as critical of SQL as the relational apologists in this forum. However, I think it's a weakness of SQL that it can't seem to make up its mind whether to use position or name as way of connecting values in a list to their "location". I see this confusion all over the language, and I think it's possible to do better. I am expecting relational apologists to point out specific languages that actually do better. Received on Thu Oct 11 2007 - 18:05:18 CEST