# Is there a SQL/English like language that lets you define formulations given some data?

It would be very useful for beginning and non technical users to be able to define models in a way that was natural for them. Further this could perhaps assist generating some kind of generic modelling language which could be used in applications.

Inspired by the BDD (Behavior Driven Development) library

For example, let's take a diet like problem. It could be stated as:

Given I have ingredient banana
Given that a banana costs 1 unit
The ingredient banana has 10 units of potassium
Find me the cheapest ingredient combination for 100 units of potassium


Maybe it's an implementation detail? And I should just define the types of constraints and parse the string data? Is this an area of research?

• People not thinking in SQL after date of birth? 0 rows returned? Aug 13 '19 at 14:29
• I wonder if Prolog could be used to solve this. Not sure if prolog could perform that last sentence requirement. Aug 13 '19 at 16:18
• I think there's a bit of an uncanny valley type effect going on here. If you tell users that "hey, this thing understands English," but it doesn't have a full understanding of all possible English, then your users are bound to get frustrated by the short-comings, in a way that they wouldn't with a "machine language" (i.e. systematic, optimized for easy and non-ambiguous parsing), where users "prepare themselves" and set some expectations. Aug 13 '19 at 16:51
• Great, now graduates will have another excuse not to learn programming! Aug 14 '19 at 10:57

Not answering your question, but related: I just came across a paper (Wang, Shi, and Reddy 2019) that aims to translate natural English into SQL queries. Here's an example of their results:

Maybe this can be a useful starting point if you're searching for literature on this, or starting to build your own English-to-modelling-language translator.

• greatly appreciated!
– fhk
Aug 12 '19 at 18:19
• Something similar was featured on the Stackoverflow blog some weeks ago. Aug 13 '19 at 9:30
• This paper also has a similar idea - arxiv.org/pdf/1907.05774.pdf
– fhk
Aug 15 '19 at 16:49

MiniZinc is the closest I know of. In terms of syntax, it is closer to Python, R, and MATLAB than SQL. However, it is a pretty powerful language.

Example:

% Baking cakes for the school fete (with data file)

int: flour;  %no. grams of flour available
int: banana; %no. of bananas available
int: sugar;  %no. grams of sugar available
int: butter; %no. grams of butter available
int: cocoa;  %no. grams of cocoa available

constraint assert(flour >= 0,"Invalid datafile: " ++
"Amount of flour should be non-negative");
constraint assert(banana >= 0,"Invalid datafile: " ++
"Amount of banana should be non-negative");
constraint assert(sugar >= 0,"Invalid datafile: " ++
"Amount of sugar should be non-negative");
constraint assert(butter >= 0,"Invalid datafile: " ++
"Amount of butter should be non-negative");
constraint assert(cocoa >= 0,"Invalid datafile: " ++
"Amount of cocoa should be non-negative");

var 0..100: b; % no. of banana cakes
var 0..100: c; % no. of chocolate cakes

% flour
constraint 250*b + 200*c <= flour;
% bananas
constraint 2*b  <= banana;
% sugar
constraint 75*b + 150*c <= sugar;
% butter
constraint 100*b + 150*c <= butter;
% cocoa
constraint 75*c <= cocoa;

% maximize our profit
solve maximize 400*b + 450*c;

output ["no. of banana cakes = \(b)\n",
"no. of chocolate cakes = \(c)\n"];


It is a little uncommon these days, but prolog is well suited to this type of problem. It allows you to concisely state and efficiently solve a wide variety of interesting things.

See https://stackoverflow.com/questions/57316504/zebra-puzzle-who-lives-on-which-floor as well as a whole mess of tagged questions https://stackoverflow.com/questions/tagged/prolog+zebra-puzzle as examples.

## Controlled natural language

There's a bunch of research in using controlled natural language (e.g. in the CNL workshop https://link.springer.com/conference/cnl) and one of the use cases is for making queries in some knowledge base. If the data and its ontology is in a structured format such as RDF, then there exist tools for querying that data directly (usually with SPARQL), there are a bunch of tools for visualizing the models/ontologies and the data in them, and there are also natural language querying tools such as http://aksw.org/Projects/AutoSPARQL.html or https://github.com/AKSW/NSpM which I admittedly haven't tried. It's a whole field of research that's not that related to operations research, but there are some interesting things there.