On 25 oct, 04:56, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

A valid set of questions (pun intended). My conclusion were that Codd
was focused onto defining a side of the relational model that would be
part of information system as opposed as being a part of mathematics
since relational domain analysis is more loosely coupled with set
theory than relational general model. While relational domain analysis
is not mandatory in the context of the general relational model, it is
mandatory in the context of implementation of RM. It even makes
header based definitions somehow moot...IMHO...

Regards...

On Oct 24, 8:56*pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
shoot your age and you are worth your weight in gold.

paul c wrote:

Nope. Natural join doesn't care about attributes in the same domain per
se. It compares like named attributes regardless of domain.

TTM explains when and how the most specific types can differ and still
have an equality comparison return true: they must share some common
supertype other than the universal supertype. In this case, the MST of
the join will be the most specific common supertype. If the MSTs of the
original relations are mutually exclusive, the body will be empty. If
not, the join body will consist of values drawn from subtypes of both
MSTs and that appear in both relations.

Otherwise, the body of the join will be empty and the type of the like
named attribute will be the universal supertype. As a type with a set of
values and a set of operations, the universal supertype's set of values
is the union of all values, and its set of operations is empty.

I expect most implementations would treat an attribute with the
universal supertype as an error, or at least complain loudly with a warning.

compdb (AT) hotmail (DOT) com wrote:

My age in what measure? Years? Integral or rational? If integral,
cardinal or ordinal? According to what calendar?

Bob Badour wrote:
I can't make much comment on type theory, which I think is optional, not
essential to RT. Appendix A, the relational underpinning of TTM,
doesn't depend on MST's or inheritance, just set membership except that
set equality crops up for unions of headings, as far as I can tell. It
does say, eg., in the <AND> definition: "It is required that if <A,T1>
'is member of' Hr1 and <A,T2> 'is member of' Hr2, then T1 = T2" (edited
quote). I presume those qualified attributes are the "like named" ones
you mean. While I wouldn't argue when you say "it compares like named
attributes regardless of domain", TTM does require that the "state is
set" beforehand, Appendix A uses the qualification to ensure that the
type for both attributes is the same.

paul c wrote:

Which still leaves Keith with the same answer: data types or domains
determine the semantics of joinability. In a system that supports
specialization and generalization, the attributes have to share a common
supertype. In a system that does not support specialization and
generalization, the types must be the same.

On Oct 24, 10:53 am, Keith H Duggar <dug... (AT) alum (DOT) mit.edu> wrote:
This question, it seems to me, is clear and to the point.
And I would answer it by saying that we shouldn't really
even make the distinction! (At least not formally.)

There are quite a lot of constraint taxonomies out there,
and I haven't ever really been able to derive any particular
value of any of them. Most generally, ever constraint
is a constraint on the entire database; if the constraint
mentions only a single variable, then we might say
it is a "table constraint" or "relvar constraint" or some
such, but that's only a degenerate case of a database
constraint. So let us say there is only one type of
constraint, the database constraint, and be done with it.

Now that I've said that, let me take it back a bit.
Transition constraints don't seem to be quite like
anything else. Transition constraints are therefore
annoying. :-)

It's Sunday and I'm hot, so I'm posting with
reckless abandon. Read my sentences at your
own risk.


Marshall

On Oct 24, 12:13*pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
That may be the most practical perspective, but from a theoretical
perspective, I find myself lately thinking that every value of every
domain is theoretically able to be compared for equality with
every value of every domain. If we are considering comparing
an integer and a string, we can say with confidence ahead of
time that the comparison will return that they are unequal.

I also am suspicious of the idea of allowing a domain designer
to define equality. From a theoretical standpoint, the system
ought to know enough to figure it out, and from a practical
standpoint, I've seen enough class designers screw it up
with subtle and annoying consequences. Damn them!


Marshall

(Still hot; still grumpy.)

On Oct 24, 7:53 pm, Keith H Duggar <dug... (AT) alum (DOT) mit.edu> wrote:

(Sorry for the resend, Google Groups seems to be taunting me...)

Uhm, being the practical kind of guy, I wonder where you're going with
this. I mean, you too agree that domains/types do get the job done.
Was there something else you wanted in addition to that?

And it is. There is no reason why the relational model wouldn't work
just fine with only a single type, such as an integer. (Encoding then,
say, textual data, would become somewhat convoluted, but it could be
done.) So the type system is indeed completely orthogonal to the
model.

However, that only means that when we model data, the relational model
by itself isn't sufficient to fully handle all of our conceptual
modelling needs. It's a very general theory, and as such it can
benefit from the addition of more specific constraints and semantics,
for example in the form of a rich type system.

From this point of view I would then say that your original question
about joinability is something that sits squarely within the type
system, and thus is itself fully orthogonal to the relational model.
Trying to somehow make the skeletal relational model responsible for
enforcing such constraints seems much akin to asking why the hammer
ain't working too well with the cream, right after casting aside the
whisk.

To reiterate, it seems to me that you're now trying to have your cake
and eat it too. First you say that domains, a particular
implementation of a type system, are outside of the relational model.
Then you go on to claim that the relational model has a problem
because in this amputated form it cannot handle a problem that is
caused by the lack of a type system.

The way I see it, both Date and Codd advocate a view of the relational
model which already includes a rich type system, and actively utilizes
it to express e.g. join semantics. Domains, they're Codd's particular
idea of how to handle typing in a form that actually reflects much of
the relevant metadata about the types back into a shared encoding that
is accessible under relational semantics. Granted, there will be other
ways of handling types, but Codd's idea doesn't seem all that bad to
me, and is also amenable to straightforward extension should we find
it somehow lacking.

True, but I would be inclined to believe that in practice those
mechanisms would only be used to circumvent modelling errors, before
the conceptual model was changed to explicitly allow the join. Most
commonly that would probably include the addition of a common
supertype, which Bob Badour already mentioned as a necessary
prerequisite for joinability. Under the domain model, this fact would
also be explicitly recorded in the data dictionary.

As I said, I could think of "at least two" ways to view it. However, I
don't think your quotation from Date falls outside of either of them.

There we agree fully. My point is just that a) yes, joinability is
something that probably should be explicitly constrained in the
database, b) domains at least get the job done, c) unlike abstract
typing, domains also reflect type metadata back into a formal, shared
and accessible form within the database which is a clear plus, so that
d) in the absence of a viable alternative, they or something closely
related to them is what we should use until we think of something even
better.

In that case, it's the systematic barcoding of certain real world
items that makes the union type sensible. The union type could then be
given the name of BarcodedItem, and could perhaps have its own
dependent data, like type of barcode, printing method, and so on.

My point was simply that you have to have a valid, real life/business
reason, or semantic justification for the union type/generalization.
Since you just gave one, I'm happy with that. At the same time you
should then note that under these semantics, you cannot add to the
union type willy nilly. For example including people in it is out of
the picture, unless you actually insist on branding your customers
with a barcode.

I don't really see what the connection with this and the above is, but
again we're in full agreement: indeed support for constraints on
relational expressions should be available. And it in fact would be if
RDBMS vendors just implemented generic support of SQL assertions.

The point is that when you start creating union types, and so the keys
used to them willy nilly, you tend to rapidly get to the point where
the resulting space of identifiers has no semantic relevance. What you
then have is a space of invented, autogenerated, and so on,
identifiers. Those cannot be correlated against the real world
entities they represent in any way except by reference to the data
dependent on them. What you then have is surrogate keys, and if you
take a look at RM/T and RM/V2, Codd makes an eloquent case against
revealing them to the users. Because making them truly opaque is then
not supported by current RDBMSes, I would hesitate to use them at all.

Heh, I did say going that way requires tremendous discipline and
insight...

At its most general, entity integrity has nothing to do with nulls.
Rather it has to do with whether you can reliably correlate the data
in your database with real life facts. It is essentially a semantic
construct which tries to ensure that it is possible to unambiguously
update the contents of your database against the changing reality
we're trying to capture/encode.

This is not a process that can be done strictly at the logical,
relational level. All we can do there is to to put up syntactic
constraints to help in the process along. The main, commonest,
simplest, and especially easiest to implement example is the key. It
tries to capture the semantics of real life things/entities a) always
having a certain set of characteristics we can use to correlate them
with the data we have on them (i.e. the not null syntactic
constraint), and b) being separable from each other once we're privy
to that set of characteristics (i.e. the uniqueness constraint).

That means that entity integrity is only certain to be maintained when
a) the set of facts we actually have on real life objects/concepts is
sufficient to uniquely identify their representation in the database,
b) in full generality, that does *not* mean that all of the data on
the primary key is available, only that all of the data required to
identify any candidate key is present, and that in case c) does not
mean that the identification has to take a form that only involves one
table; the data might be distributed all over your database, and in
general will be if you're using something other than full, natural
keys to link your data together.

Maybe a recycled example could make this clearer... Suppose you have
persons and the cars that they own. You're keying them both using
surrogates, which obviously have no real world significance because
you cannot take a look at a person or a car and immediately read off
the corresponding surrogate. (Tattooing the number on each person or
carving it into the manufacturer's stamp in a car would make the key
natural, and not a surrogate.) You can clearly have a not null and
unique primary key for each of the entities, but that no longer
guarantees entity integrity: there might be more than one person
called John Smith, and more than two cars with the make of Audi. In
order to maintain entity integrity, the syntactic constraint that the
surrogates be not null and unique is not sufficient; when you try to
update your database to reflect reality, you don't necessarily know
which John Smith is which, and certainly you can't tell one Audi apart
from the other. In particular, you don't have any reliable means of
stopping somebody from adding yet another John Smith or Audi that are
already described in your database; suddenly duplicates and the
resulting update anomalies will abound.

So the first lesson is that surrogates don't really work towards
entity integrity. Only keys referring to real life, measurable/
observable things, that is natural keys, do that.

Then, secondly, there is the more complicated case of distributed
information over multiple tables. Suppose that mess of surrogates you
have tells you that there are two John Smith's, which cannot be easily
told apart from each other. But then you notice that doing a join
against the table of vehicles owned tells you that the two Johns own
mutually disjoint sets of vehicles, which you *can* tell apart from
each other by their licence plate numbers (i.e. a natural key).
Suddenly you can tell those people apart at some level of certainty --
and counterwise if the sets overlapped, you could be perfectly certain
that entity integrity has been violated, the two Johns are actually
the same person, their records need to be unified/deduplicated, and
the update has to be cascaded into the rest of the database to reflect
the new knowledge.

Here the lesson is that keys and the related single table reasoning
aren't all there is to entity integrity, and that surrogates usually
make the situation so complicated that establishing entity integrity
even at the syntactic level can easily force you to scan through your
entire database. Which of course nobody ever does when using
surrogates, which then almost surely leads to integrity issues in the
long run.

Formally speaking, then, entity integrity is an inseparable part of
integrity as a whole, and it involves not just syntax but semantics as
well; especially model theory. Since a database essentially contains a
logical theory, the first part is about internal consistency; the
theory has to be consistent in order to have a real life model in the
first place. The second, more difficult part is that the theory also
has to be refined enough to exclude real life models which are
inappropriate, such as the one where we have one John Smith in the
database and a million of them in the wild, with no means of telling
which one is which at the logical level. And finally, third, the
hardest part is to strike the proper engineering balance between
maintainable detail, availability of real life identifying data and
the processes of efficiently correlating reality with the symbolic
universe represented by the database.

More often than not, then, surrogates and the improper generalizations
they imply hinder this process. Most often by creating a false sense
of complacency which obscures the real, underlying, logical issues
which need to be addressed. Then the result is cumulative damage to
overall integrity via simple laziness and neglect.

You seem to be, because your conflating the two types of identifiers,
simply because they're, well, "identifiers". That don't mean that they
semantically are of the same type, eventhough they might look like
that when they carry the same physical type (posrep).

None. Thus I think we're actually in what they call "vigorous
agreement".

In a highly convoluted fashion. Such a convoluted fashion that I'm not
inclined to give an example. Nor would I probably be inclined to go
through such an exercise when designing a database.

That, to me, suggests that types and domains simply make life easier,
and should be actively utilized when modelling data.
--
Sampo