Re: Dreaming About Redesigning SQL

Carl Rosenberger wrote:

>Patrick K. O'Brien wrote:
>
>
>>As I've said before, I haven't focused on optimization yet. But I
>>remain convinced that an ODBMS with the proper object model can be
>>optimized as you describe. Does that make me an optimist? ;-)
>>
>>
>
>The proof is trivial:
>
>If you back up your object database system with a parallel fast
>relational system (or write that yourself), you should be able
>to achieve the same performance for relational queries as any
>relational database. :-)
>
>
>
Right, but who is the guy that is going to write an optimiser for code
like this:

def prod_cust_totals():
"""Return order summary information."""
db = _database
group = {}
for od in db.root['OrderDetail']:
key = (od.order.customer.name, od.product.name)
subtotal = od.quantity * od.product.price
group[key] = group.get(key, 0) + subtotal
totals = [(t, c, p) for ((c, p), t) in group.items()]
totals.sort()
pprint.pprint(totals)

Granted, you can convert the refereces into simple SQL-queries
"under the cover" but how are you going to automatically transform this
code into, say, code that starts navigation from the customer table.
You will have to use some declarative approach instead so, in effect,
you are just writing "another SQL". I understand that there are
some query languages (OQL?) available for object databases. Are these
actually used in applications? If not, why?

best regards,
Lauri Pietarinen

"Paul G. Brown" wrote in message
news:57da7b56.0310171152.1ca2e61e@posting.google.com...
> "Marshall Spight" wrote in message
news:...
> > "Paul G. Brown" wrote in message
news:57da7b56.0310121418.43aeaf21@posting.google.com...
> > > Note that relations are disambiguated by their *entire* definition,
not
> > > just their names.
> >
> > That doesn't seem right. Can you justify that argument?
> >
> > Given that you can construct a projection onto one attribute
> > each of two tables and join them, this is hard to believe.
> > Maybe this is just a syntax issue, though.
>
> Sorry. Missed your question. Saw Paul Vernon's reply.
>
> To clarify: the statement you quote is intended as a definition.
>
> "In a relational schema, relations are disambiguated by the combination
> of their names and the set of their attributes."

I guess you mean "and the set of their pairs" ?

I also guess that constraints don't make it into your disamiguation critera.
E.g.

RELATION Whatever ( X Integer, Y Integer )
CONSTRAINT (X >= 0);

RELATION Whatever ( X Integer, Y Integer )
CONSTRAINT (X >= 1);

Would not, in your view, be two valid, separate relations in a database?


> Which lets us do this kind of thing in a relational schema:
>
> RELATION Contains ( Domain Document, Range Word);
> RELATION Contains ( Domain Polygon, Range Point);
> RELATION Contains ( Domain Polygon, Range Polygon);
>
> RELATION Species ( Id ScientificName KEY, What Document, Where
Polygon );
>
> Note that this leads to some interesting formulations of common
predicates.
> (I'm kind of re-inventing Prolog here, only without the types, so bear
> with me.)
>
>
> Q1: For any two documents what words are found in both?
> RELATION Intersection (D1, D2, W1) AS
> ( Contains (D1=Domain, W1=Range)
> Contains (D2=Domain, W1=Range) );

I don't know Prolog, would it be possible to rewrite the above as a SQL (or
tutorial D) style statement (or is part of the point that it would not be
trivial to do so)?

Regards
Paul Vernon
Business Intelligence, IBM Global Services

"Paul G. Brown" wrote in message
news:57da7b56.0310171152.1ca2e61e@posting.google.com...
> "Marshall Spight" wrote in message
news:...
> > "Paul G. Brown" wrote in message
news:57da7b56.0310121418.43aeaf21@posting.google.com...
> > > Note that relations are disambiguated by their *entire* definition,
not
> > > just their names.
> >
> > That doesn't seem right. Can you justify that argument?
> >
> > Given that you can construct a projection onto one attribute
> > each of two tables and join them, this is hard to believe.
> > Maybe this is just a syntax issue, though.
>
> Sorry. Missed your question. Saw Paul Vernon's reply.
>
> To clarify: the statement you quote is intended as a definition.
>
> "In a relational schema, relations are disambiguated by the combination
> of their names and the set of their attributes."

I guess you mean "and the set of their pairs" ?

I also guess that constraints don't make it into your disamiguation critera.
E.g.

RELATION Whatever ( X Integer, Y Integer )
CONSTRAINT (X >= 0);

RELATION Whatever ( X Integer, Y Integer )
CONSTRAINT (X >= 1);

Would not, in your view, be two valid, separate relations in a database?


> Which lets us do this kind of thing in a relational schema:
>
> RELATION Contains ( Domain Document, Range Word);
> RELATION Contains ( Domain Polygon, Range Point);
> RELATION Contains ( Domain Polygon, Range Polygon);
>
> RELATION Species ( Id ScientificName KEY, What Document, Where
Polygon );
>
> Note that this leads to some interesting formulations of common
predicates.
> (I'm kind of re-inventing Prolog here, only without the types, so bear
> with me.)
>
>
> Q1: For any two documents what words are found in both?
> RELATION Intersection (D1, D2, W1) AS
> ( Contains (D1=Domain, W1=Range)
> Contains (D2=Domain, W1=Range) );

I don't know Prolog, would it be possible to rewrite the above as a SQL (or
tutorial D) style statement (or is part of the point that it would not be
trivial to do so)?

Regards
Paul Vernon
Business Intelligence, IBM Global Services

"Paul G. Brown" wrote in message
news:57da7b56.0310171152.1ca2e61e@posting.google.com...
> "Marshall Spight" wrote in message
news:...
> > "Paul G. Brown" wrote in message
news:57da7b56.0310121418.43aeaf21@posting.google.com...
> > > Note that relations are disambiguated by their *entire* definition,
not
> > > just their names.
> >
> > That doesn't seem right. Can you justify that argument?
> >
> > Given that you can construct a projection onto one attribute
> > each of two tables and join them, this is hard to believe.
> > Maybe this is just a syntax issue, though.
>
> Sorry. Missed your question. Saw Paul Vernon's reply.
>
> To clarify: the statement you quote is intended as a definition.
>
> "In a relational schema, relations are disambiguated by the combination
> of their names and the set of their attributes."

I guess you mean "and the set of their pairs" ?

I also guess that constraints don't make it into your disamiguation critera.
E.g.

RELATION Whatever ( X Integer, Y Integer )
CONSTRAINT (X >= 0);

RELATION Whatever ( X Integer, Y Integer )
CONSTRAINT (X >= 1);

Would not, in your view, be two valid, separate relations in a database?


> Which lets us do this kind of thing in a relational schema:
>
> RELATION Contains ( Domain Document, Range Word);
> RELATION Contains ( Domain Polygon, Range Point);
> RELATION Contains ( Domain Polygon, Range Polygon);
>
> RELATION Species ( Id ScientificName KEY, What Document, Where
Polygon );
>
> Note that this leads to some interesting formulations of common
predicates.
> (I'm kind of re-inventing Prolog here, only without the types, so bear
> with me.)
>
>
> Q1: For any two documents what words are found in both?
> RELATION Intersection (D1, D2, W1) AS
> ( Contains (D1=Domain, W1=Range)
> Contains (D2=Domain, W1=Range) );

I don't know Prolog, would it be possible to rewrite the above as a SQL (or
tutorial D) style statement (or is part of the point that it would not be
trivial to do so)?

Regards
Paul Vernon
Business Intelligence, IBM Global Services

"Lauri Pietarinen" wrote in message
news:bmv01r$u69$1@nyytiset.pp.htv.fi...
> >>Say if you had a large insurance company with, say, 10000 rules, would
> >>it *really* work?
> >>

Il'd say that would be exactly the kind of application that a pure
relational approach would *really* work very well indeed.

> >
> >Yes, absolutely. It would scale at least as well as it does today. It
would
> >be more manageable than it is today because the important business logic
> >would not be scattered among hundreds of applications. It would easily
adapt
> >to all situations. Why would it not?
> >
> >
> [snipped]
>
> That is all very clear, and that is how I have understood the goal.
> But, as they say, the devil lies in the details.
>
> The Versata product has been used to create a fairly large rule-based
> application at
> American Management Systems. I wonder if anybody knows anything about
> this application
> .
> See this IBM red book:
>
http://publib-b.boulder.ibm.com/Redb...6510.html?Open

Can't say I know about that app, but it's statistic of replacing 3.7 million
lines of (COBOL) code with 12,000 business rules is not a bad start to what
I suspect is possible with relational approaches (i.e. I think I'd be
surprised if those 12,000 couldn't be reduced by a factor of 10 in a purer
relational system). I note also that they say they got 85-90 percent of the
business logic coded as rules. Again, could be better but not a bad start.
We would need to kill the idea of 'batch processing' to get closer to 100% I
suggest (and getting rid of transactions would help also ;-). Their 98-99%
of the GUI being rule driven is good however.

Regards
Paul Vernon
Business Intelligence, IBM Global Services

"Paul Vernon" wrote in message
news:bn31fe$aie$1@gazette.almaden.ibm.com...
> "Lauri Pietarinen" wrote in message
> news:bmv01r$u69$1@nyytiset.pp.htv.fi...
> > >>Say if you had a large insurance company with, say, 10000 rules, would
> > >>it *really* work?
> > >>
>
> Il'd say that would be exactly the kind of application that a pure
> relational approach would *really* work very well indeed.
>
> > >
> > >Yes, absolutely. It would scale at least as well as it does today. It
> would
> > >be more manageable than it is today because the important business
logic
> > >would not be scattered among hundreds of applications. It would easily
> adapt
> > >to all situations. Why would it not?
> > >
> > >
> > [snipped]
> >
> > That is all very clear, and that is how I have understood the goal.
> > But, as they say, the devil lies in the details.
> >
> > The Versata product has been used to create a fairly large rule-based
> > application at
> > American Management Systems. I wonder if anybody knows anything about
> > this application
> > .
> > See this IBM red book:
> >
>
http://publib-b.boulder.ibm.com/Redb...sg246510.html?
Open
>
> Can't say I know about that app, but it's statistic of replacing 3.7
million
> lines of (COBOL) code with 12,000 business rules is not a bad start to
what
> I suspect is possible with relational approaches (i.e. I think I'd be
> surprised if those 12,000 couldn't be reduced by a factor of 10 in a purer
> relational system).

The question is not how few rules one can write, but how few rules the dbms
must enforce to ensure consistency. If they wrote 12,000 but the dbms need
only enforce 1,200, the dbms takes care of the reduction.

"Bob Badour" wrote in message
news:x7udneV85fdKpgiiXTWJhg@golden.net...
> "Paul Vernon" wrote in message
> news:bn31fe$aie$1@gazette.almaden.ibm.com...
> > "Lauri Pietarinen" wrote in message
> > news:bmv01r$u69$1@nyytiset.pp.htv.fi...
> > > >>Say if you had a large insurance company with, say, 10000 rules,
would
> > > >>it *really* work?
> > > >>
> >
> > Il'd say that would be exactly the kind of application that a pure
> > relational approach would *really* work very well indeed.
> >
> > > >
> > > >Yes, absolutely. It would scale at least as well as it does today. It
> > would
> > > >be more manageable than it is today because the important business
> logic
> > > >would not be scattered among hundreds of applications. It would
easily
> > adapt
> > > >to all situations. Why would it not?
> > > >
> > > >
> > > [snipped]
> > >
> > > That is all very clear, and that is how I have understood the goal.
> > > But, as they say, the devil lies in the details.
> > >
> > > The Versata product has been used to create a fairly large rule-based
> > > application at
> > > American Management Systems. I wonder if anybody knows anything about
> > > this application
> > > .
> > > See this IBM red book:
> > >
> >
>
http://publib-b.boulder.ibm.com/Redb...sg246510.html?
> Open
> >
> > Can't say I know about that app, but it's statistic of replacing 3.7
> million
> > lines of (COBOL) code with 12,000 business rules is not a bad start to
> what
> > I suspect is possible with relational approaches (i.e. I think I'd be
> > surprised if those 12,000 couldn't be reduced by a factor of 10 in a
purer
> > relational system).
>
> The question is not how few rules one can write, but how few rules the
dbms
> must enforce to ensure consistency. If they wrote 12,000 but the dbms need
> only enforce 1,200, the dbms takes care of the reduction.
>

Humm,.

Well ultimately it's just one rule. One humongous Boolean expression that
must always evaluate to TRUE for every valid database value for the given
database schema.

I guess that 'number of rules' is nearly as good a measure of inherent
complexity as 'number of lines of code'.
;-)

Regards
Paul Vernon
Business Intelligence, IBM Global Services