temporary databases
Mikito Harakiri
http://dimlab.usc.edu/cs599-2000/
I don't understand the elementary: what is the subject of temporal
databases? Suppose we have a history table. Are there any queries that
can't be expressed in standard SQL?
Isn't interval just defined by a couple of trivial predicates t1<time
and time<t2? What is the purpose of introducing interval datatype if
it's not closed under union operator?
According to Date/McGoveran a database is a repository of valid facts.
Those facts are valid in *absolute* sence. We either say "Smith is
teaching CS215 class" or say it more presizely "Smith was teaching
CS215 class startind at 9am 10-Dec-1990 and ended at 9am 10-Mar-1991".
If the second date is open-ended, it's just incomplete information.
Wouldn't putting a null better than inventing some "transaction time"?
Snodgrass p299: "We learn on Jan 26 that Exa bought the flat not on
Jan 10 as initially thought, not on Jan3 as later corrected, but on
Jan 5" -- only abstraction-challenged person can consider paragraph
like that seriously. Just fix the data, not the model!
Given a table of intervals answering a query what intervals cover that
point is not trivial. Is this the only issue we could reduce temporal
databases subject to?
Clifford Heath <cjh_n...@managesoft.com> wrote in message news:<3CED8250...@managesoft.com>...
> This sort of database feature is useful of course, but (1) isn't necessarily
> the most efficient way to handle historical queries (need to follow the log
> chain back possibly many steps), (2) doesn't make it easy to identify
> which history should be kept past log backup interval(s) and (3) prevents
> revision of incorrect histories.
>
> There's still a need in many cases to model history explicitly.
Ruud de Koter
Mikito Harakiri wrote:
>
> After spending some time at
>
> http://dimlab.usc.edu/cs599-2000/
>
> I don't understand the elementary: what is the subject of temporal
> databases? Suppose we have a history table. Are there any queries that
> can't be expressed in standard SQL?
>
> Isn't interval just defined by a couple of trivial predicates t1<time
> and time<t2? What is the purpose of introducing interval datatype if
> it's not closed under union operator?
>
> According to Date/McGoveran a database is a repository of valid facts.
> Those facts are valid in *absolute* sence. We either say "Smith is
> teaching CS215 class" or say it more presizely "Smith was teaching
> CS215 class startind at 9am 10-Dec-1990 and ended at 9am 10-Mar-1991".
> If the second date is open-ended, it's just incomplete information.
> Wouldn't putting a null better than inventing some "transaction time"?
>
> Snodgrass p299: "We learn on Jan 26 that Exa bought the flat not on
> Jan 10 as initially thought, not on Jan3 as later corrected, but on
> Jan 5" -- only abstraction-challenged person can consider paragraph
> like that seriously. Just fix the data, not the model!
Never thought about the legal implications of knowing something at a given
moment? Suppose someone is going to sue *you* for not saying that Exa owned the
flat on Jan 7. It would be very interesting to know what you exactly knew at the
moment you made the statement.
Still think this is a theoretical situation? Go talk with a real customer in the
insurance business, or stock exchange.
Just as an aside: be a man, write under your own name.
Regards,
Ruud.
>
> Given a table of intervals answering a query what intervals cover that
> point is not trivial. Is this the only issue we could reduce temporal
> databases subject to?
>
> Clifford Heath <cjh_n...@managesoft.com> wrote in message news:<3CED8250...@managesoft.com>...
> > This sort of database feature is useful of course, but (1) isn't necessarily
> > the most efficient way to handle historical queries (need to follow the log
> > chain back possibly many steps), (2) doesn't make it easy to identify
> > which history should be kept past log backup interval(s) and (3) prevents
> > revision of incorrect histories.
> >
> > There's still a need in many cases to model history explicitly.
--
--------------------------------------------------------------------------------------
Ruud de Koter HP OpenView Software Business Unit
Senior Software Engineer IT Service Management Operation
Telephone: +31 (20) 514 15 89 Van Diemenstraat 200
Telefax : +31 (20) 514 15 90 PO Box 831
Telnet : 547 - 1589 1000 AV Amsterdam, the Netherlands
Email : ruud_d...@hp.com
internet: http://www.openview.hp.com/products/servicedesk
intranet: http://ovweb.bbn.hp.com/itservicemanager
--------------------------------------------------------------------------------------
Mikito Harakiri
> Never thought about the legal implications of knowing something at a given
> moment? Suppose someone is going to sue *you* for not saying that Exa owned the
> flat on Jan 7. It would be very interesting to know what you exactly knew at the
> moment you made the statement.
OK, if lawyers are involved I give up. However, letting layers to
define database model maybe is not such a good idea.
> Still think this is a theoretical situation? Go talk with a real customer in the
> insurance business, or stock exchange.
I assume that the legal implications could be triggered by wrong
transactions only. After discovering wrong transactions, a business
runs compensating transactions in order to prevent any legal actions.
Compensating transaction might be as simple as just returning
customer's credit. Compensating transaction can refer to wrong
transaction, so that a business would still be able to query
transaction history for errors, or what did they know at the moment,
if you like.
Anyway, wrong transactions are supposed to be exception, rather than
norm. Therefore, "transaction time" is just unsound abstraction
level. We learned from transaction theory that it's a good idea to
eliminate combinatorial state
explosion when transaction participants are allowed to see
each-other's intermediate states (no isolation).
"Transaction time" is increasing modelling complexity level instead of
decreasing it, because you consider much more states.
> Just as an aside: be a man, write under your own name.
I forwarded your suggestion to Samuel L. Clemens.
Ruud de Koter
Mikito Harakiri wrote:
>
> Ruud de Koter <ruud_d...@hp.com> wrote in message news:<3CF1E5B7...@hp.com>...
>
> > Never thought about the legal implications of knowing something at a given
> > moment? Suppose someone is going to sue *you* for not saying that Exa owned the
> > flat on Jan 7. It would be very interesting to know what you exactly knew at the
> > moment you made the statement.
>
> OK, if lawyers are involved I give up. However, letting layers to
> define database model maybe is not such a good idea.
I wouldn't say lawyers should define database models either. At the same time I
know lawyers are a part of the real world in which your database has to
function.
>
> > Still think this is a theoretical situation? Go talk with a real customer in the
> > insurance business, or stock exchange.
>
> I assume that the legal implications could be triggered by wrong
> transactions only. After discovering wrong transactions, a business
> runs compensating transactions in order to prevent any legal actions.
> Compensating transaction might be as simple as just returning
> customer's credit. Compensating transaction can refer to wrong
> transaction, so that a business would still be able to query
> transaction history for errors, or what did they know at the moment,
> if you like.
>
> Anyway, wrong transactions are supposed to be exception, rather than
> norm. Therefore, "transaction time" is just unsound abstraction
> level.
I happen to have some practical experience working for pension funds. These
institutions certainly do not see mistakes being made as 'wrong transactions'.
Just think about one example: a baby being born. You send around lots of cards,
and whom do you forget: the pension fund. Only when you receive your annual
summary of pension rights, you realize your kid 's not on it. And then, only a
hypothetical eight months later, you let the fund know you have a child.
Now, it seems pension funds have long ago realized this is the way people go
about their lives. They insist that their application allow them to register the
fact that they were informed on November,8 that a child was born on February,
15. They will register that the child was born in February, and grant it
whatever rights are appropriate from that date on. They will also register they
only knew this in November.
This is important because only this kind of registration will allow them to come
up with a good bookkeeping overview regarding the past. Say you want to know the
fund's oblivgation as they were July, 1. In this computation the hypothetical
child should not turn up, as its existence was only known to the fund in
November. This also applies when the computation is executed in December.
So, in short, this kind of registration is useful for important real life
issues. It might not be your way of looking at life (nor is it mine), but these
things are important for insurance companies and institutions like that.
> We learned from transaction theory that it's a good idea to
> eliminate combinatorial state
> explosion when transaction participants are allowed to see
> each-other's intermediate states (no isolation).
>
> "Transaction time" is increasing modelling complexity level instead of
> decreasing it, because you consider much more states.
>
> > Just as an aside: be a man, write under your own name.
>
> I forwarded your suggestion to Samuel L. Clemens.
Who wrote fiction, didn't he?
Regards,
Ruud de Koter.
Jeff Zucker
>
> Ruud de Koter <ruud_d...@hp.com> wrote in message news:<3CF1E5B7...@hp.com>...
>
>>Never thought about the legal implications of knowing something at a given
>>moment? Suppose someone is going to sue *you* for not saying that Exa owned the
>>flat on Jan 7. It would be very interesting to know what you exactly knew at the
>>moment you made the statement.
>
> Anyway, wrong transactions are supposed to be exception, rather than
> norm.
Except if the system learns as it goes. Take for example a hospital's
set of nursing protocols -- documents that say how the staff should
handle nursing situation X. The protocols are *supposed* to change over
time as medical practices are improved. Ok you go to the hospital in
January and have your X removed. The nurse puts in the record "patient
treated with protocol X". Six months later there's a medical discovery
and the protocol for X is changed. A year later you find an infection
and you want to know how your original case was treated (for medical
reasons, not for legal reasons :-)). In this case you want to know what
the protocol was in January, but you also want the current protocol to
be different from that protocol. The transaction was not wrong in
January, it was the best that was known at that time, but still the
record needs to be updated and the history kept. In actual usage the
nurse will probably print out January's protocol and put it in your
chart at the time of your care, but the hospital admins will probably
want a fully searchable db of what the protocol was at differing times.
> After discovering wrong transactions, a business
> runs compensating transactions in order to prevent
> any legal actions.
This kind of reduction of all thought to mental accounting gets people
in trouble in the real world and in database design. What on earth would
a "compensating transaction" be in the medical context? Financial
transactions are often given as the prime example of a "transaction"
because they are easy to explain, but the analogy breaks down when the
datbase needs to model more complex events.
--
Jeff
Mikito Harakiri
> Mikito Harakiri wrote:
> >
>
> > Ruud de Koter <ruud_d...@hp.com> wrote in message news:<3CF1E5B7...@hp.com>...
> >
> >>Never thought about the legal implications of knowing something at a given
> >>moment? Suppose someone is going to sue *you* for not saying that Exa owned the
> >>flat on Jan 7. It would be very interesting to know what you exactly knew at the
> >>moment you made the statement.
> >
> > Anyway, wrong transactions are supposed to be exception, rather than
> > norm.
>
> Except if the system learns as it goes. Take for example a hospital's
> set of nursing protocols -- documents that say how the staff should
> handle nursing situation X. The protocols are *supposed* to change over
> time as medical practices are improved.
There is no wrong transactions in that context (except malpractice),
indeed.
> Ok you go to the hospital in
> January and have your X removed. The nurse puts in the record "patient
> treated with protocol X". Six months later there's a medical discovery
> and the protocol for X is changed. A year later you find an infection
> and you want to know how your original case was treated (for medical
> reasons, not for legal reasons :-)).
The time when discovery has been made, and, more important, when
substitute protocol Y got certification, both could be ignored. (We
assume that nobody would sue the hospital in case when Y has been
certified already, but the hospital was slow in adopting Y). We record
the time when protocol Y is adopted in hospital's practice, instead.
> In this case you want to know what
> the protocol was in January, but you also want the current protocol to
> be different from that protocol. The transaction was not wrong in
> January, it was the best that was known at that time, but still the
> record needs to be updated and the history kept. In actual usage the
> nurse will probably print out January's protocol and put it in your
> chart at the time of your care, but the hospital admins will probably
> want a fully searchable db of what the protocol was at differing times.
I don't see why your example requires Transaction Time. We have:
Table Protocols (
protocol varchar2,
effective date,
expired date
)
Table Treatments (
patientName varchar2,
symptom varchar2,
protocol foreign key refers to protocol id,
applied date
)
All these timestamps seems to be "valid times" to me. A hospital admin
can query any history of patient's treatments, and verify that no
protocol has been applied after its expiration date.
> > After discovering wrong transactions, a business
> > runs compensating transactions in order to prevent
> > any legal actions.
>
> This kind of reduction of all thought to mental accounting gets people
> in trouble in the real world and in database design. What on earth would
> a "compensating transaction" be in the medical context? Financial
> transactions are often given as the prime example of a "transaction"
> because they are easy to explain, but the analogy breaks down when the
> datbase needs to model more complex events.
A person get shot. Compensating transaction would retrieve the bullet
and do some healing.
Seriously, the discussion here is about significance of "transaction
time". Can we formally define the difference between "transaction
time" and "valid time"? Do we need any special temporal operators that
allows one to query the intervals between valid time and transaction
time?
Mikito Harakiri
> Just think about one example: a baby being born. You send around lots of cards,
> and whom do you forget: the pension fund. Only when you receive your annual
> summary of pension rights, you realize your kid 's not on it. And then, only a
> hypothetical eight months later, you let the fund know you have a child.
>
> Now, it seems pension funds have long ago realized this is the way people go
> about their lives. They insist that their application allow them to register the
> fact that they were informed on November,8 that a child was born on February,
> 15. They will register that the child was born in February, and grant it
> whatever rights are appropriate from that date on. They will also register they
> only knew this in November.
>
> This is important because only this kind of registration will allow them to come
> up with a good bookkeeping overview regarding the past. Say you want to know the
> fund's oblivgation as they were July, 1. In this computation the hypothetical
> child should not turn up, as its existence was only known to the fund in
> November. This also applies when the computation is executed in December.
>
> So, in short, this kind of registration is useful for important real life
> issues. It might not be your way of looking at life (nor is it mine), but these
> things are important for insurance companies and institutions like that.
Both times make sence in the retirement fund world? Fine:
table BirthCertificates (
person varchar2,
DOB date,
received date
)
and "received" could be called "transaction time" if you like. Still,
I don't quite understand how this concept can be leveraged. Query like
TRANSACTIONTIME
SELECT * FROM BirthCertificates;
seems like a joke rather than improvement to me. (AFAIK even more
"orthogonal" time dimensions were proposed).
In
http://www.timeconsult.com/Literature/diss.pdf
Vertical Temporal Anomaly and Coalescing are mentioned:
<quote>
A user wants to view all data about an employee as a unit, for
example, in a single tuple. As we
have seen, the vertical temporal anomaly cannot be omitted in 1NF
temporal data models. The
consequence is that the overall time a real world entity plays a
specifc role, for example, when the
entity is an employee, is split up in several smaller time periods,
due to the changes in attribute
values. Often, however, it is of interest during which time period or
how long an employee was
working in a company. In order to calculate maximal time intervals for
such tuples, a special
operation { coalescing { was proposed [NA93, Sar93, B oh94, Sno95b,
SBJS96b].
</quote>
Is the author aware of grouping and transitive closure?
select max(enddate)-min(startdate) from emp where ename = 'SCOTT'
connect by startdate= prior enddate -- navigating from 'future' back
to 'past'
start with enddate between SYSDATE and '1-JAN-9999'
In short, I agree that the real world (and especially accounting
practices;-) seems to be too complicated to fit into a simplistic
model. But temporal databases look not as a solution, but rather as a
speculative metascience to me.
Bob Badour
Queries are only one function of a DBMS.
For all practical purposes, all intervals within a DBMS represent a set of
discrete points. As a result, a tuple that has an interval attribute
represents a set of discrete propositions. In the absence of any interval
attributes, a tuple represents a single proposition.
This affects the integrity function, the manipulation function etc.
Consider an HOURS relation with a single interval column representing the
hours of operation and an APPT relation with an interval column representing
the time and duration of appointments plus other columns identifying the
customer and reason etc.
One would like to declare an integrity constraint that the discrete points
represented by any APPT interval are a subset of the points represented by
the HOURS relation. Likewise, one would like to declare an integrity
constraint that the discrete points represented by all APPT intervals are
unique within APPT. One would like uniqueness within the discrete points of
time represented by the HOURS relation too.
These constraints are very similar to a foreign key constraint and a
candidate key constraint, but they are subtly different due to the
intervals. For instance, a traditional foreign key constraint would require
that the HOURS relation have the exact intervals in the APPT relation. A
traditional candidate key constraint would not prevent overlapping intervals
and would thus not enfore uniqueness among the discrete points represented
by the intervals.
I recently attended a fascinating seminar by Chris Date on the subject. He
is holding the same seminar the week of June 10th in Rome if you are in
Europe and can afford to go. (http://www.technologytransfer.it/en/seminars/)
He mentioned that he is writing a book on the subject that should hit store
shelves Q4.
Cheers,
Bob
Mikito Harakiri
Welcome back, Bob.
> For all practical purposes, all intervals within a DBMS represent a set of
> discrete points. As a result, a tuple that has an interval attribute
> represents a set of discrete propositions. In the absence of any interval
> attributes, a tuple represents a single proposition.
Interval represented as a set of discrete points is just one
possibility, and probably not the best one. Given an interval defined
by its 2 boundary points and discretisation step, one can always query
all discrete points within the interval.
Therefore, I would represent an interval by just 2 timestamp columns.
It is unnecessary to consider it as a single object with 13(!)
operations.
> This affects the integrity function, the manipulation function etc.
>
> Consider an HOURS relation with a single interval column representing the
> hours of operation and an APPT relation with an interval column representing
> the time and duration of appointments plus other columns identifying the
> customer and reason etc.
>
> One would like to declare an integrity constraint that the discrete points
> represented by any APPT interval are a subset of the points represented by
> the HOURS relation. Likewise, one would like to declare an integrity
> constraint that the discrete points represented by all APPT intervals are
> unique within APPT. One would like uniqueness within the discrete points of
> time represented by the HOURS relation too.
More simple than that:
1. Start and end of an appointment interval both are within HOURS.
2. Neither start nor end point of an appointment falls within some
other appointment.
One motivation to consider an interval as a set of discrete points
might be the fact that sets and subsets are a sweetheart of the
relational model. IMHO the discrete topology of time line is a wrong
model.
> These constraints are very similar to a foreign key constraint and a
> candidate key constraint, but they are subtly different due to the
> intervals. For instance, a traditional foreign key constraint would require
> that the HOURS relation have the exact intervals in the APPT relation. A
> traditional candidate key constraint would not prevent overlapping intervals
> and would thus not enfore uniqueness among the discrete points represented
> by the intervals.
Yes, traditional foreign key constraints emphasize equality, and we
would like to focus on the constraints with inequalities, instead.
However, I fail to see any temporary specifics in that.
> I recently attended a fascinating seminar by Chris Date on the subject. He
> is holding the same seminar the week of June 10th in Rome if you are in
> Europe and can afford to go. (http://www.technologytransfer.it/en/seminars/)
>
> He mentioned that he is writing a book on the subject that should hit store
> shelves Q4.
Is any of his recearch published (in a series of articles)? Given that
Snodgrass book is collecting dust somewhere on my bookshelf, I would
be very careful this time. Even if the author is Chris Date.
Bob Badour
news:bdf69bdf.02060...@posting.google.com...
> "Bob Badour" <bba...@golden.net> wrote in message
news:<4fXL8.25117$H%2.196...@radon.golden.net>...
>
> Welcome back, Bob.
Thanks! It's good to be back!
> > For all practical purposes, all intervals within a DBMS represent a set
of
> > discrete points. As a result, a tuple that has an interval attribute
> > represents a set of discrete propositions. In the absence of any
interval
> > attributes, a tuple represents a single proposition.
>
> Interval represented as a set of discrete points is just one
> possibility, and probably not the best one.
Well, given that Date et al. have developed interval type generator extended
short-cuts for dealing with referential integrity, relational operators etc.
and given that they have demonstrated the familiar integrity constraints,
operators etc. are simply special cases of the new ones, one has quite a
hurdle to clear to demonstrate that the choice is not a good one. While one
might pretend that an interval contains an infinite set of points, until
humans develop infinite artifacts it's a moot point.
> Given an interval defined
> by its 2 boundary points and discretisation step, one can always query
> all discrete points within the interval.
>
> Therefore, I would represent an interval by just 2 timestamp columns.
Time intervals are not the only interval types. Two timestamp columns hardly
qualify as a general solution to the problem, and in a theory newsgroup all
possible representations of a type are equally valid.
> It is unnecessary to consider it as a single object with 13(!)
> operations.
I am sorry. Do you suffer from triskaidekaphobia?
> > This affects the integrity function, the manipulation function etc.
> >
> > Consider an HOURS relation with a single interval column representing
the
> > hours of operation and an APPT relation with an interval column
representing
> > the time and duration of appointments plus other columns identifying the
> > customer and reason etc.
> >
> > One would like to declare an integrity constraint that the discrete
points
> > represented by any APPT interval are a subset of the points represented
by
> > the HOURS relation. Likewise, one would like to declare an integrity
> > constraint that the discrete points represented by all APPT intervals
are
> > unique within APPT. One would like uniqueness within the discrete points
of
> > time represented by the HOURS relation too.
>
> More simple than that:
> 1. Start and end of an appointment interval both are within HOURS.
Should I assume you prefer not to have a short-cut for declaring foreign key
constraints?
> 2. Neither start nor end point of an appointment falls within some
> other appointment.
Should I assume you prefer not to have a short-cut for declaring candidate
keys? Given the need for logical identity, this is a tough one to swallow.
> One motivation to consider an interval as a set of discrete points
> might be the fact that sets and subsets are a sweetheart of the
> relational model.
Whether the set is discrete and finite or whether the set is continuous and
infinite, it would be just as much a set. Personal motivation is hardly a
worthy topic for this newsgroup.
> IMHO the discrete topology of time line is a wrong
> model.
Build me an infinite computer, and we'll talk about making it continuous
instead.
> > These constraints are very similar to a foreign key constraint and a
> > candidate key constraint, but they are subtly different due to the
> > intervals. For instance, a traditional foreign key constraint would
require
> > that the HOURS relation have the exact intervals in the APPT relation. A
> > traditional candidate key constraint would not prevent overlapping
intervals
> > and would thus not enfore uniqueness among the discrete points
represented
> > by the intervals.
>
> Yes, traditional foreign key constraints emphasize equality, and we
> would like to focus on the constraints with inequalities, instead.
> However, I fail to see any temporary specifics in that.
Intervals are useful for temporal databases, but they are not specific to
time. One can have an interval of any enumerable, fully ordered type.
> > I recently attended a fascinating seminar by Chris Date on the subject.
He
> > is holding the same seminar the week of June 10th in Rome if you are in
> > Europe and can afford to go.
(http://www.technologytransfer.it/en/seminars/)
> >
> > He mentioned that he is writing a book on the subject that should hit
store
> > shelves Q4.
>
> Is any of his recearch published (in a series of articles)? Given that
> Snodgrass book is collecting dust somewhere on my bookshelf, I would
> be very careful this time. Even if the author is Chris Date.
I believe he is publishing in book form, and I believe the book should be
available Q4. If you cannot afford to purchase it yourself, I suggest you
recommend it to your library. Certainly, every university library should
have a copy or two when it comes out.
Jonathan Leffler
>>>I recently attended a fascinating seminar by Chris Date on the subject.
[...]
>>>He mentioned that he is writing a book on the subject that should hit
>>>store shelves Q4.
>>>
>>Is any of his recearch published (in a series of articles)? Given that
>>Snodgrass book is collecting dust somewhere on my bookshelf, I would
>>be very careful this time. Even if the author is Chris Date.
>
> I believe he is publishing in book form, and I believe the book should be
> available Q4. If you cannot afford to purchase it yourself, I suggest you
> recommend it to your library. Certainly, every university library should
> have a copy or two when it comes out.
Unless I'm completely misremembering, it was cited in a Morgan Kaufmann
Press (http://www.mkp.com/) catalog with an autumnal publication date,
but I couldn't find it on their web site when I went to check before
writing this response.
-=JL=-
Mikito Harakiri
> > > For all practical purposes, all intervals within a DBMS represent a set
> of
> > > discrete points. As a result, a tuple that has an interval attribute
> > > represents a set of discrete propositions. In the absence of any
> interval
> > > attributes, a tuple represents a single proposition.
> >
> > Interval represented as a set of discrete points is just one
> > possibility, and probably not the best one.
>
> Well, given that Date et al. have developed interval type generator extended
> short-cuts for dealing with referential integrity, relational operators etc.
> and given that they have demonstrated the familiar integrity constraints,
> operators etc. are simply special cases of the new ones, one has quite a
> hurdle to clear to demonstrate that the choice is not a good one. While one
> might pretend that an interval contains an infinite set of points, until
> humans develop infinite artifacts it's a moot point.
Why so? Real Analysis is an established theory. Real axis is a dense
uncountable ordered set of points.
> > Given an interval defined
> > by its 2 boundary points and discretisation step, one can always query
> > all discrete points within the interval.
> >
> > Therefore, I would represent an interval by just 2 timestamp columns.
>
> Time intervals are not the only interval types. Two timestamp columns hardly
> qualify as a general solution to the problem, and in a theory newsgroup all
> possible representations of a type are equally valid.
If I store just 2 interval boundary points, I have enough data to
deduce any other facts about the interval.
On the other hand, if you consider an interval as a set of _some
selected_ points it contains, not only your representation is
unnormalized, but it also ambiguosly defined.
> > > Consider an HOURS relation with a single interval column representing
> the
> > > hours of operation and an APPT relation with an interval column
> representing
> > > the time and duration of appointments plus other columns identifying the
> > > customer and reason etc.
> > >
> > > One would like to declare an integrity constraint that the discrete
> points
> > > represented by any APPT interval are a subset of the points represented
> by
> > > the HOURS relation. Likewise, one would like to declare an integrity
> > > constraint that the discrete points represented by all APPT intervals
> are
> > > unique within APPT. One would like uniqueness within the discrete points
> of
> > > time represented by the HOURS relation too.
> >
> > More simple than that:
> > 1. Start and end of an appointment interval both are within HOURS.
>
> Should I assume you prefer not to have a short-cut for declaring foreign key
> constraints?
>
> > 2. Neither start nor end point of an appointment falls within some
> > other appointment.
>
> Should I assume you prefer not to have a short-cut for declaring candidate
> keys? Given the need for logical identity, this is a tough one to swallow.
Given interval representation by its two end points, I'm simply unable
to specify containment of one interval within the other as foreign key
integrity constraint. I have 4 inequality constraints instead. Are my
inequality constraints in any way inferior to referential integrity?
> > One motivation to consider an interval as a set of discrete points
> > might be the fact that sets and subsets are a sweetheart of the
> > relational model.
>
> Whether the set is discrete and finite or whether the set is continuous and
> infinite, it would be just as much a set. Personal motivation is hardly a
> worthy topic for this newsgroup.
Yes, but in order to answer queries about sets Relational Databases
necessarily store the whole set. Therefore, the set can't be
continuous, or even countable indeed. Databases that can answer
queries about infinite domains defined by some equations rather than
simple points enumeration are called Constraint Databases.
> > IMHO the discrete topology of time line is a wrong
> > model.
>
> Build me an infinite computer, and we'll talk about making it continuous
> instead.
Again, matematicians were comfortably working with Real Axis as a
continous entity for ages. They didn't ask for infinite supply of
paper, however. Could you reconsider your requirements?
Bob Badour
news:<B5fM8.25168$hW7.20...@radon.golden.net>...
> > > > For all practical purposes, all intervals within a DBMS represent a
set
> > of
> > > > discrete points. As a result, a tuple that has an interval attribute
> > > > represents a set of discrete propositions. In the absence of any
> > interval
> > > > attributes, a tuple represents a single proposition.
> > >
> > > Interval represented as a set of discrete points is just one
> > > possibility, and probably not the best one.
> >
> > Well, given that Date et al. have developed interval type generator
extended
> > short-cuts for dealing with referential integrity, relational operators
etc.
> > and given that they have demonstrated the familiar integrity
constraints,
> > operators etc. are simply special cases of the new ones, one has quite a
> > hurdle to clear to demonstrate that the choice is not a good one. While
one
> > might pretend that an interval contains an infinite set of points, until
> > humans develop infinite artifacts it's a moot point.
>
> Why so? Real Analysis is an established theory. Real axis is a dense
> uncountable ordered set of points.
How do we represent all of those points in a computer system? If you want to
extend the work of Date et al. to include continua, by all means do.
> > > Given an interval defined
> > > by its 2 boundary points and discretisation step, one can always query
> > > all discrete points within the interval.
> > >
> > > Therefore, I would represent an interval by just 2 timestamp columns.
> >
> > Time intervals are not the only interval types. Two timestamp columns
hardly
> > qualify as a general solution to the problem, and in a theory newsgroup
all
> > possible representations of a type are equally valid.
>
> If I store just 2 interval boundary points, I have enough data to
> deduce any other facts about the interval.
Actually, one must also know the type of interval, which of course includes
all the operations on the interval type and on the type from which it is
derived.
> On the other hand, if you consider an interval as a set of _some
> selected_ points it contains, not only your representation is
> unnormalized, but it also ambiguosly defined.
I disagree with both statements.
Yes. Very inferior. I suggest you read what Date et al. have to say on the
subject and then form your opinion.
> > > One motivation to consider an interval as a set of discrete points
> > > might be the fact that sets and subsets are a sweetheart of the
> > > relational model.
> >
> > Whether the set is discrete and finite or whether the set is continuous
and
> > infinite, it would be just as much a set. Personal motivation is hardly
a
> > worthy topic for this newsgroup.
>
> Yes, but in order to answer queries about sets Relational Databases
> necessarily store the whole set.
You are forgetting physical independence. A relational database need not
store anything unecessarily.
> Therefore, the set can't be
> continuous, or even countable indeed. Databases that can answer
> queries about infinite domains defined by some equations rather than
> simple points enumeration are called Constraint Databases.
When constraint databases devise a representation for all of the points they
describe, let me know. I won't hold my breath.
> > > IMHO the discrete topology of time line is a wrong
> > > model.
> >
> > Build me an infinite computer, and we'll talk about making it continuous
> > instead.
>
> Again, matematicians were comfortably working with Real Axis as a
> continous entity for ages. They didn't ask for infinite supply of
> paper, however. Could you reconsider your requirements?
No.