Re: separation of church and state?

#21

"paul c" <toledobythesea (AT) ooyah (DOT) ac> wrote

Quote:

David Cressey wrote:
...
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.
...

Thanks for previous answers. This might see picky but I can't find
"header" or "heading" in the text I have. Does the standard use some
other term to talk about "headers"?

My use of the term "header" might be non-standard.

Quote:

ColumnA ColumnB

for Table B and

Quote:

ColumnB ColumnA

for Table C are what I was referring to as "headers". The table header,
among other things, associates a Column name with a column position. The
rows containing the data only have data values, one after the other. The
position of a value in a row and the position of the corresponding column
name in the header together, establish the linkage between the value and
the name.

#22

"David Cressey" <cressey73 (AT) verizon (DOT) net> wrote

Quote:

"Roy Hann" <specially (AT) processed (DOT) almost.meat> wrote in message
news:ds2dnQF3Rrfm0pPaRVnyigA (AT) pipex (DOT) net...
"David Cressey" <cressey73 (AT) verizon (DOT) net> wrote in message
news:2RrPi.10966$br2.10003 (AT) trndny03 (DOT) ..

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.

Sadly given that it is now considered "best practice" to blindly and
automatically attach an entirely spurious unique "primary key" value to
every row in a table, that would be entirely futile.

Why is is sad? What's wrong with declaring a primary key? Or do mean
something else by "attach"?

I mean precisely that: attach. Construct a tuple that represents your
proposition, and then *attach* a synthetic value that is chosen precisely so
that it is unique (usually the next sequential number in practice), and call
that your primary key. And that makes me sad.

Roy

#23

"Roy Hann" <specially (AT) processed (DOT) almost.meat> wrote

Quote:

"David Cressey" <cressey73 (AT) verizon (DOT) net> wrote in message
news:OVuPi.10972$br2.6118 (AT) trndny03 (DOT) ..

"Roy Hann" <specially (AT) processed (DOT) almost.meat> wrote in message
news:ds2dnQF3Rrfm0pPaRVnyigA (AT) pipex (DOT) net...
"David Cressey" <cressey73 (AT) verizon (DOT) net> wrote in message
news:2RrPi.10966$br2.10003 (AT) trndny03 (DOT) ..

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.

Sadly given that it is now considered "best practice" to blindly and
automatically attach an entirely spurious unique "primary key" value to
every row in a table, that would be entirely futile.

Why is is sad? What's wrong with declaring a primary key? Or do mean
something else by "attach"?

I mean precisely that: attach. Construct a tuple that represents your
proposition, and then *attach* a synthetic value that is chosen precisely
so
that it is unique (usually the next sequential number in practice), and
call
that your primary key. And that makes me sad.

In that case, I guess you and I are on the same page. I said, and meant,
"declare a primary key". Sometimes it's not possible to declare a primary
key. More often than not, this means there has been a slip up in the
analysis and design.

Quote:

Roy

#24

On Oct 11, 12:41 pm, "David Cressey" <cresse... (AT) verizon (DOT) net> wrote:

Quote:

The table header,
among other things, associates a Column name with a column position. The
rows containing the data only have data values, one after the other. The
position of a value in a row and the position of the corresponding column
name in the header together, establish the linkage between the value and
the name.

I think it is worthwhile to make a distinction here between syntax
and semantics. Taking advantage of positions can be quite useful
in syntax, but it seems to raise a lot of complex annoying issues
if position is part of the semantics.

Marshall

#25

"David Cressey" <cressey73 (AT) verizon (DOT) net> wrote

Quote:

"Roy Hann" <specially (AT) processed (DOT) almost.meat> wrote in message
news:LJKdnSs3w_-6H5PanZ2dnUVZ8taknZ2d (AT) pipex (DOT) net...
I mean precisely that: attach. Construct a tuple that represents your
proposition, and then *attach* a synthetic value that is chosen precisely
so
that it is unique (usually the next sequential number in practice), and
call
that your primary key. And that makes me sad.

In that case, I guess you and I are on the same page. I said, and meant,
"declare a primary key". Sometimes it's not possible to declare a primary
key. More often than not, this means there has been a slip up in the
analysis and design.

I was pretty sure we would turn out to be on the same page. But I fear the
situation in the wild is worse than you think. You say "sometimes it's not
possible to declare a primary key".

I would fall down in a swoon if I met a progammer today who agreed it might
impossible to declare a primary key. Virtually no one would recognize that
situation because, as I said earlier, it is considered "best practice" to
*always* introduce a spurious attribute for a synthetic value and call that
the primary key. The problem you describe would simply never be revealed.

None of this is to suggest that synthetic keys and surrogate keys do not
have their place. They are essential, in their proper place.

Roy

#26

Bob Badour wrote:

Quote:

David Cressey wrote:

"paul c" <toledobythesea (AT) ooyah (DOT) ac> wrote in message
news:EbrPi.13483$Da.2157 (AT) pd7urf1no (DOT) ..

(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/~s...ql/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.

No apologies necessary.

SQL problem - clearly

#27

paul c wrote:

Quote:

David Cressey wrote:
...

Oracle RDBMS, by contrast, has always forbidden ORDER BY in view
definitions. Other than satisfying some people's need for
doctrinaire purity, the RDBMS users gained nothing by this
restriction. ...

That's interesting. Does it mean that ordering a view in Oracle RDBMS
always requires some "programming" beyond that of the view definition?

IIRC, also SQLServer

Dweeb

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: separation of church and state?

comp.databases.theory comp.databases.theory

Re: sql tables - 10-11-2007 , 02:41 PM

Re: sql tables - 10-11-2007 , 03:05 PM

Re: sql tables - 10-11-2007 , 03:18 PM

Re: sql tables - 10-11-2007 , 04:20 PM

Re: sql tables - 10-11-2007 , 05:02 PM

Re: sql tables - 10-19-2007 , 09:16 AM

Re: separation of church and state? - 10-19-2007 , 09:21 AM