Prev: Scripting Full text Search
Next: Free Text Search issue ?!
From: Tony Rogerson on 12 Jul 2010 16:23
> an adjacency list model. You also need to stop using IDENTITY and
> other non-relational,
>

We've been here before and you failed to give an answer instead a grumble
about me insulting you.

Anyway, I'll ask again - why do you think the IDENTITY property is
non-relational?

It's simply a function that returns an incremental number, its
non-deterministic just like NEWID() rather than say a deterministic function
like PI.

Now - I ask again, why do you believe the scalar, atomic number value
returned by the IDENTITY property is non-relational?

If this is about your rants on surrogate keys, I also highlighted the areas
where you got your research wrong and have since built a stack of articles
on - shall we discuss that as well?

--ROGGIE--

From: --CELKO-- on 13 Jul 2010 18:14
>> Anyway, I'll ask again <<
Considering that I have covered this topic in detial in my books,
newsgroups and articles over many years, this is just trolling. You
could cut and paste my stuff and make your case.
>> why do you think the IDENTITY property is non-relational? <<
It is a count of the physical insertion attempts (not successes!) in
one physical disk on one physical machine in one installation of one
product from one vendor. This is hardly an attribute of an entity or a
relationship.
It is at best a measurement on an absolute scale. It is a table
property.
>> It's simply a function that returns an incremental number, its non-deterministic just like NEWID() rather than say a deterministic function like PI. <<

I have Codd's dislike of non-deterministic "functions" and don't even
like to consider them in RDBMS. Closure, theta operators and therefore
joins, and other nice mathematical properties disappear. This was part
of the reasoning for NULLs -- RDBMS has to know what the values. I
want to have stationary random functions in my systems, but let's skip
that topic.

How many NEWID() columns can I have in a table? I can invoke a
function as many times as I wish; that is a property of a function..
But if it is a table property, I cannot have it in columns.

To be a function, it needs a domain and an range (or co-domain, if you
grew up with that term). It has to be invokable anywhere that its
range is allowed. The range and domain have to allow theta operators.
etc.

>> Now - I ask again, why do you believe the scalar, atomic number value returned by the IDENTITY property is non-relational? <<

Because it has nothing to do with the entity or relationship modeled
by the table. It is about physical storage events. How would you feel
about putting a thermometer on the computer and adding the temperature
of the disk drive at each physical insertion time?
From: Tony Rogerson on 14 Jul 2010 02:00
>>> why do you think the IDENTITY property is non-relational? <<
> It is a count of the physical insertion attempts (not successes!) in
> one physical disk on one physical machine in one installation of one
> product from one vendor. This is hardly an attribute of an entity or a
> relationship.
> It is at best a measurement on an absolute scale. It is a table
> property.

CURRENT_TIMESTAMP - a measurement of ticks on the current machine's internal
clock, it has no baring on the atomic time measured centrally. Executing
CURRENT_TIMESTAMP on several machines at exactly the same point in time may
yield different results if those machines are not time sync'd.

The surrogate key according to both Codd and Date is a property of the
entity, it may not be in the user model, but certainly in the physical model
and further into the implementation.

>>> It's simply a function that returns an incremental number, its
>>> non-deterministic just like NEWID() rather than say a deterministic
>>> function like PI. <<
>
> I have Codd's dislike of non-deterministic "functions" and don't even
> like to consider them in RDBMS. Closure, theta operators and therefore
> joins, and other nice mathematical properties disappear. This was part
> of the reasoning for NULLs -- RDBMS has to know what the values. I
> want to have stationary random functions in my systems, but let's skip
> that topic.
>
> How many NEWID() columns can I have in a table? I can invoke a
> function as many times as I wish; that is a property of a function..
> But if it is a table property, I cannot have it in columns.
>
> To be a function, it needs a domain and an range (or co-domain, if you
> grew up with that term). It has to be invokable anywhere that its
> range is allowed. The range and domain have to allow theta operators.
> etc.
>

I think you ought to read the latest ratified ISO SQL Standard - 2008;
Section 4.14.7 on page 82 "Identity columns" - you better read it.

But, I know you've not even bothered to check so I'll copy and reference it
here....

From INCITS ISO IEC 9075 2 2008 ->
"The columns of a base table BT can optionally include not more than one
identity column. The declared type
of an identity column is either an exact numeric type with scale 0 (zero),
INTEGER for example, or a distinct
type whose source type is an exact numeric type with scale 0 (zero). An
identity column has a start value, an
increment, a maximum value, a minimum value, and a cycle option. An identity
column is associated with an
internal sequence generator SG. Let IC be the identity column of BT. When a
row R is presented for insertion
into BT, if R does not contain a column corresponding to IC, then the value
V for IC in the row inserted into
BT is obtained by applying the General Rules of Subclause 9.23, "Generation
of the next value of a sequence
generator", to SG. The definition of an identity column may specify
GENERATED ALWAYS or GENERATED
BY DEFAULT.
NOTE 30 - "Start value", "increment", "maximum value", "minimum value", and
"cycle option" are defined in Subclause 4.21,
"Sequence generators".
NOTE 31 - The notion of an internal sequence generator being associated with
an identity column is used only for definitional
purposes in this International Standard."

So the key items here are.....

not more than one identity column (per table)
declared type is an exact numeric type (integer)
internal sequence generator

UPDATE YOUR RESEARC!!!

I will concede that the IDENTITY is a "PROPERTY" rather than a FUNCTION in
that case, its a PROPERTY that more than not conforms to the requirements of
the ISO 2008 standard (the current ratified one).



>>> Now - I ask again, why do you believe the scalar, atomic number value
>>> returned by the IDENTITY property is non-relational? <<
>
> Because it has nothing to do with the entity or relationship modeled
> by the table. It is about physical storage events. How would you feel
> about putting a thermometer on the computer and adding the temperature
> of the disk drive at each physical insertion time?

By using a thermometer you'd get values already used which isn't allowed by
definition for a surrogate key which is why the IDENTITY "PROPERTY" ticks
all the boxes for Codd and Date's research on surrogate keys. And because of
Codd's information principle it MUST be exposed to the user.

Seriously --CELKO-- do you ever keep yourself up-to-date? Have you bothered
to read the current ratified standard?

--ROGGIE--


From: --CELKO-- on 14 Jul 2010 14:29
>> CURRENT_TIMESTAMP - a measurement of ticks on the current machine's internal
clock, it has no baring on the atomic time measured centrally.
Executing
CURRENT_TIMESTAMP on several machines at exactly the same point in
time may
yield different results if those machines are not time sync'd. <<

You confuse the measurement with the instrument. Time is an attribute
in the physical universe. Yes, an instrument can be mis-calibrated;
but I doubt that anyone will think it is anything but a problem. It
has validation (ISO-8601) and verification rules (i.e. synch with the
NIST clock signals).

>> The surrogate key according to both Codd and Date is a property of the
entity, it may not be in the user model, but certainly in the physical
model
and further into the implementation. <<

>> I think you ought to read the latest ratified ISO SQL Standard - 2008; .. IDENTITY .. <<

I have and I think that SQL has some non-relational "features' in it.
There are non-table data structures, etc.

>> I will concede that the IDENTITY is a "PROPERTY" rather than a FUNCTION in
that case, its a PROPERTY that more than not conforms to the
requirements of
the ISO 2008 standard (the current ratified one). <<

Agreed.

>> By using a thermometer you'd get values already used which isn't allowed by
definition for a surrogate key which is why the IDENTITY "PROPERTY"
ticks
all the boxes for Codd and Date's research on surrogate keys. And
because of
Codd's information principle it MUST be exposed to the user. <<

And by Codd's definition of a surrogate key, it cannot be exposed to
the user. See the problem?
From: Tony Rogerson on 15 Jul 2010 02:56


"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:931cef1e-fb72-4ed1-a2ef-aedbc92087a9(a)d8g2000yqf.googlegroups.com...
>>> CURRENT_TIMESTAMP - a measurement of ticks on the current machine's
>>> internal
> clock, it has no baring on the atomic time measured centrally.
> Executing
> CURRENT_TIMESTAMP on several machines at exactly the same point in
> time may
> yield different results if those machines are not time sync'd. <<
>
> You confuse the measurement with the instrument. Time is an attribute
> in the physical universe. Yes, an instrument can be mis-calibrated;
> but I doubt that anyone will think it is anything but a problem. It
> has validation (ISO-8601) and verification rules (i.e. synch with the
> NIST clock signals).
>

And you confuse the internal system generator for the surrogate key with
some wild notion it needs to be verifiable in the outside world.

Even the ISO standard defines precisely what has been implemented by the
IDENTITY property, granted the syntax isn't correct however the formulaic
requirement is fulfilled.

>>> The surrogate key according to both Codd and Date is a property of the
> entity, it may not be in the user model, but certainly in the physical
> model
> and further into the implementation. <<
>
>>> I think you ought to read the latest ratified ISO SQL Standard - 2008;
>>> .. IDENTITY .. <<
>
> I have and I think that SQL has some non-relational "features' in it.
> There are non-table data structures, etc.
>

SQL entirely bastardises the relational model, its a poor implementation; if
you want a true relational database language you need to look at tutorial D
for instance, one implementation is Rel.

>>> I will concede that the IDENTITY is a "PROPERTY" rather than a FUNCTION
>>> in
> that case, its a PROPERTY that more than not conforms to the
> requirements of
> the ISO 2008 standard (the current ratified one). <<
>
> Agreed.
>
>>> By using a thermometer you'd get values already used which isn't allowed
>>> by
> definition for a surrogate key which is why the IDENTITY "PROPERTY"
> ticks
> all the boxes for Codd and Date's research on surrogate keys. And
> because of
> Codd's information principle it MUST be exposed to the user. <<
>
> And by Codd's definition of a surrogate key, it cannot be exposed to
> the user. See the problem?

Wrong.

What do you understand by Codd's "Information Principle"?

Do you consider more researched on Codd's work that Date?

--ROGGIE--


 | 
Pages: 1
Prev: Scripting Full text Search
Next: Free Text Search issue ?!