Is mysql a RDBMS ?

#61

Heikki Tuuri wrote:

Quote:

Lauri,

thank you! We get input to this discussion from a person who really knows
practical database applications.

"Lauri Pietarinen" <lauri.pietarinen (AT) atbusiness (DOT) com> kirjoitti viestissä
news:bire7g$md8$1 (AT) nyytiset (DOT) pp.htv.fi...

Take the standard S, SP and P -tables. I want to hide the joins from
the user so I create a view:

CREATE VIEW V_S_SP_P
( S#, SNAME, QTY, P#, PNAME)
AS
SELECT S.S#, S.SNAME, SP.QTY, P.P#, P.PNAME
FROM S, SP, P
WHERE S.S# = SP.S# AND
SP.P# = P.P#;

[snipped]

Quote:

Hmm... there is at least one additional reason not to provide such a view to
programmers. Since it is denormalized, they might try to update the
attributes of a supplier for just one row, receiving an obscure error
message about the non-updateteability of the view. Or, they could try to
insert into the view.

Another reason is that it is not an outer join. There may be parts which
currently have no supplier. Users might be misled to thinking that the above
query gives all parts, not just the parts supplied by somebody. Of course,
these are well-known problems of a denormalized database.

OK, that's a fair criticisim. I'll give you a better example at a later

time...

Quote:

2) worry about bad performance
So they end up coding the joins and
exists themselves.

My (non scientific) claim is that because users and
DBMS-builders are used to thinking in terms of
(SQL-)bags, they don't even come to consider (e.g.) the usefulnes
of hiding complexity in views, at least not to the
extent that it is possible.

[snip]

Quote:

I do not see how a mathematical relation language would make the
optimization procedure above easier than a multiset language. This is a
typical example of determining the join order of tables in a nested loop
join. Looks like it makes no difference in the optimization procedure
whether the user formulates his query in a multiset language or a
mathematical relation language. That has been my impression since studying
this a bit a couple of years ago. Chris Date and some others claim the
opposite, but they should provide practical evidence.

I think one has to take into consideration the commercial and

sociological dimensions of this
issue.

Consider:

- SQL is defined in mid 70's and duplicates are allowed
(in fact there is no concept of primary keys, unique indexes
do exist).

- the keyword DISTINCT is provided so that duplicates
can be eliminated

- however, users of SQL (e.g. DB2) are strongly discouraged
against using DISTINCT keyword (it causes a sort!)
because the optimiser folks have not had the time to recognize
DISTINCT no-ops.

- so users avoid DISTINCT, hence creating a negative feedback
loop to optimizer developing team that dedicates it's time to
other matters.

-- etc...

Which really means that the "set" part of SQL (which could have
been supported via users providing DISTINCT) is poortly supported
up to this day. Hence it is still a good idea to avoid its use
in modern SQL-databases.

So while "in theory" something might be easy to provide (good
distinct support in SQL), "in practice" this has not been the case. And
it is those practical systems we now have to live with.

On the other hand IF SQL had been designed with builtin DISTINCT
(or at least DISTINCT as default instead of ALL) from the start,
perhaps the optimizer-team would have been forced to think about
optimizing DISTINCT no-ops more forcefully.

It is fair to note that it would have required also more from users:
- they would have actually had to define all candidate keys
(with the added disk space they required - in the 70's and 80's)
- they would have had to understand what a relation is.
(which, taking into account that they were using RELATIONAL
DBMS's is maybe a fair requirement).

What would the users have got in exchange? They could have
used views more easily and relied on them being properly
optimised. I also believe there would have been less confusion
because the elimination of meaningless duplicates. Because
frankly, nobody in this newsgroup has been able to provide
a sound practical example in which duplicates would be
usefull.

regards,
Lauri Pietarinen

ps. I add once more a link describing a DBMS that
Hugh Darwen was involved with in the early 80's

www.mcjones.org/System_R/bs12.html

#62

"Bob Badour" <bbadour (AT) golden (DOT) net> wrote

Quote:

"Christopher Browne" <cbbrowne (AT) acm (DOT) org> wrote in message
news:bil8gl$anhtd$1 (AT) ID-125932 (DOT) news.uni-berlin.de...
[snip]
"Christianity has not been tried and found wanting; it has been found
difficult and not tried." -- G.K. Chesterton

The problem with the relational model is that it has never been found
difficult. It has been assumed difficult and not tried.

I would say that it was assumed difficult when System R was built. Oracle had to copy
the IBM standard to be commercially viable and the rest is history; i.e. we are
locked in (albeit only weakly) to SQL.

Actually, it possibly *was* difficult back in those days with the limited CPU and
memory available.

Regards
Paul Vernon
Business Intelligence, IBM Global Services

#63

"Paul Vernon" <paul.vernon (AT) ukk (DOT) ibmm.comm> wrote

Quote:

"Bob Badour" <bbadour (AT) golden (DOT) net> wrote in message
news:U9r3b.72$Br4.10705055 (AT) mantis (DOT) golden.net...
"Christopher Browne" <cbbrowne (AT) acm (DOT) org> wrote in message
news:bil8gl$anhtd$1 (AT) ID-125932 (DOT) news.uni-berlin.de...
[snip]
"Christianity has not been tried and found wanting; it has been found
difficult and not tried." -- G.K. Chesterton

The problem with the relational model is that it has never been found
difficult. It has been assumed difficult and not tried.

I would say that it was assumed difficult when System R was built. Oracle
had to copy
the IBM standard to be commercially viable and the rest is history; i.e.
we are
locked in (albeit only weakly) to SQL.

Actually, it possibly *was* difficult back in those days with the limited
CPU and
memory available.

Sadly, logical identity would have helped back in the days of limited CPU
and memory.

#64

"Mikito Harakiri" <mikharakiri (AT) ywho (DOT) com> wrote

Quote:

"Bob Badour" <bbadour (AT) golden (DOT) net> wrote in message
news:62g3b.53$Zb3.7844959 (AT) mantis (DOT) golden.net...
I find it interesting that you chose the example you chose. Ironically,
the
example clearly demonstrates how D&D's proposed type system helps with
view
updatability. Obviously, the type system must have some efficient method
to
convert from each declared possible representation to the supported
physical
representations and vice versa. If one considers Point with both polar and
cartesian possible representations, then the problem you mention above
simply disappears.

I find it interesting that you are bringing up the types. IMO, this example
demonstrates that type system is overrated. What is the benefit having a
base type Point and two descendants CartesianPoint and PolarPoint?

I have not made any such suggestion. I have specified a single Point
type with two representations and no descendants.

Quote:

OK, we
can clearly have a Point column used in some other relation, for example
Circle:

table Circle (
center Point,
radius Number
)

Therefore, some tuples would have CartesianPoint while the others PolarPoint
implementation - that's the idea, right?

It might be. The DBA might specify any physical representation from
which the dbms can derive the relation. The dbms might physically
represent circles as two pairs of opposing points on the circumference
of the circle. The dbms would derive the center point as the
intersection of the line segments joining each pair of points and the
radius as half the distance between either pair of points.

Quote:

I disagree.

You disagree with what? You have not accurately described my position
so I am not sure what you disagree with.

Quote:

When quering how many circle center points are positioned in
vicinity of some point, for example, we would like to know Polar coordinates
for all the tuples, no matter what internal representations they have.

The type system in TTM allows (even encourages) this. The DBA might
specify a polar physical representation, but this doesn't matter
because the dbms can derive the desired representation from any
physical representation.

Quote:

Likewise, it would be convenient to manipulate Cartesian representation
exclusively in some other queries.

Exactly, and the type system in TTM allows exactly this.

Quote:

Both queries could be answered if we have
2 views CartesianPointCircleCenter and PolarPointCircleCenter and just a
single representation for the Point.

This is a straw man. You have already remarked on the updatability
problem this causes and that the type system in TTM solves.

Quote:

I can't possibly see the
benefit of having heterogeneous list of Points in the database design.

At the logical level, there is no heterogeneous list. Whether it is
heterogeneous at the physical level is irrelevant to expressibility
and affects only performance.

Quote:

Returning to your idea that type system eliminates view update problem I
fail to see that too. If you are implying that Point would have "getter
"methods returning both Cartesian and Polar coordinates in it, then it's
essentially a procedural implementation of inverse view inside a type. Not
to mention that "fat" types are disgusting, as every time you introduce new
subtype you have to add more methods inside base type definition.

I am not implying anything that isn't stated explicitly in TTM. There
are no "getter" or "setter" methods in TTM. Getting and setting
focuses on the physical location of variables and not on the logical
meaning of values. In the TTM type system, adding a subtype requires
no change to its supertypes.

I can only suggest you study the type system described in TTM and then
reapply your imagination to my idea.

#65

"Bob Badour" <bbadour (AT) golden (DOT) net> wrote

Quote:

"Mikito Harakiri" <mikharakiri (AT) ywho (DOT) com> wrote

When quering how many circle center points are positioned in
vicinity of some point, for example, we would like to know Polar
coordinates
for all the tuples, no matter what internal representations they have.

The type system in TTM allows (even encourages) this. The DBA might
specify a polar physical representation, but this doesn't matter
because the dbms can derive the desired representation from any
physical representation.

How DBMS would derive the desired representation? Is

TYPE POINT
POSSREP XY_POINT { X NUMERIC, Y NUMERIC }
POSSREP POLAR_POINT { A NUMERIC, R NUMERIC }

a valid syntax? Now

POINT P := XY_POINT (1,1)

NUMERIC ANGLE := THE_A(P)

would the last operation succeed or throw an exception? In other words, when
specifying N possreps for a type is DBA also required to write doen N*(N-1)
conversion operators?

Quote:

Both queries could be answered if we have
2 views CartesianPointCircleCenter and PolarPointCircleCenter and just a
single representation for the Point.

This is a straw man. You have already remarked on the updatability
problem this causes and that the type system in TTM solves.

Once again, if manually programmed conversion operators are part of TTM,
then the TTM possrep conversion idea is similar to "on update" trigger where
DBA must code view updates.

Quote:

I can't possibly see the
benefit of having heterogeneous list of Points in the database design.

At the logical level, there is no heterogeneous list. Whether it is
heterogeneous at the physical level is irrelevant to expressibility
and affects only performance.

OK.

Quote:

I am not implying anything that isn't stated explicitly in TTM. There
are no "getter" or "setter" methods in TTM. Getting and setting
focuses on the physical location of variables and not on the logical
meaning of values.

Question. Are observer methods on logical or physical level?

#66

"Mikito Harakiri" <mikharakiri (AT) ywho (DOT) com> wrote

Quote:

"Bob Badour" <bbadour (AT) golden (DOT) net> wrote in message
news:cd3b3cf.0309030652.27cfaff1 (AT) posting (DOT) google.com...
"Mikito Harakiri" <mikharakiri (AT) ywho (DOT) com> wrote in message
news:<irr3b.16$D94.166 (AT) news (DOT) oracle.com>...
When quering how many circle center points are positioned in
vicinity of some point, for example, we would like to know Polar
coordinates
for all the tuples, no matter what internal representations they have.

The type system in TTM allows (even encourages) this. The DBA might
specify a polar physical representation, but this doesn't matter
because the dbms can derive the desired representation from any
physical representation.

How DBMS would derive the desired representation? Is

TYPE POINT
POSSREP XY_POINT { X NUMERIC, Y NUMERIC }
POSSREP POLAR_POINT { A NUMERIC, R NUMERIC }

a valid syntax? Now

POINT P := XY_POINT (1,1)

NUMERIC ANGLE := THE_A(P)

would the last operation succeed or throw an exception? In other words,
when
specifying N possreps for a type is DBA also required to write doen
N*(N-1)
conversion operators?

No, the DBA can choose one of the declared possible representations as a
reference representation and write 2*(N-1) conversions. One conversion from
each of the other possible representations to the reference representation
and vice versa. Of course, nothing prevents the DBA from writing additional
conversions for reasons of performance etc.

The dbms can derive any of the other conversions by nesting a conversion to
the reference representation inside a conversion to the target
representation.

Actually, theoretically, the DBA could write N conversions in a ring but
then the dbms might have to evaluate N-1 nested conversions to derive each
inverse conversion. One could think up any number of graphs and let the dbms
solve the shortest path problem, but I have no problem with the dbms
implementation requiring 2*(N-1) conversions.

Quote:

Both queries could be answered if we have
2 views CartesianPointCircleCenter and PolarPointCircleCenter and just
a
single representation for the Point.

This is a straw man. You have already remarked on the updatability
problem this causes and that the type system in TTM solves.

Once again, if manually programmed conversion operators are part of TTM,

The conversion from logical to physical must be specified somewhere.
However, involving the physical takes the subject beyond the scope of TTM,
which specifies the logical model.

Quote:

then the TTM possrep conversion idea is similar to "on update" trigger
where
DBA must code view updates.

I never claimed anything different. However, the DBA need only write the
conversions once for the domain and the dbms can apply them to any view.

Your original point still stands because not all operations are possible
representation operations and the dbms may have no convenient inverse. I
only pointed out that the type system in TTM effectively reduces the
occurance of the problem without fully eliminating it.

Quote:

I can't possibly see the
benefit of having heterogeneous list of Points in the database design.

At the logical level, there is no heterogeneous list. Whether it is
heterogeneous at the physical level is irrelevant to expressibility
and affects only performance.

OK.

I am not implying anything that isn't stated explicitly in TTM. There
are no "getter" or "setter" methods in TTM. Getting and setting
focuses on the physical location of variables and not on the logical
meaning of values.

Question. Are observer methods on logical or physical level?

All operations specify a public interface and have a private implementation.
Because the implementation is private, whether the implementation references
the physical level or the logical level is irrelevant to all but the
implementer and the security function. Actually, to be exact, the privacy of
the implementation is likewise a matter for the security function.

#67

On Sat, 30 Aug 2003 21:01:00 -0700, Mikito Harakiri wrote:

Quote:

Once again, predicate calculus and set theory are just 2 tiny subjects in
math. You are not implying that the all the rest of the math is
irrelevant, aren't you?

I am implying I need or want a full mathematical engine to be
able to administrate databases... so you comments sound unnecessarily
inflammatory.

--
_ Leandro GuimarÃ£es Faria Corsetti Dutra +41 (21) 648 11 34
/ \ http://br.geocities.com./lgcdutra/ +41 (78) 778 11 34
\ / Answer to the list, not to me directly! +55 (11) 5686 2219
/ \ Rate this if helpful: http://svcs.affero.net/rm.php?r=leandro

#68

On Wed, 03 Sep 2003 10:08:44 -0700, Mikito Harakiri wrote:

Quote:

the TTM possrep conversion idea is similar to "on update" trigger
where DBA must code view updates.

Only that a trigger is a procedural mess, while POSSREPs are
centrally declared. As Bob already pointed out.

--
_ Leandro GuimarÃ£es Faria Corsetti Dutra +41 (21) 648 11 34
/ \ http://br.geocities.com./lgcdutra/ +41 (78) 778 11 34
\ / Answer to the list, not to me directly! +55 (11) 5686 2219
/ \ Rate this if helpful: http://svcs.affero.net/rm.php?r=leandro

#69

On Wed, 03 Sep 2003 10:08:44 -0700, Mikito Harakiri wrote:

Quote:

In other words,
when specifying N possreps for a type is DBA also required to write doen
N*(N-1) conversion operators?

There is one stored representation, that can be used as the
hub in conversions...

--
_ Leandro GuimarÃ£es Faria Corsetti Dutra +41 (21) 648 11 34
/ \ http://br.geocities.com./lgcdutra/ +41 (78) 778 11 34
\ / Answer to the list, not to me directly! +55 (11) 5686 2219
/ \ Rate this if helpful: http://svcs.affero.net/rm.php?r=leandro

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Is mysql a RDBMS ?

comp.databases.theory comp.databases.theory

Re: Is mysql a RDBMS ? - 08-31-2003 , 03:41 PM

Re: Is mysql a RDBMS ? - 09-01-2003 , 04:00 PM

Re: Is mysql a RDBMS ? - 09-01-2003 , 07:11 PM

Re: types vs. views - 09-03-2003 , 09:52 AM

Re: types vs. views - 09-03-2003 , 12:08 PM

Re: types vs. views - 09-03-2003 , 12:51 PM

Re: Is mysql a RDBMS ? - 09-03-2003 , 06:21 PM

Re: types vs. views - 09-05-2003 , 06:01 PM

Re: types vs. views - 09-05-2003 , 06:02 PM