| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> comp.databases.theory -> Re: A real world example
"JOG" <jog_at_cs.nott.ac.uk> wrote in message news:1155685450.575606.117120_at_b28g2000cwb.googlegroups.com...
> Brian Selzer wrote:
>>
>>>> > world, nothing to do with databases. Do you agree that?
>> > For instance if I change my name, then _in the real world_ this is of
>> > no use as identifying me when I was younger. It is an identifier for me
>> > now, but not at all for me as an entity that stretches over my
>> > lifetime. Same for my height, hair colour, age - they all change and
>> > hence do NOT identify me as a temporal concept. And thats in the real
>>
>>>> > Either way the relational model must not hide it.
>> > Now, there are things that will identify me over my lifetime (in this
>> > case the universe of discourse is objects over a period of time), that
>> > will remain consistent. THESE are the correct identifying keys to
>> > choose if the lifetime of a changing entity is what one is concerned
>> > with. I do not see how one can argue against this.
>> >
>> > Now sure, a surrogate can represent these perhaps unrecordable
>> > identifiers. But if I want to refer to myself at any point in time, I
>> > _need to know_ that surrogate (or something very intelligent in the
>> > middle has to deduce it from me giving it lots of other information).
>>
> > No, if you have chosen the correct key, none of that is necessary. Now, > I reckon your probably saying that "any key can change", and you are > right, and I'll address this further on. > >>>> > incorrect.
>> > Hence I contend to you Brian that this is nothing to do with the
>> > relational model. Rather it is a mistake in the *universe of discourse*
>> > that the schema designer has made. They mistakenly believed they were
>> > identifying 'snapshot entities'. In reality they needed to identify 'an
>> > entity over its lifetime'. The domain they were considering was
>> > completely wrong, and hence the key they chose was completely
>>
> > Well for my standpoint, this I think highlights the mistake in your > argument. First the definition of a key is a red herring because we are > all agreed on what a key is. Rather it is picking an _appropriate_ key > for the task at hand that is vital. >
I disagree. The definition of a key is critical. Because its scope is a single database instance, it cannot permanently identify things in a universe that can change. Stability is irrelavent. If it can change, then it must be assumed that it will. In the example below, the tuples in the preceding state CAN be correlated to the tuples in the succeeding state, yet as shown below, it cannot be determined whether x changed or y. If you had a transition constraint on y, it could not be enforced--it would accept changes that should be rejected and would reject changes that should be accepted. If you tried to write a trigger to cascade the update, it would introduce garbage into the system--sometimes it would propogate the change, sometimes it wouldn't. If you tried to write a trigger to log changes, it would record incorrect information.
Note: I categorically reject the notion that the application should issue key updates separately. If the system allows ad-hoc queries (and most do), then one could issue such a change, bypassing the application.
> It is important to see that "Me now" is a completely different entity > to "Me over my whole lifetime". The temporal issue is irrelevant, all > that matters is to recognise they are just different entites. I know > this is initially seems an obtuse philosophical point, but it has > _real_ consequences for how to model those entities. >
I see the difference, but I can't see how you can shrug off the temporal issue. They must have a common property because they're related. I would argue that "Me now" is part of "Me over my whole lifetime."
> Something must remain constant to compare entities - something must > identify them. If nothing remains constant the things being compared, > by liebniz equality, are different things full stop. This is what > mathematical logic is grounded in, we can't just avoid it. You seem to > be saying it is possible that "every attribute of something has > changed, yet it is still the same thing". Surely that's logical > nonsense! >
In the mathematical sense, if *any* attribute is different, then they're different, but that's beside the point. I'm not saying that it is possible that "every attribute of something has changed, yet it is still the same thing" What I'm saying is that "every universal property of something can change, yet it is still the same thing." I know I'm going to get beat up about this, but as I see it, there is a difference between a property that defines something and a property that describes something. In an earlier post, I used the terms, "individual" and "universal" to describe these categories of properties. Individual properties define the essence of something and remain constant throughout its lifetime. Universal properties depend on the state of the universe during that lifetime. Universal properties are the only ones that can change.
"Are you the same person you were when you were 10?" depends on the context of the query, not necessarily the data, but in either context, you must be able to correlate the state when you were 10 to the state you are now in order to do the comparison. In the one context, you would compare the individual properties; in the other you would compare the universal properties.
> >>>> > the domain you are considering, and one must pick the correct key
>> > This is exactly the same as the basic philosophical question of change.
>> > Are you the same person you were when you were 10? In one context the
>> > answer is absolutely not, so an appropriate identifier is not DNA. In
>> > another yes you are the same person, so DNA is perfect. It depends on
> > I worry you have glazed over this, but it highlights how the context > information is more complex than we initially think, and we must > understand that context to make the right key choices (especially if > you want to make inter-relation value comparisons). > > My DNA don't change over my lifetime, so its a good key for that > conceptual entity (n.b. the representative label to record the DNA > might change, but the value is the same.) >>> > sigh. All best, Jim.
>> >
>> >
>> >>
>> >>
>> >> Here's a simple example of what can happen:
>> >>
>> >> create table P
>> >> (
>> >> x int primary key,
>> >> y int
>> >> )
>> >> create table F
>> >> (
>> >> x int primary key references P(x),
>> >> z int
>> >> )
>> >> insert P (x, y) values (1, 1)
>> >> insert P (x, y) values (2, 3)
>> >> insert P (x, y) values (3, 2)
>> >> insert F (x, z) values (1, 5)
>> >> insert F (x, z) values (2, 4)
>> >> select * from P join F on (p.x = F.x)
>> >> update P
>> >> set x = case x
>> >> when 1 then 2
>> >> when 2 then 1
>> >> end
>> >> where x in (1, 2)
>> >> select * from P join F on (p.x = f.x)
>> >>
>> >> RESULTS:
>> >>
>> >> Before:
>> >> x y x z
>> >> ------ -------- ------- --------
>> >> 1 1 1 5
>> >> 2 3 2 4
>> >>
>> >> After:
>> >> x y x z
>> >> ------ ------- -------- --------
>> >> 1 3 1 5
>> >> 2 1 2 4
>> >>
>> >>
>> >> Should the new values for x in P have cascaded into F? Assume that
>> >> the
>> >> system only has the before image and the after image (the first two
>> >> columns
>> >> in RESULTS) in order to complete the update. How can such a system
>> >> differentiate between the above update and the following update?
>> >>
>> >> update P
>> >> set y = case y
>> >> when 1 then 3
>> >> when 3 then 1
>> >> end
>> >> where x in (1, 2)
>> >>
>> >> Clearly first update affects a key, and consequently, the changes
>> >> should
>> >> cascade, but with the information available (both updates produce the
>> >> exact
>> >> same before and after images), the system cannot differentiate between
>> >> the
>> >> two updates; therefore, it cannot determine whether or not to cascade
>> >> the
>> >> changes. Updates within the Relational Model are are constrained in
>> >> the
>> >> same way as this hypothetical system. All that is available is the
>> >> preceding instance and the succeeding instance, and if the only key
>> >> can
>> >> change, then there is no way to correlate tuples.
>> >
>> > I'll consider the example in due course. RL work to do in the meantime,
>>
 |
 |