I have no idea whether this has been addressed before. I have never
seen it (but I am not in databases), and I would be surprised if no
one had adressed it. Since there are no answers yet, I will attempt to
give a view that may hopefully help. It is very elementary, and it
would really require more study of how this kind of view may combine with
the various types of queries one can make. What I write below was strongly
influenced by your example and the queries that go with it. But there
is more to a relational database.
As remarked by Raphael, generating queries is relatively easy. But
that is not your problem. What you want is meaningful queries. It is
a semantic issue that depends much on the semantics you attach to your
columns.
I suspect that you may have a typing problem, which I tend to
see also as a dimensional problem in the physics sense. It may not be
easy or even meaningful to attempt to deal with it in a purely
automated way. I am not thinking of elementary types as found in most
programming languages, but of abstract types as found now in object
oriented languages, and all languages with data abstraction.
Hence, first of all, you should look whether there is something to
be learned from object oriented databases.
I also know that physicists use "databases" to store their
experimental data before exploiting it with specialized software. If
information can be found on these tools (usually very proprietary),
there is a possibility that they may have addressed this typing issue,
though a physicist told me that is not the case.
Also you should make clear what you intend to provide as input to your
generator, so that your queries make sense. Short of providing some
kind of semantic information, a column of numbers is just a column of
numbers, and any idea you may have to fold, spindle or mutilate them
with SQL queries is fair game. You have to say what data, what semantic information, you provide
to your generator, if you hope for an answer (which is a variant of
the GIGO principle).
I will attempt to illustrate a possible type vision with
physics. Physics uses a lot of scalar quantities: weight, distance,
speed, date, duration, temperature, heat.
You could have these quantities appear to qualify columns in the data
base of a physicist. But not all operations between them make sense.
Testing equality of weight and date probably does not. Actually,
testing equality between to quantities that have different physical
dimension is likely to be meaningless, unless there is some implicit
relation that only the database creator can know about. The same goes
for addition quantities with different dimensions.
Some dimensions have additive qualities, such as weight or volume,
while others do not, such as temperature or density, though they can
be averaged (some of them). Physicists distinguish them by talking of
intensive or extensive properties. Extensive properties are
proportional to the amount of material. They can be added.
I suspect that these concepts exists also for your store management
database, but may have to be refined (I am not sure). The price per
unit of a good is clearly an intensive property. Adding these prices
makes no sense (whether its concerns the same article or different
ones). But, for example, taking the average may have sense, if you do
it for distinct articles.
Now such a classification can lead to more knowledge. For example. the
ratio of two extensive properties is an intensive one. This is why the
price per unit is intensive.
But these concepts apply to properties of materials, and I suspect
things may be more complex than that in general. A ratio could be
additive in a context where intensive/extensive does not apply.
Still, though speeds can be added in physics, if you have a column of
speeds in your database, it is very likely that adding them makes no
sense, and that taking the average makes a lot of sense.
More generally, dimension equations may give you hints of what makes
or does not make sense. Maybe there are other concepts such as
intensive and extensive properties.
Then, in the same spirits as abstract typing in programming languages,
with a specification that states the allowed primitive operations, you
could have a specification of your database that give you information
about columns (such as dimension, or allowed operations) from which
you can infer whether a queries is meaningful.
But I do not believe this can be inferred from a simple knowledge of
the database without any semantic knowledge provided by the
designer. This knowledge should be part of the design process anyway.
Another point is about ranking. I am not sure that making sense can be
rated on a subtler scale than (true, false). If things can make sense
at all, they can be very important to some people and without interest
to others. This can probably be measured by actual database use. But
again, it also depends on what you want the ranking for, and on the
kind of semantic information you can provide.
