Subject: ROUND 2 OF THE DATA MODEL FOR TSQL2

From: /PN=nikos.n.l.loren/OU=infolab/O=aua/PRMD=ariadne-t/ADMD=_/C=gr/@grx400.ariadne-t.gr
Date: 13 Aug 93 13:08:13 UT

FROM: NIKOS
TO    RICK

SUBSECT: ROUND 2 OF THE DATA MODEL FOR TSQL2

Dear Rick,
I would firstly like to inform everybody that since
nobody expressed his interest in contributing in the
group I was coordinating, it has never been active,
therefore no output can be expected from it.
(In fact, only Shury expressed his interest two days ago
but since there is not any time left for some substunsial piece of work, I
proposed to him to join the group you coordinate.)
I also wonder whether there is still enough time for me
to join normally your group. In fact there is not too
much difference between the pieces of work which would be
carried out by your and my group.
Unfortunately, I have been very busy during this period
and at the same time I had to visit Madrid for the
purposes of the ORES project. This did not allow me to
participate closely, and express my ideas.
Please let me firstly express some of my ideas
on the decisions you have already made.
but please do not think that I am trying to cause any
problem or put them again under discussion.
I copy them below and give my opinion:

* TSQL2 should allow the restructuring (grouping, as in
SQL) of relation instances on any explicit attributes.

* TSQL2 relations should be implementable in terms of relations in some 1NF
representational model.

* Operations in TSQL2 should not accord any explicit
attributes special semantics. For example, operations
should not rely on the notion of a key.

* For simplicity, tuple timestamping should be employed.

* Temporal support be optional.

* Implementable with event- or interval-timestamped
tuples.

* For simplicity, tuple timestamping should be employed.

> I agree on the above.

***** Comments *****
---------------------
* TSQL2 must have an efficiently implementable algebra
that allows for optimization.

> I agree too but I think that optimisation issues should
not be coupled with modelling issues.
I wonder whether the meaning is that effort will be made
so that as much of the optimisation of the snapshot model
to be brought in the valid time model.

* The TSQL2 data model should not distinguish between
snapshot equivalent instances, i.e., snapshot equivalence
and identity should be synonymous.

* In TSQL2, any two different relation instances should
never be snapshot equivalent.

> It is not clear what by the above statements is meant.

* Temporal support be optional.

* TSQL2 should support exactly one valid-time dimension.

The above two statements seem to be  contradicting.
The first implies that valid time may be missing from
a relation, the second requires that one valid time must
always exist.

Some more remarks on the second of these decisions are
the following:

(a) How about relations with only a time stamp but not
explicit attributes? Will they be invalid relations?
If this is true then, by assumption, we do not allow for
queries of the form:
 "give me the time at which ...
John moved to the shoe department
John's salary was less than 10k"
etc, in which we ask to retrieve only valid time.

(b) By assumption, we also prohibit answers to queries
like the following:

(b1) "Give me the time John's salary was 10k
and the time he was in the toys department."
(b2) "For each father give me his lifespan and the lifespan of his
children. (They both requires to retrieve two valid times in two distinct
attributes.)

(b2) If R(A1, B1, VT) and S(A2, B2, VT) are two
distinct relations, we generally prohibit reasonable quries whose answer
might be of the form
(b1, vt1, b2, vt2) where vt1 and vt2 are different.

Please note that if we consider relations with at most
one time stamp will be very restrictive:

(i) Gadi Ariav has pointed it out (ACM paper) that
there are real world applications which require more than
one time stamp.

(ii) In your BTDM, you have talked about
spatiotemporal stamping. The spatial portion
of a stamp could also be replaced by another valid time
stamp.

(iii) I have shown given examples like the above
and also have demonstrated cases where we can also
talk about alphanumeric stamps.

In my opinion therefore, if we restrict to at most one
stamp, firstly we shall restrict the capabilities of a
valid time model in meeting real user requirements,
secondly we may impose standards which will restrict
further enhancements in the future for applications which
may require more than one type of stamping.

* TSQL2 should be based on homogeneous tuples.

(i) This decision disalows to answer queries (b2) above.
(ii) Note also that this greatly implies that
join operations will have to be defined in terms of
valid time intersection. As a consequence, if a father A dies before his
child B is born, it will never be
possible for (A, B) to be displayed as the result of the
execution of the query "list fathers and their children".

 This decision can also give rise to semantic
contradictions. Consider for example the query
 "Give me the times at which Alex was in the shoe
department and his salary two years later."
(If we assume that John was in  the shoe department at
time t and his salary was 12k at time t+2, we shall end
up with a relation, a tuple of which will be (t, 12k), therefore time t
'stamps' a value which is *valid* two
years later.

* Language data model allows multiple representations.
I am worried about it. If by this it is meant that
the vendor has to also develop tools for different
presentations, this is fine. Even today, they provide
screens, forms, etc which enable the user display the
results in a way which best fit his requirements.
However, this has nothing to do with the model,
it only concerns the development of display tools.
In my opinion, however,
when we talk about data modelling,
we talk about a standard way
of presenting data to the user and the operations we
supply him with, so as to manage the data.
If by different representations, it is implied
that a DBMS vendor has to directly incorporate
in his software the display capabilities of all proposed
models or at least those of the most representative ones,
and perhaps a minimum representative
repertory of operations, selected from distinct models, this directly
implies that the portability of
applications from
one DBMS to another will become prohibitively costly,
In this case, we shall
end up with a proposal which will not be accepted
by anybody.

I now come to the queries of round 3, topic 2:
* TSQL2 may support grouped features.
> If this means the SQL 'GROUP BY' clause,
it is fine. If it means nested relations,
I think that within SQL2 this should be avoided:
SQL2 is supposed to be a direct, consistent extension to
standard SQL, where nesting is not allowed.
Since it has been demonstrated in many of the proposed models that
valid time data can be supported by 1NF relations,
why go immediately to nested relations, thus
increasing the complexity of the problem?
On the other hand, this group works on an SQL2 extension
only.
In my opinion nesting should be considered only in SQL3.
I do believe however that SQL3 should be a fully
compatible with SQL2, therefore the group
does quite well by restricting only to SQL2 extensions.
Input from the work of this group will then
be used for the definition of SQL3.

* TSQL2 may support surrogates.
This should not necessarily be mandatory
because surrogates are not a requirement
in modelling data in 1NF relations.
In spite of this, they are supported in some implementations.
I understand Jim's concern on this issue.
However, the following should be noted:
(i) The issue Jim has issued can also appear in the join of  two snapshot
relations with a many-to-many
relationship in each of them.
(ii) If
we consider the two 1NF valid time relations
(I do not imply any particular model here)

SALARY(Name, Amount,    Time)
       john,   10k,  [d1, d10)
       john,   12k,  [d10,d20)

DEPARTMENT(Name, Dep,    Time  )
          john,   D1,  [d5, d15)
          john,   D2,  [d15,d25)

then pure snapshot join operations would yield
either

Name, Amount, Dep,   Time)
john,   10k,   D1  [d5, d10)
john,   12k,   D1  [d10,d15)
john,   12k,   D2  [d15,d20)

or

Name, Amount, Dep,   Time)
john,   10k,   -   [d1,  d5)
john,   10k,   D1  [d5, d10)
john,   12k,   D1  [d10,d15)
john,   12k,   D2  [d15,d20)
john,    - ,   D2  [d20,d25)

which can both satisfy the user by the way it is
displayed and, at the same time, it is more easily
managed further from within the frame of a snapshot
relation. Finally, the complexity of managing a nested
relation is avoided.

* TSQL2 may support time-invariant keys.
If it is a pure snapshot relation (which is also valid for a valid time
DBMS, this is obvious.
If  'may' implies that time does not ever participate in
the key, then there is a problem:
Consider the snapshot relation
SALES(Date, Money)
in which we record the total amount of money
spent on each particular day.
Clearly, the key is <Date> and there is no
doubt that time 'stamps' money.
Since it should also be a valid valid time relation,
I cannot see why the key should now change.
Similarly, for relation

INFLATION(Time,     Percentage)
          [d1,  d7)        8%
          [d1, d13)        8%

in which we record the rate of inflation
for various time-intervals,
the key is Time. (I am sorry for being
influenced now by my approach.)

Generally, I can provide examples
in which time may
(i) be the key,
(ii) participate in the key
(iii) be functionally dependent on the key
and it can be either time-point or time-interval.
At the same time, when we say that  "At time d10 John's
salary was 12k", in some way we implicitly assume that
John's salary depends on the particular time.
Therefore, I cannot see why valid time should never
participate in the key. I do agree that other
explicit attributes may also participate in it.

* TSQL2 may support periodic time.
In my approach, this is possible without extra effort
(though to a limited extent), it only requires that more
than one time stamps should be allowed.
(Is it possible to be supplied with a justification
for restricting to only one valid time?
Clearly, there must be some reasonable explanation
which I am missing.)
If we want to fully support periodic time,
then the problem is similar to supporting any type of
periodic data in the snapshot relational model.
Examples:
(i) Model the periodicity of the 11 year sun spots in the
snapshot relational model.

(ii) Model in the snapshot relational model that cos(x) = 1 for all x =
2*k*3.14, for all integers k.

* Ungrouped Complete
I agree for the reasons I explained above.

* Fabio's history variables
If I understand clearly, this also relates with nested
models, for which I have replied.

Once again, I want to make it clear that I do not object
to grouped models but I can see this only in a consistent
SQL3 extension to SQL2.

Some more questions I would like to raise,
if all people agree. (Perhaps, some decision has
already been made, in which case I do apologise
for coming to this issue again.)

1. Have you decided about the granularity
of the valid time? Should it be a default
system provided or not? If yes,
why should one have the capability to declare
the granularity of a user defined time and not that
of a valid stamp time?

2. Should valid time be implicit?
If yes is there any advantage?
For example, in this case, the following will probably
occur:
(i) The valid time will have to be declared to the
DBMS in some special way.
(ii) The attribute name will probably be default,
in that it will be provided by the system.
Do we really want it or do we
unduly restrict the user choices?
(iii) Though an implicit attribute, the user will
explicitly record valid time.
(iv) Is there reaaly some well justified reason
for having to distinguish between
explicit and implicit attributes?

3. Is there a well justified reason for having
to make a distinction between user defined time and
valid time stamp?

4.If I understood clearly, no decision
has been made about a distinct 'WHEN' clause.
If the issue is raised, I would like to provide
a justification, in my opinion, for which we should not
support it.

I apologise again because for many issues I am
influenced by my approach. However, I shall be happy
to change idea, if a satisfactory justification
of the opposite opinion is given.

PLEASE NOTE THAT YOU CONTINUE
SENDING EMAILS TO ELIOP

Regards,
Nikos